Pandas Get Max values within each group, by group sum

user_10613704 0
Viewed 134
import pandas as pd
import numpy as np
from datetime import datetime
df = pd.DataFrame(
    { 
        'Date' : np.random.choice(pd.date_range(datetime(2020,1,1),periods=5),20),
        'Product' : np.random.choice(['Milk','Brandy','Beer'],20)   ,    
     'Quantity' : np.random.randint(10,99,20)
        
    }  
)
df.groupby(['Date','Product']).sum()

This will give,

enter image description here

I would like to get the max values of the sum within the group what is the best way to do it?

Expected result for my random sample value would be.

enter image description here

How can I achieve this result.

pandas python
3 Answers

You can chain with another groupby, this time on your first level of your index (product) and get the max:

df.groupby(['Date','Product']).sum().groupby(level=1).max()

         Quantity
Product          
Beer          160
Brandy         97
Milk          245

To get the date as well, use sort_values with tail:

(
    df.groupby(['Date','Product']).sum()
    .sort_values('Quantity')
    .groupby(level=1)
    .tail(1)
)

        Date Product  Quantity
0 2020-01-04    Beer        81
1 2020-01-03    Milk       186
2 2020-01-03  Brandy       212
user_90812670 
df.groupby(['Date','Product']).sum().reset_index().groupby(
    ['Product']).max().reset_index()

Output:


    Product Date        Quantity
0   Beer    2020-01-04  151
1   Brandy  2020-01-05  72
2   Milk    2020-01-05  188
user_4239260

Check with drop_duplicates

df.groupby(['Date','Product'],as_index=False).sum().sort_values('Quantity').drop_duplicates('Product',keep='last')
         Date Product  Quantity
11 2020-01-05    Milk       119
10 2020-01-05  Brandy       165
5  2020-01-03    Beer       302
user_79645270
Related