Python Doc & Data Automation

Fix pandas DtypeWarning Mixed Types

Every read of a particular file prints the same warning, and the frame that comes back behaves oddly afterwards:

DtypeWarning: Columns (7,19) have mixed types. Specify dtype option on import or set low_memory=False.

Then arithmetic fails in a way that makes no sense for a numeric column:

>>> frame["amount"].sum()
TypeError: unsupported operand type(s) for +: 'float' and 'str'
>>> frame["amount"].dtype
dtype('O')

The warning is precise and easy to misread. pandas is not saying the column contains bad data — it is saying that it inferred two different types for the same column in different parts of the file, and had to fall back to object to hold both.

Root Cause

The C parser reads the file in internal blocks and infers each column's dtype per block. When block 1 of amount contains only digits it becomes float64; when block 40 contains "N/A" it becomes object; and to reconcile the two, pandas promotes the whole column to object. The warning names the column positions, not names, which is why it is so often ignored.

The usual culprits are a handful of sentinel tokens introduced by whatever exported the file: N/A, -, NULL, #N/A, TBC, a trailing total row, or a thousands separator that appears only in large values. Any one of them, anywhere in millions of rows, is enough.

How one bad token promotes a whole column A file is parsed in four internal blocks. Blocks one, two and four contain only numeric text and are inferred as float64. Block three contains the token N slash A and is inferred as object. When the blocks are reconciled into one column, the result is promoted to object dtype, and every value in the column becomes a Python object even though almost all of them are numbers. Inference happens per block, not per file block 1 float64 block 2 float64 block 3 object — holds "N/A" block 4 float64 reconciled column object — the whole column One sentinel in one block costs the memory and the arithmetic of every row The warning reports column positions — map them to names before doing anything else

Minimal Diagnostic

Turn the positional warning into names and offending values.

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

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

def name_columns(csv_path: Path, positions: list[int]) -> list[str]:
    """Translate the positions in the warning into column names."""
    header = pd.read_csv(csv_path, nrows=0)
    return [header.columns[i] for i in positions if i < len(header.columns)]

def offending_values(csv_path: Path, column: str, chunk_rows: int = 200_000) -> pd.Series:
    """Every value in a numeric-looking column that will not parse as a number."""
    bad = []
    for chunk in pd.read_csv(csv_path, usecols=[column], chunksize=chunk_rows, dtype=str):
        series = chunk[column]
        numeric = pd.to_numeric(series, errors="coerce")
        # non-null text that failed to convert is exactly what breaks inference
        bad.append(series[numeric.isna() & series.notna()])
    found = pd.concat(bad) if bad else pd.Series(dtype=str)
    return found.value_counts()

if __name__ == "__main__":
    names = name_columns(CSV, [7, 19])
    print("warned columns:", names)
    for name in names:
        print(f"\n{name}:")
        print(offending_values(CSV, name).head(10))

warned columns: ['amount', 'settled_on']

amount:
N/A          1284
-               97
1,204.50        12
TOTAL            1

That output is the whole diagnosis: three sentinel spellings and one stray total row. Reading with dtype=str in the diagnostic is deliberate — it stops pandas from converting anything, so nothing is hidden before you look at it.

Fix: Declare the dtype and the Sentinels

Two arguments together. dtype removes the inference; na_values tells the parser which tokens mean "missing" so they become NaN rather than strings.

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

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

SENTINELS = ["N/A", "n/a", "NA", "-", "--", "NULL", "null", "#N/A", "TBC", ""]

def read_clean(csv_path: Path) -> pd.DataFrame:
    """Read with explicit types and an explicit missing-value vocabulary."""
    try:
        frame = pd.read_csv(
            csv_path,
            dtype={
                "txn_id": "int64",
                "account": "category",
                "status": "category",
                "amount": "float64",     # sentinels become NaN, so this now holds
            },
            na_values=SENTINELS,
            keep_default_na=True,        # keep pandas' own list as well as these
            thousands=",",               # "1,204.50" parses as 1204.50
            parse_dates=["settled_on"],
        )
    except ValueError as exc:
        # Raised when a declared dtype cannot hold a value the sentinels did not catch
        raise ValueError(f"{csv_path}: unconvertible value — run the diagnostic: {exc}") from exc
    return frame

if __name__ == "__main__":
    frame = read_clean(CSV)
    print(frame.dtypes)
    print(f"{frame['amount'].isna().sum():,} missing amount(s)")

Declaring dtype changes the failure mode in a way that is entirely the point: an unexpected token now raises ValueError on the row that contains it, instead of quietly promoting the column and surfacing three functions later as a TypeError on a sum. Loud and early beats quiet and late.

Variant Fix 1: Keep the Bad Rows Instead of Coercing Them

errors="coerce" turns anything unparseable into NaN, which is right for sentinels and wrong for values you need to investigate. Split the two populations instead of merging them:

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

def split_unparseable(frame: pd.DataFrame, column: str) -> tuple[pd.DataFrame, pd.DataFrame]:
    """Separate rows whose value will not convert, so they can be reviewed not lost."""
    raw = frame[column].astype(str)
    numeric = pd.to_numeric(raw.str.replace(",", "", regex=False), errors="coerce")
    is_bad = numeric.isna() & raw.str.strip().ne("") & raw.ne("nan")

    good = frame.loc[~is_bad].assign(**{column: numeric[~is_bad]})
    quarantined = frame.loc[is_bad].assign(_reason=f"{column} not numeric")
    return good, quarantined

