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:
