Practice Now
pandas
unique values
multiple columns
data analysis
Python

pandas unique values multiple columns

Master System Design with Codemia

Enhance your system design skills with over 120 practice problems, detailed solutions, and hands-on exercises.

Start PracticingLearn More

Introduction

Finding unique values across multiple columns in pandas requires different approaches depending on whether you want unique combinations of column values (unique rows) or a flat set of all distinct values appearing in any of the columns. For unique row combinations, use drop_duplicates() or groupby. For a flat set of all values, use np.unique() or pd.unique() on the stacked columns. Understanding the distinction is key to choosing the right method.

Sample Data

python
1import pandas as pd
2
3df = pd.DataFrame({
4    'City': ['NYC', 'LA', 'NYC', 'Chicago', 'LA', 'NYC'],
5    'State': ['NY', 'CA', 'NY', 'IL', 'CA', 'NY'],
6    'Category': ['A', 'B', 'A', 'C', 'B', 'A']
7})
8
9print(df)
10#      City State Category
11# 0     NYC    NY        A
12# 1      LA    CA        B
13# 2     NYC    NY        A
14# 3  Chicago    IL        C
15# 4      LA    CA        B
16# 5     NYC    NY        A

Unique Combinations of Multiple Columns

drop_duplicates()

python
1# Unique combinations of City and State
2unique_combos = df[['City', 'State']].drop_duplicates()
3print(unique_combos)
4#      City State
5# 0     NYC    NY
6# 1      LA    CA
7# 3  Chicago    IL
8
9# Reset index for clean numbering
10unique_combos = df[['City', 'State']].drop_duplicates().reset_index(drop=True)
11
12# Keep last occurrence instead of first
13unique_combos = df[['City', 'State']].drop_duplicates(keep='last')

groupby for Unique Combinations with Counts

python
1# Unique combinations with count of occurrences
2counts = df.groupby(['City', 'State']).size().reset_index(name='Count')
3print(counts)
4#      City State  Count
5# 0  Chicago    IL      1
6# 1      LA    CA      2
7# 2     NYC    NY      3

value_counts on Multiple Columns

python
1# Unique combinations with counts (sorted by frequency)
2vc = df[['City', 'State']].value_counts().reset_index(name='Count')
3print(vc)
4#      City State  Count
5# 0     NYC    NY      3
6# 1      LA    CA      2
7# 2  Chicago    IL      1

Flat Set of All Unique Values Across Columns

When you want all distinct values from multiple columns combined into one set.

python
1import numpy as np
2
3# All unique values across City and State columns
4all_unique = pd.unique(df[['City', 'State']].values.ravel())
5print(all_unique)
6# ['NYC' 'NY' 'LA' 'CA' 'Chicago' 'IL']
7
8# Using numpy
9all_unique = np.unique(df[['City', 'State']].values)
10print(all_unique)
11# ['CA' 'Chicago' 'IL' 'LA' 'NY' 'NYC']  (sorted)
12
13# As a Python set
14unique_set = set(df['City']).union(set(df['State']))
15print(unique_set)
16# {'NYC', 'NY', 'LA', 'CA', 'Chicago', 'IL'}

Unique Values Per Column

python
1# Unique values for each column individually
2for col in ['City', 'State', 'Category']:
3    print(f"{col}: {df[col].unique()}")
4# City: ['NYC' 'LA' 'Chicago']
5# State: ['NY' 'CA' 'IL']
6# Category: ['A' 'B' 'C']
7
8# Number of unique values per column
9print(df[['City', 'State', 'Category']].nunique())
10# City        3
11# State       3
12# Category    3

Filtering by Unique Combinations

python
1# Keep only rows with unique City-State combinations (first occurrence)
2df_deduped = df.drop_duplicates(subset=['City', 'State'])
3print(df_deduped)
4#      City State Category
5# 0     NYC    NY        A
6# 1      LA    CA        B
7# 3  Chicago    IL        C
8
9# Mark duplicates (True for duplicates, False for first occurrence)
10df['is_duplicate'] = df.duplicated(subset=['City', 'State'])
11print(df)
12#      City State Category  is_duplicate
13# 0     NYC    NY        A         False
14# 1      LA    CA        B         False
15# 2     NYC    NY        A          True
16# 3  Chicago    IL        C         False
17# 4      LA    CA        B          True
18# 5     NYC    NY        A          True

Unique Combinations with Aggregation

python
1# Get unique City-State combinations with list of all categories
2result = df.groupby(['City', 'State'])['Category'].apply(list).reset_index()
3print(result)
4#      City State     Category
5# 0  Chicago    IL          [C]
6# 1      LA    CA       [B, B]
7# 2     NYC    NY    [A, A, A]
8
9# Unique categories per City-State combination
10result = df.groupby(['City', 'State'])['Category'].apply(
11    lambda x: list(x.unique())
12).reset_index()
13print(result)
14#      City State Category
15# 0  Chicago    IL      [C]
16# 1      LA    CA      [B]
17# 2     NYC    NY      [A]

Common Pitfalls

  • Confusing unique() with drop_duplicates(): df['col'].unique() returns unique values from a single Series. df[['col1', 'col2']].drop_duplicates() returns unique row combinations. Calling .unique() on a multi-column DataFrame selection raises TypeError.
  • Using .values.ravel() on mixed types: When columns have different dtypes (e.g., string and int), .values.ravel() converts everything to object dtype. This may cause unexpected behavior in comparisons. Ensure columns are compatible types before combining.
  • Forgetting reset_index(drop=True): drop_duplicates() preserves original index values, creating gaps (e.g., 0, 1, 3). If downstream code assumes consecutive indices, call .reset_index(drop=True) after deduplication.
  • nunique() counting NaN as a unique value: By default, nunique() excludes NaN. If your data contains NaN and you need it counted, use df['col'].nunique(dropna=False). This difference between nunique() and len(unique()) can cause confusion.
  • Performance with large DataFrames: drop_duplicates() on many columns creates a hash of each row, which is slow for millions of rows with many columns. For performance, consider sorting first or using groupby().first() which can be faster on sorted data.

Summary

  • Use df[cols].drop_duplicates() for unique combinations of rows across multiple columns
  • Use pd.unique(df[cols].values.ravel()) for a flat array of all distinct values across columns
  • Use value_counts() or groupby().size() to count occurrences of unique combinations
  • Use .duplicated(subset=cols) to flag duplicate rows based on specific columns
  • Use nunique() to quickly count the number of unique values per column
Course illustration
Course illustration
Codemia
© 2026 Codemia
Resources
Legal
Social
All Rights Reserved.