Why Audit Row Counts?
Filtering changes which records are available for analysis. If the count before filtering is not recorded, or if exclusions are applied without a clear sequence, it can be difficult to explain how the final data set was formed. A row-count audit makes this change visible: it records the starting number of rows, the number removed at each step, and the number remaining.
A count is meaningful only in relation to what a row represents. One row might describe one participant, one visit, one specimen, or one other observational unit. If participants can contribute multiple visits, a count of rows is not a count of participants. State what is counted, and keep that unit of counting consistent throughout the audit.
A useful audit starts with the rules for eligibility and exclusion already written down. For each rule, record its meaning, the stage at which it is applied, and how many rows it removes at that stage. This makes the audit reproducible: another analyst can apply the same rules to the same starting data and check whether the counts agree.
Track a Sequence of Filters
Suppose a data set begins with \(N_0\) rows. Apply one filter at a time, in a documented order. Let \(E_i\) be the number of rows excluded by filter \(i\) among the rows reaching that stage, and let \(N_i\) be the number remaining after that filter. The count must satisfy \(N_i=N_{i-1}-E_i\) at every stage. Consequently, the final count is the starting count minus the sum of the stage-specific exclusions.
The word stage-specific matters. If a row is removed at the first filter, it does not reach later filters. Thus, \(E_i\) counts only the rows removed at stage \(i\), not all rows in the original data set that might meet that filter’s condition. Sequential counts avoid counting a row twice.
Worked Example: A Sequential Audit of Three Filters
A hypothetical data set contains 240 visit records. Each row is one visit record. The documented rules are applied in this order: exclude records that do not meet eligibility rules; then exclude eligible records with a missing predictor value; then exclude the remaining records with a missing outcome value. At the second stage, 11 rows are removed; at the third, 7 are removed. The eligibility check removes 18 rows. Find the count after each filter and the final retained count.
Start with \(N_0=240\). After eligibility filtering, \(E_1=18\), so \(N_1=240-18=222\) rows remain.
The missing-predictor filter applies to those 222 remaining rows. It removes \(E_2=11\), giving \(N_2=222-11=211\).
The missing-outcome filter applies to those 211 rows. It removes \(E_3=7\), giving \(N_3=211-7=204\).
The audit is internally consistent: the final count is 204 visit records. This says nothing by itself about the number of unique participants; that would require counting participants using an appropriate identifier.
A compact table makes the sequence easy to check. Include the count entering each stage, the number removed there, and the count leaving it. Name the rule precisely rather than using a vague label such as “cleaning.”
| Stage | Rule applied | Rows entering | Rows excluded | Rows remaining |
|---|---|---|---|---|
| 0 | Starting data set | 240 | — | 240 |
| 1 | Does not meet eligibility rules | 240 | 18 | 222 |
| 2 | Missing predictor value among eligible rows | 222 | 11 | 211 |
| 3 | Missing outcome value among rows retained so far | 211 | 7 | 204 |
Overlapping Reasons and Inclusion–Exclusion
Sometimes counts are reported for each reason in the original data set rather than for filters applied in sequence. Those counts may overlap. For example, a row may be both ineligible and missing a predictor value. Adding the two counts would remove that row twice, even though the row can only be excluded once.
This identity gives a direct reconciliation when the exclusion reasons are reported as overlapping counts from the same starting set. It is important to specify that all three counts refer to that same set of starting rows. If counts instead refer to successive stages, use the sequential subtraction table; do not combine counts from the two approaches as if they described the same quantities.
Worked Example: Correcting for Overlapping Exclusions
A hypothetical data set has 120 records. In the starting data, 14 records fail an eligibility rule, 10 have a missing predictor, and 4 of those records meet both conditions. A record is excluded if it meets either condition. How many records are excluded, and how many remain?
Let \(A\) be the 14 records failing eligibility and \(B\) the 10 records with a missing predictor. The 4 records in \(A\cap B\) appear in both individual counts. Therefore,
There are 20 distinct excluded records, not 24. Subtracting from the starting count gives \(120-20=100\) retained records. The same result follows by dividing the table into distinct groups: 10 fail eligibility only, 6 have a missing predictor only, 4 meet both rules, and 100 meet neither. These groups sum to \(10+6+4+100=120\).
For more than two exclusion rules, a single combined count can be difficult to calculate because rows may meet several rules at once. A practical audit can instead make the rules sequential: after a row is excluded, do not count it as excluded again at later stages. Another option is to report the overlaps explicitly, but the categories must then be defined so that each row belongs to exactly one reported category.
Reconcile the Counts Before Moving On
A completed audit should let a reader answer three questions: how many rows were present at the start, how many were removed under each rule, and how many rows remain? The arithmetic should agree, and the rule descriptions should make clear which records each count covers. If the arithmetic does not agree, the discrepancy is a prompt to investigate rather than a number to hide.
The following sequence is a useful check:
State whether a row represents a participant, visit, specimen, or another unit. Do not label a row count as a count of people unless one row represents one person.
Write down the number of rows before filtering, and identify which data file or saved version that count describes.
Record the rule, the number of rows reaching that stage, the number it excludes, and the number remaining. Use either this sequential convention or clearly defined overlapping counts.
For sequential filters, verify that each entering count minus its exclusions equals its remaining count, and that the last remaining count equals the reported final count.
Worked Example: Finding an Unexplained Difference
A hypothetical file contains 160 visit records. An audit reports 20 records excluded for ineligibility, 12 more excluded for a missing predictor among eligible records, and 9 more excluded for a missing outcome among the records remaining after the first two stages. The reported final file contains 123 rows. Check whether the audit reconciles.
Under the stated sequential definitions, the first two stages give \(160-20=140\), then \(140-12=128\). The third stage gives \(128-9=119\). The arithmetic therefore predicts 119 final rows, not 123.
The difference is \(123-119=4\) rows. The counts as reported cannot all describe the same starting file and the stated sequence of rules. The next step is to check the saved file used at each stage, whether a filter was applied differently than documented, and whether one of the exclusion counts was calculated from a different set of rows. The report should not be changed to make the arithmetic look right without finding the source of the discrepancy.
Common Mistakes and Why the Audit Matters
- Adding overlapping counts. If a row meets two rules, adding original-file counts for both rules counts that row twice. Subtract the intersection, or use mutually exclusive sequential exclusions.
- Mixing starting-set and stage-specific counts. “Missing predictor in the original file” is not necessarily the same count as “missing predictor among eligible records.” State the set to which every count refers.
- Calling rows people. Repeated visits can make the number of rows larger than the number of participants. Name the unit actually counted.
- Reporting only the final count. A final number without the starting count and exclusion trail cannot show which rules produced it or where an error may have occurred.
- Treating a mismatch as harmless rounding. Row counts are integers. Any difference must be explained by the data or by the way the rules were applied.
A transparent row-count audit supports interpretation of later analyses because it shows which records were available to be analyzed and how they were selected. It does not establish that the retained records are representative, that the exclusion rules are appropriate, or that filtering cannot affect conclusions. It establishes the narrower but essential fact that the changes in row count are traceable and arithmetically consistent.
Check Your Understanding
Use these questions to check whether you can build and interpret a row-count audit.
- A file begins with 300 visit records. A first filter removes 25, and a second filter removes 18 of the records remaining. How many records remain? Show the sequential arithmetic.
- Of 90 starting rows, 12 meet exclusion rule A, 9 meet rule B, and 3 meet both. How many distinct rows meet at least one rule?
- Explain why “number of rows” and “number of participants” may differ when a file contains repeated visits.
- A table lists missing-predictor exclusions counted among all starting rows, then subtracts that number after first removing ineligible rows. What problem could this cause?
- A sequential audit begins with 200 rows and reports exclusions of 17, 8, and 6, but the final file has 172 rows. Does the audit reconcile? If not, calculate the expected count and the size of the discrepancy.