如何用Pandas高效计算高尔夫球员过去n天平均Strokes Gained

Got it, let's solve this without slow loops—Pandas has built-in tools that make this way faster. Here's a step-by-step approach tailored to your needs:

Step 1: Prep Your Data First #

First, make sure your date column is in datetime format (critical for time-based rolling windows). Let's assume your date column is named event_date—replace this with your actual column name if different:

import pandas as pd

# Convert date column to datetime if it isn't already
df['event_date'] = pd.to_datetime(df['event_date'])

# Sort by player and date (rolling windows require ordered data—don't skip this!)
df = df.sort_values(['Player', 'event_date'])

Step 2: Compute the Rolling Average #

We'll use Pandas' groupby + rolling with a time window to calculate the average SG for each player over the last 100 days. This is fully vectorized, so it’s way faster than looping through rows manually.

Option 1: Include the current round's SG in the average

This calculates the average of all SG values (including the current row) from the past 100 days up to the current date:

# Calculate rolling 100-day average SG per player
df['Player\'s average SG in last 100 days'] = (
    df.groupby('Player')
      .apply(lambda group: group['SG'].rolling('100D', on='event_date').mean())
      .reset_index(level=0, drop=True)
)

Option 2: Exclude the current round's SG (only past data)

If you want the average of previous rounds within the last 100 days (not including the current one), add a shift(1) to exclude the current row's SG:

# Calculate rolling 100-day average SG (excluding current round)
df['Player\'s average SG in last 100 days'] = (
    df.groupby('Player')
      .apply(lambda group: group['SG'].shift(1).rolling('100D', on='event_date').mean())
      .reset_index(level=0, drop=True)
)

Key Tips for Performance & Accuracy #

• Sorting is non-negotiable: The rolling window relies on ordered dates per player, so skipping the sort_values step will lead to incorrect results.
• Adjust the window size: Replace '100D' with any time string you need (e.g., '30D' for 30 days, '7D' for a week).
• Handle missing data: If a player has no rounds in the last 100 days, the result will be NaN—you can fill this with fillna(0) or another value if needed.
• Optimize for large datasets: For extremely large DataFrames, normalize dates to midnight with dt.floor('D') (to eliminate irrelevant time components) and use closed='left' in rolling to explicitly exclude the current date's data:

  # Normalize dates to days and use left-closed window
  df['event_date'] = df['event_date'].dt.floor('D')
  df['Player\'s average SG in last 100 days'] = (
      df.groupby('Player')
        .apply(lambda group: group['SG'].rolling('100D', on='event_date', closed='left').mean())
        .reset_index(level=0, drop=True)
  )
  

This approach leverages Pandas' optimized C-backed operations instead of Python loops, so it’ll handle even large datasets efficiently.

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