Deduplicating Store Records with Fuzzy Address Matching

This page solves one exact task: finding and merging duplicate location records — the same storefront arriving twice under different spellings, unit designators or trading names — using a blocking pass, a string-similarity score and a spatial distance test together.

Duplicates in a location dataset do specific damage. A competitor counted twice doubles the apparent saturation in the proximity index; an own store counted twice creates a cannibalization signal against itself; and both inflate the market coverage a planning team reports. None of that is visible on a map at national zoom.

Prerequisites #

• Python packages: rapidfuzz for string similarity, geopandas and shapely for the distance test, pandas for the frame. Install with pip install rapidfuzz geopandas shapely pandas.
• Normalized, geocoded records. Components from address parsing and coordinates with a precision tier from batch geocoding. Deduplicating before geocoding finds only about a quarter of real duplicates.
• A stable record identifier on every input row, so a merge can record what it absorbed.

Configuration and execution parameters #

max_distance_m interacts with the precision tier in a way that catches teams out. Two postal-centroid points are always within twenty-five metres of each other when they share a postcode, so a distance test applied to weak-tier records merges every store in the postcode. The tier floor is what stops that, and it is not optional.

Annotated implementation #

The matcher below blocks, scores, and classifies each candidate pair into merge, review or reject. The composite score is deliberately simple and explainable, because every merge it makes will eventually be questioned by someone who owns one of the records.

from __future__ import annotations

import geopandas as gpd
import pandas as pd
from rapidfuzz import fuzz
from pyproj import CRS

METRIC_CRS = CRS.from_epsg(5070)
NAME_MIN, STREET_MIN = 88, 92
NEAR_M, FAR_M = 25.0, 200.0
STRONG_TIERS = {"rooftop", "parcel"}


def _blocks(df: pd.DataFrame) -> pd.Series:
    """Postcode plus the first street token — cheap and highly selective."""
    first_token = df["street"].str.split().str[0].fillna("")
    return df["postcode"].fillna("") + "|" + first_token


def candidate_pairs(df: pd.DataFrame) -> list[tuple[int, int]]:
    pairs: list[tuple[int, int]] = []
    for _, idx in df.groupby(_blocks(df)).groups.items():
        members = list(idx)
        for i in range(len(members)):
            for j in range(i + 1, len(members)):
                pairs.append((members[i], members[j]))
    return pairs


def score_pair(a: pd.Series, b: pd.Series, distance_m: float) -> float:
    """Composite in [0,1]. Text and geometry each veto; neither alone decides."""
    name = fuzz.token_set_ratio(a["name"], b["name"])
    street = fuzz.token_set_ratio(a["street"], b["street"])
    number_match = a["number"] == b["number"]

    strong_tiers = {a["tier"], b["tier"]} <= STRONG_TIERS
    if distance_m > FAR_M:
        return 0.0                                  # too far apart to be one place
    if not strong_tiers and distance_m <= NEAR_M:
        distance_component = 0.0                    # weak tiers cannot claim proximity
    else:
        distance_component = max(0.0, 1.0 - distance_m / FAR_M)

    text_component = (
        0.5 * min(name, 100) / 100 +
        0.5 * min(street, 100) / 100
    )
    bonus = 0.1 if number_match else 0.0
    if name < NAME_MIN and street < STREET_MIN:
        return min(distance_component, 0.5)         # geometry alone: review at best
    return min(1.0, 0.55 * text_component + 0.45 * distance_component + bonus)


def deduplicate(gdf: gpd.GeoDataFrame) -> pd.DataFrame:
    g = gdf.to_crs(METRIC_CRS)
    rows = []
    for i, j in candidate_pairs(g):
        a, b = g.loc[i], g.loc[j]
        d = a.geometry.distance(b.geometry)
        s = score_pair(a, b, d)
        if s == 0.0:
            continue
        rows.append({"left_id": a["record_id"], "right_id": b["record_id"],
                     "distance_m": round(d, 1), "score": round(s, 3),
                     "decision": "merge" if s >= 0.80
                     else ("review" if s >= 0.55 else "reject")})
    return pd.DataFrame(rows).sort_values("score", ascending=False)

The design decision worth defending is that neither signal can merge alone. Two records with identical names two hundred metres apart are two branches; two records at the same coordinate with unrelated names are two tenants in one building. Requiring both to agree is what keeps the merge rate honest, and it is why the score has vetoes rather than being a simple weighted sum.

Failure modes and debugging #

Merging two tenants of one address. A shopping centre address hosts several businesses, and a distance test at twenty-five metres considers all of them identical. The name similarity is what separates them, which is why a weak name field — abbreviated, missing, or set to the centre’s name for every tenant — makes this failure likely. Where names are unreliable, add the trading category as a third signal and refuse to merge across categories.

Chained merges. Record A matches B, B matches C, but A and C are clearly different places. Naively transitive merging collapses all three. Treating merges as a graph and requiring the whole connected component to be mutually consistent — or simply capping components at pairs above a high threshold and sending larger ones to review — prevents a chain from swallowing a street.

Losing the loser’s data. A merge that keeps the survivor’s fields discards whatever the other record knew: an alternative trading name, an older identifier a downstream system still uses, a phone number. Writing the absorbed records as aliases on the survivor keeps the join key working for consumers that have not been updated, and makes the merge reversible.

Re-creating duplicates on the next import. If the merge exists only in the output table, the next import of the source produces both records again. The merge decision has to be persisted against the source identifiers, so the pipeline applies it automatically on subsequent runs — otherwise the review queue is Sisyphean.

Verification #

• Hand-label a sample of every band. A hundred pairs from merge, review and reject each, labelled by a person, gives precision and recall figures that mean something. Without them the thresholds are guesses that happen to produce a comfortable number of merges.
• Confirm the merge is reversible. Pick a merged record, follow its aliases back to the source identifiers, and confirm both original rows can be reconstructed. If they cannot, the merge policy is destroying evidence.
• Re-run the pipeline on the same input. The same merges must be produced, and previously-persisted decisions must be applied rather than re-derived. A dedupe pass that is not idempotent will oscillate as pairs are formed in different orders.
• Count stores per market before and after. A market whose store count fell by a fifth is either a real duplicate cluster or an over-merge, and either way it is worth reading before publishing.

Frequently Asked Questions #

Related #

• Parsing and Standardizing US Store Addresses — the normalization that makes text comparison meaningful.
• Geocoding & Address Normalization — where deduplication sits in the ingest sequence.
• Building a Competitor Proximity Index in PostGIS — the index that duplicate competitors corrupt.

← Back to Geocoding & Address Normalization