Day 66 · The data kitchen

pandas II — Wrangling

You will be able to

Summarize data by group with groupby-agg, including named multi-aggregations
Join tables with merge, and audit every join with validate= and indicator=
Reshape between wide and long with pivot_table and melt, and say when each shape serves
Parse and exploit datetimes with pd.to_datetime and the .dt accessor
Combine inconsistent multi-source tables into one tidy dataset

Today's ~120 minutes

Spaced-rep warm-up: Day 65 cards (loc/iloc, chained indexing)	10 min
ELI5 + tech read: split-apply-combine, join audits, reshape	20 min
Guided: merge like an auditor + groupby/pivot	40 min
Practice: the three-supplier mess	22 min
Project: wrangle.py pipeline	18 min
Quiz + flashcards	10 min

Builds on: Day 65 — DataFrames, selection & dtypes · Day 37 — SQL joins & data modeling

The analogy

🧸 ELI5⚙ Tech

Three suppliers deliver to your restaurant every morning, and none of them agree on anything. One labels crates by date, one by week; one writes "tomatoes", another "Tomato (red)"; one bills in dollars, another in cents. Before any cooking happens, the kitchen must merge the deliveries into one inventory, group them by ingredient, and reshape the pile so the chef can read it at a glance. That unglamorous back-of-house work is data wrangling, and it is most of the job.

Three moves run the kitchen. Groupby is sorting the delivery into labeled bins and summarizing each bin — "total kilos per vegetable" — split, apply, combine. Merge is matching two lists against each other — the delivery manifest against your supplier contacts — and it inherits every join subtlety you met in SQL on Day 37, including the quiet catastrophe: if a supplier appears twice in the contacts list, every matched crate DUPLICATES. Reshape is turning the same numbers sideways: a long list of (date, product, amount) rows becomes a wide month-by-product grid for humans to read — pivot — and back again for machines to process — melt. The chef never sees this work. The chef only notices when it wasn't done.

Why this matters on the job

Real data never arrives as one clean table — it arrives as exports from three systems with different keys, date formats, and opinions. The capstone's ingest pipeline (Day 119), every eval-results-joined-to-metadata analysis (Day 137), and the classic FDE morning ("here are CSVs from our CRM, billing, and support tools — what's going on?") are all wrangling. The join-audit habits you build today — validate=, indicator=, count rows before and after — are the difference between an analysis and a confident wrong answer: a silent row explosion double-counts revenue in a way nobody notices until the customer does.

Guided practice

guided 1

Merge like an auditor

20 min

Paste the starter. It builds two seeded tables the way two real systems would export them: orders (400 rows) and customers (60 rows) — and deliberately plants two problems: three orders reference a customer_id that does not exist, and one customer_id appears TWICE in customers (a dirty CRM export).
Record len(orders). Now do the naive thing: left-merge orders with customers and print the new length. It GREW — the duplicated customer matched some orders twice. Revenue is now double-counted for that customer.
Redo the merge with validate="m:1" and watch pandas raise MergeError immediately. This one keyword would have caught the bug before it shipped.
Fix the right table (customers.drop_duplicates(subset="customer_id")), merge again with validate="m:1" and indicator=True, and value_counts the _merge column: 397 both, 3 left_only — the orphaned orders, found instead of silently NaN-filled.
Write the three-line join checklist in your notes: row counts before/after; validate= always; indicator= when anything might not match.

🐍 python — editable, runs in your browser

import numpy as np
import pandas as pd

rng = np.random.default_rng(seed=66)
n_orders, n_cust = 400, 60

customers = pd.DataFrame({
    "customer_id": np.arange(1, n_cust + 1),
    "region": rng.choice(["NA", "EU", "APAC"], size=n_cust, p=[0.5, 0.3, 0.2]),
    "tier": rng.choice(["free", "pro", "enterprise"], size=n_cust, p=[0.5, 0.35, 0.15]),
})
# dirty CRM: customer 7 exported twice
customers = pd.concat([customers, customers[customers.customer_id == 7]],
                      ignore_index=True)

orders = pd.DataFrame({
    "order_id": np.arange(10_000, 10_000 + n_orders),
    "customer_id": rng.integers(1, n_cust + 4, size=n_orders),  # some ids beyond 60!
    "amount": np.round(rng.lognormal(4.0, 0.8, size=n_orders), 2),
})

print("orders before merge:", len(orders))
naive = orders.merge(customers, on="customer_id", how="left")
print("after naive merge:  ", len(naive), " <- row explosion!")

try:
    orders.merge(customers, on="customer_id", how="left", validate="m:1")
except Exception as e:
    print("validate caught it:", type(e).__name__)

clean_cust = customers.drop_duplicates(subset="customer_id")
audited = orders.merge(clean_cust, on="customer_id", how="left",
                       validate="m:1", indicator=True)
print(audited["_merge"].value_counts())

Ctrl/⌘+Enter runs · Tab indents · numpy/pandas/sklearn auto-load on import (torch and network calls need a local run)

guided 2

Groupby, time, and the pivot

20 min

