[Leetcode] 1693. Daily Leads and Partners MySQL과 pandas로 풀기

문제 링크

For each date_id and make_name, find the number of distinct lead_id's and distinct partner_id's.

Return the result table in any order.

select date_id
     , make_name
     , count(distinct lead_id) as unique_leads
     , count(distinct partner_id) as unique_partners
from DailySales
group by date_id, make_name

import pandas as pd

def daily_leads_and_partners(daily_sales: pd.DataFrame) -> pd.DataFrame:
    result = daily_sales.groupby(['date_id', 'make_name']).agg(
          unique_leads=('lead_id', 'nunique'),
          unique_partners=('partner_id', 'nunique')
      ).reset_index()
    return result

reset_index()를 하는 이유

groupby를 하면 pandas는 기본적으로 그룹 기준 컬럼(date_id, make_name)을 인덱스로 만들기 때문에 date_id와 make_name을 일반 컬럼으로 되돌려야 함.

df.groupby([group_cols]).agg(
    new_col1=('col1', 'func1'),
    new_col2=('col2', 'func2'),
    ...
)