append several Pandas dataframe on top of each other in the same excel sheet

I have two dataframes with labels, I want to append or concat them at bottom of each other

d = {}
d['first_level'] = pd.DataFrame(columns=['idx', 'a', 'b', 'c'],
                                 data=[[10, 1, 2, 3],
                                       [20, 4, 5, 6]]).set_index('idx')

d['first_level2'] = pd.DataFrame(columns=['idx', 'a', 'b', 'c'],
                                 data=[[10, 1, 2, 3],
                                       [20, 4, 5, 6]]).set_index('idx')

    df_final = pd.concat(d, axis=1,)
    writer = pd.ExcelWriter('test_file.xlsx')
            # df_final.reset_index(drop=True, inplace=True)
            # ddf_finalf=df_final.drop(['idx'],axis=1)
            df_final.to_excel(writer, sheet_name='my_analysis', index=True,)
        
        writer.save()

I have this code, this results in excel output like this:

 first_level  first_level2 
 idx a b c     idx a b c
  10 1 2 3      10 1 2 3
  20 4 5 5      20 4 5 5

However I want first_level2 at bottom of first_level,and the excel output should be like this:

first_level  
idx a b c 
10 1 2 3 
20 4 5 6 

first_level2 
idx a b c
10 1 2 3
20 4 5 5

I tried with MultiIndex too, but got the same results,How can I achieve this using pandas I want to such output in excel, I tried looking into docs but couldn’t find anything relevant.

>Solution :

If you want to append each dataframe with a custom header into an excel sheet, you can use a loop to add each DataFrame successively with help of pandas.ExcelWriter and the skiprow option of to_excel:

writer = pd.ExcelWriter('file.xlsx')
start = 0
for k, df in d.items():
    df2 = pd.concat({k: df}, axis=1)
    df2.to_excel(writer, sheet_name='test', startrow=start)
    start += len(df)+df.columns.nlevels+int(bool(df.index.name))+1
writer.save()

output:

enter image description here

Leave a Reply