DEV Community: Ashleigh Giles

Joining HM Land Registry Price Paid Data to EPC certificates: address matching, failure modes and a CR5 worked example

Ashleigh Giles — Mon, 09 Nov 2026 10:36:15 +0000

If you want to analyse UK house prices by property type, floor area or build era, you have to join two open datasets that share no key: HM Land Registry Price Paid Data and the Energy Performance Certificate register. This post explains what each dataset contains, why the join is hard, the address-matching strategy that works, the failure modes to expect, and what the result looks like for postcode district CR5 (Coulsdon, Chipstead, Old Coulsdon, Hooley and Netherne-on-the-Hill). Figures were calculated on 9 November 2026.

What each dataset contains

HM Land Registry publishes Price Paid Data under the Open Government Licence v3.0. Each record is one sale and holds the price, the date of transfer, the postcode, the property type (D detached, S semi-detached, T terraced, F flat or maisonette, O other), whether the property is new build, the tenure (F freehold, L leasehold) and the address split into parts: PAON (primary addressable object name, usually the house number or name), SAON (secondary, usually a flat number), street, locality and town. It also carries a record status: A for added, C for changed and D for deleted, so a correct import handles updates and deletions and not only inserts.

The Energy Performance Certificate register is published by the Ministry of Housing, Communities and Local Government. Each certificate holds the address, the postcode, the property type and built form, the total floor area in square metres, the construction age band, the energy rating from A to G, and a unique property reference number, the UPRN. Certificates exist only for homes that have been built, sold, let or improved since the scheme began, so some addresses have none, and a home can have several certificates over time.

Why the join is hard

Price Paid Data has no stable property identifier. The EPC register has a UPRN, but a sale record does not, so the join has to be made on address text. HM Land Registry has said it will add UPRNs to Price Paid Data, which should make joins far easier, but historical data will still need address matching. Meanwhile the two datasets write addresses differently, and the differences are systematic, not random.

The matching strategy

A reliable approach works in passes, from strictest to loosest, and stops at the first pass that matches.

Normalise both sides. Upper-case everything, remove punctuation, collapse spaces, expand common abbreviations (RD to ROAD, CL to CLOSE), and strip the postcode of spaces.
Match on postcode plus PAON plus SAON plus street. This resolves most houses and many flats exactly.
Match on postcode plus house number and street, ignoring locality. Many addresses differ only in the locality or town line.
Match flats on postcode plus the flat number and building name. In EPC data a flat may be written as FLAT 3, 12 HIGH ROAD, while Price Paid Data holds SAON FLAT 3 and PAON 12, or just 3 and 12.
Fall back to the closest EPC for the street number, flagging the match as low confidence.

Keep a column recording which pass produced each match, so you can report the share of exact matches and exclude fuzzy ones from sensitive statistics.

Failure modes to expect

Flats written in different orders. FLAT 3, 12 HIGH ROAD in one dataset becomes 3 12 HIGH ROAD in the other.
House names instead of numbers. Homes called by name, such as a house name with no number, need a match on name and postcode, and some have changed name since the certificate.
Missing certificates. A property that has never been sold, let or improved since certificates began has none. In CR5, 265 of 2,672 addresses, which is 10%, have no construction age band for this reason.
Several certificates for one home. Use the most recent lodgement date, but keep the older ones if you want to study improvements.
Conversions and splits. A house converted into flats keeps one old certificate and several new ones, and sales before the conversion belong to a different property.
Deleted and changed Price Paid records. If you ignore status D you will count sales that were removed.

A CR5 worked example

After joining, CR5 has 2,672 addresses and 1,398 recorded sales between 1 January 2019 and 24 July 2026. The median sold price is £500,000. The join lets you split that by property type and by construction age band.

By type, the median is £690,000 for detached houses across 413 sales, £530,000 for semi-detached across 412 sales, £442,000 for end-terrace across 73 sales, £422,500 for mid-terrace across 165 sales and £300,000 for flats across 328 sales. By construction age band, 876 addresses, which is 33%, were built between 1930 and 1949, and the median by band is £530,000 for 1930 to 1949 (437 sales), £556,000 for 1950 to 1966 (167 sales), £480,000 for 1900 to 1929 (164 sales) and £425,000 for 2003 to 2006 (83 sales). Flats are 31% of the addresses built from 2003 to 2006 and only 7% of those built from 1930 to 1949, which is why the newest band has a lower median than the 1930s band.

