Data Cleaning

Data Science Fundamentals

Chapter 4 · Data Cleaning

ds1-3 introduced a small coffee-shop sales table with four real, named problems, left deliberately unfixed. This chapter fixes each one — the same dataset, not a fresh example — so the techniques below are demonstrated on a problem already familiar rather than an abstract new one.

Problem 1: Missing Data — Row 1003's store_id

Detecting and handling missing values

df.isna()            # a same-shape DataFrame of True/False, marking every missing cell
df["store_id"].isna().sum()   # how many missing values in just this column

df.dropna(subset=["store_id"])          # option A: drop rows with a missing store_id
df["store_id"].fillna("UNKNOWN")        # option B: fill missing values with a placeholder

Neither option is mechanically "correct" — they're a real trade-off. Dropping row 1003 entirely discards a genuine sale that happened, distorting any total revenue figure downstream. Filling it with a placeholder like "UNKNOWN" keeps the sale in the total but makes any per-store analysis (ds1-5's own groupby) silently wrong for that one row. For this dataset, filling with "UNKNOWN" is the better trade-off — the revenue total stays accurate, and the ambiguity is at least visible and labeled rather than silently dropped.

Problem 2: The Duplicated Row

Finding and removing exact duplicates

df.duplicated()          # True for each row that's an exact repeat of an earlier one
df.drop_duplicates()     # keeps the first occurrence, drops the rest

The final row in ds1-3's own table exactly repeats order 1005 — same order ID, same store, same date, same product, same quantity, same revenue. drop_duplicates() removes it cleanly, since it isn't a second, legitimate sale — it's the same row appearing twice, most plausibly from a data-export glitch.

Problem 3: The Inconsistent Date Format

Type conversion — why mixed formats block real dates

df["date"] = pd.to_datetime(df["date"])

Row 1004's own "07/11/2026" and every other row's "2026-07-10"-style value are both, to pandas, just strings until converted — and two differently-formatted strings can't be compared, sorted, or subtracted from each other meaningfully. pd.to_datetime() parses each string into an actual datetime value, at which point the original formatting differences disappear entirely — what's stored afterward is a real date, not text that merely looks like one, and every row is now directly comparable regardless of how it was originally typed.

Problem 4: Inconsistent Capitalization

String cleaning

df["product"] = df["product"].str.title()   # "latte" -> "Latte"

Without this fix, ds1-5's own groupby("product") would treat "latte" and "Latte" as two entirely separate products, silently splitting one product's own totals in two. Pandas' .str accessor applies ordinary Python string methods (py1-5's own string-methods material) across an entire column at once, the same vectorized spirit as ds1-2's own numeric operations, just for text.

A First Look at Outlier Detection

A quick, practical rule of thumb, not yet the full statistical grounding (ds1-6 covers that properly): a value is a plausible outlier if it falls more than 1.5 × the interquartile range (IQR) below the 25th percentile or above the 75th percentile.

q1, q3 = df["revenue"].quantile([0.25, 0.75])
iqr = q3 - q1
outliers = df[(df["revenue"] < q1 - 1.5*iqr) | (df["revenue"] > q3 + 1.5*iqr)]

This is a mechanical flag, not an automatic verdict — a flagged value might be a genuine data-entry error, or it might be a real, unusually large sale that's actually the most interesting row in the whole dataset. ds1-6's own statistics chapter explains exactly what a quartile and an IQR are, and why 1.5× specifically is the conventional threshold.

Before & After

Problem	Before	After
Missing store_id	NaN (row 1003)	"UNKNOWN"
Inconsistent date	Mixed string formats	Uniform datetime type
Inconsistent product casing	"latte" / "Latte"	"Latte" (uniform)
Duplicate row	Order 1005 appears twice	Removed — appears once

Cleaning decisions are judgment calls — document them

Every fix above involved a real choice, not a mechanical certainty — filling vs. dropping the missing store_id is the clearest example. A different analyst, with a different downstream question in mind, might reasonably have chosen to drop that row instead. Real data science practice documents these decisions explicitly (in comments, in a notebook's own text cells) rather than letting them disappear silently into the cleaned dataset — a conclusion drawn later is only as trustworthy as the cleaning choices behind it.

Hands-On Exercises

Exercise 1

Explain, using this chapter's own reasoning, why filling row 1003's missing store_id with "UNKNOWN" was judged the better trade-off here than dropping the row entirely, and describe a different situation where dropping would be the better choice instead.

📄 View solution

Exercise 2

Explain, using this chapter's own reasoning about pd.to_datetime(), why "07/11/2026" and "2026-07-10" can't be meaningfully compared before conversion, and what specifically changes once both are converted.

📄 View solution

Exercise 3

Explain why this chapter describes the IQR-based outlier rule as "a mechanical flag, not an automatic verdict," and give a concrete example of a flagged value that would be wrong to simply discard.

📄 View solution

Chapter 4 Quick Reference

Missing data — isna() to detect, dropna() vs. fillna() as a real trade-off, not a mechanical choice
Duplicates — duplicated()/drop_duplicates()
Type conversion — pd.to_datetime() turns mismatched date strings into genuinely comparable values
String cleaning — the .str accessor applies py1-5's own string methods across a whole column, vectorized
Outliers — the 1.5×IQR rule as a flag, not a verdict; full statistical grounding in ds1-6
Cleaning decisions are judgment calls — document them, don't let them vanish silently
Next chapter: Data Wrangling & Reshaping