BUG: Inconsistent handling of dropping levels in MultiIndex when using IndexSlice

[mousey92]

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 numpy as np
>>>import pandas as pd
>>>idx = pd.IndexSlice
>>>df = pd.DataFrame(np.random.rand(8, 4),
                     index=pd.MultiIndex.from_product([["A", "B"], range(2), ['a', 'b']]),
                     columns=pd.MultiIndex.from_product([['a', 'b'], range(2)]))
>>>df
              a                   b          
              0         1         0         1
A 0 a  0.791022  0.810915  0.903327  0.623326
    b  0.637121  0.521150  0.036788  0.302978
  1 a  0.491420  0.622780  0.889818  0.859360
    b  0.212784  0.056860  0.562042  0.349412
B 0 a  0.246708  0.522429  0.822344  0.134595
    b  0.219090  0.909012  0.166682  0.601046
  1 a  0.638349  0.458048  0.997902  0.152901
    b  0.294982  0.435510  0.350015  0.961403
>>>df.loc["A"]  # Drops first level, since selecting with scalar value
            a                   b          
            0         1         0         1
0 a  0.791022  0.810915  0.903327  0.623326
  b  0.637121  0.521150  0.036788  0.302978
1 a  0.491420  0.622780  0.889818  0.859360
  b  0.212784  0.056860  0.562042  0.349412
>>>df.loc[idx["A", :, :], :]  # Keeps first level, despite selecting with scalar value in first level
              a                   b          
              0         1         0         1
A 0 a  0.791022  0.810915  0.903327  0.623326
    b  0.637121  0.521150  0.036788  0.302978
  1 a  0.491420  0.622780  0.889818  0.859360
    b  0.212784  0.056860  0.562042  0.349412
>>>df.loc[idx["A"]]  # Not how I would normally slice, but it is within the realm of possibilities. Scalar value drops level
            a                   b          
            0         1         0         1
0 a  0.791022  0.810915  0.903327  0.623326
  b  0.637121  0.521150  0.036788  0.302978
1 a  0.491420  0.622780  0.889818  0.859360
  b  0.212784  0.056860  0.562042  0.349412
>>>df.loc[idx["A", :, :]]  # Slicing on 2 out of 3 levels will keep the level
              a                   b          
              0         1         0         1
A 0 a  0.791022  0.810915  0.903327  0.623326
    b  0.637121  0.521150  0.036788  0.302978
  1 a  0.491420  0.622780  0.889818  0.859360
    b  0.212784  0.056860  0.562042  0.349412
>>>df.loc[idx["A", :]]  # Slicing on 1 level will drop the level
            a                   b          
            0         1         0         1
0 a  0.791022  0.810915  0.903327  0.623326
  b  0.637121  0.521150  0.036788  0.302978
1 a  0.491420  0.622780  0.889818  0.859360
  b  0.212784  0.056860  0.562042  0.349412

Issue Description

Not 100% sure this issue has not been reported yet. #10521 seems to discuss the same issue, but in my opinion doesn't generalize the problem well enough.

When selecting from the DataFrame using .loc and a scalar value, the level that scalar value is indexing is dropped. But when selecting the same using IndexSlice, the behaviour is unpredictable (see examples). Depending on which and how much levels are 'sliced' using a scalar, the level of that scalar selection may be dropped, but it is unclear what the rules for this are. Especially since there are some mentions in the documentation and other issues of scalars always dropping the level.

Expected Behavior

Any index level that is selected using a scalar value from that level should either have the level dropped across index selection tools, or not have the level dropped across index selection tools. Either way, it should be consistent.

Installed Versions

INSTALLED VERSIONS

commit : e8093ba
python : 3.10.5.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.19044
machine : AMD64
processor : Intel64 Family 6 Model 158 Stepping 13, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : English_Netherlands.1252

pandas : 1.4.3
numpy : 1.23.0
pytz : 2022.1
dateutil : 2.8.2
setuptools : 63.1.0
pip : 22.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 : 3.1.2
IPython : 8.4.0
pandas_datareader: None
bs4 : 4.11.1
bottleneck : None
brotli : None
fastparquet : None
fsspec : None
gcsfs : None
markupsafe : 2.1.1
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
xlwt : None
zstandard : None

[simonjayhawkins]

Thanks @mousey92 for the report.

IndexSlice is a convenience, so I'm not sure that there is an issue there (except maybe the last case since i'm not sure it should be currently returning the correct result, will elaborate below)

Firstly, in the versions you mention pandas : 1.4.3. The forth case was fixed in commit: [35b338e] BUG: .loc failing to drop first level (#42435) i.e. pandas-1.4.0, can you confirm?

The second case, df.loc[idx["A", :, :], :] looks incorrect. This is equivalent to df.loc[("A", slice(None), slice(None)),] i.e. Explictly passing a 1-tuple of a 3-tuple.

The way the indexing works, is it can sometime look for a match in the columns Index to allow partial indexing.

So df.loc[idx["A", :]] doesn't really need the IndexSlice since it appears to equivalent to df.loc["A", :]. I think that maybe incorrect, since imo the IndexSlice should probably only slice 1 of the indexes at a time, but I think that's a separate issue. (but does help explain the inconsistency noted) So from the return (dropping the outer level of the Index) df.loc[idx["A", :]] is probably treating the second slice as the columns since df.loc[("A", slice(None)),] is not dropping the first level.

In conclusion, including a slice in the nested tuple, (with or without partial indexing) does not drop the correct levels.

So for instance df.loc[("A", slice(None)),], df.loc[("A", slice(None), slice(None)),], df.loc[("A", slice(None), slice(None)), "a"] and df.loc[("A", ("a", slice(None)))] for the columns case does not drop the correct levels

We may already have an issue about this.

[simonjayhawkins]

Note that it is not about the first level, just about the slice in the nested tuple.

print(
    df.loc[
        ("A", 0, slice(None)),
    ]
)
#               a                   b          
#               0         1         0         1
# A 0 a  0.526977  0.658588  0.771332  0.852590
#     b  0.084024  0.761542  0.850593  0.916064
print(
    df.loc[
        ("A", 0),
    ]
)
#           a                   b          
#           0         1         0         1
# a  0.526977  0.658588  0.771332  0.852590
# b  0.084024  0.761542  0.850593  0.916064

[simonjayhawkins]

We may already have an issue about this.

seems like a few. I think #10552 covers this issue succinctly, so closing as duplicate.

[mousey92]

I didn't find #10552 before, but yes that issue covers this and this can be closed as duplicate.