Rolling & Expanding Windows
Python · GroupBy & SQL-Style Analytics
Theory
s.rolling(window=3).mean() computes a moving average: each row gets the mean of itself and the two rows before it. The first two rows have fewer than 3 values and return NaN unless you set min_periods=1. Any aggregation works on the window - sum(), max(), min(), std(), median(). center=True centres the window on the row. For time series with a DatetimeIndex, rolling('7D') uses a time window (the last 7 days) instead of a fixed number of rows.
expanding() is a window that starts at the first row and grows: s.expanding().mean() is the average of everything so far. Per-group windows need care so they do not cross groups: df.groupby('city')['amount'].transform(lambda s: s.rolling(2, min_periods=1).mean()) keeps the original row order and index. In SQL this is AVG(amount) OVER (PARTITION BY city ORDER BY day ROWS BETWEEN 1 PRECEDING AND CURRENT ROW).
Why we use it
Daily numbers are noisy. Moving averages smooth them to show the trend, rolling sums give 'last 7 days' totals, and expanding windows give running averages - all common in dashboards and forecasting.
When to use it
Use rolling for fixed-size or time-based trailing windows, expanding for 'since the start' statistics and cumsum for simple running totals. Sort by time first; group with transform when each entity needs its own window.
How it works
- Time-ordered numeric column
- Trailing window of fixed size? Yes: rolling(n) or rolling('7D'). No: expanding() from the start.
- For each row, collect the values in its window
- Enough values (min_periods)? Yes: Compute mean / sum / max. No: Return NaN.
- Smoothed or windowed column
Syntax
s.rolling(window=n, min_periods=1).mean()
s.rolling('7D').sum() # needs a DatetimeIndex
s.expanding().mean()
df.groupby('key')['col'].transform(lambda s: s.rolling(n).mean())Example
df['ma_3'] = df['amount'].rolling(3).mean()
df['avg_so_far'] = df['amount'].expanding().mean()Key points
- rolling(n) needs n values; set min_periods=1 to avoid leading NaN
- rolling('7D') uses a time window and needs a sorted DatetimeIndex
- expanding() grows from the first row: running mean, max and so on
- Use groupby + transform(lambda s: s.rolling(n).mean()) per group
- SQL equivalent: AVG(x) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)