Adding new columns on hundreds excel file with path reference into one pandas dataframe-CodePudding

I have probably hundreds or thousands small excel file with bracket into one pandas dataframe

Before I merge them, I need to give flag for which category they come from

Here's my table of reference df

    Dataframe_name      Path                                 Sheet
45  finance_auditing    Finance - Accounting/TopSites-Fin... Aggregated_Data_for_Time_Period
46  finance_lending     Finance - Banking/TopSites-...          Aggregated_Data_for_Time_Period

What I did Dataframe_name name column is filled manually, but what I expected is using refference table

finance_auditing  = pd.read_excel('Finance - Accounting/TopSites-Fin... ','Aggregated_Data_for_Time_Period')
finance_lending   = pd.read_excel('Finance - Banking/TopSites-... ','Aggregated_Data_for_Time_Period')
finance_auditing['Dataframe_name'] = 'finance_auditing'
finance_lending['Dataframe_name'] = 'finance_lending'
dF_all = pd.concat([pd.read_excel(path, sheet_name=sheet) 
           for path, sheet in zip(df.Path, df.Sheet)])

The problem is I have hundreds of of file to read and need to append them all

CodePudding user response：

This would be fairly simply, you can assign the flag dynamically for each iteration:

pd.concat([pd.read_excel(path, sheet_name=sheet).assign(df_name=name)
                             for name, path, sheet in df.to_numpy()])