python pandas duplicates unique and value_counts: Practical

When working with tabular data in Python, pandas provides a set of methods that directly address three common tasks: finding duplicate rows, extracting unique values, and counting how often each value appears. These operations are central to data cleaning and exploratory analysis. This article covers python pandas duplicates unique and value_counts in a practical way, focusing on the exact syntax, behavior, and tradeoffs you need to know when applying them to real datasets.

Understanding Duplicates in pandas

A duplicate row in a DataFrame is a row that is identical to another row across all columns, or across a specified subset of columns. pandas does not automatically consider row indices when checking for duplicates; it compares the actual values in the columns. By default, duplicated() and drop_duplicates() consider all columns, but you can restrict the comparison to a subset using the subset parameter.

One subtle point is that pandas treats NaN values as equal to each other for duplicate detection. So two rows that both contain NaN in the same column are considered duplicates in that column. This is consistent with how pandas handles missing values in many grouping operations.

Identifying Duplicate Rows with duplicated()

The duplicated() method returns a boolean Series that marks each row as True if it is a duplicate of a previous row, and False otherwise. The default behavior marks the first occurrence of a duplicated row as False and subsequent occurrences as True. You can change this with the keep parameter:

keep='first' (default): marks duplicates after the first as True.
keep='last': marks duplicates before the last as True.
keep=False: marks all duplicated rows as True, including the first occurrence.

import pandas as pd

df = pd.DataFrame({
    'id': [1, 2, 2, 3, 3, 3],
    'value': ['a', 'b', 'b', 'c', 'c', 'c']
})

print(df.duplicated())
# 0    False
# 1    False
# 2     True
# 3    False
# 4     True
# 5     True

df.duplicated(keep='last')
# 0     True
# 1    False
# 2     True
# 3    False
# 4     True
# 5    False

df.duplicated(keep=False)
# 0    False
# 1     True
# 2     True
# 3     True
# 4     True
# 5     True

The subset parameter lets you check duplicates based on specific columns. This is useful when you want to consider a row a duplicate if it has the same key value, even if other columns differ.

df.duplicated(subset=['id'])
# 0    False
# 1    False
# 2     True
# 3    False
# 4     True
# 5     True

duplicated() is often used as a filter to inspect or count duplicate rows before deciding whether to remove them.

Removing Duplicates with drop_duplicates()

The drop_duplicates() method returns a new DataFrame with duplicate rows removed. It accepts the same subset and keep parameters as duplicated(). By default, it keeps the first occurrence of each unique row and drops the rest.

df.drop_duplicates()
#    id value
# 0   1     a
# 1   2     b
# 3   3     c

df.drop_duplicates(subset=['id'])
#    id value
# 0   1     a
# 1   2     b
# 3   3     c

If you want to keep the last occurrence instead, pass keep='last':

df.drop_duplicates(keep='last')
#    id value
# 0   1     a
# 2   2     b
# 5   3     c

Setting keep=False removes all rows that appear more than once, leaving only rows that are truly unique across the specified columns.

df.drop_duplicates(keep=False)
#    id value
# 0   1     a

By default, drop_duplicates() returns a new DataFrame and leaves the original unchanged. To modify the original in place, you can use the inplace=True parameter, but in modern pandas it is generally recommended to avoid inplace and instead reassign the result.

df = df.drop_duplicates()

Extracting Unique Values with unique() and nunique()

For a Series, unique() returns an array of the distinct values in the order they first appear. This is useful when you need the actual values, not just a count. The result is a NumPy array, not a pandas Series, which can affect downstream operations.

s = pd.Series(['a', 'b', 'a', 'c', 'b'])
s.unique()
# array(['a', 'b', 'c'], dtype=object)

If you only need the number of distinct values, use nunique(). It returns an integer and by default excludes NaN values. You can include NaN by passing dropna=False.

s = pd.Series(['a', 'b', 'a', None, 'b'])
s.nunique()        # 2
s.nunique(dropna=False)  # 3

For a DataFrame, nunique() counts unique values per column and returns a Series. There is no direct DataFrame-level unique() method; you would apply it column-wise or use drop_duplicates() on the entire frame.

df.nunique()
# id      3
# value   3
# dtype: int64

