[Python/Pandas] 리트코드 1581. Customer Who Visited but Did Not Make Any Transactions

문제

Table: Visits
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| visit_id    | int     |
| customer_id | int     |
+-------------+---------+
visit_id is the column with unique values for this table.
This table contains information about the customers who visited the mall.

 

Table: Transactions
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| transaction_id | int     |
| visit_id       | int     |
| amount         | int     |
+----------------+---------+
transaction_id is column with unique values for this table.
This table contains information about the transactions made during the visit_id.

Write a solution to find the IDs of the users who visited without making any transactions and the number of times they made these types of visits.
Return the result table sorted in any order.

https://leetcode.com/problems/customer-who-visited-but-did-not-make-any-transactions/description/

예시

Input: 
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    |
+----------------+----------+--------+
Output: 
+-------------+----------------+
| customer_id | count_no_trans |
+-------------+----------------+
| 54          | 2              |
| 30          | 1              |
| 96          | 1              |
+-------------+----------------+
Explanation: 
Customer with id = 23 visited the mall once and made one transaction during the visit with id = 12.
Customer with id = 9 visited the mall once and made one transaction during the visit with id = 13.
Customer with id = 30 visited the mall once and did not make any transactions.
Customer with id = 54 visited the mall three times. During 2 visits they did not make any transactions, and during one visit they made 3 transactions.
Customer with id = 96 visited the mall once and did not make any transactions.
As we can see, users with IDs 30 and 96 visited the mall one time without making any transactions. Also, user 54 visited the mall twice and did not make any transactions.

문제 풀이

import pandas as pd

def find_customers(visits: pd.DataFrame, transactions: pd.DataFrame) -> pd.DataFrame:
    # visit_id 기준 left join
    merged = pd.merge(visits, transactions, on='visit_id', how='left')
    
    # transaction_id가 NaN(결측값)인 행만 필터링
    filtered = merged.loc[merged['transaction_id'].isna()]
    
    # customer_id로 그룹화하여 각 고객의 방문 횟수(count_no_trans)를 계산하고 결과를 데이터프레임으로 반환
    result = filtered.groupby('customer_id').size().reset_index(name='count_no_trans')
    return result

import pandas as pd

def find_customers(visits: pd.DataFrame, transactions: pd.DataFrame) -> pd.DataFrame:
    # visit_id 기준 left join
    merged = pd.merge(visits, transactions, on='visit_id', how='left')
    
    # transaction_id가 NaN(결측값)인 행만 필터링
    filtered = merged.loc[merged['transaction_id'].isna()]
    
    # customer_id로 그룹화하고, 각 customer_id에 대한 visit_id의 개수를 계산하여 새로운 열 count_no_trans를 생성
    result = filtered.groupby('customer_id', as_index=False).agg(count_no_trans=('visit_id','count'))
    return result

---

파이썬을 독학하시는 분들에게 도움이 되길 바라며,

혹 더 좋은 방법이 있거나 오류가 있다면 편하게 말씀 부탁드립니다.