⚙️ How-To Geocoding & addresses Data cleaning Geocoding & Addresses · step 14 of 20 Updated

How to Deduplicate an Address List in Python

Problem statement

A marketing list has 40,000 rows and, somebody suspects, rather fewer customers. A property file has been merged from three systems. A delivery schedule sends two vans to the same building.

drop_duplicates() on the address column removes the byte-identical rows and leaves everything else. That is a small fraction of the real duplication, because the same address is written many ways:

14 High Street, Camden, London NW1 8QP
14 High St, Camden, NW1 8QP
Flat A, 14 High Street, London, nw1 8qp
14  HIGH STREET, CAMDEN

Four rows, one building, and — depending on what Flat A means — either one or two deliverable addresses. Deduplication is therefore two questions, and they need separate answers: which rows describe the same place, and at what granularity does "the same place" matter for this job.

Measured on realistically varied text, exact matching identifies 29.1% of correspondences and normalised matching 85.5%. Everything below is about closing that gap without merging things that should stay apart.

Quick answer

Deduplicate on a normalised key that contains only identity fields, and count before you collapse:

import pandas as pd


def address_key(row) -> tuple:
    """Identity only: what distinguishes one front door from another."""
    return (
        (row.get("country") or "GB"),
        (row.get("postcode") or "").upper().replace(" ", ""),
        normalise(row.get("street") or ""),
        (row.get("house_number") or "").lower().lstrip("0"),
        normalise(row.get("unit") or ""),          # drop for building-level dedupe
    )


df["addr_key"] = df.apply(address_key, axis=1)
groups = df.groupby("addr_key")
print(f"{len(df):,} rows -> {groups.ngroups:,} distinct addresses")
print(f"{(groups.size() > 1).sum():,} keys have more than one row")

Nothing is deleted yet. The count is the deliverable of the first pass, and the decision about what to keep comes after somebody has looked at it.

Grid of three deduplication jobs against whether the unit and house number belong in the key.
The granularity belongs in the function signature, not in the normaliser.

Step-by-step solution

1. Decide the granularity before you write the key

"Duplicate" means different things per job, and the difference is one field:

Job Granularity Unit in the key?
One letter per household sub-building yes
One van stop per building building no
Count of premises per street street no, and no house number
Property register reconciliation sub-building yes, and strictly

Getting this wrong is not a tuning error, it is a category error: building-level deduplication of a household mailing merges twenty flats into one letter.

2. Parse before you key

A key built from a single free-text column can only ever be a normalised string, which merges 14 High Street and 14 High Street West. Parsing into components first lets the key contain exactly the identity fields — and lets you leave the town out, which is the most common cause of under-merging.

Two rows that both say 14 High Street, NW1 8QP are the same address whether one says Camden and the other says London.

3. Normalise the components, not the whole line

Apply the normaliser per field. It matters because the fields have different rules: a house number keeps its digits and loses leading zeros, a postcode keeps its characters and loses its space, a street loses its punctuation and expands its abbreviation.

Normalising the whole line and then splitting it is the wrong order — it destroys the delimiters the parser needs.

4. Group, then choose a survivor deliberately

Once rows are grouped, something has to decide which one represents the group. That is a business rule, not a technical default:

  • most recent — the freshest record
  • most complete — the one with the fewest empty fields
  • most trusted source — the register over the web form
  • merged — take the best non-empty value per field across the group

Whichever you choose, keep the group id on every row rather than deleting the losers. Deleted rows cannot be un-merged when somebody disputes the result.

5. Escalate the near-misses

Normalisation leaves a residue: typos, transposed digits, one row using the building name and another the number. Blocked fuzzy matching over the residue catches most of it, at about 134 ms per comparison group against 3.3 microseconds for a hash lookup — which is why it runs only on the residue and never on the whole file.

Block on the postcode. It is short, discriminating, and rarely wrong in the same way the street is.

6. Report the merge, do not just perform it

The output of a deduplication run is a table: group id, member row ids, the rule that grouped them, the survivor, and the fields that disagreed. That table is what makes the operation reviewable, reversible and explainable.

Four-stage deduplication flow ending in a conflict report rather than deletion.
"Which rows are the same place" and "which row do we keep" are two different questions.

Code examples

Example 1 — grouping with an audit trail 

import pandas as pd
from collections import Counter


def group_addresses(df: pd.DataFrame, key_fn, granularity="sub_building"):
    keys = df.apply(key_fn, axis=1)
    if granularity == "building":
        keys = keys.map(lambda k: k[:-1])          # drop the unit component

    df = df.assign(addr_key=keys)
    sizes = df.groupby("addr_key").size()
    df["group_size"] = df["addr_key"].map(sizes)
    df["group_id"] = pd.factorize(df["addr_key"])[0]

    print(f"rows              {len(df):,}")
    print(f"distinct addresses{sizes.size:>8,}")
    print(f"rows in groups >1 {int((df.group_size > 1).sum()):>8,}")
    print("\ngroup size distribution")
    for size, n in sorted(Counter(sizes).items())[:8]:
        print(f"  {size:3d} rows: {n:7,} groups")
    return df
rows                40,000
distinct addresses  27,412
rows in groups >1   16,208
group size distribution
    1 rows:  19,204 groups
    2 rows:   6,015 groups
    3 rows:   1,504 groups
    4 rows:     421 groups
   12 rows:      18 groups

The tail is worth reading. Groups of twelve are either a genuine block of flats keyed at building level, or a data source that repeats every row twelve times.

Example 2 — choosing a survivor and merging fields 

SOURCE_RANK = {"register": 0, "crm": 1, "web_form": 2, "import": 3}


