Introduction

In pandas, the SQL idea of SELECT DISTINCT col1, col2 usually maps to drop_duplicates on a selected subset of columns. The important detail is deciding whether you want unique row combinations across several columns or unique values pooled from those columns, because those are different problems.

Get Distinct Row Combinations with drop_duplicates

If you want the unique combinations of several columns, select those columns and call drop_duplicates().

1import pandas as pd
2
3df = pd.DataFrame(
4    {
5        "city": ["Toronto", "Toronto", "Montreal", "Toronto"],
6        "fruit": ["apple", "apple", "pear", "banana"],
7        "color": ["red", "red", "green", "yellow"],
8    }
9)
10
11result = df[["city", "fruit"]].drop_duplicates()
12print(result)

This is the closest pandas equivalent to a SQL distinct query over multiple columns.

Keep Other Columns Only If You Mean To

If you call drop_duplicates on the full DataFrame, pandas checks uniqueness across all columns.

full_distinct = df.drop_duplicates()
print(full_distinct)

That is different from checking uniqueness only across a subset. If your real goal is distinct pairs of city and fruit, passing the full DataFrame may keep extra rows you expected to collapse because another column still differs.

Use the subset argument when you want explicit control.

result = df.drop_duplicates(subset=["city", "fruit"])
print(result)

Reset the Index for Clean Output

Distinct queries often feed into later joins, exports, or reports. Resetting the index can make the result easier to use.

result = df[["city", "fruit"]].drop_duplicates().reset_index(drop=True)
print(result)

This is not required for correctness, but it avoids carrying old row labels into downstream logic.

Distinct Values Across Columns Is a Different Task

Sometimes people say "distinct across multiple columns" when they actually mean unique values pooled from several columns into one list. That is not the same as unique row combinations.

values = pd.unique(df[["city", "fruit"]].to_numpy().ravel())
print(values)

This gives unique scalar values drawn from both columns together, not unique two-column tuples.

Count Distinct Combinations When Needed

If the goal is only to count the number of unique combinations, you can avoid storing the full distinct table.

count = df.drop_duplicates(subset=["city", "fruit"]).shape[0]
print(count)

This is useful for data-quality checks and summary metrics.

Keep the Meaning Clear in Code

A practical habit is to write code that makes the intended distinctness obvious. Compare these two lines:

df.drop_duplicates()
df.drop_duplicates(subset=["city", "fruit"])

The second is much clearer when the uniqueness rule is part of the business logic.

If the distinct result will feed into a merge or reporting step, consider naming that subset explicitly in code or assigning it to a clearly named variable. Distinctness rules are easy to lose track of when several similar-looking DataFrame transformations appear in one pipeline.

Common Pitfalls

A common mistake is calling drop_duplicates() on the full DataFrame when the real requirement only concerns a subset of columns.

Another is confusing unique combinations with unique pooled values. Those are different outputs and should be implemented differently.

Developers also sometimes forget that duplicate removal keeps the first matching row by default, which matters if later columns still contain values you plan to inspect.

Summary

• Use drop_duplicates(subset=[...]) to get distinct combinations across selected pandas columns.
• Use df[[...]].drop_duplicates() when you want a distinct table of only those columns.
• Reset the index if the distinct result will be reused downstream.
• Use pd.unique on flattened values only when you want unique scalar values pooled from several columns.
• Be explicit about whether you want distinct tuples or distinct individual values.