Counting the number of rows based on the value of N number of columns

I have a dataset that looks like this:

Col1    Col2    Col3
A        100     100
A         0       0
A         0      100
B        100      0
C        100     100
C        100     100

I want to count the number of rows with 100 (or any other values greater than zero) based on A B and C

which will result to this:

    Col2_counts   Col3_counts
A       1           2
B       1           0
C       2           2

so I can calculate the total percentage of A B C in Col2 and Col3 etc.

I tried df.groupby(['Col1', 'Col 2', 'Col3']).transform ('count'), but it doesn't give me the desired result.

df.set_index('Col1').gt(0).groupby(level=0).sum()

output:

	    Col2	Col3
Col1		
A     	1	    2
B	    1	    0
C	    2	    2

The following block produces your expected output:

(
    df
    .set_index("Col1")
    .eq(100)
    .groupby("Col1")
    .sum()
    .add_suffix("_counts")
)

As chrslg pointed out, Panda Kim's answer evaluates rows > 0 instead of rows == 100. Also, you can add the "_counts" suffix at the end to get the column names from your example.

Here's a the step-by-step explanation of each operation:

1. Set "Col1" as the DataFrame's index.

2. Evaluate if cells are equal (eq) to 100.

   	  Col2	  Col3
   Col1		
   A	  True	  True
   A	  False	  False
   A	  False	  True
   B	  True	  False
   C	  True	  True
   C	  True	  True
   

3. Group by the "Col1" index and count True values. Using the sum operation works because it converts True to 1 and False to 0.

4. Add a "_counts" suffix to all columns.

   	  Col2_counts	Col3_counts
   Col1		
   A	      1	             2
   B	      1	             0
   C	      2	             2