Python Doc & Data Automation

Fix pandas ParserError Tokenizing Data

The read stops on a row somewhere in the middle of the file:

ParserError: Error tokenizing data. C error: Expected 5 fields in line 8842, saw 7

The message is more precise than it looks. pandas fixed the field count from the header — five columns — and line 8,842 produced seven values after splitting on the delimiter. Two extra separators appeared where none were expected, and the parser refuses to guess which of the seven values belong in which of the five columns.

Root Cause

A CSV is delimiter-separated text with a quoting convention, and the convention is only as good as the writer that applied it. Four things produce extra fields.

A value contains the delimiter and was never quoted — an address like 17 Fenwick Road, Leeds written without quotes becomes two fields. A quoted value contains an unescaped quote character, which terminates the quoted section early and turns the rest of the value into fields. A row is genuinely malformed: a trailing delimiter, a concatenated record, a footer line with a different shape. Or the file mixes delimiters, usually because two systems appended to the same file.

The line number in the message is a physical line, which matters when the file contains legitimate embedded newlines inside quoted values: the offending record may span several lines above it.

Four ways a line grows extra fields A header defines five fields. Line A contains an unquoted comma inside an address and yields six fields. Line B contains an unescaped double quote which ends the quoted region early and yields seven fields. Line C ends with a trailing delimiter and yields six fields with the last one empty. Line D uses a semicolon delimiter in a comma file and yields one field. Each is labelled with the field count the parser reports. header: 5 fields id,name,address,amount,status 417,Dana,17 Fenwick Road, Leeds,1204.50,settled saw 6 418,"O"Brien Ltd","Leeds",98.00,pending saw 7 419,Acme,"Leeds",42.00,settled, saw 6 420;Beta;Leeds;17.00;failed saw 1 The reported line number is physical — a record with embedded newlines may start several lines earlier

Minimal Diagnostic

Never open a large file in an editor to find line 8,842. Read the raw lines, count fields the way the parser does, and print the offenders with their neighbours.

# pip install "pandas>=2.2,<3"
import csv
from pathlib import Path

CSV = Path("data/customers.csv")

def bad_lines(csv_path: Path, delimiter: str = ",", quotechar: str = '"',
              context: int = 1) -> list[dict]:
    """Find every physical line whose field count differs from the header's."""
    if not csv_path.exists():
        raise FileNotFoundError(f"No such file: {csv_path}")

    offenders = []
    with csv_path.open("r", encoding="utf-8", errors="replace", newline="") as fh:
        reader = csv.reader(fh, delimiter=delimiter, quotechar=quotechar)
        try:
            header = next(reader)
        except StopIteration as exc:
            raise ValueError(f"{csv_path} is empty") from exc
        expected = len(header)

        previous = None
        for number, row in enumerate(reader, start=2):     # line 1 is the header
            if len(row) != expected:
                offenders.append({
                    "line": number,
                    "fields": len(row),
                    "expected": expected,
                    "text": delimiter.join(row)[:120],
                    "previous": (delimiter.join(previous)[:80] if previous and context else None),
                })
            previous = row
    return offenders

if __name__ == "__main__":
    for entry in bad_lines(CSV)[:10]:
        print(f"line {entry['line']}: saw {entry['fields']}, expected {entry['expected']}")
        print(f"  {entry['text']}")

Using the csv module rather than a naive line.split(",") is deliberate: it honours quoting, so it flags only the lines a compliant parser would also reject. A line that split calls broken but csv.reader reads fine was never the problem.

The Fix: Repair the Source

When the file is generated by something you control, fixing the writer is the only durable answer — quote every field, or use a delimiter that cannot occur in the data.

# Standard library only
import csv
from pathlib import Path

