Quick summary
Summarize this blog with AI
In pandas, window-style calculations can add a moving average, running total, previous value, or rank while keeping one result per input row. The important decisions are the partition, row order, frame, missing-value rule, and treatment of ties. This guide implements those decisions in Python rather than substituting SQL syntax.
The examples were tested with Python 3.12.14, NumPy 2.3.5, pandas 2.2.3 with Copy-on-Write enabled, and pandas 3.0.6. Run the blocks in order. All data below is a fictional fixture.
Create one ordered DataFrame
Each product is a separate partition. A date determines chronology, and an event ID breaks same-date ties deterministically. Sorting is part of the calculation; a groupby operation does not invent the intended chronological order. This fixture has no missing product keys. For other data, reject missing keys or deliberately include them with dropna=False; groupby excludes them by default.
import pandas as pd
sales = pd.DataFrame({
'event_id': [2, 4, 1, 5, 3],
'product': ['A', 'B', 'A', 'B', 'A'],
'day': ['2026-01-02', '2026-01-01', '2026-01-01', '2026-01-03', '2026-01-03'],
'amount': [20.0, 5.0, 10.0, 15.0, 20.0],
})
sales['day'] = pd.to_datetime(sales['day'], format='%Y-%m-%d')
sales = sales.sort_values(['product', 'day', 'event_id']).reset_index(drop=True)
for row in sales[['event_id', 'product', 'amount']].itertuples(index=False, name=None):
print(row)
Expected output:
(1, 'A', 10.0)
(2, 'A', 20.0)
(3, 'A', 20.0)
(4, 'B', 5.0)
(5, 'B', 15.0)
Moving averages, running averages, and totals
A two-row rolling mean uses the current row and at most one preceding row within the product. min_periods=1 allows the first row to have a value. An expanding mean uses all observations so far in the product. These are different frames even when a small example produces the same initial result.
grouped = sales.groupby('product', sort=False)['amount']
sales['rolling_mean_2'] = grouped.transform(lambda values: values.rolling(2, min_periods=1).mean())
sales['expanding_mean'] = grouped.transform(lambda values: values.expanding(min_periods=1).mean())
sales['running_total'] = grouped.cumsum()
print(sales['rolling_mean_2'].tolist())
print(sales['expanding_mean'].round(2).tolist())
print(sales['running_total'].tolist())
Expected output:
[10.0, 15.0, 20.0, 5.0, 10.0]
[10.0, 15.0, 16.67, 5.0, 10.0]
[10.0, 30.0, 50.0, 5.0, 20.0]
transform aligns each per-group result back to the original rows. If you choose grouped rolling directly, inspect its index carefully because it can include group levels. Do not discard an index and assume the remaining values still line up.
Previous values and tie-aware ranks
shift(1) returns the previous observed row in each product, not necessarily the previous calendar day. Product B's January 3 row follows its January 1 row. Dense descending rank gives the two A sales of 20 the same rank and does not leave a gap after the tie.
sales['previous_amount'] = grouped.shift(1)
sales['change'] = sales['amount'] - sales['previous_amount']
sales['sales_rank'] = grouped.rank(method='dense', ascending=False).astype('Int64')
sales['row_number'] = sales.groupby('product', sort=False).cumcount() + 1
print(sales['previous_amount'].fillna('missing').tolist())
print(sales['change'].fillna('missing').tolist())
print(sales['sales_rank'].tolist())
print(sales['row_number'].tolist())
Expected output:
['missing', 10.0, 20.0, 'missing', 5.0]
['missing', 10.0, 0.0, 'missing', 10.0]
[2, 1, 1, 2, 1]
[1, 2, 3, 1, 2]
Use method='min' for competition-style ranks with gaps or method='first' when ties should follow input order. Those choices answer different questions. A rank by amount is also different from a chronological row number.
A time interval is not a number of rows
This two-calendar-day window is right-closed: at timestamp t it includes observations in (t - 2 days, t]. On January 3, a January 1 observation lies exactly on the excluded left boundary. Specify the boundary instead of relying on an unstated interpretation of “two days.”
product_b = sales.loc[sales['product'].eq('B'), ['day', 'amount']].set_index('day')
print(product_b['amount'].rolling('2D', closed='right', min_periods=1).sum().tolist())
print(product_b['amount'].rolling(2, min_periods=1).sum().tolist())
Expected output:
[5.0, 15.0]
[5.0, 20.0]
Time-based rolling needs a datetime-like index or an explicitly selected time column, ordered within the group. If multiple events share a timestamp, decide whether the result is event-based or a daily aggregate before rolling. A missing day is not automatically inserted as a zero-sale day.
Missing values and forecasting leakage
min_periods counts nonmissing observations required for the calculation. It does not fill missing rows. Here the first two windows lack two valid measurements.
measurements = pd.Series([10.0, None, 30.0])
print(measurements.rolling(3, min_periods=2).mean().fillna('missing').tolist())
sales['prior_mean_2'] = grouped.transform(
lambda values: values.shift(1).rolling(2, min_periods=1).mean()
)
print(sales['prior_mean_2'].fillna('missing').tolist())
Expected output:
['missing', 'missing', 20.0]
['missing', 10.0, 15.0, 'missing', 5.0]
The shifted rolling feature excludes the current sale. That is often necessary when predicting the current row, since a feature containing its outcome leaks information. Centered windows can also use future observations. Excluding the current row is only one part of a time-aware evaluation; train/test boundaries and data availability still need review.
Check the partition and frame before optimizing
Validate one product by hand, test a single-row product, include a tie, and test a missing date and value. Prefer built-in grouped operations for sums, shifts, ranks, and counts. For a custom transform, measure on representative data and verify index alignment before pursuing speed.
For input cleanup, see NumPy and pandas data cleaning. Primary references: the pandas windowing guide, grouped shift, and ranking methods.
