Data Cleaning for Machine Learning: What Changes and What Doesn't

Data cleaning for machine learning uses the same rules as cleaning for a dashboard: parse types correctly, make missing values explicit, remove true duplicates, map categories with a dictionary and validate ranges. Three things change. Any value you learn from the data, such as a median or a list of known categories, must come from training rows only. Deleting rows changes the population the model learns from, so flagging beats deleting more often. And the target label becomes data you have to clean too. This post walks through each difference with runnable DuckDB SQL and pandas.

Key Takeaways

• Row-level rules (type parsing, trimming, sentinel codes to missing, dictionary mapping) work the same for analysis and for ML, and can run before the split.
• Anything learned from many rows (a fill value, an outlier cut-off, a category list, a scaler) is a model parameter: fit it on training rows and reuse it on test and production rows.
• Deleting "bad" rows before training teaches the model a cleaner world than the one it will score. Flag them and decide per feature.
• Missingness can carry signal, so keep an indicator column when you fill a value.
• Labels have errors too: a study of ten common benchmark test sets estimated an average label error rate of at least 3.3%.

What stays the same?

Most of the work is identical. A date stored as text is still a parsing problem, a blank string is still missing, and N/A, -1 or 999 are still sentinel codes that hide missing values. The data cleaning checklist you already use for reporting work applies unchanged.

These steps share one property: each looks at a single row (or a fixed, written-down rule) and gives the same answer no matter which other rows are in the table. That makes them safe to run on the whole dataset before you split it.

If you want to practise the rule-based half, mapping channels with an approved dictionary and turning sentinel codes into missing values are good places to start.

What changes: learned values come from training rows only

The biggest change is that any value computed across many rows becomes part of the model. If that value sees test rows, your evaluation is measured on data the model has, in a small way, already seen. This is data leakage, and it makes offline scores look better than production results.

The scikit-learn documentation states the rule plainly: "never call fit on the test data" (scikit-learn, Common pitfalls and recommended practices). Its own example shows how far the error can go: selecting features from 10,000 random columns using all rows before the split gives about 0.76 accuracy on pure noise, while selecting on training rows only gives the expected 0.5.

Data leakage is information from outside the training rows, such as a median computed on test data, reaching the model during training. Leakage is common in published research too. The Princeton "Leakage and the Reproducibility Crisis in ML-based Science" project lists 41 papers across 30 fields that found leakage errors, collectively affecting 648 papers, and names "pre-processing on train and test sets together" as one of the recurring pitfalls (Kapoor and Narayanan, Princeton, retrieved 2026-11-05).

A worked example: one median, two answers

Here are eight orders with a delivery_days column, three of them missing. The model will train on orders before March and be tested on March orders. Computing the fill value from every row gives a different number than computing it from training rows only:

CREATE TABLE orders AS SELECT * FROM (VALUES
 (1, DATE '2026-01-04', 'web',   3.0),
 (2, DATE '2026-01-09', 'store', NULL),
 (3, DATE '2026-01-15', 'web',   5.0),
 (4, DATE '2026-02-02', 'phone', 4.0),
 (5, DATE '2026-02-20', 'web',   NULL),
 (6, DATE '2026-03-03', 'web',   21.0),
 (7, DATE '2026-03-11', 'store', 18.0),
 (8, DATE '2026-03-19', 'kiosk', NULL)
) AS t(order_id, order_date, channel, delivery_days);

SELECT
  median(delivery_days) AS median_all_rows,
  median(delivery_days) FILTER (WHERE order_date < DATE '2026-03-01') AS median_train_only
FROM orders;

┌─────────────────┬───────────────────┐
│ median_all_rows │ median_train_only │
├─────────────────┼───────────────────┤
│             5.0 │               4.0 │
└─────────────────┴───────────────────┘

The slow March deliveries pull the all-rows median up to 5.0. If you fill with 5.0, the test set has quietly informed the training data. The fix is to learn the value once, from the training part, and reuse it everywhere:

