SyntaxPilot
Courses/Machine Learning/Lesson 14

Machine Learning · Lesson 14 of 90 · 13 min

Data cleaning

missing valuesMCARMARMNARdropping vs imputingmeanmedian and mode imputationKNN imputermissing-indicator featureduplicatesoutlier detection with IQR and z-scorecapping (winsorising)fixing types and inconsistent categories

Machine Learning Full Course · Episode 14. Every code sample below was run with Python 3.13, NumPy 2.5.3, pandas 3.0.5 and scikit-learn 1.9.1; the output shown is the real output. All data is made up for teaching.

The big idea: Episode 13's EDA gave us a list of surprises. Data cleaning fixes them: wrong types, inconsistent categories, duplicates, missing values and outliers. Dirty data doesn't always crash. Sometimes it quietly gives you the wrong answer, which is worse.

Numbers stored as text (wrong types)

You copy seven listings from a property website. Some rents are written "18,500" (with a comma) and one owner typed ask instead of an area. Let's compute the average rent:

import sys
sys.tracebacklimit = 0
import io, pandas as pd
raw = """area,locality,rent
1200,Baner,"18,500"
850,baner ,12000
640,Wakad,"9,800"
ask,Kothrud,15000
1100,BANER,"21,000"
850,baner ,12000
980,Kothrud,"16,200"
"""
df = pd.read_csv(io.StringIO(raw))      # 7 scraped listings
print(df.dtypes)
print(df["rent"].mean())

Output:

area        str
locality    str
rent        str
dtype: object
TypeError: Cannot perform reduction 'mean' with string dtype
  • io.StringIO(raw) lets pd.read_csv read a string as if it were a file.
  • Every column is str (text): one value pandas can't read as a number ("18,500", ask) turns the whole column into text.
  • A reduction is an operation that turns a column into one number (mean, sum, max). Text has no mean, so: TypeError: Cannot perform reduction 'mean' with string dtype.

The fix, two steps:

  1. Clean the text: .str.replace(",", "") removes the commas.
  2. Convert: pd.to_numeric(...). With errors="coerce", anything that still can't be read (like ask) becomes NaN.

NaN ("Not a Number") is how pandas and NumPy mark a missing value, an empty cell.

import io, pandas as pd
raw = """area,locality,rent
1200,Baner,"18,500"
850,baner ,12000
640,Wakad,"9,800"
ask,Kothrud,15000
1100,BANER,"21,000"
850,baner ,12000
980,Kothrud,"16,200"
"""
df = pd.read_csv(io.StringIO(raw))      # 7 scraped listings
df["rent"] = pd.to_numeric(df["rent"].str.replace(",", ""))
df["area"] = pd.to_numeric(df["area"], errors="coerce")
print(df.dtypes)
print("mean rent:", round(df["rent"].mean()))
print(df)

Output:

area        float64
locality        str
rent          int64
dtype: object
mean rent: 14929
     area locality   rent
0  1200.0    Baner  18500
1   850.0   baner   12000
2   640.0    Wakad   9800
3     NaN  Kothrud  15000
4  1100.0    BANER  21000
5   850.0   baner   12000
6   980.0  Kothrud  16200

Converting a column to the right type is called fixing types (or type casting). Note that pandas already turns the text N/A, NA, null and empty cells into NaN when reading a CSV; values like ask or - need errors="coerce".

Inconsistent categories and duplicates

import io, pandas as pd
raw = """area,locality,rent
1200,Baner,"18,500"
850,baner ,12000
640,Wakad,"9,800"
ask,Kothrud,15000
1100,BANER,"21,000"
850,baner ,12000
980,Kothrud,"16,200"
"""
df = pd.read_csv(io.StringIO(raw))      # 7 scraped listings
df["rent"] = pd.to_numeric(df["rent"].str.replace(",", ""))
df["area"] = pd.to_numeric(df["area"], errors="coerce")
print(df["locality"].unique().tolist())
df["locality"] = df["locality"].str.strip().str.title()
print(df["locality"].value_counts())
print("duplicate rows:", df.duplicated().sum())
df = df.drop_duplicates()
print("rows left:", len(df))

Output:

