how-to-EDA-using-codex-python

Input - schema + business context

Everrest is a B2B2C retail platform: 400 merchants sell to 20,000 consumers across six Southeast Asian markets. This time the category team is not asking "what's growing" - they are asking "can we trust the platform's data before we build this quarter's dashboards on it?" Eight tables come over from a fresh data pipeline. The discipline is refusing to trust any of them until the keys, types and joins have been proven.

Generate sample data - a fresh set of planted quirks

The generator reuses the canon Everrest schema (M0007 the bulk-wholesale outlier and the weekend + November seasonality stay in as real background) but plants a different six defects from the Claude edition - the kind that hide across joins, not inside a single column. Each is documented in the generator docstring, so Step 5 becomes a recall test.

import numpy as np
SEED = 42; rng = np.random.default_rng(SEED)

# Quirk 1: ~2.5% of line items reference a non-existent product_id.
orphan = rng.choice(df.index, size=int(0.025 * len(df)), replace=False)
df.loc[orphan, "product_id"] = [f"P9{i:04d}" for i in range(len(orphan))]

# Quirk 2: stale Sep-Nov snapshot - unit_price frozen at 0.82x catalog.
df.loc[stale, "unit_price"] = np.round(df.loc[stale, "unit_price"] * 0.82, 2)

# Quirk 3: PH payments logged in local time -> paid_ts ~8h BEFORE order_ts.
df.loc[ph, "paid_ts"] = df.loc[ph, "paid_ts"] - pd.Timedelta(hours=8)

# Quirk 5: fabricated merchant reports round-hundred amounts (Benford red flag).
df.loc[fab, "amount"] = rng.choice([100,200,300,400,500,600,700,800,900], fab.sum())

Objective - a trust question, not a technique

The Claude edition asked "what should we act on this quarter?" This one asks the question that has to come first: can the data even be trusted yet? Every chart in Step 5 serves one of these five sub-questions; anything that does not change a decision gets cut.

Find-skills - Codex assembles its own toolbox

This is the differentiator. Before writing a line of analysis, Codex activates its agent skills to find the right tools, then pulls open-source profiling, validation and forensic libraries. The output is still a plain Python script that runs anywhere - the toolbox is how it got written well, not a runtime dependency.

Code + charts - the real run

One sectioned script: the profiling head start, then a data-quality & integrity gate, then the business questions on the cleaned data. 19 visuals, every PNG rendered at 300 DPI from the actual run, every title a finding rather than a technique. Grab the real code below - one click, copy, run.

The 10-minute head start: one profiling call

The sweetviz report on the enriched line-item table earns its keep before any custom code: price & category show 2% missing (that is the orphan-product join failing), price_gap has a median of 0 with a negative tail (the stale-price drift), and product_id shows 7,696 distinct values where only 5,000 products exist (the fake orphan ids). Three defects surfaced from a single call - then confirmed one by one below.

# Gate: referential integrity - do the foreign keys resolve?
prod_ids = set(products.product_id)
oi_orphan = ~order_items.product_id.isin(prod_ids)
orphan_value = order_items.loc[oi_orphan, "net"].sum()

# Normalize dirty category labels BEFORE any rollup.
mcat = merchants.assign(category_clean=lambda d: d.category.replace(CLEAN_CAT).str.strip())

# Timezone check: payment latency must never be negative.
pay["lat_h"] = (pay.paid_ts - pay.order_ts).dt.total_seconds() / 3600
neg = pay.lat_h < 0   # 100% of these are PH -> local-time logging

Within-table looks clean. Duplicate-row and null rates are near zero on every table - which is the trap. The real defects live across joins, not inside single columns.

The gaps hide on JOIN. order_items is 100% complete on its own columns - so a single-table check passes while 2.5% of its product keys are quietly broken.

Broken keys. 2.5% of line items reference a non-existent product; 1% of orders a non-existent customer. $154k of line value cannot be attributed. Add a pandera gate.

8 categories, 14 labels. 'electronics', 'Beauty ', 'Grocary' split every rollup. $557k of revenue sits under variant labels a naive GROUP BY would miss.

Stale prices. 30% of line items sit below the unit=catalog diagonal - a Sep-Nov snapshot frozen at 0.82x, understating $385k of revenue in that window.

Paid before ordered. A whole region shows paid_ts ~8h before order_ts - every case is PH, logged in local time. $475k of GMV has untrustworthy timestamps.

Benford holds - platform-wide. Real payment amounts follow the expected first-digit curve, which is exactly what makes a merchant that breaks it detectable.

The fabricator. M0333 'Apex Gadget Bazaar' reports 100% round-hundred amounts vs near-zero elsewhere - reported payments differ from its line items by $45k. Fraud review.

Double-counted people. 800 customers exist twice under different ids with identical signup, channel and region - 3.8% of the base. Every CAC number is overstated.

Concentration - after cleaning. Rank #1 is the M0007 bulk outlier, not a hero merchant. Read the platform only once it is excluded.

Skew. Order values span four orders of magnitude - report medians; the mean is dragged by the wholesaler.

The tier that isn't. Enterprise's long upper tail is essentially one merchant (M0007), not a tier-wide behaviour.

No hidden driver. Order value moves with basket size and nothing else - no time-of-day price effect to chase.

Rhythm (canon). November runs 2.1x baseline, weekends lift ~40%. Commit Q4 capacity by September.

Prime time. Weekend evenings are the platform's busiest window - staff and promote into it.

Category health - on clean labels. Read only after normalization; Grocery's scale is M0007, not organic demand.

Retention. Month-1 is where customers are lost - the retention play starts in week 2, not month 3.

Geography. PH revenue is real and sizeable - but until its timestamps are fixed, no PH conversion-time metric can be trusted.

Expert review - a check that changes the code

A panel of five senior reviewer agents - each with 10+ years in data, data insights, and business - tore into the first pass (eda_codex_v1.py, kept in the repo). v1 ran clean and looked plausible, which is what made it dangerous. Every fix below was applied and the pipeline re-ran; the charts above are the post-review v2.

# v1 (before): group on the RAW label - dirty variants split the rollup
rev = orders.groupby("category").order_value.sum()
#   Grocery   $1,590,140      <-- these are the
#   Grocary   $   56,115      <-- SAME category, split in two
#   Beauty    $  613,796  |  Beauty  $198,134   <-- and again

# v2 (after): normalize first - 14 labels collapse to 8, buckets are whole
mcat["category_clean"] = mcat.category.replace(CLEAN_CAT).str.strip()
rev = orders.groupby("category_clean").order_value.sum()   # +$557k re-bucketed