BUG: `.loc[:, [*labels]]` inconsistently reorders `MultiIndex`ed `Series` and `DataFrame`s

[QuaternionsRock]

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
>>> sorted_index = pd.MultiIndex.from_product([list("ab"), list("cdef")])
>>> unsorted_index = pd.MultiIndex.from_product([list("ab"), list("fdec")])
>>> sorted_labels = ["d", "e"]
>>> unsorted_labels = ["e", "d"]
>>> sorted_s = pd.Series(range(len(sorted_index)), sorted_index)
>>> sorted_s
a  c    0
   d    1
   e    2
   f    3
b  c    4
   d    5
   e    6
   f    7
dtype: int64
>>> sorted_s.loc[:, sorted_labels]
a  d    1
   e    2
b  d    5
   e    6
dtype: int64
>>> sorted_s.loc[:, unsorted_labels]
a  e    2
b  e    6
a  d    1
b  d    5
dtype: int64
>>> unsorted_s = pd.Series(range(len(unsorted_index)), unsorted_index)
>>> unsorted_s
a  f    0
   d    1
   e    2
   c    3
b  f    4
   d    5
   e    6
   c    7
dtype: int64
>>> unsorted_s.loc[:, sorted_labels]
a  d    1
b  d    5
a  e    2
b  e    6
dtype: int64
>>> unsorted_s.loc[:, unsorted_labels]
a  e    2
b  e    6
a  d    1
b  d    5
dtype: int64

Issue Description

When indexing a MultiIndexed DataFrame or Series via .loc[:, [*labels]], the second level "takes precedence" (for lack of a better term) over the first level if the second level of the MultiIndex is unsorted or if the labels are unsorted. This could be considered unexpected behavior because it means the order of the result depends on the order in which unselected labels appear in the index as well as _lexsort_depth. This issue may be related to #31330 and #40978.

The dependency on _lexsort_depth is particularly troubling to me for several reasons. First, it means that the order of the result depends not only on whether the list-indexed level is sorted, but also whether all previous levels are sorted. In the following example, looks_sorted_s looks identical to sorted_index, but the codes of the two labels in the first level are swapped, making it technically unsorted:

>>> looks_sorted_index = pd.MultiIndex(
...     [list("ba"), list("cdef")], [[1, 1, 1, 1, 0, 0, 0, 0], [0, 1, 2, 3, 0, 1, 2, 3]]
... )
>>> looks_sorted_s = pd.Series(range(len(looks_sorted_index)), looks_sorted_index)
>>> looks_sorted_s
a  c    0
   d    1
   e    2
   f    3
b  c    4
   d    5
   e    6
   f    7
dtype: int64
>>> looks_sorted_s.loc[:, sorted_labels]
a  d    1
b  d    5
a  e    2
b  e    6
dtype: int64

The converse is also true:

>>> looks_unsorted_index = pd.MultiIndex(
...     [list("ab"), list("fdec")], [[0, 0, 0, 0, 1, 1, 1, 1], [0, 1, 2, 3, 0, 1, 2, 3]]
... )
>>> looks_unsorted_s = pd.Series(range(len(looks_unsorted_index)), looks_unsorted_index)
>>> looks_unsorted_s
a  f    0
   d    1
   e    2
   c    3
b  f    4
   d    5
   e    6
   c    7
dtype: int64
>>> looks_unsorted_s.loc[:, sorted_labels]
a  d    1
   e    2
b  d    5
   e    6
dtype: int64

...which brings me to how I discovered the issue. The documentation suggests to me that the labels, rather than the codes, must be lexicographically sorted in all levels up to sortlevel. Functions like pd.MultiIndex.from_arrays impose this requirement:

