Level 3 · Find duplicate customers

Exact duplicates are the easy part

duplicated() marks rows that repeat an earlier row:

customers.duplicated().sum()                    # whole-row duplicates
customers.duplicated(subset=["email"]).sum()    # same email, any other columns

Whole-row duplicates are rare in real data, because a second signup gets a new ID and a new date. What you need is a business key: the column (or columns) that should identify a person uniquely. For customers, that’s the email.

Normalise the key first

Six of Harbor & Pine’s duplicates share the email exactly. Twelve more differ only by case or a leading space, ' owen.hudson@icloud.com' versus 'owen.hudson@icloud.com'. A raw comparison misses them, so build a normalised key:

email_key = customers["email"].str.strip().str.lower()
email_key.duplicated().sum()

Compare that with customers["email"].duplicated().sum() to see how many duplicates the normalisation uncovers. That gap is the point of this level.

keep: which copy counts as the original?

duplicated takes keep=:

• keep="first" (default): the first occurrence is the original; later ones are True.
• keep="last": the last occurrence is the original.
• keep=False: every row in a duplicate group is True. Use it to look at all copies side by side.

dupes = customers[email_key.duplicated(keep=False)]
dupes.assign(email_key=email_key).sort_values(["email_key", "customer_id"])

Eyeballing the groups before removing anything is good practice. Here you’d see the same name and city, a later signup date and a higher ID on the second record.

drop_duplicates

To remove the repeats, keep one row per key. Sort first so the row you want to keep comes first:

deduped = (
    customers.assign(email_key=email_key)
    .sort_values("customer_id")
    .drop_duplicates(subset="email_key", keep="first")
)

Keeping the lowest ID keeps the original account, which is the one with the order history. Always decide which copy survives on purpose. “Whichever came first in the file” is not a rule.

Duplicates and joins

Duplicate keys cause the fan-out you met in the merge chapter: an order matched to two copies of its customer is counted twice. If you merge orders with customers on customer_id, these duplicates are harmless, because each has its own ID. If you match on email (say, to join a newsletter list), they double count. Deduplicate before joining on a business key.

Don’t delete, flag

In production, it’s often better to add a flag (is_duplicate) or a mapping from duplicate ID to original ID than to drop rows. Other tables may reference the duplicate IDs, and finance may need to know they existed.

Common mistakes

• Checking duplicates on the raw email and concluding there are only six.
• Using keep=False and then dropping, which removes the originals too.
• Deduplicating on all columns, which finds nothing because IDs differ.

Interview note

“How would you find duplicate customers?” is a classic. A strong answer names the business key, normalises it, inspects the groups with keep=False and explains which record survives.