Ranking and Comparing Groups in pandas: percent_rank, cumsum, and Window Functions for Running Totals and Leaderboards

Introduction

You've got a sales dataset with thousands of rows and a dozen regional managers. Your VP asks: "Who are our top quartile performers, and how does each region's running total compare to last quarter?" You could sort manually, copy-paste into Excel, and build RANK formulas — or you could write fifteen lines of pandas that answer the question definitively, reproducibly, and for any time period you choose.

Ranking and windowed comparisons are where pandas graduates from "fancy CSV reader" to a genuine analytical engine. The functions involved — rank(), cumsum(), rolling(), and their cousins — mirror what you'd reach for in SQL's window functions (RANK() OVER, SUM() OVER) or Excel's PERCENTRANK, SUMIF-plus-sort combinations. If you already understand grouping and aggregating in pandas with groupby, you have the conceptual foundation. This lesson takes you one layer deeper: computing statistics that depend on the position of a row relative to other rows, both globally and within groups.

By the end of this lesson you'll be able to build leaderboards that rank reps within their region, compute running revenue totals that reset by month, assign performance tiers based on percentile rank, and troubleshoot the subtle bugs (ties, nulls, wrong groupby scope) that trip up even experienced practitioners.

What you'll learn:

• How pandas rank() works, including the five tie-breaking methods and how to produce SQL-equivalent PERCENT_RANK
• How to use groupby().transform() to rank within groups without collapsing your DataFrame
• Building running totals with cumsum() and controlling reset points with groupby
• Using rolling() window functions for moving averages and period-over-period comparisons
• Assembling a complete leaderboard report with tiers, ranks, and running totals ready for export

---

Prerequisites

You should be comfortable with:

• pandas DataFrames, selecting columns, and filtering rows (see selecting and filtering data with loc, iloc, and boolean masks)
• groupby() and aggregation basics
• Basic Python — list comprehensions, f-strings, method chaining

You'll need pandas ≥ 1.3 and numpy installed. All examples run in Jupyter or any Python script environment.

---

The Dataset: A Sales Performance File

Let's work with a realistic scenario throughout: a 12-month sales dataset for a software company with four regions, multiple reps per region, and monthly revenue figures. We'll build it programmatically so you can run everything without a CSV file.

import pandas as pd
import numpy as np

np.random.seed(42)

regions = ["Northeast", "Southeast", "Midwest", "West"]
reps_per_region = {
    "Northeast": ["Alice", "Bob", "Carol", "David"],
    "Southeast": ["Eva", "Frank", "Grace"],
    "Midwest":   ["Hank", "Iris", "Jake", "Lena"],
    "West":      ["Mia", "Noah", "Olivia"],
}
months = pd.date_range("2024-01-01", periods=12, freq="MS")

rows = []
for region, reps in reps_per_region.items():
    for rep in reps:
        for month in months:
            base = np.random.randint(40_000, 150_000)
            rows.append({
                "month":   month,
                "region":  region,
                "rep":     rep,
                "revenue": base + np.random.randint(-10_000, 10_000),
                "deals":   np.random.randint(3, 25),
            })

df = pd.DataFrame(rows)
df["month"] = pd.to_datetime(df["month"])
print(df.shape)       # (168, 5)
print(df.head(8))

You now have 168 rows: 14 reps × 12 months. Every technique in this lesson will apply to this DataFrame, so keep it handy.

---

How pandas rank() Works

rank() assigns an ordinal position to each value in a Series or column. It's the foundation for leaderboards. The core call is simple:

# Global rank across all reps and months — probably not what you want yet
df["revenue_rank"] = df["revenue"].rank(ascending=False)

But rank() has several keyword arguments that dramatically change its behavior, and you need to understand them before you use it in production.

The method parameter: handling ties

When two rows have the same value, pandas must decide what rank they get. The method argument controls this:

method	behavior	SQL equivalent
average (default)	tied rows share the average of their ranks	—
min	tied rows get the lowest rank (dense-ish)	RANK()
max	tied rows get the highest rank	—
first	tied rows ranked by order of appearance	ROW_NUMBER()
dense	no gaps after ties	DENSE_RANK()

sample = pd.Series([100, 200, 200, 300])

print(sample.rank(method="average"))  # 1.0, 2.5, 2.5, 4.0
print(sample.rank(method="min"))      # 1.0, 2.0, 2.0, 4.0
print(sample.rank(method="dense"))    # 1.0, 2.0, 2.0, 3.0
print(sample.rank(method="first"))    # 1.0, 2.0, 3.0, 4.0

