Say I have a MultiIndex by MultiIndex DataFrame similar to the one generated here:

import pandas as pd

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() * 100)
            data_frame.loc[(car, engine, wheels), ("cost", "", "maintenance (minutes)")] = int(random() * 60)

            percent_win = random()
            recommended = percent_win >= 0.8
            data_frame.loc[(car, engine, wheels), ("race", "f1", "win %")] = percent_win
            data_frame.loc[(car, engine, wheels), ("race", "f1", "recommended")] = recommended

            percent_win = random()
            recommended = percent_win >= 0.8
            data_frame.loc[(car, engine, wheels), ("race", "indy", "win %")] = percent_win
            data_frame.loc[(car, engine, wheels), ("race", "indy", "recommended")] = recommended

            percent_win = random()
            recommended = percent_win >= 0.8
            data_frame.loc[(car, engine, wheels), ("race", "lemans", "win %")] = percent_win
            data_frame.loc[(car, engine, wheels), ("race", "lemans", "recommended")] = recommended

Which then will look something like:

group                             cost                            race                                                        
subgroup                                                            f1                  indy                lemans            
details                      money ($) maintenance (minutes)     win % recommended     win % recommended     win % recommended
car      engine       wheels                                                                                                  
mustang  normal       normal       3.0                  33.0  0.664754       False  0.584689       False  0.629107       False
                      wide        39.0                  30.0  0.143568       False  0.339405       False  0.531536       False
         supercharged normal      65.0                  59.0  0.270161       False  0.682142       False  0.953301        True
                      wide        92.0                  12.0  0.403246       False  0.801241        True  0.991273        True
corvette normal       normal      72.0                  46.0  0.361436       False  0.981196        True  0.352499       False
                      wide        19.0                   2.0  0.303950       False  0.578942       False  0.802201        True
         supercharged normal      15.0                  29.0  0.148742       False  0.305060       False  0.144883       False
                      wide        59.0                  38.0  0.102707       False  0.364228       False  0.602490       False
civic    normal       normal      51.0                  14.0  0.726772       False  0.930073        True  0.218876       False
                      wide        66.0                   7.0  0.530214       False  0.051396       False  0.240695       False
         supercharged normal      55.0                  52.0  0.939180        True  0.614061       False  0.589519       False
                      wide        74.0                  28.0  0.595012       False  0.240609       False  0.539910       False

I now want to find all rows where the particular car configuration is recommended in at least 1 race. If a particular car is recommended in more than one race, then I want to choose the configuration that has the highest percent chance of winning in either race.

I’ve read this multiple times and for the life of me I can’t figure it out.

I tried something like:

data_frame[(data_frame.loc[:,idx["race",:,"recommended"]]==True)]

But that doesn’t seem to filter the rows, just sets things to either NaN or 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        True
                      wide         NaN                   NaN   NaN         NaN   NaN        True    NaN        True
corvette normal       normal       NaN                   NaN   NaN         NaN   NaN        True    NaN         NaN
                      wide         NaN                   NaN   NaN         NaN   NaN         NaN    NaN        True
         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        True    NaN         NaN
                      wide         NaN                   NaN   NaN         NaN   NaN         NaN    NaN         NaN
         supercharged normal       NaN                   NaN   NaN        True   NaN         NaN    NaN         NaN
                      wide         NaN                   NaN   NaN         NaN   NaN         NaN    NaN         NaN