A 40-column table has roughly 3% of each column's cells absent; why does dropping every incomplete row delete most of the table?

## Absence is counted per column, removal happens per row **Dropping incomplete rows** — removing every row that contains a hole, under some rule for how many holes it takes — is the disposition that invents nothing. That is its great virtue and it is why people reach for it first. The cost is that it is applied along a different axis from the one the absence was measured on. You look at the table column by column and see a mild problem: about 3 cells in every 100 are absent in each of the 40 columns. You then remove rows. A row is a conjunction of 40 cells, and it survives only if **every one of them** is present. Those two facts do not combine the way intuition expects. ## The arithmetic If the holes fall in independently chosen rows, the chance that one particular cell is present is 0.97, and the chance that all 40 of a row's cells are present is: 1. `0.97` for the first column, 2. times `0.97` for the second, and so on, 3. giving `0.97^40`, which is about **0.30**. So roughly 70% of the table is deleted by a problem that reads as 3% when you look at any single column. The shape of the curve matters more than the exact number: completeness decays geometrically in the number of columns, so widening a table makes row-wise removal worse even when no column got any worse. At 10 columns you keep 74%; at 100 columns you keep 5%. ## The assumption that is doing all the work Independence is an assumption, not an observation, and in real data it is frequently wrong in the direction that helps you: - One upstream producer failed for a window, so all of its fields are absent together in the same rows. - A survey section was optional, so respondents who skipped it skipped all of its questions. - A late-joining reference table matched only some rows, so every field it contributed is absent on exactly the same rows. In each case the holes are **clustered by row**, and the loss from row-wise removal is close to the worst single column's rate rather than the compounded one. The reverse also happens — fields that fail independently for unrelated reasons behave exactly like the model above. The only honest move is to count complete rows directly rather than to reason from per-column rates in either direction. ## The cost that is not about size A smaller sample is the visible cost. The invisible one is direction. Cells are rarely absent for reasons unrelated to their own value: a sensor that fails under load is absent precisely on the heavy-traffic rows, a respondent who will not state an income is systematically not at the middle of the range, an optional field is filled by the engaged and skipped by the indifferent. Removing those rows removes a **kind** of row, so the survivors are not a random subsample of the original — they are a different population, and every number computed on them answers a question about that population instead of the one you meant. The symptom is that the numbers move in a consistent direction rather than scattering, and that the movement survives as the sample grows. Nothing in the table itself will tell you this happened; the only evidence is a comparison between the rows you kept and the rows you deleted. | | dropping incomplete rows | filling the holes | |---|---|---| | invents values | no | yes | | changes the row count | yes, potentially severely | no | | tilts the sample | yes, whenever absence is not random | no, but it tilts the values instead | | survives handover | visibly, as a smaller table | invisibly, unless recorded | | forces one shared population | yes | yes | ## What dropping buys One thing, and it is worth more than it looks: **a single shared population**. Any calculation that reads two columns at once — a ratio, a difference between two measures, anything comparing one column against another — has to decide what to do when one of the two is absent on a row. If you remove incomplete rows first, both columns are computed over exactly the same rows and the comparison is between like and like. If you do not, the two columns can each be reduced by a different set of rows, and the two numbers you are comparing describe different subsets of the table. ## Practical shape of the rule - **Restrict the rule to the columns you actually read.** A 40-column table analysed through 5 columns should have its completeness rule applied to those 5. This alone usually converts the 70% loss into a manageable one. - **Drop the column instead of the rows** when one column is responsible for most of the holes and is not central to the question. - **Count first.** Report how many rows the rule removes before applying it, and compare the removed rows against the kept ones on the columns you care about. One thing that genuinely varies between tools: the rule for what makes a row incomplete is a setting, not a law. Surfaces offer some combination of *any hole removes the row*, *only an entirely absent row is removed*, and *keep rows with at least this many present values* — and the setting that applies when you do not choose is not the same everywhere. Say which rule you mean rather than assuming the one you are used to.