🔧 Fix Housing & property data Joins & relationships Notebook By · Updated

Fixing Local Authority Names That Do Not Match the Boundary File

On this page

Problem statement

You join a table of statistics by local authority to the official boundaries and a handful of areas come out blank on the map. The names look the same to a reader and differ to a computer. "CITY OF BRISTOL" and "Bristol, City of" are one place, and so are "RHONDDA CYNON TAFF" and "Rhondda Cynon Taf". Names also change when councils merge, so a table from one year and boundaries from another disagree for reasons that have nothing to do with spelling.

This page joins 2024 HM Land Registry Price Paid data, which names districts its own way, to the ONS local authority boundaries for December 2024. It finds every mismatch with an anti-join, fixes spelling and convention differences with a normalisation and a short lookup, and shows how district reorganisations break joins across years.

Quick answer

Find the mismatches on both sides before merging:

norm = lambda s: s.str.upper().str.replace(r"[^A-Z ]", " ", regex=True).str.replace(r"\s+", " ", regex=True).str.strip()
lr = pd.Series(sorted(A.district.unique())); ons = LAD[LAD.LAD24CD.str[0].isin(["E", "W"])]
only_lr = sorted(set(norm(lr)) - set(norm(ons.LAD24NM)))
only_ons = sorted(set(norm(ons.LAD24NM)) - set(norm(lr)))
print(f"Land Registry districts: {len(lr)}; ONS districts in England and Wales: {len(ons)}; exact matches without normalising: {len(set(lr) & set(ons.LAD24NM.str.upper()))}")
print(f"after normalising, unmatched on each side: {len(only_lr)} and {len(only_ons)}")
print("Land Registry only:", only_lr)
print("ONS only:", only_ons)
Land Registry districts: 318; ONS districts in England and Wales: 318; exact matches without normalising: 306
after normalising, unmatched on each side: 11 and 11
Land Registry only: ['CITY OF BRISTOL', 'CITY OF DERBY', 'CITY OF KINGSTON UPON HULL', 'CITY OF NOTTINGHAM', 'CITY OF PETERBOROUGH', 'CITY OF PLYMOUTH', 'CITY OF WESTMINSTER', 'HEREFORDSHIRE', 'RHONDDA CYNON TAFF', 'THE VALE OF GLAMORGAN', 'WREKIN']
ONS only: ['BRISTOL CITY OF', 'DERBY', 'HEREFORDSHIRE COUNTY OF', 'KINGSTON UPON HULL CITY OF', 'NOTTINGHAM', 'PETERBOROUGH', 'PLYMOUTH', 'RHONDDA CYNON TAF', 'TELFORD AND WREKIN', 'VALE OF GLAMORGAN', 'WESTMINSTER']

Both sides have 318 districts, and 306 match once the ONS names are upper-cased. Normalising punctuation leaves 11 on each side. They are the same places written differently: "CITY OF" before or after the name, "COUNTY OF" in the ONS name of Herefordshire, the Welsh spelling of Rhondda Cynon Taf, and "WREKIN" for Telford and Wrekin.

Map of England and Wales districts with the 11 that fail to join after normalising in red Map of England and Wales districts with the 11 that fail to join after normalising in red
Eleven districts would be blank on the map without a lookup.

A lookup from the leftovers, then codes, changed districts

1. Build a lookup from the leftovers

Pair each leftover with its counterpart by hand. Sorting both lists and zipping them looks tempting, but "CITY OF DERBY" sorts before "HEREFORDSHIRE" on one side while "DERBY" sorts after "BRISTOL CITY OF" on the other, so the pairs slide out of step. Write the lookup out and review it:

FIX = {"CITY OF BRISTOL": "BRISTOL CITY OF", "CITY OF DERBY": "DERBY", "CITY OF KINGSTON UPON HULL": "KINGSTON UPON HULL CITY OF",
       "CITY OF NOTTINGHAM": "NOTTINGHAM", "CITY OF PETERBOROUGH": "PETERBOROUGH", "CITY OF PLYMOUTH": "PLYMOUTH", "CITY OF WESTMINSTER": "WESTMINSTER",
       "HEREFORDSHIRE": "HEREFORDSHIRE COUNTY OF", "RHONDDA CYNON TAFF": "RHONDDA CYNON TAF", "THE VALE OF GLAMORGAN": "VALE OF GLAMORGAN", "WREKIN": "TELFORD AND WREKIN"}
