Track 14 · Practical Data Analysis · Lesson 3 of 11
Finding and handling missing values
Find every form of missing value in a dataset — blanks, tokens, sentinel codes and placeholder text — count them, and choose and document a defensible way to handle them.
A retailer wants to know how old its customers are. The export looks complete at first glance, but some ages are blank, three are recorded as −1 by the till software, and some customers' referral source just says “unknown”. If you average without noticing, a −1 quietly drags the mean down; if you drop rows carelessly, you may drop exactly the customers you most need to understand.
A missing value is a cell where the true value was not recorded. Software only recognises the obvious forms. An analyst has to find all four:
- True blanks and nulls — empty cells, or tokens such as NA or N/A that the import converts to missing.
- Sentinel codes — numbers that a system uses to mean “not recorded”: −1, 0, 99, 999, or a date such as 1900-01-01. They look like real values and are averaged like real values.
- Placeholder text — words such as
unknown,not givenorTBCin a text column. - Structural gaps — a value that does not apply (the spouse's age of an unmarried employee). This is not an error and should not be filled in.
Included with a Formula Quest membership
It has 8 graded exercises with feedback, hints and worked solutions. After it you will be able to:
- Find missing values in all their forms: nulls, tokens, sentinel codes and placeholder text
- Count missing values per column and complete cases across the columns an analysis uses
- Explain MCAR, MAR and MNAR and what each means for dropping incomplete rows
- Choose a handling strategy — recode, leave and report n, drop, impute — and document it