How to count the number of rows that have the value 1 for all the columns in a dataframe?

Question

The following is an example dataframe for this issue:

name    gender    address    phone no.
---------------------------------------
1       1         1          1
1       0         0          0
1       1         1          1
1       1         0          1

The desired output here is 2 because the number of rows containing all 1s is 2.

Can anyone please help me with this issue? Thanks.

score 2 · Accepted Answer · answered Oct 25 '22 at 08:08

2

Use eq(1) to identify the values with 1, then aggregate per row with any to have True when all values are True and sum the True taking advantage of the True/1 equivalence:

df.eq(1).all(axis=1).sum()

output: 2

Intermediates:

df.eq(1)
   name  gender  address  phone no.
0  True    True     True       True
1  True   False    False      False
2  True    True     True       True
3  True    True    False       True

df.eq(1).all(axis=1)
0     True
1    False
2     True
3    False
dtype: bool

answered Oct 25 '22 at 08:08

mozway

194,879
13
39
75

is there a way to get all the rows that have the value '1' in a separate dataframe? @mozmay – Vineeth Sai Oct 25 '22 at 08:36
@VineethSai yes, but with a different method (maybe `merge` or `isin`), please open a new question if needed – mozway Oct 25 '22 at 08:37
yes, could you please provide the code snippet for the same? because I'm unable to open a new question because it says that I have to wait for 90 minutes now. thanks. @mozway – Vineeth Sai Oct 25 '22 at 08:40
The thing is that the code will depend on your exact data, I suggest you prepare the reproducible input and let me know when you can post. – mozway Oct 25 '22 at 08:43
First read [merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) carefully – mozway Oct 25 '22 at 08:44
why merge tho? i mean, i only need the values that have 1 in all the columns in the dataframe. that's all. @mozway – Vineeth Sai Oct 25 '22 at 08:49
@VineethSai but are the two dataframes the same shape and aligned? In this case a simple `df1[df2['col'].eq(1).to_numpy()]` will do – mozway Oct 25 '22 at 08:51
actually, there is no 2nd dataframe. there is only one as mentioned above. i have to just store the rows that have value 1 in a different dataframe. i hope you understood this now. @mozway – Vineeth Sai Oct 25 '22 at 08:56

score 1 · Answer 2 · answered Oct 25 '22 at 08:08

1

Let's do

l = sum(df.eq(1).all(axis=1))

print(l)

2

answered Oct 25 '22 at 08:08

Ynjxsjmh

28,441
6
34
52

is there a way to get all the rows that have the value '1' in a separate dataframe? @Ynjxsjmh – Vineeth Sai Oct 25 '22 at 08:42

score 1 · Answer 3 · answered Oct 25 '22 at 08:55

Assuming above dataframe is a binary table i.e. all values are either 1 or 0, then df.sum(axis=1) equal to 4 should give you all rows where all values are 1.

df[df.sum(axis=1) == len(df.columns)]

   name  gender  address  phone no.
0     1       1        1          1
2     1       1        1          1

How to count the number of rows that have the value 1 for all the columns in a dataframe?

3 Answers3