Pandas groupby convert series to dataframe with maximum count of values

Question

Input Data looks like following:

id   season   city        date         team1                         team2                         toss_winner                   toss_decision   result   dl_applied   winner                        win_by_runs   win_by_wickets   player_of_match   venue                                       umpire1                 umpire2         umpire3
1    2017     Hyderabad   2017-04-05   Sunrisers Hyderabad           Royal Challengers Bangalore   Royal Challengers Bangalore   field           normal   0            Sunrisers Hyderabad           35            0                Yuvraj Singh      Rajiv Gandhi International Stadium, Uppal   AY Dandekar             NJ Llong        
2    2017     Pune        2017-04-06   Mumbai Indians                Rising Pune Supergiant        Rising Pune Supergiant        field           normal   0            Rising Pune Supergiant        0             7                SPD Smith         Maharashtra Cricket Association Stadium     A Nand Kishore          S Ravi          
3    2017     Rajkot      2017-04-07   Gujarat Lions                 Kolkata Knight Riders         Kolkata Knight Riders         field           normal   0            Kolkata Knight Riders         0             10               CA Lynn           Saurashtra Cricket Association Stadium      Nitin Menon             CK Nandan       
4    2017     Indore      2017-04-08   Rising Pune Supergiant        Kings XI Punjab               Kings XI Punjab               field           normal   0            Kings XI Punjab               0             6                GJ Maxwell        Holkar Cricket Stadium                      AK Chaudhary            C Shamshuddin   
5    2017     Bangalore   2017-04-08   Royal Challengers Bangalore   Delhi Daredevils              Royal Challengers Bangalore   bat             normal   0            Royal Challengers Bangalore   15            0                KM Jadhav         M Chinnaswamy Stadium                                                               
6    2017     Hyderabad   2017-04-09   Gujarat Lions                 Sunrisers Hyderabad           Sunrisers Hyderabad           field           normal   0            Sunrisers Hyderabad           0             9                Rashid Khan       Rajiv Gandhi International Stadium, Uppal   A Deshmukh              NJ Llong        
7    2017     Mumbai      2017-04-09   Kolkata Knight Riders         Mumbai Indians                Mumbai Indians                field           normal   0            Mumbai Indians                0             4                N Rana            Wankhede Stadium                            Nitin Menon             CK Nandan       
8    2017     Indore      2017-04-10   Royal Challengers Bangalore   Kings XI Punjab               Royal Challengers Bangalore   bat             normal   0            Kings XI Punjab               0             8                AR Patel          Holkar Cricket Stadium                      AK Chaudhary            C Shamshuddin   
9    2017     Pune        2017-04-11   Delhi Daredevils              Rising Pune Supergiant        Rising Pune Supergiant        field           normal   0            Delhi Daredevils              97            0                SV Samson         Maharashtra Cricket Association Stadium     AY Dandekar             S Ravi          
10   2017     Mumbai      2017-04-12   Sunrisers Hyderabad           Mumbai Indians                Mumbai Indians                field           normal   0            Mumbai Indians                0             4                JJ Bumrah         Wankhede Stadium                            Nitin Menon             CK Nandan       
11   2017     Kolkata     2017-04-13   Kings XI Punjab               Kolkata Knight Riders         Kolkata Knight Riders         field           normal   0            Kolkata Knight Riders         0             8                SP Narine         Eden Gardens                                A Deshmukh              NJ Llong        
12   2017     Bangalore   2017-04-14   Royal Challengers Bangalore   Mumbai Indians                Mumbai Indians                field           normal   0            Mumbai Indians                0             4                KA Pollard        M Chinnaswamy Stadium                       KN Ananthapadmanabhan   AK Chaudhary    
13   2017     Rajkot      2017-04-14   Rising Pune Supergiant        Gujarat Lions                 Gujarat Lions                 field           normal   0            Gujarat Lions                 0             7                AJ Tye            Saurashtra Cricket Association Stadium      A Nand Kishore          S Ravi          
14   2017     Kolkata     2017-04-15   Kolkata Knight Riders         Sunrisers Hyderabad           Sunrisers Hyderabad           field           normal   0            Kolkata Knight Riders         17            0                RV Uthappa        Eden Gardens                                AY Dandekar             NJ Llong        
15   2017     Delhi       2017-04-15   Delhi Daredevils              Kings XI Punjab               Delhi Daredevils              bat             normal   0            Delhi Daredevils              51            0                CJ Anderson       Feroz Shah Kotla                            YC Barde                Nitin Menon

Following is data frame default output:

>>> df
      id  season       city        date                        team1  ... player_of_match                                             venue          umpire1          umpire2  umpire3
