Pandas如何按条件计算DataFrame列最近5局的得分与出局数累计值

核心逻辑前提 #

所有统计以单场比赛的单局为独立维度，计算前需保证数据集按season、match_id、inning、over/ball升序排列，确保每一球的时序正确。

实现步骤 #

步骤1：拆分轮次与球数 #

把over/ball字段拆为独立的轮次（over）、球数（ball）字段，方便后续匹配：

import pandas as pd

# 拆分over/ball字段
df[['over', 'ball']] = df['over/ball'].astype(str).str.split('.', expand=True).astype(int)
# 按比赛、局、轮次、球数排序，保证时序正确
df = df.sort_values(['season', 'match_id', 'inning', 'over', 'ball']).reset_index(drop=True)

步骤2：分组计算近5轮统计值 #

利用累计得分/出局数的差值计算近5轮结果，不足5轮时从本局第一球开始统计：

def calc_last_5_stats(group):
    for idx, row in group.iterrows():
        current_over = row['over']
        current_ball = row['ball']
        start_over = current_over - 5
        
        # 不足5轮的场景，初始累计值取0
        if start_over <= 0:
            prev_runs = 0
            prev_wickets = 0
        else:
            # 优先匹配对应轮次+相同球数的累计值
            match_row = group[(group['over'] == start_over) & (group['ball'] == current_ball)]
            if not match_row.empty:
                prev_runs = match_row['sum_total_runs'].iloc[0]
                prev_wickets = match_row['sum_total_wickets'].iloc[0]
            else:
                # 对应轮次无相同球数时，取该轮最后一球的累计值
                prev_row = group[group['over'] == start_over].iloc[-1]
                prev_runs = prev_row['sum_total_runs']
                prev_wickets = prev_row['sum_total_wickets']
        
        # 差值即为近5轮统计结果
        group.at[idx, 'runs_last_5'] = row['sum_total_runs'] - prev_runs
        group.at[idx, 'wickets_last_5'] = row['sum_total_wickets'] - prev_wickets
    return group

# 按比赛+局分组应用计算逻辑
df = df.groupby(['season', 'match_id', 'inning'], group_keys=False).apply(calc_last_5_stats)

结果验证 #

和需求示例逻辑完全匹配：

• 当前over/ball为8.1时，start_over=3，取3.1的累计值做差值，得到3.1~8.1的总得分/出局数
• 当前over/ball为11.3时，start_over=6，取6.3的累计值做差值，得到6.3~11.3的总得分/出局数

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