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

Number Rows Within Each Group in pandas

Add a running number that restarts for every customer, region or order — the pandas equivalent of Excel's COUNTIF($A$2:A2,A2) and SQL's ROW_NUMBER() OVER (PARTITION BY ...): cumcount in a chosen order, ranks that handle ties, numbering consecutive runs, numbering the groups themselves, and writing the result to Excel.
Pivots & summariesExcel functions in pandas

"Which order is this for the customer — their first, second, fifth?" "Number the lines of each invoice." "Keep only the latest reading per sensor." All of these need a number that counts rows within a group and restarts at 1 for the next group. In Excel the usual trick is =COUNTIF($A$2:A2, A2) filled down a sorted sheet; in SQL it is ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...). In pandas it is one method, cumcount, plus a few decisions about order and ties that this page makes explicit. It is part of Creating Pivot Tables from Excel Data.

Prerequisites

Bash
pip install pandas openpyxl

orders.xlsx has eleven orders from three customers, in the order they were exported, which is not date order. Birch placed two orders on the same day, and several orders share amounts, which matters for ranking.

A number that restarts for every customer On the left orders sorted by customer and date. On the right the same rows with an order number per customer that runs 1, 2, 3, 4 for Alder and restarts at 1 for Birch. Customer OrderDate Alder 2026-01-15 Alder 2026-01-28 Alder 2026-02-27 Birch 2026-01-30 Birch 2026-02-10 Customer OrderNo Alder 1 Alder 2 Alder 3 Birch 1 Birch 2 before after cumcount() + 1

Step 1 — Sort, then cumcount

groupby(...).cumcount() numbers each row within its group, starting at 0, in the order the rows appear. The order is the whole point, so sort first; + 1 makes it start at 1 like Excel's and SQL's:

Python
import pandas as pd

orders = pd.read_excel("orders.xlsx", parse_dates=["OrderDate"])
orders = orders.sort_values(["Customer", "OrderDate", "OrderID"]).reset_index(drop=True)
orders["OrderNo"] = orders.groupby("Customer").cumcount() + 1
print(orders.to_string(index=False))

Each customer's orders now run 1, 2, 3… in date order: Alder's four orders from January to March, Birch's four, Cedar's three. OrderID in the sort key breaks the tie between Birch's two orders on 20 April, so the numbering is the same every time the script runs. Without a tie-breaker, rows with equal sort keys keep whatever order they arrived in, which can change between exports.

Descending order gives "most recent first", and keeping number 1 is the common "latest row per group" pattern:

Python
latest = (orders.sort_values(["Customer", "OrderDate", "OrderID"], ascending=[True, False, False])
                .assign(Recency=lambda d: d.groupby("Customer").cumcount() + 1))
print(latest.loc[latest["Recency"] == 1, ["Customer", "OrderID", "OrderDate"]].to_string(index=False))

Step 2 — Rank instead of counting when ties matter

cumcount always gives distinct numbers, even to rows that are equal on the sort key. When equal values should share a position, as in "rank each customer's orders by amount", use rank with the tie rule you need:

Python
by_amount = orders[orders["Customer"] == "Birch"][["OrderID", "Amount"]].copy()
g = orders[orders["Customer"] == "Birch"].groupby("Customer")["Amount"]
for method in ("first", "min", "dense"):
    by_amount[method] = g.rank(method=method, ascending=False).astype(int)
print(by_amount.sort_values("Amount", ascending=False).to_string(index=False))

Birch has three orders of 480. first numbers them 1, 2, 3 in row order, like cumcount; min gives all three rank 1 and the next order rank 4, like Excel's RANK.EQ and sports tables; dense gives them 1 and the next order 2, with no gaps. Choose the one whose meaning matches the question; for "top 3 orders per customer", min can return more than three rows, which is often correct.

Three ways to number tied values A table of Birch's orders by amount with three rank methods. The three orders of 480 get 1, 2, 3 with first, all get 1 with min and dense, and the order of 130 gets 4 with first and min but 2 with dense. Amount first min dense 480 1 1 1 480 2 1 1 480 3 1 1 130 4 4 2

Step 3 — Restart the count on consecutive runs

Sometimes the group is not a column value but a run of consecutive rows: "how many orders in a row above 200", or a status that restarts its count each time it changes. Build a run ID that increases whenever the value differs from the previous row, then count within runs:

Python
seq = orders[orders["Customer"] == "Alder"].copy()
seq["Big"] = seq["Amount"] >= 150
seq["Run"] = (seq["Big"] != seq["Big"].shift()).cumsum()
seq["InRun"] = seq.groupby("Run").cumcount() + 1
print(seq[["OrderDate", "Amount", "Big", "Run", "InRun"]].to_string(index=False))

The comparison with shift() is True at the first row of each run, and the cumulative sum turns those into run IDs 1, 2, 3. The same pattern finds streaks in sensor readings, consecutive late deliveries or unbroken months of growth.

Step 4 — "3 of 4": counting from the end and the group size

Two companions of the running number answer the other common questions. cumcount(ascending=False) counts from the end of each group, so 0 marks the last row, which flags "the customer's most recent order" without a second sort. transform("size") puts the group's row count on every row, which gives labels like "order 3 of 4" and lets you filter customers with at least three orders:

Python
g = orders.groupby("Customer")
orders["FromEnd"] = g.cumcount(ascending=False)
orders["OrdersTotal"] = g["OrderID"].transform("size")
orders["Label"] = "order " + orders["OrderNo"].astype(str) + " of " + orders["OrdersTotal"].astype(str)
orders["IsLatest"] = orders["FromEnd"] == 0
print(orders[["Customer", "OrderDate", "Label", "IsLatest"]].to_string(index=False))

