Cleaning Survey Data Before Analysis: Codebooks, Outliers and a Change Log

A research team found that the Cronbach’s alpha for their motivation scale was only 0.41, while other studies reported above 0.80. They considered dropping the scale. It turned out that three negatively worded items (“I find studying really boring”) had never been reverse-scored. After reversing them, alpha rose to 0.84. They caught it in time; plenty of manuscripts reach reviewers with the same error intact.

Data cleaning is unglamorous, rarely taught and almost never described properly in papers. Yet it decides whether the numbers you report can be trusted.

Rule one: never edit the raw file

Keep the raw data file, exported directly from your survey tool or data entry, as read-only. Make every change through syntax or code (SPSS syntax, R, Python, Stata do-files) that produces a new clean file. When you find a mistake at any step, you fix the code and rerun it, rather than trying to remember what you clicked in a spreadsheet.

If manual edits are unavoidable, for example when checking against paper forms, log each one: record ID, variable, old value, new value, reason, date and who made it.

Write the codebook before cleaning

A codebook describes each variable in one row. Minimum columns:

The codebook is both your cleaning checklist and a required document if you share the data. Write it from the questionnaire before opening the data, so you do not unconsciously adjust it to what the data show.

Eight checks, in order

1. Row count and IDs: does the number of responses match what you think you collected; are any IDs duplicated?
2. Duplicate submissions: the same person submitting twice, often visible as near-identical timestamps and answers. Keep the more complete or the first submission, following a rule set in advance.
3. Consent and eligibility: remove those who did not consent or fall outside the target population.
4. Out-of-range values: a 6 on a 1–5 scale, an age of 250, 300 study hours a week. Check frequency tables and the minimum and maximum of every variable.
5. Missing value codes: convert 99 and 98 to true missing values before calculating anything. Letting 99 slip into a mean is a classic error.
6. Reverse-scored items: on a 1–5 scale, new score = 6 − old score. Verify with correlations: after reversing, negative items should correlate positively with the rest of the scale.
7. Derived variables: scale scores, age groups. Decide in advance how to handle a few missing items.
8. Cross-variable logic: someone who has “never worked” but reports earnings; a first-year student who has studied for five years.

Careless respondents: rule-based detection

Online surveys, especially with incentives, always include some people clicking through. Common indicators:

• Completion time far below plausible reading time. Set a threshold in advance, for example under a third of the median time.
• Straightlining: the same response for every item in a block, including reversed items.
• Attention checks: “For this item, please select Agree”. These need to be built in at the design stage.
• Nonsense open answers: random characters, the question pasted back.

Exclusion rules must be written down before you see the analysis results, and the number excluded for each reason reported. Removing respondents after seeing that they “spoil” the result is data manipulation.

Outliers: tell errors from real data

Not every extreme value is an error. Sort them into three groups:

• Impossible values (age 250): correct them if you can check a source; otherwise set them to missing.
• Possible but suspicious (90 study hours a week): look at related answers and decide using a pre-set rule.
• Real but extreme (a very high income): keep them; handle them with suitable analysis such as a log transformation, robust methods, or a sensitivity analysis with and without them.

Avoid mechanical rules such as “remove anything beyond three standard deviations” without considering the variable; with skewed distributions, that rule throws away genuine data.

Reporting cleaning in the paper

A short paragraph in the methods is enough: total responses received, the number excluded for each reason (no consent, ineligible, duplicate, careless), the number analysed, and how reversed items and missing values were handled. A small flow diagram, like a PRISMA diagram, lets readers see it at a glance.

In practice: the minimum file set for every project

1. A raw folder: the directly exported data, set to read-only.
2. A codebook written from the questionnaire.
3. A cleaning script divided into blocks following the eight checks, each commented.
4. A decision log: exclusion rules, time thresholds, outlier handling, dates decided.
5. A clean data file generated by the script, with a date in its name.
6. A summary table of responses remaining after each step, ready for the methods section.

Next step: open the data file you are analysing, run frequency tables for every scale item and look for 0, 98, 99 or anything outside the valid range. If you find even one, pause the analysis and write the cleaning script first.

Câu hỏi thường gặp