pandas read_csv Dtype Warnings and How to Fix Them

The warning reads: DtypeWarning: Columns (1: code) have mixed types. Specify dtype option on import or set low_memory=False. It appears on large files, it is often silenced by people who have never looked at the data, and what it describes is a real defect in the frame you get. A single column contains values of two Python types, and from that point everything that touches the column, equality, sorting, joins, group by, behaves differently depending on which half a row happens to be in. This article reproduces the warning on a 700,000 row file, shows exactly how the column is split and what that breaks, and gives three fixes in the order I would reach for them. The broader set of options for badly behaved files is in the pandas read_csv article.

Reproducing the warning

The file has id, code and v. For the first 650,000 rows code is a four digit number; after that it is A12, B07 or, now and then, another four digit number. It is the kind of column that starts as numeric codes and later acquires letters because a new system joined the feed:

import warnings
import pandas as pd

with warnings.catch_warnings(record=True) as w:
    warnings.simplefilter("always")
    df = pd.read_csv("mixed-col.csv")
print("warnings:", [f"{x.category.__name__}: {str(x.message)[:110]}" for x in w])
print(df["code"].dtype, len(df))
print(df["code"].map(type).value_counts().to_dict())

python /batches/csv-0001-0200/lab/py/pd_dtype_warn.py

warnings: ['DtypeWarning: Columns (1: code) have mixed types. Specify dtype option on import or set low_memory=False.']
object 700000
{<class 'int'>: 524288, <class 'str'>: 175712}

The warning is DtypeWarning: Columns (1: code) have mixed types; the column number is zero based, so 1 is code. The result is object dtype and 700,000 rows, as expected. The surprising line is the last: the code column holds 524,288 Python int objects and 175,712 str objects. Not one column of text with some numbers in it, but two populations.

Why the column splits

With low_memory=True, which is the default for the C engine, pandas reads the file in blocks to keep memory down and infers the type of each column independently in each block. The first block, 524,288 rows, was all numbers, so it was converted to integers. The later blocks contained A12 and B07, so they were kept as strings; even the four digit values among them, such as 5521, stayed text. At the end pandas concatenated the blocks. Concatenating an integer block and a string block gives an object column, and the warning tells you it happened. I did not look into how pandas picks the block size; here the cut fell at 524,288 rows, which is why the damage starts well before the first letter appears in the file at row 650,003.

What it breaks

The column is not unusable, which is why it is easy to miss. It is wrong in specific ways:

import warnings
import pandas as pd

warnings.simplefilter("ignore")
df = pd.read_csv("mixed-col.csv")
print("== 5521:", int((df["code"] == 5521).sum()), "| == '5521':", int((df["code"] == "5521").sum()))
print("distinct:", df["code"].nunique(), "| as text:", df["code"].astype(str).nunique())
try:
    df["code"].sort_values()
except TypeError as e:
    print("TypeError:", e)
fixed = pd.read_csv("mixed-col.csv", dtype={"code": "str"})
print("fixed == '5521':", int((fixed["code"] == "5521").sum()), "| distinct:", fixed["code"].nunique())

python /batches/csv-0001-0200/lab/py/pd_dtype_harm.py

== 5521: 49 | == '5521': 16670
distinct: 18002 | as text: 9002
TypeError: '<' not supported between instances of 'str' and 'int'
fixed == '5521': 16719 | distinct: 9002

• Equality. df["code"] == 5521 matched 49 rows. df["code"] == "5521" matched 16,670. The code 5521 appears 16,719 times in the file; either test alone reports a fraction of it. Filter by the wrong type and you silently drop almost all the rows you wanted.

• Counting distinct values. nunique() returned 18,002. The number of distinct codes as text is 9,002. Every numeric code was counted twice, once as an integer and once as a string.

• Sorting. sort_values() raised TypeError: '<' not supported between instances of 'str' and 'int'. That at least is loud.

• Joins and grouping behave like equality: a key column that holds 5521 as an int in one place and '5521' in another will not match a lookup table of either type, and group by will make two groups.

Silencing the warning is the worst response, since the defect remains and the one signal is gone. The first thing to do is decide what the column is.

