Removing duplicates

Finding them

df.duplicated().sum()                      # fully identical rows
df[df.duplicated(keep=False)]              # show every copy, not just repeats
df.duplicated(subset=['email']).sum()      # duplicate on a key column

keep=False marks all copies rather than only the second onwards, which is what you want when inspecting.

Removing them

df = df.drop_duplicates()
df = df.drop_duplicates(subset=['email'])
df = df.drop_duplicates(subset=['email'], keep='last')

keep accepts 'first' (the default), 'last', or False to drop every copy.

Keeping the right one

When duplicates differ — an old and a new record for the same customer — sort first so the row you want is the one kept:

df = (df.sort_values('updated_at')
        .drop_duplicates(subset=['email'], keep='last'))

This is the pandas equivalent of ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) in SQL. Without the sort, which row survives depends on file order — reproducible today, different tomorrow.

Duplicates that are not identical

df['email'] = df['email'].str.strip().str.lower()

[email protected] and [email protected] are different strings but the same customer. Normalise before deduplicating, or the duplicates stay.