2

I'm translating an excel formula in pandas. Where columns with specified conditions are counted and summed up row-wise. I have to count per row if a cell from the selected column satisfy the given conditions and then add the counts which satisfy the conditions.

I have dataframe:

df:

a    b     c
14   x1    2
17   x2    2
0    x,1   3
1    x1    1

Excel formula:

= COUNTIFS($U2,14,$X2,"x2",$W2,2)+COUNTIFS($U2,17,$X2,"x2",$W2,2)+COUNTIFS(U2,14,$X2,"x1",$W2,2)

Pandas formula:

df['counted'] = (df[(df['a']==14) & (df['b']=='x2') & (df['c']==2)].count(axis=1)) + (df[(df['a']==17) & (df['b']=='x2') & (df['c']==2)].count(axis=1)) + (df[(df['a']==14) & (df['b']=='x1') & (df['c']==2)].count(axis=1))

I get the result below from my pandas formula: df:

a    b     c   counted
14   x1    2      NaN
17   x2    2      NaN
0    x,1   3      NaN
1    x1    1      NaN

Expected result is as shown below. Any help to get the right formula will be very much appreciated.

Expected results df:

a    b     c   counted
14   x1    2      0
17   x2    2      1
0    x,1   3      0
1    x1    1      0
Improve this question
2
  • 3
    Could you maybe clarify in words what you're trying to do? Commented Feb 11, 2019 at 12:10
  • 1
    Is expected output correct? Commented Feb 11, 2019 at 12:26

1 Answer 1

Reset to default
2

I believe you need sum boolean mask converted to integers:

a = (df['a']==14) & (df['b']=='x2') & (df['c']==2)
b = (df['a']==17) & (df['b']=='x2') & (df['c']==2)
c = (df['a']==14) & (df['b']=='x1') & (df['c']==2)

Also is possible chain conditions for avoid repeating for better performance:

m1 = df['a']==14
m2 = df['b']=='x2'
m3 = df['c']==2
m4 = df['a']==17
m5 = df['b']=='x1'

a = m1 & m2 & m3
b = m4 & m2 & m3
c = m1 & m5 & m3

df['counted'] = a.astype(int)+ b.astype(int) + c.astype(int)
print (df)
    a    b  c  counted
0  14   x1  2        1
1  17   x2  2        1
2   0  x,1  3        0
3   1   x1  1        0

Or chain masks by bitwise OR and then convert to integer:

df['counted'] = (a | b | c).astype(int)
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

differs from desired output no?
@jezrael - Thank you, very helpful solution. Points to the right direction for some other issues i was facing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.