String mode aggregation with group by function

Question

I have dataframe which looks like below

Country  City
UK       London
USA      Washington
UK       London
UK       Manchester
USA      Washington
USA      Chicago

I want to group country and aggregate on the most repeated city in a country

My desired output should be like

Country City
UK      London
USA     Washington

Because London and Washington appears 2 times whereas Manchester and Chicago appears only 1 time.

I tried

from scipy.stats import mode
df_summary = df.groupby('Country')['City'].\
                        apply(lambda x: mode(x)[0][0]).reset_index()

But it seems it won't work on strings

jpp · Accepted Answer · 2018-11-22T02:41:08.717

1

I can't replicate your error, but you can use pd.Series.mode, which accepts strings and returns a series, using iat to extract the first value:

res = df.groupby('Country')['City'].apply(lambda x: x.mode().iat[0]).reset_index()

print(res)

  Country        City
0      UK      London
1     USA  Washington

edited Nov 22 '18 at 02:41

answered Nov 22 '18 at 02:29

jpp

159,742
34
281
339

which means should I have to import pd.Series.mode ? – Ahamed Moosa Nov 22 '18 at 02:39
@MosesSoleman, Nope, you are using `pd.Series.mode` whenever you use `x.mode()`, i.e. you don't need to import anything extra. – jpp Nov 22 '18 at 02:39
Thanks , it works on this sample set , but let me try on a bigger set. – Ahamed Moosa Nov 22 '18 at 02:49
when I try with bigger set which has many unique value I get this error index 0 is out of bounds for axis 0 with size 0 – Ahamed Moosa Nov 22 '18 at 02:56
@MosesSoleman, Can you edit your question to include data that replicates the error? – jpp Nov 22 '18 at 02:59
jpp, Your solution works actually there was a problem with my dataset , after I fixed , it worked. thank You. – Ahamed Moosa Nov 22 '18 at 03:12
1

@MosesSoleman, you can also try `scipy` which works fine as well. – Karn Kumar Nov 22 '18 at 03:36

Karn Kumar · Answer 2 · 2018-11-22T03:52:41.917

try like below:

>>> df.City.mode()
0        London
1    Washington
dtype: object

OR

import pandas as pd
from scipy import stats

Can use scipy with stats + lambda :

df.groupby('Country').agg({'City': lambda x:stats.mode(x)[0]})
               City
Country
UK           London
USA      Washington

#  df.groupby('Country').agg({'City': lambda x:stats.mode(x)[0]}).reset_index()

However, it gives nice count as well if you don't want to return ony First value:

>>> df.groupby('Country').agg({'City': lambda x:stats.mode(x)})
                        City
Country
UK           ([London], [2])
USA      ([Washington], [2])

String mode aggregation with group by function

2 Answers2