These are the building blocks of most "per customer" logic in reports: first order (OrderNo == 1), latest order (FromEnd == 0), repeat customers (OrdersTotal >= 2), and the gap between consecutive orders, which is g["OrderDate"].diff() on the same sorted frame.

The running number and its companions A table of Alder's four orders with the running number, the count from the end, the group size and a label. The first order has number 1 and 3 from the end; the last has number 4, 0 from the end, and is flagged as the latest. OrderDate OrderNo FromEnd OrdersTotal Label 2026-01-15 1 3 4 order 1 of 4 2026-01-28 2 2 4 order 2 of 4 2026-02-27 3 1 4 order 3 of 4 2026-03-15 4 0 4 order 4 of 4

Step 5 — Number the groups themselves

cumcount numbers rows within groups; ngroup numbers the groups, which is useful as a compact key, as a stable ID to share instead of customer names, or for alternating row shading by customer in the output:

Python
orders["CustomerNo"] = orders.groupby("Customer", sort=True).ngroup() + 1
print(orders[["Customer", "CustomerNo"]].drop_duplicates().to_string(index=False))

Step 6 — Write it to Excel, as values or as formulas

Usually the computed numbers are written as values. If the sheet is going to be sorted, filtered and extended by hand, a formula that recomputes may be preferred. The Excel equivalent of cumcount() + 1 on a sheet sorted by customer is COUNTIF over an expanding range:

Python
from openpyxl import load_workbook

out = orders[["Customer", "OrderDate", "OrderID", "Amount", "OrderNo"]]
out.to_excel("orders_numbered.xlsx", index=False)
wb = load_workbook("orders_numbered.xlsx")
ws = wb.active
ws["F1"] = "OrderNo (formula)"
for r in range(2, ws.max_row + 1):
    ws[f"F{r}"] = f"=COUNTIF($A$2:A{r},A{r})"
wb.save("orders_numbered.xlsx")
Bash
soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to xlsx --outdir recalculated orders_numbered.xlsx > /dev/null 2>&1
ls recalculated
Python
check = pd.read_excel("recalculated/orders_numbered.xlsx")
print("formula matches pandas:", (check["OrderNo"] == check["OrderNo (formula)"]).all())

The formula counts matches from the top of the column down to the current row, so it only numbers in date order if the sheet stays sorted by customer and date; re-sort the sheet and the formula renumbers in the new order, which may or may not be what the reader wants. On large sheets COUNTIF over expanding ranges is slow, since each row scans all rows above it; values from pandas avoid that.

Performance

cumcount is vectorised and scales to large tables; the expensive part is the sort, not the numbering. A quick measurement on a million rows spread over 50,000 customers:

Python
import time
import numpy as np

rng = np.random.default_rng(0)
big = pd.DataFrame({"Customer": rng.integers(0, 50_000, 1_000_000),
                    "OrderDate": pd.Timestamp("2025-01-01") + pd.to_timedelta(rng.integers(0, 600, 1_000_000), unit="D")})
t0 = time.perf_counter()
big = big.sort_values(["Customer", "OrderDate"], kind="stable")
t1 = time.perf_counter()
big["OrderNo"] = big.groupby("Customer").cumcount() + 1
t2 = time.perf_counter()
print(f"sort {t1 - t0:.2f} s, cumcount {t2 - t1:.2f} s, max orders per customer {big['OrderNo'].max()}")

Both steps take a fraction of a second here: roughly a tenth of a second to sort and a few hundredths to number, on the machine used for this page. The Excel COUNTIF version of the same numbering compares every row with every row above it, so its work grows with the square of the row count; that is why the formula approach is fine for a few thousand rows and painful for a few hundred thousand, and why the numbers are better computed in pandas and written as values for large sheets.

Common pitfalls

  • Not sorting first. cumcount follows row order; sort by group and the ordering column.
  • No tie-breaker. Add a unique column to the sort so the numbering is reproducible.
  • Using cumcount for rankings with ties. Use rank(method=...) when equal values should share a position.
  • Forgetting + 1. cumcount starts at 0.
  • Resetting the index too early. Keep the original index if you need to join the numbers back to the unsorted data; cumcount returns values aligned to it.

Conclusion

Sort by the group and the ordering columns, including a tie-breaker, and use groupby(...).cumcount() + 1 for a row number that restarts per group: the pandas version of ROW_NUMBER() OVER (PARTITION BY ...) and of COUNTIF($A$2:A2, A2). Use rank with first, min or dense when ties should share positions, a shift-and-cumsum run ID to restart on consecutive runs, and ngroup to number the groups themselves.

Frequently asked questions

How do I number rows within groups in pandas? Sort the DataFrame, then use df.groupby("group").cumcount() + 1. The count restarts at 1 for each group and follows the row order.

What is the pandas equivalent of SQL ROW_NUMBER() OVER (PARTITION BY ...)? groupby(partition columns).cumcount() + 1 after sorting by the ORDER BY columns. For RANK and DENSE_RANK, group the column and call rank with method="min" or method="dense".

How do I get the latest row per group? Sort by the date descending, number rows with cumcount, and keep rows where the number is 1. Alternatively use groupby(...).head(1) after sorting.

What is the Excel formula for a running count per group? =COUNTIF($A$2:A2,A2) in row 2, filled down a sheet sorted by the group column. It counts how many times the current value has appeared so far.

More on pivots & summaries

More on pivots & summaries →