多赛季足球数据处理：按赛季重置球队累计比赛次数异常排查

我有一个可处理单赛季英超数据的Python脚本，能计算每支球队的累计参赛次数并生成Key。现在要扩展至多赛季数据，要求每个赛季的累计次数从0开始，但循环处理时，后续赛季的Match_Home和Match_Away字段值全为38（正确应为从0递增至37），拆分数据集后问题仍存在。

原单赛季脚本：

import pandas as pd

data_path = r'G:\SOCCER\PremierLeague_2021.xlsx'
data = pd.read_excel(data_path)

data['Match_Home'] = data.apply(lambda row: (data['Home'][:row.name] == row['Home']).sum() + (data['Away'][:row.name] == row['Home']).sum(), axis=1)
data['Match_Away'] = data.apply(lambda row: (data['Home'][:row.name] == row['Away']).sum() + (data['Away'][:row.name] == row['Away']).sum(), axis=1)

data['Key'] = data['Home'] + data['Match_Home'].astype(str) + data['Away'] + data['Match_Away'].astype(str)

output_path = r'G:\SOCCER\PremierLeague_2021.xlsx'
data.to_excel(output_path, index=False)

尝试的多赛季脚本：

import pandas as pd

data_path = r'G:\SOCCER\PremierLeague_AllSeasons.xlsx'
data = pd.read_excel(data_path)


databases = {}
for season in data['Season'].unique():
  
  databases[season] = data[data['Season'] == season].copy()

for season, database in databases.items():
    database['Match_Home'] = database.apply(lambda row: (database['Home'][:row.name] == row['Home']).sum() + (database['Away'][:row.name] == row['Home']).sum(), axis=1)
    database['Match_Away'] = database.apply(lambda row: (database['Home'][:row.name] == row['Away']).sum() + (database['Away'][:row.name] == row['Away']).sum(), axis=1)
  

for season, database in databases.items():
  output_path = r'G:\SOCCER\PremierLeague_{}.xlsx'.format(season)
  database.to_excel(output_path, index=False)

拆分后的赛季子DataFrame保留了原大表的索引，而apply中使用的row.name是原表的索引标签，不是子DataFrame内的相对行位置。例如，第二个赛季的行索引可能从38开始，此时database['Home'][:row.name]会尝试取子DataFrame中标签小于38的行，但子DataFrame内的所有行标签都≥38，导致切片实际包含了子DataFrame的全部行，最终求和结果为38（每支球队单赛季参赛总场次），而非从0开始的递增计数。

对每个赛季的子DataFrame重置索引，使其内部索引从0开始连续递增，这样row.name就能正确对应子DataFrame内的行位置，实现从0开始的累计计数。

修正后的基础版本 #

import pandas as pd

data_path = r'G:\SOCCER\PremierLeague_AllSeasons.xlsx'
data = pd.read_excel(data_path)

databases = {}
for season in data['Season'].unique():
    # 拆分赛季数据并重置索引，drop=True避免保留原索引列
    databases[season] = data[data['Season'] == season].copy().reset_index(drop=True)

for season, database in databases.items():
    # 现在row.name是子DataFrame内的相对位置，从0开始
    database['Match_Home'] = database.apply(
        lambda row: (database['Home'][:row.name] == row['Home']).sum() + 
                    (database['Away'][:row.name] == row['Home']).sum(), 
        axis=1
    )
    database['Match_Away'] = database.apply(
        lambda row: (database['Home'][:row.name] == row['Away']).sum() + 
                    (database['Away'][:row.name] == row['Away']).sum(), 
        axis=1
    )
    # 生成Key字段
    database['Key'] = database['Home'] + database['Match_Home'].astype(str) + database['Away'] + database['Match_Away'].astype(str)

for season, database in databases.items():
    output_path = r'G:\SOCCER\PremierLeague_{}.xlsx'.format(season)
    database.to_excel(output_path, index=False)

高效优化版本（推荐） #

使用groupby和cumcount替代apply，大幅提升大数据集的处理速度：

import pandas as pd

data_path = r'G:\SOCCER\PremierLeague_AllSeasons.xlsx'
data = pd.read_excel(data_path)

# 定义赛季处理函数
def process_season(group):
    # 重置组内索引，确保行位置从0开始
    group = group.reset_index(drop=True)
    # 计算每支球队的主场、客场累计场次（仅统计当前类型的场次）
    group['home_cum'] = group.groupby('Home').cumcount()
    group['away_cum'] = group.groupby('Away').cumcount()
    # 计算球队作为对手在对方主场的累计场次，shift(1)排除当前行
    group['home_opponent_cum'] = group.groupby('Home')['Away'].transform(
        lambda x: (x == x.name).cumsum().shift(fill_value=0)
    )
    group['away_opponent_cum'] = group.groupby('Away')['Home'].transform(
        lambda x: (x == x.name).cumsum().shift(fill_value=0)
    )
    # 总累计参赛场次 = 自身主场/客场场次 + 作为对手的场次
    group['Match_Home'] = group['home_cum'] + group['home_opponent_cum']
    group['Match_Away'] = group['away_cum'] + group['away_opponent_cum']
    # 生成Key
    group['Key'] = group['Home'] + group['Match_Home'].astype(str) + group['Away'] + group['Match_Away'].astype(str)
    return group

# 按赛季分组处理数据
processed_data = data.groupby('Season', group_keys=False).apply(process_season)

# 按赛季导出结果
for season in processed_data['Season'].unique():
    season_data = processed_data[processed_data['Season'] == season]
    output_path = r'G:\SOCCER\PremierLeague_{}.xlsx'.format(season)
    season_data.to_excel(output_path, index=False)

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