Auditing Missingness with Counts and Proportions

Why Audit Missingness With Counts and Proportions?

A missing-value summary is often the first check made after data are loaded. Counts show how many records lack a value; proportions put those counts in context. Neither is useful without a clear denominator. A count of 8 missing values could be minor in a data set of 800 records or substantial in a data set of 10.

For a data frame in which every row is eligible for every variable, the denominator is the total number of rows. If only some records are eligible for a particular measurement, its rate should use that eligible group instead. Reporting counts alongside denominators makes this choice visible and allows another analyst to reproduce the result.

Definition: For variable $j$, let $E_j$ be the set of records eligible to have that variable recorded, and let $N_j=|E_j|$ be the number of eligible records. The missing count $M_j$ is the number of eligible records whose value is missing. When $N_j>0$, the missing proportion is $M_j/N_j$, and the missing percentage is $100M_j/N_j$. The observed count is $N_j-M_j$.

When all $N$ rows are eligible for every variable, $N_j=N$ for each variable. If eligibility differs, each rate needs its own $N_j$. A table that reports a percentage without identifying its denominator is incomplete: readers cannot tell whether two rates are comparable or reproduce them reliably.

Counting Missing Values in R

For ordinary columns in a data frame, is.na() returns a logical value for each entry: TRUE when it is missing and FALSE otherwise. In R, sum() counts the TRUE values because TRUE is treated as 1 and FALSE as 0. Thus, sum(is.na(x)) counts missing entries in a column called x. The function colSums(is.na(dat)) applies that count to every column in a standard data frame called dat.

A blank string, "", and the text string "NA" are not automatically missing values; is.na() returns FALSE for them. If a data source uses such entries as missing-value codes, they must be identified and handled consistently before calculating the audit. Likewise, the audit counts what R recognizes as missing; it does not decide whether a value is plausible or whether a record should be included.

M_j=\sum_{i\in E_j} I_{ij}, \qquad \text{Missing percentage}_j=100\frac{M_j}{N_j}

Here $I_{ij}=1$ if record $i$ has a missing value for variable $j$, and $I_{ij}=0$ otherwise. The formula follows directly from the indicator definition: adding one for each missing value and zero for each observed value counts the missing entries. Dividing that count by the number of eligible records gives the fraction missing; multiplying by 100 expresses it as a percentage. The rate is undefined when $N_j=0$, so an empty eligible set must not be reported as a zero-percent missing rate.

Worked Example: A Column-by-Column Audit

A hypothetical insurer has eight policy records. The data frame contains an age, an annual premium, and a region for each record. All eight records are in scope for all three variables. The values are:

Age: 42, missing, 51, 37, 44, 46, missing, 58. Premium: 1200, 900, 1000, missing, 1300, 1400, 1500, 1100. Region: North, South, missing, South, North, North, South, missing.

Method. Count missing values in each column, then divide each count by the common denominator $N=8$. The common mistake avoided here is reporting counts alone and overlooking that the percentage is based on eight eligible records for each variable.

For age, records 2 and 7 are missing, so $M_{\text{age}}=2$. The observed count is $8-2=6$, and the missing percentage is $100(2/8)=25\%$.

For premium, record 4 is missing, so $M_{\text{premium}}=1$. The observed count is $8-1=7$, and the missing percentage is $100(1/8)=12.5\%$.

For region, records 3 and 8 are missing, so $M_{\text{region}}=2$. The observed count is $8-2=6$, and the missing percentage is $100(2/8)=25\%$.

In R, the counts can be obtained with colSums(is.na(dat)). Set N <- nrow(dat), then calculate the percentages as 100 * colSums(is.na(dat)) / N. The resulting audit is:

Variable	Eligible count	Observed count	Missing count	Missing percentage
Age	8	6	2	25%
Annual premium	8	7	1	12.5%
Region	8	6	2	25%

Check. For every row in the audit, observed count plus missing count equals the eligible count: $6+2=8$, $7+1=8$, and $6+2=8$. Each percentage also matches its count divided by 8.

Make the Denominator Explicit

The full row count is not automatically the right denominator for every variable. The correct denominator is the number of records to which the missingness question applies. For a complete rectangular data set, this is usually the row count. If the data description specifies that only a subset of records was eligible for a measurement, calculate the rate within that subset and label it accordingly.

In R, the calculation should reflect that definition. For example, if a logical condition called eligible identifies the in-scope records, first form the subset with eligible_dat <- dat[eligible, ]. Then count missing values in the relevant column of that subset and use nrow(eligible_dat) as the denominator. Do not silently use the total number of rows instead. If no records are eligible, report the denominator as zero and the rate as undefined.

Worked Example: Using an Eligibility Denominator

In a hypothetical review of 12 claims, only 8 claims required a particular supporting document. Among those 8 eligible claims, the document-status field is missing for 2. A separate loss-amount field is in scope for all 12 claims and is missing for 1. Calculate and compare the two missingness rates.

Method. Use the eligible-claim count for document status and the full claim count for loss amount. The common mistake avoided is dividing both missing counts by 12 simply because the data frame has 12 rows.

For document status, $N_{\text{status}}=8$ and $M_{\text{status}}=2$. The observed count is $8-2=6$, and the rate is $100(2/8)=25\%$. The fact that the data frame has four other rows does not make those claims part of this variable’s stated eligibility denominator.

For loss amount, $N_{\text{loss}}=12$ and $M_{\text{loss}}=1$. The observed count is $12-1=11$, and the rate is $100(1/12)=8.333\ldots\%$, or 8.33% rounded to two decimal places.

