Data Cleaning Methodology for Journalism
Three documents and four sheets that log every cleaning step so it can be replayed against the untouched original and diffed cell by cell.
Free download · No account needed
Every published guide to cleaning data for journalism ends in the same place: keep the original untouched, document your methodology in a README. Both are right and neither is checkable. A README is a claim about a methodology, and nobody can tell whether the claim is true, including the person who wrote it six months later. So this pack makes the record executable. The change log is a program: replay it against the original and diff the result cell by cell.
It ships filled in for one records request to the Marleigh State Department of Health, an invented agency: seven years of food service inspections, delivered as a 63,419-line spreadsheet. The first replay came back with 1,208 mismatched cells out of 1,705,185, all in one column. The log had recorded the whitespace trim before the header unmerge, and the work had been done the other way round. Every count in it was accurate. It described a file that did not exist.
The second thing it forces is a check no cleaning checklist asks for. After any step that removes rows, compare what it took against the whole file. Deduplicating this dataset on facility name and inspection date removes 1,872 rows, of which 219 are genuine duplicates. Six counties run a two-visit protocol and hold 14 percent of the records, and they supply 1,025 of the 1,653 casualties. They would then appear never to re-inspect anybody.
What's in the pack
Change Log, one row per transformation
The operation specified precisely enough for somebody else to carry out, rows in and out, cells changed, whether it is reversible, why, what the codebook said, what the removal check found, and the date it last replayed clean.
The replay, run every time the log grows
Rebuild from the original, diff cell by cell, record the number. A log with three rows and a non-zero diff takes a minute to debug. A log with fifteen takes an afternoon, which is why this runs continuously rather than at the end.
The removal check nobody runs
After any step that removes or nulls rows, the removed set compared against the whole file on place, time and the field the story turns on. This is where a cleaning decision stops being a cleaning decision and becomes a fabricated finding.
Field Profile, before anything is touched
Every column: what it is stored as against what it means, blanks separated from the strings that mean blank, every format present with a count. Then the values outside the range, and the question for the agency where the codebook is silent.
Original Data, with its hash
The file as it arrived, its byte size, its line count and a hash, so somebody else can confirm they are holding the same file. Never opened in a spreadsheet, because opening and saving an Excel file rewrites dates.
Methodology README
Written for a hostile reader: the reconciliation from delivered to analysed, the replay result with its date, every judgement call with the alternative it rejected and what that alternative would have produced, and what the file cannot answer.
Findings Note, for what the cleaning found
The cleaning turns up things about the source rather than about the world. Four hidden columns, subtotals that do not cross-foot, one county recording date ranges, 402 aborted inspections stored as text. Written the same week, because they decay. Anything that turns out to be about a person belongs in the investigation evidence file.
Correction Procedure
The four kinds of complaint, separated before they arrive: the source's data is wrong, the cleaning is wrong, the analysis is disputed, the wording is disputed. Plus the cause analysis that makes a year of corrections mean something. The correction and accountability pack runs the same categorization across every kind of story, not just this one.
A space rule every prompt reads first
The original never changes. A manual edit is a transformation with a population of one. Order is part of the specification. The log has to replay. Removing rows requires a reason and a check. And nothing here analyses.
How to use it
- 1
Open in River, or download it
Open the pack and send the file the agency actually gave you, or download the blank sheets and README and work wherever you already do. The template library holds the rest.
- 2
Profile before you touch anything
Lines against records, columns as imported against columns present, every total the source printed cross-footed against the rows it printed it over. The dirt decides the cleaning, and half of it does not show in a preview.
- 3
Log each step, then apply it
One row per transformation, written before the step runs rather than afterwards from memory. Name the columns and the exact match. Record rows in, rows out, cells changed, and whether you can get back.
- 4
Replay, and check what came out
Rebuild from the original and diff. Until that is zero, no number is defensible. Then, for every step that removed rows, ask whether the rows it took look like the file.
Frequently asked questions
Is this template free?
Yes. Download the sheets and docs as Word and CSV files, no signup and no credit card. "Edit with AI" is the optional path for anyone who wants the agent to profile the file, run the cleaning, and keep the log replaying as it goes.
Why does a change log need to replay? Is writing it down not enough?
Because a written log cannot be checked. On the worked file, the log read perfectly and was wrong on 1,208 cells, because two steps were recorded in the wrong order. Transformations do not commute, so the order is part of the specification, and running it is the only way to know.
Can I not just use OpenRefine's operation history?
Use it, and it gets you most of the way: OpenRefine will encode your operations as JSON and re-apply them to another file. Its own documentation is clear that single-cell edits are among the operations it cannot replicate, and those are exactly the ones a log has to specify by hand.
The agency's totals do not match the rows it sent. Is that my problem?
It is often the better story. Cross-footing published totals against the rows behind them is standard practice for a reason, and the Bad Data Guide treats the gap as a lead rather than a defect. On the worked file the 27-record gap was 27 inspections filed under a county code the state does not use.
Why not just coerce the messy column to numbers and move on?
Because blank, zero and the string that means missing are three different values. This file's score column holds 1,286 blanks, 402 aborted inspections stored as the text "N/A", and 121 awaiting a lab result. Establish what an absence means before collapsing it, or the mean stays identical while the story disappears.
Does this pack analyse the data or find the story?
No, deliberately. A cleaning decision made while looking at a result is a cleaning decision made for the wrong reason, and keeping the passes apart is what lets you publish the methodology and the finding as separate documents. This one profiles, cleans, logs, replays, and hands the working copy on.
Where does this sit in the rest of the reporting?
Upstream is the records request: the FOIA request pack specifies the delivery format so you get an export rather than scanned images. Document dump triage handles the unstructured half of the same release. Downstream, the data story analysis pack runs the finding under every defensible specification, and the fact check pack checks each published number against the log it came from.
Make the record executable
Download the blank change log, field profile and methodology README as Word and CSV files, or open this exact pack in River and send it the file the agency gave you.
Edit with AI