Count Distinct Values per Group in pandas

"How many customers bought in each region?" is not the same as "how many orders were there?", and it is the first question almost every sales report asks. In a classic Excel pivot table, the only way to get a distinct count is to add the data to the Data Model; the everyday workaround is a helper column of COUNTIFS flags. In pandas it is a single nunique — with two properties worth understanding before a report is built on it: distinct counts depend on how clean the values are, and they do not add up across groups. This page, part of Creating Pivot Tables from Excel Data with Pandas, covers both.

Prerequisites

pip install pandas openpyxl

orders.xlsx has nine orders over two months and two regions. One customer ID is stored as " c2" with a space and lower case, and one order has no customer at all.

Step 1 — Count distinct values per group with nunique

nunique on a grouped column returns the number of different values in each group, next to the row count for comparison:

import pandas as pd

orders = pd.read_excel("orders.xlsx")
summary = orders.groupby("region").agg(orders=("customer", "size"), customers=("customer", "nunique"))
print(summary)

size counts rows, including the one with no customer; nunique counts different customer IDs. North shows 5 orders and — for now — 3 customers, one more than it really has, because "C2" and " c2" are different strings. The next step fixes that.

Step 2 — Normalise values before counting

Distinct counts are exact comparisons, so every spelling variant is a separate value. Clean the key column first — strip spaces, fix case — and count on the cleaned version:

orders["customer_key"] = orders["customer"].str.strip().str.upper()
summary = orders.groupby("region").agg(orders=("customer", "size"),
                                       customers=("customer_key", "nunique"))
print(summary)

North now has two customers, C1 and C2. For keys with more variation — names rather than IDs — the same idea applies with a stronger normaliser; Standardise Category Values with a Mapping Table in pandas shows one. Keep the original column untouched and count on a derived key, so the cleaning is visible.

Step 3 — Decide whether blanks count

nunique ignores missing values by default. The South order with no customer contributes nothing to South's distinct count — correct if a blank is simply missing data, misleading if "unknown customer" is something the report should surface:

print(orders.groupby("region")["customer_key"].nunique().to_dict())
print(orders.groupby("region")["customer_key"].nunique(dropna=False).to_dict())
print("orders with no customer:", orders["customer_key"].isna().sum())

With dropna=False, South counts the blank as one more distinct value. Usually the better report does neither: count real customers with the default, and show the number of orders with no customer as a separate data quality line, so the gap is visible without distorting the customer count.

Step 4 — Several distinct counts at once

Named aggregation computes several distinct counts, and other statistics, in one pass — customers, products and revenue per region and month:

monthly = (orders.groupby(["month", "region"])
                 .agg(customers=("customer_key", "nunique"),
                      products=("product", "nunique"),
                      orders=("amount", "size"),
                      revenue=("amount", "sum"))
                 .reset_index())
print(monthly.to_string(index=False))

For a pivot-style layout — months across, regions down — pivot_table accepts "nunique" as the aggregation:

grid = pd.pivot_table(orders, index="region", columns="month", values="customer_key", aggfunc="nunique", fill_value=0)
print(grid)

That is the pandas version of an Excel pivot table with a Distinct Count value field, without the Data Model. Use Different Aggregations per Column in a pandas Pivot covers mixing functions in one pivot.

Step 5 — Do not add distinct counts across groups

Distinct counts are not additive. A customer who buys in both months counts once in each month but only once for the whole period, so summing monthly counts overstates the total:

by_month = orders.groupby("month")["customer_key"].nunique()
print(by_month.to_dict(), "sum:", by_month.sum())
print("distinct customers overall:", orders["customer_key"].nunique())

January has 3 customers and February 4, which sum to 7 — but there are only 4 different customers across both months, because C1, C2 and C3 bought in each. Totals in a distinct-count report must be computed from the underlying data, never by summing the group figures. The same applies to subtotal rows: pivot_table(..., margins=True) recomputes the margin from the data for each row and column, which is correct, while a hand-built total row that sums the cells is not.

Step 6 — New and returning customers

Once distinct counts are right, the next question is usually which customers are new. Find each customer's first month, then count distinct customers by whether the order month is their first:

first_month = orders.dropna(subset=["customer_key"]).groupby("customer_key")["month"].min()
orders["first_month"] = orders["customer_key"].map(first_month)
orders["status"] = (orders["month"] == orders["first_month"]).map({True: "new", False: "returning"})
cohort = (orders.dropna(subset=["customer_key"])
                .groupby(["month", "status"])["customer_key"].nunique()
                .unstack(fill_value=0))
print(cohort)

