xwMOOC 모형
데이터베이스 - dplyr
1 약물 부작용 데이터 1
약을 복용했을 때, 이상증상이 발생되면 미국 식약처, FDA에서 운영중인 미국 FDA 부작용 사례 보고 시스템(FDA Adverse Event Reporting System)에 관련 내용을 등록시킬 수 있다. 약과 관련된 부작용에 대한 사례를 등록할 때 인적정보(demography), 약(drug), 반응(reaction)과 결과(outcome), 증상(indication)에 대한 정보를 제출하게 되어있다.
- 인적정보(demography):
demo - 약(drug):
drug - 반응(reaction):
reac - 결과(outcome):
outc - 증상(indication):
indi
2018년부터 분기별로 2019년 현재시점까지 가능한 .csv.zip 데이터를 download.file() 함수로 다운로드 받아 unzip() 함수로 압축을 풀어 .csv 파일로 저장시킨다.
library(tidyverse)
library(lubridate)
library(download)
year_start <- 2018
year_last <- year(Sys.time())
for (i in year_start:year_last){
for (m in 1:4){
url1 <- paste0("http://www.nber.org/fda/faers/",i,"/demo",i,"q",m,".csv.zip")
download.file(url1, dest=paste0("data/", "demo", i, "q", m, ".csv.zip")) # Demography
unzip(paste0("data/demo", i, "q", m, ".csv.zip"), exdir="data/")
url2<-paste0("http://www.nber.org/fda/faers/",i,"/drug",i,"q",m,".csv.zip")
download.file(url2, dest=paste0("data/", "drug", i, "q", m, ".csv.zip")) # Drug
unzip(paste0("data/drug", i, "q", m, ".csv.zip"), exdir="data/")
url3<-paste0("http://www.nber.org/fda/faers/",i,"/reac",i,"q",m,".csv.zip")
download.file(url3, dest=paste0("data/", "reac", i, "q", m, ".csv.zip")) # Reaction
unzip(paste0("data/reac", i, "q", m, ".csv.zip"), exdir="data/")
url4<-paste0("http://www.nber.org/fda/faers/",i,"/outc",i,"q",m,".csv.zip")
download.file(url4, dest=paste0("data/", "outc", i, "q", m, ".csv.zip")) # Outcome
unzip(paste0("data/outc", i, "q", m, ".csv.zip"), exdir="data/")
url5<-paste0("http://www.nber.org/fda/faers/",i,"/indi",i,"q",m,".csv.zip")
download.file(url5, dest=paste0("data/", "indi", i, "q", m, ".csv.zip")) # Indication for use
unzip(paste0("data/indi", i, "q", m, ".csv.zip"), exdir="data/")
}
}1.1 인적사항(demography)
demo_filenames <- list.files(path="data", pattern="^demo.*.csv", full.names=TRUE)
demo_df <- map_df(demo_filenames, read_csv) %>%
select(primaryid, caseid, age, age_cod, event_dt,
sex, wt, wt_cod, occr_country)
glimpse(demo_df)Observations: 1,684,852
Variables: 9
$ primaryid <dbl> 41239153, 41512242, 41595514, 42221852, 58892385, 5…
$ caseid <dbl> 4123915, 4151224, 4159551, 4222185, 5889238, 594279…
$ age <dbl> NA, 47, 19, 47, 23, 50, 41, 47, 21, 63, NA, 44, 19,…
$ age_cod <chr> NA, "YR", "YR", "YR", "YR", "YR", "YR", "YR", "YR",…
$ event_dt <dbl> NA, 20040516, 20040125, NA, 19960618, 20051103, 199…
$ sex <chr> "F", "F", "F", "M", "F", "M", "F", "F", "F", "M", N…
$ wt <dbl> NA, NA, 53.00, NA, 83.00, 69.00, 54.00, NA, 85.00, …
$ wt_cod <chr> NA, NA, "KG", NA, "KG", "KG", "KG", NA, "KG", "KG",…
$ occr_country <chr> "GB", "GB", "FR", "US", "DK", "FR", "DE", "FR", "BR…
1.2 약(drug)
drug_filenames <- list.files(path="data", pattern="^drug.*.csv", full.names=TRUE)
drug_df <- map_df(drug_filenames, read_csv) %>%
select(primaryid, drug_seq, drugname, route)
glimpse(drug_df)Observations: 6,873,134
Variables: 4
$ primaryid <dbl> 41239153, 41239153, 41239153, 41512242, 41512242, 4151…
$ drug_seq <dbl> 1, 2, 3, 1, 2, 3, 4, 5, 1, 2, 3, 4, 5, 6, 1, 2, 1, 2, …
$ drugname <chr> "DIDANOSINE.", "NEVIRAPINE.", "ZIDOVUDINE.", "ZOVIRAX"…
$ route <chr> "Unknown", "Unknown", "Unknown", "Intravenous (not oth…
1.3 반응(reaction) - 이상증상(Adverse Effect)
reaction_filenames <- list.files(path="data", pattern="^reac.*.csv", full.names=TRUE)
reac_df <- map_df(reaction_filenames, read_csv) %>%
select(primaryid, pt)
glimpse(reac_df)Observations: 5,179,206
Variables: 2
$ primaryid <dbl> 41239153, 41239153, 41239153, 41512242, 41512242, 4151…
$ pt <chr> "Amniotic cavity infection", "Pregnancy", "Premature r…
1.4 결과(outcome)
outcome_filenames <- list.files(path="data", pattern="^outc.*.csv", full.names=TRUE)
outc_df <- map_df(outcome_filenames, read_csv) %>%
select(primaryid, outc_cod)
glimpse(outc_df)Observations: 1,225,609
Variables: 2
$ primaryid <dbl> 41239153, 41512242, 41512242, 41512242, 41595514, 4159…
$ outc_cod <chr> "OT", "HO", "LT", "OT", "HO", "OT", "DE", "LT", "DE", …
1.5 진단/증상(indication)
indi_filenames <- list.files(path="data", pattern="^indi.*.csv", full.names=TRUE)
indi_df <- map_df(indi_filenames, read_csv) %>%
select(primaryid , indi_drug_seq, indi_pt)
glimpse(indi_df)Observations: 4,880,787
Variables: 3
$ primaryid <dbl> 41239153, 41239153, 41239153, 41595514, 41595514, …
$ indi_drug_seq <dbl> 1, 2, 3, 1, 2, 3, 4, 5, 6, 2, 1, 2, 3, 4, 5, 6, 7,…
$ indi_pt <chr> "HIV infection", "HIV infection", "HIV infection",…
2 데이터베이스 생성
fda_database 데이터베이스를 생성시키고 copy_to 명령어로 데이터베이스에 테이블을 추가시킨다.
## 데이터 베이스 생성
fda_database<- src_sqlite("adverse_events", create = TRUE)
## 테이블 추가
copy_to(fda_database, demo_df, temporary = FALSE, overwrite = TRUE)
copy_to(fda_database, drug_df, temporary = FALSE, overwrite = TRUE)
copy_to(fda_database, indi_df, temporary = FALSE, overwrite = TRUE)
copy_to(fda_database, reac_df, temporary = FALSE, overwrite = TRUE)
copy_to(fda_database, outc_df, temporary = FALSE, overwrite = TRUE) 2.1 데이터베이스 연결
create = FALSE 인자를 넣어 “adverse_events” 데이터베이스에 연결하고 조회할 수 있는 테이블을 src_tbls() 함수로 확인한다.
[1] "demo_df" "drug_df" "indi_df" "outc_df"
[5] "reac_df" "sqlite_stat1" "sqlite_stat4"
3 데이터베이스 쿼리
인터넷에서 데이터를 다운로드 받아 데이터베이스에 저장시킨 후에 SQL문을 작성해서 데이터 분석 작업을 수행해도 좋지만, R dplyr 문법을 사용해서 데이터 분석 작업을 수행하는 것도 가능하다.
3.1 헬로월드
fda_df 데이터베이스에서 demo_df 테이블을 추출하여 R 데이터프레임으로 메모리에 demography_df 라는 객체를 만든다.
[1] "tbl_SQLiteConnection" "tbl_dbi" "tbl_sql"
[4] "tbl_lazy" "tbl"
# Source: lazy query [?? x 9]
# Database: sqlite 3.22.0 [/Users/statkclee/swc/model/adverse_events]
primaryid caseid age age_cod event_dt sex wt wt_cod occr_country
<dbl> <dbl> <dbl> <chr> <dbl> <chr> <dbl> <chr> <chr>
1 41239153 4123915 NA <NA> NA F NA <NA> GB
2 41512242 4151224 47 YR 20040516 F NA <NA> GB
3 41595514 4159551 19 YR 20040125 F 53 KG FR
3.2 작동원리
dplyr 문법에 맞춰 정형데이터 데이터프레임에 대한 쿼리를 작성하게 되면 dplyr에서 데이터베이스에 맞게 SQL 문을 자동으로 작성해서 데이터베이스에서 쿼리 결과를 가져오게 된다.
# Source: lazy query [?? x 9]
# Database: sqlite 3.22.0 [/Users/statkclee/swc/model/adverse_events]
primaryid caseid age age_cod event_dt sex wt wt_cod occr_country
<dbl> <dbl> <dbl> <chr> <dbl> <chr> <dbl> <chr> <chr>
1 72467803 7.25e6 53 YR 20091211 M 70 KG KR
2 81008883 8.10e6 57 YR 20100212 F 51.2 KG KR
3 81183992 8.12e6 68 YR 20100224 F 52.3 KG KR
4 84850822 8.49e6 39 YR 20100904 F 52.7 KG KR
5 100117582 1.00e7 65 YR 20140302 M NA <NA> KR
6 103662838 1.04e7 65 YR 20140725 M 60 KG KR
7 105127174 1.05e7 71 YR 20120926 M NA <NA> KR
8 108908872 1.09e7 56 YR 20150116 M NA <NA> KR
9 111297233 1.11e7 NA <NA> 20150427 <NA> NA <NA> KR
10 111513232 1.12e7 26 YR NA F 49 KG KR
# … with more rows
<SQL>
SELECT *
FROM `demo_df`
WHERE (`occr_country` = 'KR')
<SQL>
SELECT *
FROM `demo_df`
WHERE (`occr_country` = 'KR')
<PLAN>
selectid order from detail
1 0 0 0 SCAN TABLE demo_df
하지만, tail()은 동작하지 않는데 이유는 명확하다. 이를 원하면 전체 테이블을 collect() 명령으로 가져와서 R 데이터프레임으로 변환시킨 후에 tail() 명령어를 던지면 된다.
Error: tail() is not supported by sql sources
# A tibble: 6 x 9
primaryid caseid age age_cod event_dt sex wt wt_cod occr_country
<dbl> <dbl> <dbl> <chr> <dbl> <chr> <dbl> <chr> <chr>
1 1547866310 1.55e7 70 YR 20180913 M NA <NA> FR
2 1549232712 1.55e7 38 YR 2018 F 113. KG US
3 1551576410 1.55e7 58 YR NA M NA <NA> PT
4 1555981410 1.56e7 47 YR 20180828 F NA <NA> CA
5 1557437710 1.56e7 NA <NA> NA F NA <NA> GB
6 1559507712 1.56e7 60 YR 2009 F NA <NA> SK
4 사례
국가별 약물 사건사고 건수에 대해 사우이 10개 국가를 뽑아내는 통계를 demography_df 데이터베이스 테이블 객체에 대해 dplyr 동사로 진행을 한 후에 collect() 명령어로 R 메모리에 데이터프레임 객체로 변환시킨 후에 ggplot 막대그래프로 시각화한다.
country_df <- demography_df %>%
group_by(Country = occr_country) %>%
summarize(Total=n()) %>%
arrange(desc(Total)) %>%
filter(Country != '')
country_df %>%
collect() %>%
top_n(10, wt = Total) %>%
ggplot(aes(x=fct_reorder(Country,Total), y=Total)) +
geom_bar(stat='identity', color='skyblue', fill='#b35900') +
labs(title = 'Top ten countries with highest number of adverse event reports',
x = "",
y = 'Total number of reports') +
coord_flip()
5 테이블 결합
관계형 데이터베이스의 장점은 복잡한 데이터 관계를 primaryid같은 키를 활용하여 테이블을 구조화하여 관리용이성을 증가시키고 효율성을 키울수 있다는 점이다. 반대로 데이터를 분석할 경우 관계형 데이터베이스 테이블을 결합(join)하여 추가 분석 작업을 용이하게 한다.
outcome_df <- tbl(fda_db, "outc_df" )
reaction_df <- tbl(fda_db, "reac_df" )
demography_df %>%
inner_join(outcome_df, by='primaryid', copy = TRUE) %>%
inner_join(reaction_df, by='primaryid', copy = TRUE) %>%
count(outc_cod, pt)# Source: lazy query [?? x 3]
# Database: sqlite 3.22.0 [/Users/statkclee/swc/model/adverse_events]
# Groups: outc_cod
outc_cod pt n
<chr> <chr> <int>
1 CA Abdominal adhesions 4
2 CA Abdominal discomfort 14
3 CA Abdominal distension 16
4 CA Abdominal hernia 9
5 CA Abdominal pain 53
6 CA Abdominal pain lower 1
7 CA Abdominal pain upper 25
8 CA Abdominal rigidity 4
9 CA Abdominal wall anomaly 9
10 CA Abdominal wall cyst 2
# … with more rows
6 데이터베이스 마무리
rm(), gc()를 하게 되면 깔끔하게 sqlite 데이터베이스 연결을 마무리고 정리한다.
used (Mb) gc trigger (Mb) limit (Mb) max used (Mb)
Ncells 1315332 70.3 3424830 183.0 NA 3424830 183.0
Vcells 99101337 756.1 291681886 2225.4 16384 361015178 2754.4