Technology · PRACTICAL GUIDE
pandas kept a bad CSV row despite skip? Validate records before usecols
on_bad_lines is a parser policy, not a complete schema check. Count logical fields before projection and reconcile records after loading.
A CSV can load without errors and still contain a short record, discarded extra fields or an unintended index. The current read_csv reference defines a bad line for on_bad_lines as one with too many fields. It does not promise that every business-invalid record will disappear.
Separate skipping from validation
Start with the producer’s delimiter, quoting rules, encoding and expected schema. Record pandas version, engine, header, names, index_col, usecols, blank/comment handling and bad-line policy. A filename ending in .csv does not establish those choices.
The current API says warn also skips the offending line, while skip omits its warning. Neither is a harmless repair switch. Keep the original file, and use deliberate rejection rules only when you can account for the excluded records.
Audit logical records before column projection
With the known encoding and dialect, open a working copy with newline='' and iterate csv.reader(..., strict=True). Check the header against the approved names; compare len(row) for every logical data record with the required width. Keep mismatch records separately with their source location. Do not count commas in raw text.
strict=True checks CSV syntax, not your schema. Track reader.line_num before and after each record to identify its physical span; embedded quoted newlines can make one record occupy several lines. Align blank/comment rules before comparing counts, and stop on parsing errors rather than inventing record boundaries.
Four controlled cases explain the traps
We tested synthetic three-column CSVs on pandas 2.2.3 with Python 3.12.14. A later four-field record raised ParserError; skip dropped it. Adding usecols kept the record while discarding the extra field. A two-field record remained with a missing value. These results are limited to that installed environment, not a runtime test of current pandas 3.0.6.
When the first data record had four fields, the default read inferred an index and shifted values; index_col=False returned three columns with a data-loss warning, not a repaired record. A separate quoted multiline field was one valid three-field record across two physical lines. No single flag certified all four cases.
Repair only with a rule you can verify
If the dialect or header was wrong, correct the import settings on a copy. If a record is genuinely malformed, obtain a corrected export or quarantine it under an explicit rule. Do not blindly remove the last field: an unexpected delimiter may have split a meaningful value in the middle.
After loading, verify intended column order and index, required identifiers, duplicates and domain constraints. Reconcile accepted plus rejected logical records with the source audit under the same filtering rules. Field-count agreement is only structural evidence; it cannot prove that values mean the right thing or that a projected DataFrame preserved all source information.
