All lessons

Module 3: Cleaning data · Lesson 3.6 · about 20 min

Checking data: ranges, rules and outliers

Write checks that prove a table is right before you analyse it: allowed ranges, allowed values, keys that must match another table, totals that must reconcile, and outliers worth a second look.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 3 · about 20 minutes · uses Lessons 2.3 and 3.1. You’ll be able to write the checks that catch bad data before it reaches a report.

Write the rules your data must follow as code, so every run checks them and a broken file stops the analysis instead of fooling it.

In everyday terms: a pilot’s pre-flight checklist. Each item is quick and boring, and the one time a check fails it saves the flight.

1. Rules as masks

Every rule is a filter for the rows that break it:

Pythonorders_messy.csv, products.csv

A table like this, run every time a new file arrives, is a simple data quality report.

2. Assert the rules that must hold

assert condition, message stops with the message when the condition is False. Put them after cleaning:

Pythonorders.csv, products.csv, customers.csv

The last rule fails on purpose: it shows what a failed check looks like. Change 50 to 100 and run again.

3. Outliers: flag, don’t delete

Pythonorders.csv

These are real sales of expensive items, not errors. An outlier rule finds rows to look at; whether to cap, keep or remove them is a decision you state (Statistics Lesson 1.4).

4. Reconcile totals

Compare your total with a number from the source system (a finance report, the till summary). If revenue from your cleaned table differs, find out why before going further: dropped rows, double-counted lines after a join, or a filter that was too strict.

Mask rule
df['qty'] <= 0 flags rows that break "quantity is positive".
assert cond, msg
Stop with a message if a rule that must hold does not.
Outlier flag
Mark unusual values for review rather than deleting them.
Reconcile
Check totals against a trusted source (the finance figure).
Physical meaning
A validation rule is a promise the data makes. When a promise breaks, everything built on it is suspect, so the code should shout, not carry on quietly.

“The data came from our own system, so it is correct.” Exports change, joins duplicate, test orders slip in. Rules catch the change the day it happens, not the day a manager spots a strange number.

Watch it happen, one step at a time:

Rules. qty > 0; discount between 0 and 0.5; every store_id in stores.csv.
Check. bad = orders[(orders.qty <= 0) | ~orders.discount.between(0, 0.5)] → 0 rows.
Must hold. assert orders.store_id.isin(stores.store_id).all(), 'unknown store'
Reconcile. Total revenue against finance's figure: within 0.1 %.
Outliers. Lines over the 99.9th percentile are flagged, read, and kept: real large orders.

5. Your turn

Your turnorders_messy.csv, products.csv
Your turnorders_messy.csv
How it works

Principle: analysis can only be as right as its inputs. Validation turns what you believe about the data (“every order has a customer”) into checks that run every time. The cheapest time to find a problem is before anyone has seen a chart.

Kinds of rule: type (dates are dates), range (0 ≤ discount ≤ 0.5), allowed values (store in the store list), uniqueness (one row per key), reference (every key matches a lookup table), and reconciliation (totals match the source).

6. Practice

  1. Write a rule for “email must contain @”.
  2. What does orders['customer_id'].isin(customers['customer_id']).all() check?
  3. Why flag outliers rather than delete them?
Answers
  1. df['email'].str.contains('@', na=False); rows where it’s False break the rule.
  2. That every order’s customer exists in the customer table (no orphan orders).
  3. They may be real and important (big customers, expensive items); deleting them changes totals and hides information. Decide case by case and document it.

Try it on your own data: CSV profiler shows ranges, missing counts and odd values per column.

New words: validation, data quality report, assert, orphan key, outlier flag, reconciliation.

  • Express rules as masks; count and inspect violations.
  • assert stops the run when a must-hold rule breaks.
  • Flag outliers for review; do not delete real values.
  • Reconcile key totals with a trusted source.

Check yourself

1Which check catches order lines for products that do not exist?
2You find quantities of −1. What is the best first step?
3What is assert good for in a cleaning script?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. All datasets are made up. Python runs in your browser (the first run downloads it once, about 20 MB); nothing you type is sent anywhere.