Learn how to build running totals, cumulative averages, and within-group rankings in pandas using cumsum, expanding, and rank. Goes beyond the basics to cover groupby integration, tie-breaking strategies, NaN handling, and a complete sales dashboard project.
Imagine you're building a monthly sales report and your manager asks for three things: the running total of revenue so far this year, the average deal size as it accumulates over time, and a ranking of each sales rep within their region — all in the same DataFrame. In Excel, you'd reach for formulas scattered across three columns, carefully anchoring rows with dollar signs and hoping nobody inserts a row in the middle. In SQL, you'd write window functions. In pandas, there are clean, vectorized solutions for all three of these patterns, and once you understand how they work, you'll use them constantly.
This lesson covers three closely related techniques: cumsum for running totals, expanding for cumulative statistics like rolling averages that grow with your data, and rank for adding competition-style rankings to your rows. These aren't exotic features — they show up in real analyses all the time, from financial reporting to cohort analysis to leaderboard generation. By the end, you'll understand not just the syntax but why each method behaves the way it does, including the edge cases that catch people off guard.
What you'll learn:
cumsum produces running totals across numeric columns, with and without groupingexpanding creates a growing window that recalculates cumulative statistics at each rowrank assigns ordinal positions, handles ties, and works inside groups with groupbyNaN values, and group boundariesYou should already be comfortable loading data with pandas and doing basic filtering and grouping. If you need a refresher on groupby, see Grouping and Aggregating in pandas: groupby as the PivotTable Replacement. Familiarity with how pandas handles dates is also helpful — if dates feel unfamiliar, check out Working with Dates and Time Series in pandas: Parsing, Resampling, and Rolling Windows.
We'll work with a realistic sales transaction dataset throughout this lesson. Here's how to build it:
import pandas as pd
import numpy as np
np.random.seed(42)
dates = pd.date_range("2024-01-01", periods=120, freq="D")
reps = ["Alice", "Bob", "Carol", "David", "Eve"]
regions = {"Alice": "East", "Bob": "East", "Carol": "West", "David": "West", "Eve": "Central"}
records = []
for date in dates:
# Each day, 2–4 transactions occur
n = np.random.randint(2, 5)
for _ in range(n):
rep = np.random.choice(reps)
revenue = round(np.random.lognormal(mean=7.5, sigma=0.6), 2)
records.append({
"date": date,
"rep": rep,
"region": regions[rep],
"revenue": revenue,
"deals_closed": np.random.randint(1, 6),
})
df = pd.DataFrame(records)
df = df.sort_values(["date", "rep"]).reset_index(drop=True)
print(df.head(10))
print(f"\nShape: {df.shape}")
print(f"Date range: {df['date'].min()} to {df['date'].max()}")
This gives you roughly 360 rows: daily transactions across five sales reps in three regions over four months. It's messy enough to be interesting — multiple transactions per day, lognormal revenue distribution so we have a realistic mix of small and large deals — but clean enough that we can focus on the computation patterns.
The simplest cumulative operation is the running total. cumsum ("cumulative sum") computes, at each row, the sum of all values from the beginning of the series up to and including that row.
# A simple example to build intuition
sample = pd.Series([100, 250, 80, 410, 175])
print(sample.cumsum())
# 0 100
# 1 350
# 2 430
# 3 840
# 4 1015
Each value is the sum of everything before it plus itself. The math is obvious once you see it, but the practical value is that you don't need a helper column or a looping formula — it's a single method call on a Series or DataFrame column.
# Sort by date to ensure the cumsum makes temporal sense
df_sorted = df.sort_values("date").reset_index(drop=True)
# Add a running total of revenue across all reps
df_sorted["cumulative_revenue"] = df_sorted["revenue"].cumsum()
print(df_sorted[["date", "rep", "revenue", "cumulative_revenue"]].head(15))
This gives you the company-wide running revenue from day one. But "company-wide" is often not what you want. More interesting is the running total per rep or per region.
Here's where the real power kicks in. When you call cumsum on a groupby object, pandas restarts the cumulative sum at the boundary of each group:
# Running total per sales rep
df_sorted["rep_cumulative_revenue"] = (
df_sorted.groupby("rep")["revenue"].cumsum()
)
# Running total per region
df_sorted["region_cumulative_revenue"] = (
df_sorted.groupby("region")["revenue"].cumsum()
)
print(
df_sorted[["date", "rep", "region", "revenue",
"rep_cumulative_revenue", "region_cumulative_revenue"]]
.head(20)
)
The key behavior to understand: groupby(...).cumsum() returns a Series aligned to your original DataFrame's index. It doesn't collapse the rows — it transforms them. This is what makes it useful for adding a new column rather than producing a summary table.
Warning
The order of your rows matters enormously for cumulative calculations. If your DataFrame isn't sorted by date before you call cumsum, your running totals will be meaningless — they'll accumulate in index order, not chronological order. Always sort first, then reset the index, then compute cumulative values.
You can also cumulate the deals count:
df_sorted["rep_cumulative_deals"] = (
df_sorted.groupby("rep")["deals_closed"].cumsum()
)
While cumsum is the workhorse, pandas gives you the whole family:
# Running maximum revenue deal (per rep)
df_sorted["rep_max_deal_ever"] = (
df_sorted.groupby("rep")["revenue"].cummax()
)
# Running minimum
df_sorted["rep_min_deal_ever"] = (
df_sorted.groupby("rep")["revenue"].cummin()
)
cummax is particularly useful for "best result so far" reporting — think year-to-date record sales, peak stock price, etc.
cumsum is great for totals, but what if you want the average revenue per deal as it accumulates over time? You could divide cumsum by a row counter, but there's a cleaner way: expanding.
Think of expanding as a window that starts at the first row and grows by one row at a time. At row 5, the window contains rows 1–5. At row 50, it contains rows 1–50. This is the conceptual opposite of rolling(n), which keeps a fixed-size window.
# Expanding mean: average revenue up to each row
df_sorted["cumulative_avg_revenue"] = (
df_sorted["revenue"].expanding().mean()
)
print(df_sorted[["date", "revenue", "cumulative_avg_revenue"]].head(10))
You'll see the cumulative average stabilize as more data comes in — early on it's volatile because there are only a few data points, but by row 50 or 60 it's smoothing out. This is exactly what happens with real business metrics: your average deal size estimate is unreliable after week one but meaningful after a quarter.
Key insight
expanding().mean() is mathematically equivalent to cumsum() / (row_number), but it's cleaner, handles edge cases better, and signals your intent more clearly to anyone reading your code.
By default, expanding requires at least one data point to produce a result. But you can raise that floor:
# Don't compute an average until we have at least 5 data points
df_sorted["stable_avg_revenue"] = (
df_sorted["revenue"].expanding(min_periods=5).mean()
)
print(df_sorted[["date", "revenue", "stable_avg_revenue"]].head(10))
# First 4 rows will be NaN
This is useful when early-stage averages would mislead. Imagine a rep who closes one massive $50,000 deal on day one — their "average deal size" of $50,000 is technically correct but completely uninformative. Setting min_periods=5 or min_periods=10 forces the metric to wait until there's enough signal.
Just like cumsum, expanding can work per-group. The syntax is slightly different — you need apply or a direct groupby().expanding() chain:
# Cumulative average revenue per rep
df_sorted["rep_cumulative_avg"] = (
df_sorted.groupby("rep")["revenue"]
.expanding()
.mean()
.reset_index(level=0, drop=True) # remove the extra index level groupby adds
.sort_index()
)
The .reset_index(level=0, drop=True) call removes the group label that groupby().expanding() adds to the index — without it, the result won't align back to your DataFrame cleanly. This trips people up constantly.
Tip
If you get a "cannot reindex" error or your cumulative column appears as all NaN after a groupby().expanding() call, check whether you need .reset_index(level=0, drop=True) and .sort_index() to realign the index back to the original DataFrame.
The expanding accessor supports all the same aggregations as rolling:
rep_stats = (
df_sorted.groupby("rep")["revenue"]
.expanding()
)
df_sorted["rep_cumulative_std"] = (
rep_stats.std()
.reset_index(level=0, drop=True)
.sort_index()
)
df_sorted["rep_cumulative_max"] = (
rep_stats.max()
.reset_index(level=0, drop=True)
.sort_index()
)
The cumulative standard deviation is a surprisingly useful metric — it tells you how much a rep's deal sizes vary, and it becomes more meaningful as the sample grows.
cumsum and expanding are time-oriented operations — they make sense in chronological order. rank is different: it's about comparing rows to each other. It assigns a position to each row based on the value of a column, with 1 being the top (or bottom) depending on how you configure it.
# Create a daily summary first — total revenue per rep per day
daily = (
df_sorted
.groupby(["date", "rep", "region"])["revenue"]
.sum()
.reset_index()
.rename(columns={"revenue": "daily_revenue"})
)
# Rank reps by their daily revenue (highest = rank 1)
daily["daily_rank"] = daily["daily_revenue"].rank(
ascending=False, # higher revenue = lower rank number
method="min" # ties get the lowest (best) rank
)
print(daily.sort_values(["date", "daily_rank"]).head(15))
The ascending=False means the highest value gets rank 1. That's counterintuitive at first glance — you're telling pandas the ranking order is descending — but you'll use this pattern constantly in business contexts where "first place" should be the biggest number.
This is where rank gets nuanced. When two rows have the same value, what rank do they get? You control this with the method parameter:
method |
Behavior | Example (two tied at 100) |
|---|---|---|
'average' |
Average of the ranks they'd occupy | Both get 2.5 |
'min' |
Both get the lowest rank (competition-style) | Both get 2 |
'max' |
Both get the highest rank | Both get 3 |
'first' |
Rank by order of appearance | One gets 2, next gets 3 |
'dense' |
Like min, but no gaps in ranking |
1, 2, 2, 3 instead of 1, 2, 2, 4 |
# Demonstrate the difference between min and dense
example = pd.Series([500, 300, 300, 100])
print("min method: ", example.rank(ascending=False, method="min").tolist())
# [1.0, 2.0, 2.0, 4.0]
print("dense method: ", example.rank(ascending=False, method="dense").tolist())
# [1.0, 2.0, 2.0, 3.0]
For leaderboards and competition-style rankings, method='min' (or method='dense') is almost always what you want. method='average' is useful for statistical purposes (like converting to percentiles). method='first' gives you a deterministic ordering when ties must be broken by row order.
Note
rank always returns floats, even when there are no ties, because the 'average' method can produce non-integers. If you need integer ranks, use .rank(...).astype(int) — but only when you're confident there are no ties and you're not using the 'average' method.
Here's the genuinely powerful pattern: ranking each row against others in the same group. In SQL, this is RANK() OVER (PARTITION BY region ORDER BY revenue DESC). In pandas, it's groupby + rank:
# Rank each rep within their region based on total revenue
rep_totals = (
df_sorted
.groupby(["rep", "region"])["revenue"]
.sum()
.reset_index()
.rename(columns={"revenue": "total_revenue"})
)
rep_totals["rank_in_region"] = (
rep_totals.groupby("region")["total_revenue"]
.rank(ascending=False, method="min")
.astype(int)
)
print(rep_totals.sort_values(["region", "rank_in_region"]))
This is where the SQL analogy becomes obvious. The groupby("region") is the PARTITION BY, the column you're ranking on is the ORDER BY, and rank() is the window function. Pandas gets you to the same result with familiar chaining syntax.
Let's do the same thing on the row-level daily data, which is more realistic for a live dashboard:
# Within each day, rank reps by revenue within their region
daily["rank_in_region_that_day"] = (
daily.groupby(["date", "region"])["daily_revenue"]
.rank(ascending=False, method="min")
.astype(int)
)
print(
daily[["date", "rep", "region", "daily_revenue", "rank_in_region_that_day"]]
.sort_values(["date", "region", "rank_in_region_that_day"])
.head(20)
)
Each day, each rep gets a rank of 1, 2, or 3 within their region. On days when two reps tie exactly (rare with continuous revenue values, but possible with binned or rounded data), they both get the lower rank number.
Sometimes you don't want an ordinal rank — you want to know what percentile a value falls in:
rep_totals["revenue_percentile"] = (
rep_totals["total_revenue"].rank(pct=True) * 100
).round(1)
print(rep_totals[["rep", "total_revenue", "rank_in_region", "revenue_percentile"]])
A percentile rank of 80.0 means this rep's revenue is higher than 80% of the other reps. This is useful for distribution analysis and identifying outliers — you can quickly flag anyone below the 25th percentile for review, or celebrate anyone above the 90th.
Now let's build something you'd actually send to a manager. We'll combine all three techniques into a single, coherent report.
import pandas as pd
import numpy as np
# --- Step 1: Rebuild and sort the dataset ---
np.random.seed(42)
dates = pd.date_range("2024-01-01", periods=120, freq="D")
reps = ["Alice", "Bob", "Carol", "David", "Eve"]
regions = {"Alice": "East", "Bob": "East", "Carol": "West", "David": "West", "Eve": "Central"}
records = []
for date in dates:
n = np.random.randint(2, 5)
for _ in range(n):
rep = np.random.choice(reps)
revenue = round(np.random.lognormal(mean=7.5, sigma=0.6), 2)
records.append({
"date": date,
"rep": rep,
"region": regions[rep],
"revenue": revenue,
"deals_closed": np.random.randint(1, 6),
})
df = pd.DataFrame(records).sort_values(["rep", "date"]).reset_index(drop=True)
# --- Step 2: Rep-level cumulative metrics ---
df["ytd_revenue"] = df.groupby("rep")["revenue"].cumsum()
df["ytd_deals"] = df.groupby("rep")["deals_closed"].cumsum()
# Cumulative average deal size (need expanding + realign)
df["avg_deal_size_to_date"] = (
df.groupby("rep")["revenue"]
.expanding(min_periods=3)
.mean()
.reset_index(level=0, drop=True)
.sort_index()
)
# --- Step 3: Aggregate to rep-level summary ---
rep_summary = (
df.groupby(["rep", "region"])
.agg(
total_revenue=("revenue", "sum"),
total_deals=("deals_closed", "sum"),
avg_deal_size=("revenue", "mean"),
max_deal=("revenue", "max"),
transaction_count=("revenue", "count"),
)
.reset_index()
)
# --- Step 4: Rank within region ---
rep_summary["rank_in_region"] = (
rep_summary.groupby("region")["total_revenue"]
.rank(ascending=False, method="min")
.astype(int)
)
# Overall company rank
rep_summary["overall_rank"] = (
rep_summary["total_revenue"]
.rank(ascending=False, method="min")
.astype(int)
)
# Percentile
rep_summary["revenue_percentile"] = (
rep_summary["total_revenue"].rank(pct=True) * 100
).round(1)
# --- Step 5: Month-over-month view ---
df["month"] = df["date"].dt.to_period("M")
monthly = (
df.groupby(["rep", "region", "month"])["revenue"]
.sum()
.reset_index()
.rename(columns={"revenue": "monthly_revenue"})
.sort_values(["rep", "month"])
)
monthly["ytd_revenue"] = monthly.groupby("rep")["monthly_revenue"].cumsum()
monthly["rank_this_month"] = (
monthly.groupby("month")["monthly_revenue"]
.rank(ascending=False, method="min")
.astype(int)
)
# --- Step 6: Print reports ---
print("=" * 60)
print("SALES REP PERFORMANCE SUMMARY (Jan–Apr 2024)")
print("=" * 60)
print("\n[Overall Rankings]")
print(
rep_summary[["rep", "region", "total_revenue", "total_deals",
"avg_deal_size", "overall_rank", "revenue_percentile"]]
.sort_values("overall_rank")
.to_string(index=False)
)
print("\n[Rankings Within Region]")
print(
rep_summary[["region", "rep", "total_revenue", "rank_in_region"]]
.sort_values(["region", "rank_in_region"])
.to_string(index=False)
)
print("\n[Monthly Revenue with YTD Cumulative]")
print(
monthly[["rep", "month", "monthly_revenue", "ytd_revenue", "rank_this_month"]]
.sort_values(["month", "rank_this_month"])
.to_string(index=False)
)
This script produces three distinct tables — an overall leaderboard, a within-region ranking, and a monthly progression with running totals — all from the same underlying data, using the three techniques you've learned. This is a genuinely useful structure for a sales ops report, and it's entirely reproducible. You can connect it to a live CSV feed and re-run it daily.
Tip
If you're building this for regular distribution, consider exporting these tables to a formatted Excel file. The lesson on Automating Excel Reports with pandas and openpyxl: Formatted Workbooks Without Manual Work shows how to add column formatting, conditional coloring for rank columns, and auto-fit column widths.
Build a customer cohort retention report using the following scenario:
You have a dataset of customer purchase transactions. Your goal is to understand how customer spending evolves over time and rank customers within their acquisition cohort.
import pandas as pd
import numpy as np
np.random.seed(99)
customer_ids = range(1, 51) # 50 customers
cohorts = {i: f"2024-Q{np.random.randint(1, 4)}" for i in customer_ids}
transactions = []
for cid in customer_ids:
n_purchases = np.random.randint(3, 15)
purchase_dates = sorted(pd.date_range("2024-01-01", "2024-12-31", periods=n_purchases))
for d in purchase_dates:
transactions.append({
"customer_id": cid,
"cohort": cohorts[cid],
"purchase_date": d,
"amount": round(np.random.lognormal(4.5, 0.8), 2),
})
tx = pd.DataFrame(transactions).sort_values(["customer_id", "purchase_date"]).reset_index(drop=True)
print(tx.head(10))
Your tasks:
Running total per customer: Add a cumulative_spend column showing each customer's total spending up to each transaction.
Cumulative average per customer: Add a rolling_avg_spend column using expanding(min_periods=2).mean() per customer. This represents their average purchase value as it's updated with each new transaction.
Rank customers by total spend within their cohort: Aggregate to customer level (total spend), then use groupby("cohort").rank() to rank each customer within their acquisition cohort. Higher spenders should get rank 1.
Identify the top spender in each cohort: Filter your ranked summary to show only rank == 1 rows — one per cohort.
Bonus: Add a cumulative_max_purchase column per customer using expanding().max(). This shows the biggest purchase a customer has ever made up to each transaction date.
The expected output is a DataFrame with all cumulative columns, plus a separate summary table showing one winner per cohort.
This is the single most common error. If your data is sorted by customer ID but not by date within each customer, your cumsum will still produce a number — it just won't be meaningful.
# WRONG: cumsum on unsorted data
df["running_total"] = df.groupby("rep")["revenue"].cumsum()
# RIGHT: sort first
df = df.sort_values(["rep", "date"]).reset_index(drop=True)
df["running_total"] = df.groupby("rep")["revenue"].cumsum()
Warning
pandas won't tell you that your data is unsorted before computing cumulative values. There's no error — just silently wrong numbers. Make sorting part of your standard setup before any time-series computation.
When you chain groupby().expanding().mean(), pandas returns a Series with a MultiIndex (group label + original index). Directly assigning this back to your DataFrame produces all NaN because the indexes don't match.
# WRONG: index mismatch
df["expanding_avg"] = df.groupby("rep")["revenue"].expanding().mean()
# RIGHT: drop the extra index level and sort back to original order
df["expanding_avg"] = (
df.groupby("rep")["revenue"]
.expanding()
.mean()
.reset_index(level=0, drop=True)
.sort_index()
)
rank always returns float64 because method='average' can produce non-integer values. If you're sorting or displaying and want clean integers:
# Works safely when method is 'min', 'max', 'first', or 'dense'
df["rank_col"] = df["value"].rank(method="min", ascending=False).astype(int)
# Don't do this with method='average' — you'll silently truncate 2.5 to 2
cumsum, expanding, and rank all handle NaN differently:
cumsum propagates NaN — once a NaN appears, every subsequent row in the cumulative total is also NaNexpanding().mean() skips NaN values by default (like most aggregation functions)rank skips NaN by default (na_option='keep' leaves them as NaN; you can also use na_option='top' or na_option='bottom')If you have missing revenue values, clean them first. See Cleaning Messy Data with pandas: Missing Values, Duplicates, and Data Types for approaches to filling or dropping NaN before cumulative calculations.
# Safe pattern: fill NaN before cumulating
df["revenue_clean"] = df["revenue"].fillna(0)
df["running_total"] = df.groupby("rep")["revenue_clean"].cumsum()
When you use groupby().cumsum(), the output is aligned to your DataFrame's current sort order — not sorted by group then by date. If you've sorted by date globally but not within each group, the cumulative values will still respect that global order. In most cases you want to sort by [group_col, date_col] before computing group-level cumulatives.
If you're ranking by date and multiple rows share the same timestamp, your method parameter determines which gets priority. method='first' uses row order, which depends on how your DataFrame was sorted. Be explicit about secondary sort keys if ties are possible:
# Sort by date, then by revenue as a tiebreaker, then rank
df_sorted = df.sort_values(["date", "revenue"], ascending=[True, False])
df_sorted["rank"] = df_sorted["revenue"].rank(method="first", ascending=False)
For DataFrames under 500,000 rows, cumsum, expanding, and rank are all fast — they're implemented in C under the hood and run in vectorized operations. You won't notice a performance difference between approaches at that scale.
For larger datasets, a few things to keep in mind:
groupby().expanding().mean() with many groups is slower than groupby().cumsum() divided by a row counter, because expanding has to track each group's window separately. For millions of rows and hundreds of groups, consider whether you actually need expanding statistics or whether a simpler operation will do.
rank with method='average' is slightly slower than other methods because of the averaging step on ties. For large datasets with many ties, method='dense' or method='min' are faster.
If performance is critical, look at the lesson on Writing Fast pandas Code: Vectorization Instead of apply and Loops — the principles there apply here too. In particular, avoid using apply with a lambda that computes running totals manually; the vectorized methods are 10–100x faster.
You now have three powerful tools in your pandas toolkit:
cumsum — the workhorse for running totals. Sort your data, call it on a grouped series, and you have a new column that accumulates over time within each group. Add cummax and cummin for "best ever" and "worst ever" metrics.
expanding — the flexible window for cumulative statistics. Anything you can do with .mean(), .std(), .max() on a static group, you can do cumulatively with expanding. Use min_periods to suppress early-stage noise. Remember the index realignment trick after groupby().expanding().
rank — the row-comparison engine. Use ascending=False for "higher is better" rankings. Choose method='min' or method='dense' for competition-style leaderboards. Combine with groupby to partition rankings within groups, exactly like SQL's RANK() OVER (PARTITION BY ...).
Together, these three techniques cover the vast majority of "progressive" or "comparative" analytics you'll encounter: YTD totals, trailing averages, leaderboards, cohort comparisons, and percentile distributions.
Where to go next:
If your analysis involves time-based sliding windows (not just expanding ones), the lesson on Working with Dates and Time Series in pandas: Parsing, Resampling, and Rolling Windows covers rolling(n) and date-offset-based windows in depth.
If you want to turn these outputs into a formatted Excel report with highlighted rank columns, see Automating Excel Reports with pandas and openpyxl: Formatted Workbooks Without Manual Work.
To reshape your cumulative data for pivot-style comparisons, check out Reshaping Data with pivot_table, melt, and stack in pandas.
If your sales dataset lives in a database rather than a CSV, Reading from SQL Databases into pandas with SQLAlchemy shows how to pull it directly into a DataFrame and apply everything you've learned here.