CODEWITHSUNDEEP
pythonpandasdataframe
Thomas Arildsen
Thomas Arildsen

Reputation: 1310

Pandas: index of max value for each group

My Pandas DataFrame, df, looks like this:

parameter1   parameter2   value

1            1            0.1
             2            0.2

2            1            0.6
             2            0.3

value is the result of a groupby(['parameter1','parameter2']).mean() on another DataFrame. Now, I can find the maximum value of value for each value of parameter1 using

df.max(level='parameter1')

However, I need to find the corresponding value of parameter2 for this maximum value. It seems df.idxmax() does not support level=, so how can I do this instead?

Upvotes: 5

Views: 3216

Answers (3)

JulienV
JulienV

Reputation: 368

On option is to override the idxmax() function :

def idxmax(x):
    return x.idxmax()[0]

and then use agg() method for aggregation :

df.groupby(['parameter1','parameter2']).agg(idxmax)

Upvotes: 0

jrjc
jrjc

Reputation: 21873

I eventually found a trick:

Groupby on level 0 (parameter1) and apply idxmax() and get the values:

v = df.groupby(level=0).idxmax().values
v
array([[(1, 2)],
       [(2, 1)]], dtype=object)

This is what df.idxmax(level=0) would give if implemented.

So v contains the index giving the max value for that level. So you can get the real values with:

df.loc[v.ravel()]

                       value
parameter1 parameter2       
1          2             0.2
2          1             0.6

and finally get the value of parameter2 corresponding to max values:

df.loc[v.ravel()].index.values[1]
(2, 1)

HTH

Upvotes: 5

JoeCondron
JoeCondron

Reputation: 8906

A nice way would be

df.unstack().idxmax(axis=1)

Unstacking the dataframe gives a dataframe with parameter_1 as the column index.

Upvotes: 4

Related Questions