Missing Is Not Zero: Five Kinds of Empty and What to Do with Each

Open a spreadsheet of prices and look at the blank cells. What do they mean? The honest answer is: it depends, and the sheet does not say. A blank can mean the price was never looked up, the item has no price (it is a bundle), the price will be available next week, the page was fetched but the parser failed, or even that the price is zero. These are five different facts, and almost every pipeline stores them all as the same empty cell. The result is quiet damage: wrong averages, wrong totals, and joins that drop rows without a word. This article names the five cases and shows how to keep them apart (Missing Data).

The five kinds of empty

Kind	Meaning	Example
Not observed	A value exists but we did not obtain it	The page was never fetched
Not applicable	There is no such value for this item	Shipping weight of a downloadable book
Not yet available	It will exist later	A review score before the first review
Parse or read failure	We fetched it but could not read it	The price element moved and the parser found nothing
Zero	A real measured value	A free item costs 0.00

The first four are absences of a value; only the last is a value. Mixing zero with any of the others is the most damaging mistake, because zero takes part in arithmetic and absence should not.

What goes wrong when they are mixed

Averages. Five items; three prices are known (20, 30 and 10), two are empty.

Treatment	Average price
Ignore the empty cells	(20 + 30 + 10) / 3 = 20
Treat empty as zero	(20 + 30 + 10 + 0 + 0) / 5 = 12

Treating “unknown” as zero lowered the average by 40% without any price changing.

Totals and counts. A total of sales over items with some values missing is a lower bound, not the total; reporting it as the total understates reality. A count of “items with a rating” shifts when the parser fails on a quarter of the pages.

Comparisons. “Show items under 20” includes the empty prices if blanks are read as zero: every item with an unknown price turns into a bargain.

Joins and filters. In many systems a comparison with an unknown value is itself unknown, so a filter such as “price below 20” silently drops the rows with unknown price. They disappear from the results with no trace (Three-valued Logic).

Trends. If a parser starts failing on Thursday, the series seems to fall to zero or to jump around; nothing happened to the world.

Why is it missing? Three patterns

Statisticians distinguish how absence relates to the value, because it decides what is safe to do (Missing Data):

Missing completely at random: a gap unrelated to anything (a sensor dropped a reading). Ignoring these is mostly safe.
Missing at random: the gap depends on something you did observe (a field is skipped more often for one category). Analysis can allow for it using that other information.
Missing not at random: the gap depends on the missing value itself (the most expensive items hide their price). Ignoring them biases the result, and no technique fixes it without outside knowledge.

You usually cannot prove which applies, but you can ask the question and record what you assume.

How to represent them

The remedy is cheap: keep the value and its status in separate columns, with a short, fixed list of statuses.

item_id,price,price_status
a1,19.99,observed
a2,,not_observed
a3,,not_applicable
a4,,parse_failed
a5,0.00,observed
a6,,not_yet_available

Now zero is a recorded value (a5 is genuinely free), and each blank says why it is blank. Document the statuses in the dataset’s contract, with the rule for each (Data Contract).

What to do with each

Status	In averages and totals	In filters	What else
observed	Included	Compared normally
not_observed	Excluded; report how many	Excluded, or shown as “unknown”	Can the value be collected?
not_applicable	Excluded, as it is not part of the population for this measure	Excluded	Consider whether the measure fits this item at all
not_yet_available	Excluded for now; revisit	Excluded	Re-check later; keep the date it was missing
parse_failed	Excluded; alarm	Excluded	A bug, not a fact: fix, and re-run from the stored raw data

Always report the count of each status next to any statistic: “average price 20 (3 of 5 items; 2 not observed)”.

Make failures loud

Parse failures should never look like ordinary absence. Count them per day and alarm when the share jumps, which is how a silent redesign of a source page is caught on day one instead of day thirty (Collecting a Time Series You Can Compare: Fixed Conditions, Snapshots and Honest Gaps, Data Quality Dimensions). Test that no pipeline step converts blanks to zero, and no step converts zero to blank.

Keep the difference all the way to the report

The distinction is only useful if it survives to the end. Charts should show gaps as gaps; tables should show “n/a” or “not observed”, not 0; headline figures should say how many items they rest on. A reader who sees “average price 12” will believe it; a reader who sees “average price 20 (3 of 5 items)” can decide how far to trust it.

A short checklist

Is every blank in your data one of the five kinds, and can you tell which?
Are value and status stored separately, with a documented list of statuses?
Does zero mean only “measured, and it was zero”?
Do statistics report their denominators and the count of missing values?
Are parse failures counted, alarmed and traceable?
Do filters and joins say what they do with unknowns?
Have you written down, even roughly, why you think values are missing?