R 에서 SQL 사용 sqldf 패키지, 집계 aggregate() 함수

거래 원 데이터 (transaction raw data)를 받으면 분석 용도에 맞게 데이터 전처리를 할 때 보통 하는 일이 특정 기준 (가령, 고객 ID, 상품 ID, 채널 ID 등)에 대해 데이터를 집계(합계, 평균, 분산 등의 함수를 적용)하는 작업을 하게 됩니다. 

 

R에는 aggregate() 라는 함수가 있습니다만, 기존에 SQL에 익숙한 분석가라면 R 에서 SQL 문을 사용할 수 있게 해주는 sqldf package를 사용하면 쉽고 빠르게 집계를 할 수 있겠습니다. 

 

(단, sqldf 가 performance 이슈가 있으니 데이터 사이즈가 크다면, 그리고 데이터 처리 속도가 중요한 경우라면 sqldf 는 부적할 수도 있다는 점은 고려하셔야 겠습니다.)

 

R sqldf package 소개자료에 보면

  - Perform SQL Selects on R Data Frames
  - Manipulate R data frames using SQL

이라고 되어 있습니다.

 

 

 

 

그럼, sqldf package의 여러 기능, 함수 중에서 데이터 집계 관련한 함수만 몇 가지 선별하여서 소개하도록 하겠습니다.

 

실습에 사용할 데이터는 MASS 패키지에 내장된 Cars93 데이터 프레임의 자동차 유형(Type), 도시 연비(MPG.city), 고속도로 연비(MPG.highway) 를 사용하겠습니다.

 

> library(MASS) > str(Cars93) 'data.frame': 93 obs. of 27 variables: $ Manufacturer : Factor w/ 32 levels "Acura","Audi",..: 1 1 2 2 3 4 4 4 4 5 ... $ Model : Factor w/ 93 levels "100","190E","240",..: 49 56 9 1 6 24 54 74 73 35 ... $ Type : Factor w/ 6 levels "Compact","Large",..: 4 3 1 3 3 3 2 2 3 2 ... $ Min.Price : num 12.9 29.2 25.9 30.8 23.7 14.2 19.9 22.6 26.3 33 ... $ Price : num 15.9 33.9 29.1 37.7 30 15.7 20.8 23.7 26.3 34.7 ... $ Max.Price : num 18.8 38.7 32.3 44.6 36.2 17.3 21.7 24.9 26.3 36.3 ... $ MPG.city : int 25 18 20 19 22 22 19 16 19 16 ... $ MPG.highway : int 31 25 26 26 30 31 28 25 27 25 ... $ AirBags : Factor w/ 3 levels "Driver & Passenger",..: 3 1 2 1 2 2 2 2 2 2 ... $ DriveTrain : Factor w/ 3 levels "4WD","Front",..: 2 2 2 2 3 2 2 3 2 2 ... $ Cylinders : Factor w/ 6 levels "3","4","5","6",..: 2 4 4 4 2 2 4 4 4 5 ... $ EngineSize : num 1.8 3.2 2.8 2.8 3.5 2.2 3.8 5.7 3.8 4.9 ... $ Horsepower : int 140 200 172 172 208 110 170 180 170 200 ... $ RPM : int 6300 5500 5500 5500 5700 5200 4800 4000 4800 4100 ... $ Rev.per.mile : int 2890 2335 2280 2535 2545 2565 1570 1320 1690 1510 ... $ Man.trans.avail : Factor w/ 2 levels "No","Yes": 2 2 2 2 2 1 1 1 1 1 ... $ Fuel.tank.capacity: num 13.2 18 16.9 21.1 21.1 16.4 18 23 18.8 18 ... $ Passengers : int 5 5 5 6 4 6 6 6 5 6 ... $ Length : int 177 195 180 193 186 189 200 216 198 206 ... $ Wheelbase : int 102 115 102 106 109 105 111 116 108 114 ... $ Width : int 68 71 67 70 69 69 74 78 73 73 ... $ Turn.circle : int 37 38 37 37 39 41 42 45 41 43 ... $ Rear.seat.room : num 26.5 30 28 31 27 28 30.5 30.5 26.5 35 ... $ Luggage.room : int 11 15 14 17 13 16 17 21 14 18 ... $ Weight : int 2705 3560 3375 3405 3640 2880 3470 4105 3495 3620 ... $ Origin : Factor w/ 2 levels "USA","non-USA": 2 2 2 2 2 1 1 1 1 1 ... $ Make : Factor w/ 93 levels "Acura Integra",..: 1 2 4 3 5 6 7 9 8 10 ...

 

 

 

