How to Match Addresses Against a Reference File in Python

On this page

Problem statement

Somebody hands you a spreadsheet of addresses and an authoritative reference file — a national address register, a property gazetteer, a previous verified extract — and asks you to join them.

pandas.merge on the address column returns a fraction of the rows. This page matches the 16,843 distinct addresses on New York City's 2019 Manhattan building permits against the city's own address register, and exact string equality finds 10.1% of them. The rest are not missing from the reference; they are written WEST 51 STREET on one side and W 51 ST on the other.

The job is therefore a small record-linkage pipeline: normalise both sides, join on a key that survives the variation, break the ties that creates, escalate what is left, and produce an auditable record of how every row matched. Because every permit also carries the building's BIN, the city's building identification number, every match on this page can be checked.

Quick answer

Build a normalised index over the reference once, then match in tiers:

from collections import defaultdict

reference_index = defaultdict(list)
for record in reference:
    reference_index[normalise(record.address)].append(record)

matched, residue = [], []
for row in queries:
    hits = reference_index.get(normalise(row.address), [])
    if len(hits) == 1:
        matched.append((row, hits[0], "normalised", 1.0))
    elif len(hits) > 1:
        pick, why = break_tie(row, hits)          # postcode, region, population
        matched.append((row, pick, f"tie:{why}", 1.0)) if pick else residue.append(row)
    else:
        residue.append(row)

print(f"{len(matched):,} matched, {len(residue):,} to escalate")

On the permits, the normalised key matches 80.7% on top of the 10.1% exact, and the matched building is the permit's own building 98.8% of the time. 9.0% fall through to the residue, and reading that residue is where the useful discoveries are.

A merge destroys the evidence; a match table keeps it and stays re-runnable.

What you get — from 1. Profile both sides before writing any matching code

queries   16,843 rows, 16,839 distinct
reference 63,245 rows, 63,241 distinct
reference covers 92 postcodes; queries use 82, 5 of which are absent

Profile both sides, one normaliser, an indexed reference

1. Profile both sides before writing any matching code

The match rate is bounded by the reference. Two counts settle whether the job is even possible:

print(f"queries   {len(q_df):,} rows, {q_df.address.nunique():,} distinct")
print(f"reference {len(r_df):,} rows, {r_df.address.nunique():,} distinct")
print(f"reference covers {r_df.postcode.nunique():,} postcodes; "
      f"queries use {q_df.postcode.nunique():,}, "
      f"{(~q_df.postcode.isin(r_df.postcode)).sum():,} of which are absent")

queries   16,843 rows, 16,839 distinct
reference 63,245 rows, 63,241 distinct
reference covers 92 postcodes; queries use 82, 5 of which are absent

Query postcodes absent from the reference are an upper bound on the misses that no algorithm will fix. Here five ZIP codes on the permits appear nowhere in the register. Finding out at the start prevents a week of tuning a matcher against data that is not there.

2. Normalise both sides with the same function

This is not a style point. If the two sides are normalised differently — even in one edge case — the keys do not meet and the failure is invisible.

Import one function from one module on both sides, and version it. The whole pipeline's correctness rests on that function being the same object.

3. Index the reference, then stream the queries

Build a dictionary from normalised key to reference records once. The lookup is then a hash hit, and the whole match is one pass over the queries: indexing the 63,245 address points and matching the 16,843 permit addresses each take a fraction of a second.

Two properties make this the right shape. It is O(n + m) rather than O(n × m), and the index is reusable across many query files, which matters when the reference is large and the query files are monthly.

4. Handle the one-to-many case deliberately

A normalised key with several reference records behind it is a real cardinality, not an anomaly: one address can belong to several buildings on a lot, and a building complex can share one. Options, in order of preference:

Break the tie with another field — postcode, region, unit number.
Return all candidates and let a human or a downstream rule choose.
Pick by a documented rule — the most recent record, the primary address — and record that you did.

What is not acceptable is silently taking hits[0], because the order of a dictionary bucket is an implementation detail.

5. Escalate the residue with blocked fuzzy matching

