richie
richie

Reputation: 18648

Get maximum of value_counts after groupby

data.groupby(['batsman'])['runs'].value_counts() gives the following result; 

How do I find out which player has scored the most number of 0's, 1's, 2's, 3's ..etc?

AB de Villiers  0    29
                1    14
                4     6
                2     3
                6     1
Abdur Razzak    0    13
                1     5
                2     1
Adam Gilchrist  0    47
                1    38
                4    17
                2     9
                6     7
                3     1
Aftab Ahmed     1    51
...
Younis Khan   0    61
              1    39
              4    10
              2     9
              6     5

Upvotes: 0

Views: 9351

Answers (1)

Andy Hayden
Andy Hayden

Reputation: 375925

I think I would actually groupby runs and then take the most frequent:

In [11]: df = pd.DataFrame([['AB de Villiers', 0], ['AB de Villiers', 0], ['AB de Villiers', 1], ['Abdur Razzak', 0], ['Abdur Razzak', 1], ['Abdur Razzak', 1]], columns=['batsman', 'runs'])

In [12]: df
Out[12]: 
          batsman  runs
0  AB de Villiers     0
1  AB de Villiers     0
2  AB de Villiers     1
3    Abdur Razzak     0
4    Abdur Razzak     1
5    Abdur Razzak     1

[6 rows x 2 columns]

In [13]: df.groupby(['runs']).apply(lambda x: x['batsman'].value_counts().index[0])
Out[13]: 
runs
0       AB de Villiers
1         Abdur Razzak
dtype: object

There may be a slightly more efficient way to get the most occurring, but this is pretty efficient.

Upvotes: 3

Related Questions