Duplicate Detection
Find and remove duplicate rows — a critical step before any aggregation or count analysis.
A data engineer ran an ETL pipeline twice by mistake. Your customer count is now 40,000 instead of 20,000. Your revenue totals are double. Every executive metric is wrong.
Duplicates enter data from ETL reruns, form double-submits, or system errors. Finding them before any analysis is non-negotiable — and pandas makes it a one-liner.
import pandas as pd
df = pd.read_csv('customers.csv')
# Are there any duplicates at all?
print(df.duplicated().sum())
# See the actual duplicate rows
print(df[df.duplicated(keep=False)])
# Remove duplicates, keep first occurrence
df_clean = df.drop_duplicates()
# Check specific columns for logical duplicates
df_clean = df.drop_duplicates(subset=['email'])keep=False marks ALL copies of a duplicate (not just the second), so you can inspect what's duplicated before deciding what to keep.What does this print?
import pandas as pd
df = pd.DataFrame({'id': [1, 2, 2, 3], 'val': ['a', 'b', 'b', 'c']})
print(df.duplicated().sum())- A1
- B2
- C0
- D3
A full duplicate is where every column matches exactly — same row copied.
A logical duplicate is where the key columns match even if other columns differ (e.g., same email with different capitalization).
# Logical duplicate on email — two rows for same user
df.drop_duplicates(subset=['email'], keep='last')
# Case-insensitive email deduplicate
df['email_lower'] = df['email'].str.lower()
df.drop_duplicates(subset=['email_lower'], keep='last')
df.drop(columns=['email_lower'], inplace=True)A user registers twice with emails 'Alice@email.com' and 'alice@email.com'. df.drop_duplicates() doesn't remove the duplicate. Why?
- Adrop_duplicates only works on numeric columns
- BThe strings are not identical — case differences make them appear unique to pandas
- CYou need to call reset_index() first
- Ddrop_duplicates requires a key= parameter
# How many duplicates?
print(f"Duplicates: {df.duplicated().sum()}")
print(f"Original rows: {len(df)}")
print(f"After dedup: {df.drop_duplicates().shape[0]}")
# Which columns drive most duplicates?
for col in df.columns:
n_unique = df[col].nunique()
print(f"{col}: {n_unique} unique values")If 50% of rows are duplicates, that's a pipeline problem — don't silently drop them, report it.
Remove rows where both 'user_id' and 'order_date' are the same, keeping only the first occurrence:
df = df.drop_duplicates(subset=['user_id', 'order_date'], keep='___')After running df.drop_duplicates(subset=['order_id']), you still see 500 rows that share the same customer_id. Is this a problem?
- AYes — drop_duplicates failed
- BNo — one customer legitimately having 500 orders is fine; order_id is the unique key
- CYes — you should always drop rows with duplicate customer_ids
- DNo — 500 rows is always the correct number