Count the number of specific values in multiple columns pandas

Question

I have a data frame:

A    B    C    D     E

12  4.5  6.1   BUY  NaN
12  BUY  BUY   5.6  NaN
BUY  4.5  6.1  BUY  NaN
12  4.5  6.1   0    NaN

I want to count the number of times 'BUY' appears in each row. Intended result:

A    B    C    D     E   score

12  4.5  6.1   BUY  NaN    1
12  BUY  BUY   5.6  NaN    2
15  4.5  6.1  BUY   NaN    1
12  4.5  6.1   0    NaN    0

I have tried the following but it simply gives 0 for all the rows:

df['score'] = df[df == 'BUY'].sum(axis=1)

Note that BUY can only appear in B, C, D, E columns.

I tried to find the solution online but shockingly found none.

Little help will be appreciated. THANKS!

anky · Accepted Answer · 2021-01-03 12:58:40Z

7

You can compare and then sum:

df['score'] = (df[['B','C','D','E']] == 'BUY').sum(axis=1)

This sums up all the booleans and you get the correct result.

When you do df[df == 'BUY'], you are just replacing anything which is not BUY with np.nan and then taking sum over axis=1 doesnot work since all you have left in your result is np.nan and the 'BUY' string. Hence you get all 0.

answered Jan 3, 2021 at 12:58

anky

75.3k11 gold badges46 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

U13-Forward · Accepted Answer · 2021-01-03 12:55:54Z

2

Or you could use apply with list.count:

df['score'] = df.apply(lambda x: x.tolist().count('BUY'), axis=1)
print(df)

Output:

     A    B    C    D   E  score
0   12  4.5  6.1  BUY NaN      1
1   12  BUY  BUY  5.6 NaN      2
2  BUY  4.5  6.1  BUY NaN      2
3   12  4.5  6.1    0 NaN      0

answered Jan 3, 2021 at 12:55

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Comments

Akshay Sehgal · Accepted Answer · 2021-01-03 12:59:29Z

1

Try using apply with lambda over axis=1. This picks up each row at a time as a series. You can use the condition [row == 'BUY'] to filter the row and then count the number of 'BUY' using len()

df['score'] = df.apply(lambda row: len(row[row == 'BUY']), axis=1)
print(df)

     A    B    C    D   E  score
0   12  4.5  6.1  BUY NaN      1
1   12  BUY  BUY  5.6 NaN      2
2  BUY  4.5  6.1  BUY NaN      2
3   12  4.5  6.1    0 NaN      0

edited Jan 3, 2021 at 12:59

answered Jan 3, 2021 at 12:53

Akshay Sehgal

19.4k3 gold badges26 silver badges57 bronze badges

Comments

Arkadiusz · Accepted Answer · 2021-01-03 17:30:31Z

1

import numpy as np
df['score'] = np.count_nonzero(df == 'BUY', axis=1)

Output:

      A   B   C   D   E score
0    12 4.5 6.1 BUY NaN     1
1    12 BUY BUY 5.6 NaN     2
2   BUY 4.5 6.1 BUY NaN     2
3    12 4.5 6.1   0 NaN     0

answered Jan 3, 2021 at 17:30

Arkadiusz

1,8751 gold badge10 silver badges15 bronze badges

Collectives™ on Stack Overflow

Count the number of specific values in multiple columns pandas

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related