Lecture 3: Data munging

Applied Statistics: From Data to Decisions

Professor Madeleine Udell

Monday, April 6, 2026

concept check from last time

df.describe() says the Airbnb price column has 3,818 values

df.shape says the DataFrame has 4,229 rows

Q: where did the other 411 rows go?

• .describe() silently skips NaN — 411 listings have missing prices
• always compare .describe() counts against .shape

Quick concept check, ~2 min. This tests the key EDA skill from lec02: notice when counts don’t match. The answer: .describe() drops NaN values from its count. 411 listings have no price. This is the EDA workflow in action: shape → types → distributions → missing data. It also sets up today’s lecture: “We noticed the missing data last time. Today we deal with it.”

a cell range dragged one row too short. austerity budgets for millions of people.

Reinhart & Rogoff (2010) — Excel error discovered by Thomas Herndon, UMass Amherst

The hook. Carmen Reinhart and Kenneth Rogoff published a 2010 paper arguing GDP growth drops sharply when government debt exceeds 90% of GDP. This influenced austerity budgets across Europe and the US. Three years later, a grad student trying to replicate the results for a class assignment discovered an Excel formula selected only 15 of 20 countries — five were excluded because someone dragged a cell range one row too short. The corrected result: -0.1% growth became +2.2%. Show the chart — red bars are R&R original, blue bars are corrected. The “Above 90%” category is the entire basis for the austerity argument.

the spreadsheet

=AVERAGE(L30:L44) — but the data goes to row 49

Show the actual spreadsheet. The highlighted formula selects L30:L44 but the data continues to L49. Five countries — Australia, Austria, Belgium, Canada, Denmark — are excluded. The corrected average flips from -0.1% to +2.2%. A grad student found this trying to replicate results for a homework assignment. Pause and let students absorb: this error shaped fiscal policy.

three data disasters

Reinhart-Rogoff (2010)

cell range one row too short

→ austerity budgets for millions

Public Health England (2020)

Excel row limit: 65,536

→ 15,841 COVID cases lost, contacts never traced

gene naming (2016–2020)

SEPT1 → “1-Sep”

→ 20% of genomics papers corrupted, 27 genes renamed

all three: a tool made a decision without warning

Three disasters, one pattern: a tool silently changed data. Excel truncated rows. Excel converted gene names. Excel excluded countries from an average. In each case, the tool proceeded as if nothing happened. This motivates the entire lecture: every cleaning decision changes the answer, and invisible decisions are the most dangerous.

every cleaning decision changes the answer

today’s concepts

• missing data: the many forms
• joining datasets: inner vs. left, one-to-many
• string standardization + deduplication
• three approaches to handling missing data
• AI gotchas in data cleaning

Orient students. These five topics map to the five parts of the chapter. Each one is a place where a reasonable-seeming default produces a wrong answer.

recap: what we found last time

• Airbnb data: relatively tidy, NaN for missing values
• pandas handled it transparently: .isna(), .describe()
• but we noticed some dtypes looked wrong…

today: real datasets are rarely so cooperative

Brief recap, ~1 min. Connect to lec02 where students first saw the Airbnb data. The transition: “That was the easy case.”

1. the many faces of missing data

today’s dataset: College Scorecard

• US Department of Education
• 7,703 institutions, 122 columns
• SAT scores, enrollment, earnings, debt
• data dictionary, methodology, public updates

. . .

contrast: a random CSV on Kaggle with no documentation

Introduce the dataset. Emphasize provenance: this dataset has documentation, methodology, yearly updates. “Before you clean a dataset, ask where it came from.” The Scorecard is trustworthy because we can verify its methodology. A random Kaggle CSV? No way to know if it was collected, curated, or generated by an AI.

data provenance

data provenance

the chain of custody from data collection to your notebook

• documented methodology? ✓
• data dictionary defining every column? ✓
• known collection procedures? ✓
• public and versioned? ✓

in the AI era: synthetic datasets, undocumented web scrapes, training data contamination

Definition after motivation. Students just saw a concrete example of good provenance (Scorecard) and bad provenance (random Kaggle CSV). Now the definition lands.

data types

type	examples	pandas dtype
continuous	4.2, \(\pi\), price	float64
discrete	0, 4, 994, enrollment	int64
nominal	apple, banana, “Public”	object / str
ordinal	rarely, sometimes, often	category
text	doctor’s note, review	object / str
identifier	UNITID, ZIP code, OPEID	looks numeric, isn’t

