基于Pandas实现按日期分组数据的滚动均值计算

Alright, let's break down how to calculate rolling averages for your dataset grouped by date (and optionally other dimensions like ID or region) using Pandas. Here's a step-by-step guide tailored to your data:

Step 1: Prepare Your Data #

First, we'll load your data into a Pandas DataFrame and convert the BC_DT column to a proper datetime type—this is critical for any time-based rolling calculations, as Pandas needs to recognize the date order.

import pandas as pd

# Your dataset (filled in the missing values from your snippet)
data = {
    'ID': ['AA', 'BB', 'CC', 'DD', 'AA', 'BB', 'CC', 'DD', 'AA', 'BB', 'CC', 'DD', 'AA', 'BB'],
    'BC_DT': ['27-Mar-18', '27-Mar-18', '27-Mar-18', '27-Mar-18',
              '28-Mar-18', '28-Mar-18', '28-Mar-18', '28-Mar-18',
              '29-Mar-18', '29-Mar-18', '29-Mar-18', '29-Mar-18',
              '30-Mar-18', '30-Mar-18'],
    'BB_3M_DEFAULT_PROB': [0, 0.000002, 0.000003, 0.000006,
                           0, 0, 0.000005, 0.000004,
                           0.000002, 0.000002, 0.000002, 0.000005,
                           0.000002, None],
    'REGION': ['Chicago', 'Chicago', 'Chicago', 'Chicago',
               'Dallas', 'New York', 'Chicago', 'Kansas City',
               'Chicago', 'Chicago', 'Kansas City', 'Chicago',
               'Kansas City', None]
}

df = pd.DataFrame(data)

# Convert BC_DT to datetime format
df['BC_DT'] = pd.to_datetime(df['BC_DT'], format='%d-%b-%y')

Step 2: Rolling Averages per ID (Time-Series per Entity) #

If you want to compute a rolling average for each individual ID over time (e.g., the average default probability for each ID across the current and previous date), use this approach. We group by ID, sort by date, then apply a rolling window:

# Calculate rolling average (window size = 2, min 1 data point to compute)
df['ROLLING_AVG_BY_ID'] = df.groupby('ID').apply(
    lambda x: x.sort_values('BC_DT')['BB_3M_DEFAULT_PROB'].rolling(window=2, min_periods=1).mean()
).reset_index(level=0, drop=True)

• window=2: Adjust this to your desired rolling window size (e.g., 3 for a 3-day moving average)
• min_periods=1: Ensures we get a value even if there's only one data point (like the first date for an ID)

Step 3: Rolling Averages Across Aggregated Dates #

If you first want to get the average default probability per day, then compute a rolling average across those daily values (e.g., a 3-day moving average of daily average probabilities), use this:

# First, compute daily average default probability
daily_avg = df.groupby('BC_DT')['BB_3M_DEFAULT_PROB'].mean().reset_index()

# Now calculate rolling average over the daily values
daily_avg['ROLLING_AVG_BY_DATE'] = daily_avg['BB_3M_DEFAULT_PROB'].rolling(window=3, min_periods=1).mean()

Step 4: Rolling Averages by Region + Date #

If you need to segment by region first, then compute rolling averages over time for each region, here's how to do it:

# Compute average default probability per region and date
region_daily_avg = df.groupby(['REGION', 'BC_DT'])['BB_3M_DEFAULT_PROB'].mean().reset_index()

# Calculate rolling average for each region over time
region_daily_avg['ROLLING_AVG_BY_REGION_DATE'] = region_daily_avg.groupby('REGION').apply(
    lambda x: x.sort_values('BC_DT')['BB_3M_DEFAULT_PROB'].rolling(window=2, min_periods=1).mean()
).reset_index(level=0, drop=True)

Each of these approaches can be tweaked by adjusting the window parameter to match your desired rolling period, or adding/removing grouping columns to fit your specific analysis needs.

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