Column with end of file name python

December 17, 2021

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

>Solution :

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)