Rename the less frequent categories by "OTHER" python

Question

In my dataframe I have some categorical columns with over 100 different categories. I want to rank the categories by the most frequent. I keep the first 9 most frequent categories and the less frequent categories rename them automatically by: OTHER

Example:

Here my df :

print(df)

    Employee_number                 Jobrol
0                 1        Sales Executive
1                 2     Research Scientist
2                 3  Laboratory Technician
3                 4        Sales Executive
4                 5     Research Scientist
5                 6  Laboratory Technician
6                 7        Sales Executive
7                 8     Research Scientist
8                 9  Laboratory Technician
9                10        Sales Executive
10               11     Research Scientist
11               12  Laboratory Technician
12               13        Sales Executive
13               14     Research Scientist
14               15  Laboratory Technician
15               16        Sales Executive
16               17     Research Scientist
17               18     Research Scientist
18               19                Manager
19               20        Human Resources
20               21        Sales Executive


valCount = df['Jobrol'].value_counts()

valCount

Sales Executive          7
Research Scientist       7
Laboratory Technician    5
Manager                  1
Human Resources          1

I keep the first 3 categories then I rename the rest by "OTHER", how should I proceed?

Thanks.

Answer 1

Convert your series to categorical, extract categories whose counts are not in the top 3, add a new category e.g. 'Other', then replace the previously calculated categories:

df['Jobrol'] = df['Jobrol'].astype('category')

others = df['Jobrol'].value_counts().index[3:]
label = 'Other'

df['Jobrol'] = df['Jobrol'].cat.add_categories([label])
df['Jobrol'] = df['Jobrol'].replace(others, label)

Note: It's tempting to combine categories by renaming them via df['Jobrol'].cat.rename_categories(dict.fromkeys(others, label)), but this won't work as this will imply multiple identically labeled categories, which isn't possible.

---

The above solution can be adapted to filter by count. For example, to include only categories with a count of 1 you can define others as so:

counts = df['Jobrol'].value_counts()
others = counts[counts == 1].index

Answer 2

Use value_counts with numpy.where:

need = df['Jobrol'].value_counts().index[:3]
df['Jobrol'] = np.where(df['Jobrol'].isin(need), df['Jobrol'], 'OTHER')

valCount = df['Jobrol'].value_counts()
print (valCount)
Research Scientist       7
Sales Executive          7
Laboratory Technician    5
OTHER                    2
Name: Jobrol, dtype: int64

Another solution:

N = 3
s = df['Jobrol'].value_counts()
valCount = s.iloc[:N].append(pd.Series(s.iloc[N:].sum(), index=['OTHER']))
print (valCount)
Research Scientist       7
Sales Executive          7
Laboratory Technician    5
OTHER                    2
dtype: int64