# Deduplicate a lead list in Python: five ways drop_duplicates misses

**Disclosure:** we built and sell the Apify Actor this post compares against, [Merge & Dedup Datasets, CSV, JSON & Excel Files](https://apify.com/wrentools/dataset-merge-dedup) (publisher WrenTools). The post was written with AI assistance and reviewed by a person before publishing. Every output below was produced by a script that ran pandas 3.1.0 and that Actor's own code on the two lists shown.

**In short:** `df.drop_duplicates(subset=["email"])` compares exact values, and that goes wrong five ways on real lead lists. Rows with no email count as duplicates of each other, so all but one are deleted. `Ana@Acme.com ` and `ana@acme.com` both survive. `read_csv` turns `1042` into a number that never equals the `"1042"` from a JSON export, and `02134` into `2134`. `keep="first"` keeps whichever copy came first, even the emptier one. And `https://www.acme.com/` and `acme.com/?utm_source=webinar` are different strings. The fix: read keys as text, match on a normalised copy of the key, never dedup a missing key, and sort by completeness before you drop.

**Prefer to watch?** Our video [Merge Two CSV Files and Remove Duplicates in Python (pandas): Half the People Lost](https://youtu.be/ZSf5Cea3P60) shows a pandas merge that reports no errors and loses half the people, and the questions that catch it (narrated by a synthetic voice).

Merging two lead lists is a five-minute job: `concat`, then `drop_duplicates`. Here are the two lists we tested on, a CRM export as CSV and a webinar sign-up list as JSON from a form tool:

```text
customer_id,name,email,company,website,zip,phone
1042,Ana Ruiz,ana@acme.com,Acme,https://www.acme.com/,02134,
1043,Ben Ode,,Bolt Labs,https://boltlabs.io,94107,555-0102
1044,Cy Park,,Cedar,http://cedar.dev,10001,
1045,Dee Shah,dee@delta.co,Delta,https://delta.co,30301,
```

```json
[{"customer_id": "1042", "name": "Ana Ruiz", "email": "Ana@Acme.com ", "website": "acme.com/?utm_source=webinar", "zip": "02134", "phone": "555-0101", ...},
 {"customer_id": "1045", "name": "Dee Shah", "email": "dee@delta.co", "website": "https://delta.co/", "zip": "30301", "phone": "555-0104", ...},
 {"customer_id": "1050", "name": "Eli Fox", "email": "eli@fern.org", "website": "https://www.fern.org/about#team", "zip": "60601", "phone": "", ...}]
```

Five people, seven rows. The right answer is five rows, with Ana's and Dee's phone numbers kept.

```python
crm = pd.read_csv("crm.csv")
web = pd.read_json("webinar.json", dtype=False)
both = pd.concat([crm, web], ignore_index=True)
naive = both.drop_duplicates(subset=["email"])
```

**Predict:** before you read on, how many rows does `naive` have, and are all five people in it? Make a guess, then check it against the figure.

![Table of the seven merged rows. drop_duplicates on the email column deletes Cy Park, whose blank email counts as equal to Ben Ode's, and keeps Ana Ruiz twice, because 'Ana@Acme.com ' with a capital and a trailing space is a different string from 'ana@acme.com'. The fix compares a trimmed, lower-case copy of the email, gives a blank email a group of its own, and keeps the fullest copy of each person: five rows, one per person.](https://cdn.hashnode.com/uploads/gql/6ad91735786cf66498f3c2f4/3a572149-55d9-4e30-9f2a-e31c03079e6f.png)

Each red cell is one of the traps below.

## Why did drop_duplicates delete a row with no email?

Because pandas treats missing values as equal to each other when it looks for duplicates. Ben and Cy have no email, so their keys are both `NaN`, and Cy counts as a duplicate of Ben:

```text
both.duplicated(subset=['email']) flags: [False, False, True, False, False, True, False]
Ben and Cy have no email; after drop_duplicates(subset=['email']): True False
```

Cy Park is gone, and nothing says so. This is the worst of the five, because it deletes a real person rather than leaving an extra one. A missing key means "we can't tell", not "the same as every other blank". Keep those rows out of the duplicate test (`key` is the email column, normalised as in the next section):

```python
dup = key.notna() & key.duplicated()
deduped = df[~dup]
```

## Why are case and spaces variants not removed?

`drop_duplicates` compares exact values. The webinar form saved `Ana@Acme.com ` with a capital and a trailing space, so it's a different string from `ana@acme.com`:

```text
drop_duplicates(subset=['email']): 5 rows
    name         email
Ana Ruiz  ana@acme.com
 Ben Ode           NaN
Dee Shah  dee@delta.co
Ana Ruiz Ana@Acme.com 
 Eli Fox  eli@fern.org
```

Five rows, the right count by accident: one wrong deletion (Cy) and one missed duplicate (Ana). Match on a normalised copy of the key and leave the column itself alone:

```python
key = df["email"].str.strip().str.casefold().replace("", pd.NA)
```

`casefold()` is `lower()` that also handles letters such as the German ß.

## Why doesn't 1042 match "1042"?

`read_csv` guesses types. The CRM's `customer_id` column became integers, and its zip codes too:

```text
crm zip dtype: int64 first value: np.int64(2134)
crm customer_id: np.int64(1042) | webinar customer_id: '1042'
1042 == '1042' after concat: False
drop_duplicates(subset=['customer_id']): 7 rows of 7
```

So deduplicating on the ID removes nothing, and Boston's `02134` has lost its leading zero. When the columns are identifiers, read everything as text:

```python
crm = pd.read_csv("crm.csv", dtype=str, keep_default_na=False)
web = pd.read_json("webinar.json", dtype=str)
```

```text
dtype=str zip: '02134' | customer_id 1042 == '1042': True
```

`keep_default_na=False` keeps empty cells as `""` instead of `NaN`, which is why the key above turns `""` back into `pd.NA` before the duplicate test. With these three fixes (text, a normalised key, missing keys left alone) we get the right five people:

```text
rows kept: 5
    name        email    phone
Ana Ruiz ana@acme.com         
 Ben Ode              555-0102
 Cy Park                      
Dee Shah dee@delta.co         
 Eli Fox eli@fern.org         
```

## Which duplicate does keep="first" keep?

The first one, whatever it holds. Ana's and Dee's phone numbers were only in the webinar list, which came second, so both were dropped with the duplicate row:

```text
Ana's kept phone: '' | the dropped duplicate had: '555-0101'
```

pandas has no "keep the most complete" option, but it takes three lines. Count each row's filled fields, put the fullest copy first with a stable sort, drop, then restore the original order. Rows with a missing key get a key of their own, so they all survive:

```python
filled = df.replace("", pd.NA).notna().sum(axis=1)
group = key.fillna(pd.Series([f"__row{i}" for i in range(len(df))], index=df.index))
order = filled.sort_values(ascending=False, kind="stable").index
best = df.loc[order][~group.loc[order].duplicated()].sort_index()
```

```text
most complete per key: 5 rows
    name         email    phone
 Ben Ode               555-0102
 Cy Park                       
Ana Ruiz Ana@Acme.com  555-0101
Dee Shah  dee@delta.co 555-0104
 Eli Fox  eli@fern.org         
```

Ana's row now comes from the webinar list, so her email is the one she typed, `Ana@Acme.com `. That's the point of normalising a copy of the key: the data you keep is untouched. Trim it on output if you want it tidy.

## How do I match the same website spelled differently?

When there's no email, the company website is the usual key, and it's the messiest field of all. Five spellings of one site:

```text
distinct as text: 5
  'https://www.acme.com/'              -> 'acme.com'
  'acme.com/?utm_source=webinar'       -> 'acme.com'
  'http://acme.com'                    -> 'acme.com'
  'https://acme.com/#pricing'          -> 'acme.com'
  'HTTPS://ACME.COM/?b=2&a=1&gclid=x'  -> 'acme.com?a=1&b=2'
distinct after normalising: 2
```

The rules: ignore `http` versus `https`, a leading `www.`, a default port, a trailing slash and the `#fragment`; drop tracking parameters (`utm_*`, `gclid`, `fbclid` and friends); sort the parameters that are left. The last one keeps `?a=1&b=2`, because a real query parameter can mean a different page. Our version is about 20 lines of `urllib.parse`:

```python
TRACKING = re.compile(r"^(utm_[a-z0-9_]+|gclid|fbclid|msclkid|dclid|yclid|mc_cid|mc_eid|igshid|_ga)$", re.I)

def normalize_url(text):
    s = text.strip()
    if not re.match(r"^https?://", s, re.I):
        s = "http://" + s
    parts = urlsplit(s)
    host = (parts.hostname or "").lower().removeprefix("www.")
    port = parts.port
    netloc = host + (f":{port}" if port and port not in (80, 443) else "")
    path = re.sub(r"/{2,}", "/", parts.path).rstrip("/")
    query = sorted((k, v) for k, v in parse_qsl(parts.query, keep_blank_values=True) if not TRACKING.match(k))
    return netloc + path + ("?" + urlencode(query) if query else "")
```

One trap we hit ourselves while writing this post. Our Actor only applied these rules to values that started with `http://`, `https://` or `www.`, so a bare `acme.com/?utm_source=webinar` stayed a different key. Lead lists are full of bare domains. It now also treats a value as a URL when it looks like a domain (labels, a dot, a top-level label of letters, then the end or a `/`, `?`, `#` or port), which leaves emails, version numbers like `3.14.4` and ordinary text alone. We shipped that fix today, with a test for each of those cases.

## Questions people ask

**Does `drop_duplicates` treat `NaN` as equal?** Yes, as measured above in pandas 3.1.0: two rows whose subset values are all missing are duplicates.

**Is `5` the same as `"5"` and `5.0`?** Partly, to pandas. In one `object` column, `5.0` was flagged as a duplicate of `5` (they're equal in Python), but `"5"` wasn't. Our Actor turns every value into comparable text first, so `5`, `"5"` and `5.0` all become `5`.

**Will this scale?** The pandas version holds every row in memory. The Actor keeps only a 16-byte hash of each distinct key and streams rows through, writing in batches of 1,000.

## Try it

If you'd rather not write any of this, the Actor does all five as input fields: `dedupFields` (one or more fields, dotted paths work), trimming and case on by default, `normalizeUrls`, `keep: "mostComplete"`, and a choice for rows with empty keys (keep them, which is the default, treat them as one group, or drop them). It reads Apify datasets, CSV, JSON, JSON Lines and Excel files, and writes a summary of what it removed from each source. It costs $0.0002 per row written; duplicates and filtered rows are free. The example [merge and dedup lead lists](https://github.com/WrenTools/apify-actor-examples/tree/main/merge-and-dedup-lead-lists) runs it on two lists like these.

Which one has bitten you: the vanished blank, the trailing space, or the zip code that lost its zero? Tell us in the comments.