R의 aggregate() 함수로 차종(Type)별 도시 연비(MPG.city)와 고속도로 연비(MPG.highway)의 평균을 구해보겠습니다. 

 

> # aggregate > R_aggregate_mean <- aggregate(Cars93[,c(7,8)], + by = list(Car_Type = Cars93$Type), # list + FUN = mean, # function + na.rm = TRUE) > > R_aggregate_mean Car_Type MPG.city MPG.highway 1 Compact 22.68750 29.87500 2 Large 18.36364 26.72727 3 Midsize 19.54545 26.72727 4 Small 29.85714 35.47619 5 Sporty 21.78571 28.78571 6 Van 17.00000 21.88889

 

 

 

이번에는 install.packages()함수와 library()함수를 사용하여 sqldf Package 를 설치하고 호출한 후에, sqldf 패키지를 사용하여 위와 같이 차종(Type)별 도시 연비(MPG.city)와 고속도로 연비(MPG.highway)의 평균을 구해보겠습니다.

 

> install.packages("sqldf") Installing package into ‘C:/Users/user/Documents/R/win-library/3.2’ (as ‘lib’ is unspecified) trying URL 'http://cran.rstudio.com/bin/windows/contrib/3.2/sqldf_0.4-10.zip' Content type 'application/zip' length 71825 bytes (70 KB) downloaded 70 KB package ‘sqldf’ successfully unpacked and MD5 sums checked The downloaded binary packages are in C:\Users\user\AppData\Local\Temp\Rtmp4i7Dhq\downloaded_packages > library(sqldf) 필요한 패키지를 로딩중입니다: gsubfn 필요한 패키지를 로딩중입니다: proto 필요한 패키지를 로딩중입니다: RSQLite 필요한 패키지를 로딩중입니다: DBI Warning messages: 1: 패키지 ‘sqldf’는 R 버전 3.2.2에서 작성되었습니다 2: 패키지 ‘gsubfn’는 R 버전 3.2.2에서 작성되었습니다 3: 패키지 ‘RSQLite’는 R 버전 3.2.2에서 작성되었습니다 4: 패키지 ‘DBI’는 R 버전 3.2.2에서 작성되었습니다

 

> R_sqldf_1 <- sqldf(' + select "Type" as "Car_Type", + avg("MPG.city") as "mean_MPG.city", + avg("MPG.highway") as "mean_MPG.highway" + from Cars93 + group by Type + order by Type + ') > R_sqldf_1 Car_Type mean_MPG.city mean_MPG.highway 1 Compact 22.68750 29.87500 2 Large 18.36364 26.72727 3 Midsize 19.54545 26.72727 4 Small 29.85714 35.47619 5 Sporty 21.78571 28.78571 6 Van 17.00000 21.88889

 

 

R의 aggregate()함수로 만든 평균과 sqldf로 만든 평균 데이터 셋을 차종(Type) 을 key로 항 merge 한 후에 두 값들이 서로 같은지 한번 점검해보겠습니다.

 

> # 두개 데이터 셋 Merge, 동일 여부 check > Type_mean <- merge(R_aggregate_mean, R_sqldf_1, by = 'Car_Type') > Type_mean <- transform(Type_mean, + gap_MPG.city = MPG.city - mean_MPG.city, + gap_MPG.highway = MPG.highway - mean_MPG.highway) > > Type_mean Car_Type MPG.city MPG.highway mean_MPG.city mean_MPG.highway gap_MPG.city gap_MPG.highway 1 Compact 22.68750 29.87500 22.68750 29.87500 0 0 2 Large 18.36364 26.72727 18.36364 26.72727 0 0 3 Midsize 19.54545 26.72727 19.54545 26.72727 0 0 4 Small 29.85714 35.47619 29.85714 35.47619 0 0 5 Sporty 21.78571 28.78571 21.78571 28.78571 0 0 6 Van 17.00000 21.88889 17.00000 21.88889 0 0

 

