How to Save or export multiple dataframes in excel in different tabs?

Question

I need to Export or save pandas Multiple Dataframe in an excel in different tabs? Let's suppose my df's is:

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

These are my four Dataframes and I need to Export as an Excel with 4 tabs i.e, df1,df2,df3,df4.

Answer 1

A simple method would be to hold your items in a collection and use the pd.ExcelWriter Class

Lets use a dictionary.

#1 Create a dictionary with your tab name and dataframe.

dfs  = {'df1' : df1, 'df2' : df2...} 

#2 create an excel writer object.

writer = pd.ExcelWriter('excel_file_name.xlsx')

#3 Loop over your dictionary write and save your excel file.

for name,dataframe in dfs.items():
    dataframe.to_excel(writer,name,index=False)

writer.save()

---

adding a path.

from pathlib import Path

trg_path = Path('your_target_path')

writer = pd.ExcelWriter(trg_path.joinpath('excel_file.xlsx'))

Answer 2

Using xlsxwriter, you could do something like the following:

import xlsxwriter
import pandas as pd

### Create df's here ###

writer = pd.ExcelWriter('C:/yourFilePath/example.xslx', engine='xlsxwriter')
workbook = writer.book

### First df tab
worksheet1 = workbook.add_worksheet({}.format('df1') # The value in the parentheses is the tab name, so you can make that dynamic or hard code it

row = 0
col = 0

for Name, Rank in (df1):
    worksheet.write(row, col, Name)
    worksheet.write(row, col + 1, Rank)
    row += 1

### Second df tab

worksheet2 = workbook.add_worksheet({}.format('df2')

row = 0
col = 0

for Name, Rank in (df2):
    worksheet.write(row, col, Name)
    worksheet.write(row, col + 1, Rank)
    row += 1

### as so on for as many tabs as you want to create

workbook.close()

xlsxwriter allows you to do a lot of formatting as well. If you want to do that check out the docs