Data Cleaning Exercises

How to Detect Outliers: IQR, Z-Scores and Domain Rules Compared

Compare IQR fences, z-scores, modified z-scores and domain rules on one dataset in DuckDB SQL and pandas, and learn which outliers each method misses.

By Tnas · Published · 9 min read

Scatter of data points with a dashed cut-off line and one circled point far above it, illustrating outlier detection

To detect outliers, use the IQR rule (flag values below Q1 − 1.5 × IQR or above Q3 + 1.5 × IQR) or the modified z-score as your statistical screen, and write domain rules for values that are impossible no matter what the data looks like. The classic z-score with a cut-off of 3 is the least reliable of the common methods: the extreme value inflates the standard deviation it is measured against, and in a sample of 10 rows no value can reach a z-score of 3 at all. Whatever method you use, flag the rows with a reason instead of deleting them. The worked example below runs all four methods on the same ten rows in DuckDB SQL and pandas.

Key Takeaways

  • IQR fences and the modified z-score use the median and quartiles, so a single extreme value can't hide itself by distorting them.
  • The ordinary z-score uses the mean and standard deviation, which the outlier itself inflates. In a sample of n rows, no z-score can exceed (n − 1)/√n, which is about 2.85 when n = 10.
  • Domain rules (no negative durations, no delivery over 60 days) catch impossible values that statistics may rate as normal, and they give each flag a readable reason.
  • A statistical outlier is a question, not a verdict. Flag it, keep the row and decide what to do with a reason attached.

The four methods at a glance

All four methods answer the same question, "is this value unusual enough to look at?", but they measure "unusual" differently.

Method Rule Centre and spread Strength Weakness
Z-score |x − mean| / sd > 3 Mean, standard deviation Simple, familiar The outlier inflates the sd; can't flag anything in small samples
IQR fences (Tukey) x < Q1 − 1.5·IQR or x > Q3 + 1.5·IQR Quartiles Robust, no normality needed Flags many points in skewed data
Modified z-score |0.6745 · (x − median) / MAD| > 3.5 Median, median absolute deviation Robust, comparable across columns MAD of 0 when most values are identical
Domain rule e.g. delivery_days < 0 or > 60 None, a written limit Catches impossible values, gives a reason Someone has to know and write the limit

The 1.5 × IQR "inner fence" and 3 × IQR "outer fence" come from the box plot convention documented in the NIST/SEMATECH e-Handbook of Statistical Methods: a point beyond an inner fence is a mild outlier, a point beyond an outer fence an extreme one. The modified z-score and its 3.5 threshold are the recommendation of Iglewicz and Hoaglin, also summarised by NIST's page on detecting outliers.

The example data

Ten delivery times in days. Most are between 2 and 6. Order 109 has −1, which can't happen. Order 110 has 250, which might be a typo or a parcel lost for eight months.

CREATE TABLE deliveries AS SELECT * FROM (VALUES
 (101, 2), (102, 3), (103, 3), (104, 4), (105, 4),
 (106, 5), (107, 5), (108, 6), (109, -1), (110, 250)
) AS t(order_id, delivery_days);

The summary statistics already show the problem with mean-based methods. The median is 4 and the quartiles are 3 and 5, but the mean is 28.1 and the standard deviation is 77.99, both dragged up by a single row.

Running all four methods in DuckDB SQL

DuckDB has the functions every method needs: avg and stddev_samp for z-scores, quantile_cont for quartiles, and mad for the median absolute deviation. One query computes the statistics once and scores every row:

