Make invalid records visible

In this chapter

• Parse a small dataset into validated records without hiding rejected observations.
• Produce and check the chapter's practical artifact.

Text is not yet a dataset

CSV is an exchange format, not a schema. A field containing the characters 12 is still text until the program interprets it. Empty values, extra spaces and malformed dates are common integration problems. Decide which transformations are legitimate: trimming surrounding whitespace is usually different from guessing an absent count. Keep the original row number so a rejected record can be located again.

For Community Meter, accept a row only when hours is a positive number and loans is a nonnegative integer. Do not use a broad 'except: pass' block. It produces a neat result by making unexplained records disappear. A small rejection ledger turns a silent data-quality failure into a reviewable artifact.

Work through a parser

The sample keeps data inside the script so the exercise does not depend on downloads or private records. DictReader associates each field with its header. The conversion rules then produce Python numbers. A bad count triggers a ValueError, which is recorded alongside the source line. Production importers also need limits on file size, encodings and unexpected columns; this toy parser demonstrates only the validation contract.

import csv
from io import StringIO
source = "day,hours,loans\nMon,10,60\nTue,8,\nSat,6,48\n"
accepted, rejected = [], []
rows = list(csv.DictReader(StringIO(source)))
for line, row in enumerate(rows, start=2):
    try:
        hours, loans = float(row["hours"]), int(row["loans"])
        if not (0 < hours <= 24) or loans < 0:
            raise ValueError("outside range")
        accepted.append((row["day"].strip(), hours, loans))
    except (ValueError, KeyError):
        rejected.append({"line": line, "reason": "invalid hours or loans"})
assert len(accepted) + len(rejected) == len(rows)
print(accepted, rejected)

Reconcile before improving

The reconciliation assertion establishes a simple invariant: every input record is either accepted or rejected. It does not mean every accepted value is true. A mistaken count of 600 instead of 60 can satisfy the type and range checks. Add a plausibility review without silently replacing unusual observations.

Do not log full private rows when a line number and rejection category are enough. In a real workplace, logs can outlive the source file and have broader access. Use synthetic fixtures for development and document retention separately. Finally, avoid overwriting the source: a corrected output and a transformation record make later review possible.

Workbench

• Run the sample; inspect the rejection for line three.
• Add a negative-loans row and a row with surrounding day whitespace.
• Write a data dictionary explaining accepted types, range checks and unknown values.

Expected checks

• Two original records are accepted and one rejected.
• Input count always equals accepted plus rejected.

Check your understanding

What should happen to a missing loan count?

• Assume zero silently
• Record a rejection or a documented missing value
• Delete the whole dataset

Official tools & further reading

• Official reference