Cleansing and standardising data

Why this matters

Profiling finds the problems; cleansing fixes them. It is the least glamorous part of analysis and often the largest, and it is where a careless rule can do more damage than the dirt it removes: merging two different categories, converting a valid value to missing, or overwriting the original so nobody can check the result.

The exam asks you to choose the right transformation for a problem and to recognise the risks of each. This lesson covers the everyday cleansing operations, using the course's raw customer file as the running example, and the principles that keep cleaning reversible and checkable.

The lesson

Trimming, case and whitespace

The smallest problems are the most common. Whitespace at the start or end of a value, or doubled inside it, makes values that look identical compare as different. In the raw customer file, 25 names carry stray spaces, so Ada and Ada would be counted as two first names and would fail to match in a join.

Trimming removes leading and trailing spaces, and most tools have a function for it: TRIM in SQL and spreadsheets, strip() in Python. Collapsing repeated internal spaces to one is a separate step. Non-printing characters, such as tabs, line breaks and the non-breaking space that pastes in from web pages, are harder to see and deserve an explicit check.

Case differences cause the same problem. North, NORTH and north are three values to a computer. Converting to a single case, or to title case for names, standardises them. Care is needed with values where case carries meaning, such as product codes that distinguish a from A, and with names such as "McDonald" that title-casing turns into "Mcdonald".

Standardising categories, spellings and codes

Categorical columns collect variants: abbreviations, misspellings, different codes for the same thing. The raw customer file's region column has 16 distinct values for 4 regions, including WEST, east, N. and Sth.

Case conversion and trimming fix some variants. The rest need a mapping table: a list of every variant and the standard value it becomes.

A mapping table is better than a chain of find-and-replace edits for three reasons: it is visible, so someone can check it; it is reusable, so next month's file is cleaned the same way; and it makes unmapped values obvious. After applying it, any value not in the table should be counted and reviewed, not silently kept, because a new variant has appeared.

The same approach applies to codes: country names against ISO country codes, product names against product IDs, units such as kg, kgs and kilograms. Where an official standard exists, map to it.

Type conversion and the values that refuse to convert

Many cleaning tasks are type conversions: text to number, text to date, number to text for identifiers. The conversion itself is one function call. The important part is what happens to values that fail.

A column of prices stored as text may include £12.50, 12,50, 12.5 GBP and TBC. Converting it directly either fails entirely or, in many tools, silently turns the unconvertible values into missing. Both outcomes lose information without saying so.

The safe method is to convert, count the failures, and look at them. The failures usually fall into a few patterns, such as a currency symbol, a comma as decimal separator, or a text placeholder, each of which gets its own rule. The remainder, which cannot be converted by any rule, are set to missing deliberately and counted in the cleaning record.

Dates are the hardest conversion, because a value can convert successfully and still be wrong: 03/04/2025 becomes 3 April or 4 March depending on the format assumed. A later lesson on parsing covers dates in detail.

Validation rules applied as data is cleaned

Validation rules state what a correct value looks like, and they turn cleaning from a one-off effort into a repeatable check. Common kinds include:

• Type: the value is a valid date, number or email address.
• Range: a quantity is greater than zero; a percentage is between 0 and 100; a date is not in the future.
• Allowed values: a region is one of North, South, East or West.
• Format: a postcode or product code matches its pattern.
• Uniqueness: an identifier appears once.
• Consistency across fields: a delivery date is not before its order date.

Rules are applied as part of cleaning, and every row that fails is counted and either corrected, set aside for review, or excluded with a record of why. Rules also catch what outlier detection cannot: the negative quantity of −3 in the practice orders is not unusual enough to be a statistical outlier, and fails the range rule immediately.

Keeping the raw value alongside the cleaned one

The last principle protects all the others: never overwrite the original.

Clean into a new column or a new file, and keep the raw value beside the cleaned one, at least until the cleaning has been checked. When a rule turns out to be wrong, the original is still there to reapply a corrected rule to. When someone questions a figure, the raw value shows what the source actually said.

A useful pattern is to add a small set of columns for each cleaned field: the raw value, the cleaned value, and a flag or reason code recording what was done, such as trimmed, mapped, converted or set_missing. Counting those flags gives the cleaning summary for free: how many values were changed, by which rule, and how many could not be cleaned.

After cleaning, profile again. The raw customer file should come out with 4 regions, no padded names and one date format, and the counts of each change should match the problems the first profile found. If they do not, the cleaning missed something or changed something it should not have.