[Python, R, PostgreSQL] 그룹별로 행을 내리기, 올리기 (Lag, Lead a row by Group)
Python 분석과 프로그래밍/Python 데이터 전처리 2019. 12. 9. 00:55Lag, 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
|
shift() 함수를 쓰기 전에 sort_values() 함수로 정렬을 해주는데요, lag 는 내림차순 정렬, lead는 오름차순 정렬임에 주의해야 합니다. (PostgreSQL, R 대비 Python이 좀 불편하긴 하네요 -,-;)
(a) lag: sort_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'])
|
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
|
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)
|
많은 도움이 되었기를 바랍니다.
이번 포스팅이 도움이 되었다면 아래의 '공감~
'를 꾹 눌러주세요.
