unir tablas datos data agrupar performance r join merge data.table

performance - tablas - ¿Cuál es la forma más rápida de fusionar/unir data.frames en R?



tablas en r (5)

Por ejemplo (aunque no estoy seguro si el ejemplo más representativo):

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

Esto es lo que tengo hasta ahora:

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

Al usar la función de combinación y sus parámetros opcionales:

Unión interna: fusionar (df1, df2) funcionará para estos ejemplos porque R une automáticamente los marcos por nombres comunes de variables, pero lo más probable es que desee especificar merge (df1, df2, by = "CustomerId") para asegurarse de que hacían coincidir solo en los campos que deseaba. También puede usar los parámetros by.x y by.y si las variables coincidentes tienen diferentes nombres en los diferentes marcos de datos.

Outer join: merge(x = df1, y = df2, by = "CustomerId", all = TRUE) Left outer: merge(x = df1, y = df2, by = "CustomerId", all.x = TRUE) Right outer: merge(x = df1, y = df2, by = "CustomerId", all.y = TRUE) Cross join: merge(x = df1, y = df2, by = NULL)

El enfoque de coincidencia funciona cuando hay una clave única en el segundo marco de datos para cada valor clave en el primero. Si hay duplicados en el segundo marco de datos, los enfoques de coincidencia y fusión no son lo mismo. El partido es, por supuesto, más rápido ya que no está haciendo tanto. En particular, nunca busca claves duplicadas. (continua después del código)

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

En el código sqldf que se publicó en la pregunta, podría parecer que los índices se usaron en las dos tablas pero, de hecho, se colocan en tablas que se sobrescribieron antes de que se ejecute el SQL selectivo y eso, en parte, explica por qué es muy lento La idea de sqldf es que los marcos de datos en su sesión R constituyen la base de datos, no las tablas en sqlite. Por lo tanto, cada vez que el código se refiere a un nombre de tabla no calificado buscará en su espacio de trabajo R, no en la base de datos principal de sqlite. Por lo tanto, la declaración de selección que se muestra lee d1 y d2 del espacio de trabajo en la base de datos principal de sqlite, marcando los que estaban allí con los índices. Como resultado, se une sin índices. Si quisiera hacer uso de las versiones de d1 y d2 que estaban en la base de datos principal de sqlite, debería referirse a ellas como main.d1 y main.d2 y no como d1 y d2. Además, si intenta hacer que se ejecute lo más rápido posible, tenga en cuenta que una unión simple no puede hacer uso de los índices en ambas tablas para que pueda guardar el tiempo de crear uno de los índices. En el siguiente código, ilustramos estos puntos.

Vale la pena observar que el cálculo preciso puede marcar una gran diferencia en cuanto a qué paquete es el más rápido. Por ejemplo, hacemos una combinación y un agregado a continuación. Vemos que los resultados casi se invierten para los dos. En el primer ejemplo, del más rápido al más lento, obtenemos: data.table, plyr, merge y sqldf, mientras que en el segundo ejemplo, sqldf, aggregate, data.table y plyr, casi al revés del primero. En el primer ejemplo, sqldf es 3 veces más lento que data.table y en el segundo es 200 veces más rápido que plyr y 100 veces más rápido que data.table. A continuación mostramos el código de entrada, los tiempos de salida para la fusión y los tiempos de salida para el agregado. También vale la pena señalar que sqldf se basa en una base de datos y, por lo tanto, puede manejar objetos mayores de los que R puede manejar (si usa el argumento dbname de sqldf) mientras que los otros enfoques están limitados al procesamiento en la memoria principal. También hemos ilustrado sqldf con sqlite pero también es compatible con las bases de datos H2 y 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")) )

Los resultados de las dos llamadas de referencia que comparan los cálculos de fusión son:

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

El resultado de la llamada de referencia que compara los cálculos agregados es:

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

Los 132 segundos informados en los resultados de Gabor para data.table son en realidad cronometrar las funciones de base colMeans y cbind (la asignación y copia de memoria inducida por el uso de esas funciones). También hay formas buenas y malas de usar data.table .

