Recombine sheets with multiple level of headers, and write out an excel file with different sheets n-CodePudding

Suppose we have an excel file (you can download sample data from

So the question is how could we split dataframe to four sheets as follows:

Note that I gave the expected result in the sample data: a new excel file containing Mexico, Moldova, Nepal and Israel as sheets.

CodePudding user response：

Create index by first column by index_col=0 in read_excel, join by pd.concat with axis=1 and last create sheetnames by country names form first level of MultiIndex:

sheets = ['PPP_GDP', 'CPI', 'PPI']
df = pd.concat([pd.read_excel('./test_data_2022-10-25.xlsx', 
                              sheet_name=sheet, 
                              header=[0, 1, 2], 
                              index_col=0) 
                 for sheet in sheets], axis=1)


writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')

for name, g in df.groupby(level=0, axis=1):
    g.to_excel(writer, sheet_name=name)

writer.save()