Missing Values Can Halve an AI Report’s Average

An AI assistant reports an average callback wait of four minutes. An analyst opens the same file and calculates eight. Neither has made an addition error. The assistant counted blank cells as zero; the analyst counted only recorded times. The disagreement began before the arithmetic, when missing evidence acquired a numerical meaning.

Even eight is not the average for all customers in the example below. This guide is for operations analysts and developers building AI reporting tools. Its practical decision is when to preserve an unknown, certify a zero, exclude an inapplicable observation or publish a separately labelled estimate. A tidy table and a confident explanation cannot recover distinctions that the input pipeline has already erased.

Define the population before filling a cell

“Average waiting time” leaves several choices unstated. Which service, period and unit? Which events start and end the wait? Does the denominator include every support ticket, only tickets requiring a callback, or only callbacks with valid timing records? Those are different populations, not interchangeable implementations of one calculation.

Our six-ticket fixture is deliberately small. All tickets are closed. Five required callbacks, which took place; one was resolved without a callback. Two completed callbacks lack timing records because event recording failed. They are not customers who are still waiting. The measure is minutes between the callback request and the connected call, with zero accepted as an immediate connection in the reporting unit.

The observed sum is 24 and the observed count is three, giving eight minutes. Filling all three nonnumeric cells with zero produces 24 / 6 = 4. Excluding F but filling the two unknowns gives 24 / 5 = 4.8. Renaming any of these outputs “actual average” does not establish its validity.

The defensible statement is narrower: “Timing is available for three of five applicable callbacks. Their observed average is eight minutes; two timings are unknown.” Coverage is 60%. F remains in the total ticket count but not the callback-wait denominator. An average per ticket could be a separate, explicitly defined measure. This constructed example is not a customer result or a model benchmark.

Retain the reason, not just the absence

A blank has no universal business meaning. It may represent an unrecorded measurement, an inapplicable question, a withheld value or a failed conversion. Those states call for different responses. A parsing failure needs investigation; confidential information must not be reconstructed and released merely because an assistant can guess it.

Statistics Canada's table-symbol guide distinguishes unavailable data, inapplicability, confidentiality suppression and values rounded to zero. Its legend is specific to that publisher, not a dictionary for every spreadsheet. The engineering lesson is to preserve a qualifier as part of the value's meaning, rather than discarding it during cleanup.

Our proposed representation stores an optional numeric value alongside status, reason and source reference. An observed value may legitimately be zero. Unknown and inapplicable states retain no fabricated number. If the source supplies no reason, record “reason unknown”; a model cannot infer a sensor outage or confidentiality restriction from a cell's appearance alone.

Preserve the original token in an appropriately controlled location. A dash, a space, a Persian zero and “not recorded” are different evidence. For a scanned table, the relevant heading and footnote matter too: our table-extraction guide explains why a cell crop alone can lose its interpretation. A reporting tool usually needs a controlled reference to the source, not sensitive source content copied into general diagnostic logs.

Audit what the importer changes before the model sees it

pandas' CSV-reader documentation describes default recognition of empty strings and common missing-data markers. keep_default_na controls the default set, while na_values can specify markers by column. A successfully parsed file therefore does not prove that the original distinctions survived ingestion.

Suppose a status column legitimately uses the literal string NULL as a defined label. Automatic missing-value recognition can erase it. Turning defaults off everywhere is not a universal fix: the importer must then recognize the producer's genuine missing markers itself. Define conversion rules by field, using the source dictionary and known examples, and isolate ambiguous tokens before numeric conversion.

Keep separate counts for originally blank cells, values classified as missing under an accepted rule, and failed conversions. Combining them lets a broken feed masquerade as changing customer behavior. Reconcile row counts before and after import as well. A disappearing record and a missing field inside a retained record are different defects.

An assistant can summarize discrepancies or group suspicious examples for review. It should not own the authority to reinterpret the producer's symbols. In particular, “anything that will not parse as a number becomes zero” is a data-changing policy, not harmless error handling. Record who accepted a mapping and which input version it applies to so that later corrections remain traceable.

Test aggregation semantics across the actual toolchain

The PostgreSQL 18 aggregate reference defines averages and sums over non-null inputs, distinguishes row counts from counts of non-null values, and specifies null rather than zero for a sum with no input rows. These are operation semantics, not approval of a business denominator.

By contrast, pandas' Series.sum reference documents zero as the default sum of an empty or entirely missing series. min_count=1 preserves a missing result in those cases. It does not establish completeness: one known value and a hundred unknowns can still produce the sum of that one value.

Moving a calculation between a database, an analysis notebook and an assistant tool needs behavioral tests. An all-numeric happy-path file will not expose the difference. Include no rows, all unknowns, and a mix of real zeros and unknowns. Check the answer and evidence counts against the product definition instead of assuming that similarly named functions implement the same missingness policy.

Also distinguish replacing a missing final aggregate for display from replacing each unknown input before averaging. The latter changes the denominator; the former can still turn “no result” into an unsupported zero. Neither is merely cosmetic when a person or downstream action consumes the number.

