R と SQL を対応付けてみた ～data.table 編～

()

data.table 便利ですよね。data.table::fread が read.table に比べて格段に速いので、データの読み込みのためだけに data.table を使うのもありかと思います。
ただ、data.table:::unique.data.table のように data.frame 感覚で関数を使うと意図しない結果になることもあるので、主要な操作を SQL と対応付けてまとめてみました。

data.frame の主要な操作については「RとSQLを対応付けてみた」や「Tsukuba.R #9で『Rデータフレーム自由自在』を発表してきました」をご覧ください。

準備

前回同様、サンプルデータとして次のものを使用します。

library(data.table)

set.seed(1000)
product <- data.table(id = 101:110, name = sample(LETTERS, 10), price = sample(5:20 * 100, 10))
n <- 100
makeOrder <- function(term, n) {
    data.table(order_id = sort(sample(seq(1001, len = n * 2), n)),
        date = sort(sample(paste("2011-05-", term, sep = ""), n, replace = TRUE)),
        customer_id = sample(10001:10100, n, replace = TRUE),
        product_id = sample(product$id, n, replace = TRUE),
        number = sample(1:5, n, replace = TRUE)
    )
}

order1 <- makeOrder(21:24, n)
order2 <- makeOrder(25:28, n)

受注に関するデータと、商品に関するデータといった感じです。
order1, order2 は同じ構造のデータで、order_id（伝票番号みたいなもの）、date（受注日）、customer_id（顧客ID）、product_id（製品ID）、number（購入個数）から成り、productはid（製品ID）、name（製品名）、price（価格）から成ります。

> head(order1)
   order_id       date customer_id product_id number
1:     1002 2011-05-21       10078        106      3
2:     1003 2011-05-21       10038        109      4
3:     1004 2011-05-21       10039        109      4
4:     1006 2011-05-21       10036        101      1
5:     1007 2011-05-21       10042        110      3
6:     1009 2011-05-21       10060        110      1
> head(order2)
   order_id       date customer_id product_id number
1:     1002 2011-05-25       10036        106      3
2:     1003 2011-05-25       10031        106      4
3:     1004 2011-05-25       10039        106      5
4:     1005 2011-05-25       10024        108      5
5:     1006 2011-05-25       10025        101      4
6:     1007 2011-05-25       10099        109      1
> head(product)
    id name price
1: 101    I  1000
2: 102    S  1600
3: 103    C   900
4: 104    P  1900
5: 105    L  1400
6: 106    B   500

基本操作

列の抽出

SQL

SELECT order_id, customer_id FROM order1

data.table

order1[, .(order_id, customer_id)]

次のような書き方も可能です

# .() と list() は同義
order1[, list(order_id, customer_id)]
# data.frame 風
order1[, c("order_id", "customer_id"), with = FALSE]

LIMIT

SQL

SELECT * FROM order1 LIMIT 5

data.table

head(order1, n = 5)

次のような書き方も可能です

order1[1:5]
# data.frame 風
order1[1:5, ]

SQL

SELECT * FROM order1 LIMIT 2 OFFSET 5

data.table

order1[seq(3, len = 5)]
# data.frame 風
order1[seq(3, len = 5), ]

DISTINCT

SQL

SELECT DISTINCT customer_id FROM order1

data.table

unique(order1[, c(customer_id)])

次のような書き方も可能です。上記の書き方だとベクトルが返りますが、こちらは data.table が返ります。

unique(order1[, .(customer_id)])

SQL

SELECT DISTINCT date, product_id FROM order1

data.table

unique(order1[, .(date, product_id)], by = c("date", "product_id"))

場合によっては by を指定しなくても大丈夫ですが、もし次のように date に key が設定されていると意図しない結果になるので、面倒でも必ず by を指定することをオススメします。

> setkeyv(order1, date)
> key(order1)
[1] "date"
> unique(order1[, .(date, product_id)])
         date product_id
1: 2011-05-21        101
2: 2011-05-22        101
3: 2011-05-23        102
4: 2011-05-24        101
> # 次と同義
> unique(order1[, .(date, product_id)], by = key(order1))
         date product_id
1: 2011-05-21        101
2: 2011-05-22        101
3: 2011-05-23        102
4: 2011-05-24        101
> # 所望の結果
> unique(order1[, .(date, product_id)], by = c("date", "product_id"))
          date product_id
 1: 2011-05-21        101
 2: 2011-05-21        102
 3: 2011-05-21        104
 4: 2011-05-21        106
 5: 2011-05-21        107
 (snip)
33: 2011-05-24        108
34: 2011-05-24        109
35: 2011-05-24        110
          date product_id

COUNT

SQL

SELECT COUNT(*) FROM order1

