如何用Pandas筛选多层索引DataFrame并调整结果列表顺序？

Got it, let's tackle this problem step by step based on your requirements and the existing code snippet from @jezrael. Here's how we can adjust and expand the code to get the desired result:

Step 1: Filter the Target Rows #

First, we'll narrow down the DataFrame to only the rows that match your criteria:

• Site index level equals 'Mid'
• Type index level is either 'Stock' or 'Demand'

We'll also clean up unused index levels to make subsequent operations smoother:

# Create filter mask for index conditions
mask = (df.index.get_level_values('Site') == 'Mid') & \
       (df.index.get_level_values('Type').isin(['Stock', 'Demand']))

# Apply mask and remove unused index levels
filtered_index = df.loc[mask].index.remove_unused_levels()

Step 2: Split Commodities and Rearrange #

Next, we'll split the commodities into groups to ensure 'Elec' (from Type='Demand') lands at the end of the final list:

1. All commodities from Type='Stock'
2. Any non-'Elec' commodities from Type='Demand' (in case others exist)
3. The isolated 'Elec' from Type='Demand' (to place last)

We use unique() here to avoid duplicate entries—remove this if you need to preserve all original occurrences:

# Extract Stock-type commodities
stock_commodities = filtered_index[filtered_index.get_level_values('Type') == 'Stock'] \
                    .get_level_values('Commodity').unique().tolist()

# Extract non-Elec Demand-type commodities (if any)
demand_non_elec = filtered_index[(filtered_index.get_level_values('Type') == 'Demand') & 
                                 (filtered_index.get_level_values('Commodity') != 'Elec')] \
                    .get_level_values('Commodity').unique().tolist()

# Isolate Elec from Demand type
demand_elec = filtered_index[(filtered_index.get_level_values('Type') == 'Demand') & 
                             (filtered_index.get_level_values('Commodity') == 'Elec')] \
                    .get_level_values('Commodity').unique().tolist()

# Combine lists in the required order
final_commodity_list = stock_commodities + demand_non_elec + demand_elec

How This Fits Your Needs #

• We start by focusing only on the rows you care about, keeping the multi-index structure intact.
• Splitting into groups guarantees 'Elec' (from Demand) is always last, no matter its original position.
• unique() ensures clean, duplicate-free results—adjust if your use case needs to retain duplicates.

Simplified Version (If Demand Only Has Elec) #

If you know 'Elec' is the only Demand-type commodity for Site='Mid', you can streamline the code:

# Get Stock commodities using your original snippet (with unique added)
stock_commodities = df[(df.index.get_level_values('Site') == 'Mid') & 
                       (df.index.get_level_values('Type') == 'Stock')] \
                    .index.remove_unused_levels() \
                    .get_level_values('Commodity').unique().tolist()

# Check if Elec exists in Demand type for Site=Mid
has_elec = not filtered_index[(filtered_index.get_level_values('Type') == 'Demand') & 
                              (filtered_index.get_level_values('Commodity') == 'Elec')].empty

# Append Elec if it exists
final_commodity_list = stock_commodities + ['Elec'] if has_elec else stock_commodities

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