WITH stats AS (
  SELECT avg(delivery_days) AS mean_d,
         stddev_samp(delivery_days) AS sd_d,
         quantile_cont(delivery_days, 0.25) AS q1,
         quantile_cont(delivery_days, 0.75) AS q3,
         median(delivery_days) AS med,
         mad(delivery_days) AS mad_d
  FROM deliveries
)
SELECT d.order_id, d.delivery_days,
       round((d.delivery_days - mean_d) / sd_d, 2) AS z,
       d.delivery_days < q1 - 1.5 * (q3 - q1)
         OR d.delivery_days > q3 + 1.5 * (q3 - q1) AS iqr_flag,
       round(0.6745 * (d.delivery_days - med) / mad_d, 2) AS modified_z,
       CASE WHEN d.delivery_days < 0 THEN 'negative duration'
            WHEN d.delivery_days > 60 THEN 'over 60-day limit' END AS rule_reason
FROM deliveries d CROSS JOIN stats
ORDER BY d.order_id;

┌──────────┬───────────────┬────────┬──────────┬────────────┬───────────────────┐
│ order_id │ delivery_days │   z    │ iqr_flag │ modified_z │    rule_reason    │
├──────────┼───────────────┼────────┼──────────┼────────────┼───────────────────┤
│      101 │             2 │  -0.33 │ false    │      -1.35 │ NULL              │
│      102 │             3 │  -0.32 │ false    │      -0.67 │ NULL              │
│      103 │             3 │  -0.32 │ false    │      -0.67 │ NULL              │
│      104 │             4 │  -0.31 │ false    │        0.0 │ NULL              │
│      105 │             4 │  -0.31 │ false    │        0.0 │ NULL              │
│      106 │             5 │   -0.3 │ false    │       0.67 │ NULL              │
│      107 │             5 │   -0.3 │ false    │       0.67 │ NULL              │
│      108 │             6 │  -0.28 │ false    │       1.35 │ NULL              │
│      109 │            -1 │  -0.37 │ true     │      -3.37 │ negative duration │
│      110 │           250 │   2.85 │ true     │     165.93 │ over 60-day limit │
└──────────┴───────────────┴────────┴──────────┴────────────┴───────────────────┘

What each method caught

  • Z-score (> 3): nothing. Order 110 scores 2.85. It is the most extreme value by far, yet it misses the cut-off.
  • IQR fences: both. The fences are 0 and 8 (3 − 1.5 × 2 and 5 + 1.5 × 2), so −1 and 250 are both outside.
  • Modified z-score (> 3.5): only 250. Order 109 scores −3.37, just inside the threshold.
  • Domain rules: both, with reasons. −1 is a "negative duration" and 250 is "over 60-day limit".
lower fence 0 upper fence 8 Q1 = 3 to Q3 = 5 −1 2 6 250 (axis broken)
IQR fences for the ten delivery times. The axis is broken before 250 so the cluster stays readable.

Why the z-score failed

The z-score is the distance from the mean in units of standard deviation. Both of those are pulled toward the outlier. With 250 in the data, the standard deviation jumps from about 2 to 77.99, so 250 ends up only 2.85 standard deviations from a mean of 28.1.

There is also a hard ceiling. NIST notes that z-scores "can be misleading (particularly for small sample sizes)" because the largest possible z-score in a sample of n values is (n − 1)/√n (NIST/SEMATECH e-Handbook). For n = 10 that is 2.846, and order 110 scored 2.85 (2.845 unrounded): almost as extreme as a value in ten rows can be, and it still doesn't reach 3. A z-score cut-off of 3 needs at least 11 rows to flag anything.

Z-scores also assume a roughly symmetric, bell-shaped distribution. Delivery times, order values and session lengths are usually right-skewed, so the mean and standard deviation describe them poorly even without outliers. The robust statistics lesson covers why the median and MAD hold up where the mean doesn't.

Why domain rules still matter

Statistics can only say "this is far from the others". They can't say "this is impossible". The modified z-score rated −1 as within range because the data is tightly clustered and −1 is only 5 days from the median. A delivery that arrives one day before it was shipped is still wrong.

Domain rules come from the business, a data contract or common sense:

  • Durations, quantities and ages can't be negative.
  • A percentage can't exceed 100.
  • A delivery promise of 60 days is the longest the company offers.
  • A date of birth after the account creation date is an error.

