Identifying Duplicates
Python · Data Cleaning
Theory
df.duplicated() returns a boolean Series that is True for every row that repeats an earlier row. By default it compares all columns; subset=['name', 'city'] compares only those columns, which is how you find duplicate business keys even when other columns differ. The keep argument decides which copy is not flagged: keep='first' (default) flags all copies except the first, keep='last' flags all except the last, and keep=False flags every row that has a twin.
So df.duplicated().sum() tells you how many extra copies exist, and df[df.duplicated(subset=['name', 'city'], keep=False)] shows every row involved, sorted for review. To see how many times each key appears, use df.value_counts(['name', 'city']) or df.groupby(['name', 'city']).size() and keep counts above 1 - the pandas version of SQL's GROUP BY ... HAVING COUNT(*) > 1. Text keys often hide duplicates behind case or spaces, so normalise them with .str.strip().str.lower() first.
Why we use it
Duplicates inflate totals, double-count customers and make joins explode into extra rows. Finding them, and understanding why they exist, comes before deciding which copy to keep.
When to use it
Check for duplicates after loading, after concatenating sources and before any join on a key. Use keep=False to review every copy; use subset when only the key columns matter. To actually remove them, use drop_duplicates.
How it works
- df.duplicated(subset=key, keep=...)
- Build each row's key from the subset columns
- Has this key appeared before? Yes: Candidate duplicate. No: First copy - not flagged.
- keep='last' or False? Yes: Also flag earlier copies. No: Flag only later copies.
- Boolean Series: True = duplicate
Syntax
df.duplicated(subset=None, keep='first' | 'last' | False)
df.duplicated().sum()
df[df.duplicated(subset=[...], keep=False)]Example
print(df.duplicated(subset=['name', 'city']).sum())
print(df[df.duplicated(subset=['name', 'city'], keep=False)])Key points
- duplicated() compares all columns unless you pass subset=
- keep='first' / 'last' leaves one copy unflagged; keep=False flags every copy
- duplicated().sum() = number of extra copies, not number of duplicate keys
- SQL equivalent: GROUP BY name, city HAVING COUNT(*) > 1
- Normalise text first: ' Asha' and 'asha' are different values to pandas