[Pandas] 판다스에서 SQL 윈도우 함수 사용해보기

0. SQL 윈도우 함수란?

Window functions operate on a range of rows, defined by the OVER clause. It is like you are looking through the window, you see sky, star, moon. You are not seeing the whole sky, you see part of the sky.

Window 함수 이름의 유래에 대해 찾아봤습니다. "창 을 통해 밖을 바라보면 하늘의 부분만을 볼 수 있다". 윈도우 함수의 기본적인 개념입니다.

 

여기서 창 = 한 행 이라고 생각하시면 됩니다. 즉, 윈도우 함수는 Group By의 결과를 한 행(각 Window)마다 뿌려주는 함수라고 할 수 있습니다. GroupBy 한 결과를 각각의 윈도우(=행)에 투영시켜준다고 생각하시면 편할 것 같습니다.

 

1. SQL 윈도우 함수를 쓰는 이유는?

 

https://sundaskhalid.medium.com/sql-window-function-vs-group-by-b246d14223d2

 

윈도우 함수를 쓰는 이유는 "편리함" 때문입니다. 왜냐하면, 윈도우 함수를 사용할 경우 원본 데이터를 보존한 상태에서 GroupBy 연산의 결과를 컬럼으로 추가할 수 있기 때문인데요. 

 

상기 그림에서 GROUP BY와 Window functions을 비교해봅시다. GROUP BY의 경우 Group의 수만큼 결과 행이 줄어든 것을 확인할 수 있습니다. 반면, 윈도우 함수의 경우 원본 행이 그대로 보존되어 있는 것을 확인할 수 있습니다.

 

2. Pandas에서 윈도우 함수처럼 쓰려면?

그렇다면, Pandas에서도 윈도우 함수처럼 사용할 수 있는 함수가 있을까요? 네 있습니다! 바로

 

pandas.DataFrame.transform

transform이라는 메서드 인데요. 

 

 

다큐먼트를 살펴보면 Same axis length를 가진다는 표현이 있습니다. 즉, GroupBy 결과를 각 행별로 뿌려주는 함수라고 할 수 있습니다. 한번 사용법을 알아봅시다.

 

사용법

import numpy as np
import pandas as pd
import seaborn as sns

penguins_raw = sns.load_dataset(name = "penguins")
penguins_data = penguins_raw.copy() #원본 복사
penguins_data = penguins_data.dropna() #null값 제거

#DataSet_tune mm -> cm 
penguins_data[["bill_length_mm", "bill_depth_mm", "flipper_length_mm"]] = \
    penguins_data[["bill_length_mm", "bill_depth_mm", "flipper_length_mm"]] \
        .apply(lambda x : x/10)

#DataSet_tune g -> kg
penguins_data["body_mass_g"] = penguins_data["body_mass_g"]/1000

#DataSet_tune change colname 
penguins_data = penguins_data.rename({"bill_length_mm" : "bill_length_cm", "bill_depth_mm" : "bill_depth_cm" \
                                         ,"flipper_length_mm" : "flipper_length_cm", "body_mass_g" : "body_mass_kg" \
                                     }, axis = 1)

 

오늘의 샘플 데이터는 seaborn에서 기본적으로 제공해주는 데이터 중 하나인 penguins 데이터 입니다.

 

 

펭귄 부리의 길이, 폭, 몸무게, 팔 길이 등이 있는 데이터 입니다.

 

penguins_data.groupby(['species', 'island'])[["bill_length_cm", "bill_depth_cm", "flipper_length_cm", "body_mass_kg"]] \
    .agg(np.mean).reset_index()
    
penguins_data.groupby(['species', 'island']).size().reset_index()

 

0은 데이터 수

 

각각 세 종류의 섬에 살고 있는 세 가지 펭귄 종에 대한 통계치입니다. 전반적으로 Adelie 보다는 chinstrap이나 Gentoo 펭귄이 부리 길이가 긴 것을 알 수 있습니다. 특히, Gentoo 평균의 경우 무게가 가장 무겁고 팔길이가 긴 것으로 보아 덩치가 가장 클 것으로 예상해볼 수 있습니다.

 

여기서, 각 종/섬 별로 몸무게 평균 초과, 평균 이하인 새의 수가 몇명인지 살펴본다고 가정하겠습니다.

 

그렇게 하기 위해서는? 각 행별로 몸무게와 종/섬별 몸무게 평균을 비교해야 합니다. 이때, 사용하는 것이 transform입니다!

 

penguins_data["body_mass_avg"] = penguins_data.groupby(['species', 'island'])["body_mass_kg"] \
	.transform(np.mean)

 

사용방법은 굉장히 간단한데요. transform이라는 함수에 집계 함수만 적용해주면 됩니다.

 

 

그러면 위와 같이 각 행이 그대로 보존되면서 그룹 연산 결과가 추가된 것을 알 수 있습니다.

 

penguins_data["body_mass_avgGroup"] = np.where(penguins_data.body_mass_kg > penguins_data.body_mass_avg
                                               , "0.평균초과", "1.평균이하")
                                               
penguins_data.groupby(['species', 'island', 'body_mass_avgGroup']).size().reset_index()

 

 

위와 같이 transform 메서드를 사용하면 GroupBy 연산 결과를 편리하게 원본 데이터 셋에 추가하고 이를 바탕으로 최종적인 연산을 진행할 수 있습니다.

 

Ref. 

https://stackoverflow.com/questions/62985636/window-functions-in-sql-why-are-they-called-window-functions

Window functions in SQL: why are they called window functions?

https://sundaskhalid.medium.com/sql-window-function-vs-group-by-b246d14223d2

SQL Window function vs Group by