Data Cleaning Techniques for Journalists

Why data cleaning matters

“Garbage in, garbage out.” If you analyse messy data without cleaning it, your results will be wrong — sometimes obviously, sometimes subtly. A council spending CSV might list the same supplier as “Capita PLC”, “CAPITA PLC”, and “Capita Plc” in three different financial years. A pivot table will treat all three as separate suppliers and undercount total Capita spending by two-thirds.

Data cleaning is the unglamorous foundation of accurate data journalism. Budget more time for it than you think you need — experienced data journalists typically spend 60–80% of their analysis time cleaning data.

Common cleaning operations

# pandas
df['supplier'] = df['supplier'].str.upper().str.strip()

# Excel formula
=UPPER(TRIM(A2))

# Remove double spaces in Excel
=TRIM(A2)  -- collapses all internal spaces to single spaces

# pandas: handle DD/MM/YYYY from government CSVs
df['date'] = pd.to_datetime(df['date'], dayfirst=True, errors='coerce')

# Check for unparseable dates
df[df['date'].isna()]

# Extract year for groupby
df['year'] = df['date'].dt.year

# pandas: check for duplicates
print(df.duplicated().sum())  # count duplicate rows
df = df.drop_duplicates()     # remove exact duplicates

# Duplicates by key columns only
df = df.drop_duplicates(subset=['transaction_id'])

# Excel: Data > Remove Duplicates (select key columns)

# pandas: audit missing values
df.isna().sum()              # count NaN per column

# Drop rows with missing values in key column
df = df.dropna(subset=['amount'])

# Fill missing category with 'Unknown'
df['category'] = df['category'].fillna('Unknown')

1. Import CSV into OpenRefine
2. Click column header > Edit cells > Cluster and edit
3. Choose Method: key collision, Keying function: fingerprint
4. Review clusters > tick 'Merge?' > set new cell value
5. Export as CSV when done

When data cleaning is most critical

• 1Supplier name analysis in council or NHS spending data — inconsistent capitalisation and abbreviation are universal.
• 2Merging two datasets with slightly different authority names or codes.
• 3Any analysis involving dates from government CSV exports — format inconsistency is common.
• 4Crime data from police.uk — areas and categories change over time and must be reconciled.
• 5Companies House data where company names change after rebranding or acquisition.

Red flags

• ⚠SUM total doesn't match the stated total in the source document — missing rows or data type issues.
• ⚠COUNTIF returns fewer matches than expected — case or whitespace inconsistency.
• ⚠Dates in the future or before the plausible range — data entry errors.
• ⚠Negative values in columns that should only be positive (e.g. population counts).
• ⚠Supplier names ending in a space character — invisible but break exact matching.

Data cleaning checklist

• I have made a copy of the raw data and am cleaning the copy.
• I have checked that numeric columns are stored as numbers, not text.
• I have standardised text case and stripped leading/trailing whitespace.
• I have checked for and handled duplicate rows.
• I have parsed date columns and verified a sample of parsed dates against the raw values.
• I have documented every cleaning step in a log or notebook.
• I have verified the cleaned total matches the source document total.
• I have used OpenRefine or fuzzy matching for text columns with many near-duplicate values.

Common mistakes

• ›Cleaning the original raw file — always work on a copy.
• ›Replacing missing numbers with 0 when 0 means something different from missing.
• ›Assuming that because a column is called "date" it contains properly formatted dates.
• ›Over-cleaning — removing rows that are genuinely valid outliers rather than errors.
• ›Not documenting what you changed — if a correction is questioned, you need to explain every step.

Related guides

Primary sources

• OpenRefine — free data cleaning tool
• pandas data cleaning documentation
• ONS methodology notes (example: crime statistics)

Frequently asked questions