얼핏 보면 R의 aggregate() 함수와 sqldf 가 서로 큰 차이가 없거나 혹은 aggregate()함수가 더 편하다고 느낄 수도 있겠습니다.  그런데, 아래의 경우처럼 다수의 함수들(count, sum, avg, variance, stdev, min, max 등)을 그룹 변수에 대해서 구분해서 집계를 할 경우에는, 그리고 SQL에 익숙한 사용자라면 sqldf 패키지를 사용하는게 편할 수 있을 것입니다 

 

> # SQL의 aggregation 함수 사용하기 > R_sqldf_2 <- sqldf(' + select "Type" as "Car_Type", + count("MPG.city") as "count_MPG.city", + sum("MPG.city") as "sum_MPG.city", + + avg("MPG.city") as "mean_MPG.city", + variance("MPG.city") as "variance_MPG.city", + stdev("MPG.city") as "stdev_MPG.city", + + min("MPG.city") as "min_MPG.city", + + max("MPG.city") as "max_MPG.city" + + from Cars93 + group by Type + order by Type desc + ') > > # count : 행의 개수 > # sum : 합계 > # avg : 평균 > # var : 분산 > # stddev : 표준편차 > # min : 최소값 > # max : 최대값 > # order by xx desc : 내림차순 정렬 > > R_sqldf_2 Car_Type count_MPG.city sum_MPG.city mean_MPG.city variance_MPG.city stdev_MPG.city min_MPG.city max_MPG.city 1 Van 9 153 17.00000 1.500000 1.224745 15 18 2 Sporty 14 305 21.78571 15.258242 3.906180 17 30 3 Small 21 627 29.85714 37.328571 6.109711 22 46 4 Midsize 22 430 19.54545 3.593074 1.895540 16 23 5 Large 11 202 18.36364 2.254545 1.501514 16 20 6 Compact 16 363 22.68750 3.695833 1.922455 20 26

 

 

변수명을 SQL 문 내에서 바로 부여하는 것도 편리합니다.  그리고 SQL에 능숙한 분석가라면 subquery를 사용해서 한방에 query를 다 돌려서 원하는 데이터셋을 만들어낼 수도 있겠습니다.  (단, sqldf는 속도는 희생될 수 있음)

 

 

그렇다고 sqldf가 데이터 집계를 하는데 있어 모든 통계량을 다 한번에 할 수 있는것은 아닙니다.  R에서는 아래 처럼 median, quantile 을 1줄만에 처리할 수 있는 반면에, 이것과 동일한 결과를 얻으려면 SQL로는 참 어렵습니다.

 

> # R로 median, quantile 지정해서 구하기 > R_aggregate_median <- aggregate(Cars93[,c(7,8)], by = list(Car_Type = Cars93$Type), FUN = median) > R_aggregate_median Car_Type MPG.city MPG.highway 1 Compact 23.0 30.0 2 Large 19.0 26.0 3 Midsize 19.0 26.5 4 Small 29.0 33.0 5 Sporty 22.5 28.5 6 Van 17.0 22.0 > > quantile_MPG.city <- quantile(Cars93[,c("MPG.city")], c(0, .01, .05, .1, .25, .5, .75, .9, .95, .99, 1)) > quantile_MPG.city 0% 1% 5% 10% 25% 50% 75% 90% 95% 99% 100% 15.00 15.00 16.60 17.00 18.00 21.00 25.00 29.00 31.40 42.32 46.00

 

sqldf 가 편하다고 했다가, 그냥 R 함수가 편하다가 했다가 오락가락 하는 것처럼 보일 수도 있겠는데요, 위의 예제를 보시고 데이터 전처리, 분석의 목적, 상황에 맞게 sqldf와 aggregate() 함수, R 함수를 선별해서 사용하시면 되겠습니다.

 

 

{dplyr} package의 summarise(n = n()), tally(), count() 함수를 사용한 집계 방법은 http://rfriend.tistory.com/240  포스팅을 참고하세요.

 

 

이번 포스팅이 도움이 되었다면 아래의 '공감 ~♡' 단추를 꾸욱 눌러주세요.^^