Distinguish empty, unknown, and not applicable data

Represent missing values according to what is known about them. An unanswered question, a field that does not apply, and a failed measurement can all lack a numeric value while requiring different treatment in a report. Store the reason separately when it changes eligibility, quality checks, or interpretation.

If you are designing a dataset rather than merely querying one, a nullable column is only part of that contract. The column says whether a value is available. It does not necessarily explain why the value is unavailable or whether a row belongs in the metric’s denominator.

A commute survey with four different states

Consider a synthetic workplace survey asking for weekly commuting minutes. One participant reports zero. Another declines the question. A remote participant marks the question not applicable. A fourth participant’s answer is lost during collection.

rows = [
    {"person": "A", "minutes": 0, "missing_reason": None},
    {"person": "B", "minutes": None, "missing_reason": "declined"},
    {"person": "C", "minutes": None, "missing_reason": "not-applicable"},
    {"person": "D", "minutes": None, "missing_reason": "collection-failure"},
]

allowed_reasons = {"declined", "not-applicable", "collection-failure"}
for row in rows:
    value, reason = row["minutes"], row["missing_reason"]
    if value is None and reason not in allowed_reasons:
        raise ValueError("missing value needs a known reason")
    if value is not None and (reason is not None or value < 0):
        raise ValueError("observed value violates the survey contract")

eligible = [r for r in rows if r["missing_reason"] != "not-applicable"]
observed = [r for r in eligible if r["minutes"] is not None]
print("eligible:", len(eligible), "observed:", len(observed))
print("numeric response coverage:", len(observed) / len(eligible))

The result is three eligible participants and one observed numeric response, giving coverage of one third. The observed mean is zero because the only observed value is zero. That mean says very little about the other eligible participants, whose commute times are unknown.

The remote participant is excluded under this survey’s chosen definition of eligibility. Another survey might define remote work as zero commuting minutes and include that person. Both conventions are possible; switching between them changes the population being described and must be visible in the survey instructions.

View full-size image ↗

The diagram separates missingness reasons from values. A zero remains a measurement, while the three absent values retain different explanations.

An empty string is a storage token

A CSV export may encode all three absent values as an empty field. Once that distinction is lost, a downstream analyst cannot reconstruct it from the empty string alone. Capture the reason at collection time if later decisions depend on it.

Sentinel strings such as “N/A” can also be ambiguous. They may mean not applicable, not available, or a literal category code. Define their interpretation per source field. Applying a global replacement across every text column can destroy legitimate values in unrelated columns.

In a typed table, prefer a numeric value column plus a constrained reason column over mixing numbers and explanatory strings in the same cell. That keeps numeric operations well defined and permits quality checks for inconsistent combinations. A reason of “declined” next to a numeric value should fail the example’s contract.

Library missingness follows type-specific rules

The pandas missing-data guide documents different missing-value representations and explicit missingness checks. A floating-point NaN, a missing timestamp, and a nullable integer value may behave differently under equality and arithmetic.

Use the library’s missingness operation for the relevant data structure instead of assuming every missing value equals itself or equals a single sentinel. The small Python example uses None in ordinary dictionaries, so identity checks are sufficient there. Copying that exact comparison into every numerical library is not a portable rule.

Defaults deserve the same scrutiny. Filling all missing commute times with zero would turn three absent values into claimed observations. Excluding every missing row before measuring coverage would hide the collection failure and refusal. Keep transformation rules attached to the measure they support.

A missingness reason is still data to validate

Enforce the numeric type as well as the reason relationship in the production schema. The small fixture already supplies integers, but a string such as “zero” or a Boolean value could otherwise enter a loosely typed representation. Type validation and missingness validation answer separate questions. Keeping both explicit prevents a valid reason code from hiding an invalid measurement value.

The collection system can assign the wrong reason. A parser that marks every exception as “not applicable” can remove difficult records from the denominator and make coverage appear higher. Keep parsing failures distinct from participant responses, and measure their counts before publishing the curated table.

A reason can also change over time. A temporary collection failure may be resolved when a delayed response arrives. Preserve the earlier version when a saved report must remain reproducible, or document that historical results are restated after late data. The value’s update time and the survey’s observation time describe different events.

Access to detailed refusal reasons may need tighter control than access to aggregate coverage. A report can show counts by missingness category without exposing which participant declined. Retain only the level of detail needed for the dataset’s stated purpose and correction workflow.

