data agent's csv parse silently dropping rows: row-count reconciliation

TL;DR

The CSV parse completes with no error but fewer rows than the file holds, usually because of quoted multiline fields or bad lines the parser skips quietly. Reconcile after every parse: count raw lines, count parsed rows, and investigate any gap instead of trusting a clean exit code.

Symptom

A data agent parses a CSV, reports success, but the parsed row count is lower than the file's line count. No error is raised. Downstream totals come up short and nobody notices until much later, because the parse step looked green.

When to use

• Row counts do not add up after a parse
• Files contain quoted multiline fields
• Parses succeed but downstream totals are short

When not to use

• The parser raises an explicit error (that already tells you what failed)
• Rows are dropped by a deliberate filter (check the filter, not the parser)
• The file is not actually CSV (fix the format assumption first)

Steps

1. Count raw lines independently of the parser:

wc -l data.csv

Expected output: the ground-truth line count. This is the number the parsed rows must reconcile against, minus the header.

2. Count parsed rows and compare:

import pandas as pd
df = pd.read_csv("data.csv")
print(len(df))

Expected output: a number you can diff against the line count. Any gap is dropped data until proven to be multiline fields.

3. Account for legitimate multiline records before assuming data loss. Quoted fields containing newlines make one record span several lines:

df = pd.read_csv("data.csv", engine="python")

Expected output: the python engine handles multiline quoted fields the C engine mangles. If the gap closes, the drops were multiline records, not lost rows.

4. Make bad lines loud instead of silent. Never let the parser skip them without a trace:

df = pd.read_csv("data.csv", on_bad_lines="warn")

Expected output: warnings name the offending lines. The default in some versions skips bad lines silently, which is exactly how rows vanish without a trace.

Variant phrasings

pandas read_csv fewer rows than expected

Run the four steps above. The usual culprits are multiline quotes, bad lines skipped silently, or a wrong separator splitting rows.

csv rows missing after agent ingestion

The agent trusted the parser's exit status. Add the reconciliation as a post-parse assertion in the agent's loop so it fails loudly.

Why it happens

CSV is a deceptively loose format: embedded newlines inside quoted fields, ragged rows, and stray quote characters are all legal-ish in the wild. Parsers handle these by heuristics, and when a heuristic gives up on a row, many configurations skip it rather than raise. A clean exit code then means the parser finished, not that it kept everything.

Edge cases

• A trailing newline at end of file inflates the line count by one. Subtract it before reconciling or you will chase a phantom gap.
• Files with a BOM or odd encoding can make the first row's fields merge. Specify the encoding explicitly.
• Separator auto-detection can split rows on commas inside unquoted fields. If the gap is large, verify the separator against the raw file first.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_m69HimJuzVty8MFrrciTjA