CODEWITHSUNDEEP
pythonpandas
Ullsokk
Ullsokk

Reputation: 717

Enumerate row by group in python

I have a dataset with rounds in a game, names, and scores:

import pandas as pd 

data = [[1,'tom', 10], [1,'nick', 15], [2,'juli', 14], [2,'peter', 20], [3,'juli', 3], [3,'peter', 13]] 

have = pd.DataFrame(data, columns = ['Round', 'Name', 'Score']) 

have.sort_values(by=['Round','Score'])

How do I get to a dataset with Round, WinnerName, LooserName, WinnerScore and LooserScore?

I started trying to enumerate, but keep messing up the syntax

Upvotes: 1

Views: 100

Answers (3)

ansev
ansev

Reputation: 30920

this seems to me definitely the best approach

since in each match only two teams compete,you can order using pandas.DataFrame.sort_values and use

Groupby.Series.first and Groupby.Series.last:

result=( have.sort_values('Score',ascending=False)
             .groupby('Round')
             .agg({'Name':{'Winner':'first','Looser':'last'},
                   'Score':{'WinnerScore':'first','LooserScore':'last'}}) )
print(result)

        Name              Score            
      Winner Looser WinnerScore LooserScore
Round                                      
1       nick    tom          15          10
2      peter   juli          20          14
3      peter   juli          13           3

Upvotes: 2

ALollz
ALollz

Reputation: 59549

You can use sort + cumcount to label the outcomes based on score, then it's a pivot.

data = [[1,'tom', 10], [1,'nick', 15], [2,'juli', 14], 
        [2,'peter', 20], [3,'juli', 3], [3,'peter', 13]] 
have = pd.DataFrame(data, columns = ['Round', 'Name', 'Score']) 

have = have.sort_values('Score')
have['outcome'] = have.groupby('Round').cumcount().map({0: 'Loser', 1: 'Winner'})

res = have.pivot(index='Round', columns='outcome', values=['Score', 'Name'])

         Name        Score       
outcome Loser Winner Loser Winner
Round                            
1         tom   nick    10     15
2        juli  peter    14     20
3        juli  peter     3     13

If you don't want the MultiIndex:

res.columns = [''.join(x[::-1]) for x in res.columns]
res = res.reset_index()

   Round LoserScore WinnerScore LoserName WinnerName
0      1         10          15       tom       nick
1      2         14          20      juli      peter
2      3          3          13      juli      peter

Upvotes: 0

Quang Hoang
Quang Hoang

Reputation: 150745

This is my approach:

min_max = have.groupby('Round').Score.agg(['idxmax','idxmin']).stack()
ret = pd.DataFrame(have.loc[min_max,["Name", "Score"]].values,
             index=min_max.index,
             columns=['Name','Score']).unstack()

# rename
ret.rename(mapper={"idxmax":'winner', 'idxmin':'looser'}, level=1, axis=1)

Output:

        Name         Score       
      winner looser winner looser
Round                            
1       nick    tom     15     10
2      peter   juli     20     14
3      peter   juli     13      3

Upvotes: 2

Related Questions