Fuzzy matching is slow per comparison, so run it only on the residue, and block the candidate set so each lookup compares against dozens of records rather than tens of thousands. The block has to be small: a Manhattan ZIP code holds up to a few thousand address points, which is too many, so the block here is the ZIP code plus the first three letters of the street.

6. Emit a match table, not a merged file

The output of a matching step should be a table of correspondences with provenance — query, reference record, tier, score and candidate count — and Example 2 prints one row of each tier. Everything downstream joins through this table. That way the merge can be re-run, audited and partially corrected without re-running the matching.

Building the index costs one pass and removes the quadratic term entirely.

Matching NYC permits to address points

The data used on this page

Two NYC Open Data sets. The reference is Address Point, the city's register of addresses with the BIN of the building each belongs to, for Manhattan. The queries are the distinct addresses on DOB Permit Issuance permits issued in Manhattan in 2019, typed by applicants, each with its own BIN. The permit BIN is never used to match; it is the answer the matches are checked against.

import os

import pandas as pd
import requests

SODA = "https://data.cityofnewyork.us/resource"
if not os.path.exists("address_points_manhattan.csv"):             # the reference
    r = requests.get(f"{SODA}/uf93-f8nk.csv", timeout=600, params={
        "$select": "addresspointid,bin,house_number,house_number_suffix,full_street_name,zipcode",
        "$where": "boroughcode='1'", "$limit": 200000})
    r.raise_for_status()
    open("address_points_manhattan.csv", "wb").write(r.content)
if not os.path.exists("permits_manhattan_2019.csv"):                # the queries
    r = requests.get(f"{SODA}/ipu4-2q9a.csv", timeout=600, params={
        "$select": "job__,bin__,house__,street_name,zip_code",
        "$where": "borough='MANHATTAN' AND issuance_date like '%/2019'", "$limit": 100000})
    r.raise_for_status()
    open("permits_manhattan_2019.csv", "wb").write(r.content)

ref = pd.read_csv("address_points_manhattan.csv", dtype=str).fillna("")
r_df = pd.DataFrame({"id": "R" + ref.addresspointid, "bin": ref["bin"], "postcode": ref.zipcode,
                    "address": (ref.house_number + ref.house_number_suffix + " " + ref.full_street_name)
                    .str.split().str.join(" ")})
permits = pd.read_csv("permits_manhattan_2019.csv", dtype=str).fillna("")
permits = permits.sort_values(list(permits.columns), kind="stable")    # the API returns rows in no fixed order
q_df = (pd.DataFrame({"bin": permits["bin__"], "postcode": permits.zip_code,
                      "address": (permits.house__ + " " + permits.street_name).str.split().str.join(" ")})
        .drop_duplicates(["address", "postcode"]).reset_index(drop=True))
q_df.insert(0, "id", "Q" + (q_df.index + 1).astype(str).str.zfill(5))
print(f"reference: {len(r_df):,} Manhattan address points; queries: {len(q_df):,} distinct permit addresses")

reference: 63,245 Manhattan address points; queries: 16,843 distinct permit addresses

Example 1 — the matcher, end to end

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())


import difflib
from collections import defaultdict
from dataclasses import dataclass


@dataclass
class Match:
    query_id: str
    reference_id: str | None
    tier: str
    score: float
    candidates: int
    tiebreaker: str | None = None


