Finding max occurrence of a column's value, af

问题:

I have a pandas data-frame:

        id                city
 [e-mail]   Bangalore
 [e-mail]       Mumbai
[e-mail]      Jamshedpur
[e-mail]      Jamshedpur
[e-mail]    Bangalore
  [e-mail]      Mumbai
  [e-mail]      Vijayawada
  [e-mail]      Vijayawada
  [e-mail]      Vijayawada

I want to find id-wise the maximum occurring city name. So that for a given id I can tell that - this is his favorite city:

         id             city
[e-mail]   Bangalore
[e-mail]       Vijayawada
[e-mail]     Jamshedpur

Using groupby id and city gives:

         id                   city       count
0  [e-mail]       Bangalore    2
1      [e-mail]        Mumbai      2
2      [e-mail]      Vijayawada    3
3    [e-mail]      Jamshedpur    2

How to proceed further? I believe some group-by apply will do that but unaware of what exactly will do the trick. So please suggest.

If some id has same count for two or three cities I am ok with returning any of those cities.

回答1:

You can try double groupby with size and idxmax. Output is list of tuples (because MultiIndex), so use apply:

df = df.groupby(['id','city']).size().groupby(level=0).idxmax()
                              .apply(lambda x: x[1]).reset_index(name='city')

Another solutions:

s = df.groupby(['id','city']).size()
df = s.loc[s.groupby(level=0).idxmax()].reset_index().drop(0,axis=1)

Or:

df = df.groupby(['id'])['city'].apply(lambda x: x.value_counts().index[0]).reset_index()

---

print (df)
                     id        city
0  [e-mail]   Bangalore
1      [e-mail]  Vijayawada
2    [e-mail]  Jamshedpur

回答2:

The recommended approach is groupby('id').apply(your_custom_function), where your_custom_function aggregates by 'city' and returns the max value (or as you mentioned, multiple max values). We don't even have to use .agg('city')

import pandas as pd

def get_top_city(g):
    return g['city'].value_counts().idxmax()    

df = pd.DataFrame.from_records(
         [('[e-mail]', 'Bangalore'), ('[e-mail]',     'Mumbai'),
         ('[e-mail]',   'Jamshedpur'),('[e-mail]',   'Jamshedpur'),
         ('[e-mail]', 'Bangalore'), ('[e-mail]',     'Mumbai'),
         ('[e-mail]',     'Vijayawada'),('[e-mail]',     'Vijayawada'),
         ('[e-mail]',     'Vijayawada')],
         columns=['id','city'],
         index=None
     )

topdf = df.groupby('id').apply(get_top_city)

id
[e-mail]     Bangalore
[e-mail]        Vijayawada
[e-mail]      Jamshedpur

# or topdf.items()/iteritems() if you want as list of (id,city) tuples

[('[e-mail]', 'Bangalore'), ('[e-mail]', 'Vijayawada'), ('[e-mail]', 'Jamshedpur')]