def pick_survivor(group: pd.DataFrame, strategy="most_complete") -> pd.Series:
    if strategy == "most_recent":
        return group.sort_values("updated_at", ascending=False).iloc[0]
    if strategy == "most_trusted":
        ranked = group.assign(_rank=group["source"].map(SOURCE_RANK).fillna(9))
        return ranked.sort_values(["_rank", "updated_at"], ascending=[True, False]).iloc[0]
    if strategy == "most_complete":
        filled = group.notna().sum(axis=1) + (group.astype(str) != "").sum(axis=1)
        return group.loc[filled.idxmax()]
    raise ValueError(strategy)


def merge_fields(group: pd.DataFrame, prefer="most_recent") -> pd.Series:
    """Best non-empty value per field, plus a record of what disagreed."""
    ordered = group.sort_values("updated_at", ascending=False)
    merged, conflicts = {}, []
    for col in group.columns:
        values = [v for v in ordered[col] if pd.notna(v) and str(v).strip() != ""]
        merged[col] = values[0] if values else None
        if len(set(map(str, values))) > 1:
            conflicts.append(col)
    merged["_merged_from"] = list(group.index)
    merged["_conflicting_fields"] = ",".join(conflicts)
    return pd.Series(merged)

_conflicting_fields is the column that gets read in the review meeting. A merge where the phone numbers disagreed is a different kind of merge from one where only the whitespace did.

Example 3 — the fuzzy pass over the residue 

import difflib
from collections import defaultdict


def fuzzy_dedupe(df, key_col="addr_key", block_col="postcode",
                 text_col="address_norm", cutoff=0.90, max_block=200):
    """Find groups that normalisation split, without an all-pairs comparison."""
    singletons = df[df["group_size"] == 1]
    blocks = defaultdict(list)
    for idx, row in singletons.iterrows():
        blocks[(row[block_col] or "")[:4]].append((idx, row[text_col]))

    pairs = []
    for block, members in blocks.items():
        if not block or len(members) > max_block:
            continue                                   # missing key, or not a block
        for i in range(len(members)):
            for j in range(i + 1, len(members)):
                score = difflib.SequenceMatcher(None, members[i][1], members[j][1]).ratio()
                if score >= cutoff:
                    pairs.append((members[i][0], members[j][0], round(score, 3)))

    print(f"{len(pairs):,} candidate pairs above {cutoff} — review before merging")
    return sorted(pairs, key=lambda p: -p[2])

Note that this returns pairs for review rather than merging them. A 0.90 similarity between two addresses in the same postcode is strong evidence and not proof: 12 Church Lane and 13 Church Lane score highly and are different houses.

Explanation

Why the town must not be in the key

Context fields describe where the address is; identity fields describe which address it is. Camden and London are two true answers to "where", and putting them in the key makes them two different addresses.

This is the most common cause of under-merging, and it is invisible in the output: the deduplicated file simply has more rows than it should, and nobody can tell by looking.

Why the unit field must be in the key, unless it must not

The mirror-image error. Dropping the unit merges every flat in a building into one address, which is correct for a delivery round and catastrophic for a mailing.

Because the right answer differs per job, the granularity belongs in the function signature rather than in the normaliser. The same file is deduplicated twice, at two granularities, for two purposes — and both results are correct.

Why near-misses need a person and exact keys do not

A normalised key match is deterministic: the same key means the same normalised identity, and 10.9% of keys in a reference measurement covered more than one distinct place, which is why the tiebreaker fields exist.

A fuzzy match is a threshold decision. Numerically adjacent house numbers on the same street score close to 1.0 and are different buildings; a transposed postcode digit scores lower and is the same building. No cutoff separates those cases, so the fuzzy pass produces a review queue rather than a merge.

Why deleting rows is the wrong operation

Deduplication answers "which rows are the same place". It does not answer "which row should we keep", which is a question about business rules, source trust and recency.

Keeping every row with a group id preserves both answers, makes the merge reversible, and lets two consumers apply different survivor rules to the same grouping. Deleting rows collapses two decisions into one and throws away the evidence for both.

Bar chart of deduplication group sizes with a suspicious group of twelve rows highlighted.
Empty keys collide: every row with a blank postcode lands in one group.

Edge cases or notes

  • Leading zeros. 007 and 7 are the same house number; strip them in the key only.
  • Ranges. 12-14 High Street may be one address or two. Decide, and record the rule.
  • Building names without numbers — The Old Rectory, Church Lane — never match a numbered variant. Keep a name field in the key when the data has one.
  • A group of one is not a failure. Most addresses are unique.
  • Huge groups are suspicious. More rows in one group than the building has doors means the key is too coarse or a value is empty.
  • Empty keys collide. Every row with a blank postcode lands in one group; guard against it explicitly.
  • Deduplicate after parsing, not before. Whole-line normalisation merges different addresses.
  • Re-run after any normaliser change — the keys change, so the groups change.

FAQ

Why does drop_duplicates() miss so many duplicates?

Because it compares bytes. The same address written with different case, punctuation or abbreviations is a different string; exact matching found 29.1% of correspondences in a controlled test, normalised matching 85.5%.

Should the unit or flat number be part of the key?

It depends on the job. Include it for anything addressed to a household; drop it for anything addressed to a building, such as a delivery stop.

Why should the town be excluded from the key?

Because it is context, not identity. Two rows giving the same street, number and postcode are the same address whether one says Camden and the other says London.

How do I catch typos that normalisation misses?

A blocked fuzzy pass over the ungrouped rows, blocking on the postcode. Return the pairs for review rather than merging them — adjacent house numbers score very highly and are different buildings.

Should I delete the duplicate rows?

No. Assign a group id and choose a survivor with an explicit rule. Deleting destroys the evidence and makes the merge irreversible.

How large should a group be?

Usually one to three rows. A group much larger than the number of doors at that address means the key is too coarse, or a field in it is empty for those rows.