如何在MultiIndex DataFrame中查询并筛选最优车辆配置行？
2026-6-20
MultiIndex DataFrame最优配置筛选方案
问题背景
有一个行、列均为MultiIndex的DataFrame，生成代码如下：
import random import pandas as pd random.seed(1) data_frame_rows = pd.MultiIndex.from_arrays([[], [], []], names=("car", "engine", "wheels")) data_frame_columns = pd.MultiIndex.from_arrays([[], [], []], names=("group", "subgroup", "details")) data_frame = pd.DataFrame(index=data_frame_rows, columns=data_frame_columns) for car in ("mustang", "corvette", "civic"): for engine in ("normal", "supercharged"): for wheels in ("normal", "wide"): data_frame.loc[(car, engine, wheels), ("cost", "", "money ($)")] = int(random.random() * 100) data_frame.loc[(car, engine, wheels), ("cost", "", "maintenance (minutes)")] = int(random.random() * 60) for race in ("f1", "indy", "lemans"): percent_win = random.random() recommended = percent_win >= 0.8 data_frame.loc[(car, engine, wheels), ("race", race, "win %")] = percent_win data_frame.loc[(car, engine, wheels), ("race", race, "recommended")] = recommended
生成的DataFrame示例：
group cost race subgroup f1 indy lemans details money ($) maintenance (minutes) win % recommended win % recommended win % recommended car engine wheels mustang normal normal 13.0 50.0 0.763775 False 0.255069 False 0.495435 False wide 44.0 39.0 0.788723 False 0.093860 False 0.028347 False supercharged normal 83.0 25.0 0.762280 False 0.002106 False 0.445387 False wide 72.0 13.0 0.945271 True 0.901427 True 0.030590 False corvette normal normal 2.0 32.0 0.939149 True 0.381204 False 0.216599 False wide 42.0 1.0 0.221692 False 0.437888 False 0.495812 False supercharged normal 23.0 13.0 0.218781 False 0.459603 False 0.289782 False wide 2.0 50.0 0.556454 False 0.642294 False 0.185906 False civic normal normal 99.0 51.0 0.120890 False 0.332695 False 0.721484 False wide 71.0 56.0 0.422107 False 0.830036 True 0.670306 False supercharged normal 30.0 35.0 0.882479 True 0.846197 True 0.505284 False wide 58.0 2.0 0.242740 False 0.797404 False 0.414314 False
需求
为每个车型筛选出最优配置行：即该车型的推荐配置中，单场比赛胜率最高的配置；无推荐配置的车型直接排除。
尝试过的错误代码
data_frame[(data_frame.loc[:,idx["race",:,"recommended"]]==True)]
该代码未正确筛选行，仅将结果设为NaN或True，输出如下：
group cost race subgroup f1 indy lemans details money ($) maintenance (minutes) win % recommended win % recommended win % recommended car engine wheels mustang normal normal NaN NaN NaN NaN NaN NaN NaN NaN wide NaN NaN NaN NaN NaN NaN NaN NaN supercharged normal NaN NaN NaN NaN NaN NaN NaN NaN wide NaN NaN NaN True NaN True NaN NaN corvette normal normal NaN NaN NaN True NaN NaN NaN NaN wide NaN NaN NaN NaN NaN NaN NaN NaN supercharged normal NaN NaN NaN NaN NaN NaN NaN NaN wide NaN NaN NaN NaN NaN NaN NaN NaN civic normal normal NaN NaN NaN NaN NaN NaN NaN NaN wide NaN NaN NaN NaN NaN True NaN NaN supercharged normal NaN NaN NaN True NaN True NaN NaN wide NaN NaN NaN NaN NaN NaN NaN NaN
解决方案
步骤说明
- 筛选有推荐的行：先找出至少有一场比赛被标记为
recommended=True的配置行，用any(axis=1)将多列布尔结果合并为每行的单个判断值。 - 计算每行的最大胜率：提取所有赛事的胜率列，计算每行的最大胜率值，作为筛选最优配置的核心依据。
- 按车型分组取最优：以车型（
car）为分组维度，在每组中筛选出最大胜率最高的行；若同一车型存在多个胜率相同的最优配置，会保留所有符合条件的行（可按需调整）。
完整代码
# 1. 筛选至少有一个推荐为True的行 recommended_rows = data_frame[data_frame.loc[:, ("race", slice(None), "recommended")].any(axis=1)] # 2. 提取所有赛事胜率列，计算每行最大胜率 win_cols = data_frame.loc[:, ("race", slice(None), "win %")] recommended_rows["max_win"] = win_cols.max(axis=1) # 3. 按车型分组，筛选每组中max_win最大的行 best_configs = recommended_rows.groupby(level="car").apply( lambda x: x[x["max_win"] == x["max_win"].max()] ).droplevel(0) # 移除分组产生的额外索引 # 可选：删除临时计算的max_win列 best_configs = best_configs.drop(columns="max_win") print(best_configs)
输出结果
group cost race subgroup f1 indy lemans details money ($) maintenance (minutes) win % recommended win % recommended win % recommended car engine wheels mustang supercharged wide 72.0 13.0 0.945271 True 0.901427 True 0.030590 False corvette normal normal 2.0 32.0 0.939149 True 0.381204 False 0.216599 False civic supercharged normal 30.0 35.0 0.882479 True 0.846197 True 0.505284 False
内容的提问来源于stack exchange，提问作者niltz
相关产品推荐
相关产品推荐
