R 與 SQLite

有時候我們需要使用資料庫來管理資料，同時以 R 進行複雜的統計分析，這種狀況我們就可以將資料放進 SQL 資料庫之後，再從 R 中存取 SQL 資料庫的資料。

以 SQLite 資料庫來說，我們可以使用 R 的 RSQLite 套件來連接 SQLite 資料庫，使用前請先安裝：

install.packages("RSQLite")

安裝好之後，載入 RSQLite 套件：

library(RSQLite)

一開始必須先建立一個 SQLite 資料庫的連線：

# 建立 SQLite 資料庫連線
delay.db <- dbConnect(RSQLite::SQLite(), dbname = "AirlineDelay.sqlite3")

接著即可以 SQL 語法來查詢資料庫：

# 以 SQL 查詢資料庫
sql <- "SELECT Year, COUNT(*) FROM AirlineDelay GROUP BY Year;"
count <- dbGetQuery(delay.db, sql)
count
   Year COUNT(*)
1  1987  1311826
2  1988  5202096
3  1989  5041200
4  1990  5270893
5  1991  5076925
6  1992  5092157
7  1993  5070501
8  1994  5180048
9  1995  5327435
10 1996  5351983
11 1997  5411843
12 1998  5384721
13 1999  5527884
14 2000  5683047
15 2001  5967780
16 2002  5271359
17 2003  6488540
18 2004  7129270
19 2005  7140596
20 2006  7141922
21 2007  7453215
22 2008  7009728

資料庫使用完之後，記得要將連線關閉：

# 關閉 RSQLite 資料庫連線
dbDisconnect(delay.db)

透過 RSQLite 套件，我們可以使用任何的 SQL 語法從資料庫取得想要的資料，再使用 R 做進一步的統計分析，這樣會比只使用 R 來管理資料更方便。

R 少量記憶體與巨量資料

透過資料庫來處理巨量資料是一個不錯的方式，但前提是必須熟悉 SQL 資料庫指令的語法，若對於 SQL 指令的使用不熟稔的話，使用上也不是很方便，這種狀況下就可以考慮改用 bigmemory 套件。

R 的 bigmemory 套件可以讓記憶體不夠大的電腦也可以處理巨量的資料，而其資料操作的方式就跟 R 基本的矩陣一樣，所以對於熟悉 R 語言的人可以立即上手使用。

由於 bigmemory 套件在處理資料時，必須將所有的資料轉成相同的類型，所以我們這裡先把文字的資料捨棄，只留下數值的欄位，再將所有的資料放進一個 CSV 檔案中。在 Linux 的 shell 中執行以下指令：

# 將所有資料放進一個 CSV 檔案中，並只留下數值的欄位
FIELDS=1,2,3,4,5,6,7,8,12,13,14,15,16,19,20,21,22,24,25,26,27,28,29
cut -d, -f $FIELDS 1987.csv > AirlineDataNum.csv
for year in {1988..2008}
do
  tail -n+2 $year.csv | cut -d, -f $FIELDS >> AirlineDataNum.csv
done

另一種做法是將那些文字的欄位改為數值的代號，這樣就可以直接透過 bigmemory 來處理。

接著安裝 bigmemory 套件，並載入之：

install.packages("bigmemory")
library(bigmemory)

直接將所有的航班延誤資料載入至 R 中，載入後會得到一個 big.matrix 物件：

# 載入所有航班延誤資料
airline.all <- read.big.matrix("AirlineDataNum.csv",
  header = TRUE,
  backingfile = "airline.bin",
  descriptorfile = "airline.desc",
  type = "integer")

這裡的 backingfile 參數是指定後端快取檔案的名稱，而 descriptorfile 參數是指定 descriptor 檔案的名稱（可用來快速建立 big.matrix 物件），type 參數是指定資料類型。

載入後，查看矩陣的大小：

# 查看矩陣大小
dim(airline.all)
[1] 123534969        24

這個 big.matrix 的操作方式就跟一般的矩陣相同，我們可用索引查看部分資料：

# 查看部分資料
airline.all[1:5,1:7]
     Year Month DayofMonth DayOfWeek DepTime CRSDepTime ArrTime
[1,] 1987    10         14         3     741        730     912
[2,] 1987    10         15         4     729        730     903
[3,] 1987    10         17         6     741        730     918
[4,] 1987    10         18         7     729        730     847
[5,] 1987    10         19         1     749        730     922

計算每一年的資料筆數：

