Cleaning and preparing data

01Cleaning is editorial work

Cleaning is often described as preparation, as though the decisions in it were mechanical. They are not. Whether a blank cell means zero or unknown, whether two similar names are one organisation, whether an outlier is an error or the story: each choice changes what the analysis can conclude, and each is a judgement that belongs in the published methodology.

The discipline that makes cleaning defensible is separation. The original file is never edited. Transformations are applied in a script or a documented sequence of steps, so that anyone can start from the same source and reach the same table.

02From human layout to analysable structure

Files built for reading break most analysis tools. The recurring problems are merged header cells, a title occupying the first rows, subtotals and totals inside the data area, blank spacer rows, categories implied by indentation, meaning carried by cell colour, and several tables stacked in one sheet.

The target structure is plain: one header row with unique names, one row per observation, one column per variable, no totals inside the data, no formatting that carries meaning. Every element removed in reaching that structure should be noted, because a total inside the data may be the only place a published figure appears.

03Types, units and silent coercion

Type errors cause the quietest failures. Identifiers with leading zeros lose them when read as numbers; long codes are rendered in scientific notation; dates shift between day-first and month-first conventions; numbers stored with thousands separators or a trailing footnote marker become text and drop out of sums.

Units need equal attention: euro and thousands of euro, counts and rates, per cent and percentage points, tonnes and kilograms. A single unit mismatch across a join produces a result that looks plausible and is wrong by three orders of magnitude, which is exactly the kind of error that survives review.

04Missing values are not zeros

A dataset can encode absence in many ways: an empty cell, a zero, a negative sentinel such as minus one, a text marker like "n/a" or a suppression code used when a count is too small to publish for privacy reasons. Treating any of these as zero manufactures facts.

The correct handling depends on the meaning, which usually has to be established with the publisher. Suppression for small counts is particularly important in Irish and European statistics: it means the value exists and cannot be released, so summing the visible rows produces a total below the real one, and that shortfall must be stated.

05Duplicates and near-duplicates

Exact duplicates are easy to remove and rarely the real problem. The difficulty is near-duplicates: the same company recorded with and without a legal suffix, a name with a typographical error, an address written two ways, a person listed with initials in one file and a full name in another.

Matching such records requires normalisation, a documented rule for what counts as a match, and manual review of the borderline cases. The count of records merged by judgement rather than by exact rule is itself a number worth publishing, because it tells the reader how much of the result rests on interpretation.

06Joining tables safely

A join is where errors multiply. Before joining, check that the key is unique on at least one side, count the rows before and after, and count the records that failed to match. An unexplained increase in row count means the key was not unique; a large unmatched set means the two sources describe overlapping but different populations.

Unmatched records deserve inspection rather than deletion. They frequently contain the most interesting cases: entities that exist in one system and not another, which may be the finding rather than a nuisance.

07Outliers: error, edge case or story

An extreme value has three possible explanations, and they cannot be distinguished by statistics alone. It may be a recording error, a legitimate extreme, or a genuine anomaly worth reporting. The only reliable test is to return to the source record and, where possible, to ask the publisher.

Removing outliers to make a chart tidier is a substantive change to the finding. If they are excluded, the exclusion, the reason and the effect on the result all belong in the note that accompanies the analysis.

08Validation rules that catch what reading cannot

Scanning a file finds obvious damage and misses the errors that matter. A short set of explicit rules, run after every transformation, finds them cheaply. Range checks assert that a value is possible: no negative counts, no percentages above one hundred, no dates in the future, no ages beyond a plausible bound. Referential checks assert that every code in a column exists in the lookup it is supposed to come from, which is how a renamed authority or a retired category announces itself.

Two further checks are worth running on every dataset. Reconciliation compares your own totals with the totals the publisher states in the same release; a mismatch is either a cleaning error or a suppression you have not accounted for, and both need resolving before analysis. Cross-field consistency tests relationships that must hold: an end date after a start date, a subtotal not exceeding its total, a count of inspections not exceeding the number of premises. Recording which rules were run, and how many rows failed each, turns cleaning into something a reviewer can check rather than trust.

09A change log that survives the story

The output of cleaning is two things: an analysable table and a record of how it was produced. That record lists each step, the rule applied, the number of rows affected and the decisions taken by judgement. With it, a colleague can reproduce the table and a correction can be traced to a specific step. Without it, the analysis is a claim about a file that no longer exists in that form.