For leaderboards, dense is usually what stakeholders expect — if two reps tie for 2nd, the next rep is 3rd, not 4th. For "top N" filtering where you need exactly N rows, first prevents ties from blowing up your row count.

Percent rank: who's in the top quartile?

Percent rank scales each rank to the range [0.0, 1.0], so you can say "this rep is at the 87th percentile." In pandas, you get it with pct=True:

df["pct_rank"] = df["revenue"].rank(ascending=False, pct=True)

Now pct_rank of 0.05 means this row's revenue is in the top 5% globally. This is the pandas equivalent of Excel's PERCENTRANK.INC or SQL's PERCENT_RANK().

To translate percentile rank into performance tiers:

def assign_tier(pct):
    if pct <= 0.25:
        return "Top Quartile"
    elif pct <= 0.50:
        return "Second Quartile"
    elif pct <= 0.75:
        return "Third Quartile"
    else:
        return "Bottom Quartile"

df["global_tier"] = df["pct_rank"].apply(assign_tier)

But notice the problem: this is a global rank across all months and all reps. A rep might look like a top quartile performer globally just because all their entries happen to fall in high-revenue months. What you actually want is rank within a period or within a region — and that's where groupby comes in.

---

Ranking Within Groups Using groupby and transform

This is the most important technique in this lesson. The pattern is:

df["rank_within_group"] = df.groupby("group_column")["value_column"].rank(...)

Because rank() is a transform-style operation (it returns a Series with the same length as the input), you don't need .transform() explicitly — groupby().rank() already broadcasts back to the original index.

Ranking reps within each month

# Who was #1 in each month? Rank all reps by revenue within each month
df["monthly_rank"] = (
    df.groupby("month")["revenue"]
    .rank(ascending=False, method="dense")
    .astype(int)
)

# Show the top 3 in January 2024
jan = df[df["month"] == "2024-01-01"].sort_values("monthly_rank")
print(jan[["rep", "region", "revenue", "monthly_rank"]].head(5))

Ranking reps within their region for each month

Now you're ranking within two grouping dimensions — region and month:

df["regional_monthly_rank"] = (
    df.groupby(["region", "month"])["revenue"]
    .rank(ascending=False, method="dense")
    .astype(int)
)

# Spot check: Northeast reps ranked within Northeast in March 2024
mask = (df["region"] == "Northeast") & (df["month"] == "2024-03-01")
print(
    df[mask]
    .sort_values("regional_monthly_rank")
    [["rep", "revenue", "regional_monthly_rank"]]
)

Percentile rank within region

The same pattern applies to pct=True:

df["regional_pct_rank"] = (
    df.groupby(["region", "month"])["revenue"]
    .rank(ascending=False, pct=True)
)

df["regional_tier"] = df["regional_pct_rank"].apply(assign_tier)

---

Building a Full Leaderboard

Let's put ranking to work in a real deliverable: an annual leaderboard that shows each rep's total revenue for the year, their global rank, their regional rank, and their performance tier.

# Aggregate: total revenue per rep per region for the full year
annual = (
    df.groupby(["region", "rep"], as_index=False)
    .agg(
        total_revenue=("revenue", "sum"),
        total_deals=("deals", "sum"),
        avg_monthly_revenue=("revenue", "mean"),
    )
)

# Global rank
annual["global_rank"] = (
    annual["total_revenue"]
    .rank(ascending=False, method="dense")
    .astype(int)
)

# Rank within region
annual["regional_rank"] = (
    annual.groupby("region")["total_revenue"]
    .rank(ascending=False, method="dense")
    .astype(int)
)

# Percent rank within region → tier
annual["regional_pct_rank"] = (
    annual.groupby("region")["total_revenue"]
    .rank(ascending=False, pct=True)
)
annual["tier"] = annual["regional_pct_rank"].apply(assign_tier)

# Revenue per deal (efficiency metric)
annual["rev_per_deal"] = (annual["total_revenue"] / annual["total_deals"]).round(0)

# Clean up
annual = annual.sort_values(["global_rank"])
annual = annual.drop(columns=["regional_pct_rank"])

print(annual.to_string(index=False))

This DataFrame is now export-ready. You could push it directly to Excel with to_excel() — see automating Excel reports with pandas and openpyxl for adding conditional formatting on top of it.

---

Running Totals with cumsum()

cumsum() computes the cumulative sum up to each row. It's the pandas equivalent of Excel's running total column (adding a SUM that expands as you drag down) or SQL's SUM() OVER (ORDER BY date ROWS UNBOUNDED PRECEDING).

Global running total

