esc
  • WorkflowsPages
  • Case studiesPages
  • Machine Learning FoundationsLearn
  • AI EngineeringLearn
  • AI Guardrails & Red TeamingLearn
  • UI/UXLearn
  • Design SystemsLearn
  • Production System DesignLearn
  • Book a callContact
  • Download ResumeContact
Let’s talk

I’ll build the agent for it.

Machine Learning Foundations / Beginner / 01.53

Data cleaning: fixing a messy table

Real tables have gaps, repeats, typos and impossible numbers. Cleaning fixes them in small, written-down steps, so the model learns from the data and not from the mistakes.

15 min readFree

Why we clean data

In the last lesson we met preprocessing, the work between raw data and a model. Cleaning is its first job. Think of washing vegetables before you cook: the recipe stays the same, the grit goes.

Google's Machine Learning Crash Course puts it plainly: 'your ML model is only as good as the data it trains on.' Wikipedia describes data cleaning as 'the process of identifying and correcting (or removing) corrupt, inaccurate, or irrelevant records from a dataset, table, or database.'

Key words

Example (row)
One item in a table, such as one customer. Google: 'You can imagine each row of the table as an example and each column as a potential feature or label.'
Feature and label
A feature is a column the model uses to guess, such as age. The label is the answer we want it to learn, such as 'will this customer renew?'.
Missing value
A cell with nothing in it. In a CSV file it is an empty gap between two commas.
Imputation
Filling a missing value with a careful guess. Google calls them 'well-reasoned guesses'.
Outlier
NIST: 'an observation that lies an abnormal distance from other values in a random sample from a population.'
Test set
Rows we hide from the model while it learns, then use at the end to see how well it really does.

Tip

Never clean your only copy. Keep the raw file as it arrived, and write every fix as a small script that makes a new, clean copy.

Where the mess comes from

  • People slip

    Google's examples: 'a person forgot to enter a value for a house's age' and 'someone typed an extra digit'.

  • Machines repeat

    Google: 'a server mistakenly uploaded the same log entries twice.'

  • Things break

    Google: 'a thermometer was left out in the sun', or a feature that is reliable 'except for that one day when the network kept crashing'.

Google adds that you 'can't purge your dataset of all noise', so aim for a table that is good enough, with every choice written down.

Here is the table we will clean. It is invented: twelve customers of a small online class, and we want to predict the last column, renewed.

Text
id,city,age,height,joined,renewed
1,Chennai,21,170 cm,2026-01-05,yes
2, chennai ,34,165 cm,12/01/2026,Yes
3,Chenai,29,1.70 m,2026-01-20,no
4,Mumbai,,158 cm,2026-02-03,yes
5,Mumbai,27,172 cm,03/02/2026,no
5,Mumbai,27,172 cm,03/02/2026,no
6,Delhi,-3,180 cm,2026-02-10,yes
7,Delhi,31,168 cm,2026-02-14,no
8,Mumbai,26,175 cm,2026-02-20,yes
8,Mumbai,26,175 cm,2026-02-20,no
9,Chennai,230,169 cm,2026-03-01,yes
10,Delhi,24,1.62 m,2026-03-02,no

Step zero: look before you fix

Never clean blind. First count, list and print. Google recommends automation: 'unit tests that define or rely on an external formal data schema can flag values that fall outside of a defined range.'

profile.py
import csv
from io import StringIO

RAW = """id,city,age,height,joined,renewed
1,Chennai,21,170 cm,2026-01-05,yes
2, chennai ,34,165 cm,12/01/2026,Yes
3,Chenai,29,1.70 m,2026-01-20,no
4,Mumbai,,158 cm,2026-02-03,yes
5,Mumbai,27,172 cm,03/02/2026,no
5,Mumbai,27,172 cm,03/02/2026,no
6,Delhi,-3,180 cm,2026-02-10,yes
7,Delhi,31,168 cm,2026-02-14,no
8,Mumbai,26,175 cm,2026-02-20,yes
8,Mumbai,26,175 cm,2026-02-20,no
9,Chennai,230,169 cm,2026-03-01,yes
10,Delhi,24,1.62 m,2026-03-02,no
"""

rows = list(csv.DictReader(StringIO(RAW)))
print("rows:", len(rows))
print("missing age:", sum(1 for r in rows if r["age"] == ""))
print("cities:", sorted({r["city"] for r in rows}))
print("labels:", sorted({r["renewed"] for r in rows}))
print("exact duplicates:", len(rows) - len({tuple(r.values()) for r in rows}))

Output

rows: 12
missing age: 1
cities: [' chennai ', 'Chenai', 'Chennai', 'Delhi', 'Mumbai']
labels: ['Yes', 'no', 'yes']
exact duplicates: 1

