Save as excel format a multi index column Pandas with diffrent level names in a long format

Question

Given a list of df, the objective is to save all df in single page long format.

Each of the df is make up from 3 level columns index. The first level is unique ('theme1' and 'theme2') for each of the df. Whereas, the second ('level2) and third (A,B,C) level is similar for all 'df`.

In the code snippet below,

import pandas as pd
df1 = pd.DataFrame({'A': [11, 21, 31],
               'B': [12, 22, 32],
               'C': [13, 23, 33]},
              index=['ONE', 'TWO', 'THREE'])

df2 = pd.DataFrame({'A': [111, 211, 311],
               'B': [121, 221, 321],
               'C': [131, 231, 331]},
              index=['ONE', 'TWO', 'THREE'])

df1.columns = pd.MultiIndex.from_product([['theme1'],['level2'],df1.columns ])

df2.columns = pd.MultiIndex.from_product([['theme2'],['level2'],df2.columns ])

list_df=[df1,df2]
df_all=pd.concat(list_df,axis=1)

df_all.to_excel('df_all1.xlsx')

A single page wide format is produced

But, I wonder how to make a single page long format as depicted below

p.s. Long format allow easy excel vertical scrolling

Rivers · Accepted Answer · 2021-08-05T15:29:02.863

You can do it like that:

import pandas as pd

def multiple_dfs_to_same_excel_sheet(df_list, file_name, sheet_name, spaces):
    writer = pd.ExcelWriter(file_name,engine='xlsxwriter')   
    row = 0
    for dataframe in df_list:
        dataframe.to_excel(writer,sheet_name=sheet_name,startrow=row , startcol=0)   
        row = row + len(dataframe.index) + spaces + 1
    writer.save()


df1 = pd.DataFrame({'A': [11, 21, 31],
               'B': [12, 22, 32],
               'C': [13, 23, 33]},
              index=['ONE', 'TWO', 'THREE'])

df2 = pd.DataFrame({'A': [111, 211, 311],
               'B': [121, 221, 321],
               'C': [131, 231, 331]},
              index=['ONE', 'TWO', 'THREE'])

df1.columns = pd.MultiIndex.from_product([['theme1'],['level2'],df1.columns ])

df2.columns = pd.MultiIndex.from_product([['theme2'],['level2'],df2.columns ])

list_df=[df1,df2]

multiple_dfs_to_same_excel_sheet(list_df, 'excel_file.xlsx', 'sheet_01', 6)

I added the definition of function multiple_dfs_to_same_excel_sheet at the beginning and its call at the end. This function is from @TomDobbs (Putting many python pandas dataframes to one excel worksheet).

Note: I set the number of spaces to 6 in order to have 3 rows between the tables as in your image. That's because we have to take the number of the first rows ("Theme1", "Level2" and "A B C") into account, so here that's 3 first rows ("Theme1", "Level2" and "A B C") plus 3 spaces.

Save as excel format a multi index column Pandas with diffrent level names in a long format

1 Answers1