class ReferenceMatcher:
    def __init__(self, reference, normalise, key_fn, block_fn):
        self.normalise, self.block_fn = normalise, block_fn
        self.exact = {}
        self.norm = defaultdict(list)
        self.blocks = defaultdict(list)
        for record in reference:
            self.exact.setdefault(key_fn(record), record)
            self.norm[normalise(key_fn(record))].append(record)
            self.blocks[block_fn(record)].append(record)

    def match_one(self, query, key, cutoff=0.85, max_block=500) -> Match:
        if key in self.exact:
            return Match(query.id, self.exact[key].id, "exact", 1.0, 1)

        hits = self.norm.get(self.normalise(key), [])
        if len(hits) == 1:
            return Match(query.id, hits[0].id, "normalised", 1.0, 1)
        if len(hits) > 1:
            pick, why = self.break_tie(query, hits)
            return Match(query.id, pick.id if pick else None,
                         "tie" if pick else "ambiguous", 1.0, len(hits), why)

        candidates = self.blocks.get(self.block_fn(query), [])
        if not candidates or len(candidates) > max_block:
            return Match(query.id, None, "none", 0.0, len(candidates))

        qn = self.normalise(key)
        scored = [(difflib.SequenceMatcher(None, qn, self.normalise(c.address)).ratio(), c)
                  for c in candidates]
        score, best = max(scored, key=lambda t: t[0])
        if score < cutoff:
            return Match(query.id, None, "none", round(score, 3), len(candidates))
        return Match(query.id, best.id, "fuzzy", round(score, 3), len(candidates))

    @staticmethod
    def break_tie(query, candidates):
        for field in ("postcode", "unit", "region"):
            value = getattr(query, field, None)
            if not value:
                continue
            same = [c for c in candidates if getattr(c, field, None) == value]
            if len(same) == 1:
                return same[0], field
        return None, "unresolved"

Example 2 — running it and reading the result

import pandas as pd
from collections import Counter


def run_match(queries, matcher, key_of):
    results = [matcher.match_one(q, key_of(q)) for q in queries]
    df = pd.DataFrame([r.__dict__ for r in results])

    tiers = Counter(df["tier"])
    total = len(df)
    print(f"{'tier':12} {'rows':>8} {'pct':>7}")
    for tier in ("exact", "normalised", "tie", "fuzzy", "ambiguous", "none"):
        n = tiers.get(tier, 0)
        if n:
            print(f"{tier:12} {n:8,} {100 * n / total:6.1f}%")

    fuzzy = df[df.tier == "fuzzy"]
    if len(fuzzy):
        print(f"\nfuzzy scores: min {fuzzy.score.min():.3f}  "
              f"median {fuzzy.score.median():.3f}  max {fuzzy.score.max():.3f}")
    return df


def block_of(record):
    """ZIP code plus the first three letters of the street: small enough to fuzzy-match within."""
    street = normalise(record.address).split(" ", 1)[-1]
    return (record.postcode, street[:3])


key_of = lambda r: f"{r.address}, {r.postcode}"
matcher = ReferenceMatcher(list(r_df.itertuples(index=False)), normalise, key_of, block_of)
matches = run_match(list(q_df.itertuples(index=False)), matcher, key_of)

addresses = dict(zip(r_df.id, r_df.address))
table = matches.merge(q_df[["id", "address"]], left_on="query_id", right_on="id").drop(columns="id")
table["reference_address"] = table.reference_id.map(addresses)
print()
print(table.groupby("tier").head(1)[["query_id", "address", "reference_address", "tier", "score", "candidates"]]
      .to_string(index=False))

anywhere = {}
for r in r_df.itertuples(index=False):
    anywhere.setdefault(normalise(r.address), set()).add(r.postcode)
unmatched = table[table.tier == "none"]
elsewhere = unmatched.address.map(lambda a: normalise(a) in anywhere)
print(f"\n{elsewhere.sum()} of {len(unmatched):,} unmatched addresses exist in the reference under another ZIP code")

tier             rows     pct
exact           1,694   10.1%
normalised     13,584   80.7%
fuzzy              44    0.3%
ambiguous           2    0.0%
none            1,519    9.0%

fuzzy scores: min 0.850  median 0.867  max 0.889

query_id                     address            reference_address       tier  score  candidates
  Q00001          245 EAST 93 STREET                  245 E 93 ST normalised  1.000           1
  Q00006            619 THIRD AVENUE                          NaN       none  0.667          50
  Q00074             969 MADISON AVE              969 MADISON AVE      exact  1.000           1
  Q00635 2166 FREDERICK DOUGLAS BLVD 2166 FREDERICK DOUGLASS BLVD      fuzzy  0.885          97
  Q13663         270 EAST 2ND STREET                          NaN  ambiguous  1.000           2

129 of 1,519 unmatched addresses exist in the reference under another ZIP code