Q: which type is MD_EARN_WNE_P10 (median earnings)? what dtype does pandas assign it?

Quick taxonomy slide, ~1 min. The key point: pandas dtypes don’t always match the semantic type. An earnings column should be continuous (float64), but if it contains “PrivacySuppressed,” pandas stores it as str. An identifier like UNITID looks numeric but arithmetic on it is meaningless. This sets up the next slide.

a column that should be numeric

MD_EARN_WNE_P10 — median earnings 10 years after enrollment

dtype: str

Q: why would an earnings column be stored as a string?

Pause after showing the dtype. Let students guess. Common answers: “formatting”, “dollar signs”. The real answer is more interesting.

the culprit: PrivacySuppressed

Non-numeric values in 'MD_EARN_WNE_P10':
PrivacySuppressed    480

• a string sitting in a numeric column
• cohort too small → Department of Education suppresses data to protect privacy
• pandas treats the whole column as str

This is the first “aha” — a single string value in 480 rows forces the entire column to be non-numeric. Every arithmetic operation, every plot, every model will fail or give wrong results.

the missingness zoo

encoding	where you’ll find it	pandas detects it?
NaN, None	standard pandas	yes
"" (empty string)	CSV exports	no
"N/A", "NA"	manual data entry	sometimes
"PrivacySuppressed"	government data	no
-999, 99, 0	sensors, surveys	no
absent rows	panel/time-series	no rows to detect

chapter 2 covered row 1. everything else requires active detection.

Show the full zoo. The key point: NaN is the easy case. Most missingness hides in plain sight.

Demo: College Scorecard exploration

open in Colab

scorecard = pd.read_csv('college-scorecard/scorecard.csv')
scorecard.dtypes.value_counts()

Switch to notebook. Show: load data, check dtypes, find non-numeric values in MD_EARN_WNE_P10, count PrivacySuppressed across all columns (7 columns affected). ~5 min.

classify each scenario as MCAR, MAR, or MNAR:

1. a lab assistant drops a tray; 12 random blood samples are destroyed
2. older patients are less likely to complete a follow-up survey (age is recorded)
3. College Scorecard suppresses earnings when the cohort is too small

1 min individual. 1 min compare with a neighbor. 2 min class.

DISCUSSION: Classification exercise (4 min) Answers: (1) MCAR — the breakage is unrelated to any variable. (2) MAR — missingness depends on age, which we observe. (3) MNAR — missingness depends on cohort size, which is related to the value we’re trying to measure (small schools have systematically different earnings). If stuck: “Ask yourself: does the reason for missingness depend on the missing value itself?” Key insight: Only MNAR is truly dangerous — no observed variable can fully correct for it.

missingness is not random

Show the bar chart from the book: suppression rate by school size. Nearly a third of the smallest schools have suppressed earnings; among 2K+ schools, it’s about 1%. This is MNAR — missingness depends on the value itself (small cohort → suppressed → small school characteristics).

three patterns of missingness

MCAR / MAR / MNAR

• MCAR: no pattern — mold destroys random records
• MAR: depends on observed data — older patients skip surveys
• MNAR: depends on the missing value itself — sickest patients don’t report

College Scorecard earnings: MNAR

small cohort → suppressed → small schools are systematically different

Define the three patterns after students have seen the concrete example. The ambulance example from the book: missing heart rate often means EMTs were too busy saving the patient. MNAR is the hardest — no observed variables can fully correct for it.

2. joining datasets

why join?

the Scorecard comes in two files:

• scorecard.csv — one row per institution (SAT, enrollment, earnings)
• field_of_study.csv — one row per school-program pair (earnings by major)

richer questions require combining tables

• “do engineering programs pay more than humanities at the same school?”
• “should I take on more debt for a prestigious school?”

Motivate joins with a real question students care about. Two files, one question that requires both. ~1 min.

inner join vs. left join: a toy example

Students

student	major_id
Alice	101
Bob	102
Carol	103

Majors

major_id	major_name
101	Physics
102	English
104	History

Q: which students survive an inner join on major_id?

Show both tables. Pause and ask students to predict. Carol (103) has no match in Majors. History (104) has no match in Students.

inner join: only matching rows

Inner join result

student	major_id	major_name
Alice	101	Physics
Bob	102	English

• Carol is gone (no match for 103)
• History is gone (no match for 104)
• only IDs in both tables survive

