warningdivide-by-tiny-denominator
What it is
A rate, ratio, or percent change is computed with a denominator that is zero or very small, producing an undefined result (division by zero → infinity/NaN) or an unstable, explosive value that swamps comparisons and rankings. Per-capita rates for tiny populations, percent changes off a near-zero base, and ratios over sparse counts all blow up and dominate "top N" lists with statistical noise. It extends the domain's rate-and-ratio theme but is not a specific example in the book.
How to spot it
In code, flag division that forms a rate/ratio without guarding the denominator — no zero check, no minimum-sample threshold — especially `(new - old) / old` percent changes and per-unit rates over small counts. Look for resulting inf/NaN values, or rankings topped by units with tiny denominators. In data, check the distribution of denominators for zeros and a long thin tail of very small values.
Pitfall
"Percent change in incidents" is computed per site as `(this_year - last_year) / last_year`. A site that went from 1 incident to 4 shows +300% and tops the "fastest-growing" list, while a site that went 0 → 5 yields a divide-by-zero (inf) — all driven by tiny baselines rather than meaningful trends.
Done well
Denominators are guarded and thresholded: sites below a minimum baseline are excluded or flagged, zero denominators are handled explicitly, and rates over small counts are shown with their uncertainty (or smoothed) — so the ranking reflects real movement rather than small-denominator noise.
How to fix it
Guard every denominator before dividing: handle zero explicitly, set a minimum-sample threshold for rates, and treat percent changes off a tiny base with caution. Report or smooth the uncertainty of rates built on small counts so noisy values don't dominate comparisons.
Avoiding Data Pitfalls (Wiley, 2020), Pitfall 3: Mathematical Miscues (domain extension — not a specific sub-pitfall example in the book) · Gelman & Price (1999), 'All maps of parameter estimates are misleading,' Statistics in Medicine 18 — instability of rates with small denominators
warningdropped-missing-categories
What it is
A category or period with no records — a true zero — is silently omitted from a chart or table instead of being shown as zero. On an ordinal/categorical axis the empty period simply doesn't appear (the gap is invisible and the slopes around it are distorted); switched to a continuous axis, a line is interpolated straight across the gap, implying nonzero values that never existed. Either way the absence is hidden. This is context-dependent: for events that legitimately occur only every few years, omitting or zero-filling can both be acceptable if chosen deliberately.
How to spot it
In code, flag series built from present records only (`value_counts`, `GROUP BY year`) and plotted without reindexing onto the complete domain of periods/categories — no `reindex`/`asfreq`/calendar spine, no explicit zero-fill. Look for ordinal date axes that can skip values, or continuous axes that connect across gaps. In data, check whether the sequence of periods has holes (e.g. missing years) the visualization never surfaces.
Pitfall
A timeline of Edgar Allan Poe's works by year is built only from years with at least one work, so 1826, 1828, and 1830 — years he published nothing — vanish from the axis entirely. The "fewest works" years look like 1824/1825, and a continuous-axis version even implies ~6–11 works in the missing years.
Done well
The series is reindexed onto every year from 1824 to 1849 with missing years filled at zero and shown explicitly (as discrete bars or points at zero), so the three nonproductive years are visible and the productivity pattern reads correctly — unless the domain is one where gaps are expected, in which case the handling is chosen deliberately and noted.
How to fix it
Decide explicitly how missing categories should render rather than accepting the software default. For dense series, reindex onto the full domain and show true zeros; for intermittent events, state that gaps are expected. Never let a continuous axis interpolate across a period that had no data.
Avoiding Data Pitfalls (Wiley, 2020), Pitfall 3B: Missing Values
warningembedded-total-rows
What it is
A data set contains pre-aggregated subtotal or "Total" rows mixed in with the detail rows — a "Total" sex alongside Male and Female, or a state-level row alongside its counties. Summing the measure naively then counts those records twice (or more), inflating the total by a factor of two, four, or worse. The duplication is silent because the total rows look like ordinary records.
How to spot it
In code, flag `SUM`/`COUNT` over a measure without first excluding aggregate rows — no `WHERE sex <> 'Total'`, no filter on a subtotal flag. In data, look for a categorical field containing a catch-all level ("Total", "All", "Both", or a state name standing in for "all counties") whose value equals the sum of the others, or a grand total that is a clean multiple (2x, 4x) of the plausible figure. Be suspicious when one category alone accounts for ~50% of a total.
Pitfall
California infectious-disease records include a "Total" row (Male + Female) for every county-year-disease and a "California" county row summing all counties. `SUM(Count)` returns 15,002,836 — double-counted by sex and again by county, four times the true ~3.74 million.
Done well
Aggregate rows are identified and excluded before summing — `WHERE Sex <> 'Total' AND County <> 'California'` — or the detail grain is selected explicitly, yielding the correct ~3.74 million. The total rows are kept only as a cross-check, never added to the detail.
How to fix it
Inspect categorical fields for subtotal/total levels before aggregating, and filter them out (or select only the base grain). Sanity-check grand totals against an independent estimate, and treat any category that equals the sum of its siblings — or a total that is a clean multiple of the expected value — as an embedded-total red flag.
Avoiding Data Pitfalls (Wiley, 2020), Pitfall 3C: Tripping on Totals
warningfloating-point-accumulation
What it is
Repeated floating-point arithmetic accumulates small representation errors, so sums of many values, running totals, or money stored as binary floating point drift from the exact result, and exact-equality comparisons (`==`) between computed floats fail unexpectedly. Individually tiny, the errors can matter for currency, reconciliations, or any value compared for exact equality. It extends the domain's calculation-error theme but is not a specific example in the book.
How to spot it
In code, flag money or high-precision quantities stored as float/double rather than decimal or integer minor-units, summation over many floats where an exact total is required, and `==`/`!=` comparisons between computed floating-point values instead of a tolerance. Look for totals that fail to reconcile by a fraction of a cent, or conditionals that hinge on exact float equality.
Pitfall
A ledger stores amounts as 64-bit floats and checks `if balance == 0.0` after summing thousands of transactions; accumulated rounding leaves balance at 1e-11, the check fails, and a "non-zero balance" alert fires on an account that is actually settled.
Done well
Money is stored as integer cents or a Decimal type, sums are taken in that exact representation, and comparisons use a tolerance (`abs(balance) < 0.005`) rather than exact equality — so the settled account reconciles to zero.
How to fix it
Use a decimal or integer (minor-unit) representation for money and other exact quantities, not binary floating point. When floats are unavoidable, compare with an explicit tolerance rather than `==`, and be mindful of error accumulation in long summations.
Avoiding Data Pitfalls (Wiley, 2020), Pitfall 3: Mathematical Miscues (domain extension — not a specific sub-pitfall example in the book) · David Goldberg (1991), 'What Every Computer Scientist Should Know About Floating-Point Arithmetic,' ACM Computing Surveys 23(1)
warninginteger-division-truncation
What it is
Two integer values are divided and the result is silently truncated (floored) to an integer instead of yielding the true fractional quotient, so a rate or ratio that should be, say, 0.3 comes back as 0. This is common wherever `/` on two integers means integer division (SQL, C, Java, Go, Python 2) and whenever a proportion is built from two integer count columns. The error is silent — no exception, just a wrong (often zero) number. It extends the domain's calculation-error theme but is not a specific example in the book.
How to spot it
In code, flag division where both operands are integer-typed and a fractional result is expected — `count_a / count_b` over int columns in SQL, `int / int` in C/Java/Go, `/` in Python 2, or integer pandas Series divided to form a rate. Look for rates/proportions that come out as 0, 1, or suspiciously round integers, and for a missing cast before division. Be suspicious of a "percentage" column whose values are only ever 0 or 100.
Pitfall
In SQL, `SELECT successes / attempts AS rate FROM trials` with both columns integer-typed returns 0 for every row that has fewer successes than attempts (3 / 10 → 0), so every conversion rate reads as 0%.
Done well
At least one operand is cast to a floating/decimal type before dividing — `successes * 1.0 / attempts` or `CAST(successes AS FLOAT) / attempts` (Python 3's `/` already does true division) — so 3 / 10 correctly returns 0.3.
How to fix it
Ensure at least one operand is a real/float/decimal type before dividing when a fractional result is intended, and cast explicitly rather than relying on the language default. Validate that computed rates fall across a plausible continuous range instead of collapsing to 0 or 1.
Avoiding Data Pitfalls (Wiley, 2020), Pitfall 3: Mathematical Miscues (domain extension — not a specific sub-pitfall example in the book) · Python PEP 238 — Changing the Division Operator (true division vs floor division)
warninglog-scale-averaging
What it is
Values measured on a logarithmic (or otherwise nonlinear) scale — pH, decibels, earthquake magnitude, star magnitude, log-returns — are averaged arithmetically and the result is read as the average of the underlying physical quantity. The calculation is valid, but the arithmetic mean of log-scale values equals the log of the geometric mean of the original quantity, not the mean of that quantity. Conflating the two answers a different question than intended and can mislead badly, because a one-unit step on the scale is a multiplicative (often 10x or ~32x) step in the quantity. This extends the domain's aggregation theme but is not a specific sub-pitfall example in the book.
How to spot it
In code and prose, flag an arithmetic mean or sum over a column known to be on a log or nonlinear scale — earthquake `mag`/Richter, `pH`, decibel/`dB`, log-returns, star magnitudes — especially when the result is described as the "average" magnitude/level/acidity or used as a stand-in for the physical quantity. Be suspicious of `mean()`/`AVG()` on such fields with no note that it reflects a geometric mean of the underlying quantity, and of summing log-scale values.
Pitfall
An analysis reports `avg_magnitude = df["mag"].mean()` as the "average earthquake," implying typical energy release — but magnitude is logarithmic (an M6 releases ~32x the energy of an M5), so the arithmetic mean of magnitudes does not represent average energy at all.
Done well
The analyst is explicit about which question is being answered: report the mean magnitude as a summary of the magnitude scale itself (equivalently, the geometric mean of energy), or — to summarize the physical quantity — convert magnitudes to energy, average in energy units, and convert back. The scale and its meaning are stated.
How to fix it
Decide whether you want the average of the scale or the average of the underlying quantity. For the physical quantity, transform to linear units (energy, concentration, intensity, dollars), average there, then transform back if needed. When you do average on the log scale, label it as such — it is the geometric mean of the quantity, not its arithmetic mean.
Avoiding Data Pitfalls (Wiley, 2020), Pitfall 3: Mathematical Miscues (domain extension — not a specific sub-pitfall example in the book) · Limpert, Stahel & Abbt (2001), 'Log-normal Distributions across the Sciences,' BioScience 51(5) — the geometric mean and log-scale data
errormismatched-units
What it is
Values expressed in different units of measure are combined, compared, or fed into a calculation without converting to a common unit. Because the numbers look interchangeable, the mismatch is silent — until the result is off by a constant factor or worse. Currency, metric vs imperial, temperature scales, magnitude suffixes (K/M/B), angle units, coordinate formats, and calendar-vs-business days are all common offenders.
How to spot it
In code, flag arithmetic or comparisons that combine fields of plausibly different units with no conversion step — summing amounts that may be different currencies, mixing meters and feet, adding values where some are in thousands/millions, comparing °C to °F, or aggregating a quantity column that has a companion unit-of-measure ("UoM") field that is never branched on. In data/prose, watch for a measure whose magnitude or metadata implies mixed units, or calculations performed without consulting each field's documented unit.
Pitfall
A spacecraft pipeline reads thruster impulse produced in pound-force seconds (lbf-s) and uses it directly where Newton-seconds (N-s) are expected; since 1 lbf ≈ 4.45 N, the trajectory is off by ~4.45x — the Mars Climate Orbiter failure mode. More mundanely, a `revenue` column that mixes USD and EUR rows is summed into a single grand total.
Done well
Units are confirmed from the metadata and normalized before any math — converting lbf-s to N-s (or all revenue to a single currency) up front. Mixed-unit fields are converted via their accompanying UoM column (IF/THEN per unit) into a common unit before aggregation, and the unit is documented.
How to fix it
Consult the metadata for every field's unit of measure before calculating; if it is missing, demand it. Convert all values to a common unit before combining them, handle mixed-unit fields by branching on their UoM column, and validate results against expected magnitudes so an order-of-magnitude unit error surfaces immediately.
Avoiding Data Pitfalls (Wiley, 2020), Pitfall 3E: Unmatching Units
warningpartial-period-aggregation
What it is
A time series is aggregated into buckets (year, month, week) where the first or — more often — the most recent bucket covers only part of its period, because the data was extracted mid-period. Plotted or trended as if complete, the partial bucket looks like a sudden drop (or the first bucket like a slow start), and viewers infer a real decline that is purely an artifact of where the data ends.
How to spot it
In code, flag time aggregations (`resample`, `GROUP BY date_trunc`, pivot by period) and trend lines that include the edge buckets without checking the data's actual min/max timestamp against the bucket boundary — i.e. no comparison of `max(date)` to the end of its period. In data, look for a final period whose total falls far below the prior trend, or whose record count is a fraction of a full period. In prose, watch for decline/trend claims about "the latest year/month" with no note of whether that period is complete.
Pitfall
An extract of FAA wildlife-strike reports runs through July 31, 2017. Counting strikes per year and plotting the timeline shows a steep drop in 2017, and the analyst speculates about new airport technology — when in fact 2017 simply holds about half a year of records.
Done well
The analyst first checks the data's date range (earliest Jan 1 2000, latest Jul 31 2017), recognizes 2017 is partial, and either excludes the incomplete period, annotates it clearly ("2017 partial — through Jul 31"), or annualizes it before comparing — so no false decline is read into the trend.
How to fix it
Before trending aggregated periods, explore the contours of the data: record the minimum and maximum timestamp and compare them to the bucket boundaries. Drop, flag, or pro-rate any partial leading/trailing bucket, and never compare a partial period head-to-head with complete ones.
Avoiding Data Pitfalls (Wiley, 2020), Pitfall 3A: Aggravating Aggregations
warningpercentage-point-vs-percent-change
What it is
A change between two percentages is described using the wrong kind of difference — reporting the absolute change in percentage points as a relative percent change, or vice versa. A move from 10% to 12% is +2 percentage points but +20% in relative terms; conflating the two overstates or understates the change by a large factor and misleads the reader. It extends the domain's rate-and-percent theme but is not a specific example in the book.
How to spot it
In code and prose, flag a difference taken between two values that are themselves percentages and then labeled ambiguously as "%" — `new_pct - old_pct` reported as a "percent increase," or a relative change `(new - old) / old` reported as "percentage points." Look for change figures derived from rate/percentage columns without specifying which kind of change is meant. In prose, watch for "rose by X%" where X is actually a difference in points.
Pitfall
An approval rating goes from 40% to 44%. The report says approval "rose 4%," implying a small relative bump, when it actually rose 4 percentage points — a 10% relative increase. The reader can't tell which is meant, and the two differ materially.
Done well
The change is stated unambiguously — "approval rose 4 percentage points (from 40% to 44%), a 10% relative increase" — distinguishing the absolute point change from the relative percent change so neither is misread.
How to fix it
Decide whether you mean an absolute change (percentage points) or a relative change (percent), compute it accordingly, and label it explicitly. Never write "%" for a difference between two percentages when you mean percentage points.
Avoiding Data Pitfalls (Wiley, 2020), Pitfall 3: Mathematical Miscues (domain extension — not a specific sub-pitfall example in the book) · Darrell Huff, How to Lie with Statistics (Norton, 1954) — the misuse of percentages
warninguneven-aggregation-coverage
What it is
Records are grouped into categories that are then compared, but the categories aggregate different amounts of underlying data, so the comparison is not apples-to-apples. The classic case: rolling several years of data up by calendar month, where some months happen to include one more year of observations than others — making those months look artificially higher and shuffling the ranking of the "biggest" categories.
How to spot it
In code, flag cross-category aggregations (`GROUP BY month`, `GROUP BY weekday`, category totals) that pool multiple periods without normalizing for how many sub-periods each category actually contains — no count of contributing years/periods per bucket, no per-period average. In data, look for category buckets backed by unequal numbers of records or unequal time spans. Be suspicious when ranking categories by a raw total that secretly depends on uneven coverage.
Pitfall
Eighteen years of wildlife-strike data (ending Jul 31, 2017) are summed by calendar month. January–July each pool 18 years while August–December pool only 17, so July tops the chart — but after dropping the partial 2017, August is actually the true peak month.
Done well
The analyst equalizes coverage before comparing — restricting to complete years, or averaging strikes per month-year rather than summing raw totals — so each month reflects the same number of contributing periods and the ranking (August highest) is accurate.
How to fix it
When comparing aggregated categories, verify each bucket draws on the same number of underlying periods/records. Normalize to a per-period rate or average, or trim the data to a span with equal coverage, before ranking or comparing category totals.
Avoiding Data Pitfalls (Wiley, 2020), Pitfall 3A: Aggravating Aggregations
errorunweighted-rate-average
What it is
Rates, ratios, or percentages computed over groups with different denominators are combined by summing them or taking a simple (unweighted) arithmetic mean, as if the groups were equal in size. Because each percentage is a quotient over a different base, the only correct aggregate recombines the underlying numerators and denominators — a population-weighted figure. The naive mean over- or under-states the true overall rate, sometimes dramatically.
How to spot it
In code, flag `mean()`/`AVG()` or `SUM()` taken over a column that is itself a rate/ratio/percentage (values bounded 0–100 or 0–1; names like pct/rate/share/ ratio) when group sizes differ and the denominators aren't carried along. Look for region/segment roll-ups of a percentage with no weighting term, and percentages being added together. In prose, watch for "the average of the rates" or summed percents.
Pitfall
North America's urban-population percentage is computed as the simple mean of the United States, Canada, and Bermuda values ((82.06 + 81.41 + 100.00) / 3 ≈ 87.93%), giving tiny Bermuda the same weight as the U.S. — or, worse, the three are summed to 263.80%.
Done well
The regional figure is rebuilt from the components: estimate each country's urban population (percent × total population), sum those numerators, and divide by the summed populations — yielding ≈81.81%, very close to the U.S. value because the U.S. dominates the region's population.
How to fix it
Never average or sum rates directly across unequal groups. Carry the numerator and denominator through the aggregation and recompute the rate at the target level (a weighted average using each group's denominator as the weight). If the denominators aren't available, obtain them — the rate alone cannot be aggregated.
Avoiding Data Pitfalls (Wiley, 2020), Pitfall 3D: Preposterous Percents
Data Pitfalls