The none rows are the deliverable as much as the matches are. The first one here, 619 THIRD AVENUE, closest of 50 candidates at 0.667, is not an address the register holds at all: the permit's own BIN is in the register, but only under the building's other addresses, none of them 619. A number inside a building's frontage that never got an address point cannot be matched by any amount of normalisation; it needs the BIN or a hand-written alias. Others are the same address filed under a different ZIP code — 129 of the unmatched addresses are like that. Every reference has a field the queries fill in differently.

Example 3 — checking the matcher against known pairs

def evaluate(matcher, labelled, key_of, truth_of):
    """labelled: [(query, truth), ...]; truth_of maps a reference id to the same kind of truth."""
    tp = fp = fn = 0
    by_tier = {}
    for query, truth in labelled:
        m = matcher.match_one(query, key_of(query))
        tier = by_tier.setdefault(m.tier, {"right": 0, "wrong": 0, "missed": 0})
        if m.reference_id is None:
            fn += 1
            tier["missed"] += 1
        elif truth_of(m.reference_id) == truth:
            tp += 1
            tier["right"] += 1
        else:
            fp += 1
            tier["wrong"] += 1

    precision = tp / (tp + fp) if tp + fp else 0
    recall = tp / (tp + fn) if tp + fn else 0
    print(f"precision {precision:.3f}  recall {recall:.3f}  "
          f"F1 {2 * precision * recall / (precision + recall):.3f}")
    for tier, counts in sorted(by_tier.items()):
        print(f"  {tier:12} right {counts['right']:6,}  wrong {counts['wrong']:4}  missed {counts['missed']:5,}")
    return precision, recall


bins = dict(zip(r_df.id, r_df["bin"]))
labelled = [(q, q.bin) for q in q_df.itertuples(index=False)]      # the permit's own BIN
evaluate(matcher, labelled, key_of, bins.get)
wrong = table[(table.tier == "fuzzy")].assign(
    right=lambda t: t.reference_id.map(bins).values == q_df.set_index("id").loc[t.query_id, "bin"].values)
print(wrong[~wrong.right][["address", "reference_address", "score"]].to_string(index=False))

checked = table[table.tier.isin(["exact", "normalised"])].assign(
    got=lambda t: t.reference_id.map(bins), truth=lambda t: q_df.set_index("id").loc[t.query_id, "bin"].values)
disagree = checked[checked.got != checked.truth]
gone = ~disagree.truth.isin(set(r_df["bin"]))
print(f"\n{len(disagree)} exact or normalised matches disagree on the BIN; "
      f"in {gone.sum()} the permit's BIN no longer exists in the reference")

precision 0.988  recall 0.909  F1 0.947
  ambiguous    right      0  wrong    0  missed     2
  exact        right  1,670  wrong   24  missed     0
  fuzzy        right     39  wrong    5  missed     0
  none         right      0  wrong    0  missed 1,519
  normalised   right 13,435  wrong  149  missed     0
                         address                reference_address  score
     2903 FREDERICK DOUGLAS BLVD     2973 FREDERICK DOUGLASS BLVD  0.852
    2726 FREDERICK DOUGLAS BLVD.     2756 FREDERICK DOUGLASS BLVD  0.852
     2738 FREDRICK DOUGLASS BLVD     2738 FREDERICK DOUGLASS BLVD  0.885
     1321 AVENUE OF THE AMERICAS         1382 AVE OF THE AMERICAS  0.852
1990 ADAM CLAYTON POWELL JR BLVD 2190 ADAM CLAYTON POWELL JR BLVD  0.886

173 exact or normalised matches disagree on the BIN; in 79 the permit's BIN no longer exists in the reference

Per-tier precision is what tells you where the fuzzy cutoff should sit. The fuzzy tier's five wrong answers are the instructive kind: 2903 matched to 2973 and 1990 to 2190, the same street with a different number — and 2738 FREDRICK DOUGLASS BLVD found the right address but a different BIN. Many of the exact and normalised disagreements are not matching errors either. Buildings demolished and rebuilt since 2019 have new BINs, such as 270 Park Avenue, and complexes such as Stuyvesant Town have one address and several buildings.

