如何用类SQL操作高效还原Pandas熔解数据集并保留新增列？

我完全懂你现在的痛点——单球队循环筛选拼接的方式，在数据集规模变大后效率会暴跌。其实用Pandas的merge操作（对应SQL里的JOIN逻辑）就能完美解决这个问题，而且是底层优化后的高效实现，比手动循环靠谱多了。

核心思路 #

把熔解后的数据集和自身做自连接，匹配逻辑是：

• 同一场比赛的日期完全一致
• A球队的对手是B球队，同时B球队的对手是A球队

这样就能把主客两队的累积统计列一次性拼到同一条记录里，不用再逐个球队处理。

具体实现步骤 #

先拿你提供的示例数据演示：

1. 准备示例熔解数据集

import pandas as pd

# 构造带累积统计的熔解后数据集
data = {
    'Date': ['2017-08-11', '2017-08-11', '2017-08-12', '2017-08-12', '2017-08-12', '2017-08-12'],
    'Home/Away': [1, 0, 1, 0, 1, 0],
    'Team': ['Arsenal', 'Leicester', 'Brighton', 'Man City', 'Chelsea', 'Burnley'],
    'Opponent': ['Leicester', 'Arsenal', 'Man City', 'Brighton', 'Burnley', 'Chelsea'],
    'Cumg': [0.0, 0.0, 0.0, 0.0, 0.0, 0.0],
    'Cumc': [0.0, 0.0, 0.0, 0.0, 0.0, 0.0],
    'Result': [1, 1, 0, 0, 0, 0]
}
df = pd.DataFrame(data)

2. 执行自连接（类SQL JOIN）

# 自连接匹配同一场比赛的主客两队数据
merged_df = pd.merge(
    df,
    df,
    left_on=['Date', 'Team'],
    right_on=['Date', 'Opponent'],
    suffixes=('_team', '_opponent')  # 区分自身和对手的字段
)

# 过滤掉错误匹配的记录（确保A的对手是B时，B的对手也必须是A）
final_df = merged_df[merged_df['Team_team'] == merged_df['Opponent_opponent']].copy()

# 整理列顺序并重命名，贴合你需要的格式
final_df = final_df[[
    'Date',
    'Home/Away_team',
    'Team_team',
    'Opponent_team',
    'Cumg_team',
    'Cumc_team',
    'Result_team',
    'Cumg_opponent',
    'Cumc_opponent',
    'Result_opponent'
]]

final_df.rename(columns={
    'Home/Away_team': 'Home/Away',
    'Team_team': 'Team',
    'Opponent_team': 'Opponent',
    'Result_team': 'Result'
}, inplace=True)

3. 最终结果示例

为什么比手动筛选高效？ #

• 手动循环每个球队的时间复杂度是O(n*k)（n是球队数，k是单球队比赛数），而merge用的是Pandas底层的哈希匹配逻辑，时间复杂度接近O(n)，数据集越大，效率差距越明显。
• 可以提前给Date、Team、Opponent列加索引，进一步加速连接：

  df['Date'] = pd.to_datetime(df['Date'])  # 先转成datetime类型，避免字符串匹配的坑
  df = df.set_index(['Date', 'Team', 'Opponent'])
  

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