Module 3 of 6 · 35 minutes

Cleaning data

Cleaning is a series of judgement calls. This module shows how to make them well — and how to leave a trail others can follow.

Learning objectives

Clean on a copy and log every decision
Remove duplicates and standardise categories
Fix types and invalid values
Choose a missing-value strategy
Investigate and handle outliers

3.1 Work on a copy and log every decision

clean = customers.copy()
decisions = []      # keep a log: what, why, how many rows

def log(step, reason, rows):
    decisions.append({"step": step, "reason": reason, "rows_affected": int(rows)})

3.2 Duplicates

n = clean.duplicated().sum()
clean = clean.drop_duplicates()
log("drop exact duplicates", "customer 103 appeared twice", n)

Watch for near-duplicates too — the same person with a slightly different email or name spelling — which need matching rules or record-linkage tools.

3.3 Standardise categories

country_map = {"uk": "UK", "united kingdom": "UK", "u.k.": "UK",
               "ireland": "IE", "ie": "IE"}
before = clean["country"].copy()
clean["country"] = clean["country"].str.strip().str.lower().map(country_map).fillna(clean["country"])
log("standardise country", "five spellings of UK, two of Ireland", (before != clean["country"]).sum())

clean["churned"] = clean["churned"].str.strip().str.lower().map({"yes": True, "no": False})

3.4 Fix types and invalid values

dates = pd.to_datetime(clean["signup_date"], format="%Y-%m-%d", errors="coerce")
alt   = pd.to_datetime(clean["signup_date"], format="%d/%m/%Y", errors="coerce")   # UK day/month format
clean["signup_date"] = dates.fillna(alt)          # "2025-13-01" stays missing: invalid

invalid_age = (clean["age"] < 16) | (clean["age"] > 110)
log("set impossible ages to missing", "230 and -1 are not valid ages", invalid_age.sum())
clean.loc[invalid_age, "age"] = np.nan

Note: "04/02/2025" could mean 4 February (UK) or 2 April (US). Ambiguous formats must be resolved by knowing the source system — don't guess.

3.5 Missing values: choose a strategy

Strategy	When it fits	Risk
Drop rows	Few rows affected, missing at random	Loses data; can bias the sample if missingness isn't random
Drop column	Mostly empty and not essential	Loses a possibly useful signal
Fill with median / mode	Small gaps in numeric or categorical data	Shrinks variation; hides uncertainty
Fill with a model (imputation)	Values predictable from other columns	More complex; can leak information if done before splitting
Keep and flag	Missingness itself may be meaningful	Models must handle the flag

clean["age_missing"] = clean["age"].isna()                 # keep the signal
median_age = clean["age"].median()
clean["age"] = clean["age"].fillna(median_age)
log("fill missing age with median", f"median = {median_age}", clean["age_missing"].sum())

Fit cleaning on training data only

Statistics used to fill values (medians, means, scalers) should be calculated from the training data and then applied to test data. Calculating them on all data leaks information from the test set (Module 5).

3.6 Outliers: investigate, then decide

suspect = clean["monthly_spend"] > 1000
print(clean[suspect])
# After checking with the finance team: 12000.0 was entered in pence, not pounds.
clean.loc[suspect, "monthly_spend"] = clean.loc[suspect, "monthly_spend"] / 100
log("convert spend entered in pence", "confirmed with finance team", suspect.sum())

print(pd.DataFrame(decisions))

Other options include capping (winsorising) extreme values, transforming skewed data (e.g. log), or keeping genuine extremes and using models that are robust to them.

Exercise: clean the churn column safely

Some systems export churn as 'Y', 'N', 'true', 'false', '1' or '0'. Write a function that maps all of these (case-insensitive) to True/False and leaves anything else as missing.

Show a solution

def to_bool(series):
    mapping = {"yes": True, "y": True, "true": True, "1": True,
               "no": False, "n": False, "false": False, "0": False}
    return series.astype(str).str.strip().str.lower().map(mapping)

print(to_bool(pd.Series(["Y", "no", "TRUE", "0", "maybe"])).tolist())
# [True, False, True, False, nan]

The decision log from this module's cleaning code. Anyone can now see — and challenge — exactly what changed and why.

Key takeaways

Work on a copy; log what, why and how many rows.
Map messy categories to standard codes.
Ambiguous formats need source knowledge, not guesses.
Every missing-value strategy has risks — consider flagging.
Fit cleaning statistics on training data only.