How to Standardise Attribute Values Against a Controlled Vocabulary

Problem statement

Every bus stop in Great Britain's national stop database, NaPTAN, has an Indicator: a few words saying where the stop sits relative to the landmark it is named after — opposite the Crown, outside the library, stand C in the bus station. It is meant to be a short standard form. Here is what West Yorkshire's 16,677 stops actually hold (the setup is under The data used on this page):

ind = stops["Indicator"]
print(f"{ind.nunique()} distinct values in {ind.notna().sum():,} filled rows; {ind.isna().sum():,} empty")
print(ind.value_counts().head(8).to_string())
print("…and at the bottom:", sorted(ind.value_counts()[lambda c: c == 1].index)[:12])

237 distinct values in 8,395 filled rows; 8,282 empty
Indicator
at         2478
opp        2112
stop        838
adj         469
near        227
N bound     223
after       220
before      199
…and at the bottom: ['1ST', '2ND', '3RD', 'Adj', 'Att 553', 'Bay 1', 'Bay 10', 'Bay 11', 'Bay 2', 'Bay 3', 'Bay 4', 'Bay 5']

Every groupby("Indicator") produces more than two hundred groups instead of a dozen or so. "opp" and "Opp" are counted as different things; so are "stand C" and "Stand C".

Normalising the column — trimming, lower-casing, collapsing punctuation — fixes some of it. opp, op and aopp survive, because they are not formatting differences. Some are different words for the same category, some are typos, and some are a landmark typed into the wrong field — and no string function knows which is which.

That mapping has to be written down. The written-down version is a controlled vocabulary.

Quick answer

Define the allowed values, map everything else onto them, and fail loudly on anything unmapped:

import re

import pandas as pd

# the vocabulary: what values are allowed to exist
POSITION = {"at", "on", "in", "opposite", "adjacent", "outside", "near", "before", "after", "corner",
            "direction", "bay", "entrance", "unspecified"}

# the mapping: every known variant → an allowed value
SYNONYMS = {"opp": "opposite", "op": "opposite", "adj": "adjacent", "o s": "outside", "nr": "near",
            "inside": "in", "corner of": "corner", "by": "near", "next": "adjacent",
            "stop": "unspecified", "stand": "bay"}

# values with a grammar are recognised by pattern, not listed one by one
PATTERNS = {r"(n|s|e|w|ne|nw|se|sw) bound": "direction",
            r"(stop|stand|bay) [a-z]{0,2}\d{0,2}": "bay",
            r"((main|west) )?entrance( \d+)?": "entrance"}


def normalise_key(value):
    """Formatting only. No semantic decisions here."""
    if pd.isna(value):
        return None
    s = str(value).strip().lower()
    s = re.sub(r"[.\-_/]+", " ", s)        # N-bound → n bound, o/s → o s
    s = re.sub(r"[^\w\s]", "", s)          # strip remaining punctuation
    return re.sub(r"\s+", " ", s).strip() or None



def to_category(key, synonyms=SYNONYMS, patterns=PATTERNS):
    if not isinstance(key, str):                # None, or pandas' NaN for a missing value
        return None
    key = synonyms.get(key, key)
    for pattern, category in patterns.items():
        if re.fullmatch(pattern, key):
            return category
    return key


def standardise(series, vocabulary=POSITION, *, on_unknown="raise"):
    mapped = series.map(normalise_key).map(to_category)
    unknown = mapped.notna() & ~mapped.isin(vocabulary)
    if unknown.any():
        found = sorted(mapped[unknown].unique())
        if on_unknown == "raise":
            raise ValueError(f"{unknown.sum()} rows have {len(found)} unmapped values: {found[:12]}")
        if on_unknown == "flag":
            mapped = mapped.mask(unknown, "unknown")
        elif on_unknown == "null":
            mapped = mapped.mask(unknown, None)
    return mapped, unknown


try:
    standardise(stops["Indicator"])
except ValueError as err:
    print(f"ValueError: {err}")

ValueError: 38 rows have 31 unmapped values: ['1st', '2nd', '3rd', 'a', 'af', 'aopp', 'app', 'ar', 'atop', 'att', 'att 553', 'aty']

That is the vocabulary doing its job on the first run: 38 rows whose values nobody has decided about yet, named rather than silently passed through. Flag them for now and look at the result:

position, unknown = standardise(stops["Indicator"], on_unknown="flag")
print(position.value_counts().to_string())

Indicator
at             2480
opposite       2121
direction       950
unspecified     839
bay             493
adjacent        471
near            231
after           220
before          199
entrance        158
on              150
unknown          38
corner           23
outside          13
in                6

