Export multiple dataframes in one excel tab

Question

I need to Export or save pandas multiple Dataframe in one excel tab. Let's suppose my df's are below and need to export it the same way in the excel all together in one tab.

 df1:
Id  Name    Rank
1   Scott   4
2   Jennie  8
3   Murphy  1

df2:
Id  Name    Rank
1   John     14
2   Brown    18
3   Claire   11

df3:
Id  Name    Rank
1   Shenzen   84
2   Dass      58
3   Ghouse    31

df4:
Id  Name    Rank
1   Zen     104
2   Ben     458
3   Susuie  198

Refer the solution her : https://stackoverflow.com/questions/47737220/append-dataframe-to-excel-with-pandas — Kaushal Pahwani, Aug 26 '22 at 15:27
How can I keep only one header at the top and remove the bottom headers for other dataframes? — Josh, Aug 26 '22 at 16:45

Timeless · Accepted Answer · 2022-08-26T17:45:42.250

0

You can loop through a list of your dfs and use pandas.ExcelWriter :

import pandas as pd

list_df = [df1, df2, df3, df4]
df1.name = 'df1'
df2.name = 'df2'
df3.name = 'df3'
df4.name = 'df4'

sr = 0

with pd.ExcelWriter('your_excel_name.xlsx') as writer:
    for df in list_df:
        df.to_excel(writer, startrow=sr, sheet_name='your_sheet_name', index=False)
        workbook  = writer.book
        worksheet = writer.sheets['Sheet1']
        title = df.name + ':'
        worksheet.write(sr-1, 0, title)
        sr += (df.shape[0] + 3)

Note : The dataframes will be put in the same sheet and separated by one single empty row. You can adjust this by changing the value of (df.shape[0] + 3).

Output (in Excel)

Edit :

If you wanna keep only one header for all the dataframes, use this :

sr = df1.shape[0]+4
with pd.ExcelWriter('output.xlsx') as writer:
    df1.to_excel(writer, startrow=1, index=False)
    workbook  = writer.book
    worksheet = writer.sheets['Sheet1']
    title = df1.name + ':'
    worksheet.write(0, 0, title)
    for df in list_df[1:]:
        df.to_excel(writer, startrow=sr, index=False, header=False)
        workbook  = writer.book
        worksheet = writer.sheets['Sheet1']
        title = df.name + ':'
        worksheet.write(sr-1, 0, title)
        sr += (df.shape[0] + 2)

edited Aug 26 '22 at 17:45

answered Aug 26 '22 at 15:29

Timeless

22,580
4
12
30

1

Thanks for the quick reply. I dont need to concatenate records. I need to export the file the same way as I have posted above (from top to bottom rows). In first 5 rows df1s output Next rows df2s output and so on – Josh Aug 26 '22 at 16:19
I updated my answer. Check it out ! – Timeless Aug 26 '22 at 16:37
I also want to display header for the first dataframe only and remove headers for the other dataframes display below. How can I do that? – Josh Aug 26 '22 at 17:14
Take a look on the Edit/Workaround I put in the end of my answer. – Timeless Aug 26 '22 at 17:46
1

Thank you so much for your help! This is exactly I was looking for and I learned new tricks today with your help :) – Josh Aug 26 '22 at 18:06

Export multiple dataframes in one excel tab

1 Answers1

Output (in Excel)

Edit :