如何修改NBA进阶数据爬虫代码，将多赛季数据存入单个Excel文件？

我正在制作NBA球员赛季进阶数据分析用的DataFrame，目前已实现按赛季自动生成DataFrame并保存为单个赛季的Excel文件（如data_2001.xlsx、data_2002.xlsx……data_2022.xlsx），但希望通过for循环将多赛季数据合并保存到一个Excel文件（如data_2001~2022.xlsx）。

现有代码会生成每个赛季单独的Excel文件，请问该如何调整代码实现合并保存？

原代码如下：

years = [2001,2002]

for y in years:
    # Season
    url = 'https://www.basketball-reference.com/leagues/NBA_{}_advanced.html'.format(y)
    html = urlopen(url)
    soup = BeautifulSoup(html)

    soup.findAll('tr',limit=2)
    # use getText()to extract the text we need into a list
    headers = [th.getText() for th in soup.findAll('tr',limit=2)[0].findAll('th')]
    # exclude the first column as we will not need the ranking order from Basketball Reference for the analysis
    headers = headers[1:]
    
    rows = soup.findAll('tr')[1:]
    player_stats = [[td.getText() for td in rows[i].findAll('td')] for i in range (len(rows))]
    stats = pd.DataFrame(player_stats, columns = headers)


    
    stats['Year'] = y             #Added column ['Year'] to recognize season in the dataframe  
    stats['Season_type'] = 'RS'.  #Added column ['Season_type'] to recognize season type in the dataframe  
    stats = stats.apply(pd.to_numeric,errors='ignore') # changed object data types to float to maniupulate data
    stats = stats[stats['G']>=57]                #Only players who played more than 70% of games
    stats = stats.drop(stats.columns[[18,23]],axis=1) # drop NAN columns

  
    print(f'Finished scraping data for the {y}.')
    lag = np.random.uniform(low=5,high=10)
    print(f'...waiting {round(lag,1)} seconds')
    time.sleep(lag)
    
    y_str = str(y)
    stats.to_excel('data_'+y_str+'.xlsx', index=False). # save as xlsx file

只需要做以下几个修改，就能实现多赛季数据合并保存：

• 初始化一个空列表，用来存储每个赛季处理后的DataFrame
• 循环内部不再单独保存Excel，而是将处理好的stats追加到列表中
• 循环结束后，用pd.concat()合并所有DataFrame
• 最后将合并后的DataFrame保存为单个Excel文件

同时需要修正原代码中的两处语法错误：

• stats['Season_type'] = 'RS'. 末尾多了一个英文句号，改为stats['Season_type'] = 'RS'
• stats.to_excel('data_'+y_str+'.xlsx', index=False). 末尾多了一个英文句号，直接移除这行代码

修改后的完整代码：

import pandas as pd
from bs4 import BeautifulSoup
from urllib.request import urlopen
import numpy as np
import time

years = [2001,2002]
# 初始化空列表存储各赛季数据
all_season_stats = []

for y in years:
    # Season
    url = 'https://www.basketball-reference.com/leagues/NBA_{}_advanced.html'.format(y)
    html = urlopen(url)
    soup = BeautifulSoup(html)

    soup.findAll('tr',limit=2)
    # use getText()to extract the text we need into a list
    headers = [th.getText() for th in soup.findAll('tr',limit=2)[0].findAll('th')]
    # exclude the first column as we will not need the ranking order from Basketball Reference for the analysis
    headers = headers[1:]
    
    rows = soup.findAll('tr')[1:]
    player_stats = [[td.getText() for td in rows[i].findAll('td')] for i in range (len(rows))]
    stats = pd.DataFrame(player_stats, columns = headers)


    
    stats['Year'] = y             #Added column ['Year'] to recognize season in the dataframe  
    stats['Season_type'] = 'RS'  # 修正语法错误：移除末尾的句号
    stats = stats.apply(pd.to_numeric,errors='ignore') # changed object data types to float to maniupulate data
    stats = stats[stats['G']>=57]                #Only players who played more than 70% of games
    stats = stats.drop(stats.columns[[18,23]],axis=1) # drop NAN columns

  
    print(f'Finished scraping data for the {y}.')
    lag = np.random.uniform(low=5,high=10)
    print(f'...waiting {round(lag,1)} seconds')
    time.sleep(lag)
    
    # 将当前赛季数据添加到列表
    all_season_stats.append(stats)

# 合并所有赛季数据
combined_stats = pd.concat(all_season_stats, ignore_index=True)
# 保存为单个Excel文件
combined_stats.to_excel('data_2001~2002.xlsx', index=False)
print('All seasons data saved to single Excel file.')

内容的提问来源于stack exchange，提问作者MinChocoCake_01