3

I have a case of a dataframe containing a categorical variable of high cardinality (many unique values). I would like to re-code that variable to a set of values (the top most frequent values) and replace all other values with a catch-all category ("others"). To give a simple example:

Here are the two values which should stay unchanged:

top_values = ['apple', 'orange']

I established them based on their frequency in the following dataframe column:

{'fruits': {0: 'apple',
1: 'apple',
2: 'orange',
3: 'orange',
4: 'banana',
5: 'grape'}}

That dataframe column should be re-coded as follows:

{'fruits': {0: 'apple',
1: 'apple',
2: 'orange',
3: 'orange',
4: 'other',
5: 'other'}}

How to do that? (The dataframe has millions of records)

jpp
  • 159,742
  • 34
  • 281
  • 339
Nick
  • 2,924
  • 4
  • 36
  • 43

2 Answers2

7

There are at least a couple of methods you can use:

where + Boolean indexing

df['fruits'].where(df['fruits'].isin(top_values), 'other', inplace=True)

loc + Boolean indexing

df.loc[~df['fruits'].isin(top_values), 'fruits'] = 'other'

After this process, you will probably want to turn your series into a categorical:

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

Doing this before the value replacement operation probably won't help as your input series has high cardinality.

jpp
  • 159,742
  • 34
  • 281
  • 339
  • It occurs to me that the WHERE code snippet lacks reversion - it would replace the values that match pattern, rather than those which don't. – Nick Nov 10 '18 at 15:09
  • @Nick, Yep it's deceptive (vs e.g. `np.where`). Use `pd.Series.mask` to change values matching a condition; use `pd.Series.where` to change values *not* matching a condition. The lack of intuition is probably why it has never caught on. – jpp Nov 10 '18 at 15:22
1
df.newCol = df.apply(lambda row: row.fruits if row.fruits in top_values else 'others' )
Venkatachalam
  • 16,288
  • 9
  • 49
  • 77