warningcategorical-value-fragmentation
What it is
A single real-world category is split across many surface forms — misspellings, abbreviations, punctuation, and inconsistent capitalization ("Chevrolet" / "Chevy" / "Cheverolet", "Gray" / "Grey" / "grey") — so grouping or counting the raw field scatters one category across dozens of values. Counts are understated, rankings shuffle, and the true leader can stay hidden until the variants are merged. The tell is a distinct-value count far larger than the domain could plausibly contain.
How to spot it
Flag group-by, counting, or ranking over a free-text categorical field with high cardinality relative to the real number of categories, a long tail of low-frequency values, and near-duplicate strings (small edit distance, case-only differences, trailing whitespace). In code, look for `value_counts()`, `GROUP BY make`, or `COUNT(*)` on a raw text column with no preceding normalization (`.str.lower().str.strip()`, fuzzy clustering, or a lookup table). Be suspicious when the distinct-value count is implausible for the domain (e.g. hundreds of "vehicle makes").
Pitfall
`SELECT make, COUNT(*) FROM tows GROUP BY make ORDER BY COUNT(*) DESC` is run over a field with 899 distinct values including "Chevy", "Cheverolet", "Chevolet", "MAZDA", and "Mazada". Honda and Ford appear far smaller than they really are, and Volkswagen — spelled 36 different ways — never reaches the top 25.
Done well
The field is normalized first (trim, lowercase, then cluster near-duplicates with a fingerprint/Levenshtein method and apply a human-reviewed mapping table), so the 899 forms collapse to ~336 real makes before counting. After merging, Honda and Ford rise materially (+46% and +36%) and Volkswagen lands at 11th — a corrected ranking the raw counts had hidden.
How to fix it
Normalize categorical text before any group-by: standardize case and whitespace, then consolidate variants with clustering (OpenRefine fingerprint, Levenshtein) plus a human-reviewed mapping. Re-check the distinct-value count against a plausible domain size, and keep the mapping as an auditable artifact rather than editing values in place.
Avoiding Data Pitfalls (Wiley, 2020), Pitfall 2A: The Dirty Data Pitfall
errorcharacter-encoding-corruption
What it is
Text is read with the wrong character encoding — typically a UTF-8 file decoded as Latin-1/Windows-1252 or vice versa — so multi-byte characters turn into garbled sequences ("café" → "café", "naïve" → "naïve"). The corruption is silent: the pipeline runs without error, but accented names, currency symbols, and non-Latin scripts are mangled, which breaks joins on text keys and misrepresents the data. This is not described in the book; it extends the domain's pipeline-failure theme.
How to spot it
Flag file reads with no explicit encoding (`open()` / `pd.read_csv` defaulting to the platform locale), and scan string columns for tell-tale mojibake — sequences like "é", "ü", "’", "ñ", or literal replacement characters (U+FFFD "�"). Watch for a byte-order mark (BOM) leaking into the first column name, text keys that fail to join despite looking similar, and double-encoded UTF-8. In data review, scan distinct values of name/address/label fields for accented-gibberish patterns.
Pitfall
A UTF-8 export of customer names is read with `pd.read_csv('names.csv', encoding='latin-1')`. "José" becomes "José" and "Łukasz" is mangled; a later join to an orders table on the (correctly encoded) name key drops every customer with an accented name.
Done well
The file is read with its actual encoding declared — `pd.read_csv('names.csv', encoding='utf-8')` (using `utf-8-sig` if a BOM is present) — so "José" and "Łukasz" survive intact. Encoding is fixed and verified at ingestion, and text is Unicode-normalized (e.g. NFC) before being used as a join key.
How to fix it
Always specify the encoding when reading and writing text; detect it explicitly (e.g. with chardet) rather than trusting platform defaults, and standardize on UTF-8 end to end. Handle BOMs (`utf-8-sig`), normalize Unicode before comparing or joining on text, and add a check that scans for mojibake markers so corruption fails loudly at ingestion rather than surfacing downstream.
Avoiding Data Pitfalls (Wiley, 2020), Pitfall 2: Technical Trespasses (domain extension — not a specific sub-pitfall example in the book) · Joel Spolsky (2003), 'The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)'
warningjoin-fan-out
What it is
A join is performed on a key that is non-unique on one or both sides, so each row matches multiple partners and the result set multiplies. Aggregates computed afterward (`SUM`, `COUNT`, `AVG`) then double-count the duplicated rows and inflate totals — a "fan trap." The row count silently grows instead of shrinking, the mirror image of a key-mismatch drop. This extends the book's join discussion, which covers non-matching keys but not row-multiplying duplicate keys.
How to spot it
In code, flag joins where the key is not known to be unique on at least one side — `pd.merge` with no cardinality check, a SQL `JOIN` to a table with duplicate keys, followed by `SUM`/`COUNT` over the result. Look for missing uniqueness assertions (`validate='one_to_many'` in pandas, primary-key or `DISTINCT` checks), a post-join row count larger than the driving table, and aggregates that drift upward once a join is added. Be suspicious of joining two fact-grain tables directly.
Pitfall
An `orders` table (one row per order) is joined to `shipments` on `order_id`, but orders with multiple partial shipments now appear several times. `SELECT SUM(order_total) FROM orders JOIN shipments USING (order_id)` counts each multi-shipment order's total once per shipment, overstating revenue.
Done well
The grain is reconciled before aggregating: either aggregate `shipments` to one row per `order_id` first, or sum `order_total` from `orders` alone and attach shipment facts separately. The merge runs with an explicit cardinality check (`pd.merge(..., validate='one_to_many')`) and the post-join row count is compared to the driving table, so unexpected multiplication fails loudly.
How to fix it
Establish the grain (the unit one row represents) of each input and the intended grain of the output before joining. Assert key uniqueness on the side that should be unique, aggregate the many-side to the target grain before joining, and compare row counts before and after. Treat any unexplained increase in row count as a fan-out until proven otherwise.
Avoiding Data Pitfalls (Wiley, 2020), Pitfall 2: Technical Trespasses (domain extension — not a specific sub-pitfall example in the book) · Ralph Kimball & Margy Ross, The Data Warehouse Toolkit (3rd ed., Wiley, 2013) — join grain and many-to-many fan traps
warningjoin-key-mismatch-row-loss
What it is
Two datasets are joined on a key whose values are represented differently across sources — spelling, punctuation, word order, abbreviations, or an entity present in one source and absent from the other ("St. Kitts & Nevis" vs "St. Kitts and Nevis", "Hong Kong" vs "Hong Kong SAR, China"). An inner join silently drops every unmatched row; a left or outer join keeps the rows but leaves nulls in the joined columns. Either way records vanish or go blank with no error, and downstream totals, rates, and rankings are computed on an incomplete table.
How to spot it
In code, flag joins, merges, and lookups on free-text or name keys with no normalization beforehand — `JOIN ... ON a.country = b.country`, `pd.merge(how='inner')`, Excel `VLOOKUP` — together with the absence of any match-rate or coverage check. Look for missing before/after row-count comparisons, no anti-join to enumerate non-matching keys, and aggregates run straight off the joined table. In prose, flag combining two sources "by name" with no mention of how unmatched names were handled.
Pitfall
Website traffic (180 country names from Google Analytics) is inner-joined to World Bank population on the country name. 34 of the GA countries have no exact match ("Bahamas" vs "Bahamas, The"; Taiwan absent entirely), so they silently drop out — and 3 of the top 25 countries by "views per 1,000 people" disappear from the result with no warning.
Done well
Before joining, the analyst compares the key sets both ways (a Venn / anti-join), finds the 34 non-matching names, normalizes or maps them to a common standard (and adds Taiwan's population by hand), then joins and verifies the post-join row count and match rate. The corrected join retains every intended country and the ranking is complete.
How to fix it
Treat the join key as data to be cleaned: normalize representations (case, punctuation, known aliases) or map to a stable code (e.g. an ISO country code) rather than a display name. Inspect the non-matching rows with an anti-join, compare row counts before and after the join, and report the match rate so silent drops and null fills surface before you analyze the merged table.
Avoiding Data Pitfalls (Wiley, 2020), Pitfall 2B: Bad Blends and Joins
warningoverloaded-missing-sentinels
What it is
A single field encodes "missing" in several incompatible ways — an empty string, a literal "?" or "Unknown", and out-of-range numeric sentinels like 0, -1, or 99 — and these are then conflated, counted as real categories, or fed into arithmetic. Different reasons for absence (not asked, asked-but-unknown, not applicable) get merged into one bucket, and numeric sentinels silently distort sums, means, and extremes.
How to spot it
Flag fields that mix blanks with placeholder tokens ("?", "N/A", "Unknown", "none") or that contain suspicious numeric sentinels (0, -1, 999, 99) inside an otherwise continuous range. In code, look for these values passing through with no explicit `na_values=[...]` mapping, sentinel tokens surviving into `value_counts()` / `GROUP BY`, or numeric sentinels included in `AVG` / `SUM` / `MIN`. Watch for two different missing markers folded into one, or a placeholder ranked as though it were a real category.
Pitfall
A `color` field holds real colors plus blanks and a separate "?" entry, and a `year` field uses 0 for unknown. The pipeline reads blank and "?" as the same "missing" bucket (losing the distinction between left-blank and recorded-as-unknown) and leaves the 0s in, so `MIN(year)` returns 0 and the average is dragged toward zero.
Done well
The loader declares the sentinels explicitly (`pd.read_csv(..., na_values=['', '?', 'Unknown', '0'])`) and keeps semantically different kinds of missing in distinct flags where the difference matters (blank = not recorded vs "?" = recorded-as-unknown). Numeric sentinels are converted to null before any arithmetic, so aggregates ignore them instead of treating 0 as a real year.
How to fix it
Inventory every value that means "missing" in each field and map them to true nulls at load time, while preserving meaningful distinctions between kinds of missingness in separate indicators. Never leave numeric sentinels (0, -1, 999) in a column you will sum, average, or take extremes of, and confirm placeholders don't survive into category counts.
Avoiding Data Pitfalls (Wiley, 2020), Pitfall 2A: The Dirty Data Pitfall
warningsilent-null-drop
What it is
Records with null or blank values in a field are filtered out — explicitly, or by a function that skips them by default — before computing a proportion, rate, or average, and the result is then read as representative of the whole. This silently assumes the missing records share the distribution of the observed ones. When missingness is not random (e.g. one source or site logs the field and another does not), the surviving subset is biased and the headline numbers are wrong.
How to spot it
In code, flag null filtering ahead of an aggregate that gets generalized — `dropna()`, `WHERE x IS NOT NULL`, `df[df.x.notnull()]`, `COUNT(x)` standing in for `COUNT(*)`, or reliance on functions that skip nulls (`mean()` with default `skipna=True`, SQL `AVG`) — especially when the null share is large. Check whether the analysis ever quantifies the missing fraction or tests whether missingness correlates with another field (source, site, date). In prose, flag "we excluded the blank/unknown rows" with no argument that they were missing at random.
Pitfall
28.5% of vehicle-color records are null. The analyst runs `df.dropna(subset=['color'])` and reports the color breakdown of the remainder as the demand mix — not realizing almost all the nulls come from a single tow yard, so the surviving rows over-represent the other yard and skew every color's share.
Done well
The analyst first measures the null rate and cross-tabs missingness against source, finding the nulls are concentrated at one yard (missing-not-at-random). They scope the analysis to the relevant yard (where only 0.8% is null), state that assumption explicitly, and report coverage ("we have color for 97.3% of records at this yard") instead of silently dropping a quarter of the data.
How to fix it
Before dropping nulls, quantify how much is missing and test whether missingness is associated with source, time, or group. If it is not random, don't filter blindly — segment, impute with a stated model, or scope and caveat the claim to the population actually observed. Always report the share excluded and the assumption that justifies excluding it.
Avoiding Data Pitfalls (Wiley, 2020), Pitfall 2A: The Dirty Data Pitfall
warningsilent-type-coercion
What it is
A value's data type is implicitly converted on load or during processing, changing its meaning without raising an error — leading zeros stripped when an identifier is read as an integer (ZIP "02134" → 2134), numeric-looking strings auto-cast, dates or booleans inferred from ambiguous text, or large IDs losing precision as floats. The data looks fine until a join, sort, or format silently breaks. The book's two-digit-year case is one instance; this generalizes the failure mode to any implicit coercion.
How to spot it
Flag readers and casts that infer types implicitly — `pd.read_csv` without `dtype`/`converters`, spreadsheet import with auto-detection, bare `int()`/ `float()` over identifier columns. Look for code that treats codes/IDs (ZIP, FIPS, SKU, phone, account or order numbers) as numeric, joins or comparisons that fail because one side became numeric, and large integer IDs stored as float. In data, watch for identifier columns missing leading zeros or collapsed to a uniform numeric width.
Pitfall
A CSV of addresses is loaded with `pd.read_csv('addresses.csv')`. Pandas infers the `zip` column as int64, so "02134" becomes 2134 and every New England ZIP loses its leading zero. A later join to a ZIP reference table on the string key silently matches nothing for those rows.
Done well
Identifier columns are read as strings with explicit types — `pd.read_csv('addresses.csv', dtype={'zip': str})` (or `converters`) — so "02134" stays "02134". Columns that are codes, not quantities, are typed as text up front, and the join key's type is asserted equal on both sides before merging.
How to fix it
Declare types explicitly at every boundary instead of relying on inference. Treat identifiers (ZIP, FIPS, SKU, phone, account, order IDs) as strings, never numbers; pin dtypes on import; and verify that join keys share a type before merging. Validate that identifier widths and leading zeros survive the round trip.
Avoiding Data Pitfalls (Wiley, 2020), Pitfall 2: Technical Trespasses (domain extension — generalizes the two-digit-year example in Pitfall 2A) · Ziemann, Eren & El-Osta (2016), 'Gene name errors are widespread in the scientific literature,' Genome Biology 17:177 — spreadsheet auto-coercion of identifiers
warningtwo-digit-year-ambiguity
What it is
Two-digit year values (or other truncated date encodings) are stored without century information, then parsed or aggregated as though they were complete, so the century is guessed wrong or the raw 0–99 codes are treated as real years. Averaging such a column can yield an impossible result — a mean "vehicle year" of 23 — and naive parsing maps "11" to the year 11 or splits a single model year across two centuries. The fingerprint is a year field whose values live in 0–99, often in two disconnected clusters.
How to spot it
In code, flag date/year handling that depends on two-digit years without an explicit century-window rule — `%y` format strings, or hand-rolled arithmetic that adds 1900/2000 — and aggregations (`mean`/`min`/`max`/`AVG`) taken over a year column whose values fall in 0–99. In data, look for a "year" field ranging over 0–99, or mixing 0–99 with 1900–2099, and histograms with two separated groups (e.g. 0–17 and 82–99). In plain-English descriptions, watch for averages or trends over dates whose source format is two-digit and therefore ambiguous.
Pitfall
A `Year` column holds values like 99, 91, 4, and 11 (keyed variously as "'11", "11", "4"). The analyst computes `df['Year'].mean()` and reports a typical towed-vehicle year of 23 — a meaningless figure, because the two-digit codes were averaged as raw integers instead of being resolved to 1999, 1991, 2004, and 2011.
Done well
The two-digit codes are mapped to full years with an explicit, documented century window justified by the data's known span (00–17 → 2000–2017, 18–99 → 1918–1999), e.g. `year = 2000 + yy if yy <= 17 else 1900 + yy`. The corrected column now averages to a sensible ~2005, and the windowing rule is recorded alongside the data so it can be reviewed.
How to fix it
Never average, sort, or threshold a two-digit year as a raw integer. Resolve the century explicitly with a window rule grounded in the data's real range, parse with full-year formats (`%Y`) at the source where possible, and validate the output range so impossible future years or pre-automobile years are caught. Document the disambiguation so the transformation is reproducible.
Avoiding Data Pitfalls (Wiley, 2020), Pitfall 2A: The Dirty Data Pitfall
Data Pitfalls