def rewrite_quoted(src: Path, dest: Path, delimiter: str = ",") -> int:
    """Rewrite a CSV with every field quoted, so embedded delimiters are safe."""
    dest.parent.mkdir(parents=True, exist_ok=True)
    written = 0
    with src.open("r", encoding="utf-8", newline="") as fin, \
         dest.open("w", encoding="utf-8", newline="") as fout:
        reader = csv.reader(fin, delimiter=delimiter)
        writer = csv.writer(fout, delimiter=delimiter, quoting=csv.QUOTE_ALL)
        for row in reader:
            writer.writerow(row)
            written += 1
    return written

if __name__ == "__main__":
    print(rewrite_quoted(Path("data/customers.csv"), Path("out/customers_quoted.csv")), "rows")

QUOTE_ALL costs a little file size and removes the entire class of problem. When writing from pandas, the equivalent is frame.to_csv(path, quoting=csv.QUOTE_ALL, index=False) — the wider set of export defaults worth setting is in exporting data to CSV formats.

Variant Fix 1: Skip or Capture the Bad Lines

When the file arrives from elsewhere and must be processed today, decide explicitly what happens to unparseable rows. on_bad_lines accepts a callable, which is the version worth using because it keeps the rejects.

# pip install "pandas>=2.2,<3"
from pathlib import Path
import pandas as pd

def read_with_quarantine(csv_path: Path, reject_path: Path) -> pd.DataFrame:
    """Read a CSV, writing unparseable rows to a rejects file instead of failing."""
    rejects: list[list[str]] = []

    def collect(bad_line: list[str]) -> None:
        rejects.append(bad_line)          # returning None drops the line from the frame

    frame = pd.read_csv(
        csv_path,
        engine="python",                  # the callable form needs the Python engine
        on_bad_lines=collect,
    )

    if rejects:
        reject_path.parent.mkdir(parents=True, exist_ok=True)
        with reject_path.open("w", encoding="utf-8") as fh:
            for row in rejects:
                fh.write(",".join(row) + "\n")
        print(f"{len(rejects)} bad line(s) written to {reject_path}")
    return frame

if __name__ == "__main__":
    df = read_with_quarantine(Path("data/customers.csv"), Path("out/rejects.csv"))
    print(f"{len(df):,} row(s) parsed")

on_bad_lines="skip" is the blunt version: it discards silently, and a job that quietly drops 4,000 rows a month is a data-quality incident waiting to be discovered by an auditor. Capturing the rejects preserves the evidence — the pattern generalised in quarantine invalid rows in a pipeline.

Variant Fix 2: The Delimiter Is Not What You Think

Semicolon-delimited exports from European locales, tab-separated files with a .csv extension, and pipe-delimited feeds all produce either a ParserError or a single-column frame. Sniff the delimiter rather than assuming:

# Standard library plus pandas
import csv
from pathlib import Path
import pandas as pd

def detect_delimiter(csv_path: Path, sample_bytes: int = 8192) -> str:
    """Sniff the delimiter from a sample of the file."""
    with csv_path.open("r", encoding="utf-8", errors="replace") as fh:
        sample = fh.read(sample_bytes)
    try:
        return csv.Sniffer().sniff(sample, delimiters=",;\t|").delimiter
    except csv.Error:
        # Fall back to the most frequent candidate on the first line
        first = sample.splitlines()[0] if sample else ""
        return max(",;\t|", key=first.count)

if __name__ == "__main__":
    path = Path("data/customers.csv")
    sep = detect_delimiter(path)
    print("delimiter:", repr(sep))
    frame = pd.read_csv(path, sep=sep)

pd.read_csv(path, sep=None, engine="python") asks pandas to sniff internally, which is convenient interactively and slower on large files. In a scheduled job, detect once, log what was found, and pass it explicitly.

Variant Fix 3: Unescaped Quotes Inside Quoted Fields

A field written as "O"Brien Ltd" breaks the quoting state machine. Three escape hatches, in order of preference:

# pip install "pandas>=2.2,<3"
import csv
import pandas as pd