0      1    2017  Hyderabad  2017-04-05          Sunrisers Hyderabad  ...    Yuvraj Singh         Rajiv Gandhi International Stadium, Uppal      AY Dandekar         NJ Llong      NaN
1      2    2017       Pune  2017-04-06               Mumbai Indians  ...       SPD Smith           Maharashtra Cricket Association Stadium   A Nand Kishore           S Ravi      NaN
2      3    2017     Rajkot  2017-04-07                Gujarat Lions  ...         CA Lynn            Saurashtra Cricket Association Stadium      Nitin Menon        CK Nandan      NaN
3      4    2017     Indore  2017-04-08       Rising Pune Supergiant  ...      GJ Maxwell                            Holkar Cricket Stadium     AK Chaudhary    C Shamshuddin      NaN
4      5    2017  Bangalore  2017-04-08  Royal Challengers Bangalore  ...       KM Jadhav                             M Chinnaswamy Stadium              NaN              NaN      NaN
..   ...     ...        ...         ...                          ...  ...             ...                                               ...              ...              ...      ...
631  632    2016     Raipur  2016-05-22             Delhi Daredevils  ...         V Kohli  Shaheed Veer Narayan Singh International Stadium   A Nand Kishore     BNJ Oxenford      NaN
632  633    2016  Bangalore  2016-05-24                Gujarat Lions  ...  AB de Villiers                             M Chinnaswamy Stadium     AK Chaudhary  HDPK Dharmasena      NaN
633  634    2016      Delhi  2016-05-25          Sunrisers Hyderabad  ...    MC Henriques                                  Feroz Shah Kotla        M Erasmus    C Shamshuddin      NaN
634  635    2016      Delhi  2016-05-27                Gujarat Lions  ...       DA Warner                                  Feroz Shah Kotla        M Erasmus        CK Nandan      NaN
635  636    2016  Bangalore  2016-05-29          Sunrisers Hyderabad  ...     BCJ Cutting                             M Chinnaswamy Stadium  HDPK Dharmasena     BNJ Oxenford      NaN

Input Data sample in image

By hitting python pandas command:

df.groupby(['season','toss_winner'])['toss_winner'].count()

Output:

season  toss_winner                
2008    Chennai Super Kings             5
        Deccan Chargers                 9
        Delhi Daredevils                6
        Kings XI Punjab                 8
        Kolkata Knight Riders           6
                                       ..
2017    Kolkata Knight Riders           9
        Mumbai Indians                 11
        Rising Pune Supergiant          6
        Royal Challengers Bangalore     9
        Sunrisers Hyderabad             5
Name: toss_winner, Length: 84, dtype: int64

but I want to convert this series to the data frame with the highest count only For example in 2017 highest is Mumbai Indians

I need a Output like following:

season  toss_winner                
2008    Deccan Chargers                 9
                                       ..
2017    Mumbai Indians                 11

Download CSV file of data set

Anyone can help in completing this command?

Please add some input data. – Dani Mesejo Nov 02 '19 at 21:23 — Dani Mesejo, Nov 02 '19 at 21:23
@DanielMesejo added some input data please check – cranktech Nov 02 '19 at 21:29 — cranktech, Nov 02 '19 at 21:29
See the answer by @ansev – Dani Mesejo Nov 02 '19 at 21:30 — Dani Mesejo, Nov 02 '19 at 21:30

ansev · Answer 1 · 2019-11-02T21:56:16.473

1

Use:

df.groupby('season').agg(mode=('toss_winner',lambda x: x.mode()))

or:

df_count=df.groupby(['season','toss_winner'])['toss_winner'].count().rename('count').reset_index()
df_count_mode=df_count[df_count['count'].eq(df_count['count'].max())]
print(df_count_mode)

   season     toss_winner  count
4    2017  Mumbai_Indians      3

edited Nov 02 '19 at 21:56

answered Nov 02 '19 at 21:29

ansev

30,322
5
17
31

didn't work, I got error as follow: ValueError: cannot insert toss_winner, already exists – cranktech Nov 02 '19 at 21:35
Edit check now:) – ansev Nov 02 '19 at 21:39
df.groupby('season').agg(mode=('toss_winner',lambda x: x.mode())) this code works well, but not able to see how many count is there for each session can you add that as well – cranktech Nov 02 '19 at 21:48
please check now. – ansev Nov 02 '19 at 21:57

Rithin Chalumuri · Accepted Answer · 2019-11-02T21:56:54.890

0

You can use .transform(max)

Try this:

df =  df.groupby(['season','toss_winner'])['toss_winner'].count().reset_index(name='count')

result_df = df[df['count'] == df.groupby(['season'])['count'].transform(max)]

Reference:

Get the Row(s) which have the max value in groups using groupby

edited Nov 02 '19 at 21:56

answered Nov 02 '19 at 21:31

Rithin Chalumuri

1,739
7
19

.max() will only maximum number like 12 is greatest in this series but I need output for each season – cranktech Nov 02 '19 at 21:34
@cranktech, it should return max value for each group i.e. season and team. – Rithin Chalumuri Nov 02 '19 at 21:37

Pandas groupby convert series to dataframe with maximum count of values

2 Answers2