unique() and nunique() are particularly useful for understanding the cardinality of categorical columns before applying transformations or encoding.

Counting Occurrences with value_counts()

The value_counts() method on a Series counts how often each distinct value appears. It returns a Series sorted by count in descending order by default. This is one of the most direct ways to get a frequency distribution.

s = pd.Series(['a', 'b', 'a', 'c', 'b', 'b'])
s.value_counts()
# b    3
# a    2
# c    1
# dtype: int64

You can control the sorting with the sort parameter, and the normalization with normalize=True, which returns relative frequencies instead of raw counts.

s.value_counts(normalize=True)
# b    0.5
# a    0.333333
# c    0.166667
# dtype: float64

By default, value_counts() excludes NaN values. To include them as a separate category, pass dropna=False.

s = pd.Series(['a', 'b', None, 'a', None])
s.value_counts(dropna=False)
# a       2
# NaN     2
# b       1
# dtype: int64

The bins parameter is available for numeric data and groups values into intervals, which is useful for continuous data.

import numpy as np
nums = pd.Series(np.random.randint(0, 10, 20))
nums.value_counts(bins=3)
# (-0.1, 3.333]      8
# (3.333, 6.667]     6
# (6.667, 10.0]      6
# dtype: int64

value_counts() is often used to check for imbalanced categories, identify dominant values, or verify data quality after cleaning.

Combining These Methods for Data Cleaning

In practice, these methods are used together to clean a dataset. A typical workflow might start with identifying duplicate rows, inspecting them, removing them, then checking the distribution of a key column.

# Assume a DataFrame df with potential duplicates
print(f"Total rows: {len(df)}")
print(f"Duplicate rows: {df.duplicated().sum()}")

# Remove duplicates based on a business key
df_clean = df.drop_duplicates(subset=['order_id'], keep='first')

# Check the distribution of a categorical column
print(df_clean['status'].value_counts())

# Verify unique customers
print(f"Unique customers: {df_clean['customer_id'].nunique()}")

This combination gives you a clear picture of data quality and lets you make informed decisions about whether to keep or drop rows. For example, if duplicate rows have different values in other columns, you may need to aggregate them instead of simply dropping.

Performance and Memory Considerations

duplicated() and drop_duplicates() operate by comparing rows, which can be expensive on large DataFrames. The underlying implementation uses hashing, so the time complexity is roughly O(n) for the number of rows, but memory usage can increase with the number of unique rows. If you are working with millions of rows, consider these points:

Use subset to limit the columns compared; fewer columns mean less work.
When you only need a count, duplicated().sum() avoids creating a new DataFrame.
drop_duplicates() returns a new DataFrame, so memory usage temporarily spikes. If memory is a concern, you can use inplace=True (though it still creates an internal copy in many pandas versions) or process the data in chunks.
value_counts() is also O(n) and typically fast, but sorting the result adds a small overhead. If you don't need sorted output, pass sort=False.

For extremely large datasets that don't fit in memory, these pandas methods are not suitable; you would need to use a distributed framework or a database. But for typical DataFrames that fit in RAM, they are efficient enough.

Handling Edge Cases: NaN, Mixed Types, and Large Data

One common pitfall is assuming that NaN values are ignored in duplicate detection. As mentioned, pandas treats NaN as a distinct value that can be duplicated. If you want to treat rows with NaN as not duplicates, you need to fill them with a sentinel before calling duplicated().

df = pd.DataFrame({'a': [1, 1, None], 'b': [2, 2, None]})
df.duplicated()
# 0    False
# 1     True
# 2    False

If you want rows with NaN to be considered duplicates only when all other values match, you might need to use fillna with a unique placeholder.

Another edge case is when a column has mixed types, such as integers and strings. pandas will treat 1 and '1' as different values, which is usually correct but can surprise you if the data was not cleaned consistently.

For very large DataFrames, consider using dtype optimization before running these operations. For example, converting string columns to the category dtype can reduce memory usage and speed up comparisons, because pandas uses integer codes internally.

df['category_col'] = df['category_col'].astype('category')

This is especially effective when a column has a limited number of unique values, which is exactly the scenario where value_counts() and nunique() are used.