如何基于end_date倒推计算Polars中的滚动统计量？

需求概述 #

需要对金融时间序列计算窗口为1m（月度）的滚动统计量，由于月度数据行数不固定，希望添加window_index列标记每个滚动窗口包含的行，索引从最新日期开始倒序分配，方便后续用.rolling().over()计算统计量。

期望的分组逻辑：从数据集结束日期倒推1个月作为窗口边界，比如结束日期是2023-02-07，那么第一个窗口是2023-01-07（或最近的2023-01-06）到2023-02-07，下一个窗口是2023-01-07再倒推1个月的日期到2023-01-07，以此类推。

当前尝试的代码 #

使用group_by_dynamic生成窗口分组，但未得到预期结果，尝试替换为timedelta(days=31)也未解决：

df_window_index = (
    data.group_by_dynamic(
        index_column="date", every="1m", by="symbol"
    )
    .agg()
    .with_columns(
        pl.int_range(0, pl.len()).over("symbol").alias("window_index")
    )
)
data = data.join_asof(df_window_index, on="date", by="symbol").sort(
    "symbol"
)

测试数据 #

df = pl.read_csv(b"""
date,open,high,low,close,volume,dividends,stock_splits,symbol,window_index
2021-01-04T00:00:00.000000000,133.52,133.61,126.76,129.41,143301900,0.0,0.0,AAPL,0
2021-01-05T00:00:00.000000000,128.89,131.74,128.43,131.01,97664900,0.0,0.0,AAPL,0
2021-01-06T00:00:00.000000000,127.72,131.05,126.38,126.6,155088000,0.0,0.0,AAPL,0
2021-01-07T00:00:00.000000000,128.36,131.63,127.86,130.92,109578200,0.0,0.0,AAPL,1
2021-01-08T00:00:00.000000000,132.43,132.63,130.23,132.05,105158200,0.0,0.0,AAPL,1
2021-01-11T00:00:00.000000000,129.19,130.17,128.5,128.98,100384500,0.0,0.0,AAPL,1
2021-01-12T00:00:00.000000000,128.5,129.69,126.86,128.8,91951100,0.0,0.0,AAPL,1
2021-01-13T00:00:00.000000000,128.76,131.45,128.49,130.89,88636800,0.0,0.0,AAPL,1
2021-01-14T00:00:00.000000000,130.8,131.0,128.76,128.91,90221800,0.0,0.0,AAPL,1
2021-01-15T00:00:00.000000000,128.78,130.22,127.0,127.14,111598500,0.0,0.0,AAPL,1
2021-01-19T00:00:00.000000000,127.78,128.71,126.94,127.83,90757300,0.0,0.0,AAPL,1
2021-01-20T00:00:00.000000000,128.66,132.49,128.55,132.03,104319500,0.0,0.0,AAPL,1
2021-01-21T00:00:00.000000000,133.8,139.67,133.59,136.87,120150900,0.0,0.0,AAPL,1
2021-01-22T00:00:00.000000000,136.28,139.85,135.02,139.07,114459400,0.0,0.0,AAPL,1
2021-01-25T00:00:00.000000000,143.07,145.09,136.54,142.92,157611700,0.0,0.0,AAPL,1
2021-01-26T00:00:00.000000000,143.6,144.3,141.37,143.16,98390600,0.0,0.0,AAPL,1
2021-01-27T00:00:00.000000000,143.43,144.3,140.41,142.06,140843800,0.0,0.0,AAPL,1
2021-01-28T00:00:00.000000000,139.52,141.99,136.7,137.09,142621100,0.0,0.0,AAPL,1
2021-01-29T00:00:00.000000000,135.83,136.74,130.21,131.96,177523800,0.0,0.0,AAPL,1
2021-02-01T00:00:00.000000000,133.75,135.38,130.93,134.14,106239800,0.0,0.0,AAPL,1
2021-02-02T00:00:00.000000000,135.73,136.31,134.61,134.99,83305400,0.0,0.0,AAPL,1
2021-02-03T00:00:00.000000000,135.76,135.77,133.61,133.94,89880900,0.0,0.0,AAPL,1
2021-02-04T00:00:00.000000000,136.3,137.4,134.59,137.39,84183100,0.0,0.0,AAPL,1
2021-02-05T00:00:00.000000000,137.35,137.42,135.86,136.76,75693800,0.2,0.0,AAPL,1
""".strip(), try_parse_dates=True)

解决方案 #

核心思路 #

问题出在group_by_dynamic默认从最早日期开始正向生成窗口，而我们需要从最晚日期倒推生成窗口。可以通过两种方式实现：

• 方式一：计算每个日期与该symbol最晚日期的月份差，直接生成倒序的window_index
• 方式二：无需单独生成window_index，通过反向排序配合滚动窗口的参数直接计算统计量

具体实现代码 #

import polars as pl

# 首先确保数据按symbol和date排序
data = data.sort(["symbol", "date"])

# 方案1：基于月份差生成倒序window_index
result = data.with_columns(
    # 计算每个日期与该symbol最晚日期的月份差，作为倒序索引
    (pl.col("date").dt.year() * 12 + pl.col("date").dt.month())
    .max().over("symbol")
    .sub(pl.col("date").dt.year() * 12 + pl.col("date").dt.month())
    .alias("window_index")
)

# 方案2：使用rolling窗口配合反向排序实现滚动统计（无需额外window_index）
# 如果直接计算滚动统计量，可以这样写：
rolling_stats = data.sort(["symbol", "date"], descending=[False, True]).with_columns(
    pl.col("close").rolling(window="1mo", offset="-1mo").mean().over("symbol").alias("rolling_monthly_mean")
).sort(["symbol", "date"])

说明 #

• 方案1生成的window_index从0开始（对应最新的1个月数据），数值越大对应越早的窗口，完全符合倒序分配的需求
• 方案2无需单独生成window_index，直接通过反向排序+滚动窗口参数实现从最新日期倒推1个月的统计计算，更简洁高效
• 如果需要严格的窗口边界（比如从结束日期倒推30天而非自然月），可以将window="1mo"替换为window=pl.duration(days=30)

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