assert set(FIX) == set(only_lr) and set(FIX.values()) == set(only_ons)
key = norm(A.district).replace(FIX)
matched = key.isin(set(norm(ons.LAD24NM)))
print(f"sales whose district now matches a boundary: {matched.mean():.2%}")
sales whose district now matches a boundary: 100.00%

With the reviewed lookup every standard sale matches a district. The assert guards against a lookup that covers the wrong names; a 100% match rate alone does not, because any lookup that maps into valid names reaches 100%, right or wrong.

District names that differ between Land Registry and ONS after normalising. same place, different nameLand RegistryONS (normalised)CITY OF BRISTOLBRISTOL CITY OFCITY OF DERBYDERBYHEREFORDSHIREHEREFORDSHIRE COUNTY OFRHONDDA CYNON TAFFRHONDDA CYNON TAFTHE VALE OF GLAMORGANVALE OF GLAMORGANWREKINTELFORD AND WREKIN6 of the 11 pairs; the rest are CITY OF ... cities2024 Price Paid against ONS December 2024 districts spatialworkflow.io
Same places, different conventions.

2. Join on codes from then on

Once matched, carry the ONS code, which is stable for an area until its boundaries change:

code = dict(zip(norm(ons.LAD24NM), ons.LAD24CD))
A["LAD24CD"] = key.map(code)
print(f"sales with an ONS code: {A.LAD24CD.notna().mean():.2%}; codes used: {A.LAD24CD.nunique()}")
print("examples:", A.drop_duplicates("district")[["district", "LAD24CD"]].head(3).values.tolist())
sales with an ONS code: 100.00%; codes used: 318
examples: [['LICHFIELD', 'E07000194'], ['TAMWORTH', 'E07000199'], ['STOKE-ON-TRENT', 'E06000021']]

Every sale now carries a code, and the 318 codes match the 318 districts.

3. Years with different districts

District reorganisations change the list itself. Compare the district names in the 2019 and 2024 files:

d19 = set(pd.read_csv(CSV19, header=None, usecols=[12])[12].unique()); d24 = set(lr)
print(f"district names in 2019: {len(d19)}; in 2024: {len(d24)}; in 2019 only: {len(d19 - d24)}; in 2024 only: {len(d24 - d19)}")
print("2019 only:", sorted(d19 - d24)[:8])
print("new 2023 unitary councils present in the 2019 file:", [n for n in ("CUMBERLAND", "WESTMORLAND AND FURNESS", "NORTH YORKSHIRE", "SOMERSET") if n in d19])
district names in 2019: 361; in 2024: 318; in 2019 only: 43; in 2024 only: 0
2019 only: ['ALLERDALE', 'AYLESBURY VALE', 'BARROW-IN-FURNESS', 'BOURNEMOUTH', 'CARLISLE', 'CHILTERN', 'CHRISTCHURCH', 'COPELAND']
new 2023 unitary councils present in the 2019 file: ['CUMBERLAND', 'WESTMORLAND AND FURNESS', 'NORTH YORKSHIRE', 'SOMERSET']

The 2019 file has 361 district names, 43 of them absent in 2024, such as Allerdale, Aylesbury Vale and Bournemouth, districts abolished in reorganisations from 2019 onwards. It also contains the four unitary councils created in 2023, so this 2019 file mixes old and new names, possibly because some records carry the district current when they were registered or amended. Joining across years needs a lookup from old districts to new ones, not a spelling fix.

What normalising alone achieves

The data used on this page

HM Land Registry Price Paid Data for 2019 and 2024 (© Crown copyright 2026, Open Government Licence v3.0), and the ONS Local Authority Districts (December 2024) boundaries from the ONS Open Geography Portal (© Crown copyright, OGL).

from pathlib import Path

import geopandas as gpd
import pandas as pd
import requests

Path("data").mkdir(exist_ok=True)
def cached_pp(y):
    f = Path(f"data/pp-{y}.csv")
    if not f.exists():
        r = requests.get(f"http://prod1.publicdata.landregistry.gov.uk.s3-website-eu-west-1.amazonaws.com/pp-{y}.csv", timeout=1800); r.raise_for_status(); f.write_bytes(r.content)
    return f
