如何用Pandas基于多列与日期分组计算球员上次获胜间隔天数及胜率

一、数据预处理 #

首先需要将原始数据中的日期列转为datetime类型，胜负标记转为整数类型，方便后续计算：

import pandas as pd

# 加载用户提供的字典数据
data = {'Player 1': {0: 'Murray', 1: 'Nadal', 2: 'Murray', 3: 'Nadal', 4: 'Murray', 5: 'Nadal', 6: 'Murray', 7: 'Nadal', 8: 'Murray', 9: 'Nadal', 10: 'Murray'}, 'Player 2': {0: 'Nadal', 1: 'Murray', 2: 'Nadal', 3: 'Murray', 4: 'Nadal', 5: 'Murray', 6: 'Nadal', 7: 'Murray', 8: 'Nadal', 9: 'Murray', 10: 'Nadal'}, 'Date': {0: '2022-05-16', 1: '2022-05-26', 2: '2022-05-27', 3: '2022-05-28', 4: '2022-05-29', 5: '2022-06-01', 6: '2022-06-02', 7: '2022-06-05', 8: '2022-06-09', 9: '2022-06-13', 10: '2022-06-17'}, 'p1_win': {0: '1', 1: '1', 2: '0', 3: '1', 4: '0', 5: '0', 6: '1', 7: '0', 8: '1', 9: '0', 10: '1'}, 'p2_win': {0: '0', 1: '0', 2: '1', 3: '0', 4: '1', 5: '1', 6: '0', 7: '1', 8: '0', 9: '1', 10: '0'}}
df = pd.DataFrame(data)

# 类型转换
df['Date'] = pd.to_datetime(df['Date'])
df['p1_win'] = df['p1_win'].astype(int)
df['p2_win'] = df['p2_win'].astype(int)

二、计算每位球员距离上次获胜的间隔天数 #

核心思路是先收集所有球员的获胜日期，按球员分组排序后计算相邻获胜的间隔，再映射回原表的p1_lastwin和p2_lastwin字段：

1. 提取所有获胜记录：

# 提取Player1获胜的记录
p1_wins = df[df['p1_win'] == 1][['Player 1', 'Date']].rename(columns={'Player 1': 'Player'})
# 提取Player2获胜的记录
p2_wins = df[df['p2_win'] == 1][['Player 2', 'Date']].rename(columns={'Player 2': 'Player'})
# 合并所有获胜记录并按日期排序
all_wins = pd.concat([p1_wins, p2_wins]).sort_values(by=['Player', 'Date'])

2. 计算每位球员的上次获胜间隔：

# 按球员分组，计算当前获胜日期与上一次获胜日期的间隔
all_wins['last_win_days'] = all_wins.groupby('Player')['Date'].diff().dt.days
# 收集所有球员的所有参赛日期
all_player_dates = pd.concat([
    df[['Player 1', 'Date']].rename(columns={'Player 1': 'Player'}),
    df[['Player 2', 'Date']].rename(columns={'Player 2': 'Player'})
]).sort_values(by=['Player', 'Date']).drop_duplicates(subset=['Player', 'Date'])

# 合并获胜间隔信息，向前填充非获胜场次的间隔数据
player_win_history = pd.merge(
    all_player_dates,
    all_wins[['Player', 'Date', 'last_win_days']],
    on=['Player', 'Date'],
    how='left'
)
player_win_history['last_win_days'] = player_win_history.groupby('Player')['last_win_days'].ffill()
# 构建快速查找映射
win_interval_map = player_win_history.set_index(['Player', 'Date'])['last_win_days'].to_dict()

3. 映射回原DataFrame：

# 计算p1_lastwin和p2_lastwin
df['p1_lastwin'] = df.apply(
    lambda row: win_interval_map.get((row['Player 1'], row['Date']), pd.NA),
    axis=1
)
df['p2_lastwin'] = df.apply(
    lambda row: win_interval_map.get((row['Player 2'], row['Date']), pd.NA),
    axis=1
)

运行后，原DataFrame会新增符合预期的p1_lastwin和p2_lastwin列。

三、计算球员的年度胜率 #

核心思路是将每一场比赛拆分为两位球员的参赛记录，标记是否获胜，再按球员+年份分组计算胜率：

1. 拆分参赛记录：

# 拆分Player1的参赛记录
player1_records = df[['Player 1', 'Date', 'p1_win']].rename(
    columns={'Player 1': 'Player', 'p1_win': 'Win'}
)
# 拆分Player2的参赛记录
player2_records = df[['Player 2', 'Date', 'p2_win']].rename(
    columns={'Player 2': 'Player', 'p2_win': 'Win'}
)
# 合并所有参赛记录并提取年份
all_records = pd.concat([player1_records, player2_records])
all_records['Year'] = all_records['Date'].dt.year

2. 计算年度胜率：

# 按球员和年份分组统计
annual_win_rate = all_records.groupby(['Player', 'Year']).agg(
    Total_Matches=('Win', 'count'),
    Wins=('Win', 'sum')
).reset_index()
# 计算胜率并保留三位小数
annual_win_rate['Win_Rate'] = (annual_win_rate['Wins'] / annual_win_rate['Total_Matches']).round(3)

最终annual_win_rate表会包含每位球员每年的参赛数、胜场数和胜率。

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