Read, Clean and Validate CSV Data with Python

Cleaning data is a sequence of decisions, not a command that makes every row trustworthy. This exercise reads five fictional item records, applies a small published rule set and produces both accepted rows and a rejection log. You will keep the source text intact, make changes in derived records and explain which rows contributed to the final quantity total.

Documentation boundary

Python’s csv documentation provides DictReader, which maps row fields to dictionary keys and can take its field names from the first row. Parsing CSV structure does not establish this exercise’s numeric, category or missing-value rules; those checks are authored separately below.

Publish a small cleaning contract

The expected headers are item, qty and zone in that order. Item must not be empty. Quantity must be a nonnegative whole number. Zone must be north or south after surrounding whitespace is removed and letter case is normalised. An empty quantity is missing, not zero. A word such as many is invalid. Preserve a source line reference and a reason whenever a row is rejected.

Predict the accepted total before running the code. It should include only rows whose item, quantity and zone all satisfy the contract. Also predict how many rows will appear in the rejection log. A useful audit reconciles five input rows with two accepted rows and three rejected rows, rather than reporting a total without an explanation of exclusions.

Worked parsing and validation

import csv
from io import StringIO

raw = "item,qty,zone\n A1 , 3 , north \nA2,,south\nA3,many,north\nA4,-2,north\nA5,2,SOUTH\n"
reader = csv.DictReader(StringIO(raw))
assert reader.fieldnames == ["item", "qty", "zone"]
accepted, rejected = [], []
for line, row in enumerate(reader, start=2):
    item, token, zone = (row[k].strip() for k in reader.fieldnames)
    zone = zone.lower()
    if not token:
        rejected.append((line, "missing quantity"))
        continue
    try:
        qty = int(token)
    except ValueError:
        rejected.append((line, "invalid quantity"))
        continue
    if qty < 0 or zone not in {"north", "south"} or not item:
        rejected.append((line, "rule violation"))
        continue
    accepted.append((item, qty, zone))
assert accepted == [("A1", 3, "north"), ("A5", 2, "south")]
assert rejected == [(3, "missing quantity"), (4, "invalid quantity"), (5, "rule violation")]
assert sum(r[1] for r in accepted) == 5
print(accepted, rejected)

The accepted quantity total is five: three from A1 and two from A5. The whitespace and uppercase zone in the source do not change those quantities. The rejected source line references are three, four and five because the header occupies line one. In this small fixture each record occupies one physical line; a general CSV audit would need a careful record-reference policy for quoted multiline fields.

Notice that the original raw string is not overwritten. The cleaned values are stored separately in accepted. This gives you something to compare when checking whether a transformation was justified. The rejection log stores categories of failure rather than silently discarding rows. In a larger exercise you might preserve a nonpersonal row identifier too, but no real data is needed here.

Diagnose accidental data invention

A tempting repair replaces every failed conversion with zero. Under the stated contract it would make A2 and A3 look like measured zero quantities, even though neither source supplied that information. A second tempting repair changes the negative value to its absolute value. That invents a positive quantity. Diagnose these as policy violations before checking arithmetic: a numerically consistent result can still be based on unsupported transformations.

The worked reader assumes the supplied fixture has the expected number of fields. Do not call it a complete validator for every possible CSV file. For an extension, add one row with an extra field and one with a missing field, then specify whether those records should be rejected before conversion. Write the expected rejection reasons first. A parser successfully returning a row is not proof that its schema matches your contract.

Prepare an audit handover

Submit the untouched fixture, the rule table, the accepted records, the rejected line references and the total. Explain why A5 is safely normalised while A4 is not safely repaired. The difference is that changing letter case follows an explicit category rule, while changing a quantity would alter the observation. Keep those two kinds of transformation separate in your explanation.

For a final check, change only A2 to a genuine zero. The expected result becomes three accepted rows, two rejected rows and the same total of five. This controlled variation demonstrates why a row count and a total measure different things. A zero can be a valid observation even when it does not increase the total. Record both the changed acceptance count and the unchanged arithmetic answer.

Questions for this exercise

Official documentation

• CSV reading

Explore the Courses overview or use Contact for general enquiries. This is an original INTIAU editorial self-study exercise; no qualification is awarded.

View the study diagram