WITH split AS (
  SELECT *, CASE WHEN order_date < DATE '2026-03-01' THEN 'train' ELSE 'test' END AS part
  FROM orders
),
learned AS (
  SELECT median(delivery_days) AS fill_value FROM split WHERE part = 'train'
)
SELECT order_id, part, delivery_days,
       delivery_days IS NULL AS delivery_days_was_missing,
       coalesce(delivery_days, learned.fill_value) AS delivery_days_filled
FROM split CROSS JOIN learned
ORDER BY order_id;

┌──────────┬─────────┬───────────────┬───────────────────────────┬──────────────────────┐
│ order_id │  part   │ delivery_days │ delivery_days_was_missing │ delivery_days_filled │
├──────────┼─────────┼───────────────┼───────────────────────────┼──────────────────────┤
│        1 │ train   │           3.0 │ false                     │                  3.0 │
│        2 │ train   │          NULL │ true                      │                  4.0 │
│        3 │ train   │           5.0 │ false                     │                  5.0 │
│        4 │ train   │           4.0 │ false                     │                  4.0 │
│        5 │ train   │          NULL │ true                      │                  4.0 │
│        6 │ test    │          21.0 │ false                     │                 21.0 │
│        7 │ test    │          18.0 │ false                     │                 18.0 │
│        8 │ test    │          NULL │ true                      │                  4.0 │
└──────────┴─────────┴───────────────┴───────────────────────────┴──────────────────────┘

Order 8 in the test set gets 4.0, the training median, and keeps a delivery_days_was_missing flag so the model can still tell a filled value from a measured one. The fill gaps without leakage exercise asks you to do exactly this, and the guide to avoiding leakage in data preparation covers the other common leaks.

The same idea in pandas

In pandas the pattern is "fit" (compute values from train) and "apply" (a function that only reads those stored values). The snippet loads the same eight orders from the DuckDB table above, so it runs as shown. The same prepare function then runs on test rows and, later, on production rows:

import duckdb

df = duckdb.sql("SELECT * FROM orders").df()
train = df[df["order_date"] < "2026-03-01"]
test = df[df["order_date"] >= "2026-03-01"]

fill_value = train["delivery_days"].median()
known_channels = set(train["channel"])

def prepare(part):
    out = part.copy()
    out["delivery_days_was_missing"] = out["delivery_days"].isna()
    out["delivery_days"] = out["delivery_days"].fillna(fill_value)
    out["channel"] = out["channel"].where(out["channel"].isin(known_channels), "other")
    return out

print(prepare(test)[["order_id", "channel", "delivery_days", "delivery_days_was_missing"]])

   order_id channel  delivery_days  delivery_days_was_missing
5         6     web           21.0                      False
6         7   store           18.0                      False
7         8   other            4.0                       True

Notice kiosk. It never appeared in training, so it becomes other instead of crashing an encoder or creating a column the model has never seen. The categories the model never saw exercise practises that case.

What changes: deleting rows changes what the model learns

In a report, removing an obviously broken row usually affects one number, and you can footnote it. In training data, every deleted row removes an example from the world the model learns. If you drop every order with a missing delivery time, and missing times are more common for one carrier or region, the model learns from a population that will not match the rows it scores in production.

That is why the advice to flag outliers instead of deleting them matters more for ML, not less. A flag column keeps the row, records why it looked unusual and lets you decide per feature: cap a value, leave it, or exclude it from one model with a documented reason.

Rows that are truly invalid, such as test records or exact duplicates, should still go. Duplicates need one extra check: the same record in both the training and test parts lets the model be graded on an example it memorised. The Princeton survey lists "duplicates across train-test split" among its pitfalls for this reason.

What changes: missing values can be a signal

