Before this: Missing is not empty (and neither is zero)
The idea
Some systems can't store a real missing value, so they write a code instead: -999 for an unknown reading, 9999 for a broken sensor, "N/A" in a text field. To a tool these are ordinary values, so averages, minimums and counts silently include them.
Find sentinels while profiling: look for suspiciously round extremes and repeated placeholder text, and check the data dictionary. Convert each documented code to a real missing value with a named rule, and keep a count of how many values the rule changed.
Never treat a sentinel as zero, and never guess that an unusual value is a sentinel without evidence. If the dictionary is silent, ask.
Worked example
Input: reading: 12, -999, 15
- Visual
Add step → Map values → column reading → -999 → (missing)
- SQL (DuckDB)
CASE WHEN reading = '-999' THEN NULL ELSE reading END AS reading
- pandas
df["reading"] = df["reading"].replace("-999", pd.NA)
Result: reading: 12, (missing), 15 — the mean becomes 13.5 instead of -324
Common mistakes
- Averaging before converting sentinels.
- Replacing sentinels with 0.
- Treating any extreme as a sentinel without documentation.
Check yourself
A temperature column's minimum is -999. What do you check first?
The data dictionary or owner: it is probably a missing-value code, not a temperature.
Practice it
Keep unknown durations missing while labelling unknown regions.
Next: Label unknown categories, keep missing numbers missing