Python Forum

I hope one of you are willing to help a complete Python beginner. I have managed to create my first script where I append multiple excel files in a folder into one merged file. So far so good! But I also need the script to create an additional column and complete it with the last two characters of the filename from each file it appends.

My script looks like this for now:

import pandas as pd
import glob

# getting excel files to be merged
path = "C:\\Users\\123\\OneDrive\\Descriptions\\Translated"


# read all the files with extension .xlsx i.e. excel 
filenames = glob.glob(path + "\*.xlsx")
print('File names:', filenames)

# empty data frame for the new output excel file with the merged excel files
outputxlsx = pd.DataFrame()

# for loop to iterate all excel files
for file in filenames:
   # using concat for excel files
   # after reading them with read_excel()
   df = pd.concat(pd.read_excel(file, sheet_name=None), ignore_index=True, sort=False)

   # appending data of excel files
   outputxlsx = outputxlsx.append( df, ignore_index=True)

print('Final Excel sheet now generated at the same location:')
outputxlsx.to_excel("C:/Users/123/OneDrive/Descriptions/Translated/Merged.xlsx", index=False)

The files in the folder are named like this:

CZ, PL, TR_cs-CZ

CZ, PL, TR_pl-PL

CZ, PL, TR_tr-TR

So the last column should be:

CZ

PL

TR

Thank you!!