Reading CSV Result Tables

Part II · Module 21 · Chapter 21.2 · Reading CSV Result Tables

1. The Table, and the Three Lines

test,seed,status,runtime_s,errors
axi_smoke,1839274,FAIL,143.2,2
axi_reset,771002,PASS,61.4,0
axi_burst,904113,NO_FINISH,7200.0,0

import csv

with open("regress.csv", newline="", encoding="utf-8-sig") as handle:
    for row in csv.DictReader(handle):
        print(f"{row['test']:<12} {row['status']:<10} {row['runtime_s']:>8}")

axi_smoke    FAIL          143.2
axi_reset    PASS           61.4
axi_burst    NO_FINISH    7200.0

csv.DictReader reads the first line as the field names and yields one dict per row afterwards. The fieldnames are available before you iterate, which is the cheapest possible check that you were handed the file you expected:

test,seed,status,runtime_s,errors
axi_smoke,1839274,FAIL,143.2,2

import csv

EXPECTED = ["test", "seed", "status", "runtime_s", "errors"]

with open("regress.csv", newline="", encoding="utf-8-sig") as handle:
    reader = csv.DictReader(handle)
    print("fieldnames:", reader.fieldnames)
    print("as expected:", reader.fieldnames == EXPECTED)
    missing = [f for f in EXPECTED if f not in (reader.fieldnames or [])]
    print("missing    :", missing)

fieldnames: ['test', 'seed', 'status', 'runtime_s', 'errors']
as expected: True
missing    : []

Two arguments in that open() are not decoration, and Sections 2 and 7 are about each of them.

2. The Header Row, and the Byte That Breaks It

A CSV exported from a spreadsheet usually starts with a byte-order mark — three invisible bytes before the first character. Opened as plain UTF-8, they end up inside the first field name:

import csv
import io

with open("bom.csv", "wb") as handle:
    handle.write("test,seed,status\naxi_smoke,1839274,FAIL\n".encode("utf-8-sig"))

with open("bom.csv", newline="", encoding="utf-8") as handle:
    reader = csv.DictReader(handle)
    print("utf-8     fieldnames:", reader.fieldnames)
    row = next(reader)
    print("utf-8     row['test']:", repr(row.get("test")))
    print("utf-8     row['seed']:", repr(row.get("seed")))

with open("bom.csv", newline="", encoding="utf-8-sig") as handle:
    reader = csv.DictReader(handle)
    print("utf-8-sig fieldnames:", reader.fieldnames)
    print("utf-8-sig row['test']:", repr(next(reader).get("test")))

utf-8     fieldnames: ['\ufefftest', 'seed', 'status']
utf-8     row['test']: None
utf-8     row['seed']: '1839274'
utf-8-sig fieldnames: ['test', 'seed', 'status']
utf-8-sig row['test']: 'axi_smoke'

row["test"] is None and row["seed"] is fine. Only the first column is affected, which is exactly why this survives a code review and a quick manual test — the file opens, the loop runs, four columns out of five are correct, and the test names are all blank.

encoding="utf-8-sig" reads the mark and discards it, and reads a file without one unchanged. It is the right default for any CSV that a human might have opened in a spreadsheet, which over a quarter is all of them.

3. The Blank Line Is Handled for You

A table with a blank line between sections, or a trailing newline somebody's editor added:

test,seed,status
axi_smoke,1839274,FAIL

axi_reset,771002,PASS

import csv

with open("gappy.csv", newline="", encoding="utf-8-sig") as handle:
    rows = list(csv.DictReader(handle))

print("rows:", len(rows))
for row in rows:
    print("  ", row)

rows: 2
   {'test': 'axi_smoke', 'seed': '1839274', 'status': 'FAIL'}
   {'test': 'axi_reset', 'seed': '771002', 'status': 'PASS'}

Two rows, no empty dicts, nothing to filter. DictReader skips a genuinely empty line, so the blank line the canonical purpose names is the one hazard on this list that needs no code at all.

A line of commas — ,, — is not blank and does yield a row of empty strings. That is a different thing and is usually a bug upstream, which Section 6's validation catches.

4. Three Spellings of Empty

This is the hazard worth the most care, because an empty field has three distinct representations and only two of them are obvious:

test,seed,status,runtime_s,errors
axi_smoke,1839274,FAIL,143.2,2
axi_reset,771002,PASS,,0
axi_burst,904113,NO_FINISH
axi_wrap,112233,PASS,61.0,0,extra,stuff

import csv

with open("ragged.csv", newline="", encoding="utf-8-sig") as handle:
    for row in csv.DictReader(handle):
        print(f"{row['test']:<11} runtime_s={row['runtime_s']!r:<9} errors={row['errors']!r}")

