CODEWITHSUNDEEP
pythonpandas
rescot
rescot

Reputation: 335

How to sum counted pandas dataframe column with multiple conditions row-wise

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

Upvotes: 2

Views: 1315

Answers (1)

jezrael
jezrael

Reputation: 862591

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)

Upvotes: 2

Related Questions