在Pandas中基于日期偏移量计算滚动加权均值的问题

Great question—let's break this down clearly, since this is a common pain point when working with irregular time series.

问题1：能否基于日期偏移量（如"90D"或"3M"）计算滚动加权均值？ #

Short answer: Yes, but not with pandas' native rolling(win_type=...) method—here's why and how to fix it:

When you specify a win_type like "gaussian", pandas expects an integer window size (fixed number of observations). The error ValueError: Invalid window 90D happens because weighted window functions rely on fixed positional indices to generate weight sequences, and time-offset windows have a dynamic number of observations (the count changes depending on how many points fall into the 90-day window at each step).

To get weighted means with time-offset windows, you need to customize the weighting logic using rolling.apply(), where you calculate weights based on actual time differences instead of positional indices. This works perfectly for non-uniform time intervals.

示例实现（高斯加权+90天窗口） #

Here's a practical example that computes Gaussian-weighted means over a 90-day window, even with irregular time gaps:

import numpy as np
import pandas as pd

# 生成带非均匀时间间隔的测试数据
np.random.seed(42)
data = np.random.randint(0, 1000, (1000, 10))
index = pd.date_range("20190101", periods=1000, freq="18H")
# 手动插入一个3天的间隔，模拟非均匀时序
index = index.insert(500, index[500] + pd.Timedelta(days=3))
df = pd.DataFrame(index=index, data=data)

def gaussian_weighted_mean(window, std_days=60):
    # 获取窗口内的时间戳，计算每个点到窗口末尾的天数差
    days_since_end = (window.index[-1] - window.index).days
    # 基于时间差生成高斯权重（标准差对应实际天数）
    weights = np.exp(-(days_since_end ** 2) / (2 * (std_days ** 2)))
    # 归一化权重，确保总和为1（避免极端值影响）
    weights = weights / weights.sum()
    # 计算加权均值（自动忽略NaN值）
    return np.nansum(window * weights, axis=0)

# 应用90天窗口的自定义高斯加权均值
df_weighted = df.rolling("90D").apply(gaussian_weighted_mean, raw=False)

问题2：若支持，指定window="90D"且win_type="gaussian"时，参数std是否代表60天？ #

With the native pandas rolling(win_type="gaussian"), no—the std parameter refers to positional units, not days. For example, if you use window=90 (90 observations), std=60 would mean the Gaussian curve's standard deviation is 60 positions into the window, which has no direct link to calendar days.

But with our custom implementation above? Absolutely—you can explicitly set std_days=60 to make the Gaussian standard deviation correspond to 60 calendar days. This aligns exactly with your intended logic, and weights are calculated based on real time differences, not arbitrary position indices.

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