Lab 01 · Verified AI-Assisted EDA Sprint

Your job is not to find those defects by reading this page; it is to run an assistant-accelerated EDA that surfaces all three, then ship a findings memo a stakeholder could act on. This is the verification discipline that separates "uses AI" from "trusts AI" — the 2025 Stack Overflow survey's top frustration is AI output that is almost right.

Time & tier

~90 minutes hands-on · Core tier.

Setup

Works on a laptop, Colab, or Kaggle — no GPU, no API spend. You need Python 3.10+, pandas, numpy, scikit-learn, plus any AI assistant (Claude, ChatGPT, Copilot Chat, or an agentic tool like Claude Code/Cursor).

Save and run this generator. It is deterministic (seed 42), so your numbers will match the Verification section exactly.

"""make_messy_orders.py — deterministic 200k-row orders dataset with planted defects.
Requires: pandas, numpy. Run: python make_messy_orders.py -> orders_raw.csv"""
import numpy as np
import pandas as pd

rng = np.random.default_rng(42)
N_CUST, N_ORD = 40_000, 200_000

first = np.array("Asha Ravi Meera Karan Divya Arjun Sneha Vikram Priya Rahul".split())
last = np.array("Sharma Iyer Patel Reddy Nair Gupta Das Mehta Kulkarni Singh".split())
cust_id = np.array([f"C{100000+i}" for i in range(N_CUST)])
name = rng.choice(first, N_CUST) + " " + rng.choice(last, N_CUST)
email = np.char.add(np.char.replace(np.char.lower(name.astype(str)), " ", "."),
                    np.char.add(".", np.char.lower(cust_id.astype(str))))
email = np.char.add(email, "@example.com")

# 1,200 customers get a doppelganger id whose email differs only by
# case/whitespace and whose name has a trailing space.
dup_src = rng.choice(N_CUST, 1200, replace=False)
dup_id = np.array([f"C9{str(i).zfill(5)}" for i in range(1200)])
cust_id = np.concatenate([cust_id, dup_id])
name = np.concatenate([name, name[dup_src] + " "])
email = np.concatenate([email, np.char.add(np.char.upper(email[dup_src].astype(str)), " ")])

idx = rng.integers(0, len(cust_id), N_ORD)
cat = rng.choice(["electronics", "apparel", "home", "beauty", "sports"], N_ORD,
                 p=[.30, .25, .20, .15, .10])
price = np.round(rng.gamma(2.2, 900, N_ORD) + 99, 2)
qty = rng.integers(1, 6, N_ORD)
disc = rng.choice([0, 5, 10, 20, 30], N_ORD, p=[.45, .2, .15, .12, .08])
ship = rng.integers(1, 15, N_ORD).astype(float)
ship[rng.choice(N_ORD, 4000, replace=False)] = np.nan  # ordinary missingness
day = rng.integers(0, 365, N_ORD)
dates = (pd.Timestamp("2025-01-01") + pd.to_timedelta(day, "D")).strftime("%Y-%m-%d").to_numpy(dtype=object)

p = .06 + .10 * (cat == "apparel") + .04 * (cat == "electronics") \
    + .003 * disc + .008 * np.nan_to_num(ship, nan=7)
returned = (rng.random(N_ORD) < p).astype(int)

# refund ticket is opened AFTER a return happens (2% noise)
leak = returned.copy()
flip = rng.choice(N_ORD, int(.02 * N_ORD), replace=False)
leak[flip] = 1 - leak[flip]

# 3,000 rows get unparseable date strings
bad = rng.choice(N_ORD, 3000, replace=False)
dates[bad[:1000]] = "2025-02-30"
dates[bad[1000:2000]] = "31/11/2025"
dates[bad[2000:]] = "20251399"

pd.DataFrame({
    "order_id": [f"O{i:07d}" for i in range(N_ORD)],
    "customer_id": cust_id[idx], "customer_name": name[idx], "email": email[idx],
    "order_date": dates, "category": cat, "unit_price": price, "quantity": qty,
    "discount_pct": disc, "ship_days": ship, "refund_ticket_opened": leak,
    "returned": returned,
}).to_csv("orders_raw.csv", index=False)
print("wrote orders_raw.csv:", N_ORD, "rows")

Run it once, confirm the CSV exists, then do not re-read the generator. From here on you work only with orders_raw.csv, exactly as you would with a client extract of unknown provenance.

Guided steps