# Sort first — cumsum follows the current row order
df_sorted = df.sort_values(["rep", "month"]).copy()
df_sorted["running_total"] = df_sorted["revenue"].cumsum()

This isn't very useful on its own — it's mixing all reps together. The running total grows monotonically across all 168 rows.

Running total per rep (cumsum with groupby)

df_sorted["rep_running_total"] = (
    df_sorted.groupby("rep")["revenue"]
    .cumsum()
)

Now each rep has their own cumulative revenue that starts at zero on their first month and accumulates through December. This is the pattern you'll use most often.

# Verify: check Alice's running total
alice = df_sorted[df_sorted["rep"] == "Alice"][["month", "revenue", "rep_running_total"]]
print(alice.to_string(index=False))

The last row for Alice should equal her total_revenue from the annual DataFrame we built earlier.

Running total that resets by region and month

What if you want to know how revenue is accumulating within each region as months progress? This is useful for a YTD (year-to-date) tracker per region:

df_sorted["region_ytd"] = (
    df_sorted.groupby(["region", df_sorted["month"].dt.month <= df_sorted["month"].dt.month])["revenue"]
    .cumsum()
)

Wait — that's not quite right. The issue with cumsum() and multi-level groupby is ordering. Let's do this properly:

# Sort by region and month, then cumsum within region
df_ytd = (
    df
    .sort_values(["region", "month"])
    .copy()
)

# Sum revenue per region per month first
region_monthly = (
    df_ytd.groupby(["region", "month"], as_index=False)["revenue"]
    .sum()
    .rename(columns={"revenue": "monthly_revenue"})
)

# Now compute running YTD per region
region_monthly["region_ytd"] = (
    region_monthly.groupby("region")["monthly_revenue"]
    .cumsum()
)

print(region_monthly[region_monthly["region"] == "West"].to_string(index=False))

---

Window Functions with rolling()

rolling() computes statistics over a sliding window of N rows. The most common use: 3-month moving average of revenue to smooth out noise, or comparing this month to the trailing average.

3-month rolling average per rep

df_sorted["revenue_3mo_avg"] = (
    df_sorted.groupby("rep")["revenue"]
    .transform(lambda x: x.rolling(window=3, min_periods=1).mean())
)

The .transform(lambda ...) pattern is necessary here because groupby().rolling() returns a multi-indexed Series that needs explicit alignment. Wrapping it in transform broadcasts the result back to the original shape.

Month-over-month growth rate

A common executive-facing metric is how much a rep's revenue changed from last month:

df_sorted["prev_month_revenue"] = (
    df_sorted.groupby("rep")["revenue"]
    .shift(1)
)

df_sorted["mom_growth"] = (
    (df_sorted["revenue"] - df_sorted["prev_month_revenue"])
    / df_sorted["prev_month_revenue"]
).round(4)

shift(1) moves values down by one row within each group, so you're comparing each row to the previous row for that rep. The first month per rep will have NaN for prev_month_revenue and therefore NaN for mom_growth — which is correct, since there's no prior period.

For deeper work with time-indexed data, rolling windows, and resampling, see working with dates and time series in pandas.

Rolling rank: who's been consistently in the top tier?

Here's an advanced pattern: computing a rolling average of a rep's monthly rank, to identify reps who are consistently in the top tier versus those who flash high one month and disappear.

# First, get the monthly global rank (already computed earlier as monthly_rank)
# Now compute a 3-month rolling average of that rank per rep
df_sorted["rolling_rank_avg"] = (
    df_sorted.groupby("rep")["monthly_rank"]
    .transform(lambda x: x.rolling(3, min_periods=1).mean())
)

# Lower rolling_rank_avg = more consistently high-ranked
consistency_check = (
    df_sorted[df_sorted["month"] == "2024-12-01"]
    [["rep", "region", "monthly_rank", "rolling_rank_avg"]]
    .sort_values("rolling_rank_avg")
)
print(consistency_check.to_string(index=False))

This kind of metric doesn't exist as a single formula in Excel — you'd be building a very messy AVERAGEIFS setup. In pandas, it's four lines.

---

Real-World Project: The Monthly Sales Intelligence Report

Let's build something you could actually hand to a VP of Sales. We'll produce a single DataFrame that combines everything — rankings, running totals, growth rates, and consistency scores — and then export it to Excel.

# ── Step 1: Start from sorted, clean data ────────────────────────────────────
report_df = df.sort_values(["rep", "month"]).copy()

# ── Step 2: Rankings ─────────────────────────────────────────────────────────
report_df["monthly_rank"] = (
    report_df.groupby("month")["revenue"]
    .rank(ascending=False, method="dense")
    .astype(int)
)

