COUNTIF in pandas python over multiple columns with multiple conditions

Question

I have a dataset wherein I am trying to determine the number of risk factors per person. So I have the following data:

Person_ID  Age  Smoker  Diabetes
      001   30       Y         N
      002   45       N         N
      003   27       N         Y
      004   18       Y         Y
      005   55       Y         Y

Each attribute (Age, Smoker, Diabetes) has its own condition to determine whether it is a risk factor. So if Age >= 45, it's a risk factor. Smoker and Diabetes are risk factors if they are "Y". What I would like is to add a column that adds up the number of risk factors for each person based on those conditions. So the data would look like this:

Person_ID  Age  Smoker  Diabetes  Risk_Factors
      001   30       Y         N             1
      002   25       N         N             0
      003   27       N         Y             1
      004   18       Y         Y             2
      005   55       Y         Y             3

I have a sample dataset that I was fooling around with in Excel, and the way I did it there was to use the COUNTIF formula like so:

=COUNTIF(B2,">45") + COUNTIF(C2,"=Y") + COUNTIF(D2,"=Y")

However, the actual dataset that I will be using is way too large for Excel, so I'm learning pandas for python. I wish I could provide examples of what I've already tried, but frankly I don't even know where to start. I looked at this question, but it doesn't really address what to do about applying it to an entire new column using different conditions from multiple columns. Any suggestions?

Answer 1

I would do this the following way.

1. For each column, create a new boolean series using the column's condition
2. Add those series row-wise

(Note that this is simpler if your Smoker and Diabetes column is already boolean (True/False) instead of in strings.)

It might look like this:

df = pd.DataFrame({'Age': [30,45,27,18,55],
                   'Smoker':['Y','N','N','Y','Y'],
                   'Diabetes': ['N','N','Y','Y','Y']})

   Age Diabetes Smoker
0   30        N      Y
1   45        N      N
2   27        Y      N
3   18        Y      Y
4   55        Y      Y

#Step 1
risk1 = df.Age > 45
risk2 = df.Smoker == "Y"
risk3 = df.Diabetes == "Y"
risk_df = pd.concat([risk1,risk2,risk3],axis=1)

     Age Smoker Diabetes
0  False   True    False
1  False  False    False
2  False  False     True
3  False   True     True
4   True   True     True

df['Risk_Factors'] = risk_df.sum(axis=1)

   Age Diabetes Smoker  Risk_Factors
0   30        N      Y             1
1   45        N      N             0
2   27        Y      N             1
3   18        Y      Y             2
4   55        Y      Y             3