>>> pd.MultiIndex.from_product([list("ab"), list("fdec")], sortorder=2)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "~/.venv/lib/python3.12/site-packages/pandas/core/indexes/multi.py", line 686, in from_product
    return cls(levels, codes, sortorder=sortorder, names=names)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "~/.venv/lib/python3.12/site-packages/pandas/core/indexes/multi.py", line 365, in __new__
    new_codes = result._verify_integrity()
                ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "~/.venv/lib/python3.12/site-packages/pandas/core/indexes/multi.py", line 459, in _verify_integrity
    raise ValueError(
ValueError: Value for sortorder must be inferior or equal to actual lexsort_depth: sortorder 2 with lexsort_depth 1

...but various other functions do not:

>>> idx = pd.IndexSlice
>>> unsorted_index = pd.MultiIndex.from_product([list("ab"), list("dc")])
>>> unsorted_df = pd.DataFrame({"f": range(4), "e": range(4, 8)}, unsorted_index)
>>> unsorted_df
     f  e
a d  0  4
  c  1  5
b d  2  6
  c  3  7
>>> unsorted_df.index._lexsort_depth
1
>>> unsorted_df.loc[idx[:, ["d", "c"]],]
     f  e
a d  0  4
b d  2  6
a c  1  5
b c  3  7
>>> sorted_df = unsorted_df.stack(future_stack=True).unstack(0)
>>> sorted_df
     a  b
c f  1  3
  e  5  7
d f  0  2
  e  4  6
>>> sorted_df.index._lexsort_depth
2
>>> sorted_df.loc[idx[:, ["f", "e"]],]
     a  b
c f  1  3
  e  5  7
d f  0  2
  e  4  6

Note: I'm not sure if this is an issue yet, but while stack (regardless of the value of future_stack) uses the same codes as self, unstack does not. Furthermore, unstack uses the order of first appearance rather than lexicographic order. I will dig into this later this week.

Expected Behavior

>>> import pandas as pd
>>> sorted_index = pd.MultiIndex.from_product([list("ab"), list("cdef")])
>>> unsorted_index = pd.MultiIndex.from_product([list("ab"), list("fdec")])
>>> sorted_labels = ["d", "e"]
>>> unsorted_labels = ["e", "d"]
>>> sorted_s = pd.Series(range(len(sorted_index)), sorted_index)
>>> sorted_s
a  c    0
   d    1
   e    2
   f    3
b  c    4
   d    5
   e    6
   f    7
dtype: int64
>>> sorted_s.loc[:, sorted_labels]
a  d    1
   e    2
b  d    5
   e    6
dtype: int64
>>> sorted_s.loc[:, unsorted_labels]
a  e    2
   d    1
b  e    6
   d    5
dtype: int64
>>> unsorted_s = pd.Series(range(len(unsorted_index)), unsorted_index)
>>> unsorted_s
a  f    0
   d    1
   e    2
   c    3
b  f    4
   d    5
   e    6
   c    7
dtype: int64
>>> unsorted_s.loc[:, sorted_labels]
a  d    1
   e    2
b  d    5
   e    6
dtype: int64
>>> unsorted_s.loc[:, unsorted_labels]
a  e    2
   d    1
b  e    6
   d    5
dtype: int64

Installed Versions

INSTALLED VERSIONS

commit : 9c8bc3e
python : 3.12.3
python-bits : 64
OS : Linux
OS-release : 5.15.167.4-microsoft-standard-WSL2
Version : #1 SMP Tue Nov 5 00:21:55 UTC 2024
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : C.UTF-8
LOCALE : C.UTF-8

pandas : 2.3.3
numpy : 2.3.5
pytz : 2025.2
dateutil : 2.9.0.post0
pip : 25.3
Cython : None
sphinx : None
IPython : None
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : None
blosc : None
bottleneck : None
dataframe-api-compat : None
fastparquet : None
fsspec : None
html5lib : None
hypothesis : None
gcsfs : None
jinja2 : None
lxml.etree : None
matplotlib : 3.10.7
numba : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
psycopg2 : None
pymysql : None
pyarrow : None
pyreadstat : None
pytest : None
python-calamine : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlsxwriter : None
zstandard : None
tzdata : 2025.2
qtpy : None
pyqt5 : None

[rhshadrach]

As you somewhat indicated, whether or not a level is sorted is based on the codes and not the values (which are determined by combining the codes with the level). Making this more clear in the docs if it is not would be welcome.

For indexing, it does seem beneficial to me to keep the result sorted when possible. It seems to me we can interpret sorted_s.loc[:, unsorted_labels] as a request for the 2nd level in the order of unsorted_labels only after maintain the order of prior levels. However I would weigh heavily here the performance implications as well as the code churn. I think further investigations would be welcome, but it could be a high bar for making a change here.

[QuaternionsRock]

As you somewhat indicated, whether or not a level is sorted is based on the codes and not the values (which are determined by combining the codes with the level). Making this more clear in the docs if it is not would be welcome.

Agreed. While the docs consistently use the phrase "Level of sortedness (must be lexicographically sorted by that level)", they freely exchange the idea of lexicographically sorted labels/"levels" (e.g., MultiIndex.from_arrays) with lexicographically sorted codes (e.g., MultiIndex). It also doesn't help that functions like unstack map labels to codes by order of appearance rather than lexicographical order, but again, it's not yet clear to me how unstack remaps labels (or if it even should). I still need to go through the implementation of unstack and a few others like MultiIndex.remove_unused_levels for comparison.

For indexing, it does seem beneficial to me to keep the result sorted when possible. It seems to me we can interpret sorted_s.loc[:, unsorted_labels] as a request for the 2nd level in the order of unsorted_labels only after maintain the order of prior levels. However I would weigh heavily here the performance implications as well as the code churn. I think further investigations would be welcome, but it could be a high bar for making a change here.

I am of the opinion that indexing with a label list should not behave any differently than indexing with an equivalent boolean array (when an equivalent boolean array exists), and this is currently not the case with unsorted_s.loc[:, sorted_labels]. Perhaps I am alone here, but I most often use

• unsorted_s.loc[:, sorted_labels] as a shorthand for unsorted_s.loc[unsorted_s.index.get_level_values(0), sorted_labels]
• unsorted_s.loc[:, labels1, labels2] as a shorthand for unsorted_s.loc[unsorted_s.index.isin(labels1, 1) & unsorted_s.index.isin(labels2, 2)] or unsorted_s.loc[unsorted_s.index.droplevel().isin(pd.MultiIndex.from_product([labels1, labels2]))]
• etc.

(As an aside, it seems rather odd to me that MultiIndex.isin cannot operate on a selection of levels.)

and I would expect a lot of people who also work with a lot of raw NumPy arrays would expect it to function as such. But besides that, the real pain point for me is that it does so inconsistently depending on invisible factors like which function mapped the labels to codes. If you don't want to rely on private members like _lexsort_depth, you have to jump through a lot of hoops to programmatically determine how the result will be ordered. If the performance implications/code churn mean the expected behavior should instead be such that label lists always take precedence over slices, then so be it:

>>> import pandas as pd
>>> sorted_index = pd.MultiIndex.from_product([list("ab"), list("cdef")])
>>> unsorted_index = pd.MultiIndex.from_product([list("ab"), list("fdec")])
>>> sorted_labels = ["d", "e"]
>>> unsorted_labels = ["e", "d"]
>>> sorted_s = pd.Series(range(len(sorted_index)), sorted_index)
>>> sorted_s
a  c    0
   d    1
   e    2
   f    3
b  c    4
   d    5
   e    6
   f    7
dtype: int64
>>> sorted_s.loc[:, sorted_labels]
a  d    1
   d    5
b  e    2
   e    6
>>> ...

[QuaternionsRock]

On a related note, it seems that MultiIndex.from_arrays and MultiIndex.from_tuples use verify_integrity=False while MultiIndex.from_product uses verify_integrity=True:

>>> index = pd.MultiIndex.from_arrays([["a", "a", "b", "b"], ["d", "c", "d", "c"]], sortorder=2)
>>> index.codes
FrozenList([[0, 0, 1, 1], [1, 0, 1, 0]])
>>> index._lexsort_depth
2
>>> index = pd.MultiIndex.from_tuples([("a", "d"), ("a", "c"), ("b", "d"), ("b", "c")], sortorder=2)
>>> index.codes
FrozenList([[0, 0, 1, 1], [1, 0, 1, 0]])
>>> index._lexsort_depth
2
>>> index = pd.MultiIndex.from_product([["a", "b"], ["d", "c"]], sortorder=2)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "~/.venv/lib/python3.12/site-packages/pandas/core/indexes/multi.py", line 686, in from_product
    return cls(levels, codes, sortorder=sortorder, names=names)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "~/.venv/lib/python3.12/site-packages/pandas/core/indexes/multi.py", line 365, in __new__
    new_codes = result._verify_integrity()
                ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "~/.venv/lib/python3.12/site-packages/pandas/core/indexes/multi.py", line 459, in _verify_integrity
    raise ValueError(
ValueError: Value for sortorder must be inferior or equal to actual lexsort_depth: sortorder 2 with lexsort_depth 1

This seems strange given that from_arrays and from_tuples have to compute lexsort_depth anyway if sortorder is unspecified. Perhaps all three should have a verify_integrity parameter?

Playing around with this a little bit, it seems verify_integrity=False allow you alter the behavior of unsorted_s.loc[:, sorted_labels] without altering the mapping of labels to codes. Among other things, this suggests to me that the performance implications of my suggested change would be roughly nil, as the inconsistent behavior clearly depends on nothing other than the value of _lexsort_depth:

>>> unsorted_index = pd.MultiIndex.from_arrays([list("aaaabbbb"), list("fdecfdec")], sortorder=2)
>>> unsorted_s = pd.Series(range(len(unsorted_index)), unsorted_index)
>>> unsorted_s
a  f    0
   d    1
   e    2
   c    3
b  f    4
   d    5
   e    6
   c    7
dtype: int64
>>> unsorted_s.loc[:, sorted_labels]
a  d    1
   e    2
b  d    5
   e    6
dtype: int64
>>> unsorted_s.loc[:, unsorted_labels]
a  e    2
b  e    6
a  d    1
b  d    5
dtype: int64
>>> unsorted_index = pd.MultiIndex.from_arrays([list("aaaabbbbaaaa"), list("fdecfdecfdec")], sortorder=2)
>>> unsorted_s = pd.Series(range(len(unsorted_index)), unsorted_index)
>>> unsorted_s.loc[:, sorted_labels]
a  d     1
   e     2
b  d     5
   e     6
a  d     9
   e    10
dtype: int64

I also expect the code churn would be insignificant for this reason, but I can't say for sure until I dig into it.