Each kind of miss has a different fix, and only one of them is a better matcher.

Index rather than scan

Why the reference has to be indexed rather than scanned

A nested loop over queries and reference records is the natural first implementation and it is quadratic: 16,843 queries against 63,245 address points is over a billion comparisons, for a job the indexed version does in a fraction of a second.

The index costs one pass over the reference and some memory. It is reusable across query files, which is the second reason to prefer it: the monthly job builds it once and matches many.

Why one-to-many is the normal case

Address references contain sub-buildings and complexes. One street address can legitimately correspond to several buildings, and a query that names only the address matches all of them.

This is not an error to be suppressed. It is a real cardinality that the output has to represent — either by returning all candidates, or by recording that a rule chose one. Collapsing it silently is what turns a match table into a source of quiet wrong answers.

Why the residue deserves a person

After normalisation, 9% of the permit addresses were unmatched, and the fuzzy tier placed only 44 of them. The rest are not near-misses: they are an alias the normaliser does not know (AVENUE OF THE AMERICAS for Sixth Avenue, CPS for Central Park South), a ZIP code that disagrees with the register, a building that has been renumbered, or an address the register does not hold.

That is usually a small enough number for a human to look at, and looking at them is the highest-value hour in the whole exercise: it is where you discover a whole class of rows with the wrong field, or that the reference is missing a new development.

Why the match table beats a merged dataframe

A merge produces one wide table and destroys the evidence. A match table keeps the two sides separate and records the relationship, so you can re-run the join, correct individual pairs by hand, measure precision against a labelled sample, and answer "why are these two rows joined?" a year later.

It also makes the pipeline restartable. Matching is the expensive step; joining through a stored match table is free.

Nine percent unmatched is a list a person can read in an afternoon — and worth the time.

Shared normalisers, empty keys and block sizes

Normalise both sides with the same imported function. Two copies drift, and the failure is silent.
Watch for empty blocking keys. Every row with a missing postcode lands in one huge block and the guard fires.
Check the block size against your reference. A block that is too big makes the fuzzy tier return nothing at all.
Sub-building matching needs the unit field, or every flat matches the building.
New buildings and demolitions lag or lead the reference. A match to the right address can point at a building that no longer exists.
Keep the reference version. A match table is only reproducible against the reference it was built from.
Do not fuzzy-match short strings. Under about five characters the ratio is meaningless.
A none is a result. Report the count; it is the measure of coverage.

Internal links

Address matching explained: why exact string equality fails — the reasoning behind the tiers
How to parse and normalise addresses in Python — the shared normaliser
How to deduplicate an address list in Python — the same machinery on a single file
How to fuzzy match place names in Python — the escalation tier
How to join attribute data in GeoPandas — joining once the match table exists
Fixing a merge that produces no matches — when the join returns nothing
The address data model: why an address is not a string — the fields the tiebreakers use
How to build an offline geocoder from open address data — matching as a geocoder

FAQ

Why does a plain merge on the address column fail?

Because the same address is written differently on the two sides. On Manhattan's 2019 permits against the city's address register, exact string equality matched 10.1% of the distinct addresses and a normalised key another 80.7%.

How should I structure the match?

In tiers: exact, then normalised, then ties broken by another field, then a blocked fuzzy pass, then an explicit "none". Record which tier produced every match.

What do I do when one query matches several reference records?

Break the tie with another field if one exists; otherwise return all candidates or apply a documented rule, and record which you did.

How fast is this on a large reference?

Indexing 63,245 reference records and matching 16,843 queries each take well under a second. The fuzzy tier is the slow part, which is why it runs only on the residue and within small blocks.

How do I know the matcher is any good?

Score it against pairs whose answer you know. Here every permit carries its building's BIN, which gave a precision of 0.988 and a recall of 0.909, and showed that many "wrong" answers were buildings rebuilt since 2019.

Should I output a merged table or a match table?

A match table. It keeps the evidence, can be corrected row by row, and can be re-joined without re-running the matching.