Guide · updated 2026-11-04
Null, blank and zero: handling missing values on purpose
How to tell unknown, empty and zero apart, and how each tool treats them.
Three different things
Missing means nothing was recorded. An empty string is a recorded, empty value. Zero is a real measurement. Treating them as the same changes averages, joins and counts.
Tool behaviour
SQL comparisons with NULL are unknown and aggregates skip NULL. pandas aggregates skip NaN, but merge matches missing keys to each other. Spreadsheets often show blanks and zeros the same way.
Rules that keep meaning
Convert blanks to missing only where a rule says so, label unknown categories explicitly, never fill numbers with 0 to make a chart look complete, and report coverage next to every average.
- Required field missing → exception
- Optional category missing → unknown label
- Numeric value missing → stays missing