# 1. Best: the writer should double the inner quote -> "O""Brien Ltd"
frame = pd.read_csv("data/customers.csv", doublequote=True)   # default; nothing to change

# 2. When the writer used a backslash escape instead
frame = pd.read_csv("data/customers.csv", escapechar="\\")

# 3. Last resort: ignore quoting entirely and treat quotes as ordinary characters
frame = pd.read_csv("data/customers.csv", quoting=csv.QUOTE_NONE)

Option 3 changes the meaning of the file — any legitimate quoted value containing the delimiter will now split — so use it only when the file has no quoting at all. Encoding damage can present with similar symptoms; if the offending characters look like é rather than stray quotes, the real problem is covered in fixing encoding errors in CSV files.

Choosing between repairing and tolerating From a ParserError the first question is whether you control the writer of the file. If you do, the durable fix is to quote all fields at write time. If you do not, the next step is to confirm the delimiter and quoting settings, and after that to quarantine the rows that still cannot be parsed, writing them to a rejects file so no data is silently lost. ParserError line 8842, saw 7 Own the writer? YES Quote every field on write csv.QUOTE_ALL — removes the class NO Confirm sep and quoting sniff, then pass explicitly Quarantine the rest capture, never skip silently

Verification

Reconcile the row count against the file, and assert the reject rate stays inside a threshold you chose deliberately.

# pip install "pandas>=2.2,<3"
from pathlib import Path
import pandas as pd

def assert_parse_complete(csv_path: Path, frame: pd.DataFrame,
                          rejects: int, max_reject_pct: float = 0.5) -> None:
    """Fail when too many rows were rejected, or when the counts do not add up."""
    with csv_path.open("rb") as fh:
        physical = sum(1 for _ in fh) - 1                # header excluded

    accounted = len(frame) + rejects
    assert accounted <= physical, (
        f"counted {accounted:,} rows but the file has {physical:,} lines"
    )
    reject_pct = rejects / max(physical, 1) * 100
    assert reject_pct <= max_reject_pct, (
        f"{rejects:,} rejected row(s) = {reject_pct:.2f}% (limit {max_reject_pct}%)"
    )
    print(f"{len(frame):,} parsed, {rejects:,} rejected ({reject_pct:.2f}%)")

The <= in the first assertion rather than == is intentional: records with embedded newlines occupy several physical lines, so parsed rows are legitimately fewer than lines. What must never happen is more rows than lines, which would mean a record was split.

Field-count histogram of a broken file A histogram of how many fields each line splits into. The overwhelming majority of lines have five fields, matching the header. A small number have six, a handful have seven, and a single line has one. A shape like this means a few malformed lines in an otherwise sound file. A histogram with two tall peaks instead would mean the delimiter itself is wrong. Lines by field count (header declares 5) 1 field 5 fields 12,468 6 fields 9 7 fields 3 One dominant peak means a few bad lines; two peaks would mean the wrong delimiter

FAQ

Why does the error name a line that looks fine when I open it? The line number is physical. A record containing an embedded newline inside a quoted field starts earlier, so look at the lines above — the diagnostic prints the previous row for exactly that reason.

Is error_bad_lines=False still available? No, it was removed in pandas 2.0. Use on_bad_lines="skip", on_bad_lines="warn", or the callable form that captures rejects.

Can I just set names= to a longer list of columns? It stops the error by making the parser expect more fields, and it misaligns every row that was correct. Do this only when the header genuinely under-declares the columns, which you can confirm from the field-count histogram in the diagnostic.

Why does the Python engine succeed where the C engine failed? It is more permissive about ragged rows and supports the callable on_bad_lines. It is also several times slower, so use it to diagnose and, where possible, fix the file so the C engine can read it.

How should I handle a file where 30% of lines are bad? Stop parsing and fix the source. At that rate the file is not a CSV with a few defects — the delimiter, the encoding or the export itself is wrong, and every downstream number will be suspect.

Part of Cleaning Messy CSV Data with pandas.