Clean, validate and document a table

The missing-data lesson teaches useful operations. A complete analysis also needs a reason for each change and checks that make mistakes visible. This lesson assumes that you can filter a DataFrame and select rows with .loc. Its small source table is deliberately invented and deliberately flawed.

Audit before repairing

Begin with the intended schema: record_id identifies a record, visits is a non-negative count, and institution is a label. Keep a copy of the received values so that rejected records remain inspectable. Repeated identifiers are not the same issue as repeated complete rows. A correction record and its earlier version can share an identifier while differing in other fields.

import pandas as pd
raw = pd.DataFrame({
    "record_id": ["r1", "r2", "r2", "r3", "r4"],
    "institution": [" North ", "SOUTH", "SOUTH", "East", None],
    "visits": ["12", "7", "7", "unknown", "-2"],
})
print(raw.duplicated().sum())
print(raw["record_id"].duplicated().sum())
assert raw.duplicated().sum() == 1

1
1

Here an exact duplicate is visible. Removing it is defensible if your source contract says each supplied row is one record and identical repeated rows are an ingestion duplication. In a transaction log, identical-looking rows could be separate transactions. The table alone does not establish which interpretation is right.

Normalize labels and parse types

For this teaching table, the intended labels differ only in spacing and capitalization. We document that assumption before normalizing. In real names, capitalization, accents and punctuation can carry meaning. Never use normalization as a substitute for a verified identifier.

work = raw.drop_duplicates().copy()
work["institution"] = work["institution"].astype("string").str.strip().str.casefold()
work["visits_numeric"] = pd.to_numeric(work["visits"], errors="coerce")
work["invalid_visits"] = work["visits_numeric"].isna() | (work["visits_numeric"] < 0)
print(work[["record_id", "institution", "visits_numeric", "invalid_visits"]])
assert work["invalid_visits"].sum() == 2

  record_id institution  visits_numeric  invalid_visits
0        r1       north            12.0           False
1        r2       south             7.0           False
3        r3        east             NaN            True
4        r4        <NA>            -2.0            True

The parser turns unknown into a missing numeric value because we explicitly requested coercion. The negative value remains numeric but violates the non-negative-count rule. Parsing and validation therefore answer different questions. The Pandas text guide documents vectorized string operations; use them to express a known transformation, not to guess the intended identities.

Separate accepted and unresolved records

A rejection table protects information you cannot responsibly fix. Include reason flags and preserve original values. Avoid replacing unknown visits with zero: zero claims that no visit occurred, whereas missingness says the value is unresolved.

rejected = work.loc[work["invalid_visits"]].copy()
accepted = work.loc[~work["invalid_visits"]].copy()
accepted["visits_numeric"] = accepted["visits_numeric"].astype("Int64")
assert accepted["record_id"].is_unique
assert accepted["visits_numeric"].ge(0).all()
assert accepted["institution"].notna().all()
print(accepted["record_id"].tolist(), rejected["record_id"].tolist())

['r1', 'r2'] ['r3', 'r4']

The accepted IDs are r1 and r2; r3 and r4 require follow-up. That is not proof that the rejected institutions had no activity. It means the current example cannot support a valid count for those records. State the exclusion when reporting results from accepted records.

Treat missingness as a question about evidence

The Pandas missing-data guide distinguishes missing sentinels and the operations used to detect them. In your analysis, additionally ask why a value is absent. Not applicable, not collected, suppressed and failed to parse are different states even if they are initially represented by the same sentinel.

Dropping rows changes the represented population. Mean imputation reduces visible variation and can alter relationships. Forward filling assumes a previous observation remains applicable; it is inappropriate for unrelated people or independently occurring events. Keep a missingness indicator when it helps communicate what was observed. If imputation is later learned for prediction, estimate its parameters on the training partition, not the entire dataset.

Use categories and range checks deliberately

A categorical type expresses a controlled label set, not a numeric scale. Ordered categories require an explicit order. A date range can be plausible while its unit or timezone is wrong. For counts and rates, check both the numerator and the denominator, and confirm that they refer to the same unit and period. For identifiers, test uniqueness at the intended key, which may include more than one column.

allowed = {"north", "south", "east"}
unexpected = set(accepted["institution"].dropna()) - allowed
assert not unexpected
log = {
    "received_rows": len(raw),
    "exact_duplicates_removed": len(raw) - len(work),
    "unresolved_rows": len(rejected),
    "accepted_rows": len(accepted),
}
print(log)
assert log["received_rows"] == 5 and log["accepted_rows"] == 2

{'received_rows': 5, 'exact_duplicates_removed': 1, 'unresolved_rows': 2, 'accepted_rows': 2}

Assertions make a failed assumption visible. They do not decide what the assumption should be. In a pipeline, stop at a violated essential constraint rather than exporting an attractive table that no longer has a defensible meaning.

Practice and independent check

Add a repeated ID with a different visit count. Explain why drop_duplicates() no longer resolves the key conflict. Add a missing institution to an otherwise valid row and decide whether it can enter an institution-level summary. Build an audit table with issue, affected IDs, proposed action, justification and result. Your independent deliverable is a cleaning log, accepted table, unresolved table and executable checks, not merely a table with no missing cells.

Transition

A validated table is ready to combine with another source. The next lesson checks whether that combination preserves the observation unit and whether the resulting shape answers the intended question.

Chapter exercisesWorked answers after an attempt