基于大型DataFrame的分ID滚动OLS线性回归实现需求

Let's walk through how to implement this rolling linear regression (OLS) for each ids in your dataset. We'll use Python with pandas and statsmodels—the standard tools for this kind of time-series regression task.

Step 1: Align Your Data #

First, we need to merge df_y and df_x so each Y observation is paired with the corresponding X variables for the same date. Since df_x covers all dates in df_y, a left join on date will work perfectly:

import pandas as pd

# Convert date columns to datetime (if they aren't already)
df_y['date'] = pd.to_datetime(df_y['date'])
df_x['date'] = pd.to_datetime(df_x['date'])

# Merge the two DataFrames
merged_df = pd.merge(df_y, df_x, on='date', how='left')

# Optional: Drop any rows with missing values in Y or X variables
merged_df = merged_df.dropna(subset=['Y', 'X1', 'X2', 'X3', 'X4', 'X5', 'X6'])

Step 2: Rolling OLS Implementation #

We have two approaches here—one that's straightforward for small datasets, and a faster, vectorized version for larger datasets.

Option 1: Fast Vectorized Approach (Recommended) #

Use statsmodels' built-in RollingOLS class, which is optimized for this exact use case. It's way faster than writing a manual loop:

import statsmodels.api as sm
from statsmodels.regression.rolling import RollingOLS

def run_rolling_ols(group):
    # Ensure the group is sorted by date
    group = group.sort_values('date').reset_index(drop=True)
    
    # Prepare X (add constant term for intercept) and Y
    X = sm.add_constant(group[['X1', 'X2', 'X3', 'X4', 'X5', 'X6']])
    y = group['Y']
    
    # Initialize rolling OLS with 200-observation window
    rolling_model = RollingOLS(y, X, window=200)
    rolling_results = rolling_model.fit()
    
    # Extract coefficients, add ids and date columns
    params = rolling_results.params
    params['ids'] = group['ids'].iloc[0]
    params['date'] = group['date']
    
    return params

# Apply the function to each ids group
final_results = merged_df.groupby('ids').apply(run_rolling_ols).reset_index(drop=True)

Option 2: Manual Loop (For Customization) #

If you need more control over the regression process (e.g., custom handling of edge cases), use a manual loop over each rolling window:

def run_rolling_ols_manual(group):
    group = group.sort_values('date').reset_index(drop=True)
    window_size = 200
    results_list = []
    
    for i in range(window_size, len(group) + 1):
        # Get the 200-day window of data
        window_data = group.iloc[i - window_size:i]
        
        # Define X (with intercept) and Y
        X = sm.add_constant(window_data[['X1', 'X2', 'X3', 'X4', 'X5', 'X6']])
        y = window_data['Y']
        
        # Fit OLS model
        model = sm.OLS(y, X).fit()
        
        # Store coefficients along with ids and the end date of the window
        param_row = model.params.to_frame().T
        param_row['ids'] = group['ids'].iloc[0]
        param_row['date'] = window_data['date'].iloc[-1]
        
        results_list.append(param_row)
    
    return pd.concat(results_list, ignore_index=True)

# Apply to each group
final_results_manual = merged_df.groupby('ids').apply(run_rolling_ols_manual).reset_index(drop=True)

Key Notes #

• Window Definition: The examples above use a fixed 200-observation window. If you need a 200-calendar-day window instead, convert your date column to datetime, set it as the index, and use window='200D' in RollingOLS.
• Sorting: Always sort each ids group by date before running the regression—this ensures the rolling window uses chronological data.
• Missing Data: The dropna step ensures we don't try to fit regressions with missing values. Adjust this if you need to impute missing data instead.

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