How to Standardise Attribute Values Against a Controlled Vocabulary

Problem statement

The land-use column has 4 categories. value_counts() finds 61.

parcels["land_use"].value_counts()
# Residential      4102
# residential      1877
# RESIDENTIAL       902
# Res.              441
# Residentail       118      ← typo
# Housing            96
# Dwelling           41
# Commercial       2911
# commercial        802
# Comm              310
# ...

Every groupby("land_use") produces 61 rows instead of 4. Every choropleth has 61 colours. Every percentage is wrong, because "Residential" and "residential" are counted as different things.

Normalising the column — trimming, lower-casing, collapsing whitespace — fixes about half of it. Res., Housing and Residentail survive, because they are not formatting differences. They are different words for the same category, and no string function knows they are the same.

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 pandas as pd

# the vocabulary: what values are allowed to exist
LAND_USE = {"residential", "commercial", "industrial", "mixed"}

# the mapping: every observed variant → an allowed value
SYNONYMS = {
    "res": "residential", "housing": "residential", "dwelling": "residential",
    "residentail": "residential",                       # known typo
    "comm": "commercial", "retail": "commercial", "shop": "commercial",
    "ind": "industrial", "industry": "industrial", "warehouse": "industrial",
    "mixed use": "mixed", "mix": "mixed",
}

def standardise(series, vocabulary, synonyms, *, strict=True):
    key = (series.astype("string").str.strip().str.lower()
                 .str.replace(r"[.\-_/]", " ", regex=True)
                 .str.replace(r"\s+", " ", regex=True))
    mapped = key.map(lambda v: synonyms.get(v, v) if pd.notna(v) else v)

    unknown = sorted(set(mapped.dropna()) - vocabulary)
    if unknown and strict:
        raise ValueError(f"{len(unknown)} unmapped values: {unknown[:10]}")
    return mapped, unknown

parcels["land_use"], unknown = standardise(parcels["land_use"], LAND_USE, SYNONYMS)
parcels["land_use"].value_counts()
# residential    7577
# commercial     4023
# industrial     1102
# mixed           285

Piece	What it is	Where it lives
vocabulary	the values allowed to exist	a set, in code or a config file
synonyms	every known variant → an allowed value	a dict, or a two-column CSV
normalisation	case, whitespace, punctuation	one function, applied before lookup
unmapped policy	what happens to a value nobody anticipated	raise, or flag as `unknown`

The fourth 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

Three panels separating formatting variation, synonym variation and genuinely new values. — Only the first is fixable by string functions. The second needs a mapping; the third needs a person.

Step-by-step solution

Vertical steps from surveying values through normalising, mapping, handling unknowns and asserting. — Survey first. A mapping written without looking at the data maps values that do not exist.

1. Survey what is actually there

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

The tail is where the work is. A column with 61 values where the top 4 cover 94% of rows is a formatting problem plus a handful of synonyms; a column where the top 4 cover 30% is a genuinely uncontrolled field and needs a different approach.

2. Normalise before mapping, and keep the two separate

import re

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)        # Res. → res, mixed-use → mixed use
    s = re.sub(r"[^\w\s]", "", s)          # strip remaining punctuation
    return re.sub(r"\s+", " ", s).strip()

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("  Res. ") == "res"
    assert normalise_key("MIXED-USE") == "mixed use"
    assert normalise_key("Residential") == "residential"
    # it must NOT know that "housing" means "residential"
    assert normalise_key("Housing") == "housing"

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

def propose_synonyms(series, vocabulary):
    """Every unmapped value, with a suggested target and its row count."""
    from rapidfuzz import process, fuzz
    key = series.map(normalise_key)
    counts = key.value_counts()
    unmapped = [v for v in counts.index if v and v not in vocabulary]

    rows = []
    for value in unmapped:
        match, score, _ = process.extractOne(value, list(vocabulary), scorer=fuzz.WRatio)
        rows.append({"value": value, "rows": int(counts[value]),
                     "suggested": match, "score": score})
    return pd.DataFrame(rows).sort_values("rows", ascending=False)

print(propose_synonyms(parcels["land_use"], LAND_USE).head(12))

        value  rows    suggested  score
0         res   441  residential     90
1     housing    96  residential     45
2 residentail   118  residential     92
3    dwelling    41  residential     32
4        comm   310   commercial     90

Fuzzy matching proposes; a person decides. residentail → residential at 92 is obviously right. housing → residential at 45 is right and the score would never have found it. dwelling → residential at 32 is right and looks like noise. Auto-applying above a threshold would take the first, miss the other two, and eventually map something wrong with confidence.

Sorting by row count puts the decisions that matter at the top: fixing res (441 rows) matters more than a variant appearing twice.

4. Decide what happens to values nobody anticipated

def standardise(series, vocabulary, synonyms, *, on_unknown="raise"):
    key = series.map(normalise_key)
    mapped = key.map(lambda v: synonyms.get(v, v) if v is not None else None)
    unknown_mask = mapped.notna() & ~mapped.isin(vocabulary)

    if unknown_mask.any():
        found = sorted(mapped[unknown_mask].unique())
        if on_unknown == "raise":
            raise ValueError(f"{unknown_mask.sum()} rows have {len(found)} "
                             f"unmapped values: {found[:10]}")
        if on_unknown == "flag":
            mapped = mapped.mask(unknown_mask, "unknown")
        elif on_unknown == "null":
            mapped = mapped.mask(unknown_mask, pd.NA)
        # on_unknown == "keep" leaves them alone
    return mapped, unknown_mask

