How to write to separate excel worksheets within the same workbook in a for loop?

Question

I have a for-loop that computes DataFrames and outputs them to an excel sheet. Right now, I am able to export one full DF to one excel sheet.

As shown below, I am trying to add code inside the for-loop so that it can do this computation for many DFs and export each DF to a separate excel worksheet within the same workbook, delineated by the sheet_name.

list = ["AUD_JPY", "AUD_USD"]
granularity = "D"
bar_count = 5000
MA_list = [20, 50]

writer = pd.ExcelWriter('BT.xlsx')
resultsDF = pd.DataFrame(columns = ['instrument', 'Start date', 'End date']

for MA in MA_list:    
    for instrument in list:
...
        data = {'instrument': [instrument], 'Start date': [startDate], 'End date': [endDate]}            
        resultsDF = resultsDF.append(data, ignore_index=True)
        instrument=+1

resultsDF.to_excel(writer, sheet_name='Price_Over_SMA{0}_{1}'.format(MA, 
granularity))
writer.save()

MA=+1

This only exports the first DF from the 'instrument' loop to the excel sheet. I get this worksheet:

But I also want to export another worksheet next to that one that should be called 'Price_Over_SMA50_D' in this case.

Not sure what I am doing wrong.

Thanks

Possible duplicate of [Save list of DataFrames to multisheet Excel spreadsheet](https://stackoverflow.com/questions/14225676/save-list-of-dataframes-to-multisheet-excel-spreadsheet) — Gabriel A, Jan 13 '18 at 05:05

score 0 · Accepted Answer · edited Jan 15 '18 at 08:34

0

I found out it was a matter of moving writer.save() outside of my 'MA' for-loop and resetting the DF after each loop.

edited Jan 15 '18 at 08:34

cs95

379,657
97
704
746

answered Jan 13 '18 at 05:36

cadig

101
2
8

score 0 · Answer 2 · answered Jan 13 '18 at 12:58

Using Pandas, you can save different dataframes into different sheets of the same excel file:

Let's say I have a list of dataframes dfs that I want to save as separate sheets in an excel file whose absolute path is file_name.

You could use something like this:

import pandas as pd
file_name = # you excel file name you're saving
dfs = # my list of dataframes
for i, df in enumerate(dfs):
    df.to_excel(file_name, sheetname='Sheet_{0}'.format(i))

How to write to separate excel worksheets within the same workbook in a for loop?

2 Answers2