report_df["regional_monthly_rank"] = (
    report_df.groupby(["region", "month"])["revenue"]
    .rank(ascending=False, method="dense")
    .astype(int)
)

report_df["regional_pct_rank"] = (
    report_df.groupby(["region", "month"])["revenue"]
    .rank(ascending=False, pct=True)
)
report_df["tier"] = report_df["regional_pct_rank"].apply(assign_tier)

# ── Step 3: Running totals ────────────────────────────────────────────────────
report_df["rep_ytd_revenue"] = (
    report_df.groupby(["rep"])["revenue"]
    .cumsum()
)

# ── Step 4: Rolling metrics ───────────────────────────────────────────────────
report_df["revenue_3mo_avg"] = (
    report_df.groupby("rep")["revenue"]
    .transform(lambda x: x.rolling(3, min_periods=1).mean())
    .round(0)
)

report_df["prev_revenue"] = report_df.groupby("rep")["revenue"].shift(1)
report_df["mom_growth_pct"] = (
    (report_df["revenue"] - report_df["prev_revenue"])
    / report_df["prev_revenue"]
    * 100
).round(1)

report_df["rolling_rank_avg"] = (
    report_df.groupby("rep")["monthly_rank"]
    .transform(lambda x: x.rolling(3, min_periods=1).mean())
    .round(1)
)

# ── Step 5: Clean up display columns ─────────────────────────────────────────
final_cols = [
    "month", "region", "rep", "revenue", "deals",
    "monthly_rank", "regional_monthly_rank", "tier",
    "rep_ytd_revenue", "revenue_3mo_avg", "mom_growth_pct",
    "rolling_rank_avg"
]
report_df = report_df[final_cols].drop(columns=[])

# ── Step 6: Inspect the result ────────────────────────────────────────────────
print(report_df[report_df["rep"] == "Alice"].to_string(index=False))

Exporting to Excel with a summary tab

# Annual summary leaderboard (reuse the annual DataFrame from earlier)
annual = (
    report_df.groupby(["region", "rep"], as_index=False)
    .agg(
        total_revenue=("revenue", "sum"),
        total_deals=("deals", "sum"),
        avg_mom_growth=("mom_growth_pct", "mean"),
        best_monthly_rank=("monthly_rank", "min"),
        avg_rolling_rank=("rolling_rank_avg", "last"),
    )
)
annual["global_rank"] = (
    annual["total_revenue"].rank(ascending=False, method="dense").astype(int)
)
annual["regional_rank"] = (
    annual.groupby("region")["total_revenue"]
    .rank(ascending=False, method="dense")
    .astype(int)
)
annual = annual.sort_values("global_rank")

with pd.ExcelWriter("sales_intelligence_report.xlsx", engine="openpyxl") as writer:
    annual.to_excel(writer, sheet_name="Annual Leaderboard", index=False)
    report_df.to_excel(writer, sheet_name="Monthly Detail", index=False)

print("Report exported.")

This is a production-grade output. For conditional formatting on the exported file, check out building summary reports with pandas pivot_table and to_excel.

---

Hands-On Exercise

Use the dataset you built at the start of this lesson to complete the following tasks. Don't peek at the solutions above — work through each one.

Exercise 1: Q4 leaderboard Filter df to only Q4 months (October, November, December). Compute each rep's total Q4 revenue, their global rank for Q4, and their rank within their region. Display the top 5 globally.

# Hint: filter with df["month"].dt.month.isin([10, 11, 12])

Exercise 2: Cumulative deals leaderboard Compute a cumulative deals total per rep across the year. Which rep closed their 100th deal first? (Sort by month, cumsum deals, then find where rep_cumulative_deals first crosses 100.)

# Hint: after cumsum, use .loc[df["rep_cumulative_deals"] >= 100].groupby("rep")["month"].first()

Exercise 3: Consistency king Identify the rep with the lowest (best) average monthly rank across the entire year. This is your "most consistent" performer. Does that rep also have the highest total revenue?

Exercise 4: Regional running total race Build a chart-ready DataFrame showing each region's cumulative revenue by month (month on the x-axis, cumulative revenue on the y-axis, one line per region). Use groupby(["region", "month"]).sum() followed by groupby("region").cumsum().

---

Common Mistakes & Troubleshooting

Mistake 1: Ranking before sorting

rank() doesn't sort your DataFrame. It computes ranks on whatever order exists. If you call rank() on an unsorted DataFrame and expect results in visual order, you'll get correct ranks (pandas handles this internally), but cumsum() absolutely requires sorted data. Always establish a clear sort before cumulative operations.

