[解決済み] Rでdata.frameをマージ/ジョインする最速の方法は何ですか？

質問

例えば（最も代表的な例かどうかは分かりませんが）。

N <- 1e6
d1 <- data.frame(x=sample(N,N), y1=rnorm(N))
d2 <- data.frame(x=sample(N,N), y2=rnorm(N))

今のところ、こんな感じです。

d <- merge(d1,d2)
# 7.6 sec

library(plyr)
d <- join(d1,d2)
# 2.9 sec

library(data.table)
dt1 <- data.table(d1, key="x")
dt2 <- data.table(d2, key="x")
d <- data.frame( dt1[dt2,list(x,y1,y2=dt2$y2)] )
# 4.9 sec

library(sqldf)
sqldf()
sqldf("create index ix1 on d1(x)")
sqldf("create index ix2 on d2(x)")
d <- sqldf("select * from d1 inner join d2 on d1.x=d2.x")
sqldf()
# 17.4 sec

どのように解決するのですか？

最初のデータフレームの各キー値に対して、2番目のデータフレームに一意のキーがある場合、マッチアプローチは機能します。 2番目のデータフレームに重複がある場合、マッチとマージのアプローチは同じではありません。 もちろん、マッチの方がより高速に処理されます。特に、重複するキーを探すことはありません。(コードの後に続く)

DF1 = data.frame(a = c(1, 1, 2, 2), b = 1:4)
DF2 = data.frame(b = c(1, 2, 3, 3, 4), c = letters[1:5])
merge(DF1, DF2)
    b a c
  1 1 1 a
  2 2 1 b
  3 3 2 c
  4 3 2 d
  5 4 2 e
DF1$c = DF2$c[match(DF1$b, DF2$b)]
DF1$c
[1] a b c e
Levels: a b c d e

> DF1
  a b c
1 1 1 a
2 1 2 b
3 2 3 c
4 2 4 e

質問で投稿されたsqldfのコードでは、2つのテーブルにインデックスが使用されているように見えますが、実際にはsql selectが実行される前に上書きされたテーブルに置かれており、それが部分的には遅い理由の理由です。 sqldfの考え方は、sqliteのテーブルではなく、Rセッションのデータフレームがデータベースを構成するというものです。 したがって、コードが未修飾のテーブル名を参照するたびに、sqliteのメインデータベースではなく、Rワークスペースを探します。 したがって、示されたselect文はd1とd2をワークスペースからsqliteの主データベースに読み込み、インデックスによってそこにあったものを破壊しています。 その結果、インデックスを使用しない結合が行われます。 もしsqliteの主データベースにあるd1とd2のバージョンを使用したい場合、それらをd1とd2としてではなく、main.d1とmain.d2として参照しなければならないでしょう。 また、可能な限り高速に実行しようとする場合、単純な結合では両方のテーブルのインデックスを使用できないことに注意して、インデックスの片方を作成する時間を節約することができます。 以下のコードで、これらの点を説明します。

正確な計算を行うことで、どのパッケージが最も速いかについて大きな違いがあることに注目する価値があります。 たとえば、以下ではマージとアグリゲートを行います。 この 2 つの結果はほぼ逆になっていることがわかります。 最初の例では、速いものから順に data.table, plyr, merge, sqldf となり、2番目の例では sqldf, aggregate, data.table, plyr となり、最初の例とはほぼ逆の結果になっています。 最初の例ではsqldfはdata.tableより3倍遅く、2番目の例ではplyrより200倍速く、data.tableより100倍速くなります。 以下に、入力コード、マージの出力タイミング、アグリゲートの出力タイミングを示します。 sqldfはデータベースをベースにしているので、Rが扱えるよりも大きなオブジェクトを扱うことができます（sqldfの引数dbnameを使用した場合）が、他のアプローチはメインメモリでの処理に限定されていることも注目すべき点です。 また、今回はsqldfをsqliteで説明しましたが、H2やPostgreSQLのデータベースもサポートしています。

library(plyr)
library(data.table)
library(sqldf)

set.seed(123)
N <- 1e5
d1 <- data.frame(x=sample(N,N), y1=rnorm(N))
d2 <- data.frame(x=sample(N,N), y2=rnorm(N))

g1 <- sample(1:1000, N, replace = TRUE)
g2<- sample(1:1000, N, replace = TRUE)
d <- data.frame(d1, g1, g2)

library(rbenchmark)

benchmark(replications = 1, order = "elapsed",
   merge = merge(d1, d2),
   plyr = join(d1, d2),
   data.table = { 
      dt1 <- data.table(d1, key = "x")
      dt2 <- data.table(d2, key = "x")
      data.frame( dt1[dt2,list(x,y1,y2=dt2$y2)] )
      },
   sqldf = sqldf(c("create index ix1 on d1(x)",
      "select * from main.d1 join d2 using(x)"))
)

set.seed(123)
N <- 1e5
g1 <- sample(1:1000, N, replace = TRUE)
g2<- sample(1:1000, N, replace = TRUE)
d <- data.frame(x=sample(N,N), y=rnorm(N), g1, g2)

benchmark(replications = 1, order = "elapsed",
   aggregate = aggregate(d[c("x", "y")], d[c("g1", "g2")], mean), 
   data.table = {
      dt <- data.table(d, key = "g1,g2")
      dt[, colMeans(cbind(x, y)), by = "g1,g2"]
   },
   plyr = ddply(d, .(g1, g2), summarise, avx = mean(x), avy=mean(y)),
   sqldf = sqldf(c("create index ix on d(g1, g2)",
      "select g1, g2, avg(x), avg(y) from main.d group by g1, g2"))
)

マージ計算を比較する2つのベンチマーク呼び出しの出力は、以下の通りです。

Joining by: x
        test replications elapsed relative user.self sys.self user.child sys.child
3 data.table            1    0.34 1.000000      0.31     0.01         NA        NA
2       plyr            1    0.44 1.294118      0.39     0.02         NA        NA
1      merge            1    1.17 3.441176      1.10     0.04         NA        NA
4      sqldf            1    3.34 9.823529      3.24     0.04         NA        NA

集計計算を比較するベンチマーク呼び出しの出力は、以下の通りです。

        test replications elapsed  relative user.self sys.self user.child sys.child
4      sqldf            1    2.81  1.000000      2.73     0.02         NA        NA
1  aggregate            1   14.89  5.298932     14.89     0.00         NA        NA
2 data.table            1  132.46 47.138790    131.70     0.08         NA        NA
3       plyr            1  212.69 75.690391    211.57     0.56         NA        NA