OpenRefine for Journalists: Cleaning Data

1. What OpenRefine is

OpenRefine is a free, open-source tool built for one job spreadsheets do badly: finding and fixing inconsistencies in messy data. You work in a browser, but it runs entirely on your own computer at an address like 127.0.0.1:3333, so your data never leaves the machine unless you choose to use an external service. That local-first design makes it a favourite for sensitive and investigative material.

Download it from openrefine.org. It is the tool to reach for when a column contains “Manchester City Council”, “Manchester CC” and “MCC” that all mean the same body.

2. Importing your data

Start a new project from a CSV, TSV, Excel, JSON or XML file. OpenRefine shows a preview so you can confirm the delimiter, the header row and the encoding before it creates the project.

• 1Create Project, then choose your file and check the preview parses the columns correctly.
• 2Confirm the character encoding -- UK data is usually UTF-8, but older exports may be Windows-1252.
• 3Untick 'parse cell text into numbers' at first if you want full control over types later.
• 4Name the project clearly; OpenRefine keeps it in its own workspace so your original file is never altered.

3. Faceting to see what is really there

A text facet lists every distinct value in a column with a count. On a supplier or authority column it exposes the variants hiding in thousands of rows. Click a value to filter to those rows, fix them, and the facet refreshes.

Open the column menu, then Facet > Text facet. Numeric facets (Facet > Numeric facet) reveal outliers — a negative payment, a date in the wrong century, an amount with a stray decimal. Faceting turns cleaning into a systematic sweep rather than guesswork.

4. Clustering inconsistent values

Clustering is OpenRefine's signature feature. Inside a text facet, click Cluster and it groups values that are probably the same thing spelled differently, ready for you to merge each group to one agreed form.

• ›Key collision with fingerprinting ignores case, punctuation and word order -- catching 'Manchester City Council' and 'Council, Manchester City'.
• ›Nearest-neighbour methods catch typos and near-misses such as 'Manchester City Cncl'.
• ›You review each cluster and approve a single merged value; nothing changes without your say-so.

This collapses hours of find-and-replace into minutes, and — crucially — you stay in control of every merge.

5. Text transformations with GREL

GREL (the General Refine Expression Language) is a small formula language for transforming cells. Use Edit cells > Transform, then write an expression that previews live against your real data before you apply it.

value.trim()                      // strip leading/trailing spaces
value.toUppercase()               // standardise case
value.replace("Ltd.", "Ltd")      // tidy a suffix
value.replace(/\s+/, " ")         // collapse repeated spaces

A handful of functions — trim, replace, toUppercase, toTitlecase— fixes the great majority of messy columns without touching a full programming language.

6. Splitting and joining columns

Real data often crams several fields into one cell, or scatters one field across many. OpenRefine splits and joins columns from the column menu, or with a GREL expression when you need to combine values.

// join a first and last name column into one full-name column
cells["First"].value + " " + cells["Last"].value

// take the postcode area (the letters before the first digit)
value.match(/([A-Z]+).*/)[0]

For simple cases use Edit column > Split into several columns (choose a separator such as a comma) and Join columns to reverse it.

7. Reconciling against external identifiers

Reconciliation matches your messy text to entries in an external database, attaching a stable identifier. Point a name column at a reconciliation service — OpenRefine ships with support for Wikidata — and it proposes matches with confidence scores for you to confirm.

For journalism this can turn a column of company or place names into linked, unambiguous entities, ready to join to other datasets. Treat every automatic match as a suggestion to verify, not a fact: confirm the high-confidence ones and review the rest by hand.

8. The operation history for reproducibility

Every step you take is logged in the Undo / Redotab, and you can extract that log as a JSON list of operations — a complete, auditable record of how the raw data became the clean data.

• ›Open Undo / Redo, then Extract to copy the operations as JSON; save it beside your project.
• ›When an updated dataset arrives, import it and use Apply to replay every step automatically.
• ›A monthly refresh becomes a two-minute job instead of a full re-clean.
• ›If an editor queries a figure, the history shows exactly which transformations produced it.

9. Exporting cleaned data

When the data is clean, use the Exportmenu (top right) to write out CSV, TSV or Excel for the next stage — a spreadsheet, R, a database or a chart tool. Export the whole project too if you want a colleague to pick up your work with the history intact.

Keep the original download untouched. Your clean export plus the saved operation history is a tidy, reproducible package you can defend and hand on.

10. Red flags

• ⚠Merging clusters too aggressively -- two similarly named councils may be genuinely different bodies; check before you approve.
• ⚠Trusting a reconciliation match without verifying it; confidence scores are suggestions, not proof.
• ⚠Cleaning in place and losing the original -- always keep the raw download and let the history be your record.
• ⚠Letting OpenRefine auto-parse numbers, then finding leading zeros or codes silently mangled.
• ⚠Forgetting the wrong encoding turns pound signs and accents into gibberish; set UTF-8 on import.

Cleaning checklist

• I have imported with the correct delimiter and UTF-8 encoding, and kept the raw file untouched.
• I have faceted every key column to see the real range of values.
• I have clustered inconsistent names and reviewed each merge before approving it.
• I have trimmed spaces and standardised case with GREL where needed.
• I have verified any reconciliation matches rather than trusting them wholesale.
• I have extracted the operation history as JSON and exported the clean data.

Related guides

Frequently asked questions