Cleaning, missing data and outliers — the raw material is in hand; now you decide what it's allowed to say.
Knowledge Discovery in Data · University of the Philippines Cebu
Find the holes, understand why they're there, then drop or fill — on purpose.
Flag the far-out points with the IQR rule — then decide, don't just delete.
Every cleaning step changes the story. Do it in code, and write it down.
Weeks 3–4 got data onto your machine. CRISP-DM (week 2’s six-step project cycle) calls what starts now Data Preparation — and it's where projects spend most of their time.
Cleaning is not fixing typos. It's a series of judgment calls a reader must be able to audit.
Whatever door you came through — parsed HTML, an API's JSON — the rows arrive with holes, duplicates and values that make no sense. Raw data is a rumor until you've checked it.
Nobody promised these values are complete, consistent, or even possible.
Every analysis downstream inherits your data's flaws — garbage in, confident-looking garbage out.
Values that are absent, and values that are absurd.
"Ask any working data scientist what they actually do all day, and the honest answer is mostly this week and next: finding gaps, chasing weird values, and defending the choices they made about both."Why CRISP-DM draws Data Preparation as the biggest loop
The famous claim is "80% of the work." The exact number varies; the ranking never does — preparation dominates modeling.
This isn't the boring part before the real work. It is the real work.
A cell where nothing was recorded. It can mean “unknown”, “not yet” or “refused”.
e.g. an unfinished project has no completion date
“Not a Number”: how pandas marks an empty cell. Summaries skip it; .isna() finds it.
e.g. df.isna().sum() counts NaNs per column
The general word for “no value”: JSON’s null, Python’s None. pandas treats both as missing.
e.g. week 4’s "closed_on": null
Filling a gap with a stand-in value you chose, instead of dropping the row.
e.g. fillna(df.temp.mean())
Three “typical values”: the average; the middle value once sorted; the most common value.
e.g. for 8, 12, 12, 45, 80: 31.4 / 12 / 12
A row identical to an earlier one. It double-counts everything it touches.
e.g. the same project scraped twice
What kind of values a column holds: whole numbers, decimals, text, dates. pandas calls it dtype.
e.g. int64, float64, object (text)
A value far from the others. Far is not the same as wrong: it may be a typo or the discovery.
e.g. 98 in a row of 12s
The three cut points that split sorted data into four equal groups: Q1, the median, Q3.
e.g. Q1 = 12 and Q3 = 14 for our toy series
Interquartile range, Q3 − Q1: the width of the middle half. Fences sit 1.5 IQRs beyond it.
e.g. IQR = 2, fences at 9 and 17
The typical distance of values from their mean. Big SD = widely spread values.
e.g. s.std()
How many standard deviations a value sits from the mean. Beyond 2 or 3 is the usual flag.
e.g. the 98 has z = 2.66
Rescale a column: to z-scores (mean 0, SD 1), or squeeze it into 0 to 1.
e.g. week 6 uses both before comparing columns
The values that aren't there — and what their absence means.
You can already load a table into pandas and look at its columns. This part adds the first question to ask of any new table: which cells are empty, why, and what to do about them.
A sensor went down, a survey question was skipped, a form field wasn't required.
Joins that didn't match, scrapes where a tag was absent — your .get()
returning None last month.
People decline to answer for reasons — income, health, politics. The gap carries information.
A missing value is a cell where nothing was recorded. In pandas,
all of these look identical: NaN. The origin is invisible in the cell — you have to reason it out.
null, Python None); pandas treats these as missing tooA random glitch and a meaningful refusal deserve different treatment — same
NaN, different decision.
isna().sum() is the first thing to run on any new dataset.
Per-column counts tell you where the problem lives; a single total hides it.
A column 2% empty and a column 60% empty are different diseases — one gets a fill, the other might get dropped entirely.
pd.DataFrame({…})a tiny weather table: five cities, three columns; np.nan is an empty celldf.isna()a same-shaped table of True (empty) / False (filled).sum()adds each column’s Trues (True counts as 1): the gaps per columntemp 2two cities (Davao, Tacloban) have no temperature; city is complete.any(axis=1)True for a row with at least one gap; axis=1 means “look across each row”Last week you pulled these through an API and counted budgets. Today you ask the question that should always come first: what is missing, and why?
Real DPWH flood control projects, 2016–2026. Public domain. Every number on the next slides is computed from the copy in this repo.
pd.read_csv("….csv.gz")load a CSV file (a plain-text table) into a DataFrame; .gz means compressed, and pandas unzips it for youdf.shape → (34079, 15)34,079 rows (projects) and 15 columnsSame one-liner as the toy frame. Different feeling when the answer is about public money.
The other ten are complete. That alone is worth knowing before you plan any cleaning at all.
.sort_values(ascending=False)sort the counts, biggest first, so the worst columns are on top64146,414 of 34,079 projects have no completion date: 18.8%Exactly 9.38% of rows.
Exactly the same count.
Not a coincidence. The same rows, every time.
A matched pair like this is a structural gap (caused by how the data was built): the geocoding step (turning an address into map coordinates) either ran for a row or it did not. Filling latitude without longitude would invent a location in the sea.
df[['latitude','longitude']].isna().sum(axis=1).value_counts() — gaps per row (axis=1), then how many rows have each count. You want only 0 and 2, never 1.
The real answer: 30,884 rows with 0 and 3,195 with 2.
"Is it random?" is not a matter of opinion. Put the missingness next to another column and look: a cross-tab is exactly that, one column’s pattern broken down by another’s groups.
If the gap were random, every status would show a similar percentage. Watch what actually happens.
df.groupby("status")split the rows into piles, one per status["completion_date"]in each pile, look only at the completion date column.apply(lambda s: s.isna().mean())run a tiny nameless function (lambda) on each pile: the share of empty cells (mean of True/False = fraction True)0.0 / 1.00% missing or 100% missing: nothing in between0% missing. A finished project has a finish date.
100% missing. It has not finished, so there is no date to record.
100% missing. Not even awarded yet.
Zero percent and one hundred percent. Nothing in between. This is as far from "missing at random" (gaps that fall anywhere, like coin tosses) as data gets — the empty cell is carrying the information.
"completion_date is missing exactly when the project is unfinished." Once you can say that, the right treatment is obvious — and it is not a fill.
The 876 For Procurement rows are missing three fields at once — and it is the same 876 rows each time.
No contractor has been chosen, no amount agreed, no completion date possible. The row is honest; it is just early.
df[df.status == "For Procurement"]a boolean (True/False) filter: test every row, keep only the rows where the status matchesfp.contractor.isna().sum()how many of those rows have no contractor(fp.budget == 0).sum()how many have a budget of exactly 0 (True counts as 1)Before choosing a treatment, ask why the value is missing. Three broad cases, from harmless to dangerous.
A missing exam score can mean the paper got lost (random), the student was sick during flu season (explained by something you know), or the student skipped the exam they expected to fail (the gap itself is the message).
The gap has nothing to do with anything — a dropped packet. Safest to drop or fill.
Older stations miss more readings. Predictable — a group-aware fill can work.
High earners skip the income question. Dropping them silently biases everything after. Handle with care — and say so.
dropna() invents nothing — it just discards. The cost is
every other value in those rows, gone with the gap.
Few rows affected, and the gaps look random. When 40% of a column vanishes — or the missing rows share a pattern — it isn't.
np.nanNumPy’s missing-value marker (from import numpy as np)df.dropna()a new table with every row that has a gap removed; df itself is unchanged0, 2, 3the row labels keep their old numbers, so you can see rows 1 and 4 were droppedFilling a gap with a stand-in value is called imputation.
fillna() plugs each hole with a stand-in — often the column
mean or median. Nothing is lost; something is made up.
df["temp"].mean()the average of the values that exist: (31 + 33 + 29) / 3 = 31.0df.fillna(…)put that number into every empty cellMean-filling drags rows toward the average and quietly shrinks the spread. The more you fill, the more average — and less real — the column looks.
Three different “typical values” you can fill with. They agree on tidy data and disagree loudly when one value is extreme, like the 80 mm day here.
The mode is the sari-sari store’s best-selling item; the median is the customer in the middle of the queue; the mean is what you get by sharing the whole bill equally.
pd.Series([…])one column of values; np.nan is the gaprain.mean() → 31.4pulled up by the 80: higher than four of the five readingsrain.mode()[0]mode() returns a list-like Series (there can be ties); [0] takes the firstOne global mean says a missing Baguio temperature is "average for the Philippines." A group mean says it's "average for Baguio" — a far better guess.
If you do not know one Baguio reading, you guess from other Baguio days, not from Cebu’s. The group is the neighbourhood you borrow from.
transform("mean") builds a column of each row's own group average —
then fillna uses it only where the holes are.
You'll write exactly this — and argue why it beats the global mean.
It runs instantly. It returns a clean frame. It is about to lie to you.
Dropping every row with any gap looks tidy. Here is the receipt.
That is a quarter of the public record, removed without a decision being written down anywhere.
clean = df.dropna()a new table without any row that had a gap; df keeps all rowslen(df), len(clean)two row counts side by side: before and afterdropna() did not remove a random quarter. It removed every unfinished project in the country, because unfinished is exactly the condition that leaves a blank.
(value_counts() counts how many rows have each value.)
Nothing in clean is false on its own. Every row is real. But the set of rows now describes a country where flood control almost never stalls.
Survivorship bias: drawing conclusions only from the cases that made it through a filter. In WWII, analysts studied bullet holes on the planes that came back; the planes hit elsewhere never returned to be counted.
df.dropna() whole-frameIt treats every column’s gap as the same problem. They are not.
df.dropna(subset=["budget"]) — "rows with no budget cannot answer a budget question."
Add df["is_finished"] = df.completion_date.notna() and keep every row.
The test: could you defend this drop in one sentence to someone who disagrees with your conclusion? If not, you are not cleaning, you are shaping.
(subset= checks only the columns you name; notna() is the opposite of isna().)
Read the CSV. Run isna().sum(). Which column is worst?
Take start_date (973 missing). Cross-tab it against status and against year.
Random, or a message? Write the one sentence you would put in a footnote.
Does your neighbour’s sentence match yours? If not, one of you has found something.
You will need df.groupby("status")["start_date"].apply(lambda s: s.isna().mean()) — the same move as the slide above.
You run
df.dropna() on the flood data and 8,709 rows disappear.
What has actually happened to your analysis?
A survey's income column is 35% empty — and you suspect high earners are the ones skipping it. Fill with the column mean?
B — this gap is a message.
If the missing values were mostly high, filling them with the average of the answered ones writes systematically low numbers into exactly the rows that were highest. Whatever you do here, it must be declared — not hidden inside a one-liner.
The treatment depends on the origin — that's why Part A started with where holes come from.
Count, diagnose, then treat. Never treat first.
A duplicate is a row identical to an earlier one. Scrapes that fetch a page twice, or files glued together twice, create them silently, and every count and sum after that is inflated.
A class list where one student was typed in twice: the headcount, the average grade and the pass rate are all quietly wrong.
No repeated rows, and no contract id used twice. Checking took one line; not checking is how double-counting ships.
t.duplicated()True for each row that repeats an earlier row; the first copy stays False.drop_duplicates()a new table with the repeats removed: 3 rows in, 2 outdf.contract_id.duplicated()repeats in one column only: a contract id should never appear twiceA column’s data type (pandas says dtype) decides what you can do with it: add numbers, sort dates, search text. Load a CSV and pandas guesses each type from what it sees.
start_date
looks like 2022-07-14 but is stored as text: you cannot subtract two of them until you
convert the column.
df[[…]]keep four columns (a list of names inside the brackets).dtypesthe data type of each columnpd.to_numeric converts text such as "31" into the number 31.
One cell that is not a number stops it with an error. Read the traceback (the error report)
from the bottom up:
ValueErrorlast line first: the type of error. A ValueError means the right kind of input with an unusable value"n/a" at position 1the message: which value, and where (counting from 0: the second cell)line 3your own line that triggered it: the to_numeric callerrors="coerce" turns
anything unreadable into NaN instead of stopping: the bad cell becomes a missing
value you can count, as in Part A.
Then: the values that showed up — and shouldn't have.
Far from the rest — which is not the same as wrong.
Part A dealt with values that are absent. This part deals with values that are present but surprising: how to flag them with a rule, then how to judge them.
An outlier is a value far from the others. That's the whole
definition — a statement about distance,
not about truth. The 98 in a row of 12s might be a typo. It might be the discovery.
(pd.Series makes one column of values.)
A data-entry slip (9.8 typed as 98)… or a real reading from an
extraordinary day. Same number, opposite meanings.
Flag mechanically (this part), then judge honestly (the next part).
Sort the values. The quartiles cut them into four equal groups: Q1 is a quarter of the way up, Q3 three quarters. The middle 50% runs from Q1 to Q3, and its width is the IQR (interquartile range). Anything beyond 1.5 × IQR outside that box gets flagged. It's the rule behind every boxplot's whiskers.
Line the class up by height. The middle half of the line is the box; a fence sits one and a half box-widths beyond each end. Anyone past a fence gets a second look.
s.quantile(0.25), s.quantile(0.75)Q1 = 12.0 and Q3 = 14.0 for this seriesiqr = q3 - q114 − 12 = 2: the width of the middle halflow, highfences at 12 − 3 = 9.0 and 14 + 3 = 17.0(s < low) | (s > high)below the low fence or (|) above the high one6 98row label 6 holds the value 98: the only point past a fenceQuartiles barely move when an extreme value appears — so the fence itself isn't bent by the very point it's trying to catch.
"The 1.5×IQR fence comes from John Tukey, who reportedly chose it because 1 was too tight and 2 was too loose. It earns its keep by being useful — not by being sacred."Flag first, then look — the rule finds candidates, not culprits
Skewed data, tiny samples and heavy-tailed measurements (where extreme values are common) all bend the rule. It's a starting flashlight, not a verdict.
Z-scores (distance in standard deviations) — simpler, but the mean and SD they use are themselves dragged around by outliers.
The standard deviation (SD) is the typical distance of values from their mean. A z-score says how many SDs a value sits from the mean; past 2 (or 3 on big data) is the usual flag. Turning every value into its z-score is called standardising; squeezing values into 0 to 1 is normalising (week 6).
The 98 is inside the mean and SD it is measured against. Without it the SD is 1.31; with it, 28.36. The outlier stretches its own yardstick, so a few big outliers can hide each other.
s - s.mean()each value’s distance from the mean (22.44)/ s.std()measured in standard deviations (28.36) instead of in millimetresz.abs() > 2abs drops the minus sign; keep values more than 2 SDs away, either side2.66the 98 is 2.66 SDs above the mean; every other value is within half an SDImpossible values: a 250°C afternoon in Cebu, a negative age, a unit slip (meters logged as millimeters). Fix from the source if you can; otherwise treat as missing.
Rare but real: the rainfall the day a typhoon hit, one viral post among thousands, a billionaire in an income survey. Deleting these deletes the news.
The deciding question is never "is it far out?" — it's "could this value actually happen, and does my analysis need to include days like that?"
Run it both ways and report both (a sensitivity check: does the answer change?). Sensitivity beats certainty you don't have.
The value is real and your question includes extreme days. Consider robust summaries (ones barely moved by extremes, like the median) so one point doesn't run the show.
Capping (statisticians say winsorizing): s.clip(upper=high) pulls extremes in to the fence. The row survives;
its leverage doesn't.
For values that can't be true and can't be fixed. Record how many, and why — a deletion without a note is a silent edit to reality.
In the lab, capping the single 98 barely touches the data — eight values unchanged — yet the mean drops from 22.4 to 13.4.
One point was quietly in charge of the average. That's leverage (one value’s power to move a result) — and it's exactly what the next slide is about.
Every value moves the mean — so one huge value moves it a lot. The median only cares about order, and one extreme point barely changes the order.
Ten jeepney passengers each earn ₱20,000 a month. A billionaire climbs aboard: the mean income jumps into the millions, while the median passenger earns exactly what they did before.
Suspect outliers? Report the median (and fill gaps with it, too). If mean and median disagree loudly, that disagreement is itself a finding.
Before flagging anything, look at the shape. A long right tail (most values small, a few huge ones stretching the range to the right: the data is skewed) is normal for money — and it is why the mean is the wrong summary.
Half of all projects sit under ₱37.7M. The largest is ₱1.45B. Both facts are true and neither is an error.
pd.set_option(…)display setting: show numbers with commas and no decimals.describe()an eight-number summary: count, mean, SD, smallest, quartiles, largest25% / 50% / 75%Q1, the median, Q3Same formula as the toy example. Now the number it produces is a peso amount you could argue about in public.
2.34% of the dataset sits above the upper fence. That is a reading list, not a delete list.
quantile([.25, .75])both quartiles at once: about ₱14.6M and ₱67.5MfenceQ3 + 1.5 × IQR: about ₱146.9M(df.budget > fence).sum()how many budgets are above it: 798| Budget | Region | Description |
|---|---|---|
| ₱1,447,499,996 | Central Office | Construction of small river impounding project |
| ₱1,096,255,230 | Central Office | Construction of bank protection works |
| ₱1,042,782,802 | Central Office | Construction of bank protection works |
All three are Central Office (DPWH’s national headquarters) entries — national-scale works, not regional ones. The fence did its job: it pointed at the rows worth reading. Reading them showed they belong.
Delete these and you have removed the largest flood control projects in the country from an analysis about flood control spending.
The mean is dragged upward by the same 798 projects the fence flagged. The median does not move.
Say "typical project" and you mean the median. Say "average" and half your audience will picture the median anyway — so give them the one they imagine.
round(df.budget.mean())the average budget, rounded to whole pesos: about ₱47.0Mround(df.budget.median())the middle budget: about ₱37.7M1.25the mean is 1.25 times the median, i.e. 25% higherNot large — impossible. The 0 is a placeholder: a value typed in to fill a slot, not a measurement. A zero-peso construction contract is not a cheap project; it is a project that has no contract yet.
876 are For Procurement. The remaining 63 are Terminated. Both are "no money moved", recorded as 0 rather than as blank.
Your mean budget drops. You are averaging contracts that do not exist.
df.budget.replace(0, np.nan) — then the mean describes real contracts only.
df[df.status.isin(["Completed","On-Going"])] — isin keeps rows whose status is in the list; say what population you mean.
This is the week-4 amount_paid lesson again: a column of zeros is a claim, and you have to check whether it means zero or unknown.
Compute the IQR fence within each region instead of across the whole country. Does Region III flag different projects?
Open the descriptions of any three flagged rows. Do they look like errors or like big projects?
Keep, cap, or remove — and write the reason next to each.
df.groupby("region").budget.transform(lambda s: s.quantile(.75) + 1.5*(s.quantile(.75)-s.quantile(.25)))
939 flood projects
have budget == 0. Should they be included when you compute
the average project budget?
Daily rainfall for Tacloban, November 2013. One day reads 600 mm — flagged hard by the IQR rule. Best move?
C — investigate before you touch it.
Yolanda made landfall that month; an extreme reading is plausibly the single most important value in the column. Delete or cap it and your "cleaned" dataset says the storm never happened.
Statistically unusual and factually wrong are different judgments. Only the first one is automatic.
Every flagged point gets a look before it gets a treatment.
Cleaning changes the story. Do it accountably.
You now know how to drop, fill, flag and cap. This part is about keeping a record of each of those choices so someone else can check them.
"Drop, fill, cap, remove — each one changes what the dataset is able to say. Two honest analysts can clean the same file differently and reach different conclusions. What makes either defensible is that the choices are visible."Cleaning is analysis — it just happens earlier
This is why "I cleaned the data" is never a full sentence in this course. Cleaned how? At what cost? To how many rows?
When data becomes journalism, these choices face public scrutiny. Build the habit now.
Never overwrite the original file. Cleaned versions are new, derived objects.
Every step is a line that can be re-run — not a hand-edit in a spreadsheet nobody can reproduce.
What you did, why, and how many rows it touched: "filled 2 temps with region means; dropped 1 impossible rainfall."
Your notebook already does rule 2 by existing. Rules 1 and 3 are habits — cheap now, priceless the day someone asks "where did row 47 go?"
Could a classmate rerun your notebook on the raw file and get your cleaned one exactly? That's reproducibility.
Every decision above changed the row count or a column’s meaning. If it is not written down, next month you cannot tell what you did — and neither can anyone checking you.
Rows in, rows out, and the reason. Three numbers and a sentence.
log = []an empty list that will collect one entry per cleaning stepdef step(name, before, after, why):define a small function (a reusable recipe) with four inputslog.append({…})add one dict (a dictionary: named values in { }, like a form with labelled boxes) to the list: one row of the logstep("no budget", 34079, 33140, …)record that 939 rows were dropped, and whyStart, after each filter, end. A missing 8,000 rows should be visible in the log, not a surprise.
Not "cleaned data" — "dropped rows with no budget, because the question is about spending."
Every step reads data/raw/ and writes data/processed/. You can always start over.
If the answer is no, the result is not reproducible (someone else cannot rerun it and get the same table) — and an irreproducible finding about public money is not a finding, it is a rumour.
.isna()null, Python Noneint64, float64, object (text)"n/a" as a numberclip()A small weather frame with gaps, and a series with one value that doesn't
belong. You'll count, drop, fill, fence and cap — and defend each choice in writing.
~45 minutes, in the portal’s lab page: fill each ____, press Run, then Check.
isna().sum(), dropna(), fillna(), quantiles and
the IQR fence, z-scores, clip() for capping, duplicates, and
to_numeric(errors="coerce").
Median vs mean on the outlier series, and a groupby().transform() fill
that beats the global mean.
isna().sum() per column, and ask why each hole exists.
Neither is free. Group-aware fills invent more carefully.
The IQR rule finds candidates; whether 98 is a typo or a typhoon is your call.
Raw stays raw, steps live in code, and every change gets a written why.
One sentence: cleaning is a chain of judgment calls — make each one on purpose, in code, out loud.
Your data is trustworthy. Next week it gets combined, reshaped and shrunk.
pandas User Guide — "Working with missing data": the full story behind
NaN, isna, dropna and fillna.
pandas.pydata.org
Any explainer of Tukey's boxplot and the 1.5×IQR whiskers — five minutes, and Part B's fences become a picture.
Both are linked on the course page beside this deck and the lab.
The docs read faster once you've already met the gaps and the 98.
Integration, transformation and reduction — merging sources, reshaping columns, and making big data small enough to think about.
DS 227 · Knowledge Discovery in Data