index_col in read_csv and read_table ignores dtype argument

[makmanalp]

xref #11728 for the multi-index case
xref #14379 for converters

import pandas as pd
import numpy as np

from pandas.compat import StringIO

data = """Internets,Spaceships
01,a
02,b
03,c
04,d
05,e
06,f
"""

# No leading zeroes in the index because it interprets the column as numeric
print pd.read_csv(StringIO.StringIO(data), index_col="Internets")

# Expected to see trailing zeroes in the index still, got 1,2,3,4 instead. Index is int64.
print pd.read_csv(StringIO.StringIO(data), index_col="Internets", dtype={"Internets": np.object})

# Trailing zeroes now, index is object.
print pd.read_csv(StringIO.StringIO(data), dtype={"Internets": np.object}).set_index("Internets")

Version:

In [1]: 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: None
numpy: 1.9.1
scipy: 0.15.1
statsmodels: 0.6.1
IPython: 2.1.0
sphinx: None
patsy: 0.3.0
dateutil: 2.4.0
pytz: 2011c
bottleneck: None
tables: None
numexpr: 2.4
matplotlib: 1.3.1
openpyxl: 2.0.4
xlrd: 0.9.3
xlwt: 0.7.5
xlsxwriter: None
lxml: None
bs4: 4.3.2
html5lib: 0.999
httplib2: None
apiclient: None
rpy2: None
sqlalchemy: 0.9.6
pymysql: None
psycopg2: None

[jreback]

iIIRC this is already fixed in master
can u give a try

[makmanalp]

@jreback Still seems to happen in version 0.15.2-163-g671b384 commit 671b384 (from today). :( Sorry!

I'm guessing the reason for this is that the index assignment happens separately from the dtype conversion somehow - I dug around in parsers.py trying to see if I could find the root but no luck.

[jreback]

The values that are passed to the index constructor are object (e.g. the 01,02....etc), but the Index constructor will coerce these if possible (and here it IS possible), unless a dtype is passed. So to fix you need to pass the dtype if it is passed in the dtype spec to read_csv. want to do a pull-request?

[makmanalp]

My first pandas pull request! I'll give it a shot tomorrow or over the long weekend. Thanks!

Notes to self:

https://github.com/pydata/pandas/blob/master/pandas/io/parsers.py#L721
https://github.com/pydata/pandas/blob/master/pandas/core/index.py#L126
https://github.com/pydata/pandas/blob/master/pandas/io/parsers.py#L246
https://github.com/pydata/pandas/blob/master/pandas/io/parsers.py#L558

• What is dtypes / vs converters ?
• Stick this in cparserwrapper / python parser or higher up?

[Eastsun]

This bug still existed in 0.17.

[jreback]

@Eastsun doesnt the open tag on the issue make this clear?

[johanneshk]

Is there also a quick workaround for the following case? I write multi-indexed columns to csv and then read them as follows:

import pandas as pd
from io import StringIO

data=""",col11,col21
,col12,col22
uuid,,
0001,1,1
0002,2,2
"""

print(pd.read_csv(StringIO(data), header = [0,1], index_col=0, dtype={'uuid':object))

Omitting the index_col messes up the columns. Rebuilding the correct structure is a bit of a pain...

[jreback]

not sure how 'quick' this is....

In [35]: result = pd.read_csv(StringIO.StringIO(data),dtype={'uuid':object},skiprows=2).set_index('uuid')

In [36]: result.columns = pd.MultiIndex.from_tuples(pd.read_csv(StringIO.StringIO(data),header=[0,1],index_col=0,nrows=0).columns)

In [37]: result
Out[37]: 
     col11 col21
     col12 col22
uuid            
0001     1     1
0002     2     2

[gfyoung]

Nice minimal example here:

>>> from pandas import read_csv
>>> from pandas.compat import StringIO
>>>
>>> data = 'a,b\n01,2'
>>> read_csv(StringIO(data), index_col='a', dtype={'a': object})
   b
a   
1  2

The reason for this is because we have this awkward double conversion for indices. First, the column will be converted here. After this conversion, the column is correctly outputted. However, a second conversion will take place here, and it is this conversion that screws up everything.

The reason for this awkward double conversion is because for some reason or another, some functionality does depend on that second conversion. For example, if a converter is passed in for the column, the first conversion step is skipped, and the second one is relied on.

This is yet another indication of the much needed refactoring in the read_csv world.

[gfairchild]

Note that this bug also affects read_fwf.

[smcinerney]

Just checking, are both of these issues still open on 0.20+? Is makmanalp's resolved but johanneshk's case still open (the latter is still wrong on 0.20.3). Do we perhaps need to open a separate issue?

Also, in Python 3, import StringIO -> import io ... and instantiate io.StringIO

[grayfall]

@smcinerney the OP's (@makmanalp) hasn't been addressed as of 0.21.1.

[gwerbin]

Still open?

On 0.23.4:

import io
pd.read_csv(io.StringIO('''
version,downloads
1.1,100
1.2,1000
1.3,10000
'''), dtype={'version': str}, index_col='version').index.dtype
# dtype('float64')

[gfyoung]

@gwerbin : Indeed, this is an open issue.

[gwerbin]

I see.

The reason for this awkward double conversion is because for some reason or another, some functionality does depend on that second conversion. For example, if a converter is passed in for the column, the first conversion step is skipped, and the second one is relied on.

That bites. Is test coverage good enough in this area that this can be fixed without a big refactor?

[gfyoung]

Is test coverage good enough in this area that this can be fixed without a big refactor?

It should be. The problem is that there are a bunch of failures if you try to "correct" the behavior.

[RaverJay]

Ran into this today, seems still open.

Any preferred workaround in the meantime?