索引Pandas两级MultiIndex时如何避免结果自动排序？

Why does the index get sorted automatically? #

Great question! The automatic sorting behavior you're seeing stems from how Pandas handles sorted MultiIndexes. When you create a MultiIndex with pd.MultiIndex.from_product, it's lexicographically sorted by default (in your case, level 0 goes 0→4, level 1 goes A→B).

When using .loc with slice notation like idx[:, values], Pandas leverages the sorted structure of the index to optimize lookups. Instead of respecting the order of your input list ["B", "A"], it returns rows in the index's native sorted order (A then B). This is a performance optimization—sorted indexes allow for faster slicing, filtering, and joining operations.

More elegant solutions to preserve order #

Your current reindex approach works, but here are two cleaner ways to get the desired order without post-processing:

1. Select using explicit index tuples

Instead of slicing with idx[:, values], generate all the (level_0, level_1) tuples in your desired order and pass them directly to .loc. This forces Pandas to return rows exactly in the order you specify:

import pandas as pd
import numpy as np

df = pd.DataFrame(np.ones((10, 3)), columns=list("ABC"))
df.index = pd.MultiIndex.from_product([range(5), list("AB")])
values = ["B", "A"]

# Build the desired index order as tuples
desired_index = [(i, v) for i in df.index.levels[0] for v in values]
subset = df.loc[desired_index, values]

print(subset)

Output:

B    A
0 B  1.0  1.0
  A  1.0  1.0
1 B  1.0  1.0
  A  1.0  1.0
2 B  1.0  1.0
  A  1.0  1.0
3 B  1.0  1.0
  A  1.0  1.0
4 B  1.0  1.0
  A  1.0  1.0

This method is intuitive and avoids the need for reindexing. .loc respects the order of explicit tuple keys, even when the underlying index is sorted.

2. Positional selection with .take() (for large DataFrames)

If you're working with a very large dataset and want to minimize overhead, compute the positional indices of your desired rows and use .take():

# Get the positions of each (i, v) pair in the original index
row_positions = [df.index.get_loc((i, v)) for i in df.index.levels[0] for v in values]
subset = df.take(row_positions)[values]

This is slightly more efficient than the tuple approach because it uses direct positional lookups, though it's a bit less readable.

Can we disable sorting directly in .loc? #

Unfortunately, there's no built-in flag to turn off this sorting when using slice notation (idx[:, values]) with MultiIndex levels. The sorted return order is baked into the optimization of slicing on sorted indexes. Your options are either to explicitly specify the full index tuples (as above) or reindex after slicing (your original solution).

内容的提问来源于stack exchange，提问作者David Thielke