For a dashboard, you usually want missing values out of the way: excluded from an average, or shown as their own group. For a model, the fact that a value is missing can be informative. A customer who skipped the income field may behave differently from one who filled it in.

Filling a gap with a median erases that information unless you also keep a boolean indicator, as in the example above. The trade-offs between fill methods are compared in imputing missing values: mean, median, model or nothing? The impute with an indicator column exercise drills this. Labels that are missing, though, are different: a row with no target cannot be used for supervised training and should be set aside, not filled.

What changes: the label is data too

Analysis work rarely has a "target column". In ML it is the column that matters most, and it has errors like any other. Northcutt, Athalye and Mueller estimated label errors of at least 3.3% on average across ten widely used benchmark test sets, and at least 6% of the ImageNet validation set (Pervasive Label Errors in Test Sets Destabilize Machine Learning Benchmarks, 2021). They also found that on corrected ImageNet labels, ResNet-18 outperformed ResNet-50 once the share of originally mislabeled test examples rose by just 6%, so label noise can change which model looks best.

Practical label checks reuse familiar cleaning moves:

• Profile the label like any categorical column: spelling variants, unexpected values, missing counts.
• Check the label definition against time. A "churned" flag computed after the prediction date includes future information.
• Look for duplicates with conflicting labels. Two identical inputs with different targets point to a labelling or join problem.

What changes: time order and the split itself

Many business datasets are time-ordered: orders, transactions, sensor readings. A random split lets the model train on next month to predict last month, which production will never allow. Split by time when the model will predict the future, and apply the same "fit on the earlier part" rule to every learned value. The split train and test by time exercise practises this.

Cross-validation raises the same issue once per fold. If you score a model on five folds, every learned value has to be refitted inside each fold on that fold's training rows. Computing a median once on all rows and then cross-validating leaks just as a single split would. scikit-learn's documentation recommends wrapping the preparation steps and the model in a Pipeline for this reason, so each fold fits its own values.

A short checklist for ML data preparation

1. Run row-level cleaning rules on all data: types, missing markers, trimming, dictionary mapping.
2. Remove exact duplicates, then check for the same entity across the split.
3. Split the data (by time if the model predicts the future).
4. Fit every learned value on training rows: fill values, cut-offs, category lists, scalers, encoders.
5. Apply those stored values to test rows without refitting.
6. Keep missing-value indicators and outlier flags instead of deleting rows.
7. Profile and validate the label like any other column.
8. Save the fitted values so production uses exactly the same ones.

The order of the later steps, including scaling and encoding, is covered in getting the order of data prep right. Step 8 is where reproducible cleaning pipelines pay off: if the cleaning lives in code with stored parameters, training and production cannot drift apart.

Frequently Asked Questions

Is data cleaning different for machine learning?

The rules are the same; the order and scope change. Row-level fixes can run on everything. Any value learned from multiple rows, such as a median, must be computed on the training set only and reused on test and production data.

Should I remove outliers before training a model?

Usually not by default. Flag them with a reason, then decide per feature whether to cap, keep or exclude. Removing them changes the population the model learns from, and any outlier cut-off computed from data must itself come from training rows only.

Can I clean the whole dataset before splitting it?

Only the rule-based steps. Parsing, trimming, mapping with a fixed dictionary and converting sentinel codes are safe on all rows. Filling with statistics, scaling, encoding with a learned category list and outlier thresholds belong after the split.

Related reading

• Do Data Scientists Really Spend 80% of Their Time Cleaning Data?

Sources

• scikit-learn developers, Common pitfalls and recommended practices, retrieved 2026-11-05.
• Sayash Kapoor and Arvind Narayanan, Leakage and the Reproducibility Crisis in ML-based Science, Princeton University, retrieved 2026-11-05.
• Curtis G. Northcutt, Anish Athalye and Jonas Mueller, Pervasive Label Errors in Test Sets Destabilize Machine Learning Benchmarks, NeurIPS Datasets and Benchmarks, 2021.