How to use {sqldf} package

{sqldf} package는 R data frame에 SQL문을 사용하기 위한 package이다. 즉, SQL문을 사용하여 R의 data frame을 manipulation하는데 사용된다.

본 문서는 {sqldf} package를 사용하여 data frame에서 필요한 부분만 select하여 가져와서 본인이 원하는 data frame을 만드는 방법과 여러개의 data frame을 merge하는 방법에 대해서 설명하겠다.

Data frame description

• bank(bank.dat) : 은행정보, 당행 은행거래 정보
• etc(etc_delinquency.dat) : 타사연체, 타사 연체관련 정보
• multi(multi_card.dat) : 복수카드, 복수카드계좌(4+) 실적정보
• pers(personal.dat) : 신상정보, 개인신상정보 및 신청정보
• perf(Performance_Data.dat) : 성과측정, 성과측정을 위한 정보

1. 데이터 불러오기

csv 형식으로 저장되어 있는 파일을 R로 불러오는 내용의 코드이다. read.csv() function을 사용해서 데이터를 import 하였다.

bank <- read.csv("bank.dat", sep = ",", header = F)
etc <- read.csv("etc_delinquency.dat", sep = ",", header = F)
multi <- read.csv("multi_card.dat", sep = ",", header = F)
pers <- read.csv("personal.dat", sep = ",", header = F)
perf <- read.csv("Performance_Data.dat", sep = ",", header = F)

library(xtable)
print(xtable(head(perf, 10)), type = "html", include.rownames = F)

위에 보이는 output은 “perf” data frame의 일부분을 출력한 것이다.

2. 변수명 변경

5개의 data frame을 살펴 보면, 1~4번째 column은 같고 5번째 이후의 column들은 각 data frame마다 다르다. 때문에, 각 data frame마다 5번째 이후의 column을 'b1', 'b2', … 와 같은 형태로 바꾸고자 한다.

colnames(bank) <- paste("b", 1:63, sep = "")
# b1, b2, ..., b63까지 변수명 지정
colnames(bank)[c(1:2)] <- c("id", "date")
# 1,2 번째 column name을 id, date로 지정

colnames(etc) <- paste("e", 1:36, sep = "")
# c1, c2, ..., c36까지 변수명 지정
colnames(etc)[c(1:2)] <- c("id", "date")
# 1,2 번째 column name을 id, date로 지정

colnames(multi) <- paste("m", 1:42, sep = "")
# m1, m2, ..., m42까지 변수명 지정
colnames(multi)[c(1:2)] <- c("id", "date")
# 1,2 번째 column name을 id, date로 지정

colnames(pers) <- paste("p", 1:35, sep = "")
# p1, p2, ..., p35까지 변수명 지정
colnames(pers)[c(1:2)] <- c("id", "date")
# 1,2 번째 column name을 id, date로 지정

colnames(perf) <- c("id", "date", "date_ymd", "target")

3. Packages {sqldf}

R에서 SQL문을 사용하기 위해서는 {sqldf} package를 설치해야 한다.

설치를 위해서는

(i) install.packages(“sqldf”) 혹은, (ii) [Packages] - [Install Packages] - [sqldf]를 선택하여 설치하면 된다.

library(sqldf)
library(tcltk)

# library 'sqldf', 'tcltk'

이제 우리는 {sqldf} package에 있는 sqldf() function을 사용할 것이다. 아래에 4. 데이터 선택에서 사용할 sqldf() function에 대해 간략히 설명을 하면,

• sqldf(“select * from data where column1 'expression1' and column2 'expression2' and … order by column”)

  a. sqldf() function에서 () 안의 전체적인 표현은 Quatation mark(“”)안에 들어가야 한다.

  b. select * : select all, 데이터 전체를 선택한다는 것이다. * 대신에 특정 column들을 지정할 수도 있다.

  c. from data : 여기서 data는 SQL문을 사용할 data frame을 의미한다.

  d. where 및 그 다음: where 이하는 조건을 걸어주는 것이다. column1, column2, …에는 data frame 안에 해당하는 column name을 넣어주면 되고, expression1, expression2, …에는 원하는 조건을 넣어주면 된다.

  e. order by column : 정렬 기준을 지정하는 것이다.

4. 데이터 선택

“perf” data frame에서 'target' column에서 Exclusion에 해당하는 observation은 필요 없고 'id' column에서 끝에 자리수가 1인 observation만 필요하다.

아래의 code는 데이터의 필요한 부분을 Select하는 sqldf() function이다.

perf_2 <- sqldf("select * from perf where target in ('Good', 'Bad')\nand substr(id,10,1) == '1' order by id")

# 'perf' data frame으로부터 'target'이 Exclusion인 observation 제외 'id'의
# 끝에 자리수가 1인 observation만 필요

print(xtable(head(perf_2, 10)), type = "html", include.rownames = F)

위의 output은 1~10번째 observation만 우선 살펴 본 것이다.

5. 데이터 합치기

이제 5개의 data frame을 하나의 data frame으로 합쳐야 한다.

그 전에, 여기에서 사용하는 sqldf() function에 대해 간략이 설명한다.

• sqldf(“select * from data1 inner join data2 using (column1, column2) inner join … order by column”)

  a. sqldf() function에서 () 안의 전체적인 표현은 Quatation mark(“”)안에 들어가야 한다.

  b. data1, data2, … : 하나로 합칠 data frame을 의미한다.

  c. join : join은 data frame들을 합치겠다는 것이다.

  d. using() : ()안에 들어가는 column이 합치는데 기준이 된다.

  e. order by column : 정렬 기준을 지정하는 것이다.

이제 그럼 아래의 code 와 output을 살펴보자.

final <- sqldf("select * from perf_2 join pers using (id, date)\njoin multi using (id, date) join etc using(id, date)\njoin bank using (id, date) order by id")
# 5개의 data frame을 id, data순으로 오름차순 정렬로 데이터를 합침.

print(xtable(head(final[, 1:22], 5)), type = "html", include.rownames = F)

# column이 매우 길기 때문에 'final' data frame의 22번째 column까지만 표시

위의 output은 column이 172개 정도 된다.한 줄에 다 보이기에는 많으므로 22번째 column까지만 출력하였다.

지금까지 {sqldf} package를 이용해서 데이터를 select하고 merge하는 방법에 대해 설명하였다.

기타 {sqldf} package에 대한 reference는 아래의 링크를 참고하면 된다.

• Google Project Hosting: sqldf
• Package 'sqldf'

Hankuk University of Foreign Studies. Dept of Statistics. Daewoo Choi Lab. Yong Cha.
한국외국어대학교 통계학과 최대우 교수 연구실 차용
e-mail : yong.stat@gmail.com