diagram: Wickham & Grolemund, R for Data Science

Reveal the result. Emphasize: inner join silently drops rows. Carol disappears and pandas doesn’t warn you. The R4DS diagram on the right shows the same idea with colored keys — matching keys (1, 2) are kept, non-matching keys (3, 4) are dropped.

left join: keep all left rows

Left join result

student	major_id	major_name
Alice	101	Physics
Bob	102	English
Carol	103	NaN

• all three students survive
• Carol’s major becomes NaN (no match)
• History still dropped (not in left table)

Left join preserves all rows from the left table. The NaN is a signal — “this student has no matching major.” That signal is lost in an inner join.

one-to-many: row multiplication

Left (2 rows)

left_id	key
L1	A
L2	A

Right (3 rows)

key	right_val
A	X
A	Y
A	Z

. . .

Result: 2 × 3 = 6 rows

left_id	key	right_val
L1	A	X
L1	A	Y
L1	A	Z
L2	A	X
L2	A	Y
L2	A	Z

every key matches → inner join = left join here. the difference only shows when keys are missing.

This is the key surprise. Joins can GROW your data, not just shrink it. 2 left rows × 3 right matches = 6 output rows. Since every key in the left table appears in the right table, inner and left join give the same result here — the distinction only matters when some keys are unmatched. This is intentional when joining institutions to programs — but a disaster when you think the key is unique and it isn’t.

defense: check your key before joining

assert scorecard['UNITID'].is_unique, \
    "UNITID is not unique — join will multiply rows!"

an assert that passes is documentation

an assert that fails is an alarm

This is a critical habit. One line of code prevents the most common join disaster. The assert pattern generalizes: after every cleaning step, assert what you expect.

• scorecard: 7,703 institutions
• programs: 228,000 rows across 5,500 institutions
• predict: how many rows will the inner join produce?
• more or fewer than 7,703?

1 min think. 2 min share. 2 min class.

DISCUSSION: Predict-then-reveal (5 min) If stuck: “Each institution matches to many programs. Stanford alone has 180+ programs.” Key insight: The inner join produces ~216,000 rows — a massive expansion because each institution is replicated once per program. Students who predicted “fewer” were thinking about inner joins as filters. The join is one-to-many.

Demo: join explosion

open in Colab

inner = pd.merge(scorecard, programs, on='UNITID', how='inner')
print(f"Before: {scorecard.shape[0]:,} → After: {inner.shape[0]:,}")

Switch to notebook. Show: the join key overlap (how many UNITIDs in common), the inner join row explosion (7K → 216K), the left join preserving all scorecard rows, the row count bar chart. Compare inner vs left. ~5 min.

after every join: check row counts

print(f"Scorecard rows:  {scorecard.shape[0]:,}")
print(f"Inner join rows: {inner.shape[0]:,}")
print(f"Left join rows:  {left.shape[0]:,}")

	gained rows?	lost rows?	what happened?
inner	yes (one-to-many)	yes (unmatched keys)	both at once
left	yes (one-to-many)	no	NaN for unmatched

the join type is a decision, not a default

Reinforce the key principle. Neither join type is “right” — each gives a different answer, and the choice should be deliberate.

when is a duplicate not a duplicate?

US voter registration: 100M+ records across 50 states

match on name + date of birth → 800,000 apparent duplicates

Q: are these really the same person?

Goel, Meredith, Morse, Rothschild & Shirani-Mehr, “One Person, One Vote,” APSR 2020

Transition from joins to deduplication. Source: Goel et al., “One Person, One Vote: Estimating the Prevalence of Double Voting in U.S. Presidential Elections,” American Political Science Review, 2020. 100M+ voter records, 800K name+DOB matches. Sounds like massive fraud — but is it?

the birthday paradox strikes

141 registered voters named “John Smith” born in 1970

• expected matching-birthday pairs: \(\binom{141}{2} \times \frac{1}{365} \approx 27\)

• actual matching pairs found: 27

exactly what chance predicts — no fraud, just combinatorics

This is the birthday paradox at scale. With 141 people sharing a common name, you expect ~27 pairs to also share a birthday. After applying the birthday paradox correction across all name groups, the 800K “duplicates” shrink to ~20K, then to ~0 after further error correction. The lesson: deduplication requires domain knowledge, not just string matching. A “match” on two fields isn’t evidence of a true duplicate without understanding the base rate of coincidental matches.

deduplication is a value judgment

