BUG: unexpected KeyError with Index.get_loc

[liuyu81]

Index.get_loc raises unexpected KeyError when data is a 2-dimensional numpy.ndarray with non-unique, monotonically increasing values, i.e.,

>>> import numpy as np
>>> import pandas as pd
>>> ix = pd.Index(np.array([["A"], ["A"], ["B"]]))
>>> ix.get_loc(ix[0])
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python2.7/site-packages/pandas/core/index.py", line 1572, in get_loc
    return self._engine.get_loc(_values_from_object(key))
  File "pandas/index.pyx", line 134, in pandas.index.IndexEngine.get_loc (pandas/index.c:3824)
  File "pandas/index.pyx", line 149, in pandas.index.IndexEngine.get_loc (pandas/index.c:3651)
  File "pandas/index.pyx", line 169, in pandas.index.IndexEngine._get_loc_duplicates (pandas/index.c:3990)
KeyError: ('A',)

But it works correctly when the Index is either unique, i.e.,

>>> ix = pd.Index(np.array([["A"], ["B"], ["C"]]))
>>> ix.get_loc(ix[0])
0

or non-monotonic, i.e.,

>>> ix = pd.Index(np.array([["A"], ["B"], ["A"]]))
>>> ix.get_loc(ix[0])
array([ True, False,  True], dtype=bool)

p.s. pandas.__version__ 0.16.2; numpy.__version__ 1.9.3

[max-sixty]

What are you trying to do with the Index? Currently, it's an Index of tuples. Are you trying to create a MultiIndex? That's the only form of a 2D array in an Index that would make sense, I think.

In [26]: ix = pd.Index(np.array([["A"], ["A"], ["B"]]))

In [27]: ix
Out[27]: Index([('A',), ('A',), ('B',)], dtype='object')

If you have a normal Index using those strings, this works as expected:

In [28]: ix = pd.Index(["A", "A", "B"])

In [29]: ix
Out[29]: Index(['A', 'A', 'B'], dtype='object')

In [30]: ix.get_loc(ix[0])
Out[30]: slice(0, 2, None)

On the specific behavior, I'll leave it to the maintainers to decide whether it's a bug, but it at worst seems like a corner case.

[shoyer]

Thanks for the report! This is actually a bug upstream in NumPy: numpy/numpy#6390

I do agree with @MaximilianR, though, that using a 2D array as input for an index is generally not very useful.

[shoyer]

Turn out this isn't a NumPy bug after all, but rather an implication of the fact that NumPy treats ('B',) as an object that should be converted into a 1d array, np.array(['B']). So I guess this is technically on us -- we could add some sort of protection in _get_loc_duplicates to ensure that we don't coerce tuples blindly into 1D arrays.

[liuyu81]

@MaximilianR, @shoyer thanks for the comments.

FYI, Here is a bit of background on why we are using a 2D array as input to Index.

Basically, our code receives 2D numpy arrays (tables) from user input, which are extracted from a spreadsheet. The received 2D arrays contain a mixture of string headers and numerical data, i.e.

>> T1
array([['w', 'x', 'y', 'z'],
   ['A', 1, 2, 3],
   ['A', 4, 5, 6],
   ['B', 7, 8, 9]], dtype=object)
>> T2
array([['w', 'x', 'y', 'z'],
   ['A', "C", 2, 3],
   ['B', "D", 5, 6],
   ['A', "E", 8, 9]], dtype=object)

We create Index's for rows of the numerical parts of the tables so as to iterate / query / cross-reference the numerical records by the string headers, i.e.

>> ix1 = pd.Index(T1[1:,0:1])
>> ix2 = pd.Index(T2[1:,0:1])

For example, to get records for a key (all keys) that exist in both T1 and T2,

>> from itertools import filter
>> key = ix1[0]
>> if key in ix2:
....    print(T2[1:][ix2.get_loc(key)])
array([['A', 'C', '2', '3'],
   ['A', 'E', '8', '9']], 
  dtype=object)

Unfortunately, with the reported issue, the following use case (symmetric to previous case) does not work,

>> key = ix2[0]
>> if key in ix1:
....    print(T1[1:][ix1.get_loc(key)])
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python2.7/site-packages/pandas/core/index.py", line 1572, in get_loc
    return self._engine.get_loc(_values_from_object(key))
  File "pandas/index.pyx", line 134, in pandas.index.IndexEngine.get_loc (pandas/index.c:3824)
  File "pandas/index.pyx", line 149, in pandas.index.IndexEngine.get_loc (pandas/index.c:3651)
  File "pandas/index.pyx", line 169, in pandas.index.IndexEngine._get_loc_duplicates (pandas/index.c:3990)
 KeyError: ('A',)

Since our code has already tested that ix1 contains the key, the subsequent KeyError raised by ix1.get_loc(key) would largely complicate the program logic on the user side.

Also, since we do need to support higher-than-one-dimensional cases (i.e. ix2 = pd.Index(T2[1:,0:2])), it's not always feasible to fall-back to 1-D index.

[jorisvandenbossche]

@liuyu81 Why can't you construct the index as pd.Index(T1[1:,0]), so you have a 1D index, and the rest of your code examples should just work the same?

[liuyu81]

@jorisvandenbossche we do need to support higher-than-one-dimensional cases. It is part of user input as which table columns belong to searchable headers. For example, if user specifies that both column 0 and 1 in T2 should be searchable, we will end up with ix2 = pd.Index(T2[1:,0:2]). It's not always feasible to hard-code a 1-D index. As far as we are aware of, the reported issue is a corner case that only happens when the data to index has shape (n, 1).

[jorisvandenbossche]

You can use MultiIndex in the more dimensional case. Eg, if you use ix1 = pd.MultiIndex.from_arrays(T1[1:,0:2].T) this will give you a MultiIndex in this case, but a plain index in the 1-D case.
And the other examples with get_loc should work as well:

In [45]: ix1 = pd.MultiIndex.from_arrays(T1[1:,0:2].T)

In [46]: key = ix1[0]

In [47]: key
Out[47]: ('A', 1)

In [49]: key in ix1
Out[49]: True

In [51]: T1[1:][ix1.get_loc(('A', 1))]
Out[51]: array(['A', 1, 2, 3], dtype=object)

But of course, don't know what you want to do with it for the rest.

[jreback]

@jorisvandenbossche soln is by far the best.

In any event, this is where its breaking in index.pyx/Index/_get_loc_duplicates

In [30]: ix.values
Out[30]: array([('A',), ('A',), ('B',)], dtype=object)

In [31]: ix.values.searchsorted(ix[0], side='left')
Out[31]: array([0])

In [33]: ix.values.searchsorted(ix[0], side='right')
Out[33]: array([0])

[33] should be array[1].

So numpy looks broken here under py2, and so the .get_loc check fails.

note that this handles the duplicates properly.

In [35]: ix.reindex([ix[0]])
Out[35]: (Index([(u'A',)], dtype='object'), Int64Index([0, 1], dtype='int64'))

So a possiblity is to do a uniqueness check in .get_loc (its down in cython anyhow, so this is not adding overhead). Then using get_indexer_duplicates([key]). Though the code path in index.pyx exists IIRC mainly for non-tuples, so special casing for tuples seems like a bad work around.

I'll mark it as a bug, but would need some digging. Pretty low priority from my perspective as a MultiIndex is the correct and supported soln.