CODEWITHSUNDEEP
pythonpandas
user128751
user128751

Reputation: 587

Changing the value of groups with few members in a pandas data frame

I have a data frame which represent different classes with their values. for example:

df=pd.DataFrame(
 {'label':['a','a','b','a','b','b','a','c','c','d','e','c'],
'date':[1,2,3,4,3,7,12,18,11,2,5,3],'value':np.random.randn(12)})

I want to choose the labels with values_counts less than a specific threshold and then put them into one class i.e. label them as for example 'zero'.

This is my attemp:

value_count=df.label.value_counts()
threshold = 3
for index in value_count[value_count.values<=threshold].index:
    df.label[df.label==index]='zero'

Is there a better way to do this?

Upvotes: 2

Views: 59

Answers (2)

Zero
Zero

Reputation: 76917

You could do

In [59]: df.loc[df['label'].isin(value_count[value_count.values<=threshold].index),
 'label'] = 'zero'

In [60]: df
Out[60]:
    date label     value
0      1     a -0.132887
1      2     a -1.306601
2      3  zero -1.431952
3      4     a  0.928743
4      3  zero  0.278955
5      7  zero  0.128430
6     12     a  0.200825
7     18  zero -0.560548
8     11  zero -2.925706
9      2  zero -0.061373
10     5  zero -0.632036
11     3  zero -1.061894

Timings

In [87]: df = pd.concat([df]*10**4, ignore_index=True)

In [88]: %timeit df['label'].isin(value_count[value_count.values<=threshold].index)
100 loops, best of 3: 7.1 ms per loop

In [89]: %timeit df.groupby('label')['label'].transform('count') <= threshold
100 loops, best of 3: 11.7 ms per loop

In [90]: df.shape
Out[90]: (120000, 3)

You may want to benchmark with larger dataset. And, this may not be aaccurate to compare, since you're precomuting value_count

Upvotes: 1

user2285236
user2285236

Reputation:

You can use groupby.transform to get the value counts aligned with the original index, then use it as a boolean index:

df.loc[df.groupby('label')['label'].transform('count') <= threshold, 'label'] = 'zero'

df
Out: 
    date label     value
0      1     a -0.587957
1      2     a  0.341551
2      3  zero  0.516933
3      4     a  0.234042
4      3  zero -0.206185
5      7  zero  0.840724
6     12     a -0.728868
7     18  zero  0.111260
8     11  zero -0.471337
9      2  zero  0.030803
10     5  zero  1.012638
11     3  zero -1.233750

Here are my timings:

df = pd.concat([df]*10**4)

%timeit df.groupby('label')['label'].transform('count') <= threshold
100 loops, best of 3: 7.86 ms per loop

%%timeit 
value_count=df.label.value_counts()
df['label'].isin(value_count[value_count.values<=threshold].index)
100 loops, best of 3: 9.24 ms per loop

Upvotes: 2

Related Questions