Guide

Clean Your Survey Data, Step by Step

A raw questionnaire export is never ready for analysis. Somewhere in it there's a duplicate submission, an impossible answer, a column of blanks, and two items scored backwards, and any one of them can wreck your results. Cleaning isn't drudgery before the "real" work; it is the work, done in a fixed order so nothing gets missed. This guide walks that order, using wellbeing-survey.csv from our practice datasets — a real file with real missing values and two reverse-worded items — so you can follow every step and check every number.

Rule zero · the raw file is sacred

Before anything else: the raw export never gets edited. Not one cell. Copy it (or better, read it into a script), clean the copy, and keep the original exactly as the survey platform produced it. Every change you make must be reproducible: a stranger holding your raw file and your steps should arrive at your clean file, decimal for decimal. That single habit, argued fully in The Cleaning Workflow and Reproducible Workflows, is what separates cleaning from tampering.

Point-and-click deleting in Excel fails this test silently: there's no record of what you did. If you work in SPSS or JASP, use computed variables and save the syntax; in R or Python, the cleaning is the script.

Step 1 · First look: what did the platform actually give you?

Open the file and just look before computing anything. How many rows? (Our example: 120 respondents, columns respondent_id, q1–q8.) Do the columns match your questionnaire? Are responses numbers or text labels ("Strongly agree")? How are missing answers marked: blanks, NA, or a numeric code like -99?

Numeric missing codes are landmines: one forgotten -99 in a 1–5 item drags a mean through the floor. Convert them to true blanks/NA immediately, and record every column's meaning, units, allowed values, and missing marker in a codebook, since future-you is the audience.

Step 2 · Structure: IDs, duplicates, tidy shape

  • One row per respondent, one column per item, one value per cell: the tidy data rule. Survey platforms usually export tidy; humans un-tidy it afterwards. Resist.
  • Check the ID column is unique. Duplicate IDs mean double submissions (keep the first, usually, and write the rule down) or an export glitch. Count rows before and after any de-duplication; if you later merge in demographics, that habit will save you from silent row explosions.
  • Watch for the platform's junk rows: test runs by you, previews, the header row repeated. Filter them by date or completeness, and log how many you removed.

Step 3 · Impossible values

Every item has a legal range; anything outside it is an error, not an opinion. For a 1–5 Likert item, a 7 is impossible; for age, 511 is a typo for 51 (probably — but you don't know, which is the point). Run a minimum/maximum on every column (the descriptives calculator does this for a pasted column, flagging outliers as a bonus) and define validation rules: allowed range per item, allowed categories, cross-field logic. The validation lesson shows how each error type creeps in and the rule that catches it.

What to do with a violation? If the truth is recoverable from context, fix it and log the fix. If it isn't, set the cell to missing — never leave an impossible value in place, and never guess silently. (Our example file is clean on this front; its siblings messy-clinic.csv and the spot-the-error game are where you practice the ugly cases.)

Step 4 · Missing data: count it before you treat it

Now quantify the holes. In wellbeing-survey.csv, 51 of the 960 item cells (120 respondents × 8 items) are blank, about 5%, scattered across people. That scattering matters: only 75 of the 120 respondents have all eight answers. Listwise deletion (analyze complete cases only) would silently discard 45 people — over a third of your sample — from any analysis that touches all items.

The options, in rising order of sophistication: listwise deletion (simple, expensive, and only unbiased under strong assumptions), pairwise deletion (each statistic uses what's available), available-item composites (average the items a person did answer, if most are present), and model-based approaches like multiple imputation. Which is defensible depends on why data are missing, and Missing Data untangles the MCAR/MAR/MNAR distinction that decides it. For a thesis, the minimum standard is: report how much was missing, what you did, and how many cases each analysis used.

Step 5 · Reverse-code the negatively worded items

Good questionnaires include reverse-worded items ("I rarely feel at ease") to catch straight-lining; Designing Surveys explains why. Before scoring, those items must be flipped so that a high number means the same thing everywhere. The formula: new = (max + min) − old. For a 1–5 scale, new = 6 − old.

In our file, q3 and q6 are reverse-worded. Recode them (into new columns, keeping the originals), and check your work: after recoding, every item should correlate positively with the others. If you skip this step, the two backwards items fight the other six and your reliability collapses; try computing α on the raw file and watch it. The mechanics of recoding, in every package, live in Transformations & Recoding.

Step 6 · Build the composite, then check its reliability

Multi-item scales are scored as a composite — usually the mean of the items (more forgiving of a stray missing answer than the sum, and it stays on the 1–5 scale where your readers can interpret it). With q3 and q6 reverse-coded, the complete-case composite in our file comes out at M = 2.88, SD = 0.93.

Before you trust the composite, ask whether the items hang together at all: Cronbach's α. Here α = .90, comfortably reliable (the folk threshold of .70, its history and its critics, are covered in Reliability & Validity). Two warnings from the trenches: an α computed before reverse-coding will look catastrophic and mean nothing; and a very high α (> .95) usually signals near-duplicate items rather than a triumph.

Step 7 · Screen the composite like any variable

The composite is a new variable, so it gets the standard screening: histogram, boxplot, mean and SD (paste it into the descriptives calculator for all of it at once, plus a copyable APA sentence). Look at the shape — floor and ceiling effects are common in wellbeing-type scales — and treat any extreme scores by the outlier triage rules: error, different population, or genuine extreme, each with its own action, and none of them is silent deletion. A respondent who answered 1 to everything including the reverse-worded items is a straight-liner; flagging (and possibly excluding) them is a documented, defensible decision, provided you wrote the rule down before looking at the results.

Step 8 · The cleaning log is a deliverable

Every step above generated a decision: rows dropped and why, values fixed, codes converted, items reversed, how missing data were handled, exclusion rules. That log belongs in your methods section (in brief) and your appendix (in full). It's also your protection: the difference between "I cleaned the data" and "I made undocumented changes" is exactly this document, and exclusion rules invented after seeing the results are where honest students drift into trouble. Decide, write it down, apply it uniformly.

The order matters: raw file preserved → look → structure & duplicates → impossible values → missing data counted → reverse-code → composite → reliability → screen → log. Reverse-coding after computing α, or building composites before handling impossible values, gives you numbers that are confidently, invisibly wrong.

Keep going