Data Analysis / Basic: Questions and Trustworthy Tables / Lesson 4
Missing Values and Duplicate Records
Treat absence and repetition as properties to investigate, preserving their effect on the population and denominator.
On this page
Lesson goals
- Distinguish missing, zero, invalid, and not-applicable values.
- Describe missingness mechanisms without claiming they are observable facts.
- Deduplicate under an explicit event/key policy.
An empty cell does not explain its own cause
A missing value can mean not collected, unavailable, suppressed, not applicable, or a failed transformation. These meanings need not share one treatment. Zero is an observed value, while unknown is an information state. Preserve source markers and define which ones become analytical missingness rather than treating every blank, dash, and zero as interchangeable.
A parser can introduce absence too: a failed join or invalid conversion may create missing output even when the source contained text. Record where the state arose. A missingness table by field and subgroup can reveal a systematic capture problem that a single overall completeness percentage hides.
Report how many values a summary uses
The first program has four eligible records but only two observed measurements, one of which is zero. It reports the observation count beside the observed-value mean. Replacing None with zero would change the mean from five to two and a half and assert values that were never measured. Dropping zeros would be another unjustified change.
An observed-only summary describes those with measurements; it does not automatically estimate the entire eligible population. Report the missing count and consider whether the missing group differs. When no observations remain, the program should follow a defined no-result policy instead of dividing by zero or inventing a zero average.
from statistics import mean
values = [10, None, 0, None]
observed = [value for value in values if value is not None]
if not observed:
raise ValueError("no observed measurements")
print(f"observed={len(observed)}/{len(values)} mean={mean(observed):.2f}")
observed=2/4 mean=5.00
Ten and zero are the two measurements; their mean is five, with half the records missing.
A missingness assumption is not a discovered label
Missing completely at random means missingness is independent of the relevant data; missing at random allows dependence on observed information after conditioning; missing not at random can depend on unobserved values even after observed information is considered. These are assumptions about a data-generating process, not categories inferred reliably from one missing-count table.
Complete-case deletion or imputation may be reasonable under particular assumptions and purposes, but neither is universally safe. Mean imputation can reduce apparent variation and distort relationships. Preserve uncertainty about missing values, compare plausible treatments, and explain what additional collection or domain knowledge could distinguish the assumptions.
Define what counts as the same event
Duplicates can be repeated extracts, valid repeated events, or conflicting updates under one key. The second program treats an identical repeated event identifier and payload as an extract duplicate. It refuses conflicting payloads rather than choosing the first silently. Two different event identifiers from the same customer remain separate events.
Deduplicating by every field or by customer identifier answers different questions. A latest-record rule needs a trusted ordering field and a tie policy, not the accidental order of a CSV file. Reconcile removed counts and totals and keep enough evidence to show why a record was excluded.
rows = [("e1", "c1", 10), ("e1", "c1", 10), ("e2", "c1", 20)]
by_event = {}
for event, customer, amount in rows:
payload = (customer, amount)
if event in by_event and by_event[event] != payload:
raise ValueError("conflicting event payload")
by_event[event] = payload
print(f"input={len(rows)} events={len(by_event)} total={sum(value[1] for value in by_event.values())}")
input=3 events=2 total=30
The repeated e1 is counted once; distinct e2 remains, producing two events and total30.
Missing-value behavior depends on the tool and dtype
Python None, floating NaN, SQL NULL, and pandas missing sentinels have different comparison and aggregation rules. Test missingness with the toolβs intended operation, not equality with NaN. In pandas, isna recognizes supported missing states and nullable dtypes have their own semantics; treating pd.NA as an ordinary Boolean is not a safe decision rule.
Record whether an aggregate excludes missing values and which denominator it reports. SQL COUNT(*) counts rows while COUNT(column) counts non-null values, a distinction developed later. A pipeline should test all-missing groups and absent keys explicitly. Preserve the distinction between a group with zero observed total and a group with no usable measurements.
Common errors and fixes
A mean improves after missing values become zero.
- Why it happens
- Unobserved values were turned into measurements.
- How to fix it
- Keep missingness explicit and justify any imputation.
- Prevent it next time
- Report observed and eligible counts.
Deduplication removes legitimate repeat orders.
- Why it happens
- Customer identity was mistaken for event identity.
- How to fix it
- Use the declared event grain and key.
- Prevent it next time
- Test two valid events for one entity.
A conflicting duplicate silently chooses a version.
- Why it happens
- Input order substitutes for an update rule.
- How to fix it
- Reject conflicts or use a documented timestamp/tie policy.
- Prevent it next time
- Audit duplicate-key payload differences.
Check your understanding
1. A group has values [None,None], and another has [0,0]. Should both be reported as total zero without qualification?
Show worked solution
No. The second group has two observed zero values, while the first has no measurements. A chosen aggregate may return the same numeric result mechanically, but the analytical states differ. Report observed counts and a no-data flag or missing result for the first group under the metric contract. Do not claim zero activity from missing capture.
2. An event key repeats with amounts10 and12. State two defensible policies and what evidence each requires.
Show worked solution
Reject the key as a conflict pending source review, or select a documented authoritative/latest version using a trusted update timestamp and tie rule. Neither policy should infer authority from file order. Preserve both raw records, identify affected counts, and show whether the metric changes. Keeping the first merely because it is convenient is not a provenance argument.
Key takeaways
- Missing is an information state, not automatically zero.
- Deduplication needs a grain, key, and conflict policy.
- Every summary should reveal its usable-data denominator.