In February, three customers were returning and one — C4 — was new. The new and returning counts within a month do add up to that month's distinct total, because every customer has exactly one status per month; it is only across months that distinct counts stop being additive.

Active customers over a rolling window

"Active customers" is usually defined over a window — anyone who ordered in the last two, three or twelve months. Because distinct counts cannot be added, a rolling distinct count cannot be built by summing monthly figures either; count over each window's rows directly:

months = sorted(orders["month"].unique())
active = {}
for i, m in enumerate(months):
    window = months[max(0, i - 1): i + 1]                    # this month and the one before
    active[m] = orders.loc[orders["month"].isin(window), "customer_key"].nunique()
print(active)

February's two-month window has 4 active customers — the same four who appear anywhere in January or February — not the 7 you would get by adding the monthly counts. For long histories, the same idea scales by keeping one row per customer and month (drop_duplicates) before looping, so each window counts a small table rather than every order.

A distinct count the workbook can recalculate

When readers need a distinct count that updates as they edit the sheet, write a formula rather than a number. The classic version works in every Excel release: each value contributes one over the number of times it appears, so a value appearing three times adds three thirds — one:

from openpyxl import load_workbook

orders[["customer_key"]].to_excel("keys.xlsx", index=False)
wb = load_workbook("keys.xlsx")
ws = wb.active
last = ws.max_row
ws["C1"] = "distinct customers"
ws["C2"] = f'=SUMPRODUCT((A2:A{last}<>"")/COUNTIF(A2:A{last},A2:A{last}&""))'
wb.save("keys.xlsx")
print(ws["C2"].value)

The (A2:A10<>"") term skips blanks, and appending &"" to the criteria stops COUNTIF from returning zero for empty cells, which would otherwise make the formula divide by zero. Recalculated in LibreOffice, the formula gives the same 4 as nunique. In Microsoft 365, =COUNTA(UNIQUE(FILTER(A2:A10,A2:A10<>""))) reads more naturally — Write Dynamic Array Formulas like FILTER and UNIQUE from Python covers writing those — but the SUMPRODUCT form also works for anyone on an older version. Either way, the formula depends on the cleaned key column: it counts spelling variants as separate values just as nunique does.

Write the report

Write the summary with its correctly computed totals:

with pd.ExcelWriter("customer_counts.xlsx") as writer:
    monthly.to_excel(writer, sheet_name="By month and region", index=False)
    grid.to_excel(writer, sheet_name="Customers grid")
    pd.DataFrame({"distinct customers": [orders["customer_key"].nunique()],
                  "orders without customer": [int(orders["customer_key"].isna().sum())]}).to_excel(
        writer, sheet_name="Totals", index=False)
print(pd.ExcelFile("customer_counts.xlsx").sheet_names)

Common pitfalls

• Counting on raw IDs. Case and spacing variants inflate the count; normalise first.
• Summing distinct counts. Compute totals from the data.
• Silent blanks. nunique ignores them; report orders without a key separately.
• count() for customers. It counts non-missing rows, which is orders, not customers.

Performance notes

groupby().nunique() is optimised and handles millions of rows quickly, though it is slower than sum or count because it has to track values per group. For very large data with few groups, drop_duplicates(["region", "customer_key"]) followed by groupby("region").size() is an equivalent and often faster formulation.

Conclusion

Count distinct values per group with groupby(...)[col].nunique(), on a normalised key so spelling variants do not inflate the count. Decide explicitly how blanks are treated, use named aggregation or pivot_table(aggfunc="nunique") for several counts at once, and never sum distinct counts across groups — compute totals from the underlying rows. The same care gives correct new-versus-returning counts.

Frequently asked questions

How do I count unique values per group in pandas? Group by the region and call nunique on the customer column. It returns the number of different customers in each region, the same as a pivot table's Distinct Count.

Does nunique count blank cells? No. By default missing values are ignored. Pass dropna=False to count "missing" as one more distinct value — useful when a blank customer is itself something the report should show.

Why do my distinct counts per region not add up to the total? Because the same value can appear in several groups. A customer who bought in North and South counts once in each region but only once in the company total. Distinct counts are not additive; compute the total separately.

Why is "C2" counted twice? Probably because it is stored with different case or spacing, such as "C2" and " c2". nunique compares values exactly, so normalise with str.strip() and str.upper() before counting.

Related

• Up one level: Creating Pivot Tables from Excel Data with Pandas — pivots and summaries.
• COUNTIF and COUNTIFS Equivalent in pandas — conditional counts of rows.
• Pandas: Drop Duplicates From an Excel Column — removing the repeats instead of counting around them.
• UNIQUE, SORT and FILTER Equivalents in pandas — listing the distinct values themselves.