Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Get the Top N Rows per Group in pandas

List the three best-selling products in each region, the largest invoice per customer, or the top five reps per quarter. Use sort_values with groupby().head(), decide what happens to ties with rank, rank aggregated totals rather than raw rows, and fold everything else into an "Other" line.
Pivots & summariespandasExcel functions in pandas

"The top three products in each region" is one of the most requested views in sales reporting, and one of the easiest to get subtly wrong. Excel users build it with LARGE and INDEX/MATCH, or a pivot table filtered to the top 10 with the "Top 10" value filter per row field. In pandas it is a sort and a groupby().head() — but the answer depends on three decisions that the one-liner makes silently: what happens to ties at the cut-off, whether you rank rows or totals, and whether the rest of the data disappears or is shown as "Other". This page, part of Creating Pivot Tables from Excel Data with Pandas, makes each decision explicit.

The top two products in each region A sales table sorted by region and sales. The top two rows of each region are highlighted: North Widgets 900 and Gadgets 700 (Gizmos is tied on 700 but falls outside head(2)), South Gadgets 650 and Widgets 400, and East Gizmos 500, the only product there. sort_values + groupby("region").head(2) A B C 1 region product sales 2 East Gizmos 500 3 North Widgets 900 4 North Gadgets 700 5 North Gizmos 700 6 North Doohickeys 300 7 South Gadgets 650 8 South Widgets 400 9 South Widgets 350

Prerequisites

Bash
pip install pandas openpyxl

sales.xlsx has ten rows across three regions. North has a tie on 700 between Gadgets and Gizmos; South lists Widgets twice, as two separate orders; East has a single product.

Step 1 — Sort, then take the first N of each group

Sort by the group ascending and the value descending, so each group's largest rows come first, then keep the first N rows of every group:

Python
import pandas as pd

sales = pd.read_excel("sales.xlsx")
top2 = (sales.sort_values(["region", "sales"], ascending=[True, False])
             .groupby("region")
             .head(2))
print(top2.to_string(index=False))

head(2) keeps at most two rows per region: East keeps its only row, North keeps Widgets and Gadgets, South keeps Gadgets and one of its Widgets rows. Unlike groupby().nlargest(), head keeps every column and returns an ordinary DataFrame, which makes it the most convenient form for reports. Groups smaller than N simply return all their rows.

Step 2 — Notice what happened to the tie

North's Gadgets and Gizmos both sold 700, and head(2) kept Gadgets and dropped Gizmos. Nothing in the data justifies that: the sort put Gadgets first because it happened to come first in the file. For a leaderboard, arbitrary tie-breaking is a problem — the same data sorted differently next week produces a different "top two". Either break ties deliberately with a second sort key, or include every tied row:

Python
sales["rank"] = sales.groupby("region")["sales"].rank(method="min", ascending=False)
with_ties = sales[sales["rank"] <= 2].sort_values(["region", "rank"])
print(with_ties.to_string(index=False))

rank(method="min") gives tied values the same, lowest rank — both 700s are rank 2 — so filtering on rank <= 2 keeps North's top three rows. This is how Excel's own "Top 10" filter behaves, and the same rule as RANK.EQ. RANK and PERCENTILE Formulas in pandas compares the ranking methods.

What each method returns for North's tie at 700 A comparison of three methods for the top two in North, where Gadgets and Gizmos tie on 700. head(2) after sorting returns Widgets and Gadgets, dropping Gizmos by file order. A second sort key such as product name returns Widgets and whichever product sorts first. rank with method min and rank <= 2 returns Widgets, Gadgets and Gizmos. Rows returned Tie decided by head(2) after sorting Widgets, Gadgets file order (arbitrary) head(2), sort by sales then product Widgets, Gadgets alphabetical order rank(method="min") <= 2 Widgets, Gadgets, Gizmos ties all kept

Step 3 — Break ties with a second key when exactly N are needed

Sometimes the report must show exactly N rows — a fixed-size table on a dashboard. Then add a second sort key that means something: more units, more recent, alphabetical as a last resort. Stable ordering makes the output reproducible:

Python
exact = (sales.sort_values(["region", "sales", "product"], ascending=[True, False, True], kind="stable")
              .groupby("region")
              .head(2))
print(exact[["region", "product", "sales"]].to_string(index=False))

The tie in North is now decided alphabetically — Gadgets before Gizmos — every time the report runs, whatever order the rows arrive in. Say so in a footnote if readers might wonder why one of two equal products was left out.

Step 4 — Rank totals, not rows, when items repeat

South lists Widgets twice: 400 and 350, two orders. "Top products in South" is a question about products, so the two rows should be combined before ranking. Ranking raw rows put Widgets in the top two with a single 400 order, which understates it and could, with more duplicates, list the same product twice:

Python
totals = sales.groupby(["region", "product"], as_index=False)["sales"].sum()
top_products = (totals.sort_values(["region", "sales", "product"], ascending=[True, False, True])
                      .groupby("region")
                      .head(2))
