Separate the observation from the decision
Data cleaning can affect every result that follows. Corrections, exclusions, recoding, duplicate handling and missing-value choices are therefore part of the method rather than invisible housekeeping. A cleaning log shows what the validation found, which rule was applied and what changed. Another researcher can then repeat the operation or challenge its justification without reverse-engineering the final spreadsheet.
Create the record while cleaning. A summary written at the end tends to omit abandoned rules and unexpected side effects. This does not mean writing an essay about every empty line removed by a predefined import routine. Group identical, low-risk operations under one testable rule, but record individual decisions where interpretation is needed or the result could be affected. Granularity should reflect the data type, consequence and method.
Seven fields turn activity into evidence
Assign an identifier and date, name the operator and reviewer, and identify the input file by version and preferably checksum. State the affected variable, record class or file group. Describe the finding as a reproducible query: “values outside 0–100” is clearer than “bad outliers”. Then capture the applicable rule, the decision, the technical action and a measurable impact.
- Identity: log ID, date, operator and reviewer.
- Input: filename, version and optional checksum.
- Finding: exact location or repeatable validation query.
- Rule: testable condition with scientific rationale.
- Action: script, command or controlled manual correction.
- Effect: records affected, counts before and after, and output file.
- Control: automated assertion, visual review or independent sign-off.
Worked example: harmonise categories without inventing meaning
Imagine that the variable qualification contains “Masters”, “masters ” and “MA”, while the codebook defines a controlled set. A validation query finds 27 values outside that set. It would be unsafe to collapse every similar string automatically. First inspect the questionnaire, codebook and known export behaviour. Map only confirmed equivalents. Leave ambiguous entries flagged for a documented decision.
A strong log entry might read: “CL-014; input survey_interim_v03; rule: trim outer whitespace and map only aliases confirmed in codebook; rationale: formatting variants with no semantic difference. Action: script clean_categories.R using alias_map_v02. Effect: 18 strings trimmed, six confirmed aliases mapped, three uncertain values retained and flagged; row count unchanged; frequency table compared before and after.” The boundary of the change is as visible as the action itself.
Make unavoidable manual corrections replayable
Some errors cannot be resolved by a general rule, such as a transcription mistake verified against an authorised source form. Record the controlled record ID, field, old value, corrected value, evidence, decision and reviewer. Keep the evidence in restricted storage. The broadly readable log must not contain names or unnecessary confidential content.
Direct editing inside a spreadsheet is difficult to reproduce. Prefer a correction table that a script applies. It can contain record ID, field, expected old value, new value, rationale and approval. The routine should stop if the old value no longer matches; otherwise a correction designed for one version might silently alter another. This arrangement also allows the complete set of manual decisions to be reviewed separately.
Do not treat missingness, duplicates and outliers as one problem
A blank may mean non-response, instrument failure, not applicable or a true absence. Preserve distinctions supported by the collection design. Duplicate rules must define whether “same” means person, event, measurement or file and how the retained instance is chosen. Matching names alone is generally not enough, especially where people share identifiers or text has been standardised.
An outlier is a finding, not proof of error. Record the detection rule and domain review. A plausible extreme value may be the most informative observation. Where appropriate, compare analyses with and without contested records and keep the two outputs distinct. The changelog should reveal uncertainty rather than converting it into an unexplained deletion.
Promote a cleaned state only after validation
Compare row counts, variable types, ranges, missing-value codes and key distributions with the input. Build automated checks that fail on unexpected changes. Verify that every cleaning-log entry points to an existing output and that each transformation has a corresponding record. Assign a clean release number and checksum only after these controls pass, then prevent accidental overwrite.
If a later error is found, preserve the old release. Add a new entry for the correction, list affected outputs and re-run the necessary analysis. Do not silently replace tables or figures. An honest chain of revisions is the point; presenting the data as if they had always been perfect defeats the evidential purpose of the log.
Audit the changelog itself
- Could another person execute each rule as a query or action?
- Can they distinguish pre-specified decisions from those made after inspecting results?
- Are counts and effects recorded instead of vague activity words?
- Are identities and confidential values appropriately protected?
- Does the reported analysis point to one clean version, log and code state?