How to count instances in a pandas dataframe based on two parameters (using a lambda function with an 'and' operator)?

Question

I am trying to determine the number of instances in my dataframe that are both 'Pentioners' and have 'Days employed' = 365243. I can obtain the count of either 'Pensioners' or 'Days employed' seperately, but not the combined requirement.

This function works correctly:

number_pensioners = app_train.apply(lambda x: True if x['NAME_INCOME_TYPE'] == 'Pensioner' else False, axis = 1
                                   )
numRows = len(number_pensioners[number_pensioners == True].index)

However this function returns and error:

number_pensioners = app_train.apply(lambda x: True if x['NAME_INCOME_TYPE'] == 'Pensioner' & x['DAYS_EMPLOYED'] == 365243 else False, axis = 1
                                   )
numRows = len(number_pensioners[number_pensioners == True].index)

The error returned is:

TypeError: ("unsupported operand type(s) for &: 'str' and 'int'", 'occurred at index 0')

USE 'AND' INSTEAD OF '&' :)

giulio
– giulio

2019-11-04 09:24:48 +00:00
Commented Nov 4, 2019 at 9:24 — giulio
– giulio, Commented Nov 4, 2019 at 9:24

jezrael · Accepted Answer · 2019-11-04 09:25:26Z

1

Use and instead &, because processing scalars:

number_pensioners = app_train.apply(lambda x: True if (x['NAME_INCOME_TYPE'] == 'Pensioner') and (x['DAYS_EMPLOYED'] == 365243) else False, axis = 1
                                   )
numRows = len(number_pensioners[number_pensioners == True].index)

But better/ faster is vectorized solution with all columns with & and paratheses for mask:

m = (app_train['NAME_INCOME_TYPE'] == 'Pensioner') & (app_train['DAYS_EMPLOYED'] == 365243)

And then count Trues by sum:

print (m.sum())

Or get length of filtered rows:

print (len(number_pensioners[m]))

answered Nov 4, 2019 at 9:25

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bernard Esterhuyse Over a year ago

Great, that worked! I tried both and they work, and your statement on the speed was confirmed, the vectorized solution is much faster than the first method. Thanks jezrael.

Collectives™ on Stack Overflow

How to count instances in a pandas dataframe based on two parameters (using a lambda function with an 'and' operator)?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related