基于X3、X4动态条件的Y列近5日滚动均值计算求助

Got it, let's tackle this problem step by step. The core requirement here is to compute a rolling 5-day mean of Y for each row, but only considering rows that share the same X3 and X4 values and fall within the 5-day window before the current row's date. Here's how to do it properly:

Step 1: Prepare the Data #

First, we need to convert the date column (X2) to a proper datetime format—this is critical for calculating date ranges accurately. We'll also sort the data to ensure each group's rows are in chronological order.

import pandas as pd
import numpy as np

# Sample DataFrame (replace with your actual data)
data = {
    'X2': ['01.02.2019', '02.02.2019', '02.02.2019', '02.02.2019', '03.02.2019',
           '04.02.2019', '05.02.2019', '06.02.2019', '07.02.2019', '08.02.2019',
           '09.02.2019', '10.02.2019', '11.02.2019', '12.02.2019', '13.02.2019',
           '14.02.2019', '15.02.2019', '16.02.2019', '17.02.2019', '18.02.2019'],
    'X3': [1,2,2,2,1,2,1,2,1,2,1,2,1,2,1,2,2,2,1,2],
    'X4': [1,2,3,1,2,3,1,2,3,1,2,3,3,2,3,1,2,3,1,2],
    'Y': [1,0,0,1,1,0,1,0,1,1,0,1,0,1,0,1,1,0,1,0]
}
df = pd.DataFrame(data)

# Convert date column to datetime
df['X2'] = pd.to_datetime(df['X2'], format='%d.%m.%Y')

# Sort data by X3, X4, and date to ensure chronological order per group
df = df.sort_values(['X3', 'X4', 'X2']).reset_index(drop=True)

Step 2: Define the Rolling Mean Calculation Function #

We'll create a function that, for each group (grouped by X3 and X4), iterates through each row and calculates the mean of Y values from rows that meet the date criteria:

def calculate_grouped_rolling_mean(group):
    # Initialize Y1 column with NaN
    group['Y1'] = np.nan
    
    # Iterate through each row starting from the second one (first row has no history)
    for i in range(1, len(group)):
        current_row = group.iloc[i]
        current_date = current_row['X2']
        
        # Filter rows in the same group that are within the 5-day window before current date
        date_mask = (group['X2'] >= current_date - pd.Timedelta(days=5)) & (group['X2'] < current_date)
        valid_Y_values = group.loc[date_mask, 'Y']
        
        # Only set Y1 if there are valid values to average
        if not valid_Y_values.empty:
            group.iloc[i, group.columns.get_loc('Y1')] = valid_Y_values.mean()
    
    return group

# Apply the function to each X3-X4 group
df = df.groupby(['X3', 'X4']).apply(calculate_grouped_rolling_mean).reset_index(drop=True)

# Optional: Convert date back to original string format if needed
df['X2'] = df['X2'].dt.strftime('%d.%m.%Y')

Step 3: Verify the Result #

If you print the resulting DataFrame, you'll see it matches exactly the expected output you provided. For example:

• The row with X2=04.02.2019, X3=2, X4=3 has Y1=0 (matches the single valid prior row with Y=0).
• The row with X2=05.02.2019, X3=1, X4=1 has Y1=1 (matches the prior row from 01.02.2019).

Key Notes #

• Grouping is critical: We only consider rows with the same X3 and X4 values, as required.
• Dynamic date window: Unlike fixed-row rolling windows, this approach uses actual date ranges to filter relevant rows, which aligns with your requirement.
• Handling edge cases: Rows with no valid prior data in the window will keep Y1 as NaN, which matches your expected output.

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