Section 2.4

The Cleaning Workflow

Real data arrives dirty: duplicated submissions, a group column spelled six different ways, invisible trailing spaces, impossible values hiding in plain sight. Cleaning is not a frantic scramble the night before analysis; it's a principled pipeline you run the same way every time, where every fix is written as a step you could undo, explain, and rerun.

The five-move loop

Good cleaning follows the same cycle regardless of the dataset:

  1. Inspect. Look before you touch. Counts, ranges, distinct categories, a scan for duplicates. You can't fix what you haven't seen.
  2. Document the issues. Write down each problem you find (and its suspected cause) before fixing anything.
  3. Fix via script. Never hand-edit cells. Every repair is a line of code that reads the raw file and produces a clean one.
  4. Re-inspect. Rerun the checks. Did the fix do what you expected, and only that? Did it break something else?
  5. Log the decision. Record what you changed and why, so a stranger (or future-you) can audit every step.

The usual suspects

  • Duplicates: the same row entered twice (a double-clicked submit), or fuzzy duplicates (the same person under "Ann Lee" and "Ann Lee "). Exact duplicates are easy to drop; fuzzy ones need judgment.
  • Inconsistent categories: Control, control, CONTROL, ctrl are one group masquerading as four. Standardize them to a canonical set.
  • Whitespace ghosts: a trailing space makes "Control " a different string from "Control". Trim first, or every later match silently fails.
  • Impossible values: a satisfaction score of 999 in a 1–100 field. A range check catches these; investigate, then correct or mark missing. A 999 that your codebook declares as "declined to answer" is a different animal, and the range check will not save you from it.
  • Date-format drift: 03/04/24 is March 4th or April 3rd depending on who typed it. Parse every date to ISO 8601 (2024-04-03, year first) early, and the ambiguity cannot come back.

Below is a messy 100-person survey — actually more than 100 rows, because some were submitted twice. Turn the cleaning steps on in order and watch the row count, the number of distinct group labels, and the mean satisfaction settle down. A cleaning log writes itself as you go. That log is your reproducible record.

🧼 Cleaning Pipeline Simulator

Toggle each step to apply it to the raw data. Every step is a pure transformation of the same untouched raw file, applied in pipeline order, so you can switch any step off and the data returns exactly to where it was. The log at the bottom is what you'd hand to a supervisor.

Rows—
Distinct group labels—
Mean satisfaction—
Missing scores—

Group labels (count)

Cleaning log

Two things to notice. First, the mean satisfaction barely moves until you range-check, then it drops sharply, because a handful of 999s were inflating it. Second, the order matters: standardizing can't merge "Control " with "Control" until you've trimmed the space. A pipeline makes that order explicit and repeatable.

Exact duplicates are the easy half

Step 3 above drops rows that are identical in every column, which is what distinct() in R and drop_duplicates() in pandas do when you call them with no arguments. That handles a double-clicked submit on a form that records nothing else. It does not handle the version you will actually meet, where the platform stamped each submission with the time it arrived:

P01 09:14 · P02 09:20 · P03 09:31 · P03 09:33 · P04 09:40 · P05 09:52 · P05 09:52

Seven rows, five participants. P05 double-clicked inside the same minute, so those two rows match exactly and a plain de-duplicate removes one. P03's two rows differ by two minutes, so they both survive — and P03 now counts twice in every mean you compute. Six rows out, mean 65.8; the correct answer is five rows and 67.4.

The fix is the same one the FAQ below insists on: say what makes a row unique, then de-duplicate on that. In R, distinct(id, .keep_all = TRUE); in pandas, drop_duplicates(subset="id"). If your design has one row per person per session, the key is the pair, not the ID alone. And check the count both ways — a de-duplicate that removes nothing is as suspicious as one that removes half your sample.

The cardinal rule: raw data is read-only. Never overwrite the file you collected. Every fix lives in a script that reads the raw file and writes a separate clean one. That single habit makes cleaning reproducible (rerun the script and get the same result), reversible (delete the clean file, keep the raw), and auditable (the script is the record of every decision).

Why it matters: the difference between a script and a spreadsheet edited by hand is the difference between an analysis you can defend and one you can only hope was right. A cleaning script is inspectable, so a co-author can check your logic; it's re-runnable, so a corrected raw file flows through in seconds; and it's a log, so "why is n = 100 and not 106?" has a one-line answer. Clean into tidy shape, catch impossible values with validation rules, and never delete an outlier without recording it. To see the whole pipeline worked on a real messy export, follow the guide Clean Your Survey Data, Step by Step.

Problem 41 of the practice problems hands you an assistant’s verbatim cleaning log and asks which of its entries destroyed the dataset without anyone noticing.

Common questions

Two rows share an ID but give different answers. Which one do I keep?

Decide the rule before you look at the answers, and write it in the log. Three choices are defensible: the first submission, the last one, or whichever row is most complete. Any of them is fine as long as you can say why you chose it and you apply it to every clashing ID in the file. What ruins the analysis is choosing per row, keeping whichever value happens to suit the result, because that is p-hacking with extra steps. Survey exports almost always carry a submission timestamp, which makes "first" or "last" a single line of code. If the two rows disagree about something central and no rule settles it, flag the participant, run the analysis both ways, and say in the write-up whether the conclusion moved.

How do I decide whether two rows are really duplicates?

First define what makes a row unique — usually a participant ID plus, for repeated measures, a timepoint. Exact duplicates (every field identical, often a double-clicked submit) are safe to drop. Fuzzy duplicates (the same person as "Ann Lee" and "Ann Lee ", or two near-identical rows differing by a whitespace ghost) need investigation, not automatic deletion. And beware the false alarm: in long-format data one person legitimately owns several rows, so identical values in a few columns don't make them duplicates. Check your row count before and after, and log how many you removed.

In what order should cleaning steps run?

Inspect before you touch anything, then apply fixes in an order where each step sets up the next. A reliable default is: trim whitespace, then standardize categories, then remove duplicates, then range-check numeric fields — re-inspecting after each. Order genuinely matters: you can't merge "Control " with "Control" until the trailing space is trimmed, and de-duplicating before you've standardized labels can miss copies that only look different because of formatting. Writing the steps as an explicit pipeline makes that order visible and repeatable.