Introduction

SQL users often look for a direct pandas equivalent of COUNT(DISTINCT ...). The closest method is nunique, but real analytics tasks usually need grouped counts, tuple-level uniqueness, conditional filters, and explicit null handling. Knowing which pattern matches your metric definition prevents subtle reporting errors and helps keep pipelines fast.

Direct Equivalent for One Column

For a single field, use Series.nunique().

1import pandas as pd
2
3df = pd.DataFrame({
4    "user_id": [1, 1, 2, 3, 3, 4],
5    "country": ["US", "US", "CA", "US", None, "FR"]
6})
7
8print(df["user_id"].nunique())              # 4
9print(df["country"].nunique(dropna=True))   # 3
10print(df["country"].nunique(dropna=False))  # 4, None counted as a distinct state

dropna is important. In many business dashboards, missing values are either ignored or mapped to a named bucket such as UNKNOWN. Pick one rule and apply it consistently.

Grouped Distinct Counts

SQL pattern:

SELECT country, COUNT(DISTINCT user_id)
FROM events
GROUP BY country;

Pandas equivalent:

1result = (
2    df.groupby("country", dropna=False)["user_id"]
3      .nunique()
4      .reset_index(name="distinct_users")
5)
6print(result)

If your group keys contain missing values and you need them in output, keep dropna=False on groupby. Many teams forget this and undercount segments with incomplete dimensions.

Distinctness Across Multiple Columns

Sometimes uniqueness is defined by a tuple, not one column. Example: distinct pairs of user and device.

1sessions = pd.DataFrame({
2    "user_id": [10, 10, 10, 11, 11],
3    "device": ["ios", "ios", "web", "web", "android"]
4})
5
6pair_count = sessions[["user_id", "device"]].drop_duplicates().shape[0]
7print(pair_count)  # 4

Grouped tuple distinct count, such as distinct user-device pairs per day:

1sessions["day"] = pd.to_datetime([
2    "2026-03-01", "2026-03-01", "2026-03-01", "2026-03-02", "2026-03-02"
3]).date
4
5tmp = sessions[["day", "user_id", "device"]].drop_duplicates()
6out = tmp.groupby("day").size().reset_index(name="distinct_user_device_pairs")
7print(out)

This mirrors SQL where distinctness spans multiple columns.

Conditional Distinct Metrics

For filtered metrics, apply the condition first and then count distinct values.

1orders = pd.DataFrame({
2    "store": ["A", "A", "A", "B", "B", "B"],
3    "user_id": [1, 1, 2, 3, 4, 4],
4    "status": ["paid", "canceled", "paid", "paid", "paid", "canceled"]
5})
6
7paid = orders[orders["status"] == "paid"]
8paid_distinct_users = paid.groupby("store")["user_id"].nunique()
9print(paid_distinct_users)

This approach keeps business logic easy to read and test. Avoid packing all logic into one dense expression that is hard to audit.

Multiple Distinct Metrics in One Aggregation

When you need several distinct counts together, named aggregation keeps code compact.

1summary = (
2    orders.groupby("store")
3          .agg(
4              distinct_users=("user_id", "nunique"),
5              distinct_statuses=("status", "nunique")
6          )
7          .reset_index()
8)
9print(summary)

This is usually cleaner than repeated groupby calls and reduces accidental mismatch between metric definitions.

Performance Practices for Large Frames

Distinct counting can be expensive on high-cardinality columns. Useful tactics:

• select only needed columns before deduplication,
• avoid repeated drop_duplicates on the same subset,
• use categorical dtype for low-cardinality strings,
• benchmark alternatives on realistic data volume.

1large = orders.copy()
2large["store"] = large["store"].astype("category")
3
4metric = large.groupby("store", observed=True)["user_id"].nunique()
5print(metric)

observed=True can reduce unnecessary category combinations in grouped outputs.

Validating Metric Semantics

Distinct counts often power billing, activation, and retention dashboards, so tests matter. Create small fixture dataframes with expected results and assert the output.

expected = {"A": 2, "B": 2}
actual = orders.groupby("store")["user_id"].nunique().to_dict()
assert actual == expected

A few focused tests catch regressions when someone later changes filter rules, joins, or null handling.

Common Pitfalls

• Assuming nunique includes missing values by default.
• Counting one column when business uniqueness actually depends on multiple columns.
• Forgetting dropna=False on group keys that contain null values.
• Recomputing expensive deduplication steps repeatedly in the same pipeline.
• Shipping distinct metrics without tests that lock expected behavior.

Summary

• 'nunique is the main pandas equivalent of SQL distinct count.'
• Combine groupby with nunique for segmented distinct metrics.
• Use drop_duplicates when distinctness is tuple-based across columns.
• Filter first for conditional metrics so business rules stay explicit.
• Define null semantics and test them to keep reporting stable.