The 6-Step EDA Blueprint: From Messy Table to Trustworthy Insights
You open the warehouse extract for the first time — 280 rows, 6 columns, 3 months of orders — and nothing looks clean. Dates are strings, one product category has a typo, two rows are exact duplicates, and a $12,000 "Office Supplies" order is clearly a data-entry error. Do you fix it by gut feel? No. You run 6 checks in order, like a pilot's pre-flight checklist. Skip one and the chart you send to leadership is wrong. Run all six and every chart after it is trustworthy. We will run the whole checklist on a tiny 10-row sample you can trace by hand — then the same code scales to 280 rows and to 280,000.
0. The Messy Sample We Will Clean Together
This is what pd.read_sql_query("SELECT * FROM orders", engine) might return before any cleaning — deliberately messy:
| id | customer_id | transaction_date | product_category | quantity | unit_price | total_amount |
|---|---|---|---|---|---|---|
| 1001 | C001 | 2024-06-22 | Electronics | 1 | 899.00 | 899.00 |
| 1002 | C002 | 2024-02-12 | Furniture | 5 | 80.00 | 400.00 |
| 1003 | C002 | 2024-02-12 | Furniture | 5 | 80.00 | 400.00 |
| 1004 | — | 2024-03-18 | NULL | 2 | 250.00 | 500.00 |
| 1005 | C003 | 2024-04-01 | Office Supplies | 4 | 120.00 | 480.00 |
| 1006 | C004 | 2024-06-22 | Office Supplies | 80 | 150.00 | 12000.00 |
| 1007 | C001 | 2024-06-28 | Apparel | 3 | 45.00 | 135.00 |
| 1008 | C005 | 2024-02-12 | Electronics | 1 | 1200.00 | 1200.00 |
Spot the issues: row 1002/1003 duplicate, row 1004 missing customer + category (yellow), row 1006 quantity 80 × $150 should be $12,000 but the unit price is clearly off by 10× — that 12000 is an outlier (red). Each step below catches one of them.
Step 1: Structural Audit — What Shape Is the Data?
Before any fix, ask: how big, what types, what memory? This is the inventory check. You catch that transaction_date arrived as object (string) not datetime, and that total_amount should equal quantity × unit_price but doesn't for row 1006.
print(df.shape) # (10, 7) — rows, columns
print(df.info()) # dtypes + non-null counts — date shows as object!
print(df.dtypes) # quick type scan
print(df.memory_usage(deep=True).sum() / 1024, "KB")
Output tells you: 10 rows × 7 columns, transaction_date object needs parsing, and df['total_amount'] != df['quantity']*df['unit_price'] for at least one row — flag it.
Step 2: Missing Data & Hygiene — Where Are the Holes?
One missing value can silently drop a whole group from a groupby. Quantify holes as percent per column, sorted worst first — then decide: drop, fill, or investigate.
# Percent missing per column, worst first
missing = (df.isnull().sum() / len(df) * 100).sort_values(ascending=False)
print(missing)
# customer_id 10.0 ← row 1004
# product_category 10.0 ← row 1004
# transaction_date 0.0
# ...
# Hygiene fixes for the sample:
df = df.drop_duplicates() # removes duplicate 1002/1003 → 9 rows
df = df.dropna(subset=['customer_id']) # drop row with no customer (or fill with 'UNKNOWN')
print(f"After dedup + dropna: {df.shape[0]} rows") # 8 rows
This is Quiz 3 Q06 — that exact chained expression is the answer. Keep it as your first hygiene line in every notebook.
Step 3: Univariate Inspection — What Does Each Column Look Like Alone?
Run one summary per column type and read it like a story — not a dump.
print(df.describe()) # numeric: count / mean / std / min / Q1 / median / Q3 / max
print(df['product_category'].value_counts()) # categorical: how many per category
print(df['order_amount'].value_counts(bins=5)) # quick histogram in text
# Skewness check: is the distribution's tail on the right?
# If mean >> median, the mean is being pulled by a few huge orders.
print(f"mean={df['total_amount'].mean():.0f} median={df['total_amount'].median():.0f}")
# mean=589 median=440 → mean > median → right-skewed (Quiz 3 Q07, Q14)
# Rule: for skewed spend, report median as "typical" — mean misleads.
Right-skew vs left-skew: Right-skew = long tail to large values (most customers spend $100, a few spend $12,000 → tail to the right, positive skew). Left-skew is the mirror. The $12,000 order in row 1006 is what creates the right tail here.
Step 4: Outlier Detection — Tukey's IQR Rule (and When to Cap, Not Drop)
An outlier is not "a big number" — it is a value far from the middle 50% of the data. Tukey's rule formalizes that using the interquartile range:
# Picture 10 spend values sorted: [45, 90, 135, 400, 440, 480, 500, 899, 1200, 12000]
Q1 = df['total_amount'].quantile(0.25) # 25th percentile — 25% of orders are below this
Q3 = df['total_amount'].quantile(0.75) # 75th percentile — 75% are below this
IQR = Q3 - Q1 # middle 50% spread
lower_bound = Q1 - 1.5 * IQR # anything below this is an outlier
upper_bound = Q3 + 1.5 * IQR # anything above this is an outlier
outliers = df[(df['total_amount'] < lower_bound) | (df['total_amount'] > upper_bound)]
print(f"Q1={Q1:.0f} Q3={Q3:.0f} IQR={IQR:.0f} → bounds [{lower_bound:.0f}, {upper_bound:.0f}]")
print(outliers[['customer_id','total_amount']])
# customer_id total_amount
# 5 C004 12000 ← the $12K entry — flagged correctly
# Two ways to handle it — pick based on the business question:
# Option A: filter out (row disappears) — for "typical order" analysis
clean = df[(df['total_amount'] >= lower_bound) & (df['total_amount'] <= upper_bound)]
# Option B: cap to the bound (row stays, value trimmed) — for "keep every customer" reports
df['total_amount_capped'] = df['total_amount'].clip(lower=lower_bound, upper=upper_bound)
# C004's 12000 becomes upper_bound (e.g. 1420) — preserves row count, tames the tail
This is Quiz 3 Q02 (IQR = Q3−Q1), Q03 (upper = Q3+1.5·IQR), and Q09 (clip()). The boxplot from Module 2 draws these same bounds visually — the dots beyond the whiskers are these outliers.
Step 5: Bivariate & Categorical Relationships — How Do Columns Relate?
One column tells you "what". Two columns tell you "why". Two quick checks:
# Categorical × numeric: does spend differ by product category?
print(pd.crosstab(df['product_category'], df['customer_tier'])) # counts
print(df.groupby('product_category')['total_amount'].median()) # vs median spend
# Visual: one box per category — same IQR logic as Step 4, now split by group
import seaborn as sns
sns.boxplot(data=df, x='product_category', y='total_amount')
# Apparel median $135, Electronics median ~$899 — very different shapes
If "Office Supplies" median suddenly jumps because of the $12K row and you haven't handled Step 4 yet, the bivariate chart lies. Order matters — outliers first, comparisons second.
Step 6: Temporal Patterns — When Does the Business Peak?
Dates arrived as strings ("2024-06-22"). Until parsed, you cannot ask "which month sold most?" or "what hour had the most orders?"
df['transaction_date'] = pd.to_datetime(df['transaction_date']) # now datetime64
df['month'] = df['transaction_date'].dt.month # 2, 4, 6
df['weekday'] = df['transaction_date'].dt.day_name() # Monday, Saturday
df['hour'] = df['transaction_date'].dt.hour # if timestamps include time
print(df.groupby('month')['total_amount'].sum()) # revenue per month
print(df.groupby('weekday')['total_amount'].mean()) # avg spend per weekday
# The .dt accessor unlocks: .year .month .day .day_name() .hour .quarter .is_month_end
This powers Quiz 3 Q13 and the notebook's Task 2. It also feeds Step 5 — groupby month/hour then bar/line chart, exactly as the Sales EDA video does after this blueprint.
Checklist before any chart: shape + types → missing % → describe/median → IQR + clip decision → crosstab/boxplot → parse dates + groupby time. Run it in this order. The notebook Tasks 1–4 follow exactly this sequence for 280 rows.