Skip to main content

Module 6 — Data cleaning

Module 8 of the introduction course established it: data preparation absorbs most of a project, and no algorithm recovers from wrong data. This module turns that observation into concrete moves. The guiding thread: every cleaning decision is a methodological choice that deserves a line of justification, not an automatic reflex.

Diagnose first, treat second​

The most common mistake is cleaning before measuring. Three commands draw up the inventory:

df.isna().mean().sort_values(ascending=False)   # missing rate PER COLUMN
df.duplicated().sum()                           # strictly identical rows
df.dtypes                                       # actual types vs expected types

Complete them with value_counts() on every categorical (typos, case variants) and describe() on every numeric (impossible values: negative ages, zero amounts, future dates).

Missing values: three questions before any treatment​

Why are they missing? The answer changes everything. A sensor down for a week (accidental absence), an optional form field (structural absence), income undeclared precisely by high earners (informative absence). In the third case, deleting or imputing erases a real signal — a boolean income_missing column is sometimes better.

How many are missing? At 2%, almost any reasonable treatment works. At 40%, the column itself is in question. In between, the trade-off depends on the variable's importance.

Drop or impute?

df = df.dropna(subset=["amount"])            # drop rows — if rare and uninformative

df["age"] = df["age"].fillna(df["age"].median())        # impute the median — robust to extremes
df["country"] = df["country"].fillna("Unknown")          # explicit category
df["temperature"] = df["temperature"].interpolate()      # time series
The imputation that leaks

If the data feeds a model, the imputation statistics (median, mean) must be computed on the training set only, then applied to the test set. Computing them on the full dataset is data leakage — the introduction course (module 5) showed what that costs. This is exactly what scikit-learn pipelines automate.

Duplicates: exact and approximate​

df = df.drop_duplicates()                                    # strictly identical rows
df = df.drop_duplicates(subset=["client_id", "date"])        # "business" duplicates
df = df.sort_values("updated").drop_duplicates(subset=["client_id"], keep="last")  # keep the latest

The strict duplicate is easy. The business duplicate — two different rows describing the same reality (same client, addresses typed differently) — requires defining the uniqueness key with the business, and the survival rule (keep). The prior question in all cases: is the duplicate a collection error or a reality (a client can legitimately order twice on the same day)?

Types: the column that lies about its nature​

A badly typed column silently corrupts everything downstream: text amounts do not sum, text dates do not compare, describe() ignores them.

df["amount"] = pd.to_numeric(df["amount"], errors="coerce")   # unconvertible → NaN
df["date"] = pd.to_datetime(df["date"], errors="coerce")
df["zip_code"] = df["zip_code"].astype("string")              # a code is NOT a number
df["segment"] = df["segment"].astype("category")              # categorical: memory and semantics

errors="coerce" turns the unconvertible into NaN which you then recount: if 3,000 values become null, the problem is upstream (decimal separator, literal "N/A", units glued to numbers) and gets fixed at the source, often right in read_csv (decimal=",", na_values=["N/A", "-"]).

The special case of identifiers: zip codes, company registration numbers, phone numbers are strings. Typing them as integers destroys leading zeros — a classic and irreversible loss once the file is rewritten.

Entry inconsistencies: the .str accessor​

df["city"] = df["city"].str.strip().str.title()       # whitespace, case
df["country"] = df["country"].replace({"FR": "France", "france": "France"})

The working method: value_counts() before to see the variants, normalize, value_counts() after to verify. For significant reference data (countries, regions), an explicit mapping table beats ad-hoc fixes: it is reviewable and reusable.

Keeping a trace of what you did​

Honest cleaning leaves a trail. The minimal form — a few log lines in the script or the notebook:

n0 = len(df)
df = df.dropna(subset=["amount"])
print(f"Missing amount: {n0 - len(df)} rows dropped ({(n0-len(df))/n0:.1%})")

If an analysis result surprises anyone, the first question will be "what did the cleaning do?"; these traces answer in seconds. Cleaning functions gathered into a module (cleaning.py, module 3) make the whole thing replayable on next month's data — the criterion that separates a cleaning process from a one-off hack.

Key takeaways​

  • Diagnose before treating: missing rate per column, duplicates, actual types, value_counts of categoricals.
  • Missing values: understand the absence mechanism before choosing between dropping, imputing or flagging; impute from the training set only if a model follows.
  • Business duplicates: define the uniqueness key and the survival rule with the business.
  • to_numeric/to_datetime with errors="coerce" then recount the NaN; identifiers as strings, never integers.
  • Every decision traced in one line; replayable cleaning is worth ten manual ones.

Next module: combining tables — joins, grouping and pivot tables, the trio that turns scattered files into answers.