Ingesting Messy Files: Encoding, Schema Drift, and Malformed Rows

Data Quality with Pandas covers detecting bad values — a negative amount, a status outside the known set — inside an already-loaded DataFrame. This article is about the step before that even becomes possible: getting a well-formed DataFrame out of a file at all. A CSV export from a legacy system or a partner's nightly drop fails in ways a business-rule check never sees, because the file never successfully becomes a DataFrame in the first place.

Encoding: the silent corruption#

A file declared as UTF-8 that is actually Windows-1252 or another regional encoding does not usually raise an error — it decodes into the wrong characters, silently, and the corruption is only noticed later when a name or address looks subtly wrong:

import pandas as pd
df = pd.read_csv("export.csv", encoding="utf-8")

If encoding is wrong, pandas.read_csv either raises a UnicodeDecodeError — the better outcome, because it fails loudly — or succeeds with mangled text, which is worse because nothing flags it. When the true encoding is unknown, fail loudly first and decide from there:

try:    df = pd.read_csv("export.csv", encoding="utf-8")except UnicodeDecodeError:    df = pd.read_csv("export.csv", encoding="cp1252")

A byte order mark at the start of a file — common in exports from Windows tools — is a related trap: utf-8 decodes it as a stray character prefixed to the first column name, so df.columns shows 'id' instead of 'id' and every lookup by that name fails. encoding="utf-8-sig" strips the BOM if present and behaves exactly like utf-8 if it is absent, so it is a safe default for files of unknown provenance.

Malformed rows: fail the row, not the whole file#

A file that mostly conforms to RFC 4180 — the closest thing CSV has to a formal specification — can still have a few rows with an extra unescaped comma, an unterminated quote, or a wrong field count. The default behavior of read_csv is to raise and abort the entire read on the first such row:

df = pd.read_csv(    "export.csv",    encoding="utf-8-sig",    on_bad_lines="warn",)

on_bad_lines="warn" skips an unparseable row and prints a warning instead of aborting the whole load; "skip" does the same silently. Neither substitute for actually looking at what was skipped — capture and log the warnings, or read once with on_bad_lines="skip" and once counting total lines in the raw file, so the gap between the two numbers is visible instead of silently discarded.

Schema drift: the columns changed and nothing told you#

A source can add a column, remove one, rename one, or reorder them without any announcement. Code that reads columns positionally, or that assumes a column exists without checking, breaks in different ways depending on which of these happened:

EXPECTED_COLUMNS = {"order_id", "customer_id", "amount", "created_at"}
def validate_schema(df: pd.DataFrame) -> None:    actual = set(df.columns)    missing = EXPECTED_COLUMNS - actual    unexpected = actual - EXPECTED_COLUMNS
    if missing:        raise ValueError(f"Missing expected columns: {missing}")    if unexpected:        print(f"New columns not yet handled by this pipeline: {unexpected}")

The two cases are handled differently on purpose. A missing column means downstream code that reads it will fail — better to stop immediately with a clear message than let a KeyError surface three transformations later with no context. A new, unexpected column is not itself dangerous — nothing currently reads it — so it is worth logging rather than halting the pipeline over a column nobody asked for yet.

Selecting columns explicitly, always#

Regardless of what validate_schema finds, downstream code should select columns by name rather than trusting the file's full shape:

df = df[["order_id", "customer_id", "amount", "created_at"]]

Writing a DataFrame back to ClickHouse makes the identical point on the output side — explicit column selection is cheap insurance against a source appending a field your pipeline was never designed to carry forward.

Type coercion after the columns are right#

Even with the right columns present, read_csv infers types from a sample of the data and can guess wrong — an order_id column that is usually numeric but has one row with a stray letter becomes object (string) for the whole column instead of the numeric type every other row would suggest:

df["amount"] = pd.to_numeric(df["amount"], errors="coerce")invalid_amounts = df[df["amount"].isna()]

errors="coerce" turns unparseable values into NaN instead of raising, which lets the load continue while making the bad rows visible in invalid_amounts for the same kind of investigation Data Quality with Pandas covers for business-rule anomalies — the pattern is deliberately the same one, applied one stage earlier.

Common mistakes#

Assuming the declared encoding is correct#

A file's stated encoding — in an email, a filename, an API's Content-Type header — is a claim, not a guarantee. Decode defensively, especially for files from external partners.

Silently skipping malformed rows without counting them#

on_bad_lines="skip" with no logging turns a data-loss event into an invisible one. At minimum, log how many rows were skipped so a silent change in a source's export quality gets noticed.

Reading columns by position#

df.iloc[:, 2] for "the amount column" breaks the moment a source inserts a new column before it. Read and select by name.

Treating a new column the same as a missing one#

A missing expected column is usually a hard failure. A new, unexpected column is usually just information — collapsing the two into one check either raises too often or misses real breakage.

Further reading#

• RFC 4180: Common Format and MIME Type for CSV Files — the closest thing to a CSV specification, and where most real files deviate from it.
• Byte order mark — the invisible character behind the 'id' column-name bug.
• Windows-1252 — a common non-UTF-8 encoding behind silent text corruption.
• Comma-separated values — background on the format's history and its many informal variants.
• pandas.read_csv — the full parameter reference, including on_bad_lines and dtype.

See also#