cleandataengineering.aiData Platform, from the big picture to the code
You are here: Start here › Bronze to silver › The thirteen stages › 6 Standardise

Data PlatformZoom level 3

Stage 6: standardise, one spelling for each value

The sixth stage makes the same thing look the same. A column can declare a value map, which replaces known spellings with the agreed one, and a letter case.

Without it, a region sent as N, North and north would be three regions in every report, and each report would grow its own fix.

Typed rowsregion as the source spells it6 Standardisevalue map, then letter caseThe same rowsregion in the agreed spelling
No row is added, removed or set aside.
Goes inRows after typing.
What happensFor each text column with a map or a case, the value is looked up in the map, then its case is changed.
Comes outThe same rows with the declared spellings.

A worked example

The dealer contract allows five regions: north, south, east, west and central_europe. Suppose the dealer interface sends short codes for some. These settings would bring them in line.

ExampleA value map and a case for one column. Typed for this page.
columns:
  - name: region
    values: {N: north, S: south, CE: central_europe}
    case: lower
region inAfter the mapAfter the caseRemark
NnorthnorthFound in the map.
CEcentral_europecentral_europeFound in the map.
WestWestwestNot in the map, so kept. The case then fixes it.
nnnThe map is exact. A small n is not the key N.
missingmissingmissingA missing value stays missing.

The fourth row is the one to remember. The map is applied before the case and compares exactly, so n passes through unmapped. The quality stage then rejects it, because the dealer contract's rule on region lists the valid values. That is the reason standardise comes before quality in the fixed order.

The settings that drive it

One honest remark about the tyre example. None of the nine tyre dataset manifests has a columns list today, so every tyre column runs with the defaults. The settings below are read by the code and covered by the tests, and the example manifest lines on this page are typed for this page, not copied from the tyre manifests.

SettingWhat it meansAllowed valuesDefault
columns[].valuesA map from a value as sent to the value wanted.Any pairs of textEmpty. No value is replaced.
columns[].caseThe letter case of the whole value.upper, lower. Anything else is refused by the plan.Empty. The case is left alone.
processor_groups.standardizationA dataset setting in the metadata store. The value disabled makes the stage runner skip this stage for the dataset.The settings registry allows enabled, disabled, opt_in and dataset_decides. The plan acts only on disabled.Not set, so the stage runs

The code

def standardise(df: DataFrame, ctx: StageCtx) -> Out:
    """Declared value maps (a value not in the map is kept as it is) and letter case."""
    out = df
    for c in ctx.plan.kept:
        if c.logical_type != "string":
            continue
        col = F.col(c.name)
        if c.values:
            mapping = F.create_map(*[F.lit(x) for kv in c.values for x in kv])
            col = F.coalesce(mapping[col], col)
        if c.case == "upper":
            col = F.upper(col)
        elif c.case == "lower":
            col = F.lower(col)
        if c.values or c.case:
            out = out.withColumn(c.name, col)
    return Out(out)
  1. Text columns only. A column whose contract type is not string is skipped.
  2. Build the map. The plan holds the map as a sorted tuple of pairs. create_map wants a flat list: key, value, key, value. The nested loop in the comprehension flattens the pairs into that list.
  3. Look up, and fall back. mapping[col] gives the mapped value, or null when the value is not a key. coalesce takes the first of its arguments that is not null, so a value that is not in the map falls back to itself.
  4. Change the case. upper or lower is applied to the result of the map, not to the raw value.
  5. Write only when asked. The column is rewritten only if it has a map or a case. Other columns are not touched at all.

How it is tested

tests/dataplatform/test_silver_stages.py has six cases. Each gives the settings, one raw value and the expected value.

tests/dataplatform/test_silver_stages.py:182The six cases. The last proves the map runs before the case.
        ("nothing declared", {}, "Ab", "Ab"),
        ("upper", {"case": "upper"}, "Ab", "AB"),
        ("lower", {"case": "lower"}, "Ab", "ab"),
        ("a value map", {"values": {"N": "north"}}, "N", "north"),
        ("a value not in the map is kept", {"values": {"N": "north"}}, "S", "S"),
        ("map then case", {"values": {"n": "North"}, "case": "upper"}, "n", "NORTH"),
Not built yet, or known gaps
  • A value that is not in the map is kept without a count or a flag. Whether it is acceptable is left to a rule in the quality stage.
  • The map compares exactly, including letter case. A map that should ignore case needs every spelling listed.
  • The settings registry has a separate key processor_groups.case_normalize. No code reads it. Letter case is switched together with the value maps by processor_groups.standardization.
  • Only text columns of the contract are handled.