BUG: loc with MultiIndex as index is returning incorrect Index

[galipremsagar]

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
>>> s = pd.DataFrame(
...         {"a": [1, 1, 2], "b": [1, 2, 3], "c": ["a", "b", "c"]}
...     ).set_index(["a", "b"])["c"]
>>> s.loc[(1, slice(None))]
b
1    a
2    b
Name: c, dtype: object
>>> print(pd.__version__)
1.4.2

Issue Description

It appears to be that one level is being dropped when loc is called.

Expected Behavior

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

print(s.loc[(1, slice(None))])
a  b
1  1    a
   2    b
Name: c, dtype: object
print(pd.__version__)
1.3.5

Installed Versions

pd.show_versions()
Traceback (most recent call last):
File "", line 1, in
File "/nvme/0/pgali/envs/cudfdev/lib/python3.8/site-packages/pandas/util/_print_versions.py", line 109, in show_versions
deps = _get_dependency_info()
File "/nvme/0/pgali/envs/cudfdev/lib/python3.8/site-packages/pandas/util/_print_versions.py", line 88, in _get_dependency_info
mod = import_optional_dependency(modname, errors="ignore")
File "/nvme/0/pgali/envs/cudfdev/lib/python3.8/site-packages/pandas/compat/_optional.py", line 138, in import_optional_dependency
module = importlib.import_module(name)
File "/nvme/0/pgali/envs/cudfdev/lib/python3.8/importlib/init.py", line 127, in import_module
return _bootstrap._gcd_import(name[level:], package, level)
File "", line 1014, in _gcd_import
File "", line 991, in _find_and_load
File "", line 975, in _find_and_load_unlocked
File "", line 671, in _load_unlocked
File "", line 843, in exec_module
File "", line 219, in _call_with_frames_removed
File "/nvme/0/pgali/envs/cudfdev/lib/python3.8/site-packages/setuptools/init.py", line 8, in
import _distutils_hack.override # noqa: F401
File "/nvme/0/pgali/envs/cudfdev/lib/python3.8/site-packages/_distutils_hack/override.py", line 1, in
import('_distutils_hack').do_override()
File "/nvme/0/pgali/envs/cudfdev/lib/python3.8/site-packages/_distutils_hack/init.py", line 72, in do_override
ensure_local_distutils()
File "/nvme/0/pgali/envs/cudfdev/lib/python3.8/site-packages/_distutils_hack/init.py", line 59, in ensure_local_distutils
assert '_distutils' in core.file, core.file
AssertionError: /nvme/0/pgali/envs/cudfdev/lib/python3.8/distutils/core.py

[kwhkim]

I think this is not a bug.

compare this with singleIndex case, s.loc[1] which loses index and return a scalar

or compare with s.loc[(slice(1,1), slice(None))] which returns data frame with multi index

[rhshadrach]

Agreed @kwhkim, this is documented in e.g. https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.loc.html.

Is there documentation that lead you to believe the level should not be dropped @galipremsagar?

[simonjayhawkins]

Thanks @galipremsagar for the report

It appears to be that one level is being dropped when loc is called.

This was a bug fixed in commit: [35b338e] BUG: .loc failing to drop first level (#42435) but unfortunately there was no issue opened related to this to allow discussion or even a release note to communicate the change in behavior.

Note: there does still seem to be some inconsistent behavior and #42435 mentioned that there are other issues.

>>> s.loc[1]
b
1    a
2    b
Name: c, dtype: object
>>> 
>>> s.loc[
...     1,
... ]
b
1    a
2    b
Name: c, dtype: object
>>> 
>>> s.loc[1, :]
b
1    a
2    b
Name: c, dtype: object
>>> 
>>> s.loc[(1, slice(None))]
b
1    a
2    b
Name: c, dtype: object
>>> 
>>> s.loc[
...     (1, slice(None)),
... ]
a  b
1  1    a
   2    b
Name: c, dtype: object
>>> 
>>> s.loc[(1, slice(None)), :]
Traceback (most recent call last):
...
pandas.core.indexing.IndexingError: Too many indexers

and for DataFrame too

>>> s.to_frame().loc[1]
   c
b   
1  a
2  b
>>> 
>>> s.to_frame().loc[1, :]
   c
b   
1  a
2  b
>>> 
>>> s.to_frame().loc[(1, slice(None))]
   c
b   
1  a
2  b
>>> 
>>> s.to_frame().loc[
...     (1, slice(None)),
... ]
     c
a b   
1 1  a
  2  b
>>> 
>>> s.to_frame().loc[(1, slice(None)), :]
     c
a b   
1 1  a
  2  b

so it appears that you can still get the old behavior with s.loc[(1, slice(None)),] for now.

There was a test added, test_loc_drops_level in #42435 to test for this new behavior, so I think we can just close this issue?

[rhshadrach]

Thanks for tracking that down @simonjayhawkins; @galipremsagar - sorry, I didn't notice you pointed to two different versions of pandas in the OP.

As an alternative to maintain the level, one can use .xs, e.g.

s = pd.DataFrame(
    {"a": [1, 1, 2], "b": [1, 2, 3], "c": ["a", "b", "c"]}
).set_index(["a", "b"])["c"]
print(s.xs(1, level=0, drop_level=False))

a  b
1  1    a
   2    b
Name: c, dtype: object

Does that work for your use case?

[phofl]

Yep, I agree that the behavior is more consistent now. Although the last two cases from @simonjayhawkins are still unfortunate

[simonjayhawkins]

Although the last two cases from @simonjayhawkins are still unfortunate

let's leave issue open for now.