Knowing Your Data

Foundations Cheatsheet

The first-15-minutes inspection ritual

Run this checklist on every new dataset, before any metric. Predict each answer before you look — a check against an expectation can fail; a check without one is just scrolling.

1. Row count — COUNT(*) / len(df). Does it match what you were told?
2. Column types — df.dtypes. Flag any object-typed column named like a number or date.
3. Head AND tail — first and last 10 rows. Hunt export footers, trailing nulls, summary rows.
4. Key uniqueness / grain — COUNT(DISTINCT key) vs COUNT(*). Mismatch = duplicates or wrong mental model. Resolve before proceeding.
5. Date min/max — every date column. Full expected coverage? Max date recent (pipeline not stale)?
6. Categorical distinct values — value_counts() on every low-cardinality column. Hunt case variants (active / Active), blanks, typos.
7. Numeric distributions — min / p25 / median / p75 / max (p25 and p75 are percentiles: the values 25% and 75% of rows fall at or below). Hunt negatives, impossible zeros, 100x unit errors.
8. Missingness profile — df.isna().mean(). Per column: how much, which story, concentrated in a segment?
9. Sentinel hunt — −999, −1, 1970-01-01, 1900-01-01, empty string, 'N/A', impossible zeros.

Grain

• Grain = "one row means one what?" — the first question of every analysis
• Verify empirically: SELECT COUNT(*), COUNT(DISTINCT key) FROM t; — equal means the key defines the grain
• Composite grains are common: (user_id, date) unique when neither column is alone
• Columns copied down from a coarser grain (e.g., order_total on line-item rows) sum incorrectly at the finer grain
• Long format: one row per entity-category; wide format: one row per entity, category per column — different grains, same information

The row-count habit

• Log the row count after every load, filter, and join; explain every change
• Every metric is a claim about rows kept and dropped — narrate what happened to your rows

Dtype traps

NULL semantics

• NULL is absence, not a value: NULL = NULL is unknown; use IS NULL
• Negative filters (<> meaning "not equal to", NOT IN) drop NULL rows from both sides — segments stop summing to totals
• NOT IN (subquery) returns zero rows if the subquery contains any NULL — use NOT EXISTS
• Aggregates skip NULLs: AVG has a smaller denominator than COUNT(*) — report n (the count of non-null values) with every mean
• Empty string '' is NOT NULL — it's an invisible category

The three missingness stories

• Missing ≠ zero: fill with 0 only when absent and zero mean the same thing for the question
• Report denominators: "avg 8.0 (n = 7 of 10)", never bare "avg 8.0"