CODEWITHSUNDEEP
pythonpandasopenpyxlxlsxwriter
MaxB
MaxB

Reputation: 458

Output to excel file without overwriting sheets

I have a python script I am running that currently does three separate things and outputs each result to a different excel file. Is it possible to instead have all of my outputs in one excel file on different sheets? It seems that the latest result always overwrites the whole excel file.

Below was my thinking: 

 df_finit1.to_excel('OutFile.xlsx', sheet_name = 'Sheet1') 
 df_finit2.to_excel('OutFile.xlsx', sheet_name = 'Sheet2') 
 df_finit3.to_excel('OutFile.xlsx', sheet_name = 'Sheet3')

I also tried to use xlsx writer to create a file with 3 different sheets, and output to those sheets but I got the same result. Any tips?

Upvotes: 3

Views: 6360

Answers (2)

Frenchy
Frenchy

Reputation: 17007

you have to use ExcelWriter like this: (maybe you have to install this module)

##############################################################################
#
# An example of writing multiple dataframes to worksheets using Pandas and
# XlsxWriter.

import pandas as pd


# Create some Pandas dataframes from some data.
df1 = pd.DataFrame({'Data': [11, 12, 13, 14]})
df2 = pd.DataFrame({'Data': [21, 22, 23, 24]})
df3 = pd.DataFrame({'Data': [31, 32, 33, 34]})

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

# Write each dataframe to a different worksheet.
df1.to_excel(writer, sheet_name='Sheet1')
df2.to_excel(writer, sheet_name='Sheet2')
df3.to_excel(writer, sheet_name='Sheet3')

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

Upvotes: 5

An economist
An economist

Reputation: 1311

You should use ExcelWriter, it allows to open single .xlsx file and manupulate it. 

import pandas as pd

# Initialize xlsx writer
writer = pd.ExcelWriter('output_file.xlsx', engine='xlsxwriter')
workbook = writer.book

df1 = pd.DataFrame({"a": [1,2,3],
                    "b": [1,2,3]})

df2 = pd.DataFrame({"c": [1,2,3],
                    "d": [1,2,3]})

df3 = pd.DataFrame({"e": [1,2,3],
                    "f": [1,2,3]})

df1.to_excel(writer,
             sheet_name="sheet1",
             startrow=0,
             startcol=0)

df2.to_excel(writer,
             sheet_name="sheet2",
             startrow=0,
             startcol=0)

df3.to_excel(writer,
             sheet_name="sheet3",
             startrow=0,
             startcol=0)

writer.save()

Upvotes: 5

Related Questions