1. 1Frame the task for the assistant. The business question: "What drives product returns, and can we predict returned at order time?" Prompt: "You are assisting with EDA on orders_raw.csv (200k rows, columns listed below). Before any modeling, produce a data-quality audit plan: what checks would you run on identifiers, dates, categoricals, numerics, and the target? Output a numbered checklist." Paste in df.dtypes and df.head(3). You want its plan, not its conclusions — you will execute the checks.
2. 2Execute the profiling with generated code. Ask the assistant to write one profiling script covering: null counts, dtype coercion failures, cardinality of identifiers, duplicate detection on raw and normalized keys, and per-column value ranges. Run it yourself. Anything the assistant claims without a number you reproduce is treated as unverified.
3. 3Date audit. Prompt: "Write code that attempts pd.to_datetime(df.order_date, format='%Y-%m-%d', errors='coerce') and reports the count and distinct values of unparseable entries." A generic .describe() will not surface these; strict-format parsing will.
4. 4Entity audit. Prompt: "Emails should uniquely identify customers. Write code that normalizes email (strip, lowercase) and reports normalized emails mapped to more than one customer_id, plus examples." Inspect five example pairs by hand — decide whether they are true duplicates (same person, two IDs) or collisions.
5. 5Leakage audit. Prompt: "For each feature, compute a fast association with returned (Cramer's V or point-biserial). Flag anything suspiciously high and explain why a feature could be leakage given that we predict at order time." Interrogate the top hit: could refund_ticket_opened exist before the return event exists? Ask the assistant to argue both sides, then decide yourself.
6. 6Prove the leak empirically. Fit two quick logistic regressions (or GBMs) on a clean subset — one with the suspect column, one without — using a random 80/20 split. Record both AUCs. A feature that alone moves AUC to ~0.98 is not a feature; it is the label wearing a costume.
7. 7Clean and re-analyze. Drop the leak, coerce dates (decide and document: drop vs impute the 3,000 bad rows), and collapse duplicate entities onto a canonical customer key. Then answer the actual business question: return rate by category, discount band, and shipping delay.
8. 8Write the findings memo yourself (assistant may critique, not draft): one page — three business findings with numbers, a defect log (what, how found, how fixed, row counts affected), and the caveats a downstream modeler must know.

Verification

Objective checks against the seeded ground truth — your numbers should match:

• •
  Strict-format date parsing yields exactly 3,000 unparseable rows across three corruption patterns (2025-02-30, 31/11/2025, 20251399).
• •
  refund_ticket_opened agrees with returned on 98% of rows; AUC with the column is ≥0.97, without it lands near 0.55–0.60.
• •
  Normalized email mapping finds ≥1,100 emails attached to two customer_ids (1,200 doppelgangers were planted; a few receive no orders).
• •
  Your cleaned analysis reports apparel as the highest-return category and return rate rising with ship_days.

Completion artifact

eda-findings.md — findings memo + defect log + link to your reproducible notebook/script. Self-grading rubric (pass = 8/10):

• Defect coverage (0–3): one point per planted defect found with correct magnitude.
• Empirical proof (0–2): leak demonstrated with a with/without model comparison, not just correlation.
• Verification discipline (0–2): at least four assistant claims independently re-executed; noted in the memo.
• Decision quality (0–2): date and duplicate handling decisions stated with rationale and affected row counts.
• Business readability (0–1): a PM could act on the three findings without asking you anything.

Stuck?

• Assistant insists the data is clean. It profiled with lenient parsing. Force strict format strings and normalized keys — steps 3–4 exist precisely because default profilers miss both defects.
• refund_ticket_opened looks like a legitimately great feature. Ask the temporal question: what is knowable at prediction time? Any feature that post-dates the outcome must go.
• Colab memory pressure. 200k rows is small; if you imported everything as objects, pass dtype hints or use low_memory=False deliberately.

Client-safe variant

On client data, run the identical protocol with the client-approved assistant (Bedrock/Azure-hosted, or a local model via Ollama) and never paste raw rows into a personal free-tier chat — schema, aggregates, and synthetic examples only. The planted-defect drill above is also a safe onboarding exercise for client teams: regenerate with a different seed and use it to calibrate how much your team over-trusts AI profiling.

Answer key (don't read before attempting)

• Leaky column:refund_ticket_opened — created by copying returned and flipping 2% of rows. Detection: 0.98 agreement; AUC ~0.99 with it, ~0.55–0.60 without.
• Corrupted dates: 3,000 rows split evenly across 2025-02-30 (impossible day), 31/11/2025 (wrong format + impossible day), 20251399 (unparseable). Detection: strict pd.to_datetime(..., format="%Y-%m-%d", errors="coerce").
• Duplicated entities: 1,200 customers duplicated under C9xxxxx IDs; emails differ only by upper-casing and a trailing space; names carry a trailing space. Detection: group by email.str.strip().str.lower() and count distinct customer_id.