• merge too aggressively → remove legitimate distinct voters
• merge too conservatively → count the same person twice

the same tradeoff appears everywhere:

• patient records across hospitals
• customer records across systems
• College Scorecard branch campuses (same OPEID6, different UNITID)

Connect back to the Scorecard. University of Phoenix has dozens of UNITIDs but one OPEID6. Whether you treat them as one institution or many depends on your question. The voter file example is from the Obama 2008 campaign — several staff found themselves registered in multiple states after moving.

3. when tools betray you

three approaches to missing earnings

same question: “what is the average median earnings?”

approach	method	result
drop rows	dropna(subset=['earnings'])	same mean, fewer rows
fill with mean	fillna(mean_val)	same mean, shrunken std
ignore NaN	pandas default (.mean() skips NaN)	same mean, full DataFrame

Q: if they all give the same mean, why does it matter?

Show results from the book. Approaches 1 and 3 give identical means but different downstream effects: dropping rows removes those schools from ALL columns, not just earnings. Mean imputation preserves sample size but distorts the standard deviation and all correlations. The “why it matters” comes next.

the damage shows in group comparisons

dropping rows with missing earnings changes which schools remain

Show the bar chart: % missing by school type. For-profit schools have far more missing data. Drop those rows and your “cleaned” dataset is biased toward public and nonprofit schools. This is informative missingness — the absence of data tells you something about the institution.

an AI assistant “cleans” the College Scorecard by calling .dropna()

• it reports the average earnings is $45,000
• name two questions you’d ask before trusting that number
• focus on what was lost in the cleaning step

2 min think. 3 min pair. 2 min class.

DISCUSSION: Think-pair-share (7 min) If stuck: “How many rows survived? Which types of schools were dropped? What was the missingness rate?” Key insight: The questions to ask are: (1) how many rows did you drop? (2) are the dropped rows systematically different? (3) which approach did you use and why? This is the critical evaluation skill.

Stanford: a case study in hidden complexity

the Scorecard website says Stanford median earnings = $136,000 (4yr after graduation)

but program-level data tells a different story:

program	credential	earnings (4yr)
Business Admin	Master’s	$262K
Computer Science	Master’s	$256K
Human Biology	Bachelor’s	$82K
English Literature	Bachelor’s	$82K
Ethnic Studies	Bachelor’s	$46K

one number. five very different realities.

This is the moment students connect personally. The Scorecard website says $136K, which sounds great. But that aggregate hides a 6x range: $46K to $262K. The aggregate is dominated by large programs and graduate degrees. Two caveats students should know: (1) only tracks Title IV aid recipients — many Stanford students pay without federal aid, so they’re invisible to the Scorecard. (2) Only 10 of Stanford’s 51 bachelor’s programs even report 4yr earnings — the rest have too few Title IV recipients. MS&E isn’t “PrivacySuppressed” — it’s simply absent, likely classified under “Engineering, Other” (CIP 1499, $115K) but there’s no way to confirm. The opaque CIP code mapping is itself a data quality problem.

when coercion erases distinctions

imagine an income column with these non-numeric values:

value	meaning
"PrivacySuppressed"	cohort too small
">100,000"	top-coded (Census)
"<LOD"	below limit of detection
"Refused"	survey respondent declined
"N/A"	question didn’t apply

pd.to_numeric(errors='coerce') turns all five into NaN

five different reasons for missingness → one undifferentiated blank

This is the real problem with errors=‘coerce’: not that it creates NaN (NaN is fine when you expect it), but that it collapses multiple DISTINCT reasons for missingness into a single indistinguishable value. “PrivacySuppressed” means small cohort. “>100,000” means the value is KNOWN to be high but capped. “Refused” means the person actively withheld. These carry very different implications for analysis. After coercion, all that context is lost. The defense: always run value_counts() BEFORE coercing, so you know what you’re erasing.

sentinel values

-999 and 999999 aren’t missing — they’re wrong

Mean WITH sentinels:    -208.6°F
Mean WITHOUT sentinels:   72.9°F

unlike NaN, sentinel values participate in every computation without warning

Quick example. A temperature sensor records -999 when it malfunctions. The mean is wildly wrong, and nothing raises an error. The only defense: know your data dictionary, check for impossible values.

the identifier trap

OPEID 00102100 → stored as integer → becomes 102100

• leading zeros vanish
• later join against zero-padded strings: zero matches, no error

ZIP codes, phone numbers, IDs — codes, not numbers

