Changing values in dataframe if the unique count of column is greater than a value

Eisen_12596824 0
Viewed 50
IP Routing Banking
1  1        6
2  1        6
3  1        7
3  3        8
4  5        9
5  9        7

For each column if the same value appears 2 or over times I want to alter it to "Other". How can I do this in pandas python?

Expected Output:

IP       Routing      Banking
1        Other        Other
2        Other        Other
Other    Other        Other
Other    3            8
4        5            9
5        9            Other
pandas python
1 Answers
df[df.transform(lambda col: col.duplicated(keep=False))] = 'Other'

Result:

      IP Routing Banking
0      1   Other   Other
1      2   Other   Other
2  Other   Other   Other
3  Other       3       8
4      4       5       9
5      5       9   Other

Same idea as above, without the lambda call :

cond = df.transform(pd.Series.duplicated, keep=False)
df.mask(cond, 'Other')

To use any threshold:

You can set any threshold that the value count must reach in order by be replaced - not only 2 (using a method from this answer):

n = 3  # set this threshold

def to_replace(ser, n):
    counts = ser.value_counts()
    return ser.isin(counts[counts >= n].index)

df.mask(df.transform(to_replace, n=n), 'Other')


   IP Routing  Banking
0   1   Other        6
1   2   Other        6
2   3   Other        7
3   3       3        8
4   4       5        9
5   5       9        7
user_146275050
Related