How to clean a survey dataset before analyzing it, in 5 steps

Before going into the data, a quick check of its structure: how many rows and columns it has, whether every row has the same columns, and whether the first row holds the variable names. A separator or a quote mark typed inside an answer, or the single quotes of an SPSS export, split a row and shift the rest of the values without anyone noticing.

A case loaded twice counts double in every percentage. Look for identical rows and, separately, for identifiers that repeat: if two different rows have the same ID, one was loaded wrongly or two people share a number. Delete the copies, but don’t pick blindly which of two rows with the same ID is the right one: check the source.

A column without a single answer is usually a question nobody saw because of a form filter. In one that more than half left blank, any average is calculated over very few cases. And one with the same value in every case, like the survey year, adds nothing. Decide what to do with each one.

A 99 or a −1 that means “no answer” and gets into the average as if it were a response ruins it without warning. The same happens with a numeric column where someone typed “don’t know” (one is enough for the program to read the column as text) and with decimals written with a comma and with a period, 3,5 and 3.5, which depending on the program’s language are read some as numbers and some as text. Leave blank what wasn’t answered, move the answers written in words to another column and use a single decimal separator.

Fix a copy of the source file, never the original: delete the copies, resolve the IDs, leave blank what wasn’t answered, unify the decimal and remove the columns you don’t need. Keep one row per case and one column per variable. Save the corrected version under another name and note what you changed (which rows you deleted and why, which codes you turned into blanks), so you can explain and repeat it.