Python Loop Saving Twice in Loop to Excel Spreadsheet Tab

Question

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:

And, after the second save sequence:

thank you,

I'm curious; does it work if you leave the file open, without opening it a second time? — Tim Roberts
– Tim Roberts, Commented Aug 24, 2021 at 20:52
And, by the way, you have three backslashes at the beginning of your path, but you need four. — Tim Roberts
– Tim Roberts, Commented Aug 24, 2021 at 20:53
no, don't think so. I close the file when the script is running so I'm not completely sure and then I open it when it finisihes. — user2100039
– user2100039, Commented Aug 24, 2021 at 20:55
Pandas's to_excel is just a helper. It's not a fully flexible Excel editing solution. You COULD, for example, use to_excel on your big list, then use a module like xlsxwriter to modify it. — Tim Roberts
– Tim Roberts, Commented Aug 24, 2021 at 20:59

Codesidian · Accepted Answer · 2021-08-26 08:03:15Z

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)

Collectives™ on Stack Overflow

Python Loop Saving Twice in Loop to Excel Spreadsheet Tab

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related