Python pandas - Dataframe getting second highest value with pd.groupby().agg()

user_9239521 0
Viewed 606

I have a DF [named cleanData] with some values and 2 columns which are custom_critirea and total_count.

Here is a section of my DF : 

     CUSTOM_CRITERIA  TOTAL_CODE_SERVED_COUNT
8            2768012                       27
9            3307322                        1
10           3270374                        2
11           3353569                        4
12           3423432                      660
13           1737751                        0
14           3564415                        5
15           3593988                        1
16           3593981                        2
17           3603423                    48367
18           3483162                        6
19           3603380                        3
20           3483062                        2
21           3617505                     2363
22           3617633                       11
23           3607897                        7
24           3619532                        1
28           3633518                        3
29           3653760                       22
30           3653625   ...

What I have right now is this : 

aggMap = {'TOTAL_CODE_SERVED_COUNT': ['sum', 'max']}
cleanData = cleanData.groupby('CUSTOM_CRITERIA').agg(aggMap)

This gives me the max and sum of the TOTAL CODE SERVED COUNT PER CUSTOM CRITERIA.

What I want to achieve now is getting the second-highest value from the aggregation

I need something like this : 

# myfunc should return for each group the second highest TOTAL_CODE_SERVED_COUNT
aggMap = {'TOTAL_CODE_SERVED_COUNT': ['sum', myfunc]}
cleanData = cleanData.groupby('CUSTOM_CRITERIA').agg(aggMap)

Can it be achieved using df.groupby().agg() ?

pandas aggregate numpy python
1 Answers

Sample data:

cleanData = pd.DataFrame({

         'TOTAL_CODE_SERVED_COUNT':[5,3,6,9,2,4,1],
         'CUSTOM_CRITERIA':list('aaabbac')
}).sort_values('CUSTOM_CRITERIA')
print (cleanData)
   TOTAL_CODE_SERVED_COUNT CUSTOM_CRITERIA
0                        5               a
1                        3               a
2                        6               a
5                        4               a
3                        9               b
4                        2               b
6                        1               c

You can sort values and get second-highest value, if not exist is returned same value:

def myfunc(x):
    y = np.sort(x)
    return y[-2] if len(y) > 1 else x

aggMap = {'TOTAL_CODE_SERVED_COUNT': ['sum', myfunc]}
cleanData1 = cleanData.groupby('CUSTOM_CRITERIA').agg(aggMap)
print (cleanData1)
                TOTAL_CODE_SERVED_COUNT       
                                    sum myfunc
CUSTOM_CRITERIA                               
a                                    18      5
b                                    11      2
c                                     1      1

If not exist second-highest is returned missing value NaN:

def myfunc(x):
    y = np.sort(x)
    return y[-2] if len(y) > 1 else np.nan

aggMap = {'TOTAL_CODE_SERVED_COUNT': ['sum', myfunc]}
cleanData2 = cleanData.groupby('CUSTOM_CRITERIA').agg(aggMap)
print (cleanData2)
                TOTAL_CODE_SERVED_COUNT       
                                    sum myfunc
CUSTOM_CRITERIA                               
a                                    18    5.0
b                                    11    2.0
c                                     1    NaN
user_29010020
Related