如何用Pandas高效计算分组累计胜负次数及衍生特征？

Core: Cumulative Wins for A/B Columns #

To compute cumulative wins for players in columns A and B up to the current row (excluding the current game), use dummy variables and cumulative sums, then map values back with vectorized lookup:

import pandas as pd

# Original DataFrame
df = pd.DataFrame({'A': ['Nadal', 'Federer', 'Djokovic', 'Nadal', 'Nadal', 'Murray', 'Nadal'], 
                   'B': ['Djokovic', 'Nadal', 'Murray', 'Murray', 'Djokovic', 'Federer', 'Murray'],
                   'Winner': ['Nadal', 'Federer', 'Djokovic', 'Murray', 'Nadal', 'Federer', 'Murray'],
                   'Loser': ['Djokovic', 'Nadal', 'Murray', 'Nadal', 'Djokovic', 'Murray', 'Nadal']})

# Step 1: Generate cumulative wins up to the previous row for all players
win_dummies = pd.get_dummies(df['Winner'])
cumulative_wins = win_dummies.cumsum().shift(fill_value=0)  # Shift to exclude current game

# Step 2: Map cumulative wins to A and B columns
df['count_of_wins_A'] = cumulative_wins.lookup(df.index, df['A'])
df['count_of_wins_B'] = cumulative_wins.lookup(df.index, df['B'])

This produces the exact count_of_wins_A and count_of_wins_B columns from your expected output, with lookup being far faster than row-wise iteration.

Extend to Cumulative Losses #

Repeat the pattern with the Loser column to track cumulative losses:

# Generate cumulative losses up to the previous row
loss_dummies = pd.get_dummies(df['Loser'])
cumulative_losses = loss_dummies.cumsum().shift(fill_value=0)

# Map to A and B columns
df['count_of_losses_A'] = cumulative_losses.lookup(df.index, df['A'])
df['count_of_losses_B'] = cumulative_losses.lookup(df.index, df['B'])

Compute Overall Win Rate #

Calculate win rate as (cumulative wins) / (total games played), handling division by zero for players with no prior games:

# Win rate for A
df['win_rate_A'] = df['count_of_wins_A'] / (df['count_of_wins_A'] + df['count_of_losses_A'])
df['win_rate_A'] = df['win_rate_A'].fillna(0)

# Win rate for B
df['win_rate_B'] = df['count_of_wins_B'] / (df['count_of_wins_B'] + df['count_of_losses_B'])
df['win_rate_B'] = df['win_rate_B'].fillna(0)

Recent N-Game Win Rate #

To compute win rate over the last N games (e.g., N=2) before the current match, use a groupby-rolling approach to track each player's recent results:

def add_recent_win_rate(df, n=2):
    # Melt data to track each player's result per game
    player_results = pd.melt(df.reset_index(), id_vars='index', 
                             value_vars=['Winner', 'Loser'], 
                             var_name='Result', value_name='Player')
    player_results['Win'] = player_results['Result'].map({'Winner': 1, 'Loser': 0})
    
    # Compute rolling win rate for each player (exclude current game)
    rolling_stats = player_results.groupby('Player')['Win'].apply(
        lambda x: x.shift(1).rolling(window=n, min_periods=1).mean()
    ).reset_index(name=f'recent_{n}_win_rate')
    
    # Merge back to original DataFrame for A and B columns
    df = df.merge(rolling_stats.rename(columns={'Player': 'A', f'recent_{n}_win_rate': f'recent_{n}_win_rate_A'}),
                  left_index=True, right_on='index', how='left')
    df = df.merge(rolling_stats.rename(columns={'Player': 'B', f'recent_{n}_win_rate': f'recent_{n}_win_rate_B'}),
                  left_index=True, right_on='index', how='left')
    
    # Clean up extra columns
    df = df.drop(columns=['index_x', 'index_y'])
    return df

# Add recent 2-game win rate
df = add_recent_win_rate(df, n=2)

Key Advantages #

• Efficiency: Uses pandas' optimized vectorized operations (cumsum, lookup, rolling) for fast performance on large datasets.
• Maintainability: Avoids messy dictionary loops; each feature follows a consistent, easy-to-modify pattern.
• Scalability: Adding new features (e.g., recent loss rates, head-to-head stats) follows the same template, reducing code duplication.

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