L'approche de mise en correspondance fonctionne lorsqu'il existe une clé unique dans le second cadre de données pour chaque valeur de clé dans le premier. S'il y a des doublons dans le deuxième cadre de données, les approches de correspondance et de fusion ne sont pas les mêmes. La correspondance est, bien sûr, plus rapide car elle ne fait pas autant d'efforts. En particulier, elle ne recherche jamais les clés en double. (suite après le code)
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
Dans le code sqldf qui a été posté dans la question, il pourrait sembler que des index ont été utilisés sur les deux tables mais, en fait, ils sont placés sur des tables qui ont été écrasées avant que le sql select ne soit exécuté et cela, en partie, explique pourquoi il est si lent. L'idée de sqldf est que les cadres de données dans votre session R constituent la base de données, pas les tables dans sqlite. Ainsi, chaque fois que le code se réfère à un nom de table non qualifié, il le cherchera dans votre espace de travail R, et non dans la base de données principale de sqlite. Ainsi, l'instruction select qui a été montrée lit d1 et d2 de l'espace de travail dans la base de données principale de sqlite en détruisant ceux qui étaient là avec les index. En conséquence, il fait une jointure sans index. Si vous vouliez utiliser les versions de d1 et d2 qui étaient dans la base de données principale de sqlite, vous devriez vous y référer comme main.d1 et main.d2 et non comme d1 et d2. De plus, si vous essayez de faire en sorte que l'opération soit aussi rapide que possible, notez qu'une simple jointure ne peut pas utiliser les index sur les deux tables, vous pouvez donc gagner du temps en créant un des index. Dans le code ci-dessous, nous illustrons ces points.
Il est intéressant de noter que le calcul précis peut faire une énorme différence sur le paquet le plus rapide. Par exemple, nous faisons une fusion et un agrégat ci-dessous. Nous voyons que les résultats sont presque inversés pour les deux. Dans le premier exemple, du plus rapide au plus lent, nous obtenons : data.table, plyr, merge et sqldf alors que dans le second exemple, sqldf, aggregate, data.table et plyr -- presque l'inverse du premier. Dans le premier exemple, sqldf est 3x plus lent que data.table et dans le second, il est 200x plus rapide que plyr et 100 fois plus rapide que data.table. Nous montrons ci-dessous le code d'entrée, les temps de sortie pour la fusion et les temps de sortie pour l'agrégat. Il est également intéressant de noter que sqldf est basé sur une base de données et peut donc gérer des objets plus grands que ceux que R peut gérer (si vous utilisez l'argument dbname de sqldf) alors que les autres approches sont limitées au traitement en mémoire principale. Nous avons également illustré sqldf avec sqlite mais il supporte également les bases de données H2 et 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"))
)
Les résultats des deux appels de référence comparant les calculs de fusion sont les suivants :
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
Les résultats de l'appel de référence comparant les calculs agrégés sont les suivants :
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