print(top_products.to_string(index=False))

After aggregation, South's Widgets total 750 and rank first, above Gadgets at 650 — a different answer from the row-level ranking. Decide which question the report answers — "largest orders" or "best products" — and aggregate accordingly.

Step 5 — Fold the rest into "Other"

A top-N table drops everything below the cut-off, so its numbers no longer add up to the region's total. Readers comparing it with another report notice immediately. Keep the total intact by relabelling everything outside the top N as "Other" and grouping again:

Python
totals["rank"] = totals.groupby("region")["sales"].rank(method="first", ascending=False)
totals["label"] = totals["product"].where(totals["rank"] <= 2, "Other")
summary = totals.groupby(["region", "label"], as_index=False)["sales"].sum()
print(summary.to_string(index=False))
print(summary.groupby("region")["sales"].sum().to_dict() == sales.groupby("region")["sales"].sum().to_dict())

North shows its top two products and an "Other" line holding the remaining 1,100; every region's lines still sum to the region's total, and the final check confirms it. method="first" gives each product a distinct rank so that exactly two are named per region; use "min" if tied products should both be named.

North's sales as top two plus Other Bars for North after folding the rest into Other: Widgets 900, Gadgets 700, and Other 1,100 made up of Gizmos 700, Doohickeys 300 and Whatsits 100. The three bars add to North's total of 2,700. North — total 2,700 Widgets 900 Gadgets 700 Other (3 products) 1100

Lay the leaders out side by side

Dashboards often show the top N as a compact grid — one row per region, one column per rank — rather than a long list. Number the rows within each group with cumcount and pivot:

Python
board = top_products.assign(position=top_products.groupby("region").cumcount() + 1)
board["entry"] = board["product"] + " (" + board["sales"].astype(str) + ")"
leaderboard = board.pivot(index="region", columns="position", values="entry")
leaderboard.columns = [f"#{c}" for c in leaderboard.columns]
print(leaderboard.fillna("").to_string())

East has only one product, so its #2 cell is empty — filled with an empty string for display rather than NaN. Because top_products came from the tie-broken sort in Step 4, the columns are stable from run to run. The grid fits naturally at the top of a summary sheet, with the detailed table further down.

Step 6 — Write one sheet with a block per group

A report usually wants the top N for every group in one place. Write the summary with a blank row between regions, which reads better than one long table:

Python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Top products"
ws.append(["Region", "Product", "Sales"])
for region, block in summary.groupby("region"):
    for row in block.sort_values("sales", ascending=False).itertuples(index=False):
        ws.append([row.region, row.label, row.sales])
    ws.append([])
wb.save("top_products.xlsx")
print(ws.max_row, "rows written")

Sorting "Other" to its natural position by value keeps the block readable; if "Other" should always come last, sort by a key that puts it there. Add a Native Excel Pivot Table with Python is the alternative when readers want to change N themselves in Excel.

Common pitfalls

  • Forgetting to sort before head. groupby().head(n) returns the first n rows in the current order, not the largest.
  • Silent tie-breaking. Include ties with rank(method="min"), or break them with a meaningful second key.
  • Ranking rows when items repeat. Aggregate first when the question is about products, customers or reps.
  • Top-N tables that do not add up. Add an "Other" line so totals reconcile.
  • Ranking before filtering. If the report covers one quarter, filter to that quarter first; ranking the whole year and then filtering keeps products that were top for the year but not in the period.
  • Missing group labels. Rows with a blank region are dropped by groupby; label them first or they never appear in any top list.

Performance notes

Sorting once and taking head per group is fast for millions of rows. groupby().rank() is also vectorised. groupby().nlargest(n) works on a single Series and returns a MultiIndex that needs joining back to the other columns — convenient for quick checks, less so for reports.

Conclusion

Sort by group and value, then groupby().head(n) for the top rows per group. Decide ties deliberately — rank(method="min") <= n to include them, a second sort key to break them — aggregate first when items repeat so you rank totals rather than rows, and fold the rest into an "Other" line so the numbers still add up to each group's total.

Frequently asked questions

What is the simplest way to get the top 3 rows per group? Sort by the group, then by the value in descending order, and take the first three rows of each group with groupby("region").head(3). The sort is what makes the first rows the largest.

How are ties handled? head(n) returns exactly n rows, so a tie at the cut-off is broken by sort order, which can be arbitrary. Use rank(method="min") and keep rows with rank <= n to include every tied row, or add a second sort column to break ties deliberately.

Should I rank rows or totals? Rank whatever the question is about. If a product appears on several rows per region, "top products" means the top summed totals, so aggregate with groupby().sum() first and rank the result.

How do I show the rest as "Other"? Mark the top N rows, replace the label of every other row with "Other", and group again. The totals then still add up to the full amount, which a plain top-N list does not.

More on pivots & summaries

More on pivots & summaries →