[Python] Counting number of null values in group - why built in method doesn't work?

I'm doing the following leetcode problem and have been experimenting with different solutions: https://leetcode.com/problems/customer-who-visited-but-did-not-make-any-transactions/description/

In the problem, we have the following data:

data = [[1, 23], [2, 9], [4, 30], [5, 54], [6, 96], [7, 54], [8, 54]]
visits = pd.DataFrame(data, columns=['visit_id', 'customer_id']).astype({'visit_id':'Int64', 'customer_id':'Int64'})
data = [[2, 5, 310], [3, 5, 300], [9, 5, 200], [12, 1, 910], [13, 2, 970]]
transactions = pd.DataFrame(data, columns=['transaction_id', 'visit_id', 'amount']).astype({'transaction_id':'Int64', 'visit_id':'Int64', 'amount':'Int64'})


Printed out, the data is:

Visits
+----------+-------------+
| visit_id | customer_id |
+----------+-------------+
| 1 | 23 |
| 2 | 9 |
| 4 | 30 |
| 5 | 54 |
| 6 | 96 |
| 7 | 54 |
| 8 | 54 |
+----------+-------------+

Transactions
+----------------+----------+--------+
| transaction_id | visit_id | amount |
+----------------+----------+--------+
| 2 | 5 | 310 |
| 3 | 5 | 300 |
| 9 | 5 | 200 |
| 12 | 1 | 910 |
| 13 | 2 | 970 |
+----------------+----------+--------+


The problem asks for a dataframe where for each customer_id, the number of visits with no transactions is recorded (if it is non-zero). Thus, with the previous sample data, we expect the following output:

+-------------+----------------+
| customer_id | count_no_trans |
+-------------+----------------+
| 54 | 2 |
| 30 | 1 |
| 96 | 1 |
+-------------+----------------+


My idea is to:

1. Remove rows with duplicate visit_id from transactions
2. Left merge visits with transactions on visit_id
3. Group by customer_id and count the number of null rows in each group (i.e. the number of visits where no transactions were made).

For Steps 1 and 2, I do the following:

df = pd.merge(left=visits,
right = transactions.drop_duplicates(subset = ['visit_id']),
how = 'left',
on = 'visit_id')


For Step 3, this works with the custom aggregation function:

df1 = (df[df['transaction_id'].isna()]
.groupby('customer_id', as_index = False)['transaction_id']
.agg(lambda x: x.isna().sum())).rename(columns = {'transaction_id':'count_no_trans'})


but if I just try to use the built in .isna().sum() methods, it doesn't work:

df2 = (df[df['transaction_id'].isna()]
.groupby('customer_id', as_index = False)['transaction_id']
.isna().sum().rename(columns = {'transaction_id':'count_no_trans'}))


I get the error:

AttributeError: 'SeriesGroupBy' object has no attribute 'isna'

Why does this happen?

Continue reading...