如何在Pandas中对非连续日期计算至少一周前的最近3条观测滚动均值

问题背景 #

给定存在随机日期缺失的数据集，需要为每条记录计算至少一周前的最近3条观测值的滚动平均值，常规的shift方法因日期缺失会导致结果不可靠，需采用基于日期筛选的方案。

实现步骤 #

核心思路是针对每条记录，先筛选出所有早于当前日期7天的历史数据，再从中取最近的3条计算平均值：

1. 将日期列转换为可计算的日期时间类型，确保时间差计算准确。
2. 遍历每条记录，计算当前日期往前推7天的临界日期。
3. 筛选出所有日期≤临界日期的历史记录。
4. 对筛选结果按日期降序排序，取前3条计算Value列的平均值。

代码实现（Python Pandas） #

import pandas as pd

# 构造示例数据集
data = {
    'Index': [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15],
    'Date': ['05/12/2022','06/12/2022','07/12/2022','09/12/2022','10/12/2022','11/12/2022','12/12/2022','13/12/2022','14/12/2022','16/12/2022','17/12/2022','18/12/2022','20/12/2022','21/12/2022','22/12/2022'],
    'Weekday': [2,3,4,6,7,1,2,3,4,6,7,1,3,4,5],
    'Value': [10,20,40,10,60,30,40,50,60,20,50,10,20,10,40]
}
df = pd.DataFrame(data)

# 转换日期格式（注意示例日期是日/月/年）
df['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%Y')

def get_rolling_avg(row, source_df):
    # 计算一周前的临界日期
    cutoff_date = row['Date'] - pd.Timedelta(days=7)
    # 筛选符合时间条件的历史数据
    valid_records = source_df[source_df['Date'] <= cutoff_date]
    if len(valid_records) >= 3:
        # 取最近3条记录计算平均值
        top3 = valid_records.sort_values('Date', ascending=False).head(3)
        return top3['Value'].mean()
    # 不足3条时返回None（可根据需求调整为0或其他值）
    return None

# 为每条记录计算滚动平均值
df['Rolling_Avg'] = df.apply(lambda x: get_rolling_avg(x, df), axis=1)

# 查看最后三行验证结果
print(df.tail(3))

验证结果 #

运行代码后，最后三行的输出与期望一致：

Index       Date  Weekday  Value  Rolling_Avg
12     13 2022-12-20        3     20         40.0
13     14 2022-12-21        4     10         50.0
14     15 2022-12-22        5     40         50.0

性能优化提示 #

如果数据集规模较大，apply方法的遍历效率较低，可采用以下优化方案：

• 预先将数据集按日期排序，利用merge_asof结合滑动窗口逻辑批量计算。
• 使用向量化操作替代逐行遍历，减少循环开销。

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