基于Pandas实现按球队ID跨主客场的累计得分计算

问题背景 #

处理赛事数据集时，需要按球队ID计算跨主客场的累计得分：无论球队以主场还是客场身份参赛，都要把所有场次的得分累加，得到截至当前赛事的总累计分。现有代码仅能分别统计主场、客场的单独累计，无法实现跨场景的总累计。

现有代码片段：

import pandas as pd

game_data = pd.read_csv('game_data.csv')

# 主客场单独的滚动平均
game_data['home_avg_home_games'] = game_data.groupby('home_id')['home_score'].transform(lambda x: x.rolling(165, min_periods = 0).mean())
game_data['home_avg_against_home_games'] = game_data.groupby('home_id')['away_score'].transform(lambda x: x.rolling(165, min_periods = 0).mean())

game_data['away_avg_away_games'] = game_data.groupby('away_id')['away_score'].transform(lambda x: x.rolling(165, min_periods = 0).mean())
game_data['away_avg_against_away_games'] = game_data.groupby('away_id')['home_score'].transform(lambda x: x.rolling(165, min_periods = 0).mean())

# 主客场单独的累计得分
game_data['scored_home_total'] = game_data.groupby('home_id')['home_score'].cumsum()
game_data['scored_away_total'] = game_data.groupby('away_id')['away_score'].cumsum()

数据集示例：

期望新增home_total_score（当前主场球队的累计总得分）和away_total_score（当前客场球队的累计总得分）列，示例输出：

解决方案 #

核心思路是先把主客场的得分记录统一整理成「球队ID-得分-比赛顺序」的格式，计算累计后再映射回原数据集。步骤如下：

1. 确保数据按比赛时间/顺序排序 #

累计得分依赖比赛的先后顺序，先按game_id或比赛日期对原数据排序：

# 假设game_id按比赛时间递增，若有日期列请替换为日期列
game_data = game_data.sort_values('game_id').reset_index(drop=True)

2. 拆分主客场记录为统一格式 #

把每场比赛拆成两条记录：一条对应主队的得分，一条对应客队的得分，包含球队ID、得分、原数据的索引（用于后续映射）：

# 主队记录
home_records = game_data[['home_id', 'home_score']].rename(columns={'home_id': 'team_id', 'home_score': 'score'})
home_records['original_idx'] = game_data.index

# 客队记录
away_records = game_data[['away_id', 'away_score']].rename(columns={'away_id': 'team_id', 'away_score': 'score'})
away_records['original_idx'] = game_data.index

# 合并所有记录
all_team_records = pd.concat([home_records, away_records], ignore_index=True)

3. 计算每个球队的累计总得分 #

按球队ID分组，计算累计得分，同时保留原数据的索引：

# 按球队ID和原索引排序，确保累计顺序正确
all_team_records = all_team_records.sort_values(['team_id', 'original_idx'])
# 计算累计得分
all_team_records['total_score'] = all_team_records.groupby('team_id')['score'].cumsum()

4. 将累计得分映射回原数据集 #

分别提取主、客队的累计得分，合并到原数据中：

# 提取主队的累计得分：筛选原记录为主队的行，按原索引匹配
home_total = all_team_records[all_team_records['team_id'].isin(game_data['home_id'])].drop_duplicates('original_idx', keep='last')
game_data['home_total_score'] = game_data.index.map(home_total.set_index('original_idx')['total_score'])

# 提取客队的累计得分：筛选原记录为客队的行，按原索引匹配
away_total = all_team_records[all_team_records['team_id'].isin(game_data['away_id'])].drop_duplicates('original_idx', keep='last')
game_data['away_total_score'] = game_data.index.map(away_total.set_index('original_idx')['total_score'])

完整代码 #

import pandas as pd

# 读取数据
game_data = pd.read_csv('game_data.csv')

# 1. 按比赛顺序排序（关键：累计得分依赖比赛先后）
game_data = game_data.sort_values('game_id').reset_index(drop=True)

# 2. 拆分主客场记录
home_records = game_data[['home_id', 'home_score']].rename(columns={'home_id': 'team_id', 'home_score': 'score'})
home_records['original_idx'] = game_data.index

away_records = game_data[['away_id', 'away_score']].rename(columns={'away_id': 'team_id', 'away_score': 'score'})
away_records['original_idx'] = game_data.index

all_team_records = pd.concat([home_records, away_records], ignore_index=True)

# 3. 计算跨主客场的累计得分
all_team_records = all_team_records.sort_values(['team_id', 'original_idx'])
all_team_records['total_score'] = all_team_records.groupby('team_id')['score'].cumsum()

# 4. 映射回原数据集
# 主队累计得分
home_total = all_team_records[all_team_records['team_id'].isin(game_data['home_id'])].drop_duplicates('original_idx', keep='last')
game_data['home_total_score'] = game_data.index.map(home_total.set_index('original_idx')['total_score'])

# 客队累计得分
away_total = all_team_records[all_team_records['team_id'].isin(game_data['away_id'])].drop_duplicates('original_idx', keep='last')
game_data['away_total_score'] = game_data.index.map(away_total.set_index('original_idx')['total_score'])

# 保留原有计算列（如果需要）
# 主客场滚动平均计算
game_data['home_avg_home_games'] = game_data.groupby('home_id')['home_score'].transform(lambda x: x.rolling(165, min_periods = 0).mean())
game_data['home_avg_against_home_games'] = game_data.groupby('home_id')['away_score'].transform(lambda x: x.rolling(165, min_periods = 0).mean())

game_data['away_avg_away_games'] = game_data.groupby('away_id')['away_score'].transform(lambda x: x.rolling(165, min_periods = 0).mean())
game_data['away_avg_against_away_games'] = game_data.groupby('away_id')['home_score'].transform(lambda x: x.rolling(165, min_periods = 0).mean())

game_data['scored_home_total'] = game_data.groupby('home_id')['home_score'].cumsum()
game_data['scored_away_total'] = game_data.groupby('away_id')['away_score'].cumsum()

说明 #

• 排序步骤是核心：必须保证数据按实际比赛的时间顺序排列，否则累计得分会出错；如果数据集有game_date字段，建议替换game_id为game_date进行排序。
• 拆分记录时保留原索引，是为了准确将累计得分映射回原数据的对应行。

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