Running Totals
Python · GroupBy & SQL-Style Analytics
Theory
Cumulative methods walk down a column and keep a running result: cumsum() (running total), cumcount() (running row counter, starting at 0), cummax() (highest so far), cummin() and cumprod(). df['amount'].cumsum() on 100, 200, 50 gives 100, 300, 350. Because the result depends on row order, always sort first - typically by date - so 'running' means what you expect.
To restart the running value for each group, call the method on a groupby: df.groupby('city')['amount'].cumsum() gives a separate running total per city, aligned with the original rows - SQL's SUM(amount) OVER (PARTITION BY city ORDER BY order_date ROWS UNBOUNDED PRECEDING). groupby(...).cummax() tracks the best value so far per group, and dividing a running total by the group total (transform('sum')) gives cumulative percentage, used for Pareto (80/20) analysis. expanding().mean() gives a running average.
Why we use it
Year-to-date revenue, running balance of an account, best score so far and cumulative share of sales are everyday analytics. Cumulative methods compute them in one vectorised call, per group if needed.
When to use it
Use cumsum/cummax for running values over an ordered column, on a groupby to restart per partition. For a fixed-size moving window (last 3 days), use rolling instead; for running averages use expanding().mean().
How it works
- Sort by group key and date
- Start each group's running value at its first row
- Add (or compare) the next row's value
- Did the group key change? Yes: Restart the running value. No: Keep accumulating.
- Running total column aligned with rows
Syntax
df.sort_values('date')['col'].cumsum()
df.groupby('key')['col'].cumsum() / .cummax() / .cumcount()
s.expanding().mean()Example
df = df.sort_values(['city', 'order_date'])
df['running'] = df.groupby('city')['amount'].cumsum()Key points
- Sort first - running values depend on row order
- groupby(key)[col].cumsum() restarts the total for each group
- cumcount() starts at 0; add 1 for 1-based row numbers
- running / group total gives cumulative percentage (Pareto analysis)
- SQL equivalent: SUM(amount) OVER (PARTITION BY city ORDER BY order_date)