Thomas Eapen · Selected work

Normalise keys & join sources

Bring observations and reference data together while keeping unmatched records visible.

Synthetic example · independently invented dataOne-page print edition · A3 landscape
01 Inspect the inputs02 Apply the method03 Check the result

INVENTED INPUTS

Two small input tables

Four synthetic observations; quotes show surrounding spaces
Raw keyValue
“ UNIT-A ”2
“unit-b”5
“UNIT-D”7
“unit-a”3
Three synthetic reference entries
KeyGroup
unit-aGroup 1
unit-bGroup 2
unit-cGroup 3

Case and whitespace vary in the observations. Unit D has no entry in the reference table.

SELECTED PYTHON

The working calculation

Pandas is pd. rows and reference hold the two input tables at left.

observations = pd.DataFrame(rows)
lookup = pd.DataFrame(reference)
for frame in [observations, lookup]:
    frame['key'] = frame['key'].str.strip().str.lower()
joined = observations.merge(
    lookup, on='key', how='left',
    validate='many_to_one', indicator=True
)
unmatched = joined.loc[joined['_merge'] == 'left_only']

The code shown here is executed to calculate this example’s results.

COMPUTED RESULT

Keep the exception visible

Left-join result after key normalisation
KeyValueGroup
unit-a2Group 1
unit-b5Group 2
unit-d7Unmatched
unit-a3Group 1
3 + 1matched + unmatched rowsAll four observations are retained

The unmatched row remains in the result for investigation. Normalisation allows both Unit A records to match the same reference entry.

What this demonstratesKey preparation, join direction and cardinality checks are separate design choices. Together they make the merge easier to verify.

Check the boundaryA duplicate reference key raises an error through many_to_one validation. An inner join would silently omit the unmatched observation from this output.