0

Basically this is a sql query task that I am trying to perform in Python.

Is there a way to get Top 10 sellers from each country without creating new DataFrames ?

Table for example: 

df = pd.DataFrame(
            {
                'Seller_ID': [1321, 1245, 1567, 1876, 1345, 1983, 1245, 1623, 1756, 1555, 1424, 1777,
                             2321, 2245, 2567, 2876, 2345, 2983, 2245, 2623, 2756, 2555, 2424, 2777],

                'Country' : ['India','India','India','India','India','India','India','India','India','India','India','India',
                            'UK','UK','UK','UK','UK','UK','UK','UK','UK','UK','UK','UK'],

                'Month' : ['Jan','Mar','Mar','Feb','May','May','Jun','Aug','Dec','Sep','Apr','Jul',
                          'Jan','Mar','Mar','Feb','May','May','Jun','Aug','Dec','Sep','Apr','Jul'],

                'Sales' : [456, 876, 345, 537, 128, 874, 458, 931, 742, 682, 386, 857,
                          456, 876, 345, 537, 128, 874, 458, 931, 742, 682, 386, 857]
            })
df

Table Output:

    Seller_ID   Country Month   Sales
0   1321    India   Jan 456
1   1245    India   Mar 876
2   1567    India   Mar 345
3   1876    India   Feb 537
4   1345    India   May 128
5   1983    India   May 874
6   1245    India   Jun 458
7   1623    India   Aug 931
8   1756    India   Dec 742
9   1555    India   Sep 682
10  1424    India   Apr 386
11  1777    India   Jul 857
12  2321    UK      Jan 456
13  2245    UK      Mar 876
14  2567    UK      Mar 345
15  2876    UK      Feb 537
16  2345    UK      May 128
17  2983    UK      May 874
18  2245    UK      Jun 458
19  2623    UK      Aug 931
20  2756    UK      Dec 742
21  2555    UK      Sep 682
22  2424    UK      Apr 386
23  2777    UK      Jul 857

Wrote below line of code but that violates condition of top 10 of each country and gives wrong results. 

df.loc[df['Country'].isin(['India','UK'])].sort_values(['Sales'], ascending=False)[0:20]

Another code that worked but it doesn't look that smart as it needs to create new dataframes

a = pd.DataFrame(df.loc[df['Country'] == 'India'].sort_values(['Sales'], ascending=False)[0:10])
b = pd.DataFrame(df.loc[df['Country'] == 'UK'].sort_values(['Sales'], ascending=False)[0:10]) 
top10_ofeach =  pd.concat([a,b], ignore_index=True)

Max I can improve here is run country inside the loop but looking for much smarter way to do it overall. I am not able to think of any better way to do it.

Scott Boston
  • 147,308
  • 15
  • 139
  • 187
Vivi
  • 155
  • 14

1 Answers1

2

Seems to be duplicate of Pandas get topmost n records within each group

df.sort_values(['Sales'], ascending=False).groupby('Country').head(10)
Shirish Goyal
  • 510
  • 2
  • 9
  • Yes its similar to the question in link thanks for the link. And I tried below code and it worked !! `df.sort_values(['Country','Sales'], ascending=(True,False)).groupby('Country').head(10)` – Vivi Jul 30 '19 at 10:25