Python Pandas: Counting the frequency of a specific value in each row of dataframe?

Question

I have a dataframe df:

domain               country     out1 out2 out3
oranjeslag.nl           NL          1    0   NaN    
pietervaartjes.nl       NL          1    1    0
andreaputting.com.au    AU          NaN  1    0 
michaelcardillo.com     US          0    0    NaN

I would like to define two columns sum_0 and sum_1 and count the number of 0s and 1s in columns (out1,out2,out3),per row. So expected results would be:

domain               country     out1 out2 out3   sum_0  sum_1
oranjeslag.nl           NL          1    0   NaN    1      1
pietervaartjes.nl       NL          1    1    0     1      2
andreaputting.com.au    AU          NaN  1    0     1      1
michaelcardillo.com     US          0    0    NaN   2      0

I have this code for counting the number of 1s, but I do not know how to count the number of 0s.

df['sum_1'] = df[['out_1','out_2','out_3']].sum(axis=1)

Can anybody help?

Luffa

Answer 1

You can call sum for each condition, the 1 condition is simple just a straight sum on axis=1, for the second you can compare the df against 0 value and then call sum as before:

In [102]:
df['sum_1'] = df[['out1','out2','out3']].sum(axis=1)
df['sum_0'] = (df[['out1','out2','out3']] == 0).sum(axis=1)
df

Out[102]:
                 domain country  out1  out2  out3  sum_0  sum_1
0         oranjeslag.nl      NL     1     0   NaN      1      1
1     pietervaartjes.nl      NL     1     1     0      1      2
2  andreaputting.com.au      AU   NaN     1     0      1      1
3   michaelcardillo.com      US     0     0   NaN      2      0

Cirque

Thank you @EdChum! I ended up using this logic to count the occurrence of strings within different columns of the same row by using: df[['out1','out2','out3']].isin(df['out1']).sum(axis=1) – Minify

Answer 2

I would do :

df["sum_0"] = df.apply(lambda row: sum(row[0:3]==0) ,axis=1)

Dira

My respect. I used the solution in a loop replacing "sum_0" and "==0" with parameters and made my job in seconds instead of several minutes before. Python's apply is blazing fast. – Brott

Answer 3

maybe pandas changed the bahaviour since 2015 but now the problem with sum is, that when you try to use this code for values > 1, it produces actual sum of these values, not their count (which is what I understood from question and also was looking for)

df['sum_0'] = df[df == 0].count(axis=1)

Whimsical