QST: Why does Pandas change data type from object to float after reading 32768 rows from CSV file?

[dwervin]

• I have searched the [pandas] tag on StackOverflow for similar questions.

• I have asked my usage related question on StackOverflow.

---

Question about pandas

Using Pandas 1.0.3.

I have a CSV with 43,00 rows. I noticed that one of the columns is object type even though it has floats. It looks like this happened because a few records contain a string that reads 'Bad Input'. However, after 32768 rows, the data becomes floats. I found it strange that this happens after exactly 32K rows.

I've used Pandas for a few years now, but I've never seen an inconsistency like this.

Is this a bug? How do I work around this?

Note: If you'd still like to submit a question, please read this guide detailing how to provide the necessary information for us to reproduce your question.

# Your code here, if applicable

[dwervin]

Looking at this some more. This seems like a serious issue when a column appears to be one type but actually contains two distinct types of data.

[smithto1]

Hi @dwervin ,

If all the rows in a column are nicely formatted floats, they will get dtype float. But once a single row appears that has a string (or anything not coercible to a float), that column will get dtype object. Any column with mixed types will have to get dtype object.

See examples below. When you take just the first two rows, they get dtype float. But read all the rows and they get dtype object.

I think the best solution is to use na_values to recognise the 'Bad Input' string as NaN value. Then you get dtype float.

import pandas as pd
from io import StringIO

data = """col1,col2
1.5,2.5
1.6,2.6
1.7,Bad Input
"""

df1 = pd.read_csv(StringIO(data), nrows=2)
print(df1)
print(df1.dtypes)

df2 = pd.read_csv(StringIO(data))
print(df2)
print(df2.dtypes)

df3 = pd.read_csv(StringIO(data), na_values=['Bad Input'])
print(df3)
print(df3.dtypes)

[dwervin]

@smithto1 Sorry, I might not have been clear enough so I will try again. I understand that when it gets to the the string in that field that it is supposed to convert the column to object, but what I'm saying is that it only appears to do that. If I do df.info(), it reports that the column is object. Since I was interested in filtering out the floats converted to object, I used .replace('.','',1).isdigit() to filter out the floats as object. Unfortunately, this did not return True/False in all cases as I noticed NaN's at the end which I thought was odd.

Ultimately I found that after 32,768 (32K) rows, the data in that column turned to float. Using type() to step through each row showed that they changed to float type after 32768 rows. This column DID NOT contain all object. It contained object and floats. To me, this seems like a serious problem.

Let me check the data to see if I can send it to you and you can try for yourself.

[smithto1]

Hi @dwervin, the best thing to do is if you can include a minimal reproducable example. Maybe just include a few rows of the data that illustrates the problem. It is hard to recommend any fix from your description.

[dwervin]

Can I upload the data somehow? The minimal working example needs more than 32,768 rows of data so you can see for yourself how it switches data type at 32K rows.

[dwervin]

I uploaded some test data. This should demonstrate what's going on.

TestData.zip

df = pd.read_csv('data/TestData.csv')

From df.info() the last column says it's object.

Unfortunately, when I look at type of data at 32,767 it shows str, but at 32,768 and beyond, it's a float.

This seems like a serious issue to me if I am expecting object, but the data type changes to float after 32K records.

[attack68]

I cannot reproduce this in pandas 1.1.0 with basic data:

import pandas as pd
import numpy as np
from io import StringIO

data = np.random.randn(100000).reshape((-1,2))
df = pd.DataFrame(data, columns=['a', 'b'])
df.iloc[32999,0] = 'text'
scsv = df.to_csv(index=False)
df2 = pd.read_csv(StringIO(scsv))
df2.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 50000 entries, 0 to 49999
Data columns (total 2 columns):
 #   Column  Non-Null Count  Dtype  
---  ------  --------------  -----  
 0   a       50000 non-null  object 
 1   b       50000 non-null  float64
dtypes: float64(1), object(1)
memory usage: 781.4+ KB

df3 = df2.applymap(lambda x: type(x) == str)
df3.sum()

a    50000
b        0
dtype: int64

When I import your data I receive console warning about FPROB:

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

You can the reason here: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.errors.DtypeWarning.html

which explains that dtype is analysed per chunk read and since you have a lot of data, i.e. more columns, then you will have multiple chunks.

[dwervin]

As I initially explained, I am using Pandas 1.0.3. Maybe that's an issue, but I really think the data and the order of the records with the 'Bad Input' combine to reveal this bug.

I understand the warning, but the expectation is that it should make the entire column an object type which it clearly does not do. As a result of this warning, I tried to search for the data that triggered this. Assuming that the column was all object because that's what it's supposed to do, I came across unexpected errors when trying to find rows that were not numeric. The errors happened after 32,768 because the data contained in the 'object' type column were actually float.

If Pandas is reading data into 32K chunks, does this mean we could have mixed types in a column if the type changes in each chunk? Seems like a bad design if that's the case.

Does anyone have an estimate on when this bug might be fixed?

[attack68]

This isn't a bug.

The warning and the associated documentation explain the issue, but for your info:

CSV files are read, in chunks, by rows not by column, meaning that if you have a lot of columns a default 'chunk' will consist of fewer rows. My example had 2 columns and it read 50,000 rows in a single chunk without issue. You example has 25 columns and obviously chunks it at 32k rows.

You might also be interested to explore the arguments contained within method read_csv. The specific documentation and the warning advises how the mixed datatype column can be avoided.

low_memorybool, default True
    Internally process the file in chunks, resulting in lower memory use while parsing, but possibly mixed type inference. To ensure 
    no mixed types either set False, or specify the type with the dtype parameter. Note that the entire file is read into a single 
    DataFrame regardless, use the chunksize or iterator parameter to return the data in chunks. (Only valid with C parser).

memory_mapbool, default False
    If a filepath is provided for filepath_or_buffer, map the file object directly onto memory and access the data directly from 
    there. Using this option can improve performance because there is no longer any I/O overhead.

chunksizeint, optional
    Return TextFileReader object for iteration. See the IO Tools docs for more information on iterator and chunksize.

[jreback]

Does anyone have an estimate on when this bug might be fixed?

@dwervin pandas is completely volunteer; if you would like to submit a PR, then reviewers will take a look.

so to answer your questions, there is no expectation

[dwervin]

@attack68 Thanks for the additional info. I have changed the read statement to read data in that column as object.

When users run into this mixed type warning, is Pandas supposed to convert the column to object?