I have a pandas dataframe of shape ~ [200K, 40]. The dataframe has a categorical column (one of many) with over 1000 unique values. I can visualizee the value counts of each such unique column by using:
df['column_name'].value_counts()
How do i now club values with:
You can extract the values you want to mask from the index of value_counts and them map them to "miscellaneous" using replace:
import pandas as pd
import numpy as np
df = pd.DataFrame(np.random.randint(0, 10, (2000, 2)), columns=['A', 'B'])
frequencies = df['A'].value_counts()
condition = frequencies<200 # you can define it however you want
mask_obs = frequencies[condition].index
mask_dict = dict.fromkeys(mask_obs, 'miscellaneous')
df['A'] = df['A'].replace(mask_dict) # or you could make a copy not to modify original data
Now, using value_counts will group all the values below your threshold as miscellaneous:
df['A'].value_counts()
df['A'].value_counts()
Out[18]:
miscellaneous 947
3 226
1 221
0 204
7 201
2 201
I think need:
df = pd.DataFrame({ 'A': ['a','a','a','a','b','b','b','c','d']})
s = df['A'].value_counts()
print (s)
a 4
b 3
d 1
c 1
Name: A, dtype: int64
If need sum all values bellow threshold:
threshold = 2
m = s < threshold
#filter values under threshold
out = s[~m]
#sum values under and create new values to Series
out['misc'] = s[m].sum()
print (out)
a 4
b 3
misc 2
Name: A, dtype: int64
But if need rename index values bellow threshold:
out = s.rename(dict.fromkeys(s.index[s < threshold], 'misc'))
print (out)
a 4
b 3
misc 1
misc 1
Name: A, dtype: int64
If need replace original column use GroupBy.transform with numpy.where:
df['A'] = np.where(df.groupby('A')['A'].transform('size') < threshold, 'misc', df['A'])
print (df)
A
0 a
1 a
2 a
3 a
4 b
5 b
6 b
7 misc
8 misc
An alternate solution:
cond = df['col'].value_counts()
threshold = 100
df['col'] = np.where(df['col'].isin(cond.index[cond >= threshold ]), df['col'], 'miscellaneous')
