A checklist for cleaning UK address data before modelling

Address data is the quiet troublemaker in most UK datasets. It looks harmless in a spreadsheet: twelve columns, a few thousand rows, postcodes mostly filled in. Then you join it to deprivation indices or sales territories and a third of the matches vanish. Build a model on top and you end up with a regional effect that is really just the difference between "Kent", "KENT" and "kent".

Cleaning addresses is unglamorous work, but it is finite. The checklist below covers the three chores that cause most of the damage: postcodes, county names and missing values. Work through them in order and you will spend less time explaining odd results later.

Profile the data before you change anything

Run a quick profiling pass first. How many distinct values sit in the county column? What proportion of postcodes are blank? What is the distribution of postcode string lengths, and how many contain a double space or a trailing dot? Pull twenty random rows and read them with your own eyes. You will spot patterns in ten minutes that no summary table will show you.

Keep the originals. Add cleaned columns alongside the raw ones, so postcode stays untouched and postcode_clean holds your version. When somebody questions a strange cluster six months from now, you will want to see what the source system actually said.

Validate postcodes properly

UK postcodes are not random strings. Each one has an outward code of two to four characters, a single space, and an inward code of exactly three characters: a digit followed by two letters. So SW1A 1AA and DN55 1PT follow the same rules as M1 1AE, and a regular expression such as [A-Z]{1,2}[0-9][A-Z0-9]? ?[0-9][A-Z]{2} catches most of the shapes you will meet. It will not catch everything, because a handful of special cases exist, which is why pattern matching is only the first gate.

1. Uppercase the value and remove all spaces, then strip any characters that are not letters or digits.
2. Reinsert the single space before the final three characters.
3. Test the result against your pattern.
4. Check that the postcode actually exists against a reference file. The ONS Postcode Directory is the usual free starting point for that, and it gives you geography lookups at the same time.
5. Write the outcome to a status column: valid, valid-but-not-found, malformed, missing. Flags are more useful than deletions, because a malformed postcode often signals a data entry problem worth fixing upstream.

Two cautions. Postcodes are living things: they get retired, split and reused, so note which reference vintage you used. And a postcode maps to several geographies at once. Store the one you actually need, and name the column after it rather than calling it "county" and hoping.

Sort out the county column

County is the field most likely to be nonsense. Postal counties were dropped from Royal Mail addressing years ago, and administrative boundaries have shifted repeatedly since, so a single column can hold Middlesex, Avon, Humberside, Greater Manchester and the occasional "Nr Leeds". None of these are wrong in isolation; they simply answer different questions.

Decide what the field is for before you standardise it, then apply rules like these:

• Standardise case and whitespace, and strip punctuation such as full stops and stray commas. Normalise apostrophes so "St Helen's" and "St Helens" collapse together.
• Map known variants to one canonical name: Beds to Bedfordshire, Gtr Manchester to Greater Manchester, Co Durham to County Durham.
• Watch for genuinely ambiguous names. Richmond is in London and in North Yorkshire; Newport is in Wales and on the Isle of Wight; St Ives is in Cambridgeshire and in Cornwall. Only postcode context can separate them.
• Do not "correct" a county using a postcode lookup unless you are explicit about which boundary vintage the lookup uses. Otherwise you will silently mix two geographies in one column.
• If the column is beyond repair, keep it for display and build a derived region column for modelling instead.

Tidy the address lines

Free-text address lines are where duplicates breed. Run the same normalisation across every line: trim, collapse repeated spaces, remove trailing punctuation, and choose one case convention and stick to it. Then handle the known variations.

• Pick one form for common abbreviations and apply it everywhere: ST or STREET, RD or ROAD, AVE or AVENUE, FLT or FLAT.
• Split flat and building where they are jammed together, so "Flat 3 24 High Street" becomes two fields. Flat-level rows behave quite differently from building-level rows in any model that counts households.
• Drop trailing country names such as United Kingdom or UK, and remove duplicated postcodes that have been copied into line three.
• If matching against a reference matters, consider a proper address product and a fuzzy match with an explicit threshold. Anything sitting near the threshold goes to a human, not straight into the model.

Handle missing fields deliberately

Blank, NULL, "N/A", "-", "unknown" and "0" all mean different things, and lumping them together destroys the signal. Separate not collected from not applicable from not yet known, and record which one you found.

Missing postcode

Try to recover it from other parts of the record, or from the source system, before you give up. What you should not do is fill it with the most common postcode in the area. That places invented households in real places, and if the missingness is not random, say it only affects customers who signed up through one channel, the model learns that artefact instead of the geography. Keep a missing indicator and fall back to a coarser geography where you have one.

Missing county or region

Derive it from the postcode using a lookup rather than guessing from nearby rows. Where the postcode is missing too, leave the field blank and let the indicator carry the information.

Missing address detail

If the house number is absent but the street is present, street-level features may still be useful. Only drop rows when the address is genuinely the thing you are modelling. For most prediction tasks, an address is a feature, not a target, and a flagged partial row beats a deleted one.

Automate the checks and keep them honest

Turn these rules into code, test them on a fixture file with known problems, and run them as a validation step every time the data is refreshed. Useful assertions include: the postcode match rate should not fall between runs; the set of unrecognised county values should shrink over time, never grow; and no row should leave the pipeline with an empty geography when a valid postcode was present on entry.

Keep a small review queue of flagged rows. Twenty or thirty examples read by a person each month will tell you more about upstream data quality than any dashboard.

A workable order of operations

Preserve the raw columns, profile the data, normalise formatting, validate postcodes, derive geography from the postcode, resolve county and casing, then decide your missing-value policy and write it down. Automate the whole sequence once it behaves.

You do not need every step to see a benefit. Fixing postcode formatting and consolidating county variants alone will sharpen most joins noticeably. Start there, keep the rules in version control, and revise them when the data changes rather than rebuilding the logic from memory.

Photo: occcu / Pixabay