Policy	Effect	Use when
`raise`	the pipeline stops	a scheduled job with a stable supplier — a new value is news
`flag`	becomes `"unknown"`, stays countable	exploratory work; the total still reconciles
`null`	becomes `NA`	the value is unusable and `groupby` should skip it
`keep`	passes through unmapped	never in production — it recreates the original problem

raise is the right default for a nightly job. A supplier adding "Agricultural" should interrupt somebody, not silently vanish into a bucket.

5. Never lose the original

parcels["land_use_raw"] = parcels["land_use"]           # before anything
parcels["land_use"], unknown = standardise(...)

One extra column, and every later question — "why is this parcel residential?" — 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):
    values = set(series.dropna().unique())
    unexpected = values - 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"

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.

Code examples

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.

# vocabularies.yml
land_use:
  allowed: [residential, commercial, industrial, mixed]
  on_unknown: raise
  synonyms:
    res: residential
    housing: residential
    dwelling: residential
    residentail: residential      # supplier typo, seen 2024-03
    comm: commercial
    retail: commercial
    ind: industrial
    warehouse: industrial
    mixed use: mixed

tenure:
  allowed: [freehold, leasehold, commonhold, unknown]
  on_unknown: flag
  synonyms:
    free: freehold
    lease: leasehold
    fh: freehold
    lh: leasehold

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()},
            "on_unknown": spec.get("on_unknown", "raise"),
        }
        for col, spec in raw.items()
    }

def apply_vocabularies(gdf, vocabularies):
    report = {}
    for col, spec in vocabularies.items():
        if col not in gdf.columns:
            continue
        gdf[f"{col}_raw"] = gdf[col]
        gdf[col], unknown = standardise(
            gdf[col], spec["allowed"], spec["synonyms"], on_unknown=spec["on_unknown"]
        )
        report[col] = {
            "distinct_before": gdf[f"{col}_raw"].nunique(),
            "distinct_after": gdf[col].nunique(),
            "unmapped_rows": int(unknown.sum()),
        }
    return gdf, report

parcels, report = apply_vocabularies(parcels, load_vocabularies("vocabularies.yml"))
print(report)
# {'land_use': {'distinct_before': 61, 'distinct_after': 4, 'unmapped_rows': 0},
#  'tenure':   {'distinct_before': 11, 'distinct_after': 4, 'unmapped_rows': 18}}

The dated comment next to residentail is worth the habit. A synonym table accumulates entries whose reason nobody remembers, and a table nobody trusts gets rewritten from scratch.

Example 2: a drift check for the next delivery

def vocabulary_drift(series, vocabulary, synonyms):
    """What is new in this file that the mapping has not seen before?"""
    key = set(series.map(normalise_key).dropna())
    known = vocabulary | set(synonyms)
    new = sorted(key - known)
    if new:
        counts = series.map(normalise_key).value_counts()
        return pd.DataFrame(
            [{"value": v, "rows": int(counts[v])} for v in new]
        ).sort_values("rows", ascending=False)
    return pd.DataFrame(columns=["value", "rows"])

Run it on arrival, before anything else. It answers "did the supplier change their coding?" in one line, and it is the check that turns a nightly raise from an interruption into a two-minute update.

Example 3: standardising codes that carry structure

Some vocabularies are not free text but codes with a grammar — and those should be validated rather than mapped:

import re

UPRN = re.compile(r"^\d{1,12}$")
POSTCODE = re.compile(r"^[A-Z]{1,2}\d[A-Z\d]? ?\d[A-Z]{2}$")

def standardise_postcode(series):
    s = series.astype("string").str.upper().str.replace(r"\s+", "", regex=True)
    # canonical form: always one space before the last three characters
    s = s.str.replace(r"^(.+)(\d[A-Z]{2})$", r"\1 \2", regex=True)
    valid = s.str.match(POSTCODE, na=False)
    return s, ~valid & s.notna()

parcels["postcode"], bad = standardise_postcode(parcels["postcode"])
print(f"{bad.sum()} postcodes do not match the expected format")

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.

Explanation

The unmapped policy is the decision that determines whether this survives the next delivery.

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 "Housing" means "residential" 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_land_use() mixing .str.lower() with "housing" → "residential" 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. "Housing" and "residential" share no letters in common order; "Residential" and "Residents Association" share almost all of them. Any threshold that catches the first will also merge the second. Fuzzy matching earns its place as a way to sort the work — putting residentail at 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 "Agricultural" has been silently absent from every report.

Edge cases or notes

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 — "MacDonald" becomes "Macdonald", "O'Brien" becomes "O'Brien". Only title-case for display, never for keys.
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 "Housing" and "residential" 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: "dwelling" and "residential" share almost no letters, while "Residential" and "Residents Association" share almost all.

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 residential?" 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.

How to Standardise Attribute Values Against a Controlled Vocabulary #

Problem statement #

Quick answer #

Three kinds of variation #

Step-by-step solution #

1. Survey what is actually there #

2. Normalise before mapping, and keep the two separate #

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

4. Decide what happens to values nobody anticipated #

5. Never lose the original #

6. Assert the invariant, in the pipeline #

Code examples #

Example 1: the vocabulary as data, not code #

Example 2: a drift check for the next delivery #

Example 3: standardising codes that carry structure #

Explanation #

Edge cases or notes #

Internal links #

FAQ #

Why is normalising not enough? #

Should I auto-map with fuzzy matching? #

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

Where should the vocabulary live? #

Do I need to keep the original values? #

How do I detect that a supplier changed their coding? #

What about codes like postcodes or reference numbers? #