Learn how to compute running totals, cumulative averages, rolling windows, and within-group rankings in pandas using groupby and transform. Practical examples using realistic sales data, with full copy-paste code and a complete weekly performance report you can adapt immediately.
Picture this: your sales director drops a spreadsheet on your desk and asks for three things before the end-of-day meeting. First, she wants a running total of revenue by region so she can see how each territory is tracking toward its quarterly goal. Second, she wants a rolling average to smooth out the noise in weekly numbers. Third, she wants every salesperson ranked within their region — not globally, but within their peer group — so the team can see where they stand relative to colleagues selling in similar markets.
In Excel, you'd cobble this together with a mix of SUMIF, RANK, and maybe a pivot table. In SQL, you'd reach for window functions like SUM() OVER (PARTITION BY ...). In pandas, there's an elegant toolkit for exactly this kind of work: cumsum, cumprod, expanding, rank, and transform — often combined with groupby to do the partitioning that makes window calculations actually useful. This lesson teaches you to use all of them fluently, in realistic scenarios that mirror what you'd encounter in a production reporting environment.
By the end of this lesson, you'll have moved beyond simple aggregation and into the territory of "per-row analytics" — the calculations that require knowing both a row's individual value and its relationship to the group it belongs to.
What you'll learn:
cumsum, cummax, and cumprod work row-by-row, and how to apply them within groups using groupby + transformrank and pct_rank work, including how to handle ties and partition rankings by grouptransform differs from agg and apply, and why it's the right tool for adding back-calculated columns to your original DataFrameYou should be comfortable with the following before diving in:
loc and boolean masks (Selecting and Filtering Data in pandas: loc, iloc, and Boolean Masks)groupby and aggregation (Grouping and Aggregating in pandas: groupby as the PivotTable Replacement)You don't need to know anything about SQL window functions, but if you do, you'll find the parallels helpful and we'll call them out explicitly.
Let's build a realistic dataset we'll use throughout the lesson. This represents daily sales transactions across four regions and a handful of reps:
import pandas as pd
import numpy as np
np.random.seed(42)
regions = ['Northeast', 'Southeast', 'Midwest', 'West']
reps = {
'Northeast': ['Alice', 'Bob', 'Carol'],
'Southeast': ['David', 'Elena'],
'Midwest': ['Frank', 'Grace', 'Hank'],
'West': ['Iris', 'Jake'],
}
rows = []
dates = pd.date_range('2024-01-01', periods=13, freq='W-MON') # 13 weekly periods
for week in dates:
for region, people in reps.items():
for rep in people:
revenue = np.random.randint(8_000, 45_000)
units = np.random.randint(10, 120)
rows.append({
'week': week,
'region': region,
'rep': rep,
'revenue': revenue,
'units': units,
})
df = pd.DataFrame(rows).sort_values(['region', 'rep', 'week']).reset_index(drop=True)
print(df.head(10))
week region rep revenue units
0 2024-01-01 Midwest Frank 32145 87
1 2024-01-08 Midwest Frank 18932 44
2 2024-01-15 Midwest Frank 41203 99
...
This gives us 130 rows (13 weeks × 10 reps), sorted by region, rep, and week — the natural order for running calculations.
Note
Sorting before cumulative calculations is critical. pandas processes rows in the order they appear; if your DataFrame isn't sorted by the time dimension first, your running totals will be nonsense. Always call .sort_values() before applying cumulative functions to time-series data.
Before touching cumulative calculations, you need to understand transform, because it's the mechanism that makes group-aware window functions possible.
When you use groupby + agg, the result is a smaller DataFrame — one row per group. That's useful for summaries, but useless when you want to add a new column back to your original data.
transform solves this by returning a same-sized Series — every row gets a value, computed within its group context. Think of it as broadcasting group-level calculations back down to the row level.
Here's the contrast:
# agg: produces a summary table (fewer rows)
df.groupby('region')['revenue'].agg('sum')
# Region
# Midwest ...
# Northeast ...
# Southeast ...
# West ...
# transform: produces a same-length Series you can assign back
df['region_total'] = df.groupby('region')['revenue'].transform('sum')
df[['region', 'rep', 'week', 'revenue', 'region_total']].head(8)
region rep week revenue region_total
0 Midwest Frank 2024-01-01 32145 3287654
1 Midwest Frank 2024-01-08 18932 3287654
2 Midwest Frank 2024-01-15 41203 3287654
...
Every Midwest row gets the same region_total. That's the "broadcast" behavior — and it's what makes the more interesting calculations below possible. You can pass any aggregation string ('sum', 'mean', 'max', 'std') or a custom function to transform.
Key insight
In SQL terms, transform is the pandas equivalent of a window function with a PARTITION BY clause but no ORDER BY. It computes a value per group and joins it back to every row in that group. The cumulative functions we'll cover next add the ORDER BY dimension.
cumsum is the simplest window function: each row gets the sum of all values up to and including that row. Globally, it's trivial:
df['running_revenue_global'] = df['revenue'].cumsum()
But that's almost never what you want. You want running totals per rep — resetting at the start of each rep's record, not carrying over from one rep to the next.
This is where groupby + transform comes in:
# Running total of revenue per rep (resets for each rep)
df['running_rev_per_rep'] = (
df
.groupby('rep')['revenue']
.transform('cumsum')
)
# Running total per region (resets for each region)
df['running_rev_per_region'] = (
df
.groupby('region')['revenue']
.transform('cumsum')
)
# Check one rep's progression
alice = df[df['rep'] == 'Alice'][['week', 'revenue', 'running_rev_per_rep']]
print(alice)
week revenue running_rev_per_rep
... 2024-01-01 22341 22341
... 2024-01-08 31204 53545
... 2024-01-15 14876 68421
...
Each week builds on the previous. The running total resets to zero at the start of each rep's record, exactly as you'd want.
Adding a percentage-of-goal column is a natural next step. Suppose the quarterly revenue target per rep is $400,000:
QUARTERLY_TARGET = 400_000
df['pct_of_target'] = (df['running_rev_per_rep'] / QUARTERLY_TARGET * 100).round(1)
Now every row shows the rep's cumulative progress toward their quarterly goal — a genuinely useful number for a mid-quarter report.
Tip
cumsum also works on boolean columns. If you have an is_enterprise_deal flag, cumsum() on it gives you a running count of enterprise deals per rep, which is often more readable than a raw count. Combine it with groupby + transform the same way.
The cumsum pattern extends to other cumulative operations:
# Best single-week revenue achieved so far (running peak)
df['peak_revenue_to_date'] = (
df
.groupby('rep')['revenue']
.transform('cummax')
)
# Worst week so far (useful for flagging reps who are declining)
df['trough_revenue_to_date'] = (
df
.groupby('rep')['revenue']
.transform('cummin')
)
The cummax column answers a question like "what's the best this rep has ever done up to this point in time?" It's a useful benchmark column — any week where revenue < peak_revenue_to_date is a week the rep underperformed their personal best.
df['below_personal_best'] = df['revenue'] < df['peak_revenue_to_date']
Warning
cumprod exists but is rarely useful for revenue-type data — multiplying dollars together produces numbers with no business meaning. It's genuinely useful for things like cumulative growth rates (multiplying 1.03 × 1.05 × 0.98 gives compound growth), but don't reach for it by default.
A running average — the mean of all observations up to the current row — smooths out week-to-week noise and shows you the underlying trend more clearly than raw values. pandas calls this an expanding window because the window size grows with each row.
For a global expanding mean, you'd write df['revenue'].expanding().mean(). For a per-group expanding mean, you need a custom function inside transform:
# Expanding (cumulative) mean per rep
df['cumulative_avg_rev'] = (
df
.groupby('rep')['revenue']
.transform(lambda x: x.expanding().mean())
)
# Expanding mean per region (all reps combined)
df['cumulative_avg_rev_region'] = (
df
.groupby('region')['revenue']
.transform(lambda x: x.expanding().mean())
)
# Round for readability
df['cumulative_avg_rev'] = df['cumulative_avg_rev'].round(0)
The expanding mean is a much more stable signal than a single week's revenue. After 10 weeks, the cumulative average is based on 10 data points and won't swing dramatically from a single strong or weak week.
Comparing weekly revenue to the cumulative average is a powerful diagnostic:
df['vs_cumulative_avg'] = (df['revenue'] - df['cumulative_avg_rev']).round(0)
df['above_avg'] = df['vs_cumulative_avg'] > 0
Reps who are consistently above_avg=True in the last few weeks are on an upward trend. Those who've been above_avg=False for several weeks are worth a closer look.
Key insight
The expanding mean is related to, but distinct from, the rolling mean. A rolling mean uses a fixed-size window (e.g., the last 4 weeks). An expanding mean uses all history from the start. For early observations, they behave similarly; as data accumulates, the expanding mean becomes more stable while the rolling mean stays reactive. Both are covered in depth for time-series work in Working with Dates and Time Series in pandas: Parsing, Resampling, and Rolling Windows.
While we're here, let's cover rolling calculations within groups — they follow the same pattern as expanding:
# 4-week rolling average revenue per rep
df['rolling_4w_avg'] = (
df
.groupby('rep')['revenue']
.transform(lambda x: x.rolling(window=4, min_periods=1).mean())
)
The min_periods=1 argument means the rolling window will compute a mean even when fewer than 4 observations are available (i.e., for the first three weeks of each rep's record). Without it, those early rows would be NaN.
# 4-week rolling sum (useful for "last month's total")
df['rolling_4w_total'] = (
df
.groupby('rep')['revenue']
.transform(lambda x: x.rolling(window=4, min_periods=1).sum())
)
Rolling calculations within groups are particularly useful for:
Ranking is where the "window function" concept becomes especially powerful for people-analytics and leaderboard-style reporting. You almost never want a global rank — you want a rank within a peer group.
# Within-region rank by total revenue (for the most recent week only)
latest_week = df['week'].max()
latest = df[df['week'] == latest_week].copy()
latest['region_rank'] = (
latest
.groupby('region')['revenue']
.rank(method='dense', ascending=False)
)
print(latest[['region', 'rep', 'revenue', 'region_rank']].sort_values(['region', 'region_rank']))
region rep revenue region_rank
... Midwest Grace 43210 1.0
... Midwest Hank 38742 2.0
... Midwest Frank 22108 3.0
... Northeast Carol 41876 1.0
... Northeast Alice 34521 2.0
... Northeast Bob 19043 3.0
Each rep is ranked 1st, 2nd, or 3rd within their own region, not against the entire company. A rep who would be #7 globally might be #1 in their region — both pieces of information are meaningful.
The method parameter controls what happens when two reps have the same revenue:
| Method | Behavior | SQL equivalent |
|---|---|---|
'average' |
Tied values get the average of their ranks (default) | AVG |
'min' |
Tied values both get the lower rank | RANK() |
'max' |
Tied values both get the higher rank | — |
'dense' |
Tied values get the same rank; next rank is consecutive | DENSE_RANK() |
'first' |
Tied values ranked by order of appearance | ROW_NUMBER() |
For most leaderboard reports, 'dense' is the most intuitive — if two reps tie for first, both get rank 1 and the next rep gets rank 2 (not rank 3).
Tip
If you're coming from SQL, dense maps to DENSE_RANK(), min maps to RANK(), and first maps to ROW_NUMBER(). The pandas default (average) has no direct SQL equivalent and is more common in statistical contexts.
To rank every row — not just the latest week — for a "ranking over time" view:
# Rank each rep within their region for every week
df['weekly_region_rank'] = (
df
.groupby(['week', 'region'])['revenue']
.rank(method='dense', ascending=False)
)
Note the grouping here: ['week', 'region']. For each combination of week and region, we independently rank the reps. This gives you a longitudinal view of how rankings shift week to week.
# How often has each rep been ranked #1 in their region?
times_top = (
df[df['weekly_region_rank'] == 1]
.groupby(['region', 'rep'])
.size()
.rename('weeks_at_top')
.reset_index()
)
print(times_top)
That's a genuinely interesting metric: not just "who's winning now" but "who's been most consistently at the top."
pct_rank (called with rank(pct=True)) converts ranks to percentiles: 0.0 means lowest, 1.0 means highest. It answers the question "what fraction of the group is this rep outperforming?"
# Percentile rank within region for total-period revenue
total_rev = (
df
.groupby(['region', 'rep'])['revenue']
.sum()
.reset_index()
.rename(columns={'revenue': 'total_revenue'})
)
total_rev['pct_rank_in_region'] = (
total_rev
.groupby('region')['total_revenue']
.rank(pct=True)
.round(2)
)
print(total_rev.sort_values(['region', 'pct_rank_in_region'], ascending=[True, False]))
region rep total_revenue pct_rank_in_region
... Midwest Grace 412340 1.00
... Midwest Frank 389201 0.67
... Midwest Hank 314820 0.33
...
A rep with pct_rank_in_region = 1.0 is at the 100th percentile within their region — they outperformed everyone in their peer group over the period.
Percent rank is useful when you want a normalized score that lets you compare across groups of different sizes. A rep in a 3-person region and a rep in a 5-person region can both have a pct_rank of 0.8 — meaning they're in the top 20% of their respective groups — even though the absolute ranks would look different.
Warning
pct_rank is sensitive to small group sizes. With only 2 reps in a group, the values are always 0.0 and 1.0 — there's no nuance. Don't over-interpret percentile ranks when the peer group is fewer than ~5 people. Flag this caveat in any report you share.
Let's pull all these techniques together into a single, coherent analytical output that you could actually send to a sales director.
import pandas as pd
import numpy as np
# --- Rebuild dataset (same as top of lesson) ---
np.random.seed(42)
regions = ['Northeast', 'Southeast', 'Midwest', 'West']
reps = {
'Northeast': ['Alice', 'Bob', 'Carol'],
'Southeast': ['David', 'Elena'],
'Midwest': ['Frank', 'Grace', 'Hank'],
'West': ['Iris', 'Jake'],
}
rows = []
dates = pd.date_range('2024-01-01', periods=13, freq='W-MON')
for week in dates:
for region, people in reps.items():
for rep in people:
revenue = np.random.randint(8_000, 45_000)
units = np.random.randint(10, 120)
rows.append({'week': week, 'region': region, 'rep': rep,
'revenue': revenue, 'units': units})
df = pd.DataFrame(rows).sort_values(['region', 'rep', 'week']).reset_index(drop=True)
# -----------------------------------------------
# 1. Cumulative revenue per rep
# -----------------------------------------------
df['cum_rev_rep'] = (
df.groupby('rep')['revenue'].transform('cumsum')
)
# 2. 4-week rolling average per rep (smoothed trend)
df['roll4_avg_rep'] = (
df.groupby('rep')['revenue']
.transform(lambda x: x.rolling(4, min_periods=1).mean())
.round(0)
)
# 3. Expanding (cumulative) average per rep
df['expanding_avg_rep'] = (
df.groupby('rep')['revenue']
.transform(lambda x: x.expanding().mean())
.round(0)
)
# 4. Week-over-week change per rep
df['wow_change'] = (
df.groupby('rep')['revenue']
.transform(lambda x: x.diff())
)
# 5. Weekly rank within region (1 = top earner that week)
df['rank_in_region'] = (
df.groupby(['week', 'region'])['revenue']
.rank(method='dense', ascending=False)
.astype(int)
)
# 6. Percentile rank within region, per week
df['pct_rank_in_region'] = (
df.groupby(['week', 'region'])['revenue']
.rank(pct=True)
.round(2)
)
# 7. Flag: is this week's revenue above the rep's cumulative average?
df['above_own_avg'] = df['revenue'] > df['expanding_avg_rep']
# -----------------------------------------------
# Build the summary snapshot: latest week only
# -----------------------------------------------
latest = df[df['week'] == df['week'].max()].copy()
# Add cumulative-period rank by total revenue within region
period_totals = (
df.groupby(['region', 'rep'])['revenue']
.sum()
.reset_index()
.rename(columns={'revenue': 'period_total'})
)
period_totals['period_rank_in_region'] = (
period_totals
.groupby('region')['period_total']
.rank(method='dense', ascending=False)
.astype(int)
)
report = latest.merge(period_totals, on=['region', 'rep'])
report = report[[
'region', 'rep', 'week',
'revenue', 'wow_change',
'roll4_avg_rep', 'expanding_avg_rep',
'above_own_avg',
'cum_rev_rep', 'period_total',
'rank_in_region', 'pct_rank_in_region',
'period_rank_in_region'
]].sort_values(['region', 'period_rank_in_region'])
print(report.to_string(index=False))
This report gives the sales director everything she asked for — and more:
cum_rev_rep: Running total toward quarterly goalroll4_avg_rep: Smoothed trend for the last monthrank_in_region and period_rank_in_region: Where each rep stands in their peer group, both for this week and the full periodabove_own_avg: A simple flag that shows whether a rep is trending up or coastingThis is the kind of output you can pipe directly into Building Summary Reports with pandas pivot_table and to_excel: Turning Aggregated Data into a Formatted, Multi-Sheet Workbook to produce a polished, formatted Excel file.
Take the dataset from this lesson and complete the following tasks. All of them require combining techniques from across the lesson.
Setup: Use the same df DataFrame built at the beginning. Make sure it's sorted by ['region', 'rep', 'week'] before starting.
Task 1: Units leaderboard
Add a column called units_rank_in_region that ranks each rep by units sold within their region, for every week. A rank of 1 should mean the most units sold. Print the rows for week 2024-01-29 only.
Task 2: Momentum flag
Add a column called momentum that is 'rising' if the rep's current week revenue is above their 4-week rolling average, and 'falling' otherwise. (Hint: use np.where or a conditional column — see Conditional Columns and Bucketing in pandas: Creating New Fields with np.where, cut, and map if you need a refresher.)
Task 3: Running share of region
For each row, calculate what percentage of the region's cumulative revenue to that point was contributed by this rep. Call it rep_share_of_region_cumrev. (Hint: you'll need both the per-rep cumulative sum and the per-region cumulative sum.)
Task 4: Full-period summary
Collapse the DataFrame to one row per rep. Include: total revenue, total units, overall percentile rank within their region by revenue, number of weeks spent at rank #1 in region, and number of weeks with above_own_avg == True. Sort by region, then by total revenue descending.
# WRONG: cumulative sum on unsorted data
df['bad_cumsum'] = df.groupby('rep')['revenue'].transform('cumsum')
# CORRECT: sort first
df = df.sort_values(['rep', 'week']).reset_index(drop=True)
df['good_cumsum'] = df.groupby('rep')['revenue'].transform('cumsum')
The result of cumsum depends entirely on row order. If your data was loaded from a database or shuffled somewhere in your pipeline, the cumulative values will be wrong and the problem won't be obvious from inspection.
# This returns a smaller DataFrame — can't assign back
result = df.groupby('rep')['revenue'].agg('cumsum') # raises an error or misaligns
# This returns a same-length Series
result = df.groupby('rep')['revenue'].transform('cumsum') # correct
If you ever get an alignment error when trying to assign a grouped result back to your DataFrame, the first thing to check is whether you accidentally used agg instead of transform.
# WRONG: this ranks globally across all weeks
df['bad_rank'] = df.groupby('region')['revenue'].rank()
# CORRECT: group by both week AND region to rank within each week's cohort
df['good_rank'] = df.groupby(['week', 'region'])['revenue'].rank()
This is easy to miss. If you want "who's #1 in the Northeast this week", you need to group on ['week', 'region'], not just ['region']. Grouping on region alone ranks all rows across all weeks, which is meaningless as a weekly leaderboard.
# rank returns floats by default (because of the 'average' method for ties)
df['rank_col'] = df.groupby(['week', 'region'])['revenue'].rank(method='dense')
# dtype: float64
# Cast to int only when you're confident there are no NaN values
df['rank_col'] = df['rank_col'].astype(int)
If your source column has NaN values, rank will propagate them and you'll get a float column even with method='dense'. Fix missing values first (see Cleaning Messy Data with pandas: Missing Values, Duplicates, and Data Types) before ranking.
# WRONG: expanding mean globally — doesn't reset per rep
df['wrong_exp_avg'] = df['revenue'].expanding().mean()
# CORRECT: expanding mean per rep using transform + lambda
df['correct_exp_avg'] = (
df.groupby('rep')['revenue']
.transform(lambda x: x.expanding().mean())
)
The expanding() method on a plain Series has no concept of groups. It sees one long sequence of values across all reps. To get per-group expanding windows, you must use groupby + transform with a lambda.
Warning
Performance can be a concern when using lambda functions inside transform on large DataFrames, because pandas can't apply the same optimizations it uses for named aggregation functions like 'sum' or 'mean'. For DataFrames with hundreds of thousands of rows, consider computing the expanding/rolling calculation directly after sorting and grouping, or look at vectorized alternatives discussed in Writing Fast pandas Code: Vectorization Instead of apply and Loops.
You've now got a solid command of the "per-row analytics" layer of pandas — the calculations that live between raw data and summary tables. Let's recap the key ideas:
cumsum, cummax, cummin applied via groupby + transform give you running totals and tracking statistics that reset cleanly per groupexpanding() inside a transform lambda produces cumulative averages that grow more stable with each new data pointrolling(window, min_periods=1) inside a transform lambda gives you smoothed trends that stay reactive to recent changesrank(method='dense', ascending=False) applied to groupby(['week', 'region']) gives you within-group leaderboards at every point in timerank(pct=True) normalizes rankings to 0–1, enabling fair comparisons across peer groups of different sizestransform is the key operator: it does the grouping, applies any function, and broadcasts results back to the original DataFrame's shapeThese calculations power a huge share of real-world business reporting — anything involving "tracking toward a goal," "trending up or down," or "where does this person/product/region rank among peers."
Where to go next: