Merge two tables from two excel files and create a column with the name of the corresponding excel file in python

Question

I would like to merge 2 tables from two different Excel files. I'd like to add a column in the new table to indicate the name of the excel file in question.

The aim would be to merge a multitude of tables with a column indicating the name of the file.

file_path = "C:\excel files" myfile = os.listdir(file_path) for f in myfile: with open(f) as infile: contents = infile.read() print(contents)

Answer 1

you can use pandas, can you try the following:

from pathlib import Path

fpath1 = Path('file1/path/filename2.xlsx')
fpath2 = Path('file1/path/filename2.xlsx')
df1 = pd.read_excel(fpath1)
df2 = pd.read_excel(fpath2)
rename_cols = {col: f'{col}_{fpath2.stem}' for col in df2.columns}
df = pd.concat([df1, df2.rename(columns=rename_cols)], ignore_index=True)