BUG: loc-indexing without MultiIndex has inconsistent behaviour if subkeys are tuples

[wence-]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd
df = pd.DataFrame({"a": [1, 2, 3], "b": [1, 2, 3], "c": [2, 3, 4]})

df.loc[(1, 2), :] # rows 2 and 3
df.loc[(1, 2), ["a"]] # rows 2 and 3 column "a"
df.loc[(1, 2), "a"] # assertionerror
# similarly if one uses a tuple to pull out the columns

Issue Description

When a dataframe is loc-indexed with a tuple in a the first (row) slot and a scalar key in the column slot, we get an assertionerror, which is inconsistent with the behaviour if passing a non-scalar in the column slot.

Expected Behavior

Tuples are usually used in loc/iloc indexing to indicate grouping of indices (between rows/columns) and levels in a multiindex. iloc-indexing raises an IndexError if any sub-indexer is itself a tuple (which is reasonable).

How should loc-indexing behave here? I think it is not as easy as the iloc case because a tuple can legitimately be a key for a Index (not a multiindex). How about, if the index is not a multiindex, then a tuple is treated as a scalar key, and any failing lookup raises KeyError. Rather than a tuple being treated like any other sequence.

So, concretely, in the above example all three approaches show raise an indexing error, since the label (1, 2) is not in the row index.

In any case, the scalar-ness (or not) of the column key should not influence the eventual outcome in terms of whether an entry is found.

Installed Versions

INSTALLED VERSIONS

commit : 37ea63d
python : 3.11.3.final.0
python-bits : 64
OS : Linux
OS-release : 5.19.0-43-generic
Version : #44~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC Mon May 22 13:39:36 UTC 2
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_GB.UTF-8
LOCALE : en_GB.UTF-8

pandas : 2.0.1
numpy : 1.24.3
pytz : 2023.3
dateutil : 2.8.2
setuptools : 67.7.2
pip : 23.1.2
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : 8.13.2
pandas_datareader: None
bs4 : None
bottleneck : None
brotli : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : None
numba : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : None
snappy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
zstandard : None
tzdata : 2023.3
qtpy : None
pyqt5 : None

[mroeschke]

So, concretely, in the above example all three approaches should raise an indexing error, since the label (1, 2) is not in the row index.

I agree with the conclusion here. I think this would require a deprecation of the current behavior of loc indexing with tuples when the index is not a MultiIndex.

[wence-]

I can add a deprecation warning in validate_key, but removing this support is going to be really hairy.

For posterity, I'll record some of my investigation here. loc-indexing with a tuple key, as here, does this:

    def _getitem_tuple(self, tup: tuple):
        with suppress(IndexingError):
            tup = self._expand_ellipsis(tup)
            return self._getitem_lowerdim(tup)

        # no multi-index, so validate all of the indexers
        tup = self._validate_tuple_indexer(tup)

        # ugly hack for GH #836
        if self._multi_take_opportunity(tup):
            return self._multi_take(tup)

        return self._getitem_tuple_same_dim(tup)

So we first attempt to pull out a lower-dimensional object.

If the key is (1, 2), "a" the result would be lower-dimensional if (1, 2) were in the index. It is not, so an IndexingError is raised, but that is caught and we continue. You might think that we should obtain a KeyError, but this does not occur because the axis-by-axis approach of _getitem_lowerdim throws away information about how the indexing call is happening. It always goes in through the .loc front door with a peeled away indexer and so the loc-indexer on the reduced section does not have the information to interpret the key correctly: we end up calling Series.loc[(1, 2)] which raises IndexingError.

The indexer validates (it has the correct shape). It is not a "multi-take" opportunity because "a" is not list like. So we go to getitem_tuple_same_dim. This path works backwards axis by axis, but it assumes that at this point we can't be pulling out a lower-dimensional object so we have:

            retval = getattr(retval, self.name)._getitem_axis(key, axis=i)
            # We should never have retval.ndim < self.ndim, as that should
            #  be handled by the _getitem_lowerdim call above.
            assert retval.ndim == self.ndim

This assertion fires because the first thing we do is df.loc._getitem_axis("a", axis=1) which does indeed lower the dimension.

Conversely, if the key is (1, 2), ["a"] then this is a multi-take opportunity (since everything is list-like) so that's a different place to check.

[wence-]

we end up calling Series.loc[(1, 2)] which raises IndexingError.

Aside, this means that it is actually impossible, with the loc interface, to pull single entries out of a frame with an index consisting of tuples:

import pandas as pd
s = pd.Series([1], index=[(1, 2)])
s.loc[(1, 2)] # IndexingError: too many indexers
s.loc[((1, 2), )] # explicitly try and say "this is a label for the rows" => KeyError (because the tuple is treated as list-like)

Series.at does work, however.