Already we learn a lot: one empty age, one exact repeat, three spellings of one city, and both 'Yes' and 'yes' in the label. To a computer each spelling is a different thing. Also, the csv module hands back every cell as text, so the age 21 arrives as '21'.

Missing values: drop, fill or flag

Google's advice is blunt: 'Don't train a model on incomplete examples.' Instead, 'fix or eliminate' them. There are three moves.

  1. 1

    1. Drop the row or the column

    Google: 'If the dataset contains enough complete examples to train a useful model, then consider deleting the incomplete examples.' scikit-learn names the price: discarding rows 'comes at the price of losing data which may be valuable (even though incomplete).'

  2. 2

    2. Fill with a typical value

    This is imputation. scikit-learn can use 'the statistics (mean, median or most frequent) of each column'. The mean is the usual average. The median is the middle value when sorted.

  3. 3

    3. Flag it

    Add a yes/no column that says 'this value was missing'. Google suggests 'an indicator column to signal imputed values to the model.' scikit-learn: 'preserving the information about which values had been missing can be informative.'

Which one? Google suggests: 'If you can't decide whether to delete or impute, consider building two datasets: one formed by deleting incomplete examples and the other by imputing. Then, determine which dataset trains the better model.'

Quick check

You fill an empty age with the column's median and add a 'was missing' column. Why add the second column?

Duplicates

A duplicate is the same row twice. It counts that customer double. Worse, copies can land on both sides of a split. Google's example is a spam model with 99% precision on both its training and test sets, because 'many of the examples in the test set are duplicates of examples in the training set.' The team 'neglected to scrub duplicate entries for the same spam email from your input database before splitting the data.' Google's rule: 'The only fair test of a model is against new examples, not duplicates.'

So remove duplicates before you split. An exact duplicate (every cell the same) is safe to drop: keep the first copy. A near duplicate shares an id but differs in a cell. Our id 8 appears twice, with labels 'yes' and 'no'. We cannot know which is right, so we drop both rows and note it in the log.

Messy text, types, units and dates

To a person, 'Chennai', ' chennai ' and 'Chenai' are one city. To a computer they are three. Wikipedia describes the repair as 'removing typographical errors or validating and correcting values against a known list of entities.' For a beginner, three moves: trim the spaces, make everything lower case, and fix known spellings with a small lookup table, a dictionary of 'wrong' to 'right' that you write and check yourself. Do the same for labels, or 'Yes' and 'yes' look like two answers.

Wrong types: a number stored as text cannot be added or compared. Convert it, and decide what to do when it will not convert. Our age becomes a whole number, and an empty cell becomes None, Python's word for 'nothing here'.

Units: our heights are '170 cm' in most rows and '1.70 m' in two. Mixed together, they look like 170 and 1.70. Pick one unit, convert the rest, and name the column height_cm.

Dates: '03/02/2026' means 3 February in some countries and 2 March in others. Our file mixes 2026-02-03 and 03/02/2026. We assume slash dates are day/month/year, because that keeps the rows in date order. It is our own assumption, and in a real project you would ask whoever made the file.

Impossible values and outliers

First, a domain check: use what you know about the world. An age cannot be negative, and 230 is not a human age. These are impossible, so we treat them as missing. The 0 to 120 range is our own choice.

Second, a statistical check for values that are possible but strange. Wikipedia: an outlier 'differs significantly from other observations.' NIST says the definition 'leaves it up to the analyst (or a consensus process) to decide what will be considered abnormal.' So several rules exist and none is law.

The IQR rule is from NIST. The lower quartile Q1 is the value a quarter of the way up the sorted list, and the upper quartile Q3 is three quarters up. The interquartile range, IQR, is Q3 minus Q1: the width of the middle half. NIST's inner fences are 'Q1 - 1.5*IQ' and 'Q3 + 1.5*IQ', and 'A point beyond an inner fence on either side is considered a mild outlier.'

The z-score rule is from Google: 'A Z-score is the number of standard deviations a value is from the mean.' The standard deviation measures how spread out the values are. Google's example clips Z-scores beyond 3, so we use 3 as the cut-off. We run both rules on the ages, with 230 left in.

outliers.py
from statistics import mean, stdev, quantiles

ages = [21, 34, 29, 27, 31, 26, 230, 24]

q1, _, q3 = quantiles(ages, n=4)
iqr = q3 - q1
low, high = q1 - 1.5 * iqr, q3 + 1.5 * iqr
print("Q1, Q3, IQR:", q1, q3, iqr)
print("fences:", low, high)
print("IQR flags:", [a for a in ages if a < low or a > high])

