Guide

Clean Your Survey Data, Step by Step

A raw questionnaire export is rarely ready for analysis. It may contain duplicate submissions, impossible answers, blank cells and items scored in the opposite direction, and any one of these can distort your results. Cleaning is part of the analysis, and doing it in a fixed order means nothing gets missed. This guide follows that order using wellbeing-survey.csv from the practice datasets, a file with missing values and two reverse-worded items, so you can follow every step and check every number.

Rule zero · the raw file is sacred

First, never edit the raw export, not even 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 must be reproducible: someone with your raw file and your list of steps should get exactly your clean file. The Cleaning Workflow and Reproducible Workflows explain this habit in full. That record is the difference between cleaning and tampering.

Deleting cells by hand in Excel fails this test, because it leaves no record of what you did. In SPSS or JASP, use computed variables and save the syntax. In R or Python, the script is the record.

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 dangerous: one forgotten -99 in a 1–5 item can lower the mean by most of a scale point. Convert them to true blanks or NA immediately. Record every column's meaning, units, allowed values, and missing marker in a codebook, because you will need it yourself later.

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 data in this shape, so do not rearrange it by hand afterwards.
  • Check the ID column is unique. Duplicate IDs mean double submissions (usually keep the first, and write the rule down) or an export glitch. Count rows before and after any de-duplication. If you later merge in demographics, the same count shows whether the merge duplicated rows.
  • 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 an allowed range, and any value outside it is an error. For a 1–5 Likert item, a 7 is impossible. For age, 511 is probably a typo for 51, but you cannot know that. Run a minimum and maximum on every column (the descriptives calculator does this for a pasted column and also flags outliers) and define validation rules: allowed range per item, allowed categories, cross-field logic. The validation lesson shows how each type of error gets into a file and which rule catches it.

If the correct value can be recovered from context, fix it and log the fix. If it cannot, set the cell to missing. Never leave an impossible value in place, and never guess without recording it. (The example file has no impossible values. To practice on harder cases, use messy-clinic.csv and the spot-the-error game.)

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

Next, count what is missing. In wellbeing-survey.csv, 51 of the 960 item cells (120 respondents × 8 items) are blank, about 5%, spread across many people. Because they are spread out, only 75 of the 120 respondents have all eight answers. Listwise deletion (analyzing complete cases only) would drop 45 people, over a third of your sample, from any analysis that uses all items.

The options, in rising order of sophistication: listwise deletion (simple, but it loses cases and is unbiased only 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 one is defensible depends on why the data are missing. Missing Data explains the MCAR/MAR/MNAR distinction that decides this. For a thesis, at a minimum, report how much was missing, what you did about it, and how many cases each analysis used.

Step 5 · Reverse-code the negatively worded items

Many questionnaires include reverse-worded items ("I rarely feel at ease") to catch straight-lining, and 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 the example file, q3 and q6 are reverse-worded. Recode them into new columns, keep the originals, and check your work: after recoding, every item should correlate positively with the others. If you skip this step, the two reversed items run against the other six and α falls sharply, as Step 6 shows. Transformations & Recoding shows how to recode in every package.

Step 6 · Build the composite, then check its reliability

Multi-item scales are scored as a composite, usually the mean of the items. The mean copes better than the sum with an occasional missing answer, 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 the example file has M = 2.88, SD = 0.93.

Before you use the composite, check the items' internal consistency with Cronbach's α. Here α = .90, well above the conventional threshold of .70 (Reliability & Validity covers that threshold's history and its critics). An α computed before reverse-coding is meaningless and looks very low (on this file it is .30). And a very high α (> .95) usually means some items are near-duplicates of each other.

Together with the SD, α also tells you how precise one respondent's score is. The standard error of measurement is SD × √(1 − α), here 0.93 × √.10 = 0.29 on the 1–5 composite, so a single score has a 95% band of roughly ±0.57. Two respondents at 3.2 and 3.6 cannot be reliably told apart, even though a table prints them as different.

Also read the two per-item columns beside α. The corrected item-total correlation is the correlation between each item and the sum of the other items. Here the eight run from .63 (q4) to .72 (q6), and values below about .30 would be a concern. Alpha if item deleted is the α the scale would have without that item. Every one of these is below the full scale's .90, so no item needs to be dropped. If an item has a low item-total correlation, or α rises when it is deleted, reread that item's wording.

Step 7 · Screen the composite like any variable

The composite is a new variable, so screen it like any other: histogram, boxplot, mean and SD. The descriptives calculator gives all of these for a pasted column, plus an APA sentence you can copy. Check the shape, because floor and ceiling effects are common in wellbeing scales. Here the composite runs from 1.00 to 4.88, with two respondents at the floor and none at the ceiling. No single item has more than 19% of people in its lowest or highest category, so the scale measures across its whole range. A top or bottom category holding a quarter of the sample would be a warning sign, fixed by revising the response options. Handle any extreme scores with the outlier triage rules: decide whether each is an error, a member of a different population, or a real extreme value, and take the action that goes with it. None of these actions is deletion without a record. A respondent who answered 1 to everything, including the reverse-worded items, is a straight-liner. You may flag that respondent, and possibly exclude them, if you wrote the rule down before looking at the results.

Step 8 · The cleaning log is a deliverable

Every step above involved a decision: rows dropped and why, values fixed, codes converted, items reversed, how missing data were handled, exclusion rules. Record them in a log, summarize it in your methods section, and include it in full in an appendix. The log also protects you, because it shows that every change was planned and documented. Exclusion rules invented after seeing the results are a common way for students with no intent to cheat to get into trouble. Decide each rule, write it down, and apply it to every case.

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 wrong numbers, and nothing in the output shows that they are wrong.

Keep going