How to Deduplicate an Address List in Python

On this page

Problem statement

A list of building permits, a customer file, a property register merged from three systems: somebody asks how many distinct places it holds, and drop_duplicates() on the address column gives an answer that is far too high. It removes byte-identical rows and nothing else, and the same address is written many ways. In New York City's 2019 building permits for Manhattan, one office tower was filed under six spellings:

1211 6TH AVENUE
1211 AVENUE OF THE AMERICAS
1211 SIXTH AVENUE
1211 6 AVENUE
1211 AVENUE OF AMERICAS
1211 AVE OF AMERICAS

Six rows, one building. Deduplication is two questions, and they need separate answers: which rows describe the same place, and at what granularity "the same place" matters for the job.

The permits make a good test because every row also carries the building's BIN, the city's building identification number, so the right answer is known. Of the pairs of permits for the same building, exact text matching links 78.7%. A normalised key links 90.6% at the same 99.6% precision. Most of what is left is not a spelling problem at all: it is buildings that genuinely have more than one address.

Quick answer

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

import re

import pandas as pd

ABBREV = {"street": "st", "avenue": "ave", "av": "ave", "east": "e", "west": "w", "north": "n",
          "south": "s", "place": "pl", "boulevard": "blvd", "road": "rd", "drive": "dr", "lane": "ln",
          "square": "sq", "plaza": "plz", "parkway": "pkwy", "terrace": "ter", "centre": "center",
          "first": "1", "second": "2", "third": "3", "fourth": "4", "fifth": "5", "sixth": "6",
          "seventh": "7", "eighth": "8", "ninth": "9", "tenth": "10", "eleventh": "11", "twelfth": "12"}


def normalise(text: str) -> str:
    """Fold what varies without changing the place: case, punctuation, suffixes, abbreviations."""
    text = re.sub(r"[^\w\s]", " ", text.casefold())
    text = re.sub(r"\b(\d+)(st|nd|rd|th)\b", r"\1", text)            # 12th -> 12
    return " ".join(ABBREV.get(t, t) for t in text.split())


def address_key(row) -> tuple:
    """Identity only: what distinguishes one front door from another."""
    return (
        row["postcode"][:5],
        normalise(row["street"]),
        normalise(row["house_number"]).lstrip("0"),
        normalise(row.get("unit") or ""),          # empty here; drop it 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")

62,345 rows -> 13,798 distinct addresses
9,290 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.

The granularity belongs in the function signature, not in the normaliser.

Decide granularity, parse, normalise components

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. The permits are filed per building and carry no unit, so the key's unit component is empty throughout this page.

2. Parse before you key

A key built from a single free-text column can only be a normalised string. Parsing into components first lets the key hold exactly the identity fields — the postcode, the street, the number — and leave out context such as a borough or town name, the most common cause of under-merging. The permits arrive already split into house number, street name and ZIP code, which is why the key can be built directly.

3. Normalise the components, not the whole line

Apply the normaliser per field, because the fields have different rules: a house number loses leading zeros, a ZIP code keeps five digits, a street loses punctuation, ordinal suffixes and the choice between STREET and ST. In the permits that one step turns 40 EAST 10 STREET, 40 E 10 ST and 40 EAST 10TH STREET into one key.

An abbreviation table cannot know that the Avenue of the Americas is Sixth Avenue. Aliases like that need a street reference, such as the city's street name dictionary, and are the job of matching against a reference file.

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, and expect most of them to be wrong

Normalisation leaves a residue: typos such as STRET, a missing space in W25TH, a name spelt two ways. A fuzzy comparison within each ZIP code finds candidates — Example 3 makes 97,426 comparisons in about a second — but on these addresses only 53 of its 1,610 candidates above a 0.90 similarity were the same building. 1 ROCKEFELLER PLZ and 10 ROCKEFELLER PLZ score 0.971. A fuzzy pass produces a review queue, never a merge.

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.

"Which rows are the same place" and "which row do we keep" are two different questions.

Grouping with an audit trail and choosing survivors

The data used on this page

New York City's DOB Permit Issuance dataset from NYC Open Data: every permit issued in Manhattan in 2019, with the typed house number, street name and ZIP code, the owner's business name, and the BIN of the building the permit is for. The BIN is not used to build any key; it is the answer the keys are scored against.

import os

import pandas as pd
import requests

URL = "https://data.cityofnewyork.us/resource/ipu4-2q9a.csv"          # DOB Permit Issuance
QUERY = {"$select": "job__,bin__,house__,street_name,zip_code,permit_type,issuance_date,owner_s_business_name",
         "$where": "borough='MANHATTAN' AND issuance_date like '%/2019'", "$limit": 100000}
if not os.path.exists("permits_manhattan_2019.csv"):
    r = requests.get(URL, params=QUERY, timeout=600)
    r.raise_for_status()
    open("permits_manhattan_2019.csv", "wb").write(r.content)
df = pd.read_csv("permits_manhattan_2019.csv", dtype=str).fillna("")
df = df.rename(columns={"bin__": "bin", "house__": "house_number", "street_name": "street",
                        "zip_code": "postcode", "owner_s_business_name": "owner"})
df["updated_at"] = pd.to_datetime(df["issuance_date"], format="%m/%d/%Y")
print(f"{len(df):,} permits, {df['bin'].nunique():,} buildings by BIN")

62,345 permits, 12,576 buildings by BIN

Example 1 — grouping with an audit trail

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):>8,}")
    print(f"distinct addresses{sizes.size:>8,}")
    print(f"rows in groups >1 {int((df.group_size > 1).sum()):>8,}")
    print("\ngroup size distribution")
    counts = sorted(Counter(sizes).items())
    for size, n in counts[:4] + counts[-3:]:
        print(f"  {size:3d} rows: {n:7,} groups")
    return df


