Complicated aggregation of DataFrame in Python Pandas?

user_14909339 0
Viewed 54

I have DataFrame like below:

df = pd.DataFrame({"VALUE" : [100, 200, 100, 300, 500],
                   "PRODUCT_ID" : [599, 200, 599, 599, 200],
                   "STATUS" : ["active", "active", "not_active", "unknown", "active"], 
                   "CLIENT" : ["1", "1", "2", "2", "1"]})

And I need to calculate average, median and max VALUE per PRODUCT_ID with "active" STATUS for each CLIENT. I need df something like this:

AVG = 266,6 because of: (500+200+100) : 3
MED = 200 ?
MAX = 500 because of 500 is the biggest value on active aggrement for client 1

enter image description here

pandas aggregation python
2 Answers

Try:

(df.query('STATUS=="active"')
        .groupby(['CLIENT'])['VALUE']
        .agg(['mean','median','max'])
        .reindex(df.CLIENT.unique())
     )

Output:

              mean  median    max
CLIENT                           
1       266.666667   200.0  500.0
2              NaN     NaN    NaN
user_42384080

Can you try this:

  df[df['STATUS'] == 'active'].groupby(['PRODUCT_ID', 'CLIENT']).agg(['mean','median','max'])

The output:

                 VALUE
                 mean   median  max
         PRODUCT_ID CLIENT          
          200       1   350 350 500
          599       1   100 100 100
user_114097410
Related