python-3.x - 将循环输出保存到多个 Excel 工作表

Question

我有一个包含多年水数据的 csv 文件。我已经将每个水年分解成它自己的数据框。现在我想对那些水年做一些数学运算，然后将每个水年保存到它自己的 Excel 表中。

代码的数学部分正在工作，但我在正确命名和保存循环输出的最后一步时遇到了麻烦。现在我有它创建excel文件并正确创建工作表名称，但循环只是将最终迭代保存到所有工作表。我已经用谷歌搜索了，但我无法得到任何其他类似问题的答案。这是我的第一个 python 程序，因此不胜感激。

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

标签： python-3.x

Answer 1

代码中的问题

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

而是试试这个。

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