Interpretation needs more than a clean schema

This schema makes absence inspectable, but it does not establish that observed respondents represent the eligible population. People with long commutes might be more or less willing to answer. Imputing their values requires assumptions about that missingness process and should be evaluated as a separate analytical step.

For a first data contract, specify the allowed reasons, their eligibility effect, and which combinations of value and reason are valid. Test a true zero, a missing answer, a not-applicable record, and a delayed correction. Then show observed coverage beside any summary statistic so readers can see how much of the intended population actually contributed a measurement.

Include the missingness policy with exported copies so downstream readers can preserve those distinctions.

Show what an explicit range assumption can establish

Missingness labels explain why values are absent, but they do not reveal the missing numbers. A small bound calculation can show the information gap without inventing answers. For this additional exercise, suppose the survey’s reported measure is capped weekly commuting minutes: participants with more than 600 minutes report 600. Valid reports therefore lie between zero and 600. This is a stated instrument rule for the hypothetical study, not a universal limit on real commuting time.

Under that rule, B and D each contribute somewhere between zero and 600 minutes. A contributes an observed zero, and C remains outside the eligible population. The eligible mean can consequently range from zero to 400. Filling both missing values with zero selects the lower endpoint of that range; it does not establish that zero is a representative estimate.

def bounded_commute_summary(records, upper=600):
    eligible_values = []
    for row in records:
        value, reason = row["minutes"], row["missing_reason"]
        if value is None:
            if reason not in allowed_reasons:
                raise ValueError("missing value needs a known reason")
        elif type(value) is not int or not 0 <= value <= upper or reason is not None:
            raise ValueError("invalid observed capped minutes")
        if reason != "not-applicable":
            eligible_values.append(value)
    n = len(eligible_values)
    if n == 0:
        return {"eligible": 0, "observed": 0, "coverage": None, "mean_bounds": None}
    values = [v for v in eligible_values if v is not None]
    total = sum(values)
    missing = n - len(values)
    return {
        "eligible": n, "observed": len(values),
        "coverage": len(values) / n,
        "mean_bounds": (total / n, (total + missing * upper) / n),
    }

print(bounded_commute_summary(rows))
corrected = [dict(row) for row in rows]
corrected[3].update(minutes=180, missing_reason=None)
print(bounded_commute_summary(corrected))

The baseline has three eligible people, one observation, coverage one third and mean bounds (0.0, 400.0). After D’s collection failure is resolved with a reported 180 minutes, coverage becomes two thirds and the bounds narrow to (60.0, 260.0). The observed-response mean is now 90, whereas the full eligible-population mean is still unknown because B declined. The bound calculation keeps all three eligible people in its denominator.

View full-size image ↗

These are arithmetic bounds under the cap assumption, not confidence intervals. They do not provide a probability that the actual eligible mean is near either endpoint. If the original question measured uncapped minutes with no credible upper limit, the finite upper bound would not follow. State which measure the bound describes, and do not silently clip source values after collection to manufacture a narrow uncertainty range.

Validate a correction as a change in state

A valid correction changes both D’s value and missingness reason together. Setting minutes to 180 while leaving collection-failure should fail the new function’s consistency check. Keeping a failed-collection reason next to an observed value would make reports disagree depending on which field they used for coverage. Record when the correction arrived separately from the week the survey describes, so a published snapshot can be reproduced or deliberately restated.

The helper also rejects Boolean, negative and over-cap observed values. It assumes a correctly configured nonnegative integer upper bound and rows with the documented keys; those structural and configuration checks belong at the input boundary. An unknown reason must not become not applicable as a fallback. Such a fallback would remove the row from the population rather than merely report a validation failure.

For independent exercises, start from the original four rows each time. Resolve D as 180 without clearing the reason and confirm rejection. Then record B as 600 and D as 180, with both reasons cleared: coverage is complete and both bounds equal 260. Finally, pass an empty population and a population containing only C. Both have zero eligible people and undefined coverage and bounds, not a measured zero-minute mean. These cases distinguish complete knowledge of a zero value from having no eligible measurements to summarize.

A missingness dashboard can show the observed mean, coverage, reason counts and any justified range bounds together. None should quietly replace another. That presentation lets a consumer decide whether the remaining uncertainty is acceptable for exploration, requires follow-up collection, or prevents publishing a population-level conclusion.