Before this: Trim the right characters, and only those
The idea
A missing value means nothing was recorded. Empty text "" means something was recorded and it was empty. Zero is a real measurement. They answer different questions, so they must not be merged by accident.
Rules often say "an empty result is missing": after trimming " \t" you get "", and the rule turns it into missing so it can't match anything. Do this explicitly, after trimming, and only for the columns the rule names.
Never fill missing values just to make a join or a total work. If a value is unknown, the honest outputs are a missing cell or an exception row with a reason.
Worked example
Input: customer_code values: "005642", " \t", missing
- Visual
Trim customer_code → Blank to missing (customer_code)
- SQL (DuckDB)
NULLIF(trim(customer_code, ' ' || chr(9) || chr(13) || chr(10)), '')
- pandas
code = df["customer_code"].str.strip(" \t\r\n") df["customer_code"] = code.mask(code == "")
Result: "005642", missing, missing
Common mistakes
- Filling missing codes with a placeholder like 000000.
- fillna(0) on measurements.
- Treating "" and missing differently in one column without a rule.
Check yourself
Is a reading of 0 the same as a month with no reading?
No. Zero is an observation; an absent month is missing.
Should you trim before or after converting blanks?
Trim first: " " only becomes empty after trimming.
Practice it
Turn blank customer keys into missing before looking up the segment.