How to Write Multiple Data Frames in an Excel Sheet

Question

I have multiple data frames with same column names. I want to write them together to an excel sheet stacked vertically on top of each other. And between each, there will be a text occupying a row. This is what I have in mind.

I tried the pandas.ExcelWriter() method, but each dataframe overwrites the previous frame in the sheet, instead of appending.

Note that, I still need multiple sheets for different dataframe, but also multiple dataframes on each sheet. Is it possible? Or any other python library which can dynamically generate the excel sheet from pandas dataframes?

Answer 1

an example to write in same sheet:

import pandas as pd

data1 = """
class    precision   recall 
<18      0.0125         12           
18-24    0.0250         16 
25-34    0.00350         4
"""
data2 = """
class    precision   recall 
<18      0               0           
18-24    0.25            6 
25-34    0.35            5
"""

#create 2 df for sample
df1 = pd.read_csv(pd.compat.StringIO(data1), sep='\s+')
df1.name = "Dataframe1"
df2 = pd.read_csv(pd.compat.StringIO(data2), sep='\s+')
df2.name = "Dataframe2"
print(df1);print(df2)

writer = pd.ExcelWriter('e:\\test.xlsx',engine='xlsxwriter')
workbook=writer.book
worksheet=workbook.add_worksheet('Result')
writer.sheets['Result'] = worksheet
worksheet.write_string(0, 0, df1.name)

df1.to_excel(writer,sheet_name='Result',startrow=1 , startcol=0)
worksheet.write_string(df1.shape[0] + 4, 0, df2.name)
df2.to_excel(writer,sheet_name='Result',startrow=df1.shape[0] + 5, startcol=0)
writer.save()

output:

if you want to write in different sheets:

import pandas as pd

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('e:\\test.xlsx', engine='xlsxwriter')

# Write each dataframe to a different worksheet. you could write different string like above if you want
df1.to_excel(writer, sheet_name='Sheet1')
df2.to_excel(writer, sheet_name='Sheet2')

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Answer 2

Several dataframes to same sheet from here and here with selected sheet:

writer = pd.ExcelWriter('pandas_multiple.xlsx', engine='xlsxwriter')

# Position the dataframes in the worksheet.
df1.to_excel(writer, sheet_name='Sheet1')  # Default position, cell A1.
df2.to_excel(writer, sheet_name='Sheet1', startcol=3)
df3.to_excel(writer, sheet_name='Sheet1', startrow=6)

writer.save()

Answer 3

Here's a universal function for writing any amount of Pandas dataframes to a single Excel sheet:

import pandas as pd

def write_dataframes_to_excel_sheet(dataframes, dir, name):
    with pd.ExcelWriter(f'{dir}/{name}.xlsx', engine='xlsxwriter') as writer:
        workbook = writer.book
        worksheet = workbook.add_worksheet('Result')
        writer.sheets['Result'] = worksheet

        COLUMN = 0
        row = 0

        for df in dataframes:
            worksheet.write_string(row, COLUMN, df.name)
            row += 1
            df.to_excel(writer, sheet_name='Result',
                        startrow=row, startcol=COLUMN)
            row += df.shape[0] + 2

Here's an example of use:

# Create sample dataframes
df1 = pd.DataFrame([(1, 2, 3), (4, 5, 6)], columns=('A', 'B', 'C'))
df1.name = "Dataframe1"
df2 = pd.DataFrame([(7, 8, 9), (10, 11, 12)], columns=('A', 'B', 'C'))
df2.name = "Dataframe2"
dataframes = (df1, df2)

write_dataframes_to_excel_sheet(dataframes, '/Users/foo/Documents', 'bar')

Answer 4

You can refer to this docs for better understanding , by using the parameter if_sheet_exists="overlay" in ExcelWriter

with ExcelWriter("path_to_file.xlsx", mode="a" 
                  ,if_sheet_exists="overlay",) as writer:
                   df1.to_excel(writer, sheet_name="Sheet1")
                   df2.to_excel(writer,sheet_name="Sheet1", startrow=df1.shape[0] + 5, startcol=0)

And with the help of startrow ,startcol parameter in the to_excel Function to place you you DataFrame suitably

Answer 5

You can open the excel editor and write to it and then save

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
data.to_excel(writer, sheet_name='Sheet1',
                  encoding='utf-8', index=False)
writer.save()

Please refer this answer https://stackoverflow.com/questions/34744863/python-how-to-use-excelwriter-to-write-into-an-existing-worksheet