m, s = mean(ages), stdev(ages)
z = [round((a - m) / s, 2) for a in ages]
print("mean, stdev:", round(m, 1), round(s, 1))
print("z of 230:", z[ages.index(230)])
print("z-score flags (|z| > 3):", [a for a, zz in zip(ages, z) if abs(zz) > 3])

Output

Q1, Q3, IQR: 24.5 33.25 8.75
fences: 11.375 46.375
IQR flags: [230]
mean, stdev: 52.8 71.7
z of 230: 2.47
z-score flags (|z| > 3): []

The IQR rule flagged 230. The z-score rule found nothing. The 230 dragged the mean up to 52.8 and the standard deviation up to 71.7, so it hid itself by widening the 'normal' range. The quartiles barely moved. This is also why the median is often a steadier fill than the mean.

Flagging is half the job. Wikipedia says an outlier 'may be due to a variability in the measurement, an indication of novel data, or it may be the result of experimental error; the latter are sometimes excluded from the data set.' In a Google exercise on temperatures, rare hot days of 31 to 45 are real and might be clipped (capped at a limit), but readings of 1,000 'are mistakes, and should be deleted rather than clipped.' Google's warning: 'some outliers are actually important, so clip values carefully.'

Quick check

A temperature column mostly holds 15 to 30. A few real hot days read 31 to 45, and one cell reads 1,000. What is the best plan?

Label noise

A wrong label is worse than a wrong feature, because the label is the answer key. Google asks: 'How common are label errors? For example, if your data is labeled by humans, how often did your human raters make mistakes?'

A wrong label cannot be seen from the number alone. Check that no item has two answers (like our id 8), read a random sample of rows by hand, and ask how the labels were made. If you cannot tell, remove the row or hand it to a person.

Write down every change

Each fix is a decision, and decisions can be wrong. Wikipedia says a cleaning system 'balances fixing corrupted data with preserving the integrity of the original source production data'. Keeping the raw file and logging each change does both.

  • What changed

    The row id, the old value and the new one: 'id 3: height 1.70 m to 170 cm'.

  • Why

    The rule behind it, such as 'age must be 0 to 120'.

  • What you assumed

    Anything you guessed, such as 'slash dates are day/month/year'.

Cleaning without leaking the test set

Here is a trap. Suppose you fill missing ages with the median of the whole table before you set the test rows aside. That median was worked out partly from test rows, so the test is no longer a surprise. This is data leakage: test information sneaks into training.

scikit-learn's guide shows it with feature selection: 'As with any other type of preprocessing, feature selection should only use the training data. Including the test data in feature selection will optimistically bias your model.' It says the risk applies to 'almost all transformations', including SimpleImputer, its tool for filling missing values, and advises: 'it is good practice to split your data into train and test subsets first.'

So there are two kinds of fix. Fixed rules you wrote yourself, like trimming spaces or 'age must be 0 to 120', use no numbers from the data, so they are safe on every row. Fixes that learn a number from the data, like a median to fill gaps, must learn it from the training rows only, then reuse that same number on the test rows. Duplicates are the exception: remove them before the split.

leak.py
from statistics import median

train_ages = [21, 29, 31, 24]
test_ages = [34, 27]
print("median of training ages only:", median(train_ages))
print("median of train and test ages:", median(train_ages + test_ages))

Output

median of training ages only: 26.5
median of train and test ages: 28.0

The two medians differ. Using the second would let test rows shape a number the model trains with.

The whole table, cleaned

  1. 1

    1. Remove exact duplicates

    Keep the first copy of id 5.

  2. 2

    2. Drop conflicting rows

    Id 8 has two labels, so both rows go, and the log says so.

  3. 3

    3. Fix text, labels, units and dates

    Trim, lower-case, fix 'Chenai', turn metres into centimetres, write dates one way.

  4. 4

    4. Check types and impossible values

    Age becomes a number. Ages -3 and 230 become missing.

  5. 5

    5. Split first, then fill

    Set aside test rows, take the median age from the training rows only, fill the gaps and add a was-missing flag.

clean_table.py
import csv
from datetime import datetime
from io import StringIO
from statistics import median

RAW = """id,city,age,height,joined,renewed
1,Chennai,21,170 cm,2026-01-05,yes
2, chennai ,34,165 cm,12/01/2026,Yes
3,Chenai,29,1.70 m,2026-01-20,no
4,Mumbai,,158 cm,2026-02-03,yes
5,Mumbai,27,172 cm,03/02/2026,no
5,Mumbai,27,172 cm,03/02/2026,no
6,Delhi,-3,180 cm,2026-02-10,yes
7,Delhi,31,168 cm,2026-02-14,no
8,Mumbai,26,175 cm,2026-02-20,yes
8,Mumbai,26,175 cm,2026-02-20,no
9,Chennai,230,169 cm,2026-03-01,yes
10,Delhi,24,1.62 m,2026-03-02,no
"""

