Saving loop output to multiple excel sheets

Question

I have a csv file full of multiple years of water data. I've broken up each water year into it's own data frame. Now I want to do some math to those water years then save each water year to it's own excel sheet.

The math part of the code is working, but I'm having trouble with the final step of naming and saving the output of the loop correctly. Right now I have it creating the excel file and creating the sheet names correctly, but the loop just saves the final iteration to all the sheets. I've googled around but I can't get any other of the similar questions answers to work. This is my first python program so advice would be appreciated.

import pandas as pd

with open(r'wft.csv') as csvfile:
    tdata = pd.read_csv(csvfile)

tdata['date'] = pd.to_datetime(tdata['date'], format='%m/%d/%Y %H:%M')
tdata = tdata.set_index(['date'])


wy2015 = tdata.loc['2014-10-1 00:00' : '2015-7-1 00:00']
wy2016 = tdata.loc['2015-10-1 00:00' : '2016-7-1 00:00']
wy2017 = tdata.loc['2016-10-1 00:00' : '2017-7-1 00:00']

writer = pd.ExcelWriter('WFT.xlsx', engine='xlsxwriter')
wyID = [wy2014, wy2015, wy2016, wy2017]
seq = ['wy2014', 'wy2015', 'wy2016', 'wy2017']

for df in wyID:
    df = df.sort_values(by=['turbidity'], ascending=False)
    df['rank'] = df['turbidity'].rank(method = 'first', ascending=0)
    df['cunnanes'] = (df['rank'] - 0.4)/(len(df['rank']) + 0.2)*100
    for name in seq:
        df.to_excel(writer, sheet_name= name)
writer.save()

Hey! Did you figure this out?

thatNLPguy
– thatNLPguy

2019-03-30 00:24:12 +00:00
Commented Mar 30, 2019 at 0:24 — thatNLPguy
– thatNLPguy, Commented Mar 30, 2019 at 0:24
@thatNLPguy yes he did

user11093202
– user11093202

2019-03-30 00:37:01 +00:00
Commented Mar 30, 2019 at 0:37 — user11093202
– user11093202, Commented Mar 30, 2019 at 0:37
Cool cool cool!

thatNLPguy
– thatNLPguy

2019-03-30 00:50:18 +00:00
Commented Mar 30, 2019 at 0:50 — thatNLPguy
– thatNLPguy, Commented Mar 30, 2019 at 0:50

thatNLPguy · Accepted Answer · 2019-03-30 00:31:44Z

1

Issues in your code

writer = pd.ExcelWriter('WFT.xlsx', engine='xlsxwriter')
wyID = [wy2014, wy2015, wy2016, wy2017]
seq = ['wy2014', 'wy2015', 'wy2016', 'wy2017']

for df in wyID: # outer loop that figures out wy20xx
    df = df.sort_values(by=['turbidity'], ascending=False)
    df['rank'] = df['turbidity'].rank(method = 'first', ascending=0)
    df['cunnanes'] = (df['rank'] - 0.4)/(len(df['rank']) + 0.2)*100
    for name in seq: # you loop through all the names and write all sheets every time. you want to be writing just one
        df.to_excel(writer, sheet_name= name)
writer.save()

Instead try this.

for i, df in enumerate(wyID): # outer loop that figures out wy20xx
        df = df.sort_values(by=['turbidity'], ascending=False)
        df['rank'] = df['turbidity'].rank(method = 'first', ascending=0)
        df['cunnanes'] = (df['rank'] - 0.4)/(len(df['rank']) + 0.2)*100
        df.to_excel(writer, sheet_name= seq[i]) # writes to correct wy20xx sheet
writer.save() # Now you're done writing the excel

answered Mar 30, 2019 at 0:31

thatNLPguy

1018 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Water_Math Over a year ago

Thanks! I'll plug it in monday and see how it goes. Now I see how I was looping through all the sheets all the time.

Collectives™ on Stack Overflow

Saving loop output to multiple excel sheets

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related