Extend the audited merge: add an order_date column of string dates (the starter generates 180 days of them) and parse with pd.to_datetime. Prove the danger first: sort the STRING version of "2024-2-1" vs "2024-10-1" style dates and watch alphabetical order lie.
Derive month = df.order_date.dt.to_period("M") and dow = df.order_date.dt.day_name().
Named aggregation: revenue, order count, and mean order value by region — one .agg() call with three named outputs. Then two-key groupby (region × tier) and reset_index() to flatten.
Build the executive grid: pivot_table(index="month", columns="region", values="amount", aggfunc="sum"). Read one cell aloud so the meaning sticks ("EU revenue in July was…").
Melt the pivot back to long form and confirm (after sorting) it matches the groupby result — pivot and melt are inverses.
Use transform once: add a column with each region's mean order value on EVERY row, then flag orders above their own region's average. Note how transform differs from agg (same length as input vs one row per group).

🐍 python — editable, runs in your browser

Ctrl/⌘+Enter runs · Tab indents · numpy/pandas/sklearn auto-load on import (torch and network calls need a local run)

On your own

The three-supplier mess

22 min

Three monthly exports arrive as DataFrames (build them seeded, ~50 rows each, in your script): supplier A has columns ["date", "product", "qty", "unit_price"] with ISO date strings; supplier B has ["Day", "Item", "Quantity", "PriceCents"] — dates as "03/15/2024", prices in CENTS; supplier C has ["date", "product", "total_cost"] only, with product names in UPPERCASE and some duplicate rows.

Goal: one tidy table ["date", "product", "total_cost"] (datetime dtype, lowercase product names, dollars), deduplicated, then: total spend per product per month (groupby) and a month × product pivot grid. Assert the final row count equals the sum of the three inputs minus the duplicates you dropped, and assert total spend equals the hand-computable sum from each source.

Hints: rename columns with a dict; pd.to_datetime(col, format="%m/%d/%Y") for supplier B; cents / 100; .str.lower().str.strip() for names; concat then drop_duplicates. Log the row count after every step — the wrangler's flight recorder.

Ship before you stop

wrangle.py — the auditable pipeline

Refactor the three-supplier practice into wrangle.py with one function per stage: load_sources() (returns the three seeded frames), standardize(df, mapping) (renames, retypes, normalizes strings and units), combine(frames) (concat + dedupe with counts logged), and summarize(tidy) (the groupby and pivot). Every stage logs rows-in → rows-out via the logging module (Day 16), and the module ends with four asserts covering row counts, dtypes (order_date is datetime64), no duplicates, and totals matching the sources. Add one deliberate test: corrupt a date in source B and confirm the pipeline fails loudly rather than sorting wrongly. Commit — Day 70's checkpoint report imports this pipeline shape.

Rubric — check what you completed (0/5)

Four stage functions, each pure (frame in, frame out) and individually runnableEvery stage logs rows-in/rows-out; final asserts all passUnit conversion (cents→dollars) and date parsing verified by an assert on totals and dtypesThe corrupted-date test fails loudly with a clear errorCommitted to the practice repo

Common mistakes & misconceptions

Merging without validate= and getting silent row explosion from duplicate keys. One duplicated key double-counts every matched row — assert the multiplicity you believe in.
Using an inner join and never noticing the dropped rows. indicator=True plus a value_counts of _merge makes losses visible.
Sorting or grouping unparsed date strings — alphabetical order is not chronological order. pd.to_datetime first, always.
Forgetting groupby returns one row per group. Reaching for original-row context afterward needs transform (broadcast back) or a merge, not wishful indexing.
Reaching for .apply(lambda …) when a vectorized column op or .str/.dt accessor exists — a hidden Python loop with Day 64's 100× tax.
Concatenating frames with mismatched column names and not checking for the NaN blocks it silently creates. Standardize names before concat.

Knowledge check

Q1. You left-merge 1,000 orders against a customers table and get 1,013 rows. What happened?

Q2. Which call reshapes a long (month, region, revenue) table into a month × region grid?

Q3. A column of date STRINGS like "2024-9-1" and "2024-10-1" is sorted without parsing. What goes wrong?

Go deeper — curated resources

docspandas User Guide — merge, join, concatenate & compare; groupby ↗30 min docs10 minutes to pandas — merge/grouping/reshaping sections ↗15 min courseKaggle Learn — Pandas (grouping & joining lessons) ↗30 min

If you have a third hour

Tidy Data (Hadley Wickham's paper, concept) — One variable per column, one observation per row, one table per entity type. The vocabulary behind "melt to tidy" — ten minutes that organizes your instincts for every reshape decision.

Done means

Row explosion reproduced, then caught with validate="m:1"; orphans found via indicator
Named aggregation, pivot, melt round-trip, and one transform all executed
Three-supplier mess tidied with asserts on counts and totals passing
wrangle.py committed with logged stages; quiz ≥ 2/3

How this connects

← Back: merge is Day 37's SQL joins executed in memory — same inner/left semantics, same duplicate-key hazards. groupby-agg is what your Day 42 SQL GROUP BY did, and every column op is still Day 64's vectorized NumPy.

Forward →: Day 67 does EDA on tidy data — which today's pipeline produces. Day 70's report and Day 83's churn project both start with a wrangle stage, and the capstone's ingest (Day 119) is this pipeline pattern with documents instead of orders.

Unlocks: D67 Exploratory Data Analysis · D68 Cleaning & Validation · D70 Week 10 Checkpoint: EDA Report · D78 Clustering