Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 64 ยท Transforming Data

Duplicates and Outliers

Find and handle duplicate rows with duplicated and drop_duplicates, detect outliers with the IQR and z-score rules, then flag, clip or remove them on purpose.

Real data is rarely clean. The same order appears twice because an export ran twice. A price of 9,500 sits among prices near 50 because someone typed an extra zero. These two problems, duplicates and outliers, change counts, totals and averages, so you need to find them and decide what to do about them.

"Decide" is the important word. Duplicates and outliers are not always mistakes: two identical-looking orders might be real, and a huge sale might be your best customer. This lesson gives you the pandas tools to detect both and a habit to go with them: flag first, remove second, and write down the rule you used.

Finding duplicate rows

df.duplicated() returns a Boolean Series that is True for every row that repeats an earlier row. drop_duplicates() returns the frame without those rows. By default two rows are duplicates only if every column matches.

Python duplicated, keep and subset

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Only row 2 is a full-row duplicate. Row 5 shares its order_id with row 1 but has a different qty, so the default comparison considers it unique. That is exactly the case where you need to think.

subset and keep

Two arguments control the comparison:

  • subset= names the columns to compare. Other columns are ignored.
  • keep= decides which member of a duplicate group survives: "first" (default), "last", or False to drop all members.
Python Choosing the key and the survivor

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The choice of subset is a business rule, not a technical one. "One row per order_id" says an order id must be unique. "One row per customer and item" would treat the second purchase of a mug by Ben as a duplicate, which is almost certainly wrong. Ask what makes a row the same real-world thing before you drop anything.

The choice of keep matters when duplicates differ in other columns. If your file is ordered by time, keep="last" keeps the most recent version of a record. If you are unsure which copy is right, do not guess: keep them all and investigate, or use keep=False to take the conflicting rows out for review.

flowchart TD
    A["rows read from a file"] --> B["duplicated(subset) marks repeats"]
    B --> C{"identical in every column?"}
    C -- yes --> D["safe to drop: drop_duplicates()"]
    C -- no --> E["same key, different values"]
    E --> F["decide: keep first / keep last / review by hand"]

Exact copies are safe to remove; rows that share a key but differ need a decision.

Near-duplicates

Real duplicates are often not exact. "[email protected]" and "[email protected] " are the same person. Normalise the key first (strip spaces, lowercase; see Working with Text), then compare:

Python Normalise before comparing

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Two things to notice: normalising revealed the second duplicate, and the two missing values count as duplicates of each other, because None/NaN compare equal inside duplicated. Check for missing keys separately (Missing Data).

Finding outliers

An outlier is a value far from the rest. pandas has no find_outliers(), because "far" depends on your data, so you apply a rule yourself. Two rules cover most work.

The IQR rule

The interquartile range (IQR) is the distance between the 25th and 75th percentiles: IQR = Q3 - Q1. The common rule calls a value an outlier if it is below Q1 - 1.5 * IQR or above Q3 + 1.5 * IQR. These limits are the "whiskers" of a box plot. Because quartiles are barely moved by a few extreme values, the rule stays reliable when outliers are present.

The z-score rule

A z-score says how many standard deviations a value is from the mean: z = (x - mean) / std. A common cut-off is |z| > 3. It works well for roughly bell-shaped data, but it has a weakness: extreme values inflate the mean and the standard deviation, which hides themselves.

Python IQR and z-score rules side by side

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The IQR rule flagged the two planted values (95.0 and 4.0) and also three mildly unusual ones, because with 40 values some natural spread lies beyond 1.5 IQR. The z-score rule at 3 flagged only the planted two. Neither is "right": the multiplier (1.5 or 3) is a judgment call, and you should look at the flagged rows.

When the rules disagree

The z-score is fooled when the outliers are large and numerous enough to drag the average:

Python Outliers that hide themselves

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The value 1000 has a z-score of only 2.36, so |z| > 3 misses it entirely, and 500 scores 0.93. The IQR rule catches both. When your data is skewed or has heavy tails (prices, incomes, response times), prefer the IQR rule, or apply the z-score to the logarithm of the values.

Flag, clip or remove

You have three choices once you have found outliers, and each answers a different question.

  • Flag. Add a Boolean column (df["price_outlier"]) and keep every row. This is the safest default: nothing is lost and anyone can filter later.
  • Clip (winsorize). Replace extreme values with the limits using clip. The row stays and its influence is capped. This suits modelling, not accounting: you are changing numbers.
  • Remove. Drop the rows. Use this when you are confident the values are errors (a negative age, a date in year 3024).
Python The three strategies on one column

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Look at the last two lines: removing the five flagged rows moved the mean but left the median unchanged. If a statistic barely changes when you remove a handful of rows, it was robust; if it jumps, your conclusion depends on those rows and you should say so. See Descriptive Statistics for more on robust measures.

Document your decisions

Cleaning steps that are not recorded cannot be repeated or questioned. Make the rules explicit in code and keep a small log of what each step did:

Python
report = {
    "rows_in": len(df),
    "duplicates_removed": int(df.duplicated(subset="order_id").sum()),
    "outliers_flagged": int(df["price_outlier"].sum()),
    "rule": f"price outside [{low:.1f}, {high:.1f}] (1.5 x IQR)",
}

Put the thresholds in named variables, not magic numbers inside expressions, and write one sentence about why the rule fits the data. Months later, that sentence is what lets someone trust, or correct, your numbers. Larger workflows turn these steps into functions; see Data Cleaning in Practice.

Never remove outliers just because they make a result look nicer. If a value is real, it belongs in the analysis; at most, report results with and without it.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does df.duplicated() return by default?

    By default the first occurrence is False and later copies are True, and a row counts as a duplicate only if every column matches. keep=False marks all members, and the result is a Boolean Series, not a frame or a count.
  2. 2. Which call keeps only the most recent row for each order_id, assuming the data is sorted by time?

    keep='last' keeps the final occurrence in each group. The default keeps the first, and keep=False removes all members of every duplicated group.
  3. 3. A column has outliers in the thousands among values near 10. Why might the z-score rule miss them?

    Extreme values raise the standard deviation a lot, so even very large values may score under 3. Quartile-based rules such as IQR are barely affected.
  4. 4. What does Series.clip(lower=a, upper=b) do to a value greater than b?

    clip caps values at the limits instead of dropping them. That keeps the row but changes the number, so record that you did it.
  5. 5. Which is the safest first response to suspected outliers?

    Flagging loses nothing and makes the decision reviewable. Whether to clip or drop depends on whether the values are errors or real.

Summary

  • duplicated() marks repeats and drop_duplicates() removes them; subset= chooses the key columns and keep= chooses the survivor ("first", "last" or False).
  • Normalise text keys (strip, lowercase) before looking for duplicates, and decide what makes two rows the same real-world thing.
  • The IQR rule flags values outside Q1 - 1.5 * IQR and Q3 + 1.5 * IQR and stays reliable with extreme values; the z-score rule suits bell-shaped data and can hide big outliers.
  • Flag first; clip (clip) to cap influence; remove only values you are confident are errors.
  • Write the rule, the threshold and the reason down, and keep a small report of what each step changed.

Next: groupby Basics.