If a variable called doc_eligible marks the eight eligible claims, the document-status calculation in R is doc_dat <- dat[dat$doc_eligible, ], followed by sum(is.na(doc_dat$doc_status)) and nrow(doc_dat). The loss-amount calculation uses sum(is.na(dat$loss_amount)) and nrow(dat). The resulting summary is:

Variable	Eligible count	Observed count	Missing count	Missing percentage
Document status	8	6	2	25%
Loss amount	12	11	1	8.33%

Check. The reported percentages use the stated eligible counts: $2/8=0.25$ and $1/12=0.08333\ldots$. If the document count were divided by 12, the result would be 16.67%, but that would answer a different question than the one specified.

Build a Reproducible Audit Table

A useful audit records the variable name, eligible count, observed count, missing count, and missing percentage together. This avoids a common reporting weakness: giving a percentage without enough information to tell what was counted. For a data frame where all rows are eligible, the calculation can be organized as follows.

Fix the scope.
State which records are included and which variables are being audited. For a full data frame, record the row count with nrow(dat).

Count missing values.
Use colSums(is.na(dat)) for column-wise counts in a standard data frame, or use a specified eligible subset for a variable with a narrower scope.

Calculate companion summaries.
For each variable, subtract its missing count from its eligible count to get its observed count, then calculate $100M_j/N_j$ when $N_j>0$.

Check and report.
Verify that observed count plus missing count equals eligible count. Report the denominator and rounding convention with the percentage.

For the all-rows-eligible case, a compact base R construction is miss <- colSums(is.na(dat)), n <- nrow(dat), and audit <- data.frame(variable = names(miss), eligible = n, observed = n - miss, missing = miss, missing_pct = 100 * miss / n). If the data frame could have zero rows, do not calculate the percentages by dividing by zero; explicitly represent them as undefined instead. For variable-specific eligibility, prepare and document the relevant eligible subset before applying the same count-and-rate logic.

Worked Example: Why a Filtered Data Set Can Hide Missingness

A hypothetical analysis begins with 10 records. Two values are missing in tenure (records 2 and 5), and three are missing in balance (records 2, 7, and 9). The two missing-value sets overlap at record 2. An analyst filters to complete cases for these two variables before creating a missingness report. Determine the original rates and explain what the filtered report would show.

Method. Audit the original data first, using $N=10$, and then count how many records survive the complete-case filter. The common mistake avoided is treating a zero missing count after filtering as evidence that the original data had no missing values.

For tenure, $M_{\text{tenure}}=2$, the observed count is $10-2=8$, and the missing percentage is $100(2/10)=20\%$.

For balance, $M_{\text{balance}}=3$, the observed count is $10-3=7$, and the missing percentage is $100(3/10)=30\%$.

There are four distinct records with at least one missing value: record 2 is shared by both variables, while records 5, 7, and 9 each contribute one additional record. Thus $10-4=6$ records are complete for both variables. In R, cc <- dat[complete.cases(dat[c("tenure", "balance")]), ] retains those six records. Within cc, both columns have zero missing values, so a new audit of cc reports $0/6=0\%$ for each.

That zero-percent result describes the filtered complete-case data, not the original 10 records. The original audit remains 20% missing for tenure and 30% for balance. A reproducible report must state which data set was counted.

Check. The original missing counts are consistent with the data: two plus three column-level missing entries total five entries, but only four distinct records are affected because record 2 is missing in both columns. The complete-case count is therefore six, not five.

Common Mistakes and Reporting Checks

Using a count as if it were a rate. Always pair a missing count with the eligible denominator before comparing variables.
Changing denominators without saying so. If eligibility differs by variable, label each eligible count. A common denominator is appropriate only when the same records are in scope.
Auditing after an unreported filter. A filtered data set can have fewer missing values simply because records containing missing entries were removed. Name the data set or subset being summarized.
Treating text that looks missing as an R missing value. Check whether blank strings or text codes have been converted to actual missing values before relying on is.na().
Reporting a rate for an empty set. If the eligible count is zero, the percentage is undefined, not zero.

These summaries describe the amount of missing data, not its cause or the consequences for a later model. A careful audit makes its scope, variable, count, and denominator explicit so that subsequent analysis starts from a clear account of the observed data.

Check Your Understanding

For each problem, identify the correct denominator, show the count or rate requested, and check that your answer matches the stated scope.

A data frame has 16 eligible records for both variables. Variable A has 4 missing values, and variable B has 1. Give each variable’s observed count and missing percentage. (Answer: A: 12 observed, 25%; B: 15 observed, 6.25%.)
Among 30 hypothetical claims, a measurement was required for 18. Three of those 18 values are missing. What denominator and missing percentage should the audit report? (Answer: Denominator 18; 16.67% missing.)
For 25 eligible records, a delay field has 5 missing values and a cost field has 2. Calculate both missing percentages and state whether they share a denominator. (Answer: Delay 20%; cost 8%; both use denominator 25.)
A hypothetical data set has 40 rows, but a variable is in scope for only 28. Seven eligible values are missing. Compare the rate using the correct denominator with the rate obtained by dividing by all rows. (Answer: Correct rate $7/28=25\%$; all-row calculation $7/40=17.5\%$, which does not use the stated eligibility denominator.)
A character column contains "A", NA, "", "NA", "B", and NA. What does sum(is.na(x)) return, and what percentage does that represent using the column length? (Answer: 2 missing values; $2/6=33.33\%$. The blank string and text string "NA" are not counted by is.na().)

What You'll Learn