How to get top 3 sales in data frame after using group by and sorting in python?

user_14269135 0
Viewed 1044

recently I am doing with this data set

    import pandas as pd

data = {'Product':['Box','Bottles','Pen','Markers','Bottles','Pen','Markers','Bottles','Box','Markers','Markers','Pen'], 
        'State':['Alaska','California','Texas','North Carolina','California','Texas','Alaska','Texas','North Carolina','Alaska','California','Texas'], 
        'Sales':[14,24,31,12,13,7,9,31,18,16,18,14]}
 
df1=pd.DataFrame(data, columns=['Product','State','Sales']) 
df1

I want to find the 3 groups that have the highest sales

grouped_df1 = df1.groupby('State')
grouped_df1.apply(lambda x: x.sort_values(by = 'Sales', ascending=False))

So I have a dataframe like this

enter image description here

Now, I want to find the top 3 State that have the highest sales. I tried to use

grouped_df1.apply(lambda x: x.sort_values(by = 'Sales', ascending=False)).head(3)
# It gives me the first three rows
grouped_df1.apply(lambda x: x.sort_values(by = 'Sales', ascending=False)).max()
#It only gives me the maximum value

The expected result should be:

Texas: 31
California: 24
North Carolina: 18

Thus, how can I fix it? Because sometimes, a State can have 3 top sales, for example Alaska may have 3 top sales. When I simply sort it, the top 3 will be Alaska, and it cannot find 2 other groups.

Many thanks!

python
2 Answers

You could add a new column called Sales_Max_For_State and then use drop_duplicates and nlargest:

>>> df1['Sales_Max_For_State'] = df1.groupby(['State'])['Sales'].transform(max)
>>> df1
    Product           State  Sales  Sales_Max_For_State
0       Box          Alaska     14                   16
1   Bottles      California     24                   24
2       Pen           Texas     31                   31
3   Markers  North Carolina     12                   18
4   Bottles      California     13                   24
5       Pen           Texas      7                   31
6   Markers          Alaska      9                   16
7   Bottles           Texas     31                   31
8       Box  North Carolina     18                   18
9   Markers          Alaska     16                   16
10  Markers      California     18                   24
11      Pen           Texas     14                   31
>>> df2 = df1.drop_duplicates(['Sales_Max_For_State']).nlargest(3, 'Sales_Max_For_State')[['State', 'Sales_Max_For_State']]
>>> df2
            State  Sales_Max_For_State
2           Texas                   31
1      California                   24
3  North Carolina                   18
user_63282560

I think there are a few ways to do this:

1- df1.groupby('State').agg({'Sales': 'max'}).sort_values(by='Sales', ascending=False).iloc[:3]

2-df1.groupby('State').agg({'Sales': 'max'})['Sales'].nlargest(3)

                Sales
State
Texas              31
California         24
North Carolina     18
user_60319950
Related