Skip to main content
 首页 » 编程设计

python之使用循环创建带有 Dataframe Pandas 的 Excel 工作表

2025年02月15日13mengfanrong

我正在研究这个功能,它可以抓取网站上的梦幻足球信息并将其写入 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 声明移出循环

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