log = []
rows = list(csv.DictReader(StringIO(RAW)))

# 1. Exact duplicates
seen, unique = set(), []
for r in rows:
    key = tuple(r.values())
    if key in seen:
        log.append(f"id {r['id']}: dropped an exact duplicate row")
        continue
    seen.add(key)
    unique.append(r)

# 2. Same id, different labels
labels = {}
for r in unique:
    labels.setdefault(r["id"], set()).add(r["renewed"].lower())
conflicts = {i for i, s in labels.items() if len(s) > 1}
for i in sorted(conflicts, key=int):
    log.append(f"id {i}: two rows disagree on the label, dropped both")
unique = [r for r in unique if r["id"] not in conflicts]

SPELLING = {"chenai": "chennai"}
clean = []
for r in unique:
    c = {"id": int(r["id"])}
    # 3. Text
    city = r["city"].strip().lower()
    city = SPELLING.get(city, city)
    if city != r["city"].lower():
        log.append(f"id {r['id']}: city {r['city']!r} -> {city!r}")
    c["city"] = city
    c["renewed"] = r["renewed"].strip().lower()
    # 3. Units
    h = r["height"]
    if h.endswith(" m"):
        c["height_cm"] = round(float(h[:-2]) * 100)
        log.append(f"id {r['id']}: height {h!r} -> {c['height_cm']} cm")
    else:
        c["height_cm"] = int(h[:-3])
    # 3. Dates (assumed day/month/year)
    j = r["joined"]
    if "/" in j:
        c["joined"] = datetime.strptime(j, "%d/%m/%Y").date().isoformat()
        log.append(f"id {r['id']}: date {j!r} -> {c['joined']!r}")
    else:
        c["joined"] = j
    # 4. Types and impossible values
    age = int(r["age"]) if r["age"] else None
    if age is not None and not 0 <= age <= 120:
        log.append(f"id {r['id']}: age {age} is impossible, set to missing")
        age = None
    c["age"] = age
    clean.append(c)

# 5. Split first, then fill
train = [c for i, c in enumerate(clean) if i % 3 != 1]
test = [c for i, c in enumerate(clean) if i % 3 == 1]
fill = median(c["age"] for c in train if c["age"] is not None)
log.append(f"age: missing filled with the training median, {fill}")
for c in clean:
    c["age_was_missing"] = c["age"] is None
    if c["age"] is None:
        c["age"] = fill

print("\n".join(log))
print()
print("test ids:", [c["id"] for c in test])
print("id city renewed height_cm joined age age_was_missing")
for c in clean:
    print(*c.values())

Output

id 5: dropped an exact duplicate row
id 8: two rows disagree on the label, dropped both
id 2: city ' chennai ' -> 'chennai'
id 2: date '12/01/2026' -> '2026-01-12'
id 3: city 'Chenai' -> 'chennai'
id 3: height '1.70 m' -> 170 cm
id 5: date '03/02/2026' -> '2026-02-03'
id 6: age -3 is impossible, set to missing
id 9: age 230 is impossible, set to missing
id 10: height '1.62 m' -> 162 cm
age: missing filled with the training median, 26.5

test ids: [2, 5, 9]
id city renewed height_cm joined age age_was_missing
1 chennai yes 170 2026-01-05 21 False
2 chennai yes 165 2026-01-12 34 False
3 chennai no 170 2026-01-20 29 False
4 mumbai yes 158 2026-02-03 26.5 True
5 mumbai no 172 2026-02-03 27 False
6 delhi yes 180 2026-02-10 26.5 True
7 delhi no 168 2026-02-14 31 False
9 chennai yes 169 2026-03-01 26.5 True
10 delhi no 162 2026-03-02 24 False

Twelve rows became nine: one duplicate and the two conflicting rows of id 8 are gone, each with a reason in the log. Three ages were missing or impossible. Each was filled with 26.5, the training median, and marked in age_was_missing.

What to remember

Look first. Drop, fill or flag missing values, and when unsure, try both. Remove duplicates before you split. Make text, units and dates consistent. Use domain checks for impossible values and the IQR rule for strange ones, but remember that real rare values can be your most useful rows. Keep the raw file, write a change log, and learn every fill value from the training rows only.

Next: feature engineering. With a clean table in hand, we can build better columns from the ones we have.

Sources

How is this lesson?

Last updated on 16 November 2026

Learn with me

Every lesson is free. I add new ones as I learn. Have a question, or want help with your own work? Book a short call.