# Wrong: cumsum on unsorted data
df["rep_ytd"] = df.groupby("rep")["revenue"].cumsum()  # BUG if df not sorted by month

# Right:
df = df.sort_values(["rep", "month"])
df["rep_ytd"] = df.groupby("rep")["revenue"].cumsum()

Mistake 2: Using groupby().agg() when you need transform()

groupby().agg() collapses your DataFrame to one row per group. groupby().rank() and groupby().cumsum() return a Series with the same length as the input. If you accidentally use agg("sum") expecting a running total, you'll get a scalar per group instead.

# Wrong: agg collapses
df.groupby("rep")["revenue"].agg("sum")  # 14 rows, not 168

# Right: cumsum broadcasts
df.groupby("rep")["revenue"].cumsum()    # 168 rows ✓

Mistake 3: Forgetting that rank() returns float

Even method="dense" returns float64. Assigning it to a column works fine, but if you filter with df[df["monthly_rank"] == 1] and the column is float, you're comparing int to float — this usually works in pandas but can cause subtle issues. Explicitly cast to int early.

Mistake 4: Rolling window crossing group boundaries

When you use .rolling() without groupby, the window slides across all rows in the DataFrame — including across different reps. A rep's "3-month average" will silently include the last months of the previous rep alphabetically.

# Wrong: rolling across all rows, mixing reps
df_sorted["bad_avg"] = df_sorted["revenue"].rolling(3).mean()

# Right: rolling within each rep's group
df_sorted["good_avg"] = (
    df_sorted.groupby("rep")["revenue"]
    .transform(lambda x: x.rolling(3, min_periods=1).mean())
)

Mistake 5: NaN handling in rank()

rank() skips NaN values by default (na_option="keep"), so NaN rows get NaN ranks. This is usually correct. But if you have NaN revenue values and forget to handle them, your leaderboard will have gaps. Use na_option="bottom" to push NaN rows to the bottom, or clean your data first with cleaning messy data with pandas.

# Push NaN revenue rows to the bottom of rankings
df["revenue_rank"] = df["revenue"].rank(ascending=False, na_option="bottom", method="dense")

Mistake 6: Percent rank with small groups

With a group of 4 reps, pct=True produces only 4 distinct values: 0.0, 0.333, 0.667, 1.0. The "top quartile" tier will only ever include one person (the 0.0 percentile). This is mathematically correct but may confuse stakeholders who expect more granularity. Consider labeling small groups by rank number rather than tier.

---

Performance Considerations

For DataFrames with millions of rows, the groupby().transform(lambda x: x.rolling(...).mean()) pattern becomes slow. Some alternatives:

Use groupby().rolling() directly for cumulative functions:

# Faster than transform for large DataFrames
result = (
    df_sorted.groupby("rep")["revenue"]
    .rolling(3, min_periods=1)
    .mean()
    .reset_index(level=0, drop=True)  # drop the extra index level
)
df_sorted["revenue_3mo_avg"] = result

Pre-aggregate before windowing: if your underlying data has many rows per natural group (daily transactions), aggregate to the period level (monthly totals) first, then apply rolling windows. Fewer rows = faster computation.

For extremely large datasets, consider moving to a SQL engine for the windowing step and loading results into pandas for final formatting. See reading from SQL databases into pandas with SQLAlchemy for how to execute window function queries and pull results directly.

---

Summary & Next Steps

Here's what you can now do that you couldn't before:

• Use rank(method="dense") for clean leaderboards and rank(pct=True) for percentile-based tiers
• Apply groupby().rank() to rank within groups without losing your original DataFrame shape
• Build running totals with groupby().cumsum() that reset correctly at group boundaries
• Use groupby().transform(lambda x: x.rolling(n).mean()) for moving averages within groups
• Compute month-over-month growth with groupby().shift(1)
• Combine all of these into a single analysis DataFrame ready for export

The mental model to take away: window functions always operate along a sequence within a scope. The sequence is your sort order; the scope is your groupby key. Get those two right and everything else follows.

Where to go next:

If you want to take this output and create multi-dimensional summaries — cross-tabbing tier distribution by region, for example — summarizing and cross-tabulating categorical data in pandas covers that exact workflow.

If your leaderboard needs to pull data from multiple months of files automatically, combining and stacking multiple Excel or CSV files into one pandas DataFrame shows you how to build that ingestion layer before the ranking logic runs.

And if you're preparing to turn this into a scheduled report that runs itself every week, building and automating recurring reports with pandas covers the full automation pipeline.