Indexing bug with Sparse DataFrames and column names not starting with 0

[rasbt]

Code Sample, a copy-pastable example if possible

import pandas as pd
import numpy as np

ary = np.array([ [1, 0, 0, 3],
                 [1, 0, 2, 0],
                 [0, 4, 0 ,0] ])

df = pd.DataFrame(ary)
df.columns = [1, 2, 3, 4]

dfs = pd.SparseDataFrame(df,
                         default_fill_value=0)

# DOES NOT WORK:

dfs.to_coo() # raises KeyError: 0

# WORKS (1)

dfs2 = dfs.copy()
dfs2.columns = [0, 1, 2, 3]
dfs2.to_coo()

# WORKS (2)

dfs3 = dfs.copy()
dfs3.columns = [str(i) for i in dfs3.columns]
dfs3.to_coo()

Problem description

In the example above, the Pandas SparseDataFrame method to_coo() (and possibly others) cannot handle sparse dataframes if the column names are integer types and don't start at 0. If the column names start at 0 or are string types, this is not an issue.

Expected Output

<3x4 sparse matrix of type '<class 'numpy.int64'>'
	with 5 stored elements in COOrdinate format>

Output of pd.show_versions()

INSTALLED VERSIONS

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

pandas: 0.23.4
pytest: None
pip: 18.1
setuptools: 40.2.0
Cython: 0.28.5
numpy: 1.15.4
scipy: 1.1.0
pyarrow: None
xarray: None
IPython: 6.5.0
sphinx: None
patsy: None
dateutil: 2.7.3
pytz: 2018.5
blosc: None
bottleneck: None
tables: 3.4.4
numexpr: 2.6.9
feather: None
matplotlib: 2.2.3
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: 1.0.1
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

[parkerdgabel]

I'll look into this.

[ghost]

I think this may be a bug. In Traceback info, we find dfs.to_coo() raise errors and track into find_common_type(types) function and in this function we find this code:

first = types[0]

The find_common_type() function's comment says:
"""
types : list of dtypes
""" .
Yes, it is where the error lies.

In fact, we check the dfs.dtypes and find its type is pandas.core.series.Series, not a list. But if we index a Series, first = types[0], the 0 as a key not the index location for this Series (This may be a feature of pd.Series). Using this way to get the first type in the function is not a good idea.

So I think in the find_common_type() function to add a type check for the param types or a simple way can be:

first = types[:1]

Then you can get what you excepted output.

[ghost]

Sorry, I gave last modifition first = types[:1] in find_common_type() (pandas.core.dtypes.cast.py) , but it can't pass the pandas test. And I update this:

first = [t for t in types][0]

The change passed Test and excepted output.

Hope to help you.

[jorisvandenbossche]

This also is a problem with the sparse accessor:

In [5]: ary = np.array([ [1, 0, 0, 3], 
   ...:                  [1, 0, 2, 0], 
   ...:                  [0, 4, 0 ,0] ]) 
   ...:  
   ...: df = pd.DataFrame(ary) 
   ...: df.columns = [1, 2, 3, 4]                                                                                                                                                                                  

In [6]: df = df.astype('Sparse[int64, 0]')                                                                                                                                                                         

In [7]: df.sparse.to_coo()                                                                                                                                                                                         
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-7-4c6c08d2fee9> in <module>
----> 1 df.sparse.to_coo()

~/scipy/pandas/pandas/core/arrays/sparse.py in to_coo(self)
   2208         from scipy.sparse import coo_matrix
   2209 
-> 2210         dtype = find_common_type(self._parent.dtypes)
   2211         if isinstance(dtype, SparseDtype):
   2212             dtype = dtype.subtype

~/scipy/pandas/pandas/core/dtypes/cast.py in find_common_type(types)
   1166         raise ValueError("no types given")
   1167 
-> 1168     first = types[0]
   1169 
   1170     # workaround for find_common_type([np.dtype('datetime64[ns]')] * 2)

~/scipy/pandas/pandas/core/series.py in __getitem__(self, key)
   1082         key = com.apply_if_callable(key, self)
   1083         try:
-> 1084             result = self.index.get_value(self, key)
   1085 
   1086             if not is_scalar(result):

~/scipy/pandas/pandas/core/indexes/base.py in get_value(self, series, key)
   4654         k = self._convert_scalar_indexer(k, kind="getitem")
   4655         try:
-> 4656             return self._engine.get_value(s, k, tz=getattr(series.dtype, "tz", None))
   4657         except KeyError as e1:
   4658             if len(self) > 0 and (self.holds_integer() or self.is_boolean()):

~/scipy/pandas/pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_value()

~/scipy/pandas/pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_value()

~/scipy/pandas/pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

~/scipy/pandas/pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item()

~/scipy/pandas/pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item()

KeyError: 0

[mroeschke]

This looks to work on master. Could use a test

In [1]:
   ...:
   ...: In [5]: ary = np.array([ [1, 0, 0, 3],
   ...:    ...:                  [1, 0, 2, 0],
   ...:    ...:                  [0, 4, 0 ,0] ])
   ...:    ...:
   ...:    ...: df = pd.DataFrame(ary)
   ...:    ...: df.columns = [1, 2, 3, 4]
   ...:
   ...:
   ...: In [6]: df = df.astype('Sparse[int64, 0]')
   ...:
   ...:
   ...: In [7]: df.sparse.to_coo()
Out[1]:
<3x4 sparse matrix of type '<class 'numpy.int64'>'
	with 5 stored elements in COOrdinate format>

[andthewatersays]

Hello !

I have the same problem than @jorisvandenbossche, and I do not understand what did you change @mroeschke in your script ... Could you explain it ?

Thank you !

[yifeim]

Same here on pandas==1.1.5 (last python3.6 version):

import pandas as pd
import numpy as np
import scipy.sparse as sps

df = pd.DataFrame.sparse.from_spmatrix(
    sps.diags([1,2,3]), ['a','b','c'], [1,2,3])
df.sparse.to_coo() # raises KeyError: 0

df = pd.DataFrame.sparse.from_spmatrix(
    sps.diags([1,2,3]), ['a','b','c'], ['1','2','3'])
df.sparse.to_coo().toarray() # [[1, 0, 0], [0, 2, 0], [0, 0, 3]]

df = pd.DataFrame.sparse.from_spmatrix(
    sps.diags([1,2,3]), ['a','b','c'], [0,3,2])
df.sparse.to_coo().toarray() # also [[1, 0, 0], [0, 2, 0], [0, 0, 3]]

While the first example failed, the next two examples gave consistent outputs, which are independent of the column names, as we expected. This then begs the question why do we need to check for column names in the first place?

<edit: add the third example to verify that the numerical column names do not affect the final orders of the numbers, as we would expect for the consistency with df.values>

[dna-witch]

take

[mroeschke]

Looks like this has a unit test now in test_to_coo so closing