Two hundred and thirty-six values have become fourteen categories and a small, visible unknown.

The last one is the one that decides whether this holds up over time. A mapping that silently drops unrecognised values will quietly lose a whole category the day a supplier adds one.

Three kinds of variation

Survey, normalise, map from the survey, keep the original

1. Survey what is actually there

def survey(series, top=8):
    counts = series.value_counts(dropna=False)
    print(f"{len(counts)} distinct values, {counts.sum():,} rows")
    for value, n in counts.head(top).items():
        print(f"  {n:>7,}  {n / counts.sum():>6.1%}  {value!r}")
    tail = counts.iloc[top:]
    print(f"  … {len(tail)} more values covering {tail.sum():,} rows ({tail.sum() / counts.sum():.1%})")


survey(stops["Indicator"])

238 distinct values, 16,677 rows
    8,282   49.7%  <NA>
    2,478   14.9%  'at'
    2,112   12.7%  'opp'
      838    5.0%  'stop'
      469    2.8%  'adj'
      227    1.4%  'near'
      223    1.3%  'N bound'
      220    1.3%  'after'
  … 230 more values covering 1,828 rows (11.0%)

The tail is where the work is. Here the top eight values — one of them "empty" — cover 89% of rows, which says this is a formatting problem plus a handful of synonyms and structured codes. A column where the top values cover a third of the rows is a genuinely uncontrolled field and needs a different approach.

Half the rows are empty. That is not a formatting problem either: it is a decision — are empty indicators "unspecified", or missing? — and it belongs in the vocabulary too, not in whatever groupby happens to do with NaN.

2. Normalise before mapping, and keep the two separate

normalise_key in the quick answer is formatting only. Keeping normalisation and mapping in separate functions matters more than it looks. Normalisation is mechanical and testable in isolation; mapping is a set of decisions about meaning. Mixing them produces a function nobody can review, because there is no way to tell which lines are facts and which are opinions.

def test_normalise_is_formatting_only():
    assert normalise_key("  Opp. ") == "opp"
    assert normalise_key("N-bound") == "n bound"
    assert normalise_key("o/s") == "o s"
    assert normalise_key("*") is None
    # it must NOT know that "opp" means "opposite"
    assert normalise_key("opp") == "opp"



test_normalise_is_formatting_only()
keys = stops["Indicator"].map(normalise_key)
print(f"distinct values: {stops['Indicator'].nunique()} raw → {keys.nunique()} after normalising → "
      f"{keys.map(to_category).nunique()} after synonyms and patterns")

distinct values: 237 raw → 199 after normalising → 45 after synonyms and patterns

3. Build the synonym table from the survey, not from imagination

from rapidfuzz import fuzz, process


def propose_synonyms(series, vocabulary):
    """Every value nothing maps yet, with a suggested target and its row count."""
    keys = series.map(normalise_key)
    counts = keys.value_counts()
    unmapped = [k for k in counts.index if to_category(k) not in vocabulary]
    rows = []
    for value in unmapped:
        match, score, _ = process.extractOne(value, sorted(vocabulary), scorer=fuzz.WRatio)
        rows.append({"value": value, "rows": int(counts[value]), "suggested": match, "score": round(score)})
    return pd.DataFrame(rows).sort_values(["rows", "value"], ascending=[False, True]).reset_index(drop=True)


print(propose_synonyms(stops["Indicator"], POSITION).head(12).to_string())

      value  rows  suggested  score
0      atop     4         at     90
1         a     3   adjacent     90
2        ar     3       near     90
3       1st     1         at     60
4       2nd     1  direction     45
5       3rd     1      after     45
6        af     1      after     90
7      aopp     1   opposite     77
8       app     1         at     60
9       att     1         at     90
10  att 553     1         at     90
11      aty     1         at     90

Fuzzy matching proposes; a person decides. aopp → opposite is right, and scores only 77. ar → near and a → adjacent score 90 and are guesses — WRatio rates a short string that appears inside a longer one highly, whatever it means. And some cannot be decided from the text at all: is atop "at" with a typo, or "at top"? What does 3rd mean at a school with three stops? Auto-applying above a threshold would take the guesses with the same confidence as the fixes. Sorting by row count at least puts the decisions that matter first.

4. Decide what happens to values nobody anticipated

standardise takes the policy as an argument. Run it every way on the same column:

for policy in ("flag", "null", "keep"):
    out, unknown = standardise(stops["Indicator"], on_unknown=policy)
    print(f"{policy:5s} → {out.nunique(dropna=True):>3} distinct values, "
          f"{out.isna().sum():>5,} null, {unknown.sum()} rows unmapped")

