Data Cleaning & Preprocessing

Cleaning and preprocessing are different jobs

The two get named in one breath, which blurs them. Cleaning restores data to what it should have been all along. Preprocessing changes it into something a model can use.

That last row is where real projects go wrong. Fixing a typo using the whole dataset harms nothing. But the moment you compute a mean in order to fill blanks, that mean has to come from the training rows alone.

The four things cleaning deals with

• •
  Duplicates — rows that are already identical, and rows that only become identical after cleaning.
• •
  Types — dates and numbers that arrived as strings. Comparison and sorting both go wrong.
• •
  Consistency — one value fragmented by whitespace, casing or naming.
• •
  Sentinels — 999 or −1 standing in for missing. They corrupt statistics without a warning.

Never edit the source

Open the file and fix it by hand, and the work evaporates. Nobody knows what you changed, next month's extract needs the whole job doing again, and when a result looks odd there is nothing to trace back through.

• •
  The raw file is read-only and never overwritten
• •
  Every fix is code — one readable line at a time
• •
  Rerunning it has to produce exactly the same table
• •
  Log the row count after each step
• •
  Comment why the fix was made, not just what it does

The steps do not commute

Cleaning steps look independent, which is why nobody thinks about ordering them. In fact each one creates the state the next one needs in order to see anything.

For the same reason, type casting comes first almost always. While a date is text, 2025-10-01 sorts before 2025-2-01, and every duration calculation and range filter downstream is wrong.

Sentinels corrupt statistics quietly

An age column averaging 100 because of a few 999s gets noticed. The dangerous case is the less extreme one: a handful of −1s in "purchase count" pulls the mean down just slightly, and nobody ever looks twice.

Finding them is easy. For each numeric column, look at the minimum, the maximum and the most frequent values. If 999 is a genuine measurement there will be one of it; if it is a sentinel there will be dozens, all identical.

How preprocessing leaks

Cleaning on the full dataset is broadly safe. Anything that learns a parameter is not. These steps extract a number from the data and remember it, and if that number carries information from the test rows, you have leakage.

1. Seven steps, in this order

The sequence below is not a preference. Each step sits where it does because it needs what the previous step produced.

Step 6 is the dividing line. Above it the work has a right answer, so the full dataset is fine. Below it the work learns numbers from the data, so it may only ever see the training rows.

2. Sort the duplicates by kind first

Five quite different situations hide under the phrase "drop duplicates". Delete indiscriminately and you lose real records.

The last row is the one to remember. If the same customer paid the same amount twice on the same day, those are two transactions, not a duplicate. Dropping them because the rows match deletes revenue.

3. A standard order for text normalisation

Strings that look identical to a reader are frequently different to a machine. Working through these in order clears up most of it.

4. Write the range rules down

Rules that come from the domain — "age is between 0 and 120" — cost one line of code and then catch violations in next month's extract automatically. Kept in your head, they get missed every time.

• •
  Numeric: the physically possible minimum and maximum
• •
  Dates: nothing before launch, nothing after today
• •
  Ordering: signup ≤ first purchase ≤ last purchase
• •
  Totals: the parts must not exceed the whole
• •
  Categories: flag anything outside the allowed list

In practice: a cleaning log for 50,000 rows

Here is the log from cleaning a subscription membership table. Recording the row count at every step means any later question about the numbers has an answer.

The total loss is 2,096 rows, or 4.2%. But 1,683 of those came from an export job that ran twice — which is not a data problem, it is a pipeline problem. Deleting the rows and moving on is the wrong response; telling the data team is the right one.

In practice: the rows that survived because someone asked

In the same table, 41 rows had a negative tenure_days. It looked like an obvious error and was about to be deleted. Asking why turned up a reason.

The whole difference is between "this looks wrong, delete it" and "this looks wrong, find out why". Odd values are usually information about how the data came to exist.

Common misunderstandings

Run the same steps in different orders

Twelve rows. The four steps are applied in the order you click them, and the badge on each button shows its position. Turn all four on and the surviving row count still depends on the sequence.

What to look for

• •
  With nothing enabled, why are there ten distinct cities?
• •
  Click "Trim & case" then "Drop duplicates" — how many rows remain?
• •
  Now the other way round. How many remain then?
• •
  How far does the mean spend move once −1 becomes missing?
• •
  Casting dates changes no row counts — so why bother?