Column with end of file name python-CodePudding

I have a code that merges all txt files from a directory into a dataframe

follow the code below

import pandas as pd
import os
import glob

diretorio = "F:\PROJETOS\LOTE45\ARQUIVOS\RISK\RISK_CUSTOM_FUND_N1" 
files = [] 

files = [pd.read_csv(file, delimiter='\t')
     for file in glob.glob(os.path.join(diretorio ,"*.txt"))]


df = pd.concat(files, ignore_index=True)
df

that gives result to this table

I needed to add a date column to this table, but I only have the date available at the end of the filename.

How can I get the date at the end of the filename and put it inside the dataframe.

I have no idea how to do this

CodePudding user response：

Assuming the file structure is constant, you can parse the end of the filename for every iteration of the loop this way :-

from datetime import datetime

files = []

for file in glob.glob(os.path.join(diretorio ,"*.txt")):
  df_f = pd.read_csv(file, delimiter='\t')
  df_f['date'] = datetime.strptime(file[-11:-4], "%d%m%Y")
  files.append(df_f)


df = pd.concat(files, ignore_index=True)

CodePudding user response：

import pandas as pd
import os

diretorio = "F:/PROJETOS/LOTE45/ARQUIVOS/RISK/RISK_CUSTOM_FUND_N1/"
files = []
for filename in os.listdir(diretorio):
    if filename.endswith(".csv"):
        df = pd.read_csv(diretorio   filename, sep=";")
        df['Date'] = filename.split('.')[0].split("_")[-1]
        files.append(df)
df = pd.concat(files, ignore_index=True)
print(df)