data.table

nrow(order1)

WHERE

1つの条件

SQL

SELECT * FROM order1 WHERE customer_id = 10038

data.table

order1[customer_id == 10038]  # order1[customer_id == 10038, ] と同義

次のような書き方も可能です

order1[order1$customer_id == 10038]
subset(order1, customer_id == 10038)

AND 条件

SQL

SELECT * FROM order1 WHERE customer_id = 10038 AND date = '2011-05-21'

data.table

order1[customer_id == 10038 & date == "2011-05-21"]

OR 条件

SQL

SELECT * FROM order1 WHERE customer_id = 10038 OR customer_id = 10050

data.table

order1[customer_id == 10038 | customer_id == 10050]

IN

SQL

SELECT * FROM order1 WHERE customer_id IN (10038, 10050, 10079)

data.table

order1[customer_id %in% c(10038, 10050, 10079)]

ORDER BY

1 つのフィールドでソート

SQL

SELECT * FROM order1 ORDER BY customer_id

data.table

order1[order(customer_id)]

2 つのフィールドでソート

SQL

SELECT * FROM order1 ORDER BY customer_id, product_id DESC

data.table

order1[order(customer_id, -product_id)]

JOIN

INNER JOIN

SQL

SELECT * FROM order1 INNER JOIN order2 ON order1.order_id = order2.order_id

data.table

merge(order1, order2, by = "order_id")

※ by で指定したフィールドが key として設定されるので注意

LEFT JOIN

SQL

SELECT * FROM order1 LEFT JOIN order2 ON order1.order_id = order2.order_id

data.table

merge(order1, order2, by = "order_id", all.x = TRUE)

RIGHT JOIN

SQL

SELECT * FROM order1 RIGHT JOIN order2 ON order1.order_id = order2.order_id

data.table

merge(order1, order2, by = "order_id", all.y = TRUE)

FULL JOIN

SELECT * FROM order1 FULL JOIN order2 ON order1.order_id = order2.order_id

R

merge(order1, order2, by = "order_id", all = TRUE)

GROUP BY

1 つのフィールドでグループ化して COUNT

SQL

SELECT customer_id, COUNT(*) FROM order1 GROUP BY customer_id

data.table

order1[, .N, by = customer_id]  # order1[, .(.N), by = customer_id] と同義

.N はそのグループのレコード数を意味する特殊なシンボルで、これによって算出される値の列は N が割り当てられます。

> head(order1[, .N, by = customer_id], 2)
   customer_id N
1:       10036 2
2:       10022 1

2 つのフィールドでグループ化して COUNT

SQL

SELECT customer_id, date, COUNT(*) FROM order1 GROUP BY customer_id, date

data.table

order1[, .N, by = .(customer_id, date)]

2 つのフィールドでグループ化して COUNT してソート

SQL

SELECT
  customer_id, date, COUNT(*)
FROM
  order1
GROUP BY
  customer_id, date
ORDER BY
  customer_id, date DESC

data.table

order1[, .N, by = .(customer_id, date)][order(customer_id, -date)]
# 次と同義
# tmp <- order1[, .N, by = .(customer_id, date)]
# tmp[order(customer_id, -date)]

1 つのフィールドでグループ化して COUNT してフィルタリング

SQL

SELECT
  customer_id, COUNT(*)
FROM
  order1
GROUP BY
  customer_id
HAVING
  COUNT(*) >= 3

data.table

order1[, .N, by = customer_id][N >= 3]
# 次と同義
# tmp <- order1[, .N, by = customer_id]
# tmp[N >= 3]

1 つのフィールドでグループ化して平均

SQL

SELECT customer_id, AVG(number) FROM order1 GROUP BY customer_id

data.table

order1[, .(mean(number)), by = customer_id]

.() の中に集約関数と対象のフィールド名を与えることで集約処理を行えます。
処理結果に名前を付けることもできます。

> head(order1[, .(mean(number)), by = customer_id], 2)
   customer_id V1
1:       10036  2
2:       10022  2
> head(order1[, .(avg = mean(number)), by = customer_id], 2)
   customer_id avg
1:       10036   2
2:       10022   2

2 つのフィールドでグループ化して平均

SQL

SELECT customer_id, AVG(number) FROM order1 GROUP BY customer_id, date

data.table

order1[, .(mean(number)), by = .(customer_id, date)]

1 つのフィールドでグループ化して MIN, MAX

SQL

SELECT product_id, MIN(number), MAX(number) FROM order1 GROUP BY product_id

data.table

order1[, .(min(number), max(number)), by = product_id]

以上です。data.table に慣れると subset, table, aggregate などの関数がなくてもサクッとデータ操作ができそうで便利ですね！

参考