axi_smoke   runtime_s='143.2'   errors='2'
axi_reset   runtime_s=''        errors='0'
axi_burst   runtime_s=None      errors=None
axi_wrap    runtime_s='61.0'    errors='0'

Three different things:

'143.2' — a value, as a string.

'' — the column was present and the field was empty. Row two wrote ,, and that is what came back.

None — the row was short, so the field was never there. Row three has three values for five columns, and DictReader fills the rest with None.

That distinction is real and useful. '' usually means this run has no value for this field — a NO_FINISH with no runtime. None means this row is malformed, which is a different problem with a different owner.

Chapter 21.1 Section 6 showed that None and '' are indistinguishable when writing — both produce an empty field. On reading they are distinguishable, and they tell you different things.

The fourth row is the other direction:

test,seed,status,runtime_s,errors
axi_smoke,1839274,FAIL,143.2,2
axi_reset,771002,PASS,,0
axi_burst,904113,NO_FINISH
axi_wrap,112233,PASS,61.0,0,extra,stuff

import csv

with open("ragged.csv", newline="", encoding="utf-8-sig") as handle:
    for row in csv.DictReader(handle):
        if None in row:
            print("long row :", row["test"], "-> extras under the key None:", row[None])

with open("ragged.csv", newline="", encoding="utf-8-sig") as handle:
    reader = csv.DictReader(handle, restkey="_extra", restval="<missing>")
    for row in reader:
        if "_extra" in row:
            print("named    :", row["test"], "->", row["_extra"])
        if "<missing>" in row.values():
            short = [k for k, v in row.items() if v == "<missing>"]
            print("short    :", row["test"], "-> missing:", short)

long row : axi_wrap -> extras under the key None: ['extra', 'stuff']
short    : axi_burst -> missing: ['runtime_s', 'errors']
named    : axi_wrap -> ['extra', 'stuff']

A long row puts its surplus values in a list under the dict key None — a literal None as a dictionary key, which is startling the first time and is the documented behaviour. restkey="_extra" gives it a name you can test for, and restval replaces the Nones in a short row with something you choose.

Both are worth setting. The default behaviour is not wrong, but if None in row is a line nobody writes, so a long row goes unnoticed until the extra columns matter.

5. Everything Is a String

test,seed,status,runtime_s,errors
axi_smoke,1839274,FAIL,143.2,2
axi_reset,771002,PASS,,0

import csv

total = 0.0
with open("regress.csv", newline="", encoding="utf-8-sig") as handle:
    for row in csv.DictReader(handle):
        runtime = float(row["runtime_s"] or 0)
        errors = int(row["errors"] or 0)
        total += runtime
        print(f"{row['test']:<11} {runtime:>8.1f}s  {errors} errors  "
              f"{type(row['runtime_s']).__name__} -> {type(runtime).__name__}")

print(f"total {total:.1f}s")

axi_smoke      143.2s  2 errors  str -> float
axi_reset        0.0s  0 errors  str -> float
total 143.2s

That is the canonical annotation:

for row in csv.DictReader(f):
    runtime = float(row["runtime_s"] or 0)

and the or 0 is carrying all three of Section 4's cases at once. float('') raises ValueError and float(None) raises TypeError; or 0 turns both into 0, because '' and None are both falsy — Chapter 9.5.

Without it, the second row ends the script:

import csv
import io

TEXT = "test,runtime_s\naxi_smoke,143.2\naxi_reset,\n"

for row in csv.DictReader(io.StringIO(TEXT)):
    print(row["test"], float(row["runtime_s"]))

axi_smoke 143.2
Traceback (most recent call last):
  File "unguarded.py", line 7, in <module>
    print(row["test"], float(row["runtime_s"]))
                       ~~~~~^^^^^^^^^^^^^^^^^^
ValueError: could not convert string to float: ''

6. What or 0 Cannot Tell You

The idiom is right and it has one cost worth knowing before you copy it everywhere:

for value in ["143.2", "", "0", "0.0", None]:
    print(f"{value!r:<8} -> {float(value or 0)}")

'143.2'  -> 143.2
''       -> 0.0
'0'      -> 0.0
'0.0'    -> 0.0
None     -> 0.0

A missing value and a real zero become the same number. For runtime_s that is usually harmless — a run with no recorded runtime contributes nothing to a total, which is about right.

For a field where zero is a measurement, it is not harmless at all. coverage_pct of 0 is a result; coverage_pct of '' is a missing measurement, and averaging them together quietly pulls the average down with runs that were never measured.

When the difference matters, keep it:

def as_float(text, default=None):
    """None when there was no value; a float when there was one."""
    if text is None or text == "":
        return default
    return float(text)


