I have a pandas data-frame:
id city
000.tushar@gmail.com Bangalore
00078r@gmail.com Mumbai
0007ayan@gmail.com Jamshedpur
0007ayan@gmail.com Jamshedpur
000.tushar@gmail.com Bangalore
00078r@gmail.com Mumbai
00078r@gmail.com Vijayawada
00078r@gmail.com Vijayawada
00078r@gmail.com 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
000.tushar@gmail.com Bangalore
00078r@gmail.com Vijayawada
0007ayan@gmail.com Jamshedpur
Using groupby id and city gives:
id city count
0 000.tushar@gmail.com Bangalore 2
1 00078r@gmail.com Mumbai 2
2 00078r@gmail.com Vijayawada 3
3 0007ayan@gmail.com 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.