Sort top N and group 'others' in pandas df

user_15309695 0
Viewed 159

Suppose I have the df

import pandas as pd

dic = {'001': [14],
       '002': [3],
       '003': [2],
       '004': [6],
       '005': [7],
       '006': [1],
       '007': [2]}
df = pd.DataFrame.from_dict(dic,orient='index')
df.reset_index(inplace=True)
df = df.rename(columns = {'index':'id',0:'count'})
sorted = df.sort_values('count',ascending=False)
print(sorted)

which results in

    id  count
0  001     14
4  005      7
3  004      6
1  002      3
2  003      2
6  007      2
5  006      1

and I wanted to sort the top 3 by the count column, and group the rest as 'others'. I imagine I'd want to do something like not_top3 = sorted[3:], but can't figure out how to rename the id as 'others' from there. Once that's done, I assume use groupby and sum to do the rest.

Expected output would be:

    id  count
0  001     14
1  005      7
2  004      6
3  other    8

where 'other' is the sum of the remaining ids.

pandas python
2 Answers

You can use df.append to add a row at the bottom.

sorted_df = df.sort_values("count", ascending=False)
out = sorted_df.iloc[:3]
out.append(
    {"id": "others", "count": sorted_df["count"].iloc[3:].sum()},
    ignore_index=True,
)

       id  count
0     001     14
1     005      7
2     004      6
3  others      8
user_124164530

You could create a new id, where values less than the top three are mapped as others, then aggregate to get the new dataframe:

(df
.assign(id = np.where(df['count'].isin(df['count'].nlargest(3)), 
                      df['id'], 
                      'other'))
.groupby('id', 
         as_index = False, 
         sort = False)
.sum()
 )

      id  count
0    001     14
1    005      7
2    004      6
3  other      8
user_71757130
Related