VALUES = ["143.2", "", "0", None]
parsed = [as_float(v) for v in VALUES]
print("parsed :", parsed)

measured = [v for v in parsed if v is not None]
print("measured:", len(measured), "of", len(parsed))
print("mean of measured :", sum(measured) / len(measured))
print("mean with or-zero:", sum(float(v or 0) for v in VALUES) / len(VALUES))

parsed : [143.2, None, 0.0, None]
measured: 2 of 4
mean of measured : 71.6
mean with or-zero: 35.8

The same four values, two different means, and the second one is an average over two runs that were never measured. Which number is right depends on the question, and the point is that or 0 answers it without asking.

7. newline="" Is Not Cosmetic

The second argument from Section 1. The csv module asks for newline="" and the reason is specific — a field may legitimately contain a line break:

import csv

with open("crlf.csv", "wb") as handle:
    handle.write(b'test,note\r\naxi_smoke,"line one\r\nline two"\r\naxi_reset,ok\r\n')

for kwargs in ({"newline": ""}, {}):
    with open("crlf.csv", encoding="utf-8-sig", **kwargs) as handle:
        rows = list(csv.DictReader(handle))
    label = "newline=''" if kwargs else "default   "
    print(f"{label}  rows={len(rows)}  note={rows[0]['note']!r}")

newline=''  rows=2  note='line one\r\nline two'
default     rows=2  note='line one\nline two'

Both read two rows. The data is different. Without newline="", Python's universal-newline translation rewrites the \r\n inside the quoted field before csv ever sees it, so a value the file contained is not the value you get.

On a single-line field nothing changes, which is why the omission survives — until a note field, a command line or a first-failure message with a line break in it goes through, and then the file and the parse disagree with no error.

Set it on reads and on writes. Chapter 21.3 is the write side.

8. Two More the File Can Do to You

import csv
import io

print("duplicate headers:")
rows = list(csv.DictReader(io.StringIO("a,a,b\n1,2,3\n")))
print("  row :", rows[0])
print("  keys:", len(rows[0]), "for 3 columns")

print()
print("a line of commas is not a blank line:")
rows = list(csv.DictReader(io.StringIO("a,b,c\n,,\n1,2,3\n")))
print("  rows:", len(rows))
print("  first:", rows[0])

duplicate headers:
  row : {'a': '2', 'b': '3'}
  keys: 2 for 3 columns

a line of commas is not a blank line:
  rows: 2
  first: {'a': '', 'b': '', 'c': ''}

Duplicate column names: the last one silently wins, and the row has two keys for three columns. Nothing warns. A table with two columns called status — easy to produce by concatenating two reports — loses one of them entirely.

A line of commas yields a row of empty strings, not a skipped line. Section 3's blank line is skipped; this one is not, and it is usually a truncated write.

9. A Reader That Checks Its Input

Everything above, as the function a flow would actually call:

test,seed,status,runtime_s,errors
axi_smoke,1839274,FAIL,143.2,2
axi_reset,771002,PASS,,0
axi_burst,904113,NO_FINISH

import csv

REQUIRED = ["test", "seed", "status", "runtime_s", "errors"]


def as_float(text, default=None):
    if text is None or text == "":
        return default
    return float(text)


def read_results(path):
    """Rows as dicts with numbers converted. Returns (rows, problems)."""
    rows, problems = [], []
    with open(path, newline="", encoding="utf-8-sig") as handle:
        reader = csv.DictReader(handle, restkey="_extra", restval=None)

        missing = [f for f in REQUIRED if f not in (reader.fieldnames or [])]
        if missing:
            problems.append(f"header is missing {missing}")
            return rows, problems

        for number, row in enumerate(reader, 2):
            if "_extra" in row:
                problems.append(f"line {number}: {len(row['_extra'])} extra value(s)")
            short = [f for f in REQUIRED if row.get(f) is None]
            if short:
                problems.append(f"line {number}: no value for {short}")
            rows.append({
                "test": row["test"],
                "seed": int(row["seed"]) if row["seed"] else None,
                "status": row["status"],
                "runtime_s": as_float(row["runtime_s"]),
                "errors": int(row["errors"]) if row["errors"] else 0,
            })
    return rows, problems


rows, problems = read_results("regress.csv")
for row in rows:
    print(f"  {row['test']:<11} {row['status']:<10} runtime={row['runtime_s']!r:<7} "
          f"errors={row['errors']}")
print("problems:", problems)

  axi_smoke   FAIL       runtime=143.2   errors=2
  axi_reset   PASS       runtime=None    errors=0
  axi_burst   NO_FINISH  runtime=None    errors=0
problems: ["line 4: no value for ['runtime_s', 'errors']"]

