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

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.

CodePudding user response：

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

CodePudding user response：

as answered by Epsi95, axis should be 0

import pandas as pd
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=0,)
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()