如何用Pandas去除重复赛事对阵并转换为宽表格式

核心思路 #

同一轮次中，A队vsB队和B队vsA队属于同一场赛事，我们先给每场赛事生成唯一标识，再将两队的xG数据分别映射为xG_team和xG_adversary，最终合并去重得到目标格式。

方法一：分步合并法 #

import pandas as pd

# 假设原始数据存储在df中
df = pd.DataFrame({
    'team_id': [262, 263, 245, 254],
    'adversary_id': [263, 262, 254, 245],
    'round_id': [1, 1, 1, 1],
    'xG': [0.45, 0.34, 0.67, 0.15]
})

# 1. 生成唯一赛事ID：排序两队ID+轮次，确保A-B和B-A对应同一ID
df['match_id'] = df.apply(
    lambda row: '_'.join(sorted([str(row['team_id']), str(row['adversary_id'])]) + [str(row['round_id'])]),
    axis=1
)

# 2. 拆分球队自身xG和对手xG
team_xg = df.rename(columns={'xG': 'xG_team'})[['team_id', 'adversary_id', 'round_id', 'match_id', 'xG_team']]
adversary_xg = df.rename(
    columns={'team_id': 'adversary_id', 'adversary_id': 'team_id', 'xG': 'xG_adversary'}
)[['team_id', 'adversary_id', 'round_id', 'match_id', 'xG_adversary']]

# 3. 合并并去重
result = pd.merge(team_xg, adversary_xg, on=['team_id', 'adversary_id', 'round_id', 'match_id'])
result = result[['team_id', 'adversary_id', 'round_id', 'xG_team', 'xG_adversary']].drop_duplicates()

方法二：分组聚合法（更简洁） #

import pandas as pd

# 原始数据同上
df = pd.DataFrame({
    'team_id': [262, 263, 245, 254],
    'adversary_id': [263, 262, 254, 245],
    'round_id': [1, 1, 1, 1],
    'xG': [0.45, 0.34, 0.67, 0.15]
})

# 生成唯一赛事ID
df['match_id'] = df.apply(
    lambda row: '_'.join(sorted([str(row['team_id']), str(row['adversary_id'])]) + [str(row['round_id'])]),
    axis=1
)

# 按赛事ID分组，提取每队的xG数据
def process_single_match(group):
    # 取每组第一条记录的对阵关系作为基准
    base_team = group.iloc[0]['team_id']
    base_adv = group.iloc[0]['adversary_id']
    return pd.Series({
        'team_id': base_team,
        'adversary_id': base_adv,
        'round_id': group.iloc[0]['round_id'],
        'xG_team': group[group['team_id'] == base_team]['xG'].values[0],
        'xG_adversary': group[group['team_id'] == base_adv]['xG'].values[0]
    })

# 生成结果表
result = df.groupby('match_id').apply(process_single_match).reset_index(drop=True)

最终效果 #

执行上述代码后，result就是你需要的宽表格式：

team_id  adversary_id  round_id  xG_team  xG_adversary
0      262           263         1     0.45          0.34
1      245           254         1     0.67          0.15

内容的提问来源于stack exchange，提问作者8-Bit Borges