grouped = group_addresses(df, address_key)
top = grouped.sort_values("group_size").iloc[-1]
print(f"\nlargest group: {top.house_number} {top.street}, {top.postcode} ({top.group_size} permits)")

rows                62,345
distinct addresses  13,798
rows in groups >1   57,837

group size distribution
    1 rows:   4,508 groups
    2 rows:   2,716 groups
    3 rows:   1,702 groups
    4 rows:   1,103 groups
  147 rows:       1 groups
  252 rows:       1 groups
  253 rows:       1 groups

largest group: 2376 BROADWAY, 10024 (253 permits)

The tail is worth reading. A group of 253 rows is either one very busy building or a key that has collapsed many places together, and only a look tells them apart: here it is one building, BIN 1033197, with 65 separate jobs in the year.

Example 2 — choosing a survivor and merging fields

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_complete":
        filled = (group.astype(str) != "").sum(axis=1)
        return group.loc[filled.idxmax()]
    raise ValueError(strategy)


def merge_fields(group: pd.DataFrame) -> 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)


fields = ["house_number", "street", "postcode", "owner", "updated_at"]
example = grouped[grouped.street.str.contains("12TH") & (grouped.group_size.between(3, 6))]
group = grouped[grouped.group_id == example.group_id.iloc[0]][fields]
print(group.to_string())
print(merge_fields(group)[["street", "owner", "_conflicting_fields"]].to_string())

      house_number       street postcode                    owner updated_at
838            711  12TH AVENUE    10019                   NYCEDC 2019-01-24
43888          711  12TH AVENUE    10019  HUDSON RIVER PARK TRUST 2019-09-20
61882          711  12TH AVENUE    10019                   NYCEDC 2019-02-28
street                             12TH AVENUE
owner                  HUDSON RIVER PARK TRUST
_conflicting_fields           owner,updated_at

_conflicting_fields is the column that gets read in the review meeting. Here the owner changed between permits — the city's development corporation, then the Hudson River Park Trust — and a merge that silently kept one of them would have lost that.

Example 3 — the fuzzy pass over the residue

import difflib
import time
from collections import defaultdict


def fuzzy_dedupe(df, 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.drop_duplicates("addr_key")          # one row per distinct key
    blocks = defaultdict(list)
    for idx, row in singletons.iterrows():
        blocks[row[block_col][:5]].append((idx, row[text_col]))

    pairs, compared = [], 0
    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)):
                compared += 1
                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"{compared:,} comparisons in {len(blocks)} blocks; "
          f"{len(pairs):,} candidate pairs above {cutoff} — review before merging")
    return sorted(pairs, key=lambda p: -p[2])


grouped["address_norm"] = grouped.addr_key.map(lambda k: f"{k[2]} {k[1]}")
start = time.perf_counter()
pairs = fuzzy_dedupe(grouped)
print(f"fuzzy pass {time.perf_counter() - start:.1f} s")
for a, b, score in pairs[:6]:
    print(f"  {score:.3f}  {grouped.address_norm[a]:28} | {grouped.address_norm[b]:28} "
          f"same building: {grouped.bin[a] == grouped.bin[b]}")
same = sum(grouped.bin[a] == grouped.bin[b] for a, b, _ in pairs)
print(f"{same} of {len(pairs)} candidate pairs are the same building by BIN")

97,426 comparisons in 82 blocks; 1,610 candidate pairs above 0.9 — review before merging
fuzzy pass 0.9 s
  0.982  2850 frederick douglass blvd | 2850 frederick douglas blvd  same building: True
  0.974  2533 a c powell blvd         | 2533 ac powell blvd          same building: True
  0.974  2529 a c powell blvd         | 2529 ac powell blvd          same building: True
  0.974  2525 a c powell blvd         | 2525 ac powell blvd          same building: True
  0.971  1 rockefeller plz            | 10 rockefeller plz           same building: False
  0.971  240 riverside blvd           | 40 riverside blvd            same building: False