benchmark(replications = 1, order = "elapsed", aggregate = aggregate(d[c("x", "y")], d[c("g1", "g2")], mean), data.tableBad = { dt <- data.table(d, key = "g1,g2") dt[, colMeans(cbind(x, y)), by = "g1,g2"] }, data.tableGood = { dt <- data.table(d, key = "g1,g2") dt[, list(mean(x),mean(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")) ) test replications elapsed relative user.self sys.self 3 data.tableGood 1 0.15 1.000 0.16 0.00 5 sqldf 1 1.01 6.733 1.01 0.00 2 data.tableBad 1 1.63 10.867 1.61 0.01 1 aggregate 1 6.40 42.667 6.38 0.00 4 plyr 1 317.97 2119.800 265.12 51.05 packageVersion("data.table") # [1] ‘1.8.2’ packageVersion("plyr") # [1] ‘1.7.1’ packageVersion("sqldf") # [1] ‘0.4.6.4’ R.version.string # R version 2.15.1 (2012-06-22)

Tenga en cuenta que no conozco bien a Plyr, por favor consulte con Hadley antes de confiar en los horarios de plyr aquí. También tenga en cuenta que data.table incluye el tiempo para convertir a data.table y establecer la clave, para la tarifa.

Esta respuesta se ha actualizado desde que se respondió originalmente en diciembre de 2010. Los resultados de referencia anteriores están a continuación. Por favor, consulte el historial de revisión de esta respuesta para ver qué cambió.

test replications elapsed relative user.self sys.self 4 data.tableBest 1 0.532 1.000000 0.488 0.020 7 sqldf 1 2.059 3.870301 2.041 0.008 3 data.tableBetter 1 9.580 18.007519 9.213 0.220 1 aggregate 1 14.864 27.939850 13.937 0.316 2 data.tableWorst 1 152.046 285.800752 150.173 0.556 6 plyrwithInternal 1 198.283 372.712406 189.391 7.665 5 plyr 1 225.726 424.296992 208.013 8.004

Para una tarea simple (valores únicos en ambos lados de la combinación) uso match :

system.time({ d <- d1 d$y2 <- d2$y2[match(d1$x,d2$x)] })

Es mucho más rápido que fusionar (en mi máquina 0.13s a 3.37s).

Mis horarios:

  • merge : 3.32s
  • plyr : 0.84s
  • match : 0.12s

Pensé que sería interesante publicar un punto de referencia con dplyr en la mezcla: (tenían muchas cosas en ejecución)

test replications elapsed relative user.self sys.self 5 dplyr 1 0.25 1.00 0.25 0.00 3 data.tableGood 1 0.28 1.12 0.27 0.00 6 sqldf 1 0.58 2.32 0.57 0.00 2 data.tableBad 1 1.10 4.40 1.09 0.01 1 aggregate 1 4.79 19.16 4.73 0.02 4 plyr 1 186.70 746.80 152.11 30.27 packageVersion("data.table") [1] ‘1.8.10’ packageVersion("plyr") [1] ‘1.8’ packageVersion("sqldf") [1] ‘0.4.7’ packageVersion("dplyr") [1] ‘0.1.2’ R.version.string [1] "R version 3.0.2 (2013-09-25)"

Recien agregado:

dplyr = summarise(dt_dt, avx = mean(x), avy = mean(y))

y configure los datos para dplyr con una tabla de datos:

dt <- tbl_dt(d) dt_dt <- group_by(dt, g1, g2)

Actualizado: Eliminé data.tableBad y plyr y nada más que RStudio abierto (i7, 16GB ram).

Con data.table 1.9 y dplyr con marco de datos:

test replications elapsed relative user.self sys.self 2 data.tableGood 1 0.02 1.0 0.02 0.00 3 dplyr 1 0.04 2.0 0.04 0.00 4 sqldf 1 0.46 23.0 0.46 0.00 1 aggregate 1 6.11 305.5 6.10 0.02

Con data.table 1.9 y dplyr con tabla de datos:

test replications elapsed relative user.self sys.self 2 data.tableGood 1 0.02 1 0.02 0.00 3 dplyr 1 0.02 1 0.02 0.00 4 sqldf 1 0.44 22 0.43 0.02 1 aggregate 1 6.14 307 6.10 0.01 packageVersion("data.table") [1] ''1.9.0'' packageVersion("dplyr") [1] ''0.1.2''

Para coherencia aquí está el original con todo y data.table 1.9 y dplyr usando una tabla de datos:

test replications elapsed relative user.self sys.self 5 dplyr 1 0.01 1 0.02 0.00 3 data.tableGood 1 0.02 2 0.01 0.00 6 sqldf 1 0.47 47 0.46 0.00 1 aggregate 1 6.16 616 6.16 0.00 2 data.tableBad 1 15.45 1545 15.38 0.01 4 plyr 1 110.23 11023 90.46 19.52

Creo que esta información es demasiado pequeña para los nuevos data.table y dplyr :)

Conjunto de datos más grande:

N <- 1e8 g1 <- sample(1:50000, N, replace = TRUE) g2<- sample(1:50000, N, replace = TRUE) d <- data.frame(x=sample(N,N), y=rnorm(N), g1, g2)

Tomó alrededor de 10-13 GB de RAM solo para contener los datos antes de ejecutar el punto de referencia.

Resultados:

test replications elapsed relative user.self sys.self 1 dplyr 1 14.88 1 6.24 7.52 2 data.tableGood 1 28.41 1 18.55 9.4

Intentó un mil millones pero explotó ram. 32GB lo manejará sin problema.

[Editar por Arun] (dotcomken, ¿podría ejecutar este código y pegar los resultados de su evaluación comparativa? Gracias).

require(data.table) require(dplyr) require(rbenchmark) N <- 1e8 g1 <- sample(1:50000, N, replace = TRUE) g2 <- sample(1:50000, N, replace = TRUE) d <- data.frame(x=sample(N,N), y=rnorm(N), g1, g2) benchmark(replications = 5, order = "elapsed", data.table = { dt <- as.data.table(d) dt[, lapply(.SD, mean), by = "g1,g2"] }, dplyr_DF = d %.% group_by(g1, g2) %.% summarise(avx = mean(x), avy=mean(y)) )

Según la solicitud de Arun aquí, el resultado de lo que me proporcionó para ejecutar:

test replications elapsed relative user.self sys.self 1 data.table 5 15.35 1.00 13.77 1.57 2 dplyr_DF 5 137.84 8.98 136.31 1.44

Perdón por la confusión, la noche me llegó.

El uso de dplyr con el marco de datos parece ser la forma menos eficiente de procesar resúmenes. ¿Se incluyen estos métodos para comparar la funcionalidad exacta de data.table y dplyr con sus métodos de estructura de datos? Casi preferiría separar eso ya que la mayoría de los datos necesitarán ser limpiados antes de que group_by o cree el data.table. Podría ser una cuestión de gusto, pero creo que la parte más importante es qué tan eficientemente se pueden modelar los datos.