pandas count data in row with specific condition

Question

my Dataframe is like below-having c2 is an empty column and initially total is zero in all row

Data               c1   c2  c3   c4      Total  

ABCDEFG01AB        P         A    A       0            
ABCDEFG02AB        A         P    P       0
ABCDEFG03AB        p         A    P       0
ABCDEFG04AB        P         P    p       0  
ABCDEFG05AB        A         p    A       0 
ABCDEFG06AB        A         A    A       0

I want to count the number of P in every row and put it to Total Like

Data               c1   c2  c3   c4     Total  

ABCDEFG01AB        P         A    A       1            
ABCDEFG02AB        A         P    P       2
ABCDEFG03AB        p         A    P       2
ABCDEFG04AB        P         P    p       3  
ABCDEFG05AB        A         p    A       1 
ABCDEFG06AB        A         A    A       0

related: https://stackoverflow.com/questions/30059260/python-pandas-counting-the-number-of-missing-nan-in-each-row — anky, Aug 07 '21 at 18:33

Henry Ecker · Answer 1 · 2021-08-07T18:39:37.110

We can sum to count values where equal to P or p on axis=1:

df['Total'] = (df.eq('P') | df.eq('p')).sum(axis=1)

Or with isin:

df['Total'] = df.isin(['P', 'p']).sum(axis=1)

If needed we can select specific columns first:

filtered = df[['c1', 'c3', 'c4']]
df['Total'] = (filtered.eq('P') | filtered.eq('p')).sum(axis=1)

Or:

filtered = df[['c1', 'c3', 'c4']]
df['Total'] = filtered.isin(['P', 'p']).sum(axis=1)

All options produce df:

          Data c1 c2 c3 c4  Total
0  ABCDEFG01AB  P     A  A      1
1  ABCDEFG02AB  A     P  P      2
2  ABCDEFG03AB  p     A  P      2
3  ABCDEFG04AB  P     P  p      3
4  ABCDEFG05AB  A     p  A      1
5  ABCDEFG06AB  A     A  A      0

DataFrame and imports:

import pandas as pd

df = pd.DataFrame({
    'Data': ['ABCDEFG01AB', 'ABCDEFG02AB', 'ABCDEFG03AB', 'ABCDEFG04AB',
             'ABCDEFG05AB', 'ABCDEFG06AB'],
    'c1': ['P', 'A', 'p', 'P', 'A', 'A'],
    'c2': ["", "", "", "", "", ""],
    'c3': ['A', 'P', 'A', 'P', 'p', 'A'], 'c4': ['A', 'P', 'P', 'p', 'A', 'A'],
    'Total': [0, 0, 0, 0, 0, 0]
})

pandas count data in row with specific condition

1 Answers1