These are the notes for Data Analytics with Python, episode 23, the last cleaning episode. A table can be perfectly tidy and still be wrong. Here you find outliers with the z-score rule and the IQR rule, use domain rules and range checks, decide whether to fix, drop, keep or cap (clip, winsorising), and then validate data automatically: assert statements, referential checks, cross-field checks, reconciliation with source totals, and schema validation with the pandera library. Every term is defined the first time it appears and collected in the Glossary. Every program below was run for real (Python 3.13, pandas 3.0.5, pandera 0.35.0); outputs are unedited.
The data: ten orders and a 2,000-rupee cup of chai
Ramesh sells chai at 20 rupees a cup (CH), samosa at 15 (SM) and coffee at 30 (CF). A typical order is 20 to 80 rupees. The weekly report says the average order is 232 rupees: more than ten cups each. Something is wrong.
import sys; sys.tracebacklimit = 0
import pandas as pd, io
raw = """order_id,item,qty,amount
1,CH,2,40
2,CH,1,20
3,SM,3,45
4,CF,1,30
5,CH,4,80
6,SM,2,30
7,CH,1,2000
8,CJ,2,40
9,CH,-1,-20
10,CF,2,60
"""
orders = pd.read_csv(io.StringIO(raw))
a = orders["amount"]
What an outlier is
An outlier is a value far away from the rest of the data. Some outliers are mistakes (a typo), some are real and important (a wedding order of 200 cups). Either way: find them first, then decide.
print("mean:", a.mean(), " median:", a.median())
print(a.sort_values().tolist())
mean: 232.5 median: 40.0
[-20, 20, 30, 30, 40, 40, 45, 60, 80, 2000]
The mean adds everything and divides by the count, so one huge value drags it up. The median is the middle value of the sorted data and ignores how big the biggest value is. A big gap between mean and median is a first warning sign. With 10 rows you can see -20 and 2000 by eye; with ten lakh rows you need a rule for "how far is too far".
The z-score rule, and why it fails here
The standard deviation (std) is the typical distance of values from the mean. The z-score of a value is how many standard deviations it is from the mean:
z = (value − mean) / std
The z-score rule: a value with z above 3 or below −3 is an outlier.
z = (a - a.mean()) / a.std()
print(z.round(2).tolist())
print(orders[z.abs() > 3])
[-0.31, -0.34, -0.3, -0.33, -0.25, -0.33, 2.84, -0.31, -0.41, -0.28]
Empty DataFrame
Columns: [order_id, item, qty, amount]
Index: []
2000 gets z = 2.84 and nothing is flagged. The outlier is part of the mean and std it is measured with: it pulls the mean up and inflates the std, so it hides itself (this is called masking). Mathematically, with n rows no value can have |z| above (n − 1)/√n (sample std), which for n = 10 is 9/√10 ≈ 2.85. The z-score rule works on large, roughly bell-shaped data; on small or skewed data it fails.
The IQR rule
Real-life picture: line up 40 students by height. Ignore the shortest quarter and the tallest quarter: the rest is the middle half of the class. A seven-foot visitor joining the line barely moves the middle half.
- A quantile is the value below which a given fraction of the data lies;
quantile(0.25)is the value with 25% of the data below it. A percentile is the same idea in percent (the 25th percentile). - Quartiles cut sorted data into four quarters: Q1 = 25th percentile, Q3 = 75th percentile.
- The IQR (interquartile range) = Q3 − Q1, the width of the middle half: a ruler the extremes can't stretch.
- The IQR rule draws two fences: Q1 − 1.5 × IQR and Q3 + 1.5 × IQR. Values outside the fences are outliers. The 1.5 is a widely used convention (it's the rule behind box-plot whiskers), not a law of nature.
q1, q3 = a.quantile(0.25), a.quantile(0.75)
iqr = q3 - q1
low, high = q1 - 1.5 * iqr, q3 + 1.5 * iqr
print("Q1", q1, " Q3", q3, " IQR", iqr)
print("fences:", low, "to", high)
print(orders[(a < low) | (a > high)])
Q1 30.0 Q3 56.25 IQR 26.25
fences: -9.375 to 95.625
order_id item qty amount
6 7 CH 1 2000
8 9 CH -1 -20
Both strange orders are caught. But the lower fence is a negative amount: statistics doesn't know chai money can't be negative.
Domain rules and range checks
Domain knowledge is knowledge of the business; a domain rule is a rule built from it. Ramesh says: an order has 1 to 50 items and costs 10 to 1,000 rupees. Checking that values fall inside allowed limits is a range check; between(lo, hi) is True when lo ≤ value ≤ hi (both ends included by default).
ok_qty = orders["qty"].between(1, 50)
ok_amt = orders["amount"].between(10, 1000)
print(orders[~(ok_qty & ok_amt)])
order_id item qty amount
6 7 CH 1 2000
8 9 CH -1 -20
Same two rows, but now for reasons a person understands. Domain rules beat statistics whenever you have them: use statistics to find candidates, the business to decide.
Fix, drop, keep or cap
- A typo you can trace? Fix it (2000 → 20) and record it in your cleaning log (episode 18).
- Impossible and unfixable? Drop the row, or set the value to NaN, and log it.
- Real but extreme (a wedding order)? Keep it; report the median beside the mean.
- Need tame numbers for a chart or model? Cap it.
Never delete outliers just because they are inconvenient.
Capping means pulling extreme values in to a limit. In pandas that's clip(lower, upper). Winsorising is capping at percentiles (for example the 5th and 95th) instead of fixed numbers.
print(a.clip(lower=0, upper=500).tolist())
lo, hi = a.quantile(0.10), a.quantile(0.90)
print("10th and 90th percentiles:", round(lo, 1), round(hi, 1))
print(a.clip(lo, hi).round(1).tolist())
[40, 20, 45, 30, 80, 30, 500, 40, 0, 60]
10th and 90th percentiles: 16.0 272.0
[40.0, 20.0, 45.0, 30.0, 80.0, 30.0, 272.0, 40.0, 16.0, 60.0]
clip turned 2000 into 500 and −20 into 0. Winsorising at the 10th/90th percentiles turned them into 16 and 272. On ten rows that's crude, because the outlier itself drags the 90th percentile up; winsorising is meant for large data. (SciPy also has scipy.stats.mstats.winsorize.)
assert: checks that shout
Ramesh fixes the traced typo:
orders.loc[orders["order_id"] == 7, "amount"] = 20
Next week a new file arrives. A check should stop everything when something is wrong. Python's assert condition, "message" does nothing if the condition is True, and raises an AssertionError with your message if it's False.
assert (orders["qty"] > 0).all(), "qty must be positive"
print("all checks passed")
AssertionError: qty must be positive
The program stopped at the exact line where an assumption broke; "all checks passed" was never printed. Data validation is checking data against rules before you trust it. .all() is True only if every value is True. (Note: Python skips assert statements when run with python -O, so production pipelines often raise their own errors or use a validation library.)
Referential checks
A referential check verifies that a value in one table exists in another table it refers to. Rows that point to nothing are orphans.
menu = pd.DataFrame({"item": ["CH", "SM", "CF"],
"price": [20, 15, 30]})
orphans = orders[~orders["item"].isin(menu["item"])]
print(orphans)
order_id item qty amount
7 8 CJ 2 40
CJ is not on the menu (J pressed instead of H). Without this check, a later join with the menu (episode 26) would silently drop this row or give it no price.
Cross-field checks
A cross-field check tests that columns agree with each other: amount should equal quantity × price.
menu = {"CH": 20, "SM": 15, "CF": 30}
expected = orders["item"].map(menu) * orders["qty"]
print(orders[orders["amount"] != expected])
order_id item qty amount
7 8 CJ 2 40
CJ has no price, so its expected value is NaN, and NaN never equals anything. Order 9 passes: −1 × 20 = −20 is consistent but impossible. Each check catches a different kind of error, so you need several.
Reconciliation with the source
The app's weekly payout statement says 11 orders and 370 rupees. Reconciliation is comparing your counts and totals with the system the data came from.
source = {"orders": 11, "total": 370} # the app's payout statement
print("rows:", len(orders), "vs", source["orders"])
print("total:", orders["amount"].sum(), "vs", source["total"])
gap = source["total"] - orders["amount"].sum()
print("missing:", source["orders"] - len(orders), "order worth", gap)
rows: 10 vs 11
total: 345 vs 370
missing: 1 order worth 25
One 25-rupee order never reached the export. No rule on your own table can find a row that isn't there; only the source can. Reconcile counts and totals before you publish a number.
Schema validation (pandera)
A schema is a written list of rules every column must follow: its type, allowed range, uniqueness. Schema validation checks a whole table against it. pandera is an open-source Python library for declaring DataFrame schemas (pip install pandera):
import sys; sys.tracebacklimit = 0
import pandas as pd, io
import pandera.pandas as pa
raw = """order_id,item,qty,amount
1,CH,2,40
2,CH,1,20
8,CJ,2,40
9,CH,-1,-20
"""
orders = pd.read_csv(io.StringIO(raw))
schema = pa.DataFrameSchema({
"order_id": pa.Column(int, unique=True),
"item": pa.Column(str, pa.Check.isin(["CH", "SM", "CF"])),
"qty": pa.Column(int, pa.Check.in_range(1, 50)),
"amount": pa.Column(int, pa.Check.in_range(10, 1000)),
})
try:
schema.validate(orders, lazy=True)
except pa.errors.SchemaErrors as e:
print(e.failure_cases[["column", "check", "failure_case", "index"]])
column check failure_case index
0 item isin(['CH', 'SM', 'CF']) CJ 2
1 qty in_range(1, 50) -1 3
2 amount in_range(10, 1000) -20 3
lazy=True collects every failure instead of stopping at the first. Rules written once, run on every new file.
Mini project: check_orders()
def check_orders(df, menu, source):
price = df["item"].map(menu)
checks = {
"qty in 1-50": df["qty"].between(1, 50),
"item on menu": df["item"].isin(list(menu)),
"amount = qty x price": df["amount"] == price * df["qty"],
"unique order_id": ~df["order_id"].duplicated()}
for name, ok in checks.items():
bad = df.loc[~ok, "order_id"].tolist()
print(f"{name:22} {'PASS' if not bad else 'FAIL'} {bad}")
print(f"{'total matches source':22} {df['amount'].sum() == source['total']}")
check_orders(orders, {"CH": 20, "SM": 15, "CF": 30}, {"total": 370})
qty in 1-50 FAIL [9]
item on menu FAIL [8]
amount = qty x price FAIL [8]
unique order_id PASS []
total matches source False
(Run on the table after the order 7 fix.) {name:22} in an f-string pads the name to 22 characters so the report lines up. duplicated() is from episode 20.
Common mistakes
| Code / habit | Real error / symptom | Why | Fix |
|---|---|---|---|
z.abs() > 3 on 10 rows |
nothing flagged | the outlier inflates the std (masking) | IQR rule or domain rules |
| Trusting the mean of skewed data | average order 232.5 | one outlier drags the mean | check the median; investigate |
| Deleting everything outside the fences | real big orders lost | outliers can be real | investigate, then fix / keep / cap |
| Only statistical rules | lower fence −9.375 allows negative amounts | statistics ignores the business | add domain rules (between) |
assert (orders["qty"] > 0).all(), "qty must be positive" |
AssertionError: qty must be positive |
the data broke a rule (good!) | fix the data, never delete the assert |
amount == expected where expected has NaN |
row counted as a mismatch | NaN never equals anything | run the referential check first |
| Only checking your own table | missing rows never found | a missing row breaks no rule | reconcile with source totals |
Practice
All practice questions use this table of daily sales at 20 rupees a cup:
import pandas as pd, io
raw = """day,cups,rupees,staff
Mon,120,2400,2
Tue,135,2700,2
Wed,128,2560,2
Thu,0,0,2
Fri,141,2820,3
Sat,1650,3300,3
Sun,150,3000,3
"""
d = pd.read_csv(io.StringIO(raw))
c = d["cups"]
1. Find outliers in cups with the IQR rule.
Solution
q1, q3 = c.quantile(0.25), c.quantile(0.75); i = q3 - q1; lo, hi = q1 - 1.5*i, q3 + 1.5*i
print(q1, q3, i, lo, hi); print(d[(c < lo) | (c > hi)])
124.0 145.5 21.5 91.75 177.75
day cups rupees staff
3 Thu 0 0 2
5 Sat 1650 3300 3
Thursday's 0 may be real (the stall was closed): ask before "fixing" it.
2. Does the z-score rule catch Saturday?
Solution
z = (c - c.mean()) / c.std(); print(z.round(2).tolist())
[-0.36, -0.34, -0.35, -0.57, -0.33, 2.26, -0.31]
No: 2.26 < 3. With 7 rows the maximum possible |z| is 6/√7 ≈ 2.27.
3. Cross-field check: which days have rupees != cups × 20?
Solution
print(d[d["rupees"] != d["cups"] * 20])
day cups rupees staff
5 Sat 1650 3300 3
3300 rupees means 165 cups: an extra 0 was typed in cups. A traceable typo: fix it to 165.
4. Domain rule: a normal day sells 50 to 500 cups. Which days fail?
Solution
print(d.loc[~d["cups"].between(50, 500), "day"].tolist())
['Thu', 'Sat']
5. Cap cups at 500 and compare the median and mean.
Solution
print(c.clip(upper=500).median(), c.clip(upper=500).mean().round(1))
135.0 167.7
The median didn't move; the mean is still pulled by the capped 500 and the 0.
Quick quiz
s = pd.Series([10, 20, 30, 40, 1000])
c = s.clip(upper=50)
print(c.median(), c.mean())
A. 30 220 B. 30.0 30.0 C. 30.0 220.0 D. 50.0 30.0
Answer
B. 30.0 30.0. clip turns 1000 into 50: [10, 20, 30, 40, 50]. Median 30.0, mean 150 / 5 = 30.0. Without clipping the mean would be 220.
Interview questions
- What is an outlier, and should you always remove it? A value far from the rest. No: first find out whether it's an error (fix or drop) or real (keep, or cap for modelling), and document the decision.
- Explain the IQR rule. Q1 and Q3 are the 25th and 75th percentiles; IQR = Q3 − Q1; values below Q1 − 1.5·IQR or above Q3 + 1.5·IQR are outliers. Robust because quartiles aren't moved by extremes.
- When does the z-score rule fail? On small samples and skewed or heavy-tailed data: the outlier inflates the mean and std (masking); with n points |z| can't exceed (n−1)/√n.
- Capping vs winsorising? Capping clips values at chosen limits; winsorising clips at chosen percentiles of the data.
- What is a referential check? Verifying that foreign values (item codes, customer ids) exist in the table they refer to; failures are orphans.
- Give an example of a cross-field check.
amount == qty × price,delivered >= ordered,end_date >= start_date. - What is reconciliation and why is it important? Comparing your counts and totals with the source system; it's the only way to detect rows lost during export or loading.
- Why use
assert(or a validation library) in a data pipeline? To fail loudly at the step where an assumption breaks, instead of publishing wrong numbers. - What is schema validation? Checking a table against declared column rules (type, range, uniqueness, allowed values), for example with pandera.
- The mean is far above the median. What do you suspect? Right skew or high outliers; inspect the largest values.
Glossary
| Term | Meaning | Example |
|---|---|---|
| Outlier | A value far from the rest of the data | 2000 among 20-80 |
| Mean | Sum of values divided by their count | 232.5 |
| Median | The middle value of the sorted data | 40.0 |
| Standard deviation (std) | Typical distance of values from the mean | a.std() |
| z-score | (value − mean) / std: how many stds from the mean | 2.84 |
| z-score rule | Flag values with |z| > 3 | works on big bell-shaped data |
| Masking | An outlier inflating the statistics used to detect it | z of 2000 stays below 3 |
| Quantile | Value below which a fraction of the data lies | quantile(0.25) |
| Percentile | A quantile expressed in percent | 90th percentile |
| Quartile | Cut points splitting sorted data into quarters: Q1, Q2 (median), Q3 | Q1 = 30 |
| IQR (interquartile range) | Q3 − Q1, the width of the middle half | 26.25 |
| Fences | Q1 − 1.5·IQR and Q3 + 1.5·IQR | −9.375 … 95.625 |
| IQR rule | Values outside the fences are outliers | catches 2000 and −20 |
| Domain knowledge | Knowledge of the business or field | chai costs 20 |
| Domain rule | A rule built from domain knowledge | amount 10-1000 |
| Range check | Check that values fall within allowed limits | between(1, 50) |
between(lo, hi) |
True when lo ≤ value ≤ hi | qty.between(1, 50) |
| Capping | Pulling extreme values in to a limit | 2000 → 500 |
clip(lower, upper) |
pandas' capping method | a.clip(0, 500) |
| Winsorising | Capping at percentiles of the data | 10th / 90th |
| Data validation | Checking data against rules before trusting it | asserts, schemas |
assert |
Python statement: raise an error if a condition is False | assert ok.all(), "msg" |
AssertionError |
The error a failed assert raises | qty must be positive |
| Referential check | A value must exist in the table it refers to | item in menu |
| Orphan | A row that points to nothing | item CJ |
| Cross-field check | Columns must agree with each other | amount = qty × price |
| Reconciliation | Comparing your counts and totals with the source system | 345 vs 370 |
| Schema | Written rules every column must follow | type, range, unique |
| Schema validation | Checking a whole table against its schema | schema.validate(df) |
| pandera | Python library for DataFrame schemas and validation | pa.DataFrameSchema |
Summary
- An outlier is far from the rest; it drags the mean, not the median.
- The z-score rule (|z| > 3) needs large, bell-shaped data; on small data the outlier masks itself.
- The IQR rule uses quartiles the outlier can't stretch: fences at Q1 − 1.5·IQR and Q3 + 1.5·IQR.
- Domain rules and range checks (
between) beat statistics whenever you have them. - Then decide: fix, drop, keep or cap (
clip, winsorising), and log it. - Validate automatically:
assert, referential checks (isin), cross-field checks, reconciliation with the source, and schema validation (pandera). - Next episode: GroupBy 1, split-apply-combine and aggregation.