Rank the highest number of downloaded app in every genre and filter only top 2 apps from each genre

user_9010859 0
Viewed 52

I am trying to get the highest top 2 downloaded app from the below data set, here is the dataset im using

import pandas as pd
df = pd.DataFrame(data={'genre_id': ['tools', 'tools', 'VIDEO_PLAYERS',
                                   'VIDEO_PLAYERS', 'PHOTOGRAPHY'],
                        'app_id':['MP3Cutter','PhotoCutter','VLC','MXPlayer','Picasa'],
                        'min_installs': [10, 100, 10, 20,1000]})
df

This is what i have tried

    df['default_rank'] = df.groupby(['genre_id']).agg(['rank'])
    df.sort_values(by='default_rank')

I am getting output such like this :

   genre_id           app_id    min_installs    default_rank
0   tools            MP3Cutter        10            1.0
2   VIDEO_PLAYERS      VLC            10            1.0
4   PHOTOGRAPHY       Picasa         1000           1.0
1   tools           PhotoCutter       100           2.0
3   VIDEO_PLAYERS     MXPlayer        20            2.0

But i want to get something like this :

   genre_id           app_id    min_installs    default_rank
4   PHOTOGRAPHY       Picasa         1000           1.0
1   tools           PhotoCutter       100           1.0
0   tools            MP3Cutter        10            2.0
3   VIDEO_PLAYERS     MXPlayer        20            1.0
2   VIDEO_PLAYERS      VLC            10            2.0

Im new to pandas, Is the Advance data manipulations are possible using python pandas? like we do in SQL ?

pandas-groupby pandas python-3.x
1 Answers

I believe you need sorting by DataFrameGroupBy.rank and also by maximal values per groups, then use:

s =  df.groupby('genre_id')['min_installs'].transform('max')

df['default_rank'] = (df.groupby('genre_id')['min_installs']
                        .rank(method='max', ascending=False))

df = (df.assign(m=s)
         .sort_values(by=['m', 'default_rank', 'genre_id'], 
                      ascending=[False, True, True])
         .drop('m', axis=1))

If need filter top2 values per groups:

df = df.groupby('genre_id').head(2)

print (df)
        genre_id       app_id  min_installs  default_rank
4    PHOTOGRAPHY       Picasa          1000           1.0
1          tools  PhotoCutter           100           1.0
0          tools    MP3Cutter            10           2.0
3  VIDEO_PLAYERS     MXPlayer            20           1.0
2  VIDEO_PLAYERS          VLC            10           2.0
user_29010020
Related