Lesson 8 of 8 · 9 min · Cleaning data
Outliers and a repeatable cleaning script
What you will learn
- What an outlier is and why it needs a decision, not automatic deletion
- Detect outliers with the IQR rule, z-scores and domain rules
- Cap (winsorise), fix, flag or remove them
- Look at them with a box plot and histogram
- Combine every cleaning step into one repeatable script
In simple words
An outlier is a value far away from the others, like a customer aged 150 or a salary of ten crore in a list of ten lakhs. It could be a typing mistake (clean it), a real extreme case (keep it) or a fraud signal (report it). The skill is not deleting everything unusual, it is finding the unusual values and then deciding, with a reason, what to do about each.
Words to know
- Outlier: a value that is unusually far from the rest of the data.
- Quartile: one of the three cut points that split sorted data into four equal parts.
- IQR (interquartile range): the distance between the first and third quartile.
- Winsorising: replacing extreme values with a chosen limit, such as the 1st or 99th percentile.
Look before you decide
df["age"].describe()
df["age"].quantile([0.01, 0.25, 0.5, 0.75, 0.99])
df["age"].plot(kind="box"); plt.show()
df["age"].plot(kind="hist", bins=20); plt.show()
df.nlargest(5, "age")
df.nsmallest(5, "age")
In the sample, the ages are 24, 31, (missing), 29, 27, 150. The 150 is plainly impossible for a person, so it is an error, not an extreme. Charts show you the shape: a box plot marks points beyond the whiskers as dots.
Three ways to detect them
1. Domain rules (the best first check). Use what you know: age 0 to 100, price above 0, a date not in the future, a percentage between 0 and 100.
bad_age = ~df["age"].between(0, 100) & df["age"].notna()
df[bad_age]
2. The IQR rule (robust and common). Values more than 1.5 times the IQR below Q1 or above Q3 are flagged:
q1, q3 = df["salary"].quantile([0.25, 0.75])
iqr = q3 - q1
low, high = q1 - 1.5 * iqr, q3 + 1.5 * iqr
outliers = df[(df["salary"] < low) | (df["salary"] > high)]
It is the rule behind box plots. Use 3 instead of 1.5 for "extreme" outliers.
3. Z-scores (for roughly bell-shaped data). A z-score is how many standard deviations a value is from the mean:
z = (df["salary"] - df["salary"].mean()) / df["salary"].std()
outliers = df[z.abs() > 3]
Extreme values inflate the mean and standard deviation themselves, so z-scores can miss outliers in small or skewed data; the IQR rule or a robust version using the median and MAD is safer. Skewed money data (salaries, order values) often looks better on a log scale: np.log1p(df["salary"]).
Outliers can also be about a group: a 40,000 salary is normal for a fresher and odd for a director. Compare within groups using groupby(...).transform for quartiles.
Decide what to do
| Decision | When | Code |
|---|---|---|
| Fix it | clear typing error with a known right value | df.loc[mask, "age"] = 35 after checking the source |
| Set to missing | impossible value, true value unknown | df.loc[mask, "age"] = np.nan |
| Cap (winsorise) | real but extreme, and you need stable averages | df["x"].clip(lower, upper) |
| Flag and keep | might be real or informative (possible fraud, a VIP) | df["is_outlier"] = mask |
| Remove row | the whole record is junk (test data) | df[~mask] |
| Use robust statistics | keep the data, avoid distortion | median, IQR, trimmed mean |
Capping example:
low, high = df["salary"].quantile([0.01, 0.99])
df["salary_capped"] = df["salary"].clip(low, high)
Never remove outliers just because they spoil a result. Report both views when it matters: "average order value is 920; without the three bulk orders it is 640".
A repeatable cleaning script
Notebook cells are fine for exploring, but run the same steps every week from one function. A skeleton for the messy sample:
import numpy as np
import pandas as pd
def clean_customers(raw: pd.DataFrame) -> tuple[pd.DataFrame, list[str]]:
df = raw.copy()
log = []
# text
for col in ["name", "city"]:
df[col] = df[col].str.strip().str.title()
# missing and duplicates
df["salary"] = pd.to_numeric(
df["salary"].astype("str").str.replace(r"[₹,\s]", "", regex=True), errors="coerce")
n = len(df)
df = df.drop_duplicates(subset=["name", "city", "signup"])
log.append(f"removed {n - len(df)} duplicate rows")
# dates
iso = pd.to_datetime(df["signup"], format="%Y-%m-%d", errors="coerce")
dmy = pd.to_datetime(df["signup"], format="%d/%m/%Y", errors="coerce")
df["signup"] = iso.fillna(dmy)
log.append(f"{df['signup'].isna().sum()} signup dates could not be read")
# outliers by domain rule
bad = ~df["age"].between(0, 100) & df["age"].notna()
df.loc[bad, "age"] = np.nan
log.append(f"set {bad.sum()} impossible ages to missing")
# missing values
df["city"] = df["city"].fillna("Unknown")
df["age"] = df["age"].fillna(df["age"].median())
return df, log
clean, log = clean_customers(raw)
print("\n".join(log))
clean.to_csv("customers_clean.csv", index=False)
Habits that make it trustworthy:
- Never overwrite the raw file; read raw, write clean.
- One function, small steps, a log of every change with counts.
- Assertions at the end (
assert clean["name"].notna().all()). - Put it under version control, with a short README of rules and assumptions.
- Test on a sample with known problems, as we did here, before running on the full file.
- Run it from the command line (
python clean.py raw.csv clean.csv), so it can be scheduled.
Common mistakes
- Deleting outliers automatically without asking if they are real.
- Using z-scores on small or skewed data.
- Capping the target variable you are trying to forecast or report.
- Cleaning by hand in a spreadsheet where nothing is recorded or repeatable.
- Not saving the cleaning rules, so next month's report differs for unknown reasons.
Try it yourself
- Add a salary of 9,00,00,000 to the sample and detect it with IQR and with a z-score; compare.
- Draw a box plot and a histogram of the salary column.
- Cap the salary at the 1st and 99th percentiles and compare the mean before and after.
- Put every step you have done in this course into one
clean_customersfunction that returns the log. - Write three assertions that your cleaned table must satisfy.
Check yourself
How does the IQR rule flag outliers?
Values below Q1 minus 1.5 times the IQR or above Q3 plus 1.5 times the IQR are flagged, where IQR is Q3 minus Q1.
Why is a domain rule often better than a statistical rule?
It uses real knowledge, such as age cannot exceed 100, so impossible values are caught regardless of the data's spread.
What is winsorising?
Replacing values beyond chosen limits, such as the 1st and 99th percentiles, with those limits, instead of deleting them.
Why keep a log in a cleaning script?
So every change and its size is visible and the results can be trusted and reproduced.
Interview questions
How do you detect and treat outliers?
Inspect with summary statistics and plots, apply domain limits and a statistical rule such as the IQR; decide per case to fix, cap, flag or remove, explain the reason, and compare results with and without them.
When should you not remove an outlier?
When it is a real observation, such as a genuine large order or a fraud case, or when the question is about extreme events.
How would you make a cleaning process reproducible?
Keep raw data unchanged, write the steps as a script or function with logging and assertions, store it in version control and run it the same way each time.
Summary
- Outliers need a decision: fix, set missing, cap, flag, remove or use robust statistics.
- Detect with domain rules, the IQR rule and, carefully, z-scores; look at plots.
- Put all cleaning into one logged, repeatable function and keep the raw data.
- Report results with and without outliers when they matter.
Going deeper
- Visualization: box plots and histograms: quick plots for spotting outliers.
- Series.clip: capping values.
- Series.quantile: percentiles for the IQR rule.
- Statistics: median, quantiles in the Python standard library: basics without pandas.
See how much of this you can explain
A free mock interview asks you new questions on Python for data analysis with pandas for Data Analyst and scores every answer, with what to fix.