Multiple column groupby with pandas to find maximum value for each group

user_7505065 0
Viewed 350

I have a dataframe like below:

Feature value frequency label
age_45_and_above No 2700 negative
age_45_and_above No 1707 positive
age_45_and_above No 83 other
age_45_and_above Yes 222 negative
age_45_and_above Yes 15 positive
age_45_and_above Yes 8 other
age_45_and_above [Null] 323 negative
age_45_and_above [Null] 8 other
age_45_and_above [Null] 5 positive
talk No 20 negative
talk No 170 positive
talk No 500 other
talk Yes 210 negative
talk Yes 1500 positive
talk Yes 809 other
talk [Null] 234 negative
talk [Null] 43 other
talk [Null] 85 positive

and so on.

for each feature group, I want to find the maximum frequency with all its related row data, like if the feature is age_45_and_above then by looking for NO group we have 3 rows with different frequency and label, I want to report the maximum one with it's related data.

I've tried groupby in different ways:

result.groupby(['Feature', 'Value'])['Frequency', 'Predict'].max()

or this one, with this one, I'm getting multi-Index dataframe which is not the desired results:

result.groupby(['Feature', 'Value', 'Predict'])['Frequency'].max()

and so many failed attempts with idxmax, transfrom and ... .

the intended output I'm looking for looks like this:

Feature value frequency label
age_45_and_above No 2700 negative
age_45_and_above Yes 222 negative
age_45_and_above [Null] 323 negative
talk No 500 other
talk Yes 1500 positive
talk [Null] 234 negative

Also, I wonder how to sum the frequencies for each <<Feature-value>> group except the max row as I don't know how to locate the max row, like in here for the first feature and value, <<age_45_and_above-No>> max is 2700, so the sum would be 1707+83.

Thanks for your time.

pandas-groupby pandas dataframe max python
2 Answers

using idxmax after the groupby in aloc.

print(df.loc[df.groupby(['Feature','value'])['frequency'].idxmax()])
             Feature   value  frequency     label
0   age_45_and_above      No       2700  negative
3   age_45_and_above     Yes        222  negative
6   age_45_and_above  [Null]        323  negative
11              talk      No        500     other
13              talk     Yes       1500  positive
15              talk  [Null]        234  negative

and for the sum without the max, then do the total sum per group and remove the frequency of the row, then select the max row

gr = df.groupby(['Feature','value'])['frequency']

res = (
    df.assign(total=gr.transform(sum)-df['frequency'])
      .loc[gr.idxmax()]
)
print(res)
             Feature   value  frequency     label  total
0   age_45_and_above      No       2700  negative   1790
3   age_45_and_above     Yes        222  negative     23
6   age_45_and_above  [Null]        323  negative     13
11              talk      No        500     other    190
13              talk     Yes       1500  positive   1019
15              talk  [Null]        234  negative    128
user_92747320

I would do it by using merge on the grouped data.

Based on this data:

df = pd.DataFrame({'Feature':['age']*9+['talk']*9,
                   'value':(['No']*3+['Yes']*3+['[Null]']*3)*2,
                   'frequency':[2700,1707,83,222,15,8,323,8,5,20,170,500,210,1500,809,234,43,85],
                   'label':['N','P','O']*6})

Using:

df.groupby(['Feature','value'],as_index=False)['frequency'].max().merge(df,on=['Feature','Value','frequency'])

Outputs:

  Feature   value  frequency label
0     age      No       2700     N
1     age     Yes        222     N
2     age  [Null]        323     N
3    talk      No        500     O
4    talk     Yes       1500     P
5    talk  [Null]        234     N

Adding the extra column can be done via a simple assignment:

df_1['sum_no_max'] = df.groupby(['Feature','value'])['frequency'].sum().values - df_1['frequency'].values

Finally outputting:

  Feature   value  frequency label  sum_no_max
0     age      No       2700     N        1790
1     age     Yes        222     N          23
2     age  [Null]        323     N          13
3    talk      No        500     O         190
4    talk     Yes       1500     P        1019
5    talk  [Null]        234     N         128
user_118970070
Related