CSV, CSV19 = cached_pp(2024), cached_pp(2019)
A = pd.read_csv(CSV, header=None, usecols=[1, 12, 14], names=["price", "district", "ppd_category"]).query("ppd_category == 'A'")
GJ = Path("data/lad_2024_bgc.geojson")
if not GJ.exists():
    url = "https://services1.arcgis.com/ESMARspQHYMw9BZ9/arcgis/rest/services/Local_Authority_Districts_December_2024_Boundaries_UK_BGC/FeatureServer/0/query"
    r = requests.get(url, params={"where": "1=1", "outFields": "LAD24CD,LAD24NM", "outSR": 4326, "f": "geojson"}, timeout=600); r.raise_for_status(); GJ.write_bytes(r.content)
LAD = gpd.read_file(GJ)
print(f"{len(A):,} standard sales; {len(LAD)} UK districts")
763,905 standard sales; 361 UK districts

The boundary file covers all 361 UK districts; England and Wales, with codes starting E or W, are the ones Price Paid Data covers.

Example 1: what normalising alone achieves

Count matches at each stage of cleaning:

stages = {"exact": (lr.isin(ons.LAD24NM)).sum(), "upper case": lr.isin(ons.LAD24NM.str.upper()).sum(),
          "normalised": norm(lr).isin(set(norm(ons.LAD24NM))).sum(), "with lookup": norm(lr).replace(FIX).isin(set(norm(ons.LAD24NM))).sum()}
print(stages)
{'exact': np.int64(0), 'upper case': np.int64(306), 'normalised': np.int64(307), 'with lookup': np.int64(318)}

Exact matching finds nothing, because ONS names are in mixed case. Upper case brings 306, normalising punctuation one more, and the lookup the last 11.

Districts matched at each stage of cleaning the names. districts matched, of 318exact0upper case306normalised307with lookup3180100200318ONS names are mixed case, so exact matching finds none spatialworkflow.io
Exact 0, upper case 306, normalised 307, with lookup 318.

Names are a fragile join key

A reliable way to join a table to boundaries by name. joining by name, safely1anti-join both ways11 left on each side2normalisecase, punctuation, spaces3reviewed lookupwritten out, not zipped4assert coveragekeys and values both5carry the GSS codeE06000021 etc.6join on codesfrom then onA 100% match rate does not prove the pairs are right spatialworkflow.io
The assert, not the match rate, proves the lookup is right.

Names are a fragile join key. Different organisations follow different style guides: Land Registry writes district names in capitals with a leading "CITY OF" or "THE", while ONS puts the descriptor after a comma, as in "Bristol, City of". Welsh names may be spelled with or without doubled consonants, and older names linger in one source after a reorganisation, such as "WREKIN" for Telford and Wrekin. An inner join drops all of these silently, and a left join leaves them as missing values that look like "no data" on a map.

The robust approach has three steps. Find mismatches on both sides with an anti-join before merging. Remove the differences in case, punctuation and spacing that carry no meaning. Then map the few remaining conventions with an explicit, reviewed lookup. After that, carry the official code, the nine-character GSS code for UK areas, and join on codes from then on. Codes still change when boundaries change, as they did when Cumberland, Westmorland and Furness, North Yorkshire and Somerset became unitary councils in 2023. Data from different years needs a lookup between old and new areas, not a spelling fix.

Fuzzy matching, Welsh names and counties

Name join problems and fixes. name join trapsSymptomFixblank areas on the mapanti-join to find themsorted-and-zipped pairs wrongwrite the lookup by hand2019 names gone in 2024old-to-new area lookupsimilar names, different placesreview fuzzy matches43 district names in 2019 are absent in 2024 spatialworkflow.io
43 district names in the 2019 file are absent in 2024.
  • Fuzzy matching: string-similarity matching can find candidates, but always review its pairs; similar names can be different places.
  • Welsh names: ONS files also carry Welsh-language names (LAD24NMW) for Welsh districts.
  • Postcodes: the ONS Postcode Directory assigns every postcode to a district code, avoiding names entirely.
  • Counties vs districts: Land Registry's county column mixes metropolitan counties, shire counties and unitary authorities.

FAQ

Why are some areas blank after joining my data to boundaries?

Their names differ between the table and the boundary file; find them with an anti-join and normalise or map them.

How do I join data to local authority boundaries reliably?

Normalise names, map the remaining differences with a reviewed lookup, then join on the ONS GSS code.

Why do Land Registry district names differ from ONS names?

They follow different conventions, such as "CITY OF BRISTOL" versus "Bristol, City of", and some older names persist.

What happens when local authorities merge?

The old names and codes disappear; data from before and after needs a lookup from old to new areas.