Diagnose CSV Types and Missing-Value Distortion

Understand the likely causes

• CSV readers expose text under a dialect; type inference and missing-marker defaults add assumptions. Empty text, an invalid word, a suppressed value, and an observed zero can become conflated. Locale-specific decimal/group separators, quoted delimiters, duplicate headers, and mixed units can further corrupt interpretation without necessarily causing a parser error.

1. Preserve a small raw reproduction

Use a synthetic in-memory excerpt with the actual delimiter, quoting, header shape, and problematic token pattern. Inspect repr-style text and the parser’s field count rather than a spreadsheet’s formatted display. Record encoding and newline rules for a real file. Preserve identifiers as text before any numeric conversion; lost leading zeros cannot generally be reconstructed from the remaining number alone.

2. State the schema and missingness policy

Define required unique headers, identifier roles, amount units, permitted signs, and missing markers. Decide which failures reject a row and which values remain explicitly missing. Do not fix a failed conversion with fill-zero unless the domain genuinely observed zero. In pandas, read_csv dtype and missing-value options must be reviewed explicitly; pandas is not executed in this environment.

3. Parse and reconcile distinct states

The complete program treats blank amounts as missing, malformed or nonfinite amounts as invalid, and zero as observed. It preserves identifier strings and computes the mean only over accepted measurements. Decimal is constructed directly from text. The output exposes all four input states instead of quietly changing the population behind the average.

import csv
import io
from decimal import Decimal, InvalidOperation

text = "id,amount\n001,12.50\n002,\n003,bad\n004,0\n"
rows = list(csv.DictReader(io.StringIO(text, newline="")))
observed = []
missing = invalid = 0
for row in rows:
    token = row["amount"].strip()
    if token == "":
        missing += 1
        continue
    try:
        value = Decimal(token)
    except InvalidOperation:
        invalid += 1
        continue
    if not value.is_finite() or value < 0:
        invalid += 1
        continue
    observed.append(value)
assert len(observed) + missing + invalid == len(rows)
print(f"rows={len(rows)} observed={len(observed)} missing={missing} invalid={invalid}")
print(f"total={sum(observed):.2f} mean={sum(observed) / len(observed):.2f}")

rows=4 observed=2 missing=1 invalid=1
total=12.50 mean=6.25

4. Check locale, units, and normalization

A token such as1,200 may mean a grouped integer or a decimal under different conventions. Removing punctuation blindly can multiply or divide a value’s meaning. Validate a documented source format and normalize units in a separately named step. Review raw-to-clean counts and a controlled sample of changes; do not overwrite the only raw evidence during experimentation.

5. Recheck the metric denominator

The observed-only mean describes observed values, not automatically all eligible records. Compare missingness and rejection by meaningful groups and inspect all-missing cases. If a correction changes the reported result, explain whether data were repaired, the eligible population changed, or an assumption about unobserved values changed. Those are different analytical updates and should not share a vague “cleaned” label.

Keep it from recurring

• Keep a versioned data dictionary, explicit type/missing policies, raw snapshots, and row-level rejection reasons. Test boundaries before aggregate reporting, and record package versions whenever inference defaults may change. Prefer an auditable no-data state to an invented zero.