Without the EPC join you could not produce the second table at all, because Price Paid Data carries no age band. With it, you can explain why price follows built form, and the village pages built on it, such as Chipstead at £600,000 and Hooley at £423,000, show a clear link between the share of flats and the village median.

Statistics pitfalls once joined

Three mistakes recur. The first is mixing property types: compare detached with detached and flats with flats, because composition effects dominate. The neighbouring district CR8 has a lower overall median than CR5 (£455,000 against £500,000) even though detached, semi-detached and flat medians are all higher there, because flats are 44% of its sales against 23% in CR5. The second is ranking streets on tiny samples: a median from three sales moves a long way on one transaction, so require a minimum count and display it. The third is ignoring the publication delay: Price Paid Data is published with a lag, so the latest months are incomplete and a partial year looks like a fall in activity.

Code sketch

import pandas as pd, re

def norm(s):
    s = re.sub(r"[^A-Z0-9 ]", " ", str(s).upper())
    s = re.sub(r"\bRD\b", "ROAD", s)
    s = re.sub(r"\bCL\b", "CLOSE", s)
    return re.sub(r"\s+", " ", s).strip()

ppd["key1"] = (ppd.postcode.str.replace(" ", "") + "|" + ppd.paon.map(norm) + "|" + ppd.saon.fillna("").map(norm) + "|" + ppd.street.map(norm))
epc["key1"] = (epc.postcode.str.replace(" ", "") + "|" + epc.paon.map(norm) + "|" + epc.saon.fillna("").map(norm) + "|" + epc.street.map(norm))
epc = epc.sort_values("lodgement_date").drop_duplicates("key1", keep="last")
joined = ppd.merge(epc[["key1", "floor_area", "age_band", "rating", "uprn"]], on="key1", how="left")
share = joined.age_band.notna().mean()
print("matched to an EPC (per cent):", round(share * 100))

Run the later passes only on the rows still unmatched, and record the pass number in a column.

How to validate the join

A join that runs without errors can still be wrong, so test it. Start with the match rate by pass: if more than a few per cent of rows are matched only in the last pass, your normalisation is missing a pattern. Check property type agreement, because a sale recorded as a flat that joins to an EPC for a detached house is almost certainly a bad match. Compare floor area against price: a sale of a 40 square metre home at £1.5 million signals a mismatch or a data error. Look at the age band by street, because one street of 1930s semi-detached houses should not contain a 2012 band unless there is a new development on it. Finally, count addresses per street against an independent source, such as the street tables for CR8 or CR3, to confirm you have not dropped or duplicated homes. In CR8 the join covers 3,892 addresses and 2,009 sales, and in CR3 3,853 addresses and 1,838 sales, which gives a sense of the scale a district-level join should reach.

Three questions the joined data answers

First, how does price vary with build era? Because the EPC register records construction age band, you can show that homes built from 1930 to 1949 sell for a median of £555,000 in CR8 while homes built from 2012 onwards sell for £425,000, with 82% of the newest addresses being flats. Second, how large is the premium for space? With floor area you can compute a price per square metre and compare it with the street median, which helps to separate a genuinely expensive street from one that simply has large houses. Third, which areas look expensive only because of their property mix? Village figures such as Woodcote at £600,000 and Purley at £427,500 need the type and age splits before they can be compared fairly.

Frequently asked questions

Does Price Paid Data include every sale? No. It leaves out some transactions, such as non-market transfers, and recent months are filled in over time.

Can I use the UPRN to join? The EPC register has a UPRN, and HM Land Registry has announced that UPRNs will be added to Price Paid Data, which will simplify future joins. For historical sales you still need address matching.

How often are the datasets updated? Price Paid Data is published monthly, and the EPC register is updated as new certificates are lodged.

