Mathew K AnalyticsLesson 20 · Pandas Full Course
Detect Outliers in Pandas (IQR Rule) and Validate Your Data | Pandas Course #20
In this lesson you'll be able to: Spot extreme values with summaries and box plots Flag outliers with the IQR rule, overall and within groups Choose between…
- CoursePandas Full Course
- Lesson20 of 65
- FormatJupyter notebook · 37 code cells
- Data2 datasets
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- titanic.csv preview58.9 KB
- taxis.csv preview849.0 KB
📓 Full notebook
Download .ipynbPandas 20: Handling Outliers and Data Validation#
In this lesson you'll be able to:
- Spot extreme values with summaries and box plots
- Flag outliers with the IQR rule, overall and within groups
- Choose between keeping, capping and removing outliers
- Write validation rules that catch impossible values Data: the Titanic passenger list and New York taxi trips
Warm-up: titles hidden in names#
Last lesson we cleaned text with .str. A quick reminder on Titanic.
import pandas as pd
import numpy as np
df = pd.read_csv("data/pandas/titanic.csv")
titles = df["Name"].str.extract(r",\s*([^.]+)\.")[0]
titles.value_counts().head()
Spot the extremes before you fix anything#
Summaries and a box plot show which values sit far from the rest.
df[["Age", "Fare"]].describe().round(2)
df["Fare"].sort_values(ascending=False).head(6)
import matplotlib.pyplot as plt
import seaborn as sns
fig, ax = plt.subplots(figsize=(8, 4))
sns.boxplot(data=df, x="Pclass", y="Fare", ax=ax)
ax.set_title("Titanic fares by passenger class")
ax.set_xlabel("Passenger class")
ax.set_ylabel("Fare (pounds)")
plt.show()
Quick check: is the 512 pound fare a typing mistake, or a real ticket?
top = df.loc[df["Fare"] > 500, ["Name", "Pclass", "Ticket", "Fare"]]
top
free = df.loc[df["Fare"] == 0, ["Name", "Pclass", "Ticket"]]
print(len(free), "passengers paid nothing")
free.head()
Flag outliers with the IQR rule#
Anything more than 1.5 IQRs outside the middle half gets flagged.
q1 = df["Fare"].quantile(0.25)
q3 = df["Fare"].quantile(0.75)
iqr = q3 - q1
print("Q1:", q1, " Q3:", q3, " IQR:", round(iqr, 2))
lower = q1 - 1.5 * iqr
upper = q3 + 1.5 * iqr
print("Fences:", round(lower, 2), "to", round(upper, 2))
Common mistake: joining two conditions with
or.
df[(df["Fare"] < lower) or (df["Fare"] > upper)]
is_out = (df["Fare"] < lower) | (df["Fare"] > upper)
print(is_out.sum(), "fare outliers")
print(round(is_out.mean() * 100, 1), "percent of passengers")
is_out.groupby(df["Pclass"]).sum()
Your turn: write
iqr_bounds(s)that returns both fences, then count Age outliers.
def iqr_bounds(s, k=1.5):
q1, q3 = s.quantile([0.25, 0.75])
return q1 - k * (q3 - q1), q3 + k * (q3 - q1)
ages = df["Age"].dropna()
lo, hi = iqr_bounds(ages)
print("Age fences:", lo, "to", hi)
print((~ages.between(lo, hi)).sum(), "age outliers")
Judge each value against its own group#
A 100 pound fare is normal in first class but extreme in third.
z = (df["Fare"] - df["Fare"].mean()) / df["Fare"].std()
print((z.abs() > 3).sum(), "fares more than 3 std devs out")
by_class = df.groupby("Pclass")["Fare"]
q1_c = by_class.transform("quantile", 0.25)
q3_c = by_class.transform("quantile", 0.75)
iqr_c = q3_c - q1_c
out_c = ((df["Fare"] < q1_c - 1.5 * iqr_c)
| (df["Fare"] > q3_c + 1.5 * iqr_c))
out_c.groupby(df["Pclass"]).sum()
Quick check: who are the extreme fares in third class?
cols = ["Name", "Ticket", "Fare"]
df.loc[out_c & (df["Pclass"] == 3), cols].head(6)
Keep, cap or remove: fix the cause first#
Fares are per ticket, not per person. That changes everything.
ticket_size = df.groupby("Ticket")["Ticket"].transform("size")
df["Fare_pp"] = df["Fare"] / ticket_size
df[["Fare", "Fare_pp"]].describe().round(2)
lo, hi = iqr_bounds(df["Fare_pp"])
pp_out = ~df["Fare_pp"].between(lo, hi)
print(pp_out.sum(), "outliers per person, was", is_out.sum())
cap = df["Fare"].quantile(0.99)
capped = df["Fare"].clip(upper=cap)
print("Cap at the 99th percentile:", round(cap, 2))
print("Max before:", df["Fare"].max(), " after:", round(capped.max(), 2))
removed = df.loc[~is_out, "Fare"]
summary = pd.Series({"original": df["Fare"].mean(),
"capped": capped.mean(),
"removed": removed.mean(),
"median": df["Fare"].median()})
summary.round(2)
fig, ax = plt.subplots(figsize=(8, 4))
df["Fare"].plot.hist(bins=50, alpha=0.5, ax=ax, label="Original")
capped.plot.hist(bins=50, alpha=0.5, ax=ax, label="Capped at 99%")
ax.set_title("Titanic fares before and after capping")
ax.set_xlabel("Fare (pounds)")
ax.set_ylabel("Passengers")
ax.legend()
plt.show()
Quick check: if we delete fare outliers, does first class survival change?
rates = pd.DataFrame({
"all": df.groupby("Pclass")["Survived"].mean(),
"no_outliers": df[~is_out].groupby("Pclass")["Survived"].mean()})
rates.round(2)
Validate with rules: catch impossible values#
Unusual can be real. Impossible never is. Write the rules down.
taxis = pd.read_csv("data/pandas/taxis.csv")
print(taxis.shape)
taxis[["pickup", "dropoff", "passengers", "distance", "fare"]].head(3)
Common mistake: doing date maths on columns that are still text.
taxis["dropoff"] - taxis["pickup"]
taxis = pd.read_csv("data/pandas/taxis.csv",
parse_dates=["pickup", "dropoff"])
trip = taxis["dropoff"] - taxis["pickup"]
taxis["minutes"] = trip.dt.total_seconds() / 60
taxis["minutes"].describe().round(2)
checks = pd.DataFrame({
"no passengers": taxis["passengers"] == 0,
"zero distance": taxis["distance"] <= 0,
"zero minutes": taxis["minutes"] <= 0,
"fare below 2.50": taxis["fare"] < 2.5,
"total below fare": taxis["total"] < taxis["fare"]})
checks.sum()
valid_pay = ["credit card", "cash"]
bad_pay = ~taxis["payment"].isin(valid_pay)
print(bad_pay.sum(), "trips with an unknown payment type")
taxis["payment"].value_counts(dropna=False)
Your turn: check that every
pickup_boroughis one of New York's five boroughs.
boroughs = ["Manhattan", "Brooklyn", "Queens", "Bronx",
"Staten Island"]
bad_boro = ~taxis["pickup_borough"].isin(boroughs)
print(bad_boro.sum(), "trips fail the borough rule")
taxis.loc[bad_boro, ["pickup_zone", "pickup_borough"]].head(3)
Turn the rules into a reusable report#
One function you can run on every new batch of data.
def validate(d):
return pd.DataFrame({
"no passengers": d["passengers"] == 0,
"zero distance": d["distance"] <= 0,
"zero minutes": d["minutes"] <= 0,
"fare below 2.50": d["fare"] < 2.5,
"unknown payment": ~d["payment"].isin(valid_pay),
"unknown borough": ~d["pickup_borough"].isin(boroughs)})
report = validate(taxis)
summary = report.sum().to_frame("failures")
summary["percent"] = (summary["failures"] / len(taxis) * 100).round(1)
summary.sort_values("failures", ascending=False)
bad = report.any(axis=1)
print(bad.sum(), "trips fail at least one rule")
report.sum(axis=1).value_counts().sort_index()
Quick check: will
assertlet the raw taxi data through?
assert not bad.any(), f"{bad.sum()} trips failed validation"
valid = taxis[~bad].copy()
assert not validate(valid).any().any()
print("All rules pass on", len(valid), "of", len(taxis), "trips")
Mini-project: what does a taxi mile really cost?#
Compare fare per mile on the raw trips and the validated trips.
per_mile = taxis["fare"] / taxis["distance"]
print("Infinite values:", np.isinf(per_mile).sum())
print("Raw mean:", per_mile.mean())
speed = valid["distance"] / (valid["minutes"] / 60)
print((speed > 60).sum(), "valid trips faster than 60 mph")
valid[speed > 60][["distance", "minutes", "fare"]].round(2).head()
clean = valid[speed <= 60].copy()
clean["per_mile"] = clean["fare"] / clean["distance"]
lo, hi = iqr_bounds(clean["per_mile"])
print(len(clean), "trips; fences", round(lo, 2), "to", round(hi, 2))
clean["per_mile"].describe().round(2)
typical = clean["per_mile"].between(lo, hi)
print((~typical).sum(), "fare-per-mile outliers")
print("Mean with them:", round(clean["per_mile"].mean(), 2))
print("Mean without:", round(clean.loc[typical, "per_mile"].mean(), 2))
by_boro = clean[typical].groupby("pickup_borough")["per_mile"]
by_boro.agg(["median", "mean", "count"]).round(2)
Finding: a clean New York taxi mile costs about 5.4 dollars at the median. Raw data gave an infinite mean. Validate first, then summarise.
Recap#
describe, sorting and box plots reveal extremes fast- IQR fences: Q1 minus 1.5 IQR to Q3 plus 1.5 IQR; combine with
| - Compare within groups using
groupby().transform() - Fix the cause first, then keep,
clipor remove, and say which - Validation rules are boolean columns;
isin,between,assert
Challenges#
- Apply the IQR rule to taxi
tip. Which payment type holds the outliers? - Add a rule:
tipmust be 0 for cash trips. How many fail? - Repeat the class-based fences for Titanic
Fare_pp.
What's next#
Lesson 21: Hands-on - Cleaning a Real-World Dataset. Every cleaning tool so far, on one genuinely messy survey.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.
Related lessons
24 minLesson 2
Installing Pandas and Setting Up Your Python Environment for Data Science Success
26 minLesson 4
Data Mining with Python: pandas & scikit-learn Crash Course
19 minLesson 17
Effective Techniques for Handling Outliers and Validating Data in Pandas
36 minLesson 69