Pandas set_levels如何避免标签排序？重复执行生效的原因探究

Let's break down what's happening with your code and how to get the behavior you want.

Why Running set_levels Twice Gives the Same Result #

First, let's clarify how set_levels works: it replaces the global collection of unique values for the specified level (not individual column labels). Here's the step-by-step breakdown:

1. Initial Column Structure: When you read the CSV with header=[0,1], your MultiIndex columns start with level 1 labels: ['', 'Metres', ''] (note: na_values only affects data rows, not header rows). Pandas automatically deduplicates and sorts the unique values for each level, so the underlying levels[1] becomes ['', 'Metres'] (sorted), with codes[1] pointing to these values: [0, 1, 0] (meaning Name and Age use the first value, Height uses the second).

2. First set_levels Execution: You pass df.columns.get_level_values(level=1).str.replace('Un.*','') (which returns ['', 'Metres', '']) to set_levels. Pandas takes this array, deduplicates it back to ['', 'Metres'], sorts it, and replaces the level 1 levels array. The codes (which map columns to level values) stay unchanged, so your column labels remain ('', 'Metres', '') for level 1.

3. Second set_levels Execution: When you run the same command again, df.columns.get_level_values(level=1) still returns ['', 'Metres', '']. Deduplicating and sorting this gives the same ['', 'Metres'] level array, so there's no change to your columns—hence the identical result.

How to Preserve Label Order on First Execution #

The core issue is that set_levels relies on Pandas' default behavior of sorting deduplicated level values. To avoid this and keep your original label order, use one of these approaches:

1. Use rename to Modify Individual Labels #

Instead of set_levels, use MultiIndex.rename to tweak level 1 labels directly. This modifies each column's label without altering the global level sorting:

df.columns = df.columns.rename(lambda x: x.replace('Un.*', ''), level=1)

This works because rename operates on each label individually, preserving the original structure of the MultiIndex.

2. Manually Build the MultiIndex with sort=False #

If you need full control over the level order, reconstruct the MultiIndex explicitly using from_tuples with sort=False (this prevents automatic sorting of unique level values):

# Create new tuples with modified level 1 labels
new_col_tuples = [
    (col_level0, col_level1.replace('Un.*', ''))
    for col_level0, col_level1 in df.columns
]

# Build MultiIndex without sorting levels
df.columns = pd.MultiIndex.from_tuples(new_col_tuples, sort=False)

This keeps the unique values in the order they first appear in your columns, rather than sorting them alphabetically.

内容的提问来源于stack exchange，提问作者Bharath M Shetty