Pandas multiindex dataframe – Selecting max from one index within multiindex multi column

I have below Dataframe how to find dedicated result. I have found multi row maximum with column A.

                col_0  col_1
Caps Lower            
A    a     0  0.246490   2
           1 -1.265711   5
           2 -0.477415   6
           3 -0.355812   1
           4 -0.724521   2
     b     0 -0.409198   1
           1 -0.062552   1
           2 -0.731789   9 
           3  1.131616   5 
           4  0.085248   3 
B    a     0  0.193948   7
           1  2.010710   6
           2  0.289300   4
           3  0.305373   3
           4  1.376965   4
     b     0  0.210522   1
           1  1.431279   3
           2 -0.247171   1
           3  0.899074   8
           4  0.639926   1

result must be :

        col_0  col_1
Caps          
A    -0.731789   9
B     0.899074   8

How can I find maximum with col_1?

Answer

based on your desired example output, your question appears to be: how do I return the rows with the largest col_1 value per Caps group.

Your example input makes it look like 'Caps' and 'Lower' are your indices, but it’s a little easier if they are not indices and just regular columns.

I set up your data like this:

df = pd.DataFrame({'Caps': np.repeat(['A', 'B'], 10),
             'Lower': list(np.repeat(['a', 'b'], 5)) * 2,
              'col_0': [0.246490, -1.265711, -0.477415, -0.355812, -0.724521,
                        -0.409198, -0.062552, -0.731789, 1.131616, 0.085248,
                        0.193948, 2.010710, 0.289300, 0.305373, 1.376965,
                        0.210522, 1.431279, -0.247171, 0.899074, 0.639926],
              'col_1': [2, 5, 6, 1, 2, 1, 1, 9, 5, 3, 7, 6, 4, 3, 4, 1, 3, 1, 8, 1]
             })

[pandas.Series.idxmax][1] should help, which returns the indices of the rows with the largest col_1 per Caps group:

df.groupby('Caps')['col_1'].idxmax()
# Caps
# A     7
# B    18
# Name: col_1, dtype: int64

Plugging that in:

df.loc[df.groupby('Caps')['col_1'].idxmax()]
#    Caps   Lower   col_0       col_1
# 7  A      a      -0.731789    9
# 18 B      b       0.899074    8