Fix 1: declare the type

dtype is the most direct fix and the one I use. If code is an identifier, it is text, whatever it looks like:

import warnings
import pandas as pd

for label, kw in [
    ("dtype=str       ", {"dtype": {"code": "str"}}),
    ("low_memory=False", {"low_memory": False}),
    ("engine=pyarrow  ", {"engine": "pyarrow"}),
]:
    with warnings.catch_warnings(record=True) as w:
        warnings.simplefilter("always")
        df = pd.read_csv("mixed-col.csv", **kw)
    types = df["code"].map(lambda x: type(x).__name__).value_counts().to_dict()
    print(label, "dtype:", df["code"].dtype, "| python types:", types, "| warnings:", len(w))

python /batches/csv-0001-0200/lab/py/pd_dtype_fix.py

dtype=str        dtype: str | python types: {'str': 700000} | warnings: 0
low_memory=False dtype: str | python types: {'str': 700000} | warnings: 0
engine=pyarrow   dtype: str | python types: {'str': 700000} | warnings: 0

With dtype={"code": "str"} the column is str with 700,000 Python strings and no warning. The test == "5521" now matches the 16,719 rows, and nunique() says 9,002. Declaring dtype per column (the IO guide has the syntax) is also easier to reason about than inference, because the file's types are written next to the code that reads it. Do this for every column that is an identifier: account numbers, SKUs, ZIP codes (which pandas otherwise turns into numbers), phone numbers.

Fix 2: read the file in one piece

low_memory=False tells pandas to read the whole file and infer each column once. The second line of the output above is this option. The column became a single str dtype, with all 700,000 values strings and no warning, because the inference saw both numbers and letters and chose text for the lot. This costs memory, the whole file's strings at once, and it leaves the decision to the inference. I would use it to explore a file I do not know, and then replace it with a dtype once I do. For an enormous file, the memory is the reason low_memory exists.

Fix 3: use another engine

engine="pyarrow" is the third line of the output. It also produced a single str column and no warning. The pyarrow engine reads the file with Arrow's multithreaded CSV reader and infers types over the file in its own way; in the benchmark in the pandas and csv guide it was slightly slower and used a little more memory than the default on the 2,000,000 row file (1.04 s and 522 MB against 0.74 s and 490 MB). It supports fewer options than the C engine, so it is not a drop-in change for every file.

Cleaning up afterwards

If the column is supposed to be numeric and the letters are errors, convert it after reading and look at what failed:

import pandas as pd

df = pd.read_csv("mixed-col.csv", dtype={"code": "str"})
num = pd.to_numeric(df["code"], errors="coerce")
bad = df.loc[num.isna(), "code"]
print("non numeric values:", bad.value_counts().to_dict())
print("first bad row id:", int(df.loc[num.isna(), "id"].iloc[0]))

python /batches/csv-0001-0200/lab/py/pd_dtype_clean.py

non numeric values: {'A12': 16679, 'B07': 16668}
first bad row id: 650003

pd.to_numeric(..., errors="coerce") turns anything that is not a number into NaN, and selecting the NaN positions shows what they were: A12 16,679 times and B07 16,668 times, with the first at id 650,003. That is what the warning was about, made visible. Whether to drop them, map them or ask the owner of the feed is a question about the data, and the answer should be written down with the code.

How to tell it worked

Run the read again and confirm there is no warning, with warnings turned into errors in a test: warnings.simplefilter("error"). Check df["code"].map(type).value_counts(); a single type means the column is whole. Compare nunique() with the count you get from another tool such as SELECT count(DISTINCT code) in DuckDB. And filter on a value you know is common and compare the count with grep -c on the file.

When the warning is telling you the truth

Sometimes mixed types are the finding. A column that was meant to be numeric and has letters in it is a data quality problem, and dtype=str followed by to_numeric(errors="coerce") is how you measure it. The warning is also a signal that the file is large enough for chunking to matter, so if the load is slow or the memory is high, the article on reading only some columns is the next stop. What I would not do is warnings.filterwarnings("ignore") and carry on: of everything in this article, it is the one choice that makes the problem invisible.