Filtering of maximum value by ID and category sum I have a task that is quite simple: I need to get the sum of values in the categories of each ID, and keep the category with the highest sum:
id    category   value
1       A         10 
1       A         15
1       B         13
2       A         80

So, in this case the sum of value for each category-id pair would be:
id    category   value
1       A         25 
1       B         13
2       A         80 

And then the maximum for id == 1 is 25 and for the other is 80, so the final dataframe is:
id    category   value
1       A         25 
2       A         80 

I achieved this like this:
(df.groupby(['id', 'category'])['value']
 .sum().reset_index().sort_values(by=['id', 'value'])
 .drop_duplicates(['id'], keep='last'))

I feel this can be done in lesser steps, maybe with some transform, but I can't find a better way. Any ideas?
Thanks
You could also do the following. Compute the first grouping by id and category, sum up the values:
y = df.groupby([&quot;id&quot;,&quot;category&quot;])[&quot;value&quot;].sum()

Afterwards, grab the best category according to your definition:
y.groupby(&quot;category&quot;).sum().nlargest(1)

Combining these, so that we get the full job done:
y = df.groupby([&quot;id&quot;,&quot;category&quot;])[&quot;value&quot;].sum()
cat = y.groupby(&quot;category&quot;).sum().nlargest(1).index
y.loc[:,cat]