Type inference problem with read_csv

[diehl]

I have a large CSV file that contains a single column of integers. When loading the CSV file with read_csv, nearly 2/3rds of the approximately 1.5 million values are loaded as ints while the remaining values are loaded as strings. I see no obvious problem with the file that would lead to this behavior. The file I used is available here: https://drive.google.com/file/d/0ByZvgdTf0yfAT2dPdHRvc2hLVkU/view?usp=sharing

test_df = pd.read_csv('test.csv',header=0)
from collections import Counter
c = Counter()
els = []
for el in test_df['SERIALNO'].values:
    c[type(el)] += 1
print c
Counter({<type 'int'>: 952026, <type 'str'>: 524288})

It appears that a continguous block of rows were read as strings - a block in the middle of the file. Very curious.

pd.show_versions()

INSTALLED VERSIONS

commit: None python: 2.7.9.final.0 python-bits: 64 OS: Darwin OS-release: 13.4.0 machine: x86_64 processor: i386 byteorder: little LC_ALL: None LANG: en_US.UTF-8

pandas: 0.15.2 nose: 1.3.4 Cython: 0.21 numpy: 1.9.2 scipy: 0.15.1 statsmodels: 0.5.0 IPython: 3.0.0 sphinx: 1.2.3 patsy: 0.3.0 dateutil: 2.4.1 pytz: 2014.9 bottleneck: None tables: 3.1.1 numexpr: 2.3.1 matplotlib: 1.4.0 openpyxl: 2.1.0 xlrd: 0.9.3 xlwt: 0.7.5 xlsxwriter: 0.5.7 lxml: 3.4.0 bs4: 4.3.2 html5lib: None httplib2: None apiclient: None rpy2: None sqlalchemy: 0.9.7 pymysql: None psycopg2: None

[jreback]

xref is #3866

something is throwing the inference engine off. You can try iterating by chunks and seeing where the invalid data is.

After its read in, you could do .convert_objects(convert_numeric=True). If this works might be a deeper issue.

[diehl]

@jreback I just tried the convert_objects call and it works. I've manually inspected the CSV file hunting for hidden characters and have seen nothing.

I found a similar problem on Stack Overflow that suggests this problem has been around for a bit. http://stackoverflow.com/questions/18471859/pandas-read-csv-dtype-inference-issue

All the evidence so far points to a deeper issue.

[jreback]

@diehl your pointed to issue is really completely different, though if you DID have actual string-likes then I suppose it could be the same (though it IS really hard to inspect visually for this kind of thing). That's why I suggested you iterate in read_csv using chunksize=1000 or something, and narrow down which chunk gives this error. OR if it doesn't then we can discuss a bug from there.

[diehl]

@jreback false alarm. after more digging I found the issue with the file. thanks for the feedback.

[jreback]

np