# 計算每一年的資料筆數
for(year in 1987:2008) {
  cat(year, "年:", sum(airline.all[,"Year"] == year), "\n")
}
1987 年: 1311826
1988 年: 5202096
1989 年: 5041200
1990 年: 5270893
1991 年: 5076925
1992 年: 5092157
1993 年: 5070501
1994 年: 5180048
1995 年: 5327435
1996 年: 5351983
1997 年: 5411843
1998 年: 5384721
1999 年: 5527884
2000 年: 5683047
2001 年: 5967780
2002 年: 5271359
2003 年: 6488540
2004 年: 7129270
2005 年: 7140596
2006 年: 7141922
2007 年: 7453215
2008 年: 7009728

計算一週當中每一天的資料總筆數：

# 計算一週當中每一天的資料總筆數
for(day in 1:7) {
  cat(day, ":", sum(airline.all[,"DayOfWeek"] == day), "\n")
}
1 : 18136111
2 : 18061938
3 : 18103222
4 : 18083800
5 : 18091338
6 : 15915382
7 : 17143178

建立一個 big.matrix 物件需要等待比較久的時間，在建立好 big.matrix 物件之後，如果後續想要再次建立這個 big.matrix 物件時，就可以使用先前建立好的 descriptor 檔案，這樣就可以不用再等那麼久：

# 使用 descriptor 檔案建立 big.matrix 物件
airline.all.2 <- attach.big.matrix("airline.desc")

R 平行運算

上面我們使用迴圈的方式來計算各種統計數值，而各個統計數值之間其實是獨立的（例如週一的資料筆數不會跟週二的資料筆數有關），所以我們可以把迴圈內的計算分散至多個 CPU 核心，加速計算。

R 的 parallel 套件是一個內建的平行計算套件，我們可以用它來加速上面的迴圈計算。

若要將程式平行化，第一步就是要把程式以 lapply 的方式改寫，以計算一週當中每一天的資料總筆數為例，可以改成這樣：

# 計算一週當中每一天的資料總筆數
lapply(1:7, function(day) sum(airline.all[,"DayOfWeek"] == day))
[[1]]
[1] 18136111

[[2]]
[1] 18061938

[[3]]
[1] 18103222

[[4]]
[1] 18083800

[[5]]
[1] 18091338

[[6]]
[1] 15915382

[[7]]
[1] 17143178

也可以使用 sapply，以這個例子來說，這樣會比較方便：

# 計算一週當中每一天的資料總筆數
sapply(1:7, function(day) sum(airline.all[,"DayOfWeek"] == day))
[1] 18136111 18061938 18103222 18083800 18091338 15915382 17143178

確認結果正確之後，接著載入 parallel 套件：

library(parallel)

接著使用 makeForkCluster 建立 cluster，建立時要指定欲使用的 CPU 核心數，這裡我們用 detectCores 自動偵測系統的 CPU 核心數，使用所有的核心：

# 偵測 CPU 核心數
cores <- detectCores()

# 建立 cluster
cl <- makeForkCluster(cores)

接著將上面寫好的 lapply 或 sapply 改為 parLapply 或 parSapply，即可將工作分散至多個 CPU 核心進行平行計算：

# 計算一週當中每一天的資料總筆數
parSapply(cl, 1:7, function(day) sum(airline.all[,"DayOfWeek"] == day))
[1] 18136111 18061938 18103222 18083800 18091338 15915382 17143178

正常來說，計算的結果會相同，但是速度卻快很多。

計算完畢之後，記得要關閉 cluster：

# 關閉 cluster
stopCluster(cl)

R 巨量資料回歸分析

若在資料量很大的情況下想做迴歸分析，沒有辦法以普通的 lm 來處理，必須改用 biglm 套件，它的原理是每次只拿部分的資料作計算，逐步更新模型參數。

如果想要拿 big.matrix 的資料放進 biglm 做迴歸，可以用 biganalytics 這個包裝套件，它可以簡化資料轉換的流程，使用起來更簡單。

首先安裝 biganalytics 套件：

install.packages("biganalytics")
library(biganalytics)

接著就可以使用 biglm.big.matrix 進行迴歸分析了：

# 迴歸分析
blm <- biglm.big.matrix(ArrDelay ~ Distance, data = airline.all)

最後輸出配適的迴歸模型：

# 輸出結果
summary(blm)
Large data regression model: biglm(formula = formula, data = data, ...)
Sample size =  120748239
              Coef   (95%    CI)     SE p
(Intercept) 6.9707 6.9616 6.9798 0.0045 0
Distance    0.0001 0.0001 0.0001 0.0000 0

練習題

  1. 請使用 R 的 parallel 平行計算套件，解決以下問題：
    • 哪一年的班機誤點情況最嚴重？
    • 一週當中，星期幾的班機誤點情況最嚴重？
  2. 以迴歸分析檢查 ArrDelay 與 DepDelay 兩者之間是否有關係？

繼續閱讀： 12