How to get a dataframe with the sum of the max values in a groupby?

user_5838551 0
Viewed 416

I have something like this dataframe:

base = {"month": [201901, 201901, 201902, 201902, 201902], "cat": ['0101', '0102', '0201', '0201', '0203'],  "value": [1, 2, 3, 4, 5]}
df = pd.DataFrame.from_dict(base)
df
    month   cat     value
0   201901  0101    1
1   201901  0102    2
2   201902  0201    3
3   201902  0201    4
4   201902  0203    5

I need to group by month and get the sum of the maximum values for each category (cat). I don't need the cat info in this resultant dataframe.

It would be something like this:

    month   value
    201901  3 # (1+2, from cat 0101 + cat 0102)
    201902  9 # (4+5, from the maximum value of cat 0201 + cat 0203)

I was able to reach this with two steps, like:

temp = df.groupby(['month', 'cat']).max()
finaldf = temp.groupby('month').sum()

The question is: is there any other elegant way to reach this, using aggregation or idmax or something else?

Thanks in advance

pandas dataframe python
1 Answers

We can remove duplicates and sum:

(df.sort_values(['value']).drop_duplicates(['month','cat'], keep='last')
   .groupby('month')['value'].sum()
)

Output:

month
201901    3
201902    9
Name: value, dtype: int64
user_42384080
Related