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

How to Match Addresses Against a Reference File in Python

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. In a controlled measurement against a 238,483-row reference, exact string equality on lightly varied inputs matched 29.1%. The other 71% are not missing from the reference; they are spelled differently.

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.

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

The measured yield on that data: 85.5% matched on the normalised key, 14.5% into the residue, of which fuzzy matching recovered about 94% at a 0.85 cutoff.

Match table with query id, reference id, tier, score and tiebreaker columns.
A merge destroys the evidence; a match table keeps it and stays re-runnable.

Step-by-step solution

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

Query postcodes absent from the reference are an upper bound on the misses that no algorithm will fix. 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 — measured at 3.3 microseconds — and the whole match is one pass over the queries.

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 the common case, not an anomaly: 10.9% of keys in the measured reference had more than one record. Options, in order of preference:

  1. Break the tie with another field — postcode, region, unit number.
  2. Return all candidates and let a human or a downstream rule choose.
  3. Pick by a documented rule — highest population, most recent record — 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 costs 134 ms per lookup against 3.3 microseconds for a hash hit. Run it only on the residue, and block the candidate set so each lookup compares against hundreds of records rather than hundreds of thousands.

The outward postcode is usually the best block: short, discriminating, and present in most address data. Where the postcode is missing, fall back to the first three characters of the normalised street name.

6. Emit a match table, not a merged file

The output of a matching step should be a table of correspondences with provenance:

query_id  reference_id  tier         score  candidates  tiebreaker
--------  ------------  -----------  -----  ----------  ----------
Q0001     R018223       exact        1.000           1  
Q0002     R041991       normalised   1.000           1  
Q0003     R007612       tie          1.000           3  postcode
Q0004     R119043       fuzzy        0.912          64  
Q0005                   none         0.612          48

Everything downstream joins through this table. That way the merge can be re-run, audited and partially corrected without re-running the matching.

Two panels comparing a nested-loop match with an indexed match.
Building the index costs one pass and removes the quadratic term entirely.

Code examples

Example 1 — the matcher, end to end 

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}")
        print("lowest-scoring fuzzy matches (check these first):")
        print(fuzzy.nsmallest(5, "score")[["query_id", "reference_id", "score"]]
              .to_string(index=False))
    return df
tier             rows     pct
exact          14,551    29.1%
normalised     27,199    54.4%
tie             1,022     2.0%
fuzzy           5,401    10.8%
none            1,827     3.7%

The none rows are the deliverable as much as the matches are: they are the addresses the reference does not contain, and somebody needs to know that.

Example 3 — checking the matcher against known pairs 

def evaluate(matcher, labelled, key_of):
    """labelled: [(query, true_reference_id), ...]"""
    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 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']:5}  wrong {counts['wrong']:5}")
    return precision, recall

Per-tier precision is what tells you where the fuzzy cutoff should sit. If the fuzzy tier's wrong count climbs while the right count plateaus, the cutoff is too low.

Explanation

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. At 50,000 queries against 238,483 references that is 12 billion comparisons; at a microsecond each it is three and a half hours for a job the indexed version does in under 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. One street address legitimately corresponds to twenty flats, and a query that names only the building matches all twenty.

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, 14.5% of the measured queries were unmatched, and fuzzy matching recovered about 94% of a sample of them. The remainder — a few percent of the file — are genuinely absent from the reference, badly damaged, or not addresses at all.

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 that a whole branch's data has the town in the street column, 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.

Bar chart of matching outcomes across exact, normalised, tie, fuzzy and no-match tiers.
A few percent unmatched is usually small enough for a person to read — and worth the hour.

Edge cases or notes

  • 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.
  • Sub-building matching needs the unit field, or every flat matches the building.
  • New-build addresses lag the reference by months. A cluster of unmatched rows in one postcode is usually a new estate, not a data error.
  • 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.
  • Re-check precision after any change to the normaliser or the cutoff, against the same labelled sample.

FAQ

Why does a plain merge on the address column fail?

Because the same address is written differently on the two sides. Exact string equality matched 29.1% of realistically varied queries in a controlled test; normalised matching reached 85.5%.

How should I structure the match?

Normalised exact first, tie-breaking second, blocked fuzzy on the residue third. Each tier is orders of magnitude more expensive than the one before, so each should see far fewer rows.

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

Break the tie with another field — postcode, unit, region — and record which one you used. If nothing resolves it, report the row rather than taking the first candidate.

How fast is this on a large reference?

Indexing 238,483 reference records is one pass; each query lookup is about 3.3 microseconds. Fuzzy matching is 134 ms per lookup, which is why it runs only on the residue.

How do I know the matcher is any good?

Evaluate against a labelled sample and report precision and recall per tier. If the fuzzy tier's wrong count grows without its right count growing, lower the cutoff no further.

Should I output a merged table or a match table?

A match table with the tier, score and tiebreaker. It is auditable, re-runnable, and it keeps the two datasets separate so a single wrong pair can be corrected by hand.