[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)
|
많은 도움이 되었기를 바랍니다.
이번 포스팅이 도움이 되었다면 아래의 '공감~
'를 꾹 눌러주세요.
댓글을 달아 주세요
비밀댓글입니다
안녕하세요 차차님,
간단한 예제 샘플 데이터와 기대하시는 아웃풋 이미지 올려주시면 코드 짜서 올릴께요.
비밀댓글입니다
안녕하세요.
제가 지금 해외출장 나와있는데요, 어찌된 일인지 모바일 티스토리 앱으로 댓글 업데이트 알람을 받지 못해서요, 이제서야 (12월12일, 오전10시) 웹으로 블로그 로그인 한 후에야 추가로 댓글 달아주신것을 확인했습니다.
제가 업무시간에는 시간을 낼 수 없어서요, 퇴근 후에 저녁에 답글 달도록 하겠습니다. 너무나 많이 늦어져서 죄송합니다.
비밀댓글입니다
안녕하세요 차차님,
댓글에 남겨주신 말씀처럼, pandas 시계열 데이터 DataFrame 을 처리할 때 특정 시간 단위 구간별 집계/요약은 resample() 메소드를 사용하는 것이 가장 간편한 방법입니다.
resample() 메소드를 사용해서 여러 통계량을 좀더 쓰기 쉽도록 사용자 정의 함수(UDF)를 만들어보았는데요, 아래의 포스팅을 참고하시기 바랍니다.
https://rfriend.tistory.com/494
만약, 대용량 시계열 데이터를 처리해야 하는 경우라면 관계형 Database를 사용하는 것이 빠르고 편리한 방법입니다.
PostgreSQL DB를 사용해서 python pandas resample() 메소드와 동일한 결과를 얻기 위한 방법에 대한 소개는 아래 포스팅을 참고하세요.
https://rfriend.tistory.com/495
포스팅 2개 하다보니 주말이 훌쩍 가버리네요. ^^;