A survey whose result depended on how blanks were coded
A research group combining three waves of a student wellbeing survey discovers that each wave recorded the same answers differently.
Illustrative example. The organization and its data are invented to show a typical pattern. This is not a customer story.
What they hold
- Three waves of responses, collected in different years with different survey tools.
- A codebook for the first wave, partly updated for the second, and not updated for the third.
- Free-text fields for department, year of study, and hours worked per week.
The mess
Missing answers coded as numbers
The first wave stored a skipped question as 99 and a refused one as 98. The second wave left them blank. The third exported the words Prefer not to say. On a scale that runs from one to five, a 99 is not an answer, but it is a number, and it is averaged like one.
The same department under many names
Department was typed freely: Psychology, Psych, Dept. of Psychology, psychology and neuroscience. A comparison across departments has one group for each spelling.
Hours as text
Hours worked per week was answered as 20, as 20 hrs, as 15-20, and as part time. Only the first of those is a number.
Participants who cannot be linked
Student identifiers lost their leading zeros in one wave when the file passed through a spreadsheet, so the same student does not match between waves.
What it costs
- The headline average for the first wave is pulled upward by the 99s, and the apparent change between waves is partly an artifact of coding.
- Students who cannot be linked across waves drop out of the longitudinal analysis, shrinking the sample and possibly biasing it.
- A reviewer who asks how missing data was handled cannot be answered precisely, because it was handled three different ways.
- A graduate student spends weeks rebuilding the dataset by hand, and the next student to use it does the same work again.
What clean looks like
- Skipped and refused answers are represented the same way in every wave, as missing, with the reason kept in a separate column.
- Department maps to one agreed list, and the mapping is saved alongside the data.
- Hours is a number where a number was given, and is flagged where the answer was a range or a description.
- Identifiers are stored as text with their zeros restored, and the same student links across all three waves.
- Every one of those decisions is written down, so the methods section can state exactly what was done.
Problems in this example
- Disguised missing valuesA cell is not empty, but what it holds means there is no value: N/A, a dash, 999, or the word none.
- Inconsistent category labelsA column meant to hold a few fixed values holds many spellings of them.
- Numbers stored as textA column of quantities contains symbols, separators, or words that stop software reading it as numbers.
- Identifiers that lost their leading zerosA code that starts with zero, such as a postal code or account number, was read as a number and the zeros were dropped.