使用Loop创建带有Dataframe Pandas的Excel工作表 [英] Using Loop to Create Excel Sheets with Dataframe Pandas

问题描述

我正在使用此功能，该功能可抓取一个网站以获取梦幻足球信息并将其写入Excel文件.最终，我希望每周在Excel工作簿的另一张纸上获得信息.

I'm working on this function that scrapes a website for fantasy football information and writes it to an Excel file. Ultimately, I want to have information for each week on a separate sheet in the Excel workbook.

下面发布的代码可以完美工作，直到我想将其写入Excel工作簿.该工作簿最终仅具有第17周的数据.好像我真的希望熊猫ExcelWriter每次都添加一个工作表时，都会覆盖该工作表.

The code as posted below works perfectly until I want to write it to the Excel workbook. The workbook ends up having just week 17 data. It seems that the pandas ExcelWriter overwrites the sheet every time when I really want it to add a sheet every time.

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

I couldn't find anything online about creating sheets with a loop in the pandas ExcelWriter, so I'm not entirely sure if it can be done the way I want it.

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

推荐答案

所有您需要做的就是将编写者的权限移到循环之外

All you need to do is move the writer decleration outside of the loop

例如

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

这篇关于使用Loop创建带有Dataframe Pandas的Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！