Three rows parsed and one problem reported rather than raised — which is Chapter 19.18's collect-don't-break rule applied to a file instead of a test suite, and Chapter 20.6's contract applied to a reader.

enumerate(reader, 2) numbers from 2 because line 1 is the header, so the message names the line a person would find in an editor.

Note that axi_reset and axi_burst both end with runtime_s of None, for the two different reasons from Section 4 — and only one of them is in problems. That is the distinction doing its job: an empty field is data, a short row is a defect.

10. Predict, Run, Observe

import csv
import io

TEXT = "a,b,c\n1,,3\n4,5\n6,7,8,9\n"
rows = list(csv.DictReader(io.StringIO(TEXT)))

# A
print("A:", len(rows))

# B
print("B:", rows[0])

# C
print("C:", repr(rows[0]["b"]), repr(rows[1]["c"]))

# D
print("D:", rows[2].get(None))

# E
print("E:", float(rows[0]["b"] or 0), float(rows[1]["c"] or 0))

# F
print("F:", list(csv.DictReader(io.StringIO("a,b\n\n1,2\n"))))

# G
print("G:", csv.DictReader(io.StringIO(TEXT)).fieldnames)

The verified output:

A: 3
B: {'a': '1', 'b': '', 'c': '3'}
C: '' None
D: ['9']
E: 0.0 0.0
F: [{'a': '1', 'b': '2'}]
G: ['a', 'b', 'c']

A — three data rows from four lines, because the first is the header.

B — the middle field was present and empty, so ''.

C — '' from a present-but-empty field and None from a short row. Two spellings, two causes.

D — the long row's surplus went under the key None, as a list.

E — or 0 flattens both to 0.0, which is the convenience and the information loss together.

F — the blank line produced no row at all.

G — fieldnames is available without consuming any rows, which is what makes the header check in Section 9 free.

11. Exercises

Exercise 1 — the three lines. Read a results table with DictReader and print one aligned row per record.

Exercise 2 — check the header first. Print fieldnames and compare it against a required list before iterating. Say why this is better than failing on the first row.

Exercise 3 — the BOM. Write a file with utf-8-sig and read it back as plain utf-8. Record what fieldnames contains and what row["<first column>"] returns.

Exercise 4 — why only the first column. Explain in one sentence why the BOM corrupts exactly one field name.

Exercise 5 — get versus subscript. Read the BOM file with row.get("test") and with row["test"]. Say which failure you would rather have.

Exercise 6 — the blank line. Put a blank line and a line of commas in the same table. Report how many rows come back and what distinguishes them.

Exercise 7 — three empties. Build a table with a present-but-empty field and a short row. Print both values with repr and say what each one means about the producer.

Exercise 8 — restkey and restval. Add a long row. Find its extras with the default behaviour and then with restkey. Say why the named version is the one you would ship.

Exercise 9 — or zero. Convert a column with float(x or 0) and with a function returning None. Compute a mean both ways over data containing an empty field, and say which answer your report wants.

Exercise 10 — newline. Write a CRLF file with a line break inside a quoted field. Read it with and without newline="" and compare the field's repr.

Exercise 11 — duplicate columns. Read a table with two identically named columns. Report how many keys the row has and which value survived.

Exercise 12 — a checking reader. Write read_results for your own table. Run it on a file with one short row and one long row, and report the problems without raising.

12. Summary

• csv.DictReader reads the first line as field names and yields one dict per row; fieldnames is available before iterating, so a header check is free.
• A spreadsheet-exported CSV carries a BOM, and read as plain UTF-8 it lands in the first field name — so row["test"] is None and every other column is fine.
• Only the first column is affected, which is why the bug survives review.
• encoding="utf-8-sig" is the right default for any CSV a human might have opened.
• row.get() turns that into a blank column; row[...] turns it into a KeyError. Prefer the loud one.
• A blank line is skipped for you. A line of commas is not — it yields a row of empty strings.
• An empty field has three spellings: a value, '' when the column was present and empty, and None when the row was short.
• '' is data and None is a defect, and they have different owners.
• A long row's extras go under the dict key None — set restkey so you can test for them, and restval so a short row says what you chose.
• Every value is a string, so float(row["x"] or 0) is the canonical read, and or 0 covers '' and None together.
• or 0 cannot distinguish a missing value from a real zero — fine for a runtime total, wrong for a coverage average.
• newline="" is not cosmetic: without it, a \r\n inside a quoted field is rewritten before csv sees it, so the parse and the file disagree with no error.
• Duplicate column names: the last silently wins, and the row has fewer keys than the file has columns.
• Collect problems rather than raising, and number lines from 2 so the message matches an editor.

The next chapter writes these tables — where the column order, the quoting and the line endings all become decisions somebody else depends on.