Reading a CSV With Inconsistent Column Counts in Python

The error message depends on the tool: Expected 3 fields in line 4, saw 4 from pandas, IndexError from your own indexing, a silent None from DictReader. The cause is the same, and it is rarely what the message suggests. Some record in the file has a different number of fields from the header, and the reason is almost always something earlier in that record or in a neighbor. This article is about finding those records, telling the common causes apart, and deciding what to do with each, using a small file that has four of them. The rule that all records have the same number of fields comes from RFC 4180. The tool-specific options, on_bad_lines in pandas and restkey in DictReader, are in the pandas article and the DictReader article. What follows is the diagnosis they depend on.

The file

Four columns, eight lines, and four different kinds of trouble. cat -n shows the line numbers:

cat -n ragged2.csv

     1	id,name,city,note
     2	1,Ana,Lisbon,likes tea
     3	2,Bo,Oslo,likes coffee, no sugar
     4	3,Cy,Lyon,
     5	4,Di,Rome,fine,
     6	5,Ed,Bern
     7	6,Flo,Kyiv,first line
     8	second line of the note
     9	7,Gus,Riga,ok

Line 3 has an unquoted comma inside the note. Line 4 is fine: it has an empty last field. Line 5 has a trailing comma. Line 6 is short. Lines 7 and 8 are one note that was broken across two lines without quoting.

Count the fields, and keep the line number

The first tool is a histogram of field counts, with a line number for every record that does not match. The csv.reader knows which physical line it has reached, so the line number of a record is the previous line_num plus one:

import csv
from collections import Counter

path = "ragged2.csv"
with open(path, newline="", encoding="utf-8") as f:
    reader = csv.reader(f)
    header = next(reader)
    want = len(header)
    counts = Counter()
    bad = []
    prev = reader.line_num
    for row in reader:
        counts[len(row)] += 1
        if len(row) != want:
            bad.append((prev + 1, len(row), row))
        prev = reader.line_num
print("header width:", want)
print("width histogram:", dict(sorted(counts.items())))
for line, n, row in bad:
    print(f"  line {line}: {n} fields {row}")

python /batches/csv-0001-0200/lab/py/widths.py

header width: 4
width histogram: {1: 1, 3: 1, 4: 4, 5: 2}
  line 3: 5 fields ['2', 'Bo', 'Oslo', 'likes coffee', ' no sugar']
  line 5: 5 fields ['4', 'Di', 'Rome', 'fine', '']
  line 6: 3 fields ['5', 'Ed', 'Bern']
  line 8: 1 fields ['second line of the note']

The header is 4 wide. The histogram is {1: 1, 3: 1, 4: 4, 5: 2}: four records of the right width, two of 5, one of 3 and one of 1. The four offenders are listed with their line numbers and the parsed fields: line 3 (5 fields), line 5 (5 fields), line 6 (3 fields), line 8 (1 field). That list is usually all the information you need to fix the file at the source. If the histogram shows one off-width count that is large, for example half the file at 5 fields, the problem is systematic, such as a delimiter inside values or a trailing comma on every row, and you fix it for the whole file. If it shows a few strays, you have damaged records.

Telling the causes apart

The same count can come from different causes, and the fix depends on which.

An unquoted delimiter in a value. Line 3 split likes coffee, no sugar into two fields, and the second begins with a space. The giveaway is that the record is wide by exactly the number of delimiters in the value, and the extra pieces read like the continuation of the last real column. The cure is at the source, which should quote that field. If you cannot change the source and you know that the extra delimiters are always in the last column, you can merge the surplus back, which I show below.

A trailing delimiter. Line 5, 4,Di,Rome,fine,, has an empty fifth field. Here the extra piece is empty, and often every row in the file has it, since a writer appended a comma to each line. The cure is to drop the trailing empty field, not to merge it into the previous one.

A short row. Line 6, 5,Ed,Bern, is missing its last field. It may be a record that was truncated, or a writer that omits empty trailing fields; in the second case the missing value is blank and padding is correct, in the first the row is damaged. Look at the neighbors to decide.

A record broken across lines. Lines 7 and 8 are one note that contained a newline, written without quotes. This is the one that fools the width check, because the first half, 6,Flo,Kyiv,first line, has exactly 4 fields and passes. Only the second half, with 1 field, is flagged. A check that looks only at widths would keep a truncated row 6 and reject the tail, and the data would be wrong in a way that no counter notices.

Quarantine first, repair second

