Description
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
from pandas.testing import assert_frame_equal
df = pd.DataFrame({"a": [1, 1, 2, 3], "b": [1, 0, 2, 2], "c": [1, 2, 3, 4]}).set_index(["a", "b"])
df = df.reindex(df.index.sortlevel([0, 1], ascending=[True, True])[0])
slice_with_none = df.loc[(slice(None), slice(2, 0, -1)), :]
slice_with_full_range = df.loc[(slice(1, 3), slice(2, 0, -1)), :]
assert_frame_equal(slice_with_none, slice_with_full_range)
# AssertionError: MultiIndex level [0] are different
# MultiIndex level [0] values are different (100.0 %)
# [left]: Index([3, 2, 1, 1], dtype='int64', name='a')
# [right]: Index([1, 1, 2, 3], dtype='int64', name='a')
# At positional index 0, first diff: 3 != 1
index_with_none = df.loc[(slice(None), [2, 0, 1]), :]
index_with_range = df.loc[(slice(1, 3), [2, 0, 1]), :]
assert_frame_equal(index_with_none, index_with_range)
# AssertionError: MultiIndex level [0] are different
# MultiIndex level [0] values are different (100.0 %)
# [left]: Index([2, 3, 1, 1], dtype='int64', name='a')
# [right]: Index([1, 1, 2, 3], dtype='int64', name='a')
# At positional index 0, first diff: 2 != 1Issue Description
When indexing on multiple levels, the returned order of the frame only respects the ordering of the level indexer if the indexers to its left are null (slice(None)) slices. Otherwise, the indexed result comes out in "original" dataframe order.
In the examples above, since slice(None) and slice(1, 3) grab the same (i.e. all) entries (in the same order). I expect that the results should be equivalent when indexing on the second level.
However, as soon as the left-most indexer is not a no-op slice, the order induced by subsequent level indexers is ignored.
I think this is a consequence of the implementation converting all level indexers one by one into an ANDed boolean mask, which cannot encode ordering.
Expected Behavior
Ordering should either be the same between the "null slice" and "full slice" examples, or it should be explicitly documented that one cannot rely on ordering when indexing with multiindices (with implementation-defined semantics).
For indexing with slices, I think consistent ordering can be guaranteed by keeping track of the total slice "sign" of all the indexers and reversing the final result if it is negative.
For list-like entries I can't think through immediately how this might work.
Installed Versions
INSTALLED VERSIONS
------------------
commit : 6edff75126ba1500052b569e03a4567b98c8b8c7
python : 3.10.11.final.0
python-bits : 64
OS : Linux
OS-release : 5.19.0-46-generic
Version : #47~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC Wed Jun 21 15:35:31 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.1.0.dev0+1129.g6edff75126
numpy : 1.24.3
pytz : 2023.3
dateutil : 2.8.2
setuptools : 67.7.2
pip : 23.1.2
Cython : 0.29.33
pytest : 7.3.2
hypothesis : 6.79.1
sphinx : 6.2.1
blosc : None
feather : None
xlsxwriter : 3.1.2
lxml.etree : 4.9.2
html5lib : 1.1
pymysql : 1.0.3
psycopg2 : 2.9.3
jinja2 : 3.1.2
IPython : 8.14.0
pandas_datareader: None
bs4 : 4.12.2
bottleneck : 1.3.7
brotli :
fastparquet : 2023.4.0
fsspec : 2023.6.0
gcsfs : 2023.6.0
matplotlib : 3.7.1
numba : 0.57.0
numexpr : 2.8.4
odfpy : None
openpyxl : 3.1.2
pandas_gbq : None
pyarrow : 12.0.0
pyreadstat : 1.2.2
pyxlsb : 1.0.10
s3fs : 2023.6.0
scipy : 1.10.1
snappy :
sqlalchemy : 2.0.16
tables : 3.8.0
tabulate : 0.9.0
xarray : 2023.5.0
xlrd : 2.0.1
zstandard : 0.19.0
tzdata : 2023.3
qtpy : None
pyqt5 : None