Real data arrives in eight tables, three date formats, and a thousand blanks. pandas joins, groupbys, and datetime features on Olist e-commerce — and how to spot the leak that makes a model look brilliant and ship useless
By the end of tonight you'll have both the concept and the working code — every objective maps to a cell you run yourself.
Olist ships as eight CSVs: orders, items, payments, reviews, customers, sellers, products, geolocation. One row per order with the right columns is the deliverable — and a join that silently triples your rows is the trap.
Late-delivery rate by state, review score by delivery delay, subscription rate by past outcome. EDA is not making charts — it is interrogating the data before the model does.
Missing completely at random, missing at random, missing not at random — three patterns in plain English, and why the third one is a feature rather than a problem.
Call duration predicts a bank deposit with AUC 0.91 — and is useless, because you only know it after the call ends. Three real leaks, and the one question that catches all of them.
A business question, a dataset, a model — and the judgment to evaluate it.
We open with the two remaining Python for Everybody decks — functions (Ch. 4) and loops (Ch. 5) — because tonight's pandas code is loops and functions in disguise. Then the uncomfortable truth: in Session 1 the data came as one clean table. It never does. Tonight is about the 80% of the job that happens before fit().
About 100,000 Brazilian e-commerce orders from 2016–2018 across orders, items, payments, reviews, customers, sellers, products and geolocation. Business question: which orders will arrive late — and will the customer leave a one-star review?
Orders join to items one-to-many: an order with three items becomes three rows, and your late-delivery rate is now weighted by basket size. The fix is a habit: print the row count before and after every merge, and use validate='one_to_one' to make pandas refuse a bad join.
Stretch, coffee, compare row counts with a neighbor.
groupby, agg, and pivot_table as interview tools. Late-delivery rate by customer state, review score by days late, and datetime features pulled from the purchase timestamp: weekday, hour, month, and days-to-estimate. Every chart ends with a sentence a manager could act on.
Bank Marketing: predicting whether a customer subscribes to a term deposit. Add call duration and AUC jumps from 0.76 to 0.91 — and the model is worthless, because duration is only known after the call in which the customer said yes. Three real leaks, one test: would you know this value at the moment you have to predict?
The wrangling checklist: grain, join, validate, aggregate, datetime, missingness, leakage. Homework #1 applies it end to end on Telco with a baseline model.
Notebooks, readings, explorers, and the stories behind the methods.
The notebook we build in class: load Olist via kagglehub (or the no-login Telco fallback), validate every merge, aggregate items to the order grain, pull datetime features, interrogate missingness, and reproduce the call-duration leak on Bank Marketing.
Open in ColabThe live notebook from our meeting, cell for cell: comparisons and if/elif, try/except on a bad int(), a first function, while and for loops, the largest-so-far pattern, then pandas on the Titanic passenger list — head, dtypes, shape, column selection, means, isnull().sum(), and filtering rows. Outputs cleared so you get them by running. Four questions at the end.
Open in ColabDrag the moment a prediction is made and watch which columns exist yet, how good a real model looks, and whether there is still a decision left to make — on a bank's phone campaign (AUC 0.73 → 0.93 once the call length leaks in) and on the hotel bookings from the competition. Tonight's one question, made draggable.
Explore nowThirteen short exercises on the four Python chapters from Sessions 1–2, each with a check cell that prints ✓ or tells you what is off. Due Mon Oct 5, 11:59 PM; completion credit under Participation & in-class labs.
Explore nowSeven case files on the Telco data, one per part of Homework 1: the tampered TotalCharges column, groupby interrogations, engineered clues, the sign-up-day time machine, a model that beats the baseline, and framing your own prediction problem. Guess before you run, then a check cell prints ✓. 60–90 minutes; ends with a scorecard.
Open in ColabThe review page for Python for Everybody chapters 2–5: what we covered, what you should be able to do, one worked example per chapter on the churn problem, and the slides embedded.
Explore nowRoughly 100,000 real orders from a Brazilian marketplace, 2016–2018, in eight linked tables with a published schema diagram. Requires a free Kaggle account (two minutes to create with your suffolk.edu email); the notebook has a no-login fallback on Telco if you would rather not.
Open on KaggleThe official reference for merge(), including the validate argument that turns a silent row explosion into a loud error. Read the 'Checking for duplicate keys' section twice.
Read the guideWes McKinney wrote pandas; this is his free online book. Chapters 7 and 8 are the definitive treatment of missing data, joins, reshaping, and groupby.
Read online (free)Caruana et al. (KDD 2015): a model trained on hospital data concluded asthma patients had a lower risk of dying from pneumonia — because asthma patients were rushed to intensive care. The data was right, the model was right, and deploying it would have killed people. The classic story of a signal that is really a leak.
Read the paperThe library's own page on leakage during preprocessing and how Pipelines prevent it. The bridge from tonight's warning to next week's fix.
Read the pageA short, free tutorial with two worked examples of target leakage and train-test contamination. Twenty minutes, and you will never look at a suspiciously high score the same way.
Open the tutorialProf. Arslan's earlier notebook on matplotlib and seaborn — histograms, pair plots, bar plots. If tonight's charts went fast, this is the slow version. Hosted in the course repo — opens in Colab with one click.
Open in ColabOpen the notebook and build it with us.
Build the Olist modeling table at the order grain: one row per order, total basket value, item count, customer state, purchase weekday and hour, and the target — delivered after the estimated date. Every merge is validated and counted.
orders is one row per order; order_items is one row per item. Print both shapes and the number of unique order_id in each. That difference is the trap.
groupby('order_id') on items to get item_count and basket_value. Now items is one row per order and the join is safe.
orders.merge(items_agg, on='order_id', how='left', validate='one_to_one'). Print the row count before and after. They must match.
late = delivered_date > estimated_date. Then weekday, hour, and month from the purchase timestamp. Check the late rate overall and by customer state.
import pandas as pd, kagglehub, os
path = kagglehub.dataset_download('olistbr/brazilian-ecommerce')
orders = pd.read_csv(os.path.join(path, 'olist_orders_dataset.csv'), parse_dates=[
'order_purchase_timestamp', 'order_delivered_customer_date', 'order_estimated_delivery_date'])
items = pd.read_csv(os.path.join(path, 'olist_order_items_dataset.csv'))
print(len(orders), orders.order_id.nunique()) # one row per order
print(len(items), items.order_id.nunique()) # many rows per order!
items_agg = (items.groupby('order_id')
.agg(item_count=('order_item_id', 'size'), basket_value=('price', 'sum'))
.reset_index())
df = orders.merge(items_agg, on='order_id', how='left', validate='one_to_one')
assert len(df) == len(orders)
df['late'] = (df.order_delivered_customer_date > df.order_estimated_delivery_date).astype(int)
df['weekday'] = df.order_purchase_timestamp.dt.dayofweek
df['hour'] = df.order_purchase_timestamp.dt.hour
print(df['late'].mean()) Notice which columns you are not allowed to use: the actual delivery date builds the target, so anything derived from it — days late, carrier hand-off date — is a leak. The clock stops at order_purchase_timestamp. Everything the model sees must exist at that moment.
45,211 phone calls from a Portuguese bank's deposit campaign; 11.7% said yes. Fit the same logistic regression twice — once with call duration, once without — and watch a worthless feature produce a spectacular score.
Age, balance, job, prior-campaign outcome and the rest. Cross-validated AUC lands around 0.76.
Add the length of the call. AUC jumps to about 0.91. A manager would be thrilled.
When do we need the prediction? Before dialing, to choose whom to call. Is call duration known then? No. Delete it, and explain why the 0.91 was a lie.
The UCI documentation for this dataset says it outright: duration 'highly affects the output target' and 'should be discarded if the intention is to have a realistic predictive model.' Most datasets don't come with that warning. Your job is to write it yourself.
The deliverable of wrangling is a modeling table: one row per prediction unit, features known at prediction time, one target column. Get the grain right and everything downstream gets easier.
Joins lie silently. Aggregate the many side first, merge with validate=, and print the row count before and after. A model trained on tripled rows is a model trained on a different population.
Leakage is the #1 way smart people build useless models. Freeze the clock at the moment of decision and delete every column the clock hasn't reached — call duration, refund amount, days late.
Complete these before next Monday — they are the practice that makes the next session land.
Exam-style questions for the ideas in this session — Data & Leak-Proof Features. Every wrong answer names the misconception behind it.
Encoding, scaling, imputation and feature engineering as one scikit-learn Pipeline that fits on training data only — so leakage becomes structurally impossible. Plus skrub's TableVectorizer, the 2025 shortcut.