优化Python网球赛事数据处理嵌套For循环，提升大数据运行效率

问题描述 #

作为Python初学者，我需要处理32000行的网球赛事数据库，新增三列数据：

• 选手1最近10场比赛的获胜次数
• 选手2最近10场比赛的获胜次数
• 选手1对阵选手2的胜率

原代码在小数据集上可正常输出结果，但处理大数据集时耗时极长（数小时甚至数天）。尝试用Lambda函数未得到正确结果，请求优化代码适配大数据集。

变量说明 #

Column 0 = Date（比赛日期）
Column 4 = ID Player 1（选手1ID）
Column 9 = ID Player 2（选手2ID）
Column 19 = 1 if P1 won the game / 0 if P2 won（选手1是否获胜）

原代码 #

def historique(df) :
  for i in range(0, len(df)-1) :
    date = pd.to_datetime(df.iloc[i, 0])
    J1 = df.iloc[i,4]
    J2 = df.iloc[i,9]  
    #Deleting games that occured after the one studied
    df_date = df.sort_values(by='Date', ascending=False)
    df_hist = df_date.loc[df_date['Date'] < date]

    
    totv1 = 0   #Nb of wins for player 1
    totv2 = 0   #Nb of wins for player 2
    totff = 0   #% of wins for p1 over p2
    count1 = 0
    count2 = 0
    countff = 0

    #First loop to create the column equals to totv1
    for j in range(0, len(df_hist)-1) :
      if ((df_hist.iloc[j,4] == J1) & (df_hist.iloc[j,19] == 1)) | (((df_hist.iloc[j,9] == J1) & (df_hist.iloc[j,19] == 0))) == True :
        totv1 += 1
        count1 += 1
        if count1 == 10 :
          break
      elif ((df_hist.iloc[j,4] == J1) & (df_hist.iloc[j,19] == 0)) | (((df_hist.iloc[j,9] == J1) & (df_hist.iloc[j,19] == 1))) == True :
        count1 += 1
        if count1 == 10 :
          break
      else :
        count1 += 0
    df.iloc[i,8] = totv1

    #Second loop to create the column equals to totv2
    for k in range(0, len(df_hist)-1) :
      if ((df_hist.iloc[k,4] == J2) & (df_hist.iloc[k,19] == 1)) | (((df_hist.iloc[k,9] == J2) & (df_hist.iloc[k,19] == 0))) == True :
        totv2 += 1
        count2 += 1
        if count2 == 10 :
          break
      elif ((df_hist.iloc[k,4] == J2) & (df_hist.iloc[k,19] == 0)) | (((df_hist.iloc[k,9] == J2) & (df_hist.iloc[k,19] == 1))) == True :
        count2 += 1
        if count2 == 10 :
          break
      else :
        count2 += 0
    df.iloc[i,13] = totv2

    #Third loop to create the column equals tot totff
    for l in range(0, len(df_hist)-1) :
      if ((df_hist.iloc[l,4] == J1) & (df_hist.iloc[l,9] == J2) & (df_hist.iloc[l,19] == 1)) | ((df_hist.iloc[l,4] == J2) & (df_hist.iloc[l,9] == J1) & (df_hist.iloc[l,19] == 0)) == True :
        totff += 1
        countff += 1
        if countff == 10 :
          break
      elif ((df_hist.iloc[l,4] == J2) & (df_hist.iloc[l,19] == 0)) | (((df_hist.iloc[l,9] == J2) & (df_hist.iloc[l,19] == 1))) == True :
        countff += 1
        if countff == 10 :
          break
      else :
        countff += 0
    if countff != 0 :
      df.iloc[i,14] = round((totff/countff), 2)
    else :
      df.iloc[i,14] = 0

  return df

原代码问题分析 #

1. 嵌套循环效率极低：外层遍历每一行，内层三次循环分别统计，时间复杂度为O(n²)，32000行数据会产生近10亿次循环操作
2. 重复计算：每次循环都重新排序整个数据集并筛选历史数据，完全重复劳动
3. 逐元素访问慢：使用iloc逐个访问DataFrame元素，远慢于向量化操作

优化方案及代码 #

利用Pandas的向量化操作、分组滚动窗口替代嵌套循环，大幅提升效率，处理32000行数据仅需数秒至数分钟。

步骤1：预处理数据 #

import pandas as pd

# 重命名列，方便后续操作
df.rename(columns={
    0: 'Date',
    4: 'P1_ID',
    9: 'P2_ID',
    19: 'P1_Won'
}, inplace=True)