flag  →  15 distinct values, 8,285 null, 38 rows unmapped
null  →  14 distinct values, 8,323 null, 38 rows unmapped
keep  →  45 distinct values, 8,285 null, 38 rows unmapped

null is worse than it looks on this column: the 38 unmapped rows vanish into the 8,285 empty ones, and no later count can separate "nobody entered anything" from "somebody entered something we did not understand".

5. Never lose the original

stops["Indicator_raw"] = stops["Indicator"]                       # before anything
stops["position"], _ = standardise(stops["Indicator"], on_unknown="flag")
print(stops.loc[stops["position"] == "unknown", ["ATCOCode", "CommonName", "Indicator_raw"]].head(5).to_string(index=False))

 ATCOCode                CommonName Indicator_raw
450012603 Bell Lane Bellmount Close           att
450013060          Priestley Avenue            ar
450013583              Fitzroy Road           aty
450014011   Spen Valley High School           3RD
450014012   Spen Valley High School           1ST

One extra column, and every later question — "why is this stop unknown?" — is answerable. Without it, the mapping is a one-way transformation and the evidence is gone. This is the same principle as recording what a repair changed.

6. Assert the invariant, in the pipeline

def assert_vocabulary(series, vocabulary, *, allow_null=True):
    unexpected = set(series.dropna().unique()) - vocabulary
    assert not unexpected, f"values outside the vocabulary: {sorted(unexpected)}"
    if not allow_null:
        assert series.notna().all(), f"{series.isna().sum()} null values"


assert_vocabulary(stops["position"], POSITION | {"unknown"})
try:
    assert_vocabulary(stops["Indicator"], POSITION)
except AssertionError as err:
    print(f"raw column: AssertionError: {str(err)[:90]}…")

