Rolling windows per group, the version-safe way

A trailing window per group is the most common feature on a panel: a 20-day volatility, a 5-day return, a 60-day mean volume. pandas offers two ways to write it and only one of them behaves the same across versions.

import pandas as pd, numpy as np

df = pd.read_csv("all_stocks_5yr.csv")
df["date"] = pd.to_datetime(df["date"])
df = df.rename(columns={"Name": "ticker"}).sort_values(["ticker", "date"]).reset_index(drop=True)
df["ret"] = df.groupby("ticker")["close"].pct_change()
g = df.groupby("ticker")

The safe form

df["vol20"] = g["ret"].transform(lambda x: x.rolling(20).std())
df["ret5"] = g["ret"].transform(lambda x: x.rolling(5).sum())
df["ma60"] = g["close"].transform(lambda x: x.rolling(60, min_periods=30).mean())
print(df[["ticker", "date", "ret", "vol20"]].iloc[18:22].to_string(index=False))

ticker       date       ret    vol20
     A 2013-03-07  0.000231      NaN
     A 2013-03-08 -0.005087      NaN
     A 2013-03-11 -0.005113 0.015879
     A 2013-03-12 -0.004438 0.015771

Row 0 of each ticker has no return, so the first twenty returns occupy rows 1 to 20 and the window first fills at row 20. transform hands each ticker’s Series to the lambda in row order and returns a Series aligned to df, so the assignment lands on the right rows regardless of pandas version.

The form to avoid

df["vol20"] = g["ret"].rolling(20).std()                 # index is (ticker, row): may misalign or raise on assignment
df["vol20"] = g["ret"].rolling(20).std().reset_index(level=0, drop=True)   # works on some versions, not all

groupby.rolling returns a result with a two-level index whose exact shape has changed between pandas releases. Code that dropped the first level worked on one version and silently misaligned on another. The transform form is slightly slower and has no such history.

Windows count rows, not days

rolling(20) is the last twenty rows of the group. On a file with one row per trading day and no gaps that is twenty trading days, roughly a calendar month. If the file has gaps the window quietly spans them. Check with the gap between consecutive rows:

gap = g["date"].diff().dt.days
print("gaps > 5 calendar days within a ticker:", (gap > 5).sum())

For a true calendar window, set a DatetimeIndex and use an offset string: x.rolling("30D"). That only works per group inside the lambda after x.set_axis(dates), and is rarely worth it on a daily file.

min_periods

By default a window of 20 produces NaN until it has 20 observations. min_periods=k produces a value once k are available. Use it when short histories would otherwise lose most of their rows, and say so, because the early values are noisier.

full = g["ret"].transform(lambda x: x.rolling(60).std())
partial = g["ret"].transform(lambda x: x.rolling(60, min_periods=20).std())
print("rows with a value: strict %d   with min_periods=20 %d" % (full.notna().sum(), partial.notna().sum()))

rows with a value: strict 588756   with min_periods=20 608940

Check a window by eye, once

Before trusting any window, compute one value by hand for one ticker.

one = df[df["ticker"] == "AAPL"].reset_index(drop=True)
by_hand = one.loc[1:20, "ret"].std()          # rows 1..20 are the first twenty returns (row 0 is NaN)
print("rolling: %.6f   by hand: %.6f" % (one.loc[20, "vol20"], by_hand))

rolling: 0.015246   by hand: 0.015246

If the two do not match, the window is off by one or the sort order is wrong. This check takes ten seconds and has caught more bugs than any test suite.

Forward windows

For a multi-day target, shift first and then roll, so the window covers the days after t rather than before:

df["fwd5"] = g["ret"].transform(lambda x: x.shift(-5).rolling(5).sum())     # returns on days t+1 .. t+5

Overlapping forward windows make consecutive days’ targets share observations, which inflates any t-statistic computed across days. See t-statistics from repeated looks.