Disguised missing values
A cell is not empty, but what it holds means there is no value: N/A, a dash, 999, or the word none.
What it looks like
Before
| age | status |
|---|---|
| 34 | active |
| 999 | N/A |
| - | active |
| 0 | unknown |
| 41 | · |
After
| age | status |
|---|---|
| 34 | active |
| · | · |
| · | active |
| 0 | · |
| 41 | · |
How it happens
- A form required an answer, so people typed something to get past it.
- An older system had no way to store an empty number and used a code such as 999 or -1 instead.
- Different teams adopted different words for not known and not applicable.
What it breaks
- Averages and totals include the placeholder. A few 999s move the mean of a column of ages a long way.
- Counts of missing data come out too low, so the column looks more complete than it is.
- A number column that contains a dash or the letters N/A is read as text, and every calculation on it stops working.
How to spot it
- List the most frequent values. Placeholders are often near the top.
- Look at the extremes of numeric columns. Repeated values at the edge of the range are suspicious.
- Search for the usual words: n/a, na, none, null, unknown, tbd, and single punctuation marks.
How to fix it
- Agree on the list of placeholders for each column. The same token can be a placeholder in one column and a real value in another.
- Replace placeholders with a true empty cell so software treats them as missing.
- If the difference between not known and not applicable matters, keep it in a separate column instead of mixing it into the values.
- Numbers stored as textA column of quantities contains symbols, separators, or words that stop software reading it as numbers.
- Inconsistent category labelsA column meant to hold a few fixed values holds many spellings of them.
- Case example: Non-profitsA donor list that counted the same people twice
- Case example: Academic researchA survey whose result depended on how blanks were coded
TermsMissing value, Null, Sentinel value, Imputation