raw column: AssertionError: values outside the vocabulary: ['*', '1ST', '2ND', '3RD', 'Adj', 'At', 'Att 553', 'Bay 1',…

Run it after standardising and after any join or concat — a merge with another dataset is the most common way an unmapped value reappears in a column you already cleaned.

NaPTAN stop names for West Yorkshire against a vocabulary

The data used on this page

NaPTAN, the Department for Transport's National Public Transport Access Nodes database, downloaded as CSV from its public API for ATCO area 450 (West Yorkshire): 16,677 stops with their CommonName, Indicator, Street and locality. Example 2 also downloads area 180 (Greater Manchester) to play the part of a new delivery.

import io

import pandas as pd
import requests


def naptan(area):
    # the NaPTAN API (naptan.api.dft.gov.uk/v1/access-nodes?dataFormat=csv&atcoAreaCodes=<area>)
    # as downloaded on 9 October 2026; the live register changes every week
    reply = requests.get(f"https://data.spatialworkflow.io/naptan-area-{area}-2026-10-09.csv", timeout=300)
    reply.raise_for_status()
    df = pd.read_csv(io.StringIO(reply.text), low_memory=False, dtype={"Indicator": "string"})
    return df[["ATCOCode", "CommonName", "Indicator", "Street", "LocalityName"]].sort_values("ATCOCode").reset_index(drop=True)


stops = naptan(450)
print(f"{len(stops):,} West Yorkshire stops")

16,677 West Yorkshire stops

Example 1: the vocabulary as data, not code

Once there is more than one column, the mapping belongs in a file that a domain expert can edit without touching Python.

%%writefile vocabularies.yml
Indicator:

  allowed: [at, "on", in, opposite, adjacent, outside, near, before, after, corner,
            direction, bay, entrance, unspecified]
  on_unknown: flag
  synonyms:
    opp: opposite
    op: opposite
    aopp: opposite          # typo, West Yorkshire 2026-09
    adj: adjacent
    next: adjacent
    o/s: outside
    nr: near
    by: near
    inside: in
    corner of: corner
    s bund: direction       # "S bund", one stop
    stop: unspecified
    stand: bay
  patterns:
    '(n|s|e|w|ne|nw|se|sw) bound': direction
    '(stop|stand|bay) [a-z]{0,2}\d{0,2}': bay
    '((main|west) )?entrance( \d+)?': entrance

import yaml


def load_vocabularies(path):
    raw = yaml.safe_load(open(path))
    return {col: {"allowed": set(spec["allowed"]),
                  "synonyms": {normalise_key(k): v for k, v in spec.get("synonyms", {}).items()},
                  "patterns": spec.get("patterns", {}),
                  "on_unknown": spec.get("on_unknown", "raise")}
            for col, spec in raw.items()}


def apply_vocabularies(df, vocabularies):
    report = {}
    for col, spec in vocabularies.items():
        raw = df[col]
        mapped = raw.map(normalise_key).map(lambda k: to_category(k, spec["synonyms"], spec["patterns"]))
        unknown = mapped.notna() & ~mapped.isin(spec["allowed"])
        if unknown.any() and spec["on_unknown"] == "raise":
            raise ValueError(f"{col}: unmapped {sorted(mapped[unknown].unique())[:10]}")
        df = df.assign(**{f"{col}_raw": raw, col: mapped.mask(unknown, "unknown")})
        report[col] = {"distinct_before": raw.nunique(), "distinct_after": df[col].nunique(),
                       "unmapped_rows": int(unknown.sum())}
    return df, report


vocabularies = load_vocabularies("vocabularies.yml")
standardised, report = apply_vocabularies(naptan(450), vocabularies)
print(report)

{'Indicator': {'distinct_before': 237, 'distinct_after': 15, 'unmapped_rows': 36}}

The quotes round "on" are not decoration. PyYAML follows YAML 1.1, where a bare on is a boolean:

print(yaml.safe_load("allowed: [at, on, in]"))

{'allowed': ['at', True, 'in']}

Without the quotes the vocabulary silently loses a category, and every stop whose indicator is "on" — 150 of them here — lands in unknown. The same happens to yes, no and off, which is worth remembering for any vocabulary of short words.

The dated comment next to aopp is worth the habit. A synonym table accumulates entries whose reason nobody remembers, and a table nobody trusts gets rewritten from scratch. The keys are normalised on load, which is why the file can say o/s — the way a person would write it — and still match.

Example 2: a drift check for the next delivery

def vocabulary_drift(series, spec):
    """What is in this delivery that the vocabulary has never seen?"""
    keys = series.map(normalise_key)
    category = keys.map(lambda k: to_category(k, spec["synonyms"], spec["patterns"]))
    new = keys[category.notna() & ~category.isin(spec["allowed"])]
    return new.value_counts().rename_axis("value").reset_index(name="rows")


manchester = naptan(180)
drift = vocabulary_drift(manchester["Indicator"], vocabularies["Indicator"])
print(f"{len(manchester):,} Greater Manchester stops: {len(drift)} values the West Yorkshire vocabulary "
      f"has never seen, on {drift['rows'].sum():,} rows")
print(drift.head(10).to_string(index=False))

21,527 Greater Manchester stops: 2808 values the West Yorkshire vocabulary has never seen, on 5,989 rows
                     value  rows
                       cnr   293
                  arrivals    62
                  motorway    58
            north entrance    47
            south entrance    47
             east entrance    45
           manchester road    32
         blackhorse street    26
wellington road s daw bank    25
              smith street    24

A different supplier, a different house style: Greater Manchester writes cnr for corner on 293 stops, names its bus-station entrances by compass point, and often puts a street name where West Yorkshire would put opp. Run the check on arrival, before anything else: it answers "did the coding change?" in one line, and it is what turns a nightly raise from an interruption into a two-minute update of the YAML file.

Example 3: values that carry structure

Some of this column is not free text but codes with a grammar — and those should be parsed and validated rather than listed:

BAY = re.compile(r"(stop|stand|bay) ([a-z]{0,2})(\d{0,2})")
BOUND = re.compile(r"(n|s|e|w|ne|nw|se|sw) bound")


def parse_indicator(value):
    key = normalise_key(value)
    if key is None:
        return {}
    if m := BAY.fullmatch(key):
        return {"bay": (m[2].upper() + m[3].lstrip("0")) or None}
    if m := BOUND.fullmatch(key):
        return {"heading": m[1].upper()}
    return {}


parsed = pd.DataFrame([parse_indicator(v) for v in stops["Indicator"]], index=stops.index)
print(f"bays parsed: {parsed['bay'].notna().sum()}, e.g. {sorted(parsed['bay'].dropna().unique())[:8]}")
print(f"headings parsed: {parsed['heading'].value_counts().to_dict()}")

bays parsed: 492, e.g. ['1', '10', '11', '12', '13', '14', '15', '16']
headings parsed: {'N': 223, 'S': 179, 'E': 165, 'W': 159, 'NE': 69, 'SE': 56, 'NW': 52, 'SW': 47}

A regex is the vocabulary here: the set of allowed values is infinite but the shape is fixed. The same three-part structure applies — normalise, validate, decide what happens to the failures — and parsing also recovers what the category throws away: stand C and Stop 05 both become bay, but the bay letter is the part a passenger needs.

A controlled vocabulary makes the domain explicit

A controlled vocabulary makes a column's domain explicit. Without one, the set of possible values is "whatever anyone has ever typed", which is unbounded, unknowable, and grows with every delivery. With one, the set is finite, written down, and checkable — which is what allows an assertion to exist at all.

The three-layer split — normalise, map, validate — matters because the layers have different natures. Normalisation is mechanical: lower-casing and trimming are correct regardless of domain, and can be tested exhaustively. Mapping is editorial: deciding that "by" means "near", or that a bare "stop" means "unspecified", is a judgement about how this organisation classifies things, and it needs review by someone who knows. Validation is structural: it enforces that only allowed values escape, and knows nothing about either of the other two.

Collapsing them into one function loses that. A single clean_indicator() mixing .str.lower() with "nr" → "near" cannot be reviewed by a domain expert (too much Python) or tested by a developer (too many domain assumptions).

The reason fuzzy matching only proposes is worth being firm about. String similarity measures spelling, and synonymy has nothing to do with spelling. "o/s" and "outside" share two letters; "ar" and "near" share the same two and are not known to be related at all — yet step 3 scored ar → near at 90. Any threshold that trusts the second will trust worse. Fuzzy matching earns its place as a way to sort the work — putting aopp near the top of the review list — not as a way to skip it.

Finally, the unmapped policy is where this either holds up or quietly rots. Data suppliers change their coding. When they do, keep reintroduces the original problem one value at a time; null deletes a category; flag keeps it visible; raise interrupts someone. For a job that runs unattended, the interruption is the feature — a new value in a controlled field is genuinely news, and the alternative is discovering six months later that a whole category — every stop marked cnr, say — has been silently absent from every report.

casefold, str.title and YAML booleans

• Case-only normalisation is not enough for non-ASCII. str.casefold() handles more than .lower(); consider unicodedata.normalize("NFKD", s) for accents.
• str.title() mangles real names — "KFC" becomes "Kfc" and "Sainsbury's" becomes "Sainsbury'S" (see repair, reject or flag). Only title-case for display, never for keys.
• YAML 1.1 reads on, off, yes and no as booleans. Quote short words in a vocabulary file, or a category silently disappears.
• Empty string and NaN are different. "" maps to "" and passes an isin check that NA would fail. Convert empties to NA early.
• Trailing whitespace survives shapefile round trips and is invisible in QGIS. Always trim on read.
• A synonym table with a cycle (a→b, b→a) loops forever if applied repeatedly. Map once, never iteratively.
• Order matters if synonyms map to synonyms. Resolve the table to its final targets before applying it.
• Keep the raw column, but drop it before delivery if the file format has a column limit — shapefile allows 255.
• A vocabulary should be versioned with the code, not maintained in a spreadsheet nobody can diff.

Internal links

• How to clean and normalise attribute columns in a GeoDataFrame — the formatting layer this builds on
• How to fuzzy-match place names when joining spatial data — the same scorers, used for joining
• How to validate a GeoDataFrame against a schema before analysis — where the vocabulary assertion belongs
• Repair, reject or flag? Choosing what cleaning should do — the unmapped policy as a cleaning decision
• Configuration vs code: what belongs in a config file — why the vocabulary is YAML
• How to handle missing and null values in spatial datasets — what null as a policy produces
• The Python GIS data cleaning checklist — where this fits in a full clean
• How to explore a spatial dataset you have never seen before — the survey step, in general

FAQ

Why is normalising not enough?

Normalising fixes case, whitespace and punctuation. It cannot know that "opp" and "opposite" are the same category — that is a decision about meaning, and it has to be written down.

Should I auto-map with fuzzy matching?

No. Use it to propose and rank, then decide by hand. Synonymy is unrelated to spelling: on this page WRatio scored ar → near and a → adjacent at 90, and the genuine typo aopp → opposite at only 77.

What should happen to a value the mapping does not know?

For a scheduled job, raise. A new value in a controlled field means the supplier changed something, and that deserves a human. For exploration, flag it as "unknown" so totals still reconcile.

Where should the vocabulary live?

In a config file once you have more than one column. It is domain knowledge, edited by people who are not writing Python, and it needs to be diffable.

Do I need to keep the original values?

Yes — one extra _raw column. Without it the mapping is irreversible and "why is this stop unknown?" has no answer.

How do I detect that a supplier changed their coding?

Compare the incoming distinct values against the vocabulary plus the synonym keys. Anything new is drift, and it is worth checking on arrival rather than mid-pipeline.

What about codes like postcodes or reference numbers?

Those have a grammar rather than a value list. Normalise to a canonical form, validate with a regex, and apply the same policy to the failures.