Each rule gives the flag a reason a person can act on, which "modified z-score 165.93" doesn't. The validation rules guide shows how to turn these limits into an exception report, and the validate ranges with ordered reasons exercise practises it.

The same checks in pandas

The pandas version computes the same statistics with quantile, median and std. pandas' std() is the sample standard deviation by default, matching DuckDB's stddev_samp:

import pandas as pd

df = pd.DataFrame({
    "order_id": range(101, 111),
    "delivery_days": [2, 3, 3, 4, 4, 5, 5, 6, -1, 250],
})
s = df["delivery_days"]

q1, q3 = s.quantile(0.25), s.quantile(0.75)
iqr = q3 - q1
mad = (s - s.median()).abs().median()

df["z"] = ((s - s.mean()) / s.std()).round(2)
df["iqr_flag"] = (s < q1 - 1.5 * iqr) | (s > q3 + 1.5 * iqr)
df["modified_z"] = (0.6745 * (s - s.median()) / mad).round(2)
df["rule_reason"] = None
df.loc[s < 0, "rule_reason"] = "negative duration"
df.loc[s > 60, "rule_reason"] = "over 60-day limit"
print(df)

The output matches the SQL result row for row (tested with DuckDB 1.5.1 and pandas 3.0.2). pandas' default quantile uses linear interpolation, the same as DuckDB's quantile_cont. If you use quantile_disc in SQL instead, the quartiles can differ on small samples, so pick one and write it down.

Which method should you use?

Use them in layers rather than choosing one:

  1. Domain rules first. They catch impossible values and give the clearest reasons. Values that break a rule are usually errors to fix or quarantine.
  2. IQR fences or the modified z-score second. They find values that are possible but unusual. These deserve a look, not automatic removal.
  3. Z-scores only for large, roughly symmetric data, and preferably computed after removing values that broke a domain rule.

Two refinements make the statistical screen more useful:

  • Compute fences per group. A 10-day delivery is normal for international freight and unusual for same-city courier. Run the statistics per carrier or region with GROUP BY or a window function.
  • Transform skewed data first. On right-skewed measures such as revenue, IQR fences on the raw values flag a long tail of legitimate large orders. Computing them on the log of the value is often more useful.

If the data feeds a model, compute the fences on training rows only and reuse them on test rows (see data cleaning for machine learning), as the guide to avoiding leakage in data preparation explains.

Flag, don't delete

Detection is the easy part. The decision is what to do next, and deleting is rarely right. Order 110 might be a typo for 25, a lost parcel that matters to the customer service team, or a real event that a delivery-time model should know about. The outliers: flag, don't delete guide covers the options, and the lesson on outlier or error helps you tell the two apart.

A good outlier output keeps every row and adds two columns: a boolean flag and a reason. The flag bulk orders without removing them exercise asks for exactly that.

Frequently Asked Questions

What is the 1.5 IQR rule for outliers?

Compute the first quartile (Q1), the third quartile (Q3) and the interquartile range IQR = Q3 − Q1. A value below Q1 − 1.5 × IQR or above Q3 + 1.5 × IQR is a potential outlier. Values beyond 3 × IQR from the quartiles are called extreme outliers.

Is IQR or z-score better for outlier detection?

IQR is usually better for data cleaning because quartiles aren't distorted by the outliers themselves and it doesn't assume a normal distribution. The z-score works best on large, roughly symmetric samples, and in a sample of 10 rows it can't exceed about 2.85, so a cut-off of 3 flags nothing.

What is a modified z-score?

It replaces the mean with the median and the standard deviation with the median absolute deviation (MAD): 0.6745 × (x − median) / MAD. Iglewicz and Hoaglin recommend treating absolute values above 3.5 as potential outliers.

Should I remove outliers from my data?

Usually not. Flag them with a reason and keep the row. Remove or fix a value only when a domain rule shows it is an error, and record what you changed.

Sources