[LeetCode] 1179. Reformat Department Table (Pivot Table)

 

reindexinghttps://leetcode.com/problems/reformat-department-table/

 

위 문제는 LeetCode 내에서도 굉장히 쉬운 문제에 속하지만 행으로 쌓인 데이터를 피봇테이블로 변환하는 정형화된 로직을 SQL과 Pandas 모두로 연습해보기에 좋은 문제라고 생각되어 정리하게 되었다.

 

Department라는 테이블의 스키마를 보면

(id, revenue, month)로 구성된 각 필드가 month가 지남에 따라 id에 대한 revenue가 결산되어 기록되는 형태임을 알 수 있다.

 

문제의 요구사항은 일종의 로그 형태로 월마다 쌓인 기록을 각 사용자(id) 별로 언제(month) 얼만큼(total revenue per month) 구매하였는지를 알 수 있는 피벗 테이블(pivot table) 형태로 바꾸는 SQL 쿼리를 작성하는 문제이다.

 

SQL 관점에서

id를 기준으로 -> GROUP BY & 조회에 포함(일종의 인덱스)

month를 해당하는 값마다 -> CASE WHEN 조건문

전체 revenue의 집계를 구하기 -> SUM() 집계 사용

와 같이 요약해볼 수 있을 것 같다.

 

이를 MySQL 쿼리로 옮기면 아래와 같다.

특히 문제에서는 존재하지 않는 값은 NULL로 처리하는 것을 요구하고 있기 때문에

CASE WHEN에 해당하지 않는 월의 경우 집계에서 빠지게 되어 자연스럽게 NULL이 조회되도록 하면 된다.

 

SELECT id
    , SUM(CASE WHEN month = "Jan" THEN revenue END) AS 'Jan_Revenue'
    , SUM(CASE WHEN month = "Feb" THEN revenue END) AS 'Feb_Revenue'
    , SUM(CASE WHEN month = "Mar" THEN revenue END) AS 'Mar_Revenue'
    , SUM(CASE WHEN month = "Apr" THEN revenue END) AS 'Apr_Revenue'
    , SUM(CASE WHEN month = "May" THEN revenue END) AS 'May_Revenue'
    , SUM(CASE WHEN month = "Jun" THEN revenue END) AS 'Jun_Revenue'
    , SUM(CASE WHEN month = "Jul" THEN revenue END) AS 'Jul_Revenue'
    , SUM(CASE WHEN month = "Aug" THEN revenue END) AS 'Aug_Revenue'
    , SUM(CASE WHEN month = "Sep" THEN revenue END) AS 'Sep_Revenue'
    , SUM(CASE WHEN month = "Oct" THEN revenue END) AS 'Oct_Revenue'
    , SUM(CASE WHEN month = "Nov" THEN revenue END) AS 'Nov_Revenue'
    , SUM(CASE WHEN month = "Dec" THEN revenue END) AS 'Dec_Revenue'
FROM Department
GROUP BY id

 

이를 Python의 Pandas 라이브러리 기준으로 다시 작성해보았다.

 

처음에 내가 생각한 방식은 Pandas.DataFrame의 .pivot_table 메서드를 활용하여 한번에 존재하는 값들에 대한 pivot table을 만든 후에 값이 존재하지 않는 월들의 경우에 NaN(SQL에서는 NULL)로 처리해주는 방식을 택했다. 이후에 다시 컬럼 순서를 올바르게 정렬하고, 컬럼 이름을 요구사항에 맞게 변경하는 로직으로 코드를 작성했다.

 

import pandas as pd

def reformat_table(department: pd.DataFrame) -> pd.DataFrame:

    reformed_table = department.pivot_table(
        index='id', # SQL의 GROUP BY
        columns='month', # PIVOT으로 전개할 열
        values='revenue', # PIVOT Table의 값에 사용될 열
        aggfunc='sum' # 집계하는 방식
    )
    # rename the columns
    cols = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]
    for new_col in cols:
        if new_col not in list(reformed_table.columns):
            reformed_table[new_col] = None
    
    reformed_table = reformed_table[cols] # re-order the columns
    for original_col in cols:
        reformed_table.rename(columns={original_col : f"{original_col}_Revenue"}, inplace=True)
    reformed_table.reset_index(inplace=True) # get 'id' out from index to a column
    
    return reformed_table

 

하지만 pivot table을 만드는 과정을 SQL 쿼리만큼 깔끔하게 정형화해서 작성하지 못했다는 생각이 들어

Solution에 있는 time complexity 측면에서 더 효율적인 코드의 경우 어떠한 방식으로 구현했는지 찾아보게 되었다.

 

그 중 Pandas.DataFrame의 .reset_index 라는 메서드를 효과적으로 사용하는 경우에 내가 처음에 일부분만 피봇 테이블을 만든 후에 인위적으로 필요한 컬럼을 추가해주었던것과 달리 의도된 동작을 한번에 수행할 수 있다는 것을 알게 되었다.

 

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.reindex.html

pandas.DataFrame.reindex — pandas 2.2.1 documentation

 

공식 Documentation에서는 다음과 같이 설명하고 있다.

Conform DataFrame to new index with optional filling logic.
Places NA/NaN in locations having no value in the previous index. A new object is produced unless the new index is equivalent to the current one and copy=False.

 

https://pandas.pydata.org/pandas-docs/stable/user_guide/basics.html#basics-reindexing

Essential basic functionality — pandas 2.2.1 documentation

 

간단히 로직을 요약해보자면 다음과 같았다.

1) .pivot(index, columns, values)

2) .reindex(columns)

3) .rename(columns)

4) .reset_index()

 

import pandas as pd

def reformat_table(department: pd.DataFrame) -> pd.DataFrame:
	
    months = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]
    
    df = department.pivot(index='id', columns='month', values='revenue')
    
    df = df.reindex(columns=months)
    
    df.rename(columns = lambda months : months+"_Revenue", inplace=True)
    
    df.reset_index(inplace=True)
    
    return df