How to select top N of two groups and aggregate the rest of the second group into "Others" with Pandas?

user_9476901 0
Viewed 978

I had a datased that had product, price, category, and county. I used this code to count the number of products per category per county:

df_count = df.groupby(['County','Category']).size().reset_index(name='counts')

My dataframe now looks like this:

County Category counts
0 Blekinge Accessories & watches 35
1 Blekinge Audio & video 101
2 Blekinge Bicycles 78
3 Blekinge Boat parts & accessories 65
4 Blekinge Boats 143
... ... ... ...
657 Östergötland Snowmobile parts & accessories 2
658 Östergötland Snowmobiles 5
659 Östergötland Sports & leisure equipment 335
660 Östergötland Tools 102
661 Östergötland Trucks & construction 66

662 rows × 3 columns

There are 21 counties and 32 categories. Counts are the number of products in a category. Not all categories are necessary in a county.

I'd like to have a new dataframe with the top N (e.g. 2) largest category of each county and aggregate the remainder into "Others". I want this for each county and it'd look like this:

County Category counts
Blekinge Boats 143
Blekinge Audio & video 101
Blekinge Others 178
... ... ...
Östergötland Sports & leisure equipment 335
Östergötland Tools 102
Östergötland Others 175

I've seen previous posts that did similar for arrays

How to group "remaining" results beyond Top N into "Others" with pandas

Sort top N and group 'others' in pandas df

and tried this

# group by & sort descending
df_sorted=df_count.groupby(['County','Category']).sum().sort_values('counts', ascending=False).reset_index()

# rename rows other than top-n to 'Others'
x_sorted.groupby('County').loc[x_sorted.index >=3, 'Category'] = 'Others'

and this

df_count.sort_values(by=['counts'], ascending=False).groupby('County').head(2).sort_values(by=['County']).reset_index(drop=True)

#not_top2 = df.groupby('Version').sum().sort('Value', ascending=False).index[2:]
not_top2 = x.groupby(['County','Category']).sum().sort_values('counts', ascending=False).index[2:]

dfnew = x.replace(not_top2, 'Other')

dfnew.groupby(['County','Category']).sum()

but didn't succeed to get the desired output.

Any help or guidance is very much appreciated!

pandas-groupby pandas python
2 Answers

You can use the below sequence of steps to get to your final output, which I believe is fairly straightforward.

With the hope of making it easy to follow, I will add comments in the code and the output per line.

# Grab top 2 largest caterogies of each country
top_two = df.groupby('County').apply(lambda x: x.nlargest(2, 'counts')).reset_index(drop=True)  

>>> top_two
         County                    Category  counts
0      Blekinge                       Boats     143
1      Blekinge               Audio & video     101
2  Östergötland  Sports & leisure equipment     335
3  Östergötland                       Tools     102

# Create a dataframe with the rest of the information
df_others = df.append(df.merge(top_two,'inner')).drop_duplicates(keep=False)

>>> df_others
         County                        Category  counts
0      Blekinge           Accessories & watches      35
2      Blekinge                        Bicycles      78
3      Blekinge        Boat parts & accessories      65
5  Östergötland  Snowmobile parts & accessories       2
6  Östergötland                     Snowmobiles       5
9  Östergötland           Trucks & construction      66

# Groupby country and Sum and assign 'others' under Category in the df_others dataframe
df_others = df_others.groupby('County')['counts'].sum().reset_index()
df_others['Category'] = 'Others'

>>> df_others
         County  counts Category
0      Blekinge     178   Others
1  Östergötland      73   Others

Finally, concat() the two dataframes to get your final output:

res = pd.concat([top_two,df_others]).sort_values('County').reset_index(drop=True)
>>> res
         County                    Category  counts
0      Blekinge                       Boats     143
1      Blekinge               Audio & video     101
2      Blekinge                      Others     178
3  Östergötland  Sports & leisure equipment     335
4  Östergötland                       Tools     102
5  Östergötland                      Others      73

Please get back if there's something unclear.

user_91673820

You can use iloc and pd.concat:

df = df.sort_values(['County', 'counts'], ascending=False)
result = (
    df.groupby('County').apply(
        lambda x: pd.concat(
            [x.iloc[:2],
             x.iloc[2:].groupby('County', as_index=False)
             .agg({'counts': sum})
             .assign(Category='Others')]))
    .reset_index(drop=True)
)

OUTPUT:

         County                    Category  counts
0      Blekinge                       Boats     143
1      Blekinge               Audio & video     101
2      Blekinge                      Others     178
3  Östergötland  Sports & leisure equipment     335
4  Östergötland                       Tools     102
5  Östergötland                      Others      73
user_154380330
Related