# 转换日期格式并按日期排序（仅需执行一次）
df['Date'] = pd.to_datetime(df['Date'])
df = df.sort_values('Date').reset_index(drop=True)

步骤2：计算选手最近10场胜场数 #

将所有选手的参赛记录（无论作为P1还是P2）合并，用滚动窗口统计最近10场胜场：

# 整理选手所有参赛记录
# 作为P1的记录
p1_records = df[['Date', 'P1_ID', 'P1_Won']].rename(columns={'P1_ID': 'Player_ID', 'P1_Won': 'Player_Won'})
# 作为P2的记录，P2获胜时Player_Won为1 - P1_Won
p2_records = df[['Date', 'P2_ID', 'P1_Won']].rename(columns={'P2_ID': 'Player_ID'})
p2_records['Player_Won'] = 1 - p2_records['P1_Won']

# 合并所有记录并按选手、日期排序
all_player_records = pd.concat([p1_records, p2_records]).sort_values(['Player_ID', 'Date'])

# 滚动窗口统计每个选手最近10场（当前比赛之前）的胜场数
all_player_records['Last_10_Wins'] = all_player_records.groupby('Player_ID')['Player_Won'] \
    .rolling(10, closed='left').sum().reset_index(level=0, drop=True)

# 将结果映射回原数据集
df = df.merge(all_player_records[['Date', 'Player_ID', 'Last_10_Wins']],
              left_on=['Date', 'P1_ID'], right_on=['Date', 'Player_ID'],
              how='left').rename(columns={'Last_10_Wins': 'P1_Last10_Wins'}).drop('Player_ID', axis=1)

df = df.merge(all_player_records[['Date', 'Player_ID', 'Last_10_Wins']],
              left_on=['Date', 'P2_ID'], right_on=['Date', 'Player_ID'],
              how='left').rename(columns={'Last_10_Wins': 'P2_Last10_Wins'}).drop('Player_ID', axis=1)

步骤3：计算选手1对阵选手2的胜率 #

对每一对选手的交手记录分组，用滚动窗口统计最近10次交手中选手1的胜率：

# 创建交手对的唯一标识（按ID排序，避免(P1,P2)和(P2,P1)重复分组）
df['Matchup_Key'] = df.apply(lambda x: tuple(sorted([x['P1_ID'], x['P2_ID']])), axis=1)
# 标记当前比赛中P1是交手对中的第一个选手还是第二个
df['P1_Is_First'] = df.apply(lambda x: x['P1_ID'] == x['Matchup_Key'][0], axis=1)

# 整理交手记录，标记每场比赛中P1的胜场情况
matchup_records = df[['Date', 'Matchup_Key', 'P1_Is_First', 'P1_Won']].sort_values(['Matchup_Key', 'Date'])
matchup_records['P1_Hist_Win'] = matchup_records.apply(
    lambda x: x['P1_Won'] if x['P1_Is_First'] else 1 - x['P1_Won'],
    axis=1
)

# 滚动窗口统计最近10次交手的胜场数和总次数
matchup_records['Last10_Matchup_Wins'] = matchup_records.groupby('Matchup_Key')['P1_Hist_Win'] \
    .rolling(10, closed='left').sum().reset_index(level=0, drop=True)
matchup_records['Last10_Matchup_Total'] = matchup_records.groupby('Matchup_Key')['P1_Hist_Win'] \
    .rolling(10, closed='left').count().reset_index(level=0, drop=True)

# 计算胜率，无交手记录时设为0
matchup_records['P1_vs_P2_WinRate'] = matchup_records.apply(
    lambda x: round(x['Last10_Matchup_Wins'] / x['Last10_Matchup_Total'], 2) if x['Last10_Matchup_Total'] > 0 else 0,
    axis=1
)

# 将胜率映射回原数据集并清理临时列
df = df.merge(matchup_records[['Date', 'Matchup_Key', 'P1_Is_First', 'P1_vs_P2_WinRate']],
              on=['Date', 'Matchup_Key', 'P1_Is_First'], how='left')
df.drop(['Matchup_Key', 'P1_Is_First'], axis=1, inplace=True)

优化效果说明 #

• 完全消除嵌套循环，时间复杂度降至O(n log n)，处理32000行数据效率提升数百倍
• 所有操作均为Pandas内置的向量化/分组操作，避免逐元素访问的开销
• 仅需一次排序和预处理，无重复计算

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