53 of 1610 candidate pairs are the same building by BIN

The pairs that are right fix spellings the normaliser does not know, such as DOUGLAS for DOUGLASS and A C POWELL for AC POWELL. The pairs that are wrong are the dangerous ones: numbers one digit apart on the same street look almost identical to a string comparison and are different buildings.

The key does the work; the fuzzy pass only proposes, and most of its proposals are wrong.

Example 4 — scoring the keys against the building ids

def pair_count(sizes):
    return int((sizes * (sizes - 1) // 2).sum())


df["raw"] = df.house_number + " " + df.street + ", " + df.postcode
same_building = pair_count(df.groupby("bin").size())
for label, col in [("exact text", "raw"), ("normalised key", "addr_key")]:
    found = pair_count(df.groupby(["bin", col]).size())
    claimed = pair_count(df.groupby(col).size())
    print(f"{label:15} finds {found / same_building:6.1%} of same-building pairs, "
          f"precision {found / claimed:.2%}")
keys_per_bin = df.groupby("bin").addr_key.nunique()
bins_per_key = df.groupby("addr_key")["bin"].nunique()
print(f"buildings with more than one key: {(keys_per_bin > 1).sum():,} of {len(keys_per_bin):,}; "
      f"keys covering more than one building: {(bins_per_key > 1).sum()} of {len(bins_per_key):,}")
split = df[df["bin"].isin(keys_per_bin[keys_per_bin > 1].index)]
streets = split.groupby("bin").addr_key.agg(lambda k: len({x[1] for x in k}))
print(f"of the split buildings, {(streets > 1).mean():.0%} are filed under two or more streets "
      f"(corner lots and through-block buildings)")

exact text      finds  78.7% of same-building pairs, precision 99.63%
normalised key  finds  90.6% of same-building pairs, precision 99.64%
buildings with more than one key: 1,069 of 12,576; keys covering more than one building: 45 of 13,798
of the split buildings, 68% are filed under two or more streets (corner lots and through-block buildings)

The normalised key costs no precision: the 45 keys that cover two buildings are the same in both methods, typically a lot that holds more than one building under one address. What it cannot recover is the 9.4% of same-building pairs filed under different addresses, and two thirds of the buildings behind them sit on two or more streets — corner buildings and through-block towers with a legitimate address on each frontage.

Context fields against identity fields

Why the town must not be in the key

Context fields describe where the address is; identity fields describe which address it is. Manhattan and New York 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 an address and a building are different things

A building on a corner has an address on each street, and a large one can have a range of numbers on each. The permits file 20 BROAD STREET and 24 BROAD STREET for one building, and 200 WATER STREET and 127 JOHN ST. for another. No normalisation can merge those, because the strings are not variants of one address; they are different addresses of one building. Merging them needs a reference that knows which addresses belong to which building.

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

A normalised key match is deterministic, and on the permits it was as precise as exact text. 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 misspelt street name 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.

The tail is where a key error would show. Here it is one busy building, which is why the largest group gets looked at rather than assumed.

Leading zeros, ranges and aliases

Leading zeros. 007 and 7 are the same house number; strip them in the key only.
Ranges. 20-24 Broad Street may be one building or several. Decide, and record the rule.
Aliases. Avenue of the Americas and Sixth Avenue are one street; an abbreviation table will not know it.
Building names without numbers 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 in a customer file are unique; in a permit file, most are not.
Huge groups need a look. More rows than the place could plausibly have means the key is too coarse or a field 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.

Internal links

The address data model: why an address is not a string — the identity key in detail
How to parse and normalise addresses in Python — producing the components
Address matching explained: why exact string equality fails — the tiers this reuses
How to remove duplicate geometries in GeoPandas — the geometric equivalent
How to merge near-duplicate features — merging when the duplicates are shapes
Repair, reject or flag — deciding what to do with the groups
How to match addresses against a reference file — deduplicating against an authority
How to produce a data cleaning report — reporting the merge

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. On Manhattan's 2019 building permits, exact text linked 78.7% of the pairs of permits for the same building, and a normalised key 90.6%.

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 whatever town name each one uses.

How do I catch typos that normalisation misses?

A fuzzy pass over the distinct keys, blocked on the postcode. Return the pairs for review rather than merging them: on the permits, only 53 of 1,610 candidates above a 0.90 similarity were the same building.

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.

Why do some duplicates survive every normaliser?

Because they are not spellings of one address. A corner building has an address on each street, and only a reference that maps addresses to buildings can join them.