if __name__ == "__main__":
    frame = pd.read_csv("data/transactions.csv", dtype=str)
    clean, bad = split_unparseable(frame, "amount")
    print(f"{len(clean):,} clean row(s), {len(bad):,} quarantined")
    if not bad.empty:
        bad.to_csv("out/quarantine_amount.csv", index=False)

Writing the rejects to their own file keeps the pipeline running while preserving the evidence — the general pattern is set out in quarantine invalid rows in a pipeline.

Variant Fix 2: low_memory=False Is Not the Fix

The warning text suggests it, and it does silence the message — by making the parser read the whole file before inferring, which raises peak memory on exactly the files that are already large enough to be a problem.

# Silences the warning, raises memory, and still leaves the column as object
frame = pd.read_csv("data/transactions.csv", low_memory=False)
print(frame["amount"].dtype)     # still object if "N/A" is present

Use it only on small files where the warning is noise. On anything large, declare dtype — it is both cheaper and more informative. This matters most inside a chunked read, where each chunk infers independently and the dtypes can differ chunk to chunk; see handling large CSV files with chunking.

An export that ends with a TOTAL line puts text in every numeric column of the last row. Skip it at read time rather than filtering after:

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

# Drop a known number of footer lines (needs the Python engine)
frame = pd.read_csv("data/transactions.csv", skipfooter=1, engine="python")

# Or filter on a key column when the footer count varies between files
frame = pd.read_csv("data/transactions.csv", dtype=str)
frame = frame[frame["txn_id"].str.fullmatch(r"\d+", na=False)]
frame["amount"] = pd.to_numeric(frame["amount"].str.replace(",", "", regex=False))

The regular-expression filter is the more robust of the two, because it survives an export whose footer grows a second summary line. Related encoding-level surprises in the same class of file are covered in fixing encoding errors in CSV files.

Silencing the warning against fixing it From the DtypeWarning two paths lead away. Setting low_memory to False silences the message but keeps the column as object dtype and increases peak memory. Declaring dtype with na_values converts the sentinels to missing values, keeps the numeric dtype, and raises a ValueError on any future unexpected token, which is the desired behaviour. DtypeWarning columns 7, 19 low_memory=False warning gone, bug stays still object dtype higher peak memory dtype + na_values sentinels become NaN float64 preserved new tokens raise loudly Silencing a warning removes the message; declaring the type removes the defect What actually blocked the inference A ranked count of the non numeric tokens found in a single amount column across two million rows. N slash A appears one thousand two hundred and eighty four times, a bare hyphen ninety seven times, values carrying a thousands separator twelve times, and a single TOTAL footer row once. Together they are fewer than seven hundredths of one percent of the column, and they are enough to promote all of it to object dtype. Non-numeric tokens in one 2,000,000-row column "N/A" 1,284 "-" 97 "1,204.50" 12 "TOTAL" 1 0.07% of the column is enough to make 100% of it object dtype

Verification

Assert the dtypes you asked for actually arrived, and that coercion did not quietly eat live data.

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

EXPECTED = {"txn_id": "int64", "amount": "float64", "status": "category"}

def assert_dtypes(frame: pd.DataFrame, expected: dict[str, str], max_null_pct: float = 5.0) -> None:
    """Fail on a promoted dtype or on suspiciously heavy coercion."""
    for column, want in expected.items():
        assert column in frame.columns, f"missing column: {column}"
        got = str(frame[column].dtype)
        assert got == want, f"{column}: expected {want}, got {got} (a value did not convert)"

    for column in expected:
        null_pct = frame[column].isna().mean() * 100
        assert null_pct <= max_null_pct, (
            f"{column}: {null_pct:.1f}% null after parsing — coercion is eating real values"
        )
    print(f"{len(expected)} column(s) verified with the expected dtypes")

if __name__ == "__main__":
    frame = pd.read_csv("data/transactions.csv", dtype=EXPECTED, na_values=["N/A", "-", "NULL"])
    assert_dtypes(frame, EXPECTED)

The null-percentage assertion is the one that catches over-eager sentinel lists. Adding "0" or "None" to na_values by accident turns real values into missing ones, and nothing else in the pipeline will notice. For a stricter, declarative version of the same idea, express the contract as a schema — see validating document data with schemas.

FAQ

Why does the warning name numbers instead of column names? The message is emitted from the parser before the frame exists, so only positional indexes are available. Map them with pd.read_csv(path, nrows=0).columns, as the diagnostic does.

Can I just ignore it? Only if the affected column is genuinely text. If it is meant to be numeric or dated, the column is now object: arithmetic breaks, comparisons behave lexicographically, and memory is many times higher than needed.

Does converters= solve it? It works, but it runs a Python function per value and is therefore very slow on large files. Prefer dtype plus na_values, and keep converters for genuinely custom parsing such as mixed-unit strings.

Why did the warning appear only after I switched to chunked reading? Chunk boundaries change which values land in which inference block, so a sentinel that used to sit in the same block as other text now sits alone among numbers. The underlying data has not changed — the fix is the same explicit dtype.

Should I use nullable integer dtypes? Yes, when a numeric column can be missing: Int64 (capital I) holds pd.NA where plain int64 cannot, so you avoid the forced promotion to float that turns identifiers into 417.0.

Part of Handling Large CSV Files with Chunking.