manipulate groupby index pandas

user_10327984 0
Viewed 38

I have a dataset containing tennis matches I want to study the player's behaviours in series so I used to group by so I defined the columns grouped by them and defined the aggregate function for the numerical columns then sorted the data frame to get the players with the most wins

gp_winner=df_atp.groupby(['Player1',"Series"])['encoded_label','P1_win_rate', 'P2_win_rate'].agg({
                                                            'P1_win_rate':['max'], 
                                                             'P2_win_rate':['min', 'max',"mean","std"], 
                                                            'encoded_label':'count' })
gp_winner.sort_values(by=[('encoded_label',"count")],inplace=True,ascending=False)
gp_winner.index

and I got the index of my grouped data frame is:

MultiIndex([(     'Federer R.',         'Grand Slam'),
            (       'Nadal R.',       'Masters 1000'),
            (    'Djokovic N.',       'Masters 1000'),
            (    'Djokovic N.',         'Grand Slam'),
            (       'Nadal R.',         'Grand Slam'),
            (      'Murray A.',         'Grand Slam'),
            ('Garcia-Lopez G.',             'ATP250'),
            (     'Federer R.',            'Masters'),
            (     'Istomin D.',             'ATP250'),
            (     'Federer R.',       'Masters 1000'),
            ...
            (   'Schuttler P.',      'International'),
            (   'Johansson T.',        'Masters Cup'),
            (     'Jeong S.Y.',             'ATP250'),
            (      'Jensen L.', 'International Gold'),
            (     'Jenkins S.', 'International Gold'),
            (     'Jenkins S.',             'ATP250'),
            (     'Jelenic P.',             'ATP250'),
            (  'Jeanpierre J.',         'Grand Slam'),
            (       'Jarry N.',             'ATP500'),
            (      ' Hajek J.',      'International')],
           names=['Player1', 'Series'], length=3436)

I want to show the statistics of each player for every tournament then so that it can be more interpretable and readable I am trying to get a result like this

MultiIndex([(     'Federer R.',         'International'),
            (       'Federer R.',       'ATP250'),
            (    'Federer R.',       'Grand Slam'),
            (    'Federer R.',         'Masters'),
            (       'Nadal R.',         'International'),
            (      'Nadal R.',         'ATP250'),
            ('Nadal R.',             'Grand Slam'),
            (     'Nadal R.',            'Masters'),
            (     'Nadal R.',             'ATP250'),etc.....
pandas-groupby multi-index pandas python
1 Answers

From the docs, MultiIndex has a sortlevel method.

You could assign the resulting MultiIndex to a variable and call that method on it, passing 'Player1' as the level, like:

gp_winner=df_atp.groupby(['Player1',"Series"])['encoded_label','P1_win_rate', 'P2_win_rate'].agg({
                                                        'P1_win_rate':['max'], 
                                                         'P2_win_rate':['min', 'max',"mean","std"], 
                                                        'encoded_label':'count' })
gp_winner.sort_values(by=[('encoded_label',"count")],inplace=True,ascending=False)
# assign to two variables to capture the returned tuple, 
# mi is the MultiIndex and indexer is the indices of output values in original index.
mi, indexer = gp_winner.sortlevel(level='Player1')
user_129157660
Related