Getting low, high, and mean from column

user_2896120 0
Viewed 284

I am trying to get the low, high and mean from a column. However, I'd like to only aggregate by the column value. For example, if we have 2 rows with the same column value, then we aggregate these two together. Also, they have to be of the same carrier. Something like this:

Before processing:

carrier   class   price
SP        A       22
VZ        C       33
XM        A       50 
XM        D       20     
SP        A       88
VZ        C       100

After processing:

carrier   class   price   low   high   mean
SP        A       22      22    88     55
VZ        C       33      33    100    66.5
XM        A       50      50    50     50
XM        D       20      20    20     20
SP        A       88      22    88     55
VZ        C       100     33    100    66.5

As you can see, if we have the same carrier and same class, then we aggregate and get the low, high, and mean. If we have the same carrier, but no same class then we don't aggregate, but we still get the low, high, mean which is the same number as the class' price.

I want the result to be exactly as the way it is after processing. The result should be a dataframe. How can I accomplish this?

pandas python
1 Answers

Use DataFrameGroupBy.agg with list of tuples foe new columns names with aggregate function and join to original DataFrame:

d = [('low','min'),('high','max'),('mean','mean')]
df1 = df.join(df.groupby(['carrier','class'])['price'].agg(d), on=['carrier','class'])
print (df1)
  carrier class  price  low  high  mean
0      SP     A     22   22    88  55.0
1      VZ     C     33   33   100  66.5
2      XM     A     50   50    50  50.0
3      XM     D     20   20    20  20.0
4      SP     A     88   22    88  55.0
5      VZ     C    100   33   100  66.5

Detail:

print (df.groupby(['carrier','class'])['price'].agg(d))
               low  high  mean
carrier class                 
SP      A       22    88  55.0
VZ      C       33   100  66.5
XM      A       50    50  50.0
        D       20    20  20.0

Or use transform, fun solution:

d = [('low','min'),('high','max'),('mean','mean')]
g = df.groupby(['carrier','class'])['price']
for i, j in d:
    df[i] = g.transform(j)
print (df)
  carrier class  price  low  high  mean
0      SP     A     22   22    88  55.0
1      VZ     C     33   33   100  66.5
2      XM     A     50   50    50  50.0
3      XM     D     20   20    20  20.0
4      SP     A     88   22    88  55.0
5      VZ     C    100   33   100  66.5
user_29010020
Related