使用循环使用 Dataframe Pandas 创建 Excel 工作表

我正在开发这个函数，它可以抓取网站上的 Fantasy Football 信息并将其写入 Excel 文件。最终，我希望在 Excel 工作簿的单独工作表中包含每周的信息。

下面发布的代码可以完美运行，直到我想将其写入 Excel 工作簿。该工作簿最终只有第 17 周的数据。似乎 pandas ExcelWriter 每当我真的希望它每次添加一个工作表时都会覆盖该工作表。

我在网上找不到任何有关在 pandas ExcelWriter 中创建带有循环的工作表的信息，所以我不完全确定它是否可以按照我想要的方式完成。

import bs4 as bs
import urllib.request
import pandas as pd
from pandas import ExcelWriter    

for week in range(1,18):
    #IGNORE MOST OF THIS STUFF BELOW BECAUSE IT WORKS AS IS
    source = urllib.request.urlopen('http://fftoday.com/stats/playerstats.php?Season=2015&GameWeek='+str(week)+'&PosID=10&LeagueID=1').read()
    soup = bs.BeautifulSoup(source, 'lxml')

    table = soup.find('table', width='100%', border='0', cellpadding='2', cellspacing='1')
    table_rows = table.find_all('tr')

    player_data = {}

    for tr in table_rows:
       td = tr.find_all('td')
       row = [i.text for i in td]
       if row != ['\xa0 ', 'Passing', 'Rushing', 'Fantasy'] and row != ['Player\nSort First: \n\n\n\xa0\xa0\n\t\tLast: \n\n\n', 'Team\n\n\n\n', 'G\n\n\n\n', 'Comp\n\n', 'Att\n\n', 'Yard\n\n', 'TD\n\n', 'INT\n\n', 'Att\n\n', 'Yard\n\n', 'TD\n\n', 'FPts\n\n\n\n', 'FPts/G\n\n\n\n']:
           names = str(row[0]).encode('utf-8')
           names = str(names)[:-1].split()[1:]
           names[0:] = [' '.join(names[0:])]
           row[0] = names[0]
           player_data[str(row[0])] = row[1:]
    df_qb = pd.DataFrame.from_dict(player_data)
    df_qb = df_qb.transpose()
    df_qb.columns = ['Team', 'Games', 'Completions', 'Att', 'Yards',
                'TD', 'INT', 'Rush Att', 'Rush Yards', 'Rush TD',
                'Fantasy Points', 'Fantasy Points per Game']

    #EVERY THING WORKS JUST FINE UNTIL RIGHT HERE
    writer = ExcelWriter('Weekly Fantasy Football Data.xlsx')
    df_qb.to_excel(writer, 'Week ' + str(week))
    writer.save()

您需要做的就是将 writer deceleration 移到循环之外

e.g.

a = numpy.arange(5)
w = ExcelWriter('e.xlsx')
for i in a:
    df = pd.DataFrame({'a':np.random.randint(1,100,10)})
    df.to_excel(w, sheet_name='sheet '  + str(i))
    w.save()