Window Functions and Rolling Calculations
Moving averages, ranks, and running totals.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
Teach me window calculations in pandas using my data. Columns and sample rows: [COLUMNS_AND_SAMPLE]. Calculations I need (rolling 7-day average, running total, rank within group, percent change versus last year, days since last purchase): [CALCULATIONS].
For each calculation give:
- A plain definition and the formula.
- The code (rolling, expanding, cumsum, rank, shift, pct_change, groupby with transform).
- A five-row worked example with the numbers shown, including the first rows where the window is incomplete and what min_periods does.
- The edge cases: irregular dates (use time-based windows such as rolling("7D") on a sorted datetime index), missing days, ties in rank (method="min" versus "dense"), and groups smaller than the window.
- A test I can run on one group by hand to check the result.
Then explain the most common mistake, computing a rolling mean before sorting, and show how to detect it. Finish with the SQL equivalent of one calculation so I can compare the two languages.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.