CODEWITHSUNDEEP
pythonpandaspandas-groupby
Inineor
Inineor

Reputation: 23

How to make an pivot table with values of chosen column instead of aggregated values in Pandas?

I want to get a table of min and max. But instead of values I want to see names of people that have this min or max value. For example if I have a dataframe like this:

0  Name        Group    Salary  Age   
1  'Josh'      'A'      135000  50
2  'Bill'      'B'      130000  53
3  'Nikolaj'   'C'      125000  33
4  'Alfred'    'A'      130000  20
5  'Linda'     'C'      121000  25
6  'Yu'        'B'      134000  44
7  'Jeff'      'A'      120000  29

How could I use groupby or pivot_table to get frame that looks like this:

          Max                 Min
          Salary    Age       Salary    Age   
Group
A         'Josh'    'Josh'    'Jeff'    'Alfred'
B         'Yu'      'Bill'    'Bill'    'Yu'
C         'Nikolaj' 'Nikolaj' 'Linda'   'Linda'

If I try to use np.argmax or np.argmin it gives me indexes after groping so for example argmin for age in group B will give me 1 instead of 6. So i can't get correct indexes for original table to access 'Name' column.

Upvotes: 1

Views: 140

Answers (1)

jezrael
jezrael

Reputation: 863166

Use GroupBy.agg with convert Name to index in first step, then change order of levels by DataFrame.swaplevel and sort MultiIndex in columns:

df1 = (df.set_index('Name')
         .groupby('Group')
         .agg([('Max','idxmax'),('Min','idxmin')])
         .swaplevel(1, 0, axis=1)
         .sort_index(axis=1, ascending=[True, False]))
print (df1)
             Max                 Min          
          Salary        Age   Salary       Age
Group                                         
'A'       'Josh'     'Josh'   'Jeff'  'Alfred'
'B'         'Yu'     'Bill'   'Bill'      'Yu'
'C'    'Nikolaj'  'Nikolaj'  'Linda'   'Linda'

Upvotes: 1

Related Questions