SyntaxPilot
Courses/Data Analytics with Python/Lesson 23

Data Analytics with Python · Lesson 23 of 90 · 11 min

Cleaning 5: outliers, validation and sanity checks

outlierIQR rulez-score ruledomain rulescappingwinsorisingclipdata validationassert statementsschema validation (pandera mention)range checksreferential checksreconciliation with source totals

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

  1. A typo you can trace? Fix it (2000 → 20) and record it in your cleaning log (episode 18).
  2. Impossible and unfixable? Drop the row, or set the value to NaN, and log it.
  3. Real but extreme (a wedding order)? Keep it; report the median beside the mean.
  4. 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

  1. 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.
  2. 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.
  3. 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.
  4. Capping vs winsorising? Capping clips values at chosen limits; winsorising clips at chosen percentiles of the data.
  5. What is a referential check? Verifying that foreign values (item codes, customer ids) exist in the table they refer to; failures are orphans.
  6. Give an example of a cross-field check. amount == qty × price, delivered >= ordered, end_date >= start_date.
  7. 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.
  8. 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.
  9. What is schema validation? Checking a table against declared column rules (type, range, uniqueness, allowed values), for example with pandera.
  10. 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.