What match rate is realistic? In the CR5 example about 90% of addresses have a construction age band. The remainder have no usable certificate or could not be matched.

Is a median a valuation? No. A median describes past sales and is not an estimate of what a particular home is worth today.

Licences and attribution

Price Paid Data and the EPC register are both published under the Open Government Licence, and you should check the terms for the address fields in the EPC data before redistributing them. OpenStreetMap data, if you add places, is under the Open Database Licence and needs the credit "© OpenStreetMap contributors". The data sources and licences used for the examples above are listed on the data sources and licensing page.

Where to see the result

Street-level tables built on this join are published for every street in CR5, and the house price rankings compare streets, the biggest and oldest homes and districts by price and energy efficiency. For budgeting around a purchase, the free property tools calculate stamp duty and mortgage payments.

Summary

Price Paid Data has the price and no attributes; the EPC register has the attributes and no price. Joining them on normalised address text, in passes from strict to loose, gives you a dataset that can answer questions neither source can alone. Expect about ten per cent of addresses to have no certificate, keep a confidence flag on every match, compare like with like, and always show the number of sales behind a median.

About the author: Door Number is a free property information site for England and Wales that builds its pages from open datasets and credits the source of every figure.

Street-level median house prices from HM Land Registry Price Paid Data in Python

Ashleigh Giles — Sun, 08 Nov 2026 23:05:19 +0000

To get street-level median house prices in England and Wales, download HM Land Registry Price Paid Data, filter by postcode prefix, group by street and take the median, keeping only streets with enough sales. This tutorial does it with pandas in under 20 lines, then shows why the result needs a minimum sample size and a property-type split.

I run Door Number, which publishes street-level tables built on this data. Checked on 8 November 2026.

What does the Price Paid Data file look like?

The yearly CSV files (pp-2019.csv to pp-2026.csv) have no header row. Each record has 16 fields, in this order: transaction ID, price, date of transfer, postcode, property type (D detached, S semi-detached, T terraced, F flat or maisonette, O other), old or new build (Y or N), tenure (F freehold, L leasehold), PAON (house number or name), SAON (flat number), street, locality, town or city, district, county, PPD category type (A or B) and record status (A added, C changed, D deleted). The data is published under the Open Government Licence v3.0.

How do you calculate the street medians?

import pandas as pd

cols = ["id", "price", "date", "postcode", "ptype", "new", "tenure",
        "paon", "saon", "street", "locality", "town", "district",
        "county", "category", "status"]

files = [f"pp-{y}.csv" for y in range(2019, 2027)]
df = pd.concat(pd.read_csv(f, names=cols, parse_dates=["date"]) for f in files)

cr5 = df[df.postcode.str.startswith("CR5 ", na=False)]

streets = (cr5.groupby("street").price
           .agg(["median", "count"])
           .query("count >= 3")
           .sort_values("median", ascending=False))
print(streets.head())

Note the trailing space in startswith("CR5 "). Every full UK postcode has a space after its outward code, so this selects exactly the CR5 district and nothing longer.

How do you split by property type?

by_type = cr5.groupby("ptype").price.agg(["median", "count"])
print(by_type)

Splitting by type is not optional. On Door Number's CR5 dataset, the median detached sale since 2019 is £690,000 (413 sales) and the median flat is £300,000 (328 sales). A street of flats and a street of detached houses cannot be ranked together.

What does the result look like?

Door Number's published table, which joins sales to EPC data, puts Caversham Close at the top of CR5 with a median of £1,230,000 from six detached sales. Compare it with your own output, and expect small differences where street names are spelled differently across records. The full table is at every street in CR5 with sold prices.

Which pitfalls catch people out?

Records with status D are deletions. If you merge monthly update files, drop them; the complete file already reflects them.
Price Paid Data excludes some transactions, such as non-market sales, and the latest months are incomplete.
Street names can be blank for some rural or named properties, so check for NaN before grouping.
Medians from three sales are fragile. Show the count next to every median, as above.
To compare postcodes or single homes rather than streets, the same grouping works on the full postcode column. See an example postcode page, CR5 3LZ, or a single property record, 14 Jennys Way, Netherne-on-the-Hill.

Where else can you go from here?

Join the sales to the EPC register for floor area and construction age band, or to Census 2021 for area context. HM Land Registry has announced that it will add UPRNs to Price Paid Data from 28 August 2026, which should make that join far easier. Door Number lists every dataset and licence on its data sources page, and has free calculators on its tools page. For national context, see house prices by region and the national street rankings.

About the author: Ashleigh Giles runs Door Number, which publishes house prices and local information from open UK data, with sources credited on every page.

Open UK property data explained: Price Paid, EPC, Census 2021 and OpenStreetMap

Ashleigh Giles — Sun, 08 Nov 2026 22:32:57 +0000

If you want to build anything with UK house price data, four free datasets do most of the work: HM Land Registry Price Paid Data, the EPC register, ONS Census 2021 and OpenStreetMap. This post covers what each one contains, how they are licensed, where they join badly, and what a street-level result looks like using postcode district CR5 (Coulsdon, in the London Borough of Croydon) as a worked example.

I run Door Number, a free property information site built on these datasets. Everything below was checked on 8 November 2026.

What does each dataset contain?

Dataset	Publisher	What you get	Licence
Price Paid Data	HM Land Registry	Every residential sale in England and Wales: price, date, postcode, property type, new build flag, freehold or leasehold, address parts	Open Government Licence v3.0
EPC register	MHCLG	Energy certificates: floor area, construction age band, built form, energy rating, UPRN	Open Government Licence (check the terms on address fields)
Census 2021	ONS	Population, households, tenure, travel, by output area and local authority	Open Government Licence v3.0
OpenStreetMap	OSM contributors	Roads, buildings, shops, schools, transport	Open Database Licence (ODbL), credit "© OpenStreetMap contributors"

Price Paid Data records property type as a single letter (D detached, S semi-detached, T terraced, F flat or maisonette, O other), tenure as F or L, and a record status of A (added), C (changed) or D (deleted), so a correct import has to handle updates and deletions, not only inserts. HM Land Registry has announced that it will add UPRNs to Price Paid Data from 28 August 2026, which should make joins to the EPC register far easier.

Where do the datasets join badly?

Price Paid Data has no stable identifier for a property, so matching a sale to an EPC means matching addresses: the PAON (house number or name), SAON (flat), street and postcode. Typical failure modes:

Flats written "Flat 3, 12 High Road" in one dataset and "3 12 High Road" in another
House names instead of numbers
Properties with no EPC at all, because certificates only exist when a property was sold, let or improved
Several certificates for one property, so you need the most recent

In the CR5 dataset (2,672 addresses), 265 (10%) have no construction age band on the EPC register.

What do you learn once the joins are done?

A worked example from CR5, using sales since 1 January 2019 (1,398 sales):

Median sold price, all property types: £500,000
Detached £690,000 (413 sales), semi-detached £530,000 (412), flat £300,000 (328)
33% of addresses (876 of 2,672) were built 1930 to 1949, according to the EPC construction age band

Because the median is driven by the mix of property types, comparing two areas by their overall median can mislead. The neighbouring district CR8 (Purley and Kenley) has a lower overall median than CR5 (£455,000 against £500,000) even though detached homes, semis and flats each sell for more there, because flats are 44% of its sales against 23% in CR5.

What mistakes should you avoid?

Do not treat Price Paid Data as complete. It excludes some transactions, such as non-market sales, and recent months are filled in over time.
Do not rank streets on tiny samples. A median from three sales moves a long way on one transaction; require a minimum count and show it.
Do not mix property types in one comparison. Group by detached, semi-detached, terraced and flat first.
Do state the period. A median since 2019 will not match a 12-month figure.

Sources

HM Land Registry Price Paid Data, EPC register (MHCLG), ONS Census 2021, OpenStreetMap, all as above. Street-level tables for CR5 are published at doornumber.co.uk/postcode-district/cr5/streets, and the data sources and licensing page lists every dataset used: doornumber.co.uk/data-sources.

About the author: Ashleigh Giles runs Door Number, which publishes house prices and local information from open UK data, with sources credited on every page.