River
Y CombinatorBacked by Y Combinator
Log InStart Free
FREE TEMPLATE

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.

Edit with AIDownload (.docx + .csv)

Free download  ·  No account needed

What you get

Pack of 7 files: 3 documents, 4 spreadsheets

Data Cleaning Methodology for Journalism
  • Methodology READMEDocument
  • Findings NoteDocument
  • Correction ProcedureDocument
  • Original DataSpreadsheet
  • Cleaned Working CopySpreadsheet
  • Change LogSpreadsheet
  • Field ProfileSpreadsheet

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.

Nine logged steps, one failed replay, one rejected rule

The Change Log, the replay result recorded in the Methodology README, and the removal check behind one deduplication decision.

Data Cleaning Methodology for Journalism
RiverChange Log

Change Log

Marleigh State Department of Health, an invented agency. Food service inspections 2018 to 2024, obtained by records request, delivered as a 63,419-line spreadsheet.

StepOperationRowsColsReversible
—As delivered, as a default import reads it63,41918 
T-01Strip 3 letterhead lines, promote line 4 to header63,41518Yes
T-02Label the 4 columns hidden inside 2 merged header cells63,41522Yes
T-03Remove 41 agency-inserted county subtotal rows63,37422Yes
T-04Cross-foot the subtotals, flag 27 orphan county codes63,37423Yes
T-05Trim whitespace on 5 text columns, 18,902 cells63,37423Yes
T-06Split the date into start, end and source format63,37425Yes
T-07Coerce the score, add score status63,37426Yes
T-08Remove 219 rows identical across all 22 columns63,15526Yes
T-09Flag 311 permit dates later than their inspection63,15527Yes

Two of the nine steps removed rows. A press office that wants to argue with this file has to argue with a numbered row. The column count is the part people miss: a default import of the delivered file reports 18 columns and the file has 22, because two header cells were merged across three columns each. One of the four dropped columns records whether the violation was corrected on site.

RiverThe replay

Replay result

Rebuild the working copy from the untouched original by applying the log in order, then diff the rebuild against the working copy cell by cell.

ReplayCells comparedMismatchedWhere
2026-02-181,705,1851,208All in Facility Name
2026-02-191,705,1850 

What was wrong

The log recorded T-05 before T-02. The work had been done the other way round.

OrderFirstThenFacility Name cells
As loggedTrim whitespaceUnmerge the header1,208 differ
As doneUnmerge the headerTrim whitespaceMatches

No data was wrong and no count in the log was inaccurate. Transformations do not commute, so order is part of a step's specification, and a log that reads perfectly can still describe a file that does not exist. The fix was to swap two rows. Nothing written in prose catches this, which is the entire argument for running the log rather than reading it.

Diagnosing a non-zero diff

MismatchesUsual cause
Concentrated in one columnTwo steps logged in the wrong order
Spread across a subset of rowsA manual edit with no log row
EverywhereA missing step, usually an early obvious one
RiverRemoval check

Removal check

Run after every step that removes or nulls rows: are the rows it took distributed like the file?

Deduplication ruleRemovesLeavesVerdict
Facility name plus inspection date1,87261,502Rejected
Identical across all 22 columns21963,155Applied

What the rejected rule would have removed

 RowsShare of the file
Genuine duplicates, identical throughout2190.3%
Two real inspections of one facility, one day1,6532.6%
Of those, from the 6 two-visit counties1,02562% of the loss
Records those 6 counties hold8,87214% of the file

Sixty-two percent of the damage on fourteen percent of the state, 4.4 times an even spread, and those six counties would then appear never to re-inspect anybody. Nothing downstream catches it: the row count looks sensible and every average looks sensible. The rule that removes 1,653 same-day re-inspections does not produce a data-quality problem, it produces a finding about six counties that was manufactured by a cleaning step.

Edit with AI

What's in the pack

01

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.

02

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.

03

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.

04

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.

05

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.

06

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.

07

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.

08

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.

09

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. 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. 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. 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. 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.

Sources

  1. openrefine.orgRunning OpenRefine (opens in a new tab)
  2. GitHubThe Quartz guide to bad data: Totals differ from published aggregates (opens in a new tab)
  3. GitHubThe Quartz guide to bad data: Values are missing (opens in a new tab)

Written by

Chandler Supple

Co-founder & CEO, River

Chandler has spent the last six years working in AI, building tools for millions of users and Fortune 100 companies. In 2025, he co-founded River to build a new kind of operating system for AI agents.

LinkedInGitHubX

Reviewed by

Brian Joseff, Co-founder, River

LinkedInX

More free templates

Data Journalism Analysis Template

Run the finding under every defensible analyst choice, not just the one you picked. On the worked file 144 of 648 specifications support the headline.

Investigative Journalism Timeline Template

An evidence register and chronology that count corroboration by independent origin. On the worked file 43 events looked sourced twice and 23 actually were.

Investigative No-Surprises Letter Template

A finding-to-evidence map that drafts the no-surprises letter, then a register that logs each response so nothing changes without a documented reason.

Newsroom Correction and Accountability Pack

The register logs what changed. The cause analysis categorizes why: sourcing, analysis or editing, the pattern a correction log alone cannot show.

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