['Baner', 'baner ', 'Wakad', 'Kothrud', 'BANER']
locality
Baner      4
Kothrud    2
Wakad      1
Name: count, dtype: int64
duplicate rows: 1
rows left: 6
  • unique() lists every distinct value: 'Baner', 'baner ' (trailing space) and 'BANER' are three different strings, so pandas would treat them as three places. These are inconsistent categories.
  • .str.strip() removes spaces at both ends; .str.title() makes the first letter of each word capital and the rest small. Now Baner has 4 listings.
  • A duplicate is a row identical to another row (the same flat posted twice). df.duplicated() marks repeats; df.drop_duplicates() removes them. A duplicate counts one flat twice and skews every average. For near-duplicates, check a subset of columns: df.duplicated(subset=["area", "locality", "rent"]).

Missing values: why the blanks matter

300 owners fill in a form (area, bedrooms, rent). Some leave area blank, and in our made-up data it's mostly the owners of small flats (60% of flats under 800 sq ft hide it, 5% of the rest). We keep a secret copy of the true areas only to check our work:

import numpy as np, pandas as pd
rng = np.random.default_rng(14); n = 300       # 300 made-up flats
area = rng.normal(950, 300, n).clip(350, 2500).round()
df = pd.DataFrame({"area": area,
                   "bhk": np.clip(area // 450, 1, 3).astype(int),
                   "rent": (area * 20 * rng.lognormal(0, .25, n)).round(-2)})
true_area = df["area"].copy()          # only to check our work
hide = rng.random(n) < np.where(area < 800, .6, .05)  # small hide
df.loc[hide, "area"] = np.nan
print(df.isna().sum())
print("true mean area:   ", true_area.mean().round(1))
print("mean of the rest: ", df["area"].mean().round(1))
rand = rng.random(n) < 0.19                    # if blanks were random
print("mean if random:   ", true_area[~rand].mean().round(1))

Output:

area    57
bhk      0
rent     0
dtype: int64
true mean area:    975.4
mean of the rest:  1035.7
mean if random:    968.5
  • df.isna() marks every missing cell; .sum() counts them per column: 57 missing areas (19%).
  • The average of the visible areas, 1035.7, is far above the true 975.4: the small flats are hiding.
  • If the same number of blanks had been chosen at random, the average of the rest would be 968.5, close to the truth. Why a value is missing changes what you can do about it.

MCAR, MAR and MNAR

Three reasons values go missing, pictured with lost exam answer sheets:

Type Exam picture Meaning In our data
MCAR: missing completely at random rain soaks a random bundle of sheets missingness has nothing to do with any value a server randomly lost some fields
MAR: missing at random (given the other columns) one school's bus came late missingness depends on columns we can see e.g. listings from one website never show area
MNAR: missing not at random weak students skip the paper missingness depends on the missing value itself small flats hide their area
  • With MCAR, the remaining rows are still a fair sample: dropping them loses data but adds no bias.
  • With MAR, other columns explain the gaps, so methods that use those columns (like KNN imputation) can fill them well.
  • With MNAR, the data alone can't fully fix the bias; keep a missing-indicator column and think about the cause.

You usually can't prove which type you have; you reason about how the data was collected.

The model can't take NaN; dropping is dangerous

import sys
sys.tracebacklimit = 0
import numpy as np, pandas as pd
rng = np.random.default_rng(14); n = 300       # 300 made-up flats
area = rng.normal(950, 300, n).clip(350, 2500).round()
df = pd.DataFrame({"area": area,
                   "bhk": np.clip(area // 450, 1, 3).astype(int),
                   "rent": (area * 20 * rng.lognormal(0, .25, n)).round(-2)})
true_area = df["area"].copy()          # only to check our work
hide = rng.random(n) < np.where(area < 800, .6, .05)  # small hide
df.loc[hide, "area"] = np.nan
from sklearn.linear_model import LinearRegression
LinearRegression().fit(df[["area", "bhk"]], df["rent"])

Output:

ValueError: Input X contains NaN.
LinearRegression does not accept missing values encoded as NaN natively. For supervised learning, you might want to consider sklearn.ensemble.HistGradientBoostingClassifier and Regressor which accept missing values encoded as NaNs natively. Alternatively, it is possible to preprocess the data, for instance by using an imputer transformer in a pipeline or drop samples with missing values. See https://scikit-learn.org/stable/modules/impute.html You can find a list of all estimators that handle NaN values at the following page: https://scikit-learn.org/stable/modules/impute.html#estimators-that-handle-nan-values

Most models need a value in every cell. The simplest fix, dropping every row with a NaN (dropna):

import numpy as np, pandas as pd
rng = np.random.default_rng(14); n = 300       # 300 made-up flats
area = rng.normal(950, 300, n).clip(350, 2500).round()
df = pd.DataFrame({"area": area,
                   "bhk": np.clip(area // 450, 1, 3).astype(int),
                   "rent": (area * 20 * rng.lognormal(0, .25, n)).round(-2)})
true_area = df["area"].copy()          # only to check our work
hide = rng.random(n) < np.where(area < 800, .6, .05)  # small hide
df.loc[hide, "area"] = np.nan
print("rows before dropna:", len(df), " after:", len(df.dropna()))
print("rent mean, all flats:   ", df["rent"].mean().round())
print("rent mean, after dropna:", df.dropna()["rent"].mean().round())

Output:

rows before dropna: 300  after: 243
rent mean, all flats:    19825.0
rent mean, after dropna: 21107.0

We lost 19% of our data, and the average rent jumped from 19,825 to 21,107, because the dropped rows were the small, cheap flats. Dropping MNAR rows removes a whole kind of example: a model trained on the rest has never seen small flats.

Option When it's OK
Drop rows (dropna) few rows missing and MCAR
Drop the column the column is mostly empty and not important
Impute (fill each blank with a sensible guess) almost always the better default

Mean, median and mode imputation

Imputation = filling missing values with estimates.

import pandas as pd

rent = pd.Series([12000, 15000, None, 14000, 250000, None])   # one villa
print("mean fill:  ", rent.fillna(rent.mean()).tolist())
print("median fill:", rent.fillna(rent.median()).tolist())

furn = pd.Series(["semi", "none", None, "semi", "full"])
print("mode:", furn.mode()[0])
print("mode fill:  ", furn.fillna(furn.mode()[0]).tolist())

Output:

mean fill:   [12000.0, 15000.0, 72750.0, 14000.0, 250000.0, 72750.0]
median fill: [12000.0, 15000.0, 14500.0, 14000.0, 250000.0, 14500.0]
mode: semi
mode fill:   ['semi', 'none', 'semi', 'semi', 'full']
  • Mean imputation fills 72,750: the one villa (₹2,50,000) pulled the mean up.
  • Median imputation fills 14,500, a typical flat. Prefer the median when a column is skewed or has outliers.
  • Mode imputation (the most frequent value) is for categorical columns: semi. Text has no mean or median.

In scikit-learn: SimpleImputer(strategy="mean" | "median" | "most_frequent" | "constant").

KNN imputer: ask the neighbours

Mean and median give every blank the same guess. But a 1 BHK renting for ₹9,000 surely isn't 1000 sq ft. KNN imputation finds the k rows most similar to the incomplete row (its k nearest neighbours, measured on the columns that are known) and fills the blank with their average.

import numpy as np, pandas as pd
rng = np.random.default_rng(14); n = 300       # 300 made-up flats
area = rng.normal(950, 300, n).clip(350, 2500).round()
df = pd.DataFrame({"area": area,
                   "bhk": np.clip(area // 450, 1, 3).astype(int),
                   "rent": (area * 20 * rng.lognormal(0, .25, n)).round(-2)})
true_area = df["area"].copy()          # only to check our work
hide = rng.random(n) < np.where(area < 800, .6, .05)  # small hide
df.loc[hide, "area"] = np.nan
from sklearn.impute import SimpleImputer, KNNImputer
miss = df["area"].isna()
for name, imp in [("mean", SimpleImputer(strategy="mean")),
                  ("median", SimpleImputer(strategy="median")),
                  ("knn", KNNImputer(n_neighbors=5))]:
    filled = imp.fit_transform(df)[:, 0]          # column 0 = area
    err = np.abs(filled[miss] - true_area[miss]).mean()
    print(name, filled[miss][:3].round().tolist(), "avg error", round(err))

Output:

mean [1036.0, 1036.0, 1036.0] avg error 377
median [1040.0, 1040.0, 1040.0] avg error 380
knn [854.0, 444.0, 448.0] avg error 186
  • fit_transform learns the fill values from the data and returns the filled table (a NumPy array; column 0 is area).
  • Mean and median imputation are off by about 380 sq ft on average; KNN halves the error (186), giving each flat its own guess.
  • Catch: KNN measures similarity by distance, and rent (thousands) dominates bedrooms (1 to 3). Putting features on the same scale is Episode 16. KNN as a model is Episode 27.

The missing-indicator feature

If small flats hide their area, then the blank itself is information:

import numpy as np, pandas as pd
rng = np.random.default_rng(14); n = 300       # 300 made-up flats
area = rng.normal(950, 300, n).clip(350, 2500).round()
df = pd.DataFrame({"area": area,
                   "bhk": np.clip(area // 450, 1, 3).astype(int),
                   "rent": (area * 20 * rng.lognormal(0, .25, n)).round(-2)})
true_area = df["area"].copy()          # only to check our work
hide = rng.random(n) < np.where(area < 800, .6, .05)  # small hide
df.loc[hide, "area"] = np.nan
df["area_missing"] = df["area"].isna().astype(int)
print(df.groupby("area_missing")["rent"].mean().round())

from sklearn.impute import SimpleImputer
imp = SimpleImputer(strategy="median", add_indicator=True)
out = imp.fit_transform(df[["area"]])
print(pd.DataFrame(out, columns=imp.get_feature_names_out()).head(3))

Output:

area_missing
0    21107.0
1    14360.0
Name: rent, dtype: float64
     area  missingindicator_area
0  1159.0                    0.0
1  1040.0                    1.0
2  1040.0                    1.0
  • Flats that hid their area rent for ₹14,360 on average vs ₹21,107: the blank predicts a cheaper flat.
  • A 0/1 column recording whether a value was missing is a missing-indicator feature. SimpleImputer(add_indicator=True) adds it automatically (missingindicator_area), so imputing doesn't erase the clue. Use it whenever you suspect MNAR.

Outliers: the IQR rule and the z-score

An outlier is a value far from the rest. Two rules to flag them, on Episode 13's flats (with the planted typo):

import numpy as np, pandas as pd
rng = np.random.default_rng(13); n = 300     # episode 13's flats
area = rng.normal(950, 300, n).clip(350, 2500).round()
df = pd.DataFrame({"area": area, "bhk": np.clip(area // 450, 1, 3).astype(int),
    "furnishing": rng.choice(["none", "semi", "full"], n, p=[.4, .4, .2]),
    "locality": rng.choice(["Baner", "Wakad", "Kothrud", "Hadapsar"], n)})
lift = df["locality"].map({"Baner": 1.25, "Kothrud": 1.15, "Wakad": 1, "Hadapsar": 0.9})
df["rent"] = (area * 18 * lift * rng.lognormal(0, .3, n)).round(-2)
df.loc[17, "area"] = df.loc[17, "area"] * 10     # a typo: an extra 0
a = df["area"]
q1, q3 = a.quantile([0.25, 0.75])
iqr = q3 - q1
lo, hi = q1 - 1.5 * iqr, q3 + 1.5 * iqr
print("IQR fence:", lo, "to", hi)
print(df[(a < lo) | (a > hi)])
z = (a - a.mean()) / a.std()                 # z-score
print("|z| > 3:", (z.abs() > 3).sum(), "flat, |z| =", round(z.abs().max(), 1))

Output:

IQR fence: 160.5 to 1772.5
       area  bhk furnishing  locality     rent
17   4080.0    1       none   Kothrud   8000.0
156  1826.0    3       full  Hadapsar  31900.0
|z| > 3: 1 flat, |z| = 8.7
Rule How Strength Weakness
IQR rule flag x < Q1 − 1.5·IQR or x > Q3 + 1.5·IQR robust: quartiles barely move when a few values are extreme flags real but large values too (row 156: a real 1826 sq ft flat)
z-score z = (x − mean) / std; flag |z| > 3 simple; good for roughly bell-shaped data the mean and std are pulled by outliers themselves, which can hide other outliers (masking)

A rule only flags candidates. A human decides: fix (a typo with a known correction), remove (junk), cap (real but extreme), or keep (real and important).

Capping (winsorising)

Row 17 is a typo with a known fix (408 sq ft). Rent has real but extreme values; capping (also called winsorising) pulls them in to a limit instead of deleting the row:

import numpy as np, pandas as pd
rng = np.random.default_rng(13); n = 300     # episode 13's flats
area = rng.normal(950, 300, n).clip(350, 2500).round()
df = pd.DataFrame({"area": area, "bhk": np.clip(area // 450, 1, 3).astype(int),
    "furnishing": rng.choice(["none", "semi", "full"], n, p=[.4, .4, .2]),
    "locality": rng.choice(["Baner", "Wakad", "Kothrud", "Hadapsar"], n)})
lift = df["locality"].map({"Baner": 1.25, "Kothrud": 1.15, "Wakad": 1, "Hadapsar": 0.9})
df["rent"] = (area * 18 * lift * rng.lognormal(0, .3, n)).round(-2)
df.loc[17, "area"] = df.loc[17, "area"] * 10     # a typo: an extra 0
df.loc[17, "area"] = 408                     # fix the typo
r = df["rent"]
lo, hi = r.quantile([0.01, 0.99]).round()
capped = r.clip(lo, hi)                      # winsorise at 1% and 99%
print("1st and 99th percentile:", lo, hi)
print("max before:", r.max(), " after:", capped.max())
print("rows changed:", (capped != r).sum())

Output:

1st and 99th percentile: 4798.0 44908.0
max before: 52400.0  after: 44908.0
rows changed: 6

clip(lo, hi) replaces anything below lo with lo and anything above hi with hi. The rows stay; only 6 extreme values changed.

Important: medians for imputing, percentiles for capping, any number learned from data must be computed on the training set only. Why is the next episode.

Common mistakes

Mistake Exact error / symptom Why Fix
df["rent"].mean() on "18,500" TypeError: Cannot perform reduction 'mean' with string dtype numbers stored as text str.replace + pd.to_numeric
pd.to_numeric on ask without errors= ValueError: Unable to parse string "ask" unreadable text errors="coerce" → NaN
'baner ' vs 'Baner' one place split into 3 groups inconsistent categories .str.strip().str.title()
fitting with NaN ValueError: Input X contains NaN. missing values impute (or drop if few and MCAR)
dropna on MNAR data mean rent 19,825 → 21,107 removes a kind of row impute + missing indicator
mean imputation with outliers fills 72,750 the mean is pulled median imputation
deleting every flagged outlier real flats lost (row 156) outlier ≠ error investigate; fix, cap or keep
learning fill values on all data test data leaks into training contamination fit on train only (Episodes 15 to 17)

The ValueError: Unable to parse string "ask" line, for reference (pandas 3.0.5):

import pandas as pd
pd.to_numeric(pd.Series(["850", "ask"]))
ValueError: Unable to parse string "ask" at position 1

Mini project: a reusable cleaning function

import io, numpy as np, pandas as pd
from sklearn.impute import SimpleImputer
# Mini project: a reusable cleaning function for scraped listings
raw = """area,bhk,furnishing,locality,rent
1200,2,semi,Baner,"18,500"
850,1,none,baner ,12000
640,1,,Wakad,"9,800"
ask,2,full,Kothrud,15000
1100,2,semi,BANER,"21,000"
850,1,none,baner ,12000
980,2,semi,Kothrud,"16,200"
6400,1,none,Wakad,"8,900"
"""
def clean(df):
    df = df.copy()
    df["rent"] = pd.to_numeric(df["rent"].astype(str).str.replace(",", ""))
    df["area"] = pd.to_numeric(df["area"], errors="coerce")
    df["locality"] = df["locality"].str.strip().str.title()
    df = df.drop_duplicates()
    bad = (df["area"] / df["bhk"]) > 1500                # > 1500 sq ft per bedroom
    df.loc[bad, "area"] = df.loc[bad, "area"] / 10        # extra-zero typos
    df["area_missing"] = df["area"].isna().astype(int)
    df["area"] = df["area"].fillna(df["area"].median())
    df["furnishing"] = df["furnishing"].fillna(df["furnishing"].mode()[0])
    return df

print(clean(pd.read_csv(io.StringIO(raw))))

Output:

     area  bhk furnishing locality   rent  area_missing
0  1200.0    2       semi    Baner  18500             0
1   850.0    1       none    Baner  12000             0
2   640.0    1       semi    Wakad   9800             0
3   915.0    2       full  Kothrud  15000             1
4  1100.0    2       semi    Baner  21000             0
6   980.0    2       semi  Kothrud  16200             0
7   640.0    1       none    Wakad   8900             0

It fixes types, categories, duplicates, an extra-zero typo (row 7: 6400 sq ft for 1 bedroom → 640), imputes area with the median and furnishing with the mode, and keeps a missing indicator.

Practice

  1. Clean "₹ 12,000"-style text into numbers.
  2. Impute furnishing with SimpleImputer(strategy="most_frequent").
Solutions 1 and 2
import pandas as pd
# Homework 1: clean "₹ 12,000"-style text into numbers
s = pd.Series(["₹ 12,000", "₹9,500", "15000", "ask"])
num = pd.to_numeric(s.str.replace("₹", "").str.replace(",", "").str.strip(), errors="coerce")
print(num.tolist())

Output:

[12000.0, 9500.0, 15000.0, nan]
import numpy as np, pandas as pd
from sklearn.impute import SimpleImputer
# Homework 2: impute a categorical column with the mode
furn = pd.DataFrame({"furnishing": ["semi", "none", np.nan, "semi", "full", np.nan]})
imp = SimpleImputer(strategy="most_frequent")
print(imp.fit_transform(furn).ravel().tolist())

Output:

['semi', 'none', 'semi', 'semi', 'full', 'semi']
  1. Try KNNImputer with n_neighbors = 1, 5, 20 and 100.
  2. Cap area at the 1st and 99th percentile.
Solutions 3 and 4
import numpy as np, pandas as pd
from sklearn.impute import KNNImputer
# Homework 3: how k changes the KNN imputer
rng = np.random.default_rng(14); n = 300
area = rng.normal(950, 300, n).clip(350, 2500).round()
df = pd.DataFrame({"area": area,
                   "bhk": np.clip(area // 450, 1, 3).astype(int),
                   "rent": (area * 20 * rng.lognormal(0, .25, n)).round(-2)})
true_area = df["area"].copy()
hide = rng.random(n) < np.where(area < 800, .6, .05)
df.loc[hide, "area"] = np.nan
miss = df["area"].isna()
for k in [1, 5, 20, 100]:
    filled = KNNImputer(n_neighbors=k).fit_transform(df)[:, 0]
    print("k =", k, " avg error", round(np.abs(filled[miss] - true_area[miss]).mean()))

Output:

k = 1  avg error 200
k = 5  avg error 186
k = 20  avg error 189
k = 100  avg error 256

k = 1 copies a single neighbour (noisy); very large k approaches the overall mean (error 256). A middle value works best.

import numpy as np, pandas as pd
rng = np.random.default_rng(13); n = 300          # 300 made-up flat listings
area = rng.normal(950, 300, n).clip(350, 2500).round()
df = pd.DataFrame({"area": area, "bhk": np.clip(area // 450, 1, 3).astype(int),
    "furnishing": rng.choice(["none", "semi", "full"], n, p=[.4, .4, .2]),
    "locality": rng.choice(["Baner", "Wakad", "Kothrud", "Hadapsar"], n)})
lift = df.locality.map({"Baner": 1.25, "Kothrud": 1.15, "Wakad": 1, "Hadapsar": .9})
df["rent"] = (area * 18 * lift * rng.lognormal(0, .3, n)).round(-2)
df.loc[17, "area"] = df.loc[17, "area"] * 10     # one typo: an extra zero
# Homework 4: cap area at the 1st and 99th percentile
a = df["area"]
lo, hi = a.quantile([0.01, 0.99]).round()
print("limits:", lo, hi)
print("max before:", a.max(), " after:", a.clip(lo, hi).max())

Output:

limits: 350.0 1727.0
max before: 4080.0  after: 1727.0

Quick quiz

import pandas as pd

s = pd.Series([4, None, 6, None, 50])
print(s.fillna(s.median()).tolist())

A) [4.0, 6.0, 6.0, 6.0, 50.0] B) [4.0, 20.0, 6.0, 20.0, 50.0] C) [4.0, 5.0, 6.0, 5.0, 50.0] D) [4.0, 0.0, 6.0, 0.0, 50.0]

Answer

A. median() skips missing values: the middle of 4, 6, 50 is 6, so both blanks become 6.0. B is what the mean (20.0) would give, pulled up by the 50.

Interview questions

  1. Explain MCAR, MAR and MNAR with an example each. MCAR: random loss; MAR: explained by observed columns; MNAR: depends on the missing value itself (small flats hiding area, high earners hiding income).
  2. When is dropping rows with missing values acceptable? When few rows are missing and the data is MCAR; otherwise you lose data and add bias.
  3. Mean vs median imputation? Median is robust to skew and outliers; mean is fine for symmetric data. Both shrink the variance and give everyone the same value.
  4. How does a KNN imputer work, and what does it need? Fills a blank with the average of the k most similar rows on the known columns; needs scaled features and is slower on big data.
  5. What is a missing-indicator feature and why use it? A 0/1 column for "was missing"; it lets the model use the fact of missingness (vital for MNAR).
  6. IQR rule vs z-score for outliers? IQR is robust to extreme values; z-score assumes a roughly normal shape and suffers from masking.
  7. What is winsorising? Capping values at chosen percentiles instead of deleting rows.
  8. Why must imputation and capping statistics come from the training set only? Computing them on all data lets test information into training (leakage), making test scores optimistic.

Glossary

Term Meaning Example
Data cleaning Fixing types, categories, duplicates, missing values and outliers today's episode
io.StringIO Treats a string like a file pd.read_csv(io.StringIO(raw))
Reduction An operation turning a column into one value mean, sum, max
Fixing types (type casting) Converting a column to the right type "18,500" → 18500
pd.to_numeric Converts text to numbers pd.to_numeric(s)
errors="coerce" Unreadable values become NaN instead of an error ask → NaN
NaN "Not a Number": the marker for a missing value empty area
Missing value A cell with no recorded value 57 areas
Inconsistent categories One category written several ways Baner / baner / BANER
str.strip() / str.title() Remove edge spaces / Capitalise Each Word 'baner ' → 'Baner'
Duplicate A row identical to another the same flat posted twice
drop_duplicates() Removes duplicate rows 7 → 6 rows
isna() True where a value is missing df.isna().sum()
MCAR Missing completely at random rain-soaked sheets
MAR Missing at random given observed columns the late school bus
MNAR Missing not at random: depends on the hidden value small flats hide area
dropna() Removes rows with any missing value 300 → 243 rows
Imputation Filling missing values with estimates median fill
Mean imputation Fill with the column mean 72,750 (pulled by a villa)
Median imputation Fill with the column median 14,500
Mode imputation Fill with the most frequent value (categories) semi
SimpleImputer scikit-learn imputer: mean, median, most_frequent, constant SimpleImputer(strategy="median")
fit_transform Learn from the data, then transform it, in one call imp.fit_transform(df)
k nearest neighbours (KNN) The k rows most similar to a given row 5 similar flats
KNNImputer Fills a blank with the neighbours' average error 186 vs 377
Missing-indicator feature 0/1 column: was this value missing? area_missing
add_indicator=True SimpleImputer adds the indicator column missingindicator_area
Outlier A value far from the rest row 17
IQR rule Flag values beyond Q1 − 1.5·IQR or Q3 + 1.5·IQR fence 160.5 to 1772.5
z-score (x − mean) / std: distance in standard deviations |z| = 8.7
Robust Barely affected by extreme values median, IQR
Masking Outliers inflating the std so others look normal z-score weakness
Capping (winsorising) Pulling extreme values in to percentile limits rent capped at 44,908
Percentile Value below which a given % of data lies 99th percentile
clip(lo, hi) Replace values outside [lo, hi] with the limits r.clip(4798, 44908)

Summary

  • Wrong types: str.replace + pd.to_numeric(errors="coerce"). Categories: strip + one case. Duplicates: drop_duplicates.
  • Missing values are MCAR, MAR or MNAR; why they're missing decides the fix. Our MNAR blanks made the visible average 1035.7 instead of 975.4.
  • Models reject NaN; dropping MNAR rows biases the data (mean rent 19,825 → 21,107). Impute instead: mean, median (skew/outliers), mode (categories), KNN (uses other columns, halved the error), plus a missing indicator.
  • Outliers: flag with the IQR rule or z-scores, then fix, remove, cap (winsorise) or keep.
  • Every statistic used for cleaning must be learned from the training set only.

Next episode: Train, validation and test splits: holdout, generalisation, the i.i.d. assumption, stratified, group-aware and time-based splits, data leakage, target leakage and train-test contamination.