CODEWITHSUNDEEP
pythonpandasdataframefilter
P3P0
P3P0

Reputation: 165

Count the records based on value of columns

I have the below df:

ID  Col1 Col2 Col3
1    A    NB    C
2    A    NB    C 
3    NS   B     NC
4    NS   NB    NC
5    NS   B     NC
6    NS    B     C

And I'm trying to get the count of each column based on their values.

How many "A" are in the Col1

How many "B" are in the Col2

How many "C" are in the Col3

In the original df I have a lot of column and conditions.

The expected output:

Col1                Col2            Col3         
TotalCount"A"   TotalCount"B"    TotalCount"C"

So, I'm trying to get the list of columns and iterate it but I am not getting the expected results. I'm working with pandas in jupyternotebook

Upvotes: 0

Views: 50

Answers (2)

Mayank Porwal
Mayank Porwal

Reputation: 34046

Extending on @Ch3ster's answer to match the expected output:

In [1382]: values = ['A', 'B', 'C']
In [1391]: res = df.filter(like='Col', axis=1).eq(values).sum().to_frame().T

In [1392]: res
Out[1392]: 
   Col1  Col2  Col3
0     2     3     3

Upvotes: 1

Ch3steR
Ch3steR

Reputation: 20669

You can use df.eq here and pass a list of values to compare against.

values = ['A', 'B', 'C']
out = df.loc[:, 'Col1':].eq(values).sum()

Col1    2
Col2    3
Col3    3
dtype: int64

Upvotes: 1

Related Questions