Merging 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

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

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')
all = finance_auditing.append(finance_lending)

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

CodePudding user response：

You can use list comprehension to iterate over the pairs of path and sheet and read the corresponding excel file, finally concat all the excel files:

pd.concat([pd.read_excel(path, sheet_name=sheet) 
           for path, sheet in zip(df.Path, df.Sheet)])