I do not fix rows in the same pass that finds them. The first pass splits the file into rows that are certainly well-formed and rows that are not, and keeps the second group with their line numbers:

import csv

want = None
good = bad = 0
with open("ragged2.csv", newline="", encoding="utf-8") as src, \
     open("clean.csv", "w", newline="", encoding="utf-8") as ok, \
     open("rejects.csv", "w", newline="", encoding="utf-8") as rej:
    r, w_ok, w_rej = csv.reader(src), csv.writer(ok), csv.writer(rej)
    header = next(r)
    want = len(header)
    w_ok.writerow(header)
    w_rej.writerow(["line", *header, "extra"])
    prev = r.line_num
    for row in r:
        line = prev + 1
        prev = r.line_num
        if len(row) == want:
            w_ok.writerow(row)
            good += 1
        else:
            w_rej.writerow([line, *row])
            bad += 1
print(f"kept {good}, rejected {bad}")
print(open("rejects.csv", newline="").read(), end="")

python /batches/csv-0001-0200/lab/py/quarantine.py

kept 4, rejected 4
line,id,name,city,note,extra
3,2,Bo,Oslo,likes coffee, no sugar
5,4,Di,Rome,fine,
6,5,Ed,Bern
8,second line of the note

It kept 4 and rejected 4. rejects.csv has the line number in front of each record, so the file you hand to whoever owns the source says exactly where to look: lines 3, 5, 6 and 8. The kept file contains row 6 in its truncated form, 6,Flo,Kyiv,first line, which the width check could not flag. So "kept" means "well-formed", not "correct", and a note that this can happen should travel with the output. To catch it, add a content check that matches your data: a note column that ends mid-sentence, an id that is not an integer, or a file where the ids are sequential and one jumps. In this file the rejected line 8, second line of the note, has an id of second line of the note, which a numeric check on the first field would have flagged immediately.

When a repair is safe: merging into the last column

If a column of free text is always the last one, and the source does not quote it, extra fields can be joined back with the delimiter:

import csv

with open("ragged2.csv", newline="", encoding="utf-8") as f:
    r = csv.reader(f)
    header = next(r)
    want = len(header)
    for row in r:
        if len(row) > want:
            fixed = row[: want - 1] + [",".join(row[want - 1:])]
            print(f"{len(row)} fields -> {fixed}")

python /batches/csv-0001-0200/lab/py/merge_last.py

5 fields -> ['2', 'Bo', 'Oslo', 'likes coffee, no sugar']
5 fields -> ['4', 'Di', 'Rome', 'fine,']

The first line is right: ['2', 'Bo', 'Oslo', 'likes coffee, no sugar']. The second is wrong: the trailing comma on line 5 turned fine into 'fine,', because the repair cannot tell an extra empty field from a comma that belongs to the text. That is the general problem with automatic repair. It gives the right answer when the assumption holds and a plausible wrong answer when it does not. The assumption here was "extra fields are text that was split"; it is false for a trailing delimiter. If you repair, check for the trailing-empty case separately, and apply each repair only to the class of record it was written for. Better, count how many records each rule fixes and compare with how many you expected.

Other tools

awk -F, can compute the same histogram in a line, but it does not understand quotes. On a three line file with one quoted comma it reports the wrong width:

printf 'id,name,note\n1,Ana,"hello, world"\n2,Bo,plain\n' > quoted-awk.csv
awk -F, '{print NF}' quoted-awk.csv | sort | uniq -c

      2 3
      1 4

The quoted row, 1,Ana,"hello, world", counted as 4 fields where the file has 3, so awk would flag a good row. That is the reason I use the csv module for anything that may contain quotes. DuckDB can report the same information with store_rejects, as in the bad rows article, and for PostgreSQL, the COPY error extra data after last expected column is this same condition, with the fix described in the PostgreSQL article.

How to tell it worked

After a repair, the width histogram has a single entry. The number of records out equals the number in, minus the ones you rejected on purpose, and the rejected file has a row for each. Spot check a repaired row against the raw line. Re-run the content checks on the kept rows. And if a source system produces these files regularly, keep the rejects file from each run, since a rising count is the early sign that someone changed the export.

When a repair is a guess

Repairs that merge, pad or truncate are guesses about what the writer meant. They are justified when the source cannot be fixed, the cause is understood, and the same rule has been verified on enough rows. They are not justified for money, identities or anything where a wrong value costs more than a missing one. In those cases reject and ask. The most reliable fix is always upstream: a writer that uses a proper CSV library, so that a comma or a newline in a value is quoted and never breaks a record in the first place.