Quick point. Identifiers that look like numbers but aren’t. Arithmetic on them is meaningless. Storing them as integers loses leading zeros and breaks downstream joins.

spot the bugs

this code merges two tables and computes mean earnings:

merged = pd.merge(scorecard, programs, on='UNITID')
mean_earn = merged['EARN_MDN_4YR'].mean()

how many problems can you find?

Show the code. Give students 10 seconds to read it. Then advance to reveal the question and transition to the discussion slide.

the code on the previous slide has at least four bugs

• find as many as you can
• for each, explain what goes wrong and how to fix it

small groups, 3 min. then 2 min share-out.

DISCUSSION: Analyze-this (5 min) Correct answers — at least four problems: (1) No how= specified — defaults to inner join, silently dropping unmatched schools. (2) No assert on key uniqueness — one-to-many join will replicate institution rows, inflating the mean toward schools with many programs. (3) No pd.to_numeric(errors='coerce') — EARN_MDN_4YR contains strings (PrivacySuppressed, blanks) that block .mean(). (4) No check of how many rows survived — could be computing mean from a biased subset. Bonus: the join includes all credential levels (Bachelor’s through Doctoral), mixing very different populations. If stuck: “What kind of join is this? How many rows will it produce?”

why we write code

spreadsheets hide logic

• formula in cell G47 excludes rows
• dragged range stops one row short
• no one notices unless they click every cell

code makes decisions visible

• every drop, coerce, join is a readable line
• when wrong, the bug is findable
• when right, the logic is reproducible

good code is deterministic. same script, same data → same answer every time.

The Reinhart-Rogoff error was a formula hidden in a cell. The PHE error was an invisible row limit. Code makes every decision visible, reviewable, and reproducible.

AI analysis vs. AI-assisted code

	AI analyzes data directly	AI helps you write code
cleaning decisions	hidden	visible as lines you can read
reproducibility	different answers each session	deterministic
what you learn	nothing about the data	everything about the data

use AI to write code, not to replace understanding

This is the course philosophy. AI is a powerful tool for writing code. But uploading a CSV and asking for a summary skips the critical thinking. A 2025 study found ChatGPT gives inconsistent results on the same data — different factor structures, different conclusions, across sessions.

data munging checklist

1. check provenance — where did the data come from?
2. check types — any str columns that should be numeric?
3. find the missing data — NaN, sentinels, string placeholders, absent rows
4. understand why data is missing — MCAR, MAR, MNAR?
5. check joins — row counts after every merge. assert.
6. standardize strings — lowercase, strip, collapse variants
7. verify type conversions — how many values became NaN?
8. compare groups — is missingness evenly distributed?

This is the checklist from the book. Students should internalize this as a workflow they run every time they encounter a new dataset. Each item connects to a concrete example from today’s lecture.

“the combination of some data and an aching desire for an answer does not ensure that a reasonable one can be extracted from a given body of data.”

— John Tukey

Let this sit for a moment. The quote captures the entire lecture: cleaning decisions matter, tools can betray you, and AI always produces something — but something is not the same as something correct. John Tukey (1915-2000) was one of the most influential statisticians of the 20th century — he coined “bit”, invented the FFT algorithm with Cooley, and pioneered exploratory data analysis.

synthesis

• missing data hides in many forms — NaN is just the easy case
• joins can grow or shrink your data — the type is a decision
• every cleaning choice changes which observations enter your analysis
• code makes decisions visible — spreadsheets and AI bury them
• domain knowledge has no substitute — choosing the right metric, spotting leakage, interpreting missingness

Revisit the agenda items with takeaways. Each bullet maps to a content block. This is the “what you should remember” version of the lecture.

next time: linear algebra

we can clean and join data. now we need a language for modeling it.

• vectors as data points, vectors as features
• linear combinations → predictions
• column space → what a model can reach

read: Chapter 4 in the course notes

Forward pointer. “We’ve spent two lectures getting data ready. Now we build the mathematical machinery to do something with it.” The transition from Act 1 data prep to Act 1 modeling.

before next class

• read Chapter 3 in the course notes
• try the notebook exercises on your own
• Quiz 1 is Wednesday — covers Lectures 1-3

one-minute feedback

1. what was the most useful thing you learned today?
2. what was the most confusing?

give feedback

Last 1-2 minutes. Anonymous. QR code goes to the same form. Entry field pre-filled with “3” for lecture number.