Validate row identity before these calculations. Join fanout in AI-generated SQL can corrupt both numerator and denominator. Correct missing-value handling will not repair duplicated tickets, just as correct joins will not determine whether a blank means “not applicable.” Each check protects a separate property of the answer.

Permit zero only when the evidence supports zero

A zero can be derived rather than directly entered, but the derivation needs a contract. Consider a count of qualifying events in a defined period from a source whose completeness has been established. No qualifying events supports a count of zero. A missing daily file or failed connection does not supply the same evidence. Nor does a zero denominator imply a zero rate; that rate may be undefined.

This is ZharfAI's proposed decision table, not a standard's mandate. Where it matters to the decision, also distinguish a rounded zero from evidence that no event occurred. Limited measurement precision and a failure to receive data are different limitations.

The metric owner should decide which distinctions matter for the intended use. A convention that is reasonable for a bounded event counter may be wrong for a duration, a balance or a survey response. A reusable cleanup function should implement an agreed field policy, not silently create one for every number it encounters.

Keep model-ready estimates away from observed facts

Some predictive workflows require a complete feature matrix. The scikit-learn imputation guide describes replacement strategies and indicators that preserve whether an input was missing. These mechanisms help prepare model inputs; they do not make an inferred replacement an observed event.

Our recommended design leaves original data and statuses unchanged. Store estimated values, method and transformation version in a separate layer. Give an estimate-based report a distinct name and disclose the share of values supplied by estimation. The observed-only mean and a model-completed mean are different products even when they happen to display the same number.

Fit a proposed imputation method using training data only, then apply that fitted transformation during evaluation. Future-period information and test answers must not influence the replacements. This is our implementation recommendation, not a claim that one imputation strategy is appropriate for every dataset. Choice of method still depends on the data-generating process and the decision.

In the callback example, if longer waits are more likely to be unrecorded, the three observed waits are not necessarily representative of the five applicable callbacks. If a recording service fails during a particular shift, the missingness pattern changes with staffing and system operation. High overall coverage alone cannot rule out that concentration. Investigate the reason before ranking branches or reducing capacity on the strength of an incomplete mean.

Use sensitivity analysis without disguising assumptions

An urgent decision may need more than “unknown.” Show what follows from explicit assumptions instead of pretending that assumptions are measurements. For this exercise only, suppose each of the two unknown waits lies between zero and 30 minutes. The mean across the five applicable callbacks then ranges from 4.8 to 16.8 minutes: the low end is 24 / 5; the high end is (24 + 30 + 30) / 5.

This is not a statistical confidence interval. The 30-minute ceiling does not come from the original records. A real application needs independent evidence for that bound. A service promise that may have been breached is not a valid hard limit on a measured wait. Without a defensible ceiling, this calculation has no established upper endpoint.

If every accepted scenario leads to the same operational choice, a narrowly scoped decision may be possible with assumptions recorded. If the choice changes across plausible values, recovering evidence has greater decision value. Sensitivity analysis does not authorize guessing an individual customer's history or reconstructing confidential data. Its role here is to expose how much the aggregate decision depends on unresolved information.

Make the tool return an evidence denominator

The calculation tool should not return only eight. For this fixture it should return total tickets, applicable tickets, valid observations, unknown timings, inapplicable tickets, observed sum and observed mean. Include units, reporting window, data cutoff and metric-definition version. Let the language model explain that package, not decide invisibly which records to discard or fill.

Build acceptance tests from small, inspectable cases. A true zero must remain an observation. Adding an unknown should change coverage without changing the observed-only mean. An entirely unknown dataset must not produce a reported mean of zero. An inapplicable record belongs in the total population count but not this measure's denominator. A conversion error must not silently become “not recorded.” File-to-database-to-report round trips must preserve these distinctions.

Monitor missingness by reason, source, branch and shift, rather than watching a single global null percentage. Investigate a sudden increase in zeros too: a new repair rule may be manufacturing them. Alongside missing-data rates, measure reports that calculate the number correctly but describe the wrong population. Our data-quality and observability guide places these checks in the broader data path.

Avoid a universal acceptance rule such as “95% coverage is enough.” The decision owner must consider consequences, concentration of missingness and reversibility. Revisit the tests when the file producer, library version, metric definition or missingness mechanism changes. A useful AI report makes its evidence boundary visible: which observations support the number, and which parts of the requested answer remain unknown.

Source notes — reviewed September 29, 2026

The six-ticket fixture, calculations, decision table and proposed architecture are ZharfAI's educational analysis, not customer findings or an evaluation of an assistant. All five sources were opened on the review date. Living software documentation has no single page publication date; the served versions identify the scope consulted.

• Statistics Canada — Standard table symbols, last-modified date April 7, 2025; publisher-specific qualifiers.
• pandas — read_csv, served version 3.0.6; missing-marker recognition at import.
• PostgreSQL 18 — Aggregate Functions; count, average and sum semantics.
• pandas — Series.sum, served version 3.0.6; empty inputs and minimum valid counts.
• scikit-learn — Imputation of missing values, served version 1.9.1; replacement strategies and missingness indicators.