CODEWITHSUNDEEP
pythonpandassortinggroup-by
jonboy
jonboy

Reputation: 368

Pandas groupby and sort max values

I am trying to groupby two Columns in a pandas df and return the max value. I'm then hoping to sort these max values in relation to the same Columns.

This is my attempt:

import pandas as pd

d = ({
'year' : ['2016','2016','2016','2016','2016','2015','2015','2015','2015','2014','2014','2014','2014'],        
    'Val' : ['A','B','D','T','S','D','T','T','U','T','T','V','C'],                 
    'Num' : [1,2,4,5,3,6,4,3,2,5,6,1,2],                                     
    })

df = pd.DataFrame(data = d)

df = df.groupby(['year','Val']).Num.max()

Output:

year  Val
2014  C      2
      T      6
      V      1
2015  D      6
      T      4
      U      2
2016  A      1
      B      2
      D      4
      S      3
      T      5

I have tried this to produce the following

df = df.sort_values(['year','Num'], ascending=False)

Intended Output:

year  Val
2014  T      6
      C      2
      V      1
2015  D      6
      T      4
      U      2
2016  T      5
      D      4
      S      3
      B      2
      A      1

Upvotes: 1

Views: 2046

Answers (2)

ListenSoftware Louise Ai Agent
ListenSoftware Louise Ai Agent

Reputation: 4233

use transform to sort the values returned by max

 data = ({
 'year' :      ['2016','2016','2016','2016','2016','2015','2015','2015','2015','2014','2014','2014','2014'],        
     'Val' : ['A','B','D','T','S','D','T','T','U','T','T','V','C'],                 
     'Num' : [1,2,4,5,3,6,4,3,2,5,6,1,2],                                     
     })

 df = pd.DataFrame(data = data)

 grouped = df.groupby(['year','Val']).max()
 print(grouped)
 print(grouped.transform(pd.Series.sort_values,ascending=False))

output:

 Num
      year Val     
      2014 C      2
           T      6
           V      1
      2015 D      6
           T      4
           U      2
      2016 A      1
           B      2
           D      4
           S      3
           T      5

output 2:

           Num
 year Val     
 2015 D      6
 2014 T      6
 2016 T      5
      D      4
 2015 T      4
 2016 S      3
      B      2
 2015 U      2
 2014 C      2
 2016 A      1
 2014 V      1

Upvotes: 1

BENY
BENY

Reputation: 323226

So you need groupby with transform sort_values

df.groupby('year').transform(pd.Series.sort_values,ascending=False)
Out[42]: 
year  Val
2014  C      6
      T      2
      V      1
2015  D      6
      T      4
      U      2
2016  A      5
      B      4
      D      3
      S      2
      T      1
Name: Num, dtype: int64

Upvotes: 3

Related Questions