Pandas: Replace a string with 'other' if it is not present in a list of strings

Question

I have the following data frame, df, with column 'Class'

    Class
0   Individual
1   Group
2   A
3   B
4   C
5   D
6   Group

I would like to replace everything apart from Group and Individual with 'Other', so the final data frame is

    Class
0   Individual
1   Group
2   Other
3   Other
4   Other
5   Other
6   Group

The dataframe is huge, with over 600 K rows. What is the best way to optimally look for values other than 'Group' and 'Individual' and replace them with 'Other'?

I have seen examples for replace, such as:

df['Class'] = df['Class'].replace({'A':'Other', 'B':'Other'})

but since the sheer amount of unique values i have are too many i cannot individually do this. I want to rather just use the exclude subset of 'Group' and 'Individual'.

Answer 1

I think you need:

df['Class'] = np.where(df['Class'].isin(['Individual','Group']), df['Class'], 'Other')
print (df)
        Class
0  Individual
1       Group
2       Other
3       Other
4       Other
5       Other
6       Group

Another solution (slower):

m = (df['Class'] == 'Individual') | (df['Class'] == 'Group')
df['Class'] = np.where(m, df['Class'], 'Other')

Another solution:

df['Class'] = df['Class'].map({'Individual':'Individual', 'Group':'Group'}).fillna('Other')

---

Performance (in real data depends of number of replacements):

#[700000 rows x 1 columns]
df = pd.concat([df] * 100000, ignore_index=True)
#print (df)

In [208]: %timeit df['Class1'] = np.where(df['Class'].isin(['Individual','Group']), df['Class'], 'Other')
25.9 ms ± 485 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [209]: %timeit df['Class2'] = np.where((df['Class'] == 'Individual') | (df['Class'] == 'Group'), df['Class'], 'Other')
120 ms ± 6.63 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [210]: %timeit df['Class3'] = df['Class'].map({'Individual':'Individual', 'Group':'Group'}).fillna('Other')
95.7 ms ± 3.85 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [211]: %timeit df.loc[~df['Class'].isin(['Individual', 'Group']), 'Class'] = 'Other'
97.8 ms ± 6.78 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)