Lag, Lead window function은 시계열 데이터를 처리할 때 많이 사용하는 매우 유용한 함수입니다. 


이번 포스팅에서는 PostgreSQL, Python (pandas), R (dplyr) 을 이용해서 그룹별로 행을 하나씩 내리기, 올리기 (lag or lead a row by group using PostgreSQL, Python, R) 하는 방법을 소개하겠습니다. 





  1. PostgreSQL로 그룹별로 특정 칼럼의 행을 하나씩 내리기, 올리기 

    (lag, lead a row by group using PostgreSQL lag(), lead() window function)


연월일(dt), 그룹ID(id), 측정값(val) 의 세 개 칼럼을 가진 시계열 데이터의 테이블을 PostgreSQL DB에 만들어보겠습니다. 



DROP TABLE IF EXISTS ts;

CREATE TABLE ts (

    dt date not null

    , id text not null  

    , val numeric not null

);


INSERT INTO ts VALUES 

  ('2019-12-01', 'a', 5)

, ('2019-12-02', 'a', 6)

, ('2019-12-03', 'a', 7)

, ('2019-12-04', 'a', 8)

, ('2019-12-01', 'b', 13)

, ('2019-12-02', 'b', 14)

, ('2019-12-03', 'b', 15)

, ('2019-12-04', 'b', 16);


SELECT * FROM ts ORDER BY id, dt;




PostgreSQL 의 LAG(value, offset, default), LEAD(value, offset, default) Window function을 이용해서 그룹ID('id') 별로 측정값('val')의 행을 하나씩 내리기(lag), 올리기(lead) 해보겠습니다. 행을 내리거나 올린 후에 빈 셀의 값은 'NULL'로 지정해주었습니다. 


LAG(), LEAD() 함수를 사용할 때 그룹ID('id')별로 년월일('dt') 을 기준으로 내림차순 정렬(OVER(PARTITIO BY id ORDER BY dt)) 을 해줍니다. 



-- lead() windows function

SELECT 

    *

    , LAG(val, 1, NULL) OVER (PARTITION BY id ORDER BY dt) AS val_lag_1

    , LEAD(val, 1, NULL) OVER (PARTITION BY id ORDER BY dt) AS val_lead_2

FROM ts;

 



lag(), lead() 함수를 사용해서 lag_1, lead_2 라는 새로운 칼럼을 추가한 'ts_lag_lead' 라는 이름의 테이블을 만들어보겠습니다. 



DROP TABLE IF EXISTS ts_lag_lead;

CREATE TABLE ts_lag_lead AS (

SELECT 

    *

    , LAG(val, 1, NULL) OVER (PARTITION BY id ORDER BY dt) AS val_lag_1

    , LEAD(val, 1, NULL) OVER (PARTITION BY id ORDER BY dt) AS val_lead_2

FROM ts

);


SELECT * FROM ts_lag_lead ORDER BY id, dt;

 





  2. Python pandas 로 DataFrame 내 그룹별 특정 칼럼의 행을 하나씩 내리기, 올리기 

     (shift a row by group using Python pandas library)


위에서 PostgreSQL의 lag(), lead() window function과 똑같은 작업을 Python pandas 를 가지고 수행해보겠습니다. 


먼저 dt, id, val의 칼럼을 가진 pandas DataFrame 시계열 데이터를 만들어보겠습니다. 



import pandas as pd


ts = pd.DataFrame({'dt': ['2019-12-01', '2019-12-02', '2019-12-03', '2019-12-04', 

                          '2019-12-01', '2019-12-02', '2019-12-03', '2019-12-04'], 

                  'id': ['a', 'a', 'a', 'a', 'b', 'b', 'b', 'b'], 

                  'val': [5, 6, 7, 8, 13, 14, 15, 16]})


ts

dtidval
02019-12-01a5
12019-12-02a6
22019-12-03a7
32019-12-04a8
42019-12-01b13
52019-12-02b14
62019-12-03b15
72019-12-04b16

 



shift() 함수를 쓰기 전에 sort_values() 함수로 정렬을 해주는데요, lag 는 내림차순 정렬, lead는 오름차순 정렬임에 주의해야 합니다. (PostgreSQL, R 대비 Python이 좀 불편하긴 하네요 -,-;)


(a) lagsort_values() 함수를 이용해서 년월일('dt')를 기준으로 내림차순 정렬 (ascending=True) 한 후, 'id' 그룹별로 'val' 값을 하나씩 내려기 groupby('id')['val'].shift(1)


(b) lead: sort_values() 함수를 이용해서 년월일('dt')를 기준으로 오름차순 정렬 (ascending=False) 한 후, 'id' 그룹별로 'val' 값을 하나씩 올리기 groupby('id')['val].shift(1)



# lag a row by group 'id'

ts['val_lag_1'] =  ts.sort_values(by='dt', ascending=True).groupby('id')['val'].shift(1)


# lead a row by group 'id'

ts['val_lead_1'] = ts.sort_values(by='dt', ascending=False).groupby('id')['val'].shift(1)


ts.sort_values(by=['id', 'dt'])

dtidvalval_lag_1val_lead_1
02019-12-01a5NaN6.0
12019-12-02a65.07.0
22019-12-03a76.08.0
32019-12-04a87.0NaN
42019-12-01b13NaN14.0
52019-12-02b1413.015.0
62019-12-03b1514.016.0
72019-12-04b1615.0NaN

 





  3. R dplyr 로 dataframe 내 그룹별 특정 칼럼의 행을 하나씩 내리기, 올리기 

     (lag, lead a row by group using R dplyr library)



위에서 PostgreSQL의 lag(), lead() window function과 똑같은 작업을 R dplyr library를 가지고 수행해보겠습니다. 


먼저 dt, id, val의 칼럼을 가진 R DataFrame 시계열 데이터를 만들어보겠습니다. 



#install.packages("dplyr")

library(dplyr)


dt <- c(rep(c('2019-12-01', '2019-12-02', '2019-12-03', '2019-12-04'), 2))

id <- c(rep('a', 4), rep('b', 4)) 

val <- c(5, 6, 7, 8, 13, 14, 15, 16)


ts <- data.frame(dt, id, val)

ts

A data.frame: 8 × 3
dtidval
<fct><fct><dbl>
2019-12-01a5
2019-12-02a6
2019-12-03a7
2019-12-04a8
2019-12-01b13
2019-12-02b14
2019-12-03b15
2019-12-04b16

 



R은 Postgresql 처럼 lag(), lead() window function을 가지고 있고 dplyr library의 chain operator를 써서 arrange() 함수로 'dt' 기준 내림차순 정렬하고, group_by(id)를 써서 그룹ID('id')별로 lag(), lead()를 무척 편리하게 적용해서 새로운 변수를 생성(mutate)할 수 있습니다. 



ts <- ts %>% 

    arrange(dt) %>%

    group_by(id)  %>% 

    mutate(val_lag_1 = lag(val, 1), 

          val_lead_1 = lead(val, 1))

 


arrange(ts, id, dt)

A grouped_df: 8 × 5
dtidvalval_lag_1val_lead_1
<fct><fct><dbl><dbl><dbl>
2019-12-01a5NA6
2019-12-02a657
2019-12-03a768
2019-12-04a87NA
2019-12-01b13NA14
2019-12-02b141315
2019-12-03b151416
2019-12-04b1615NA




많은 도움이 되었기를 바랍니다. 

이번 포스팅이 도움이 되었다면 아래의 '공감~'를 꾹 눌러주세요.



728x90
반응형
Posted by Rfriend
,