Grouping results of a groupby with too numerous cases into a "trash bin" level

user_914693 0
Viewed 52

I need to "simplify" for reporting purposes rare events in a pandas DataFrame resulting from a group-by operation.

Let's take for example this DataFrame, where I use colA to count the occurrences of values in colB

df = pd.DataFrame(data={'colA':['a','b','c','d','e','a','a','b','b','a'],'colB':[1,2,3,4,5,1,1,2,2,1]})

df_grouped = df.groupby(['colA']).agg('count')

The result is:

      colB
colA      
a        4
b        3
c        1
d        1
e        1

from this grouped dataframe I want to obtain a new dataframe, where the least frequent values, namely those corresponding to colA={'c','d','e'} are grouped in a new value of the colA level called 'other', that contains the total number of all these, like the following:

       colB
colA
a      4
b      3
other  3

Is there a simple way to perform this "put-rare-stuff-in-the-trashbin" operation? Moreover, how can I do it in the presence of a MultiIndex?

pandas-groupby pandas python
4 Answers

Try this

df_final = df_grouped.rename({k: 'Other' for k, v in df_grouped.colB.eq(1).items() 
                                                           if v == True}).sum(level=0)

Out[671]:
       colB
colA
a         4
b         3
Other     3
user_101892140

IIUC, you, I'd do something like this:

s = df['colA'].value_counts()
s['other'] = s[s<2].sum()
s = s[s >= 2]
print(s)

Output:

a        4
b        3
other    3
Name: colA, dtype: int64
user_63615310

You can try the following where you create a boolean series if equal to the minimum. Then, use .where() to change the index values:

s = (df_grouped['colB'] != df_grouped['colB'].min())
df_grouped.index = df_grouped.index.where(s, 'other')
df_grouped = df_grouped.sum(level='colA')
df_grouped
Out[1]: 
       colB
colA       
a         4
b         3
other     3
user_63667700

Let us do it one-line

out = df.rename(index=dict.fromkeys(df[df.colB<2].index,'other')).sum(level=0)
Out[189]: 
       colB
colA       
a         4
b         3
other     3
user_79645270
Related