Removing Duplicates
Python · Data Cleaning
Theory
df.drop_duplicates() removes the rows that duplicated() would flag and returns the rest. With no arguments it removes exact copies of whole rows. subset=['customer_id'] keeps one row per key even if other columns differ, and keep='first', 'last' or False chooses which copy survives (False removes every copy of a repeated key). ignore_index=True renumbers the result 0, 1, 2.
The most common real task is 'keep the latest record per key'. Because drop_duplicates keeps rows by position, sort first: df.sort_values('updated_at').drop_duplicates('customer_id', keep='last') keeps each customer's newest row. This is the pandas version of SQL's ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1. An alternative is df.loc[df.groupby('customer_id')['updated_at'].idxmax()]. Just to remove duplicates in one column of values, use s.drop_duplicates() or s.unique().
Why we use it
Feeds and exports often send the same record more than once, or send updates as new rows. Removing the extra copies, and choosing the right one to keep, gives one trustworthy row per entity.
When to use it
Use drop_duplicates() for exact copies, subset= for one row per business key, and sort + keep='last' for the latest version. If duplicates should be combined (summed or averaged) rather than dropped, use groupby instead.
How it works
- Duplicate keys found
- Are they exact copies of the whole row? Yes: df.drop_duplicates(). No: Decide which version wins.
- Sort by the column that decides the winner, e.g. updated_at
- drop_duplicates(subset=key, keep='last')
- Check: df[key].is_unique is True
- One row per key
Syntax
df.drop_duplicates(subset=None, keep='first' | 'last' | False, ignore_index=False)
df.sort_values('ts').drop_duplicates('key', keep='last')Example
latest = (df.sort_values('updated_at')
.drop_duplicates('customer_id', keep='last'))Key points
- Sort first, then keep='last' (or 'first') to choose which copy survives
- subset= defines the key; other columns may differ between copies
- keep=False drops every copy of a repeated key
- SQL equivalent: SELECT DISTINCT for exact rows; ROW_NUMBER() ... = 1 for latest per key
- Use groupby().agg() instead when duplicates should be summed, not dropped