CODEWITHSUNDEEP
pythonexcelloopsxlsxwriterpandas.excelwriter
user2100039
user2100039

Reputation: 1356

Python Loop Saving Twice in Loop to Excel Spreadsheet Tab

I have my code that looks like the following as part of a long loop structure that writes 2 dfs to the same spreadsheet and excel tab. Here is the code I'm using trying to append with each write sequence:

# WRITE OUTPUT TO EXCEL FILE - 
with pd.ExcelWriter('\\\porfiler03\\gtdshare\\ERA5-MAPPING\\PCA\\PCA_ERA5_output.xlsx', engine="openpyxl", mode="a") as writer:
    w.to_excel(writer, sheet_name=sheet) 
with pd.ExcelWriter('\\\porfiler03\\gtdshare\\ERA5-MAPPING\\PCA\\PCA_ERA5_output.xlsx', engine="openpyxl", mode="a") as writer:
    finalpcs.to_excel(writer, sheet_name=sheet,startrow=5)

My result, however, has the df1 ("w") written to one worksheet tab (14) followed by the next df ("finalpcs") written to a new worksheet tab (141). I need them both written to the same worksheet tab - "14". I've tried to use "startrow" for the second df to not overwrite the first write of df "w".

Here is a pic of the first save sequence:

enter image description here

And, after the second save sequence:

enter image description here

thank you,

Upvotes: 1

Views: 797

Answers (1)

Codesidian
Codesidian

Reputation: 310

It's not possible with how you're currently doing it. The docs for the ExcelWriter class has this flag:

if_sheet_exists{‘error’, ‘new’, ‘replace’}, default ‘error’: How to behave when trying to write to a sheet that already exists (append mode only).

  • error: raise a ValueError.

  • new: Create a new sheet, with a name determined by the engine.

  • replace: Delete the contents of the sheet before writing to it.

You can either wipe the sheet or have a suffix appended to it in the event of a clash. My suggestion would be to take your excel code out of the 'with' since every time you're writing, it will be starting the process from the beginning. Meaning the worksheet and workbook will be closed then reopened. I'm assuming this is the issue.

Since this is tagged as xlsxwriter, but you're using openpyxl. This is the answer for multiple dataframes in xlsxwriter:

Under Handling multiple Pandas Dataframes in the Working with Python Pandas in the docs shows the following example:

writer = pd.ExcelWriter("foo.xlsx",
                        engine='xlsxwriter',
                        datetime_format='mmm d yyyy hh:mm:ss',
                        date_format='mmmm dd yyyy')

# 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)

# Write the dataframe without the header and index.
df4.to_excel(writer, sheet_name='Sheet1',
             startrow=7, startcol=4, header=False, index=False)

Upvotes: 1

Related Questions