Incoherence between Index and MultiIndex when labels in list are not found

[toobaz]

Code Sample, a copy-pastable example if possible

In [2]: s = pd.Series(range(4), index=pd.MultiIndex.from_product([[1,2], ['a', 'b']]))

In [3]: s.loc[['not', 'found']]
Out[3]: Series([], dtype: int64)

Problem description

With regular Indexes, looking for a list of labels of which none is present will raise KeyError (see below). We should be coherent (and while I would prefer the empty result to the exception, this was already discussed in #7999).

Expected Output

Compare to

In [4]: s.reset_index().loc[['not', 'found']]
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-4-e13655430320> in <module>()
----> 1 s.reset_index().loc[['not', 'found']]

/home/nobackup/repo/pandas/pandas/core/indexing.py in __getitem__(self, key)
   1339         else:
   1340             key = com._apply_if_callable(key, self.obj)
-> 1341             return self._getitem_axis(key, axis=0)
   1342 
   1343     def _is_scalar_access(self, key):

/home/nobackup/repo/pandas/pandas/core/indexing.py in _getitem_axis(self, key, axis)
   1539                     raise ValueError('Cannot index with multidimensional key')
   1540 
-> 1541                 return self._getitem_iterable(key, axis=axis)
   1542 
   1543             # nested tuple slicing

/home/nobackup/repo/pandas/pandas/core/indexing.py in _getitem_iterable(self, key, axis)
   1049     def _getitem_iterable(self, key, axis=0):
   1050         if self._should_validate_iterable(axis):
-> 1051             self._has_valid_type(key, axis)
   1052 
   1053         labels = self.obj._get_axis(axis)

/home/nobackup/repo/pandas/pandas/core/indexing.py in _has_valid_type(self, key, axis)
   1429 
   1430                 raise KeyError("None of [%s] are in the [%s]" %
-> 1431                                (key, self.obj._get_axis_name(axis)))
   1432 
   1433             return True

KeyError: "None of [['not', 'found']] are in the [index]"

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit: f65a641 python: 3.5.2.final.0 python-bits: 64 OS: Linux OS-release: 4.7.0-1-amd64 machine: x86_64 processor: byteorder: little LC_ALL: None LANG: it_IT.utf8 LOCALE: it_IT.UTF-8

pandas: 0.19.0+473.gf65a641
pytest: 3.0.6
pip: 8.1.2
setuptools: 28.0.0
Cython: 0.23.4
numpy: 1.12.0
scipy: 0.18.1
xarray: None
IPython: 5.1.0.dev
sphinx: 1.4.8
patsy: 0.3.0-dev
dateutil: 2.5.3
pytz: 2015.7
blosc: None
bottleneck: 1.2.0
tables: 3.2.2
numexpr: 2.6.0
feather: None
matplotlib: 2.0.0rc2
openpyxl: 2.3.0
xlrd: 1.0.0
xlwt: 1.1.2
xlsxwriter: 0.9.3
lxml: 3.6.4
bs4: 4.5.1
html5lib: 0.999
httplib2: 0.9.1
apiclient: 1.5.2
sqlalchemy: 1.0.15
pymysql: None
psycopg2: None
jinja2: 2.8
s3fs: None
pandas_datareader: 0.2.1

[jreback]

these are not equivalent, asking for a scalar, e.g. s.loc[('not', 'found')] is the equivalent operation and this does raise KeyError.

[toobaz]

@jreback , while indeed the most strict equivalent to

# flat Index, looking for a list of values
s.reset_index().loc[['not', 'found']] 

is not really

# MultiIndex, looking for a list of values (in first level)
s.loc[['not', 'found']]

but rather

# MultiIndex, looking for a list of (complete) keys
s.loc[[('not', 'found1'), ('not', 'found2')]] 

, your example is not related to any of those (compare to s.reset_index().loc[[1, 2]], which does not return a scalar, and neither s.reset_index().loc[[1, 'a']] does!). And by the way, the last example above is even more incoherent:

In [6]: s.loc[[('not', 'found1'), ('not', 'found2')]]
Out[6]: 
not  found1   NaN
     found2   NaN
dtype: float64

... but at least there seems to be some reasoning behind (not clear to me).

[toobaz]

Or if you want a more concise example: s.iloc[:2].loc[[s.index[-1]]] will raise KeyError if s has a flat Index, and not if it has a MultiIndex

[jorisvandenbossche]

asking for a scalar, e.g. s.loc[('not', 'found')] is the equivalent operation and this does raise KeyError.

that is asking for a scalar label, but @toobaz issue was a list of labels, so the equivalent would indeed rather be (but of course it difficult to pinpoint an exact equivalent):

In [97]: s.loc[[('not', 'found'), ('also', 'not')]]
Out[97]: 
not   found   NaN
also  not     NaN
dtype: float64

which does a reindex. Thus, different from single Index indexing, where it only does a reindex when at least one label of the list is included in the Index.

[toobaz]

@jreback @jorisvandenbossche to be honest, I don't get why it was decided in the first place to make .loc behave like .reindex at all (e.g. change the structure of the indexed index). It seems to me completely incoherent with regular .loc behavior, and also undocumented. In my view, we should enforce the following general rules:

• s.loc[list_of_things] for a Series or df.loc[list_of_things, :] for a DataFrame should always (except when they raise some error, obviously) return an object of the same kind and with an index of the same depth (including 1) as s and df, respectively (where list_of_things is not necessarily a list, but rather anything that we interpret as a list of labels/levels, including Indexes)
• such index must contain a subset of the labels of the index of s, or df (possibly repeated, however, and/or in another order)

And I know it's probably late to say this, but concerning this bug, the cleanest solution would seem to me to abandon the rule (documented, this time) At least 1 of the labels for which you ask, must be in the index or a KeyError will be raised!, and allow an empty DataFrame/Series to be returned when looking for a list of missing labels (even in a flat Index). By the way, pd.Series(range(2)).loc[[]] is currently incoherent with the docs, since it doesn't raise.

Or alternatively, we could decide to be strict and raise a KeyError whenever any of the provided labels (or partial labels, when indexing only on some levels) is missing.

I understand that either of the two would break existing code. But the current compromise, in which we raise only if we do not find at least one label, seems to me a source of headaches both for the pandas user and for the pandas developer.

(sorry if this was already discussed somewhere I couldn't find)