Count Distinct Values in Pandas: nunique(), unique(), value_counts()

To count distinct values in pandas, call .nunique() — it is the direct equivalent of SQL's COUNT(DISTINCT col) and works on a Series, a whole DataFrame, or after a groupby(). Its two siblings answer adjacent questions: .unique() returns the distinct values themselves, and .value_counts() tells you how many times each one appears.

How do I count distinct values in a pandas column?

Call .nunique() on the Series. It returns a single integer — the number of distinct non-null values. Called on a whole DataFrame, it returns one distinct count per column, which is a great first move when profiling an unfamiliar dataset.

import pandas as pd

df = pd.DataFrame({
    "customer": ["amy", "ben", "amy", "cara", "ben", "amy"],
    "product":  ["A", "B", "A", "C", None, "B"],
    "region":   ["east", "east", "west", "west", "east", "east"],
})

print(df["customer"].nunique())   # 3
print(df.nunique())
# customer    3
# product     3
# region      2
# dtype: int64

Does nunique() count NaN values?

No — by default nunique() ignores missing values, exactly like SQL's COUNT(DISTINCT) ignores NULLs. Pass dropna=False to count NaN as its own distinct value. This one parameter explains most "why is my count off by one?" moments.

print(df["product"].nunique())              # 3  (A, B, C)
print(df["product"].nunique(dropna=False))  # 4  (A, B, C, NaN)

unique() behaves differently: it keeps NaN in its output array. So len(s.unique()) and s.nunique() disagree whenever missing values exist — a classic source of silent off-by-one bugs:

print(df["product"].unique())        # ['A' 'B' 'C' None]
print(len(df["product"].unique()))   # 4
print(df["product"].nunique())       # 3 — not the same!

How do I count distinct values per group?

Chain nunique() after a groupby(). This is the pandas spelling of SELECT g, COUNT(DISTINCT col) ... GROUP BY g and it is the pattern behind most "unique customers per region" style metrics. You can also mix it with other aggregations via .agg().

print(df.groupby("region")["customer"].nunique())
# region
# east    3
# west    2
# Name: customer, dtype: int64

# Several aggregations at once
print(df.groupby("region").agg(
    customers=("customer", "nunique"),
    rows=("customer", "size"),
))
#         customers  rows
# region
# east            3     4
# west            2     2

New to grouping? Our pandas groupby guide covers the split-apply-combine model these one-liners rely on.

value_counts() vs unique() vs nunique(): which one do I need?

Pick by the question you are answering. nunique() answers "how many distinct values?" with an integer. unique() answers "what are they?" with a NumPy array in order of first appearance. value_counts() answers "how often does each occur?" with a Series sorted by frequency — and len(s.value_counts()) equals s.nunique().

s = df["customer"]

print(s.nunique())        # 3
print(s.unique())         # ['amy' 'ben' 'cara']
print(s.value_counts())
# customer
# amy     3
# ben     2
# cara    1
# Name: count, dtype: int64

If you find yourself reaching for value_counts() often, it has useful knobs of its own (normalize=True for percentages, dropna=False, binning) — see pandas value_counts() explained.

What is the SQL COUNT DISTINCT equivalent in pandas?

Every distinct-counting idiom in SQL has a direct pandas translation. If you are coming from SQL, this table is the whole story — the semantics line up almost exactly, including the NULL/NaN exclusion rule.

SQL	pandas
COUNT(DISTINCT col)	df["col"].nunique()
COUNT(DISTINCT col) counting NULL too	df["col"].nunique(dropna=False)
SELECT DISTINCT col	df["col"].unique()
SELECT DISTINCT a, b	df[["a", "b"]].drop_duplicates()
SELECT col, COUNT(*) ... GROUP BY col	df["col"].value_counts()
SELECT g, COUNT(DISTINCT col) ... GROUP BY g	df.groupby("g")["col"].nunique()
COUNT(DISTINCT a, b) (multi-column)	df[["a", "b"]].drop_duplicates().shape[0]

For the SQL side of this table — including how different databases treat NULLs and multi-column distincts — see COUNT DISTINCT in SQL.

How do I count distinct combinations of multiple columns?

Neither nunique() nor unique() works across columns directly — df[["a", "b"]].nunique() counts each column separately, not the pairs. To count distinct combinations, drop duplicates on the column subset and take the length, or use value_counts() on multiple columns to see each combination's frequency.

# Distinct (customer, region) pairs
n_pairs = len(df[["customer", "region"]].drop_duplicates())
print(n_pairs)   # 4

# Frequency of each combination
print(df.value_counts(["customer", "region"]))
# customer  region
# amy       east      2
# ben       east      2
# amy       west      1
# cara      west      1
# Name: count, dtype: int64

This is the pandas answer to SQL's COUNT(*) FROM (SELECT DISTINCT a, b ...) subquery pattern, and df.value_counts([...]) doubles as a quick duplicate-key detector: any combination with a count above 1 will inflate a later join.