Cómo unir (combinar) marcos de datos (interno, externo, izquierdo, derecho)?

Dado dos marcos de datos:

df1 = data.frame(CustomerId = c(1:6), Product = c(rep("Toaster", 3), rep("Radio", 3))) df2 = data.frame(CustomerId = c(2, 4, 6), State = c(rep("Alabama", 2), rep("Ohio", 1))) df1 # CustomerId Product # 1 Toaster # 2 Toaster # 3 Toaster # 4 Radio # 5 Radio # 6 Radio df2 # CustomerId State # 2 Alabama # 4 Alabama # 6 Ohio 

¿Cómo puedo hacer el estilo de base de datos, es decir, estilo sql, join ? Es decir, cómo obtengo:

  • Una unión interna de df1 y df2 :
    Devuelva solo las filas en las que la tabla de la izquierda tenga las teclas correspondientes en la tabla derecha.
  • Una combinación externa de df1 y df2 :
    Devuelve todas las filas de ambas tablas, une los registros de la izquierda que tienen claves coincidentes en la tabla derecha.
  • Una combinación externa izquierda (o simplemente combinación izquierda) de df1 y df2
    Devuelve todas las filas de la tabla izquierda y las filas con las teclas correspondientes de la tabla correcta.
  • Una combinación externa derecha de df1 y df2
    Devuelve todas las filas de la tabla derecha y cualquier fila con las teclas correspondientes de la tabla de la izquierda.

Crédito adicional:

¿Cómo puedo hacer una statement de selección de estilo SQL?

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

Unión interna: merge(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.

Unión externa: merge(x = df1, y = df2, by = "CustomerId", all = TRUE)

Izquierda exterior: merge(x = df1, y = df2, by = "CustomerId", all.x = TRUE)

Derecha exterior: merge(x = df1, y = df2, by = "CustomerId", all.y = TRUE)

Combinación cruzada: merge(x = df1, y = df2, by = NULL)

Al igual que con la unión interna, es probable que desee pasar explícitamente “CustomerId” a R como la variable de coincidencia. Creo que casi siempre es mejor indicar explícitamente los identificadores en los que desea fusionar; es más seguro si los marcos de entrada de datos cambian inesperadamente y son más fáciles de leer más adelante.

Puede fusionar en varias columnas dando by un vector, por ejemplo, by = c("CustomerId", "OrderId") .

Si los nombres de las columnas para fusionar no son los mismos, puede especificar, por ejemplo, by.x = "CustomerId_in_df1", by.y = “CustomerId_in_df2” where CustomerId_in_df1 is the name of the column in the first data frame and CustomerId_in_df2` es el nombre de la columna en el segundo dataframe. (Estos también pueden ser vectores si necesita fusionarse en múltiples columnas).

Recomiendo consultar el paquete sqldf de Gabor Grothendieck , que le permite express estas operaciones en SQL.

 library(sqldf) ## inner join df3 < - sqldf("SELECT CustomerId, Product, State FROM df1 JOIN df2 USING(CustomerID)") ## left join (substitute 'right' for right join) df4 <- sqldf("SELECT CustomerId, Product, State FROM df1 LEFT JOIN df2 USING(CustomerID)") 

Encuentro que la syntax SQL es más simple y más natural que su equivalente R (pero esto solo puede reflejar mi sesgo RDBMS).

Consulte el sqldf GitHub de Gabor para obtener más información sobre las uniones.

Existe el enfoque data.table para una unión interna, que es muy eficiente en tiempo y memoria (y necesario para algunos data.frames más grandes):

 library(data.table) dt1 < - data.table(df1, key = "CustomerId") dt2 <- data.table(df2, key = "CustomerId") joined.dt1.dt.2 <- dt1[dt2] 

merge también funciona en data.tables (ya que es genérico y llama a merge.data.table )

 merge(dt1, dt2) 

data.table documentada en stackoverflow:
Cómo hacer una operación de fusión de data.table
Traducir las uniones de SQL en claves externas a la syntax de R data.table
Alternativas eficientes para fusionar para data.frames más grandes R
¿Cómo hacer una combinación externa izquierda básica con data.table en R?

Otra opción más es la función de join que se encuentra en el paquete plyr

 library(plyr) join(df1, df2, type = "inner") # CustomerId Product State # 1 2 Toaster Alabama # 2 4 Radio Alabama # 3 6 Radio Ohio 

Opciones para type : inner , left , right , full .

From ?join : A diferencia de merge , [ join ] conserva el orden de x sin importar qué tipo de combinación se use.

Puedes hacer combinaciones también usando el impresionante paquete dplyr de Hadley Wickham.

 library(dplyr) #make sure that CustomerId cols are both type numeric #they ARE not using the provided code in question and dplyr will complain df1$CustomerId < - as.numeric(df1$CustomerId) df2$CustomerId <- as.numeric(df2$CustomerId) 

Muteting joins: agrega columnas a df1 usando coincidencias en df2

 #inner inner_join(df1, df2) #left outer left_join(df1, df2) #right outer right_join(df1, df2) #alternate right outer left_join(df2, df1) #full join full_join(df1, df2) 

Filtrar combinaciones: filtrar filas en df1, no modificar columnas

 semi_join(df1, df2) #keep only observations in df1 that match in df2. anti_join(df1, df2) #drops all observations in df1 that match in df2. 

Hay algunos buenos ejemplos de cómo hacer esto en R Wiki . Robaré un par aquí:

Método de fusión

Como las claves se nombran de la misma manera, la manera más fácil de hacer una combinación interna es merge ():

 merge(df1,df2) 

una combinación interna completa (todos los registros de ambas tablas) se puede crear con la palabra clave “todos”:

 merge(df1,df2, all=TRUE) 

una combinación externa izquierda de df1 y df2:

 merge(df1,df2, all.x=TRUE) 

una combinación externa derecha de df1 y df2:

 merge(df1,df2, all.y=TRUE) 

puedes voltearlos, abofetearlos y frotarlos para obtener los otros dos enlaces externos sobre los que preguntas 🙂

Método de subíndice

Una combinación externa izquierda con df1 a la izquierda con un método de subíndice sería:

 df1[,"State"]< -df2[df1[ ,"Product"], "State"] 

La otra combinación de uniones externas se puede crear mezclando el ejemplo del subíndice de unión externa izquierda. (Sí, sé que es el equivalente a decir "Lo dejaré como un ejercicio para el lector ...")

Nuevo en 2014:

Especialmente si también está interesado en la manipulación de datos en general (incluyendo clasificación, filtrado, subconjunto, resumen, etc.), definitivamente debe echar un vistazo a dplyr , que viene con una variedad de funciones, todas diseñadas para facilitar su trabajo específicamente con datos marcos y ciertos tipos de bases de datos. Incluso ofrece una interfaz SQL bastante elaborada, e incluso una función para convertir (la mayoría) el código SQL directamente en R.

Las cuatro funciones relacionadas con la unión en el paquete dplyr son (por citar):

  • inner_join(x, y, by = NULL, copy = FALSE, ...) : devuelve todas las filas de x donde hay valores coincidentes en y, y todas las columnas de xey
  • left_join(x, y, by = NULL, copy = FALSE, ...) : devuelve todas las filas de xy todas las columnas de xey
  • semi_join(x, y, by = NULL, copy = FALSE, ...) : devuelve todas las filas de x donde hay valores coincidentes en y, manteniendo solo columnas de x.
  • anti_join(x, y, by = NULL, copy = FALSE, ...) : devuelve todas las filas de x donde no hay valores coincidentes en y, manteniendo solo las columnas de x

Todo está aquí en gran detalle.

La selección de columnas se puede hacer select(df,"column") . Si eso no es lo suficientemente SQL-you para usted, entonces está la función sql() , en la cual puede ingresar el código SQL tal cual, y hará la operación que especificó como si estuviera escribiendo en R todo el tiempo (para más información , consulte la viñeta dplyr / databases ). Por ejemplo, si se aplica correctamente, sql("SELECT * FROM hflights") seleccionará todas las columnas de la tabla dplyr “hflights” (a “tbl”).

Actualización sobre métodos data.table para unir conjuntos de datos. Vea ejemplos a continuación para cada tipo de unión. Hay dos métodos, uno de [.data.table al pasar el segundo data.table como el primer argumento al subconjunto, otra forma es usar la función merge que se envía al método rápido data.table.

Actualización el 01/04/2016 – ¡y no es una broma de April Fools!
En la versión 1.9.7 de data.table joins ahora son capaces de usar el índice existente que reduce enormemente el tiempo de una unión. A continuación, el código y el índice de referencia NO usan índices de data.table en join . Si está buscando unirse casi en tiempo real, debe usar los índices data.table.

 df1 = data.frame(CustomerId = c(1:6), Product = c(rep("Toaster", 3), rep("Radio", 3))) df2 = data.frame(CustomerId = c(2L, 4L, 7L), State = c(rep("Alabama", 2), rep("Ohio", 1))) # one value changed to show full outer join library(data.table) dt1 = as.data.table(df1) dt2 = as.data.table(df2) setkey(dt1, CustomerId) setkey(dt2, CustomerId) # right outer join keyed data.tables dt1[dt2] setkey(dt1, NULL) setkey(dt2, NULL) # right outer join unkeyed data.tables - use `on` argument dt1[dt2, on = "CustomerId"] # left outer join - swap dt1 with dt2 dt2[dt1, on = "CustomerId"] # inner join - use `nomatch` argument dt1[dt2, nomatch=0L, on = "CustomerId"] # anti join - use `!` operator dt1[!dt2, on = "CustomerId"] # inner join merge(dt1, dt2, by = "CustomerId") # full outer join merge(dt1, dt2, by = "CustomerId", all = TRUE) # see ?merge.data.table arguments for other cases 

Debajo de las pruebas de benchmark base R, sqldf, dplyr y data.table.
Benchmark comprueba conjuntos de datos no indexados. Puede obtener un rendimiento aún mejor si está utilizando claves en sus data.tables o índices con sqldf. Base R y dplyr no tienen índices ni claves, por lo que no incluí ese escenario en el índice de referencia.
El Benchmark se realiza en conjuntos de datos de 5M-1 filas, existen 5M-2 valores comunes en la columna de unión, por lo que cada escenario (izquierdo, derecho, completo, interno) se puede probar y la unión aún no es trivial.

 library(microbenchmark) library(sqldf) library(dplyr) library(data.table) n = 5e6 set.seed(123) df1 = data.frame(x=sample(n,n-1L), y1=rnorm(n-1L)) df2 = data.frame(x=sample(n,n-1L), y2=rnorm(n-1L)) dt1 = as.data.table(df1) dt2 = as.data.table(df2) # inner join microbenchmark(times = 10L, base = merge(df1, df2, by = "x"), sqldf = sqldf("SELECT * FROM df1 INNER JOIN df2 ON df1.x = df2.x"), dplyr = inner_join(df1, df2, by = "x"), data.table = dt1[dt2, nomatch = 0L, on = "x"]) #Unit: milliseconds # expr min lq mean median uq max neval # base 15546.0097 16083.4915 16687.117 16539.0148 17388.290 18513.216 10 # sqldf 44392.6685 44709.7128 45096.401 45067.7461 45504.376 45563.472 10 # dplyr 4124.0068 4248.7758 4281.122 4272.3619 4342.829 4411.388 10 # data.table 937.2461 946.0227 1053.411 973.0805 1214.300 1281.958 10 # left outer join microbenchmark(times = 10L, base = merge(df1, df2, by = "x", all.x = TRUE), sqldf = sqldf("SELECT * FROM df1 LEFT OUTER JOIN df2 ON df1.x = df2.x"), dplyr = left_join(df1, df2, by = c("x"="x")), data.table = dt2[dt1, on = "x"]) #Unit: milliseconds # expr min lq mean median uq max neval # base 16140.791 17107.7366 17441.9538 17414.6263 17821.9035 19453.034 10 # sqldf 43656.633 44141.9186 44777.1872 44498.7191 45288.7406 47108.900 10 # dplyr 4062.153 4352.8021 4780.3221 4409.1186 4450.9301 8385.050 10 # data.table 823.218 823.5557 901.0383 837.9206 883.3292 1277.239 10 # right outer join microbenchmark(times = 10L, base = merge(df1, df2, by = "x", all.y = TRUE), sqldf = sqldf("SELECT * FROM df2 LEFT OUTER JOIN df1 ON df2.x = df1.x"), dplyr = right_join(df1, df2, by = "x"), data.table = dt1[dt2, on = "x"]) #Unit: milliseconds # expr min lq mean median uq max neval # base 15821.3351 15954.9927 16347.3093 16044.3500 16621.887 17604.794 10 # sqldf 43635.5308 43761.3532 43984.3682 43969.0081 44044.461 44499.891 10 # dplyr 3936.0329 4028.1239 4102.4167 4045.0854 4219.958 4307.350 10 # data.table 820.8535 835.9101 918.5243 887.0207 1005.721 1068.919 10 # full outer join microbenchmark(times = 10L, base = merge(df1, df2, by = "x", all = TRUE), #sqldf = sqldf("SELECT * FROM df1 FULL OUTER JOIN df2 ON df1.x = df2.x"), # not supported dplyr = full_join(df1, df2, by = "x"), data.table = merge(dt1, dt2, by = "x", all = TRUE)) #Unit: seconds # expr min lq mean median uq max neval # base 16.176423 16.908908 17.485457 17.364857 18.271790 18.626762 10 # dplyr 7.610498 7.666426 7.745850 7.710638 7.832125 7.951426 10 # data.table 2.052590 2.130317 2.352626 2.208913 2.470721 2.951948 10 

dplyr desde 0.4 implementó todas las combinaciones incluyendo outer_join, pero vale la pena señalar que para las primeras versiones solía no ofrecer outer_join, y como resultado había una gran cantidad de código de usuario de solución hacky muy malo flotando por un tiempo ( todavía puede encontrar esto en SO y Kaggle respuestas de ese período).

Aspectos destacados de la publicación relacionada con la unión :

v0.5 (6/2016)

  • Manejo de POSIXct tipo, zonas horarias, duplicados, diferentes niveles de factor. Mejores errores y advertencias.
  • Nuevo argumento de sufijo para controlar qué sufijo recibe el nombre de variable duplicado (# 1296)

v0.4.0 (1/2015)

  • Implementar unión correcta y unión externa (# 96)
  • Muteting joins, que agrega nuevas variables a una tabla de filas coincidentes en otra. Filtrado de uniones, que filtran observaciones de una tabla en función de si coinciden o no con una observación en la otra tabla.

v0.3 (10/2014)

  • Ahora puede left_join por diferentes variables en cada tabla: df1%>% left_join (df2, c (“var1” = “var2”))

v0.2 (5/2014)

  • * _join () ya no reordena los nombres de columna (# 324)

v0.1.3 (4/2014)

  • tiene inner_join, left_join, semi_join, anti_join
  • outer_join aún no implementado, fallback es use base :: merge () (o plyr :: join ())
  • todavía no implementó right_join y outer_join
  • Hadley menciona otras ventajas aquí
  • una combinación de características menores actualmente tiene que dplyr no tiene la capacidad de tener columnas by.x separadas, por ejemplo, como lo hace Python pandas.

Soluciones provisionales por los comentarios de hadley en ese tema:

  • right_join (x, y) es lo mismo que left_join (y, x) en términos de filas, solo las columnas serán órdenes diferentes. Se trabajó fácilmente con select (new_column_order)
  • outer_join es básicamente union (left_join (x, y), right_join (x, y)) – es decir, conserva todas las filas en ambos marcos de datos.

Al unir dos marcos de datos con ~ 1 millón de filas cada uno, uno con 2 columnas y el otro con ~ 20, sorprendentemente he encontrado merge(..., all.x = TRUE, all.y = TRUE) para ser más rápido que dplyr::full_join() . Esto es con dplyr v0.4

La fusión tarda ~ 17 segundos, la unión completa tarda ~ 65 segundos.

Sin embargo, algo de comida, ya que generalmente prefiero dplyr para tareas de manipulación.

Para el caso de una combinación izquierda con una cardinalidad de 0..*:0..1 o una combinación de la derecha con una cardinalidad de 0..1:0..* , es posible asignar in situ las columnas unilaterales de la unión ( la tabla 0..1 ) directamente en el sitio web (la tabla 0..* ), y así evitar la creación de una tabla de datos completamente nueva. Esto requiere que coincidan las columnas de clave de la unión en la unión y la indexación + ordenación de las filas de la unión para la asignación.

Si la clave es una sola columna, podemos usar una sola llamada para hacer match() . Este es el caso que cubriré en esta respuesta.

Aquí hay un ejemplo basado en el OP, excepto que agregué una fila adicional a df2 con una identificación de 7 para probar el caso de una clave que no coincide en el carpintero. Esto es efectivamente df1 left join df2 :

 df1 < - data.frame(CustomerId=1:6,Product=c(rep('Toaster',3L),rep('Radio',3L))); df2 <- data.frame(CustomerId=c(2L,4L,6L,7L),State=c(rep('Alabama',2L),'Ohio','Texas')); df1[names(df2)[-1L]] <- df2[match(df1[,1L],df2[,1L]),-1L]; df1; ## CustomerId Product State ## 1 1 Toaster  ## 2 2 Toaster Alabama ## 3 3 Toaster  ## 4 4 Radio Alabama ## 5 5 Radio  ## 6 6 Radio Ohio 

En lo anterior, codifiqué una suposición de que la columna clave es la primera columna de ambas tablas de entrada. Yo diría que, en general, esto no es una suposición poco razonable, ya que, si tiene un data.frame con una columna de clave, sería extraño si no se hubiera configurado como la primera columna del data.frame de El principio. Y siempre puede reordenar las columnas para que así sea. Una consecuencia ventajosa de esta suposición es que el nombre de la columna de clave no tiene que estar codificado, aunque supongo que simplemente está reemplazando una suposición por otra. La concisión es otra ventaja de la indexación entera, así como de la velocidad. En los puntos de referencia a continuación, cambiaré la implementación para usar la indexación del nombre de cadena para que coincida con las implementaciones de la competencia.

Creo que esta es una solución particularmente adecuada si tiene varias tablas que desea unir contra una única tabla grande. Reconstruir repetidamente toda la tabla para cada fusión sería innecesario e ineficiente.

Por otro lado, si necesita que la unión permanezca inalterada a través de esta operación por el motivo que sea, esta solución no se puede usar, ya que modifica directamente la unión. Aunque en ese caso podría simplemente hacer una copia y realizar la (s) asignación (es) in situ en la copia.


Como nota al margen, analicé brevemente las posibles soluciones de coincidencia para claves de múltiples columnas. Lamentablemente, las únicas soluciones de coincidencia que encontré fueron:

  • concatenaciones ineficientes. por ejemplo, match(interaction(df1$a,df1$b),interaction(df2$a,df2$b)) , o la misma idea con paste() .
  • Conjunciones cartesianas ineficientes, por ejemplo, outer(df1$a,df2$a,`==`) & outer(df1$b,df2$b,`==`) .
  • base R merge() y funciones de fusión basadas en paquetes equivalentes, que siempre asignan una nueva tabla para devolver el resultado fusionado, y por lo tanto no son adecuadas para una solución local basada en asignación.

Por ejemplo, consulte Hacer coincidir varias columnas en diferentes marcos de datos y obtener otra columna como resultado , unir dos columnas con otras dos columnas , Hacer coincidir en varias columnas y el engaño de esta pregunta donde originalmente se me ocurrió la solución in situ. Combinar dos marcos de datos con diferente número de filas en R.


Benchmarking

Decidí hacer mi propia evaluación comparativa para ver cómo el enfoque de asignación en el lugar se compara con las otras soluciones que se han ofrecido en esta pregunta.

Código de prueba:

 library(microbenchmark); library(data.table); library(sqldf); library(plyr); library(dplyr); solSpecs < - list( merge=list(testFuncs=list( inner=function(df1,df2,key) merge(df1,df2,key), left =function(df1,df2,key) merge(df1,df2,key,all.x=T), right=function(df1,df2,key) merge(df1,df2,key,all.y=T), full =function(df1,df2,key) merge(df1,df2,key,all=T) )), data.table.unkeyed=list(argSpec='data.table.unkeyed',testFuncs=list( inner=function(dt1,dt2,key) dt1[dt2,on=key,nomatch=0L,allow.cartesian=T], left =function(dt1,dt2,key) dt2[dt1,on=key,allow.cartesian=T], right=function(dt1,dt2,key) dt1[dt2,on=key,allow.cartesian=T], full =function(dt1,dt2,key) merge(dt1,dt2,key,all=T,allow.cartesian=T) ## calls merge.data.table() )), data.table.keyed=list(argSpec='data.table.keyed',testFuncs=list( inner=function(dt1,dt2) dt1[dt2,nomatch=0L,allow.cartesian=T], left =function(dt1,dt2) dt2[dt1,allow.cartesian=T], right=function(dt1,dt2) dt1[dt2,allow.cartesian=T], full =function(dt1,dt2) merge(dt1,dt2,all=T,allow.cartesian=T) ## calls merge.data.table() )), sqldf.unindexed=list(testFuncs=list( ## note: must pass connection=NULL to avoid running against the live DB connection, which would result in collisions with the residual tables from the last query upload inner=function(df1,df2,key) sqldf(paste0('select * from df1 inner join df2 using(',paste(collapse=',',key),')'),connection=NULL), left =function(df1,df2,key) sqldf(paste0('select * from df1 left join df2 using(',paste(collapse=',',key),')'),connection=NULL), right=function(df1,df2,key) sqldf(paste0('select * from df2 left join df1 using(',paste(collapse=',',key),')'),connection=NULL) ## can't do right join proper, not yet supported; inverted left join is equivalent ##full =function(df1,df2,key) sqldf(paste0('select * from df1 full join df2 using(',paste(collapse=',',key),')'),connection=NULL) ## can't do full join proper, not yet supported; possible to hack it with a union of left joins, but too unreasonable to include in testing )), sqldf.indexed=list(testFuncs=list( ## important: requires an active DB connection with preindexed main.df1 and main.df2 ready to go; arguments are actually ignored inner=function(df1,df2,key) sqldf(paste0('select * from main.df1 inner join main.df2 using(',paste(collapse=',',key),')')), left =function(df1,df2,key) sqldf(paste0('select * from main.df1 left join main.df2 using(',paste(collapse=',',key),')')), right=function(df1,df2,key) sqldf(paste0('select * from main.df2 left join main.df1 using(',paste(collapse=',',key),')')) ## can't do right join proper, not yet supported; inverted left join is equivalent ##full =function(df1,df2,key) sqldf(paste0('select * from main.df1 full join main.df2 using(',paste(collapse=',',key),')')) ## can't do full join proper, not yet supported; possible to hack it with a union of left joins, but too unreasonable to include in testing )), plyr=list(testFuncs=list( inner=function(df1,df2,key) join(df1,df2,key,'inner'), left =function(df1,df2,key) join(df1,df2,key,'left'), right=function(df1,df2,key) join(df1,df2,key,'right'), full =function(df1,df2,key) join(df1,df2,key,'full') )), dplyr=list(testFuncs=list( inner=function(df1,df2,key) inner_join(df1,df2,key), left =function(df1,df2,key) left_join(df1,df2,key), right=function(df1,df2,key) right_join(df1,df2,key), full =function(df1,df2,key) full_join(df1,df2,key) )), in.place=list(testFuncs=list( left =function(df1,df2,key) { cns <- setdiff(names(df2),key); df1[cns] <- df2[match(df1[,key],df2[,key]),cns]; df1; }, right=function(df1,df2,key) { cns <- setdiff(names(df1),key); df2[cns] <- df1[match(df2[,key],df1[,key]),cns]; df2; } )) ); getSolTypes <- function() names(solSpecs); getJoinTypes <- function() unique(unlist(lapply(solSpecs,function(x) names(x$testFuncs)))); getArgSpec <- function(argSpecs,key=NULL) if (is.null(key)) argSpecs$default else argSpecs[[key]]; initSqldf <- function() { sqldf(); ## creates sqlite connection on first run, cleans up and closes existing connection otherwise if (exists('sqldfInitFlag',envir=globalenv(),inherits=F) && sqldfInitFlag) { ## false only on first run sqldf(); ## creates a new connection } else { assign('sqldfInitFlag',T,envir=globalenv()); ## set to true for the one and only time }; ## end if invisible(); }; ## end initSqldf() setUpBenchmarkCall <- function(argSpecs,joinType,solTypes=getSolTypes(),env=parent.frame()) { ## builds and returns a list of expressions suitable for passing to the list argument of microbenchmark(), and assigns variables to resolve symbol references in those expressions callExpressions <- list(); nms <- character(); for (solType in solTypes) { testFunc <- solSpecs[[solType]]$testFuncs[[joinType]]; if (is.null(testFunc)) next; ## this join type is not defined for this solution type testFuncName <- paste0('tf.',solType); assign(testFuncName,testFunc,envir=env); argSpecKey <- solSpecs[[solType]]$argSpec; argSpec <- getArgSpec(argSpecs,argSpecKey); argList <- setNames(nm=names(argSpec$args),vector('list',length(argSpec$args))); for (i in seq_along(argSpec$args)) { argName <- paste0('tfa.',argSpecKey,i); assign(argName,argSpec$args[[i]],envir=env); argList[[i]] <- if (i%in%argSpec$copySpec) call('copy',as.symbol(argName)) else as.symbol(argName); }; ## end for callExpressions[[length(callExpressions)+1L]] <- do.call(call,c(list(testFuncName),argList),quote=T); nms[length(nms)+1L] <- solType; }; ## end for names(callExpressions) <- nms; callExpressions; }; ## end setUpBenchmarkCall() harmonize <- function(res) { res <- as.data.frame(res); ## coerce to data.frame for (ci in which(sapply(res,is.factor))) res[[ci]] <- as.character(res[[ci]]); ## coerce factor columns to character for (ci in which(sapply(res,is.logical))) res[[ci]] <- as.integer(res[[ci]]); ## coerce logical columns to integer (works around sqldf quirk of munging logicals to integers) ##for (ci in which(sapply(res,inherits,'POSIXct'))) res[[ci]] <- as.double(res[[ci]]); ## coerce POSIXct columns to double (works around sqldf quirk of losing POSIXct class) ----- POSIXct doesn't work at all in sqldf.indexed res <- res[order(names(res))]; ## order columns res <- res[do.call(order,res),]; ## order rows res; }; ## end harmonize() checkIdentical <- function(argSpecs,solTypes=getSolTypes()) { for (joinType in getJoinTypes()) { callExpressions <- setUpBenchmarkCall(argSpecs,joinType,solTypes); if (length(callExpressions)<2L) next; ex <- harmonize(eval(callExpressions[[1L]])); for (i in seq(2L,len=length(callExpressions)-1L)) { y <- harmonize(eval(callExpressions[[i]])); if (!isTRUE(all.equal(ex,y,check.attributes=F))) { ex <<- ex; y <<- y; solType <- names(callExpressions)[i]; stop(paste0('non-identical: ',solType,' ',joinType,'.')); }; ## end if }; ## end for }; ## end for invisible(); }; ## end checkIdentical() testJoinType <- function(argSpecs,joinType,solTypes=getSolTypes(),metric=NULL,times=100L) { callExpressions <- setUpBenchmarkCall(argSpecs,joinType,solTypes); bm <- microbenchmark(list=callExpressions,times=times); if (is.null(metric)) return(bm); bm <- summary(bm); res <- setNames(nm=names(callExpressions),bm[[metric]]); attr(res,'unit') <- attr(bm,'unit'); res; }; ## end testJoinType() testAllJoinTypes <- function(argSpecs,solTypes=getSolTypes(),metric=NULL,times=100L) { joinTypes <- getJoinTypes(); resList <- setNames(nm=joinTypes,lapply(joinTypes,function(joinType) testJoinType(argSpecs,joinType,solTypes,metric,times))); if (is.null(metric)) return(resList); units <- unname(unlist(lapply(resList,attr,'unit'))); res <- do.call(data.frame,c(list(join=joinTypes),setNames(nm=solTypes,rep(list(rep(NA_real_,length(joinTypes))),length(solTypes))),list(unit=units,stringsAsFactors=F))); for (i in seq_along(resList)) res[i,match(names(resList[[i]]),names(res))] <- resList[[i]]; res; }; ## end testAllJoinTypes() testGrid <- function(makeArgSpecsFunc,sizes,overlaps,solTypes=getSolTypes(),joinTypes=getJoinTypes(),metric='median',times=100L) { res <- expand.grid(size=sizes,overlap=overlaps,joinType=joinTypes,stringsAsFactors=F); res[solTypes] <- NA_real_; res$unit <- NA_character_; for (ri in seq_len(nrow(res))) { size <- res$size[ri]; overlap <- res$overlap[ri]; joinType <- res$joinType[ri]; argSpecs <- makeArgSpecsFunc(size,overlap); checkIdentical(argSpecs,solTypes); cur <- testJoinType(argSpecs,joinType,solTypes,metric,times); res[ri,match(names(cur),names(res))] <- cur; res$unit[ri] <- attr(cur,'unit'); }; ## end for res; }; ## end testGrid() 

Here's a benchmark of the example based on the OP that I demonstrated earlier:

 ## OP's example, supplemented with a non-matching row in df2 argSpecs < - list( default=list(copySpec=1:2,args=list( df1 <- data.frame(CustomerId=1:6,Product=c(rep('Toaster',3L),rep('Radio',3L))), df2 <- data.frame(CustomerId=c(2L,4L,6L,7L),State=c(rep('Alabama',2L),'Ohio','Texas')), 'CustomerId' )), data.table.unkeyed=list(copySpec=1:2,args=list( as.data.table(df1), as.data.table(df2), 'CustomerId' )), data.table.keyed=list(copySpec=1:2,args=list( setkey(as.data.table(df1),CustomerId), setkey(as.data.table(df2),CustomerId) )) ); ## prepare sqldf initSqldf(); sqldf('create index df1_key on df1(CustomerId);'); ## upload and create an sqlite index on df1 sqldf('create index df2_key on df2(CustomerId);'); ## upload and create an sqlite index on df2 checkIdentical(argSpecs); testAllJoinTypes(argSpecs,metric='median'); ## join merge data.table.unkeyed data.table.keyed sqldf.unindexed sqldf.indexed plyr dplyr in.place unit ## 1 inner 644.259 861.9345 923.516 9157.752 1580.390 959.2250 270.9190 NA microseconds ## 2 left 713.539 888.0205 910.045 8820.334 1529.714 968.4195 270.9185 224.3045 microseconds ## 3 right 1221.804 909.1900 923.944 8930.668 1533.135 1063.7860 269.8495 218.1035 microseconds ## 4 full 1302.203 3107.5380 3184.729 NA NA 1593.6475 270.7055 NA microseconds 

Here I benchmark on random input data, trying different scales and different patterns of key overlap between the two input tables. This benchmark is still restricted to the case of a single-column integer key. As well, to ensure that the in-place solution would work for both left and right joins of the same tables, all random test data uses 0..1:0..1 cardinality. This is implemented by sampling without replacement the key column of the first data.frame when generating the key column of the second data.frame.

 makeArgSpecs.singleIntegerKey.optionalOneToOne < - function(size,overlap) { com <- as.integer(size*overlap); argSpecs <- list( default=list(copySpec=1:2,args=list( df1 <- data.frame(id=sample(size),y1=rnorm(size),y2=rnorm(size)), df2 <- data.frame(id=sample(c(if (com>0L) sample(df1$id,com) else integer(),seq(size+1L,len=size-com))),y3=rnorm(size),y4=rnorm(size)), 'id' )), data.table.unkeyed=list(copySpec=1:2,args=list( as.data.table(df1), as.data.table(df2), 'id' )), data.table.keyed=list(copySpec=1:2,args=list( setkey(as.data.table(df1),id), setkey(as.data.table(df2),id) )) ); ## prepare sqldf initSqldf(); sqldf('create index df1_key on df1(id);'); ## upload and create an sqlite index on df1 sqldf('create index df2_key on df2(id);'); ## upload and create an sqlite index on df2 argSpecs; }; ## end makeArgSpecs.singleIntegerKey.optionalOneToOne() ## cross of various input sizes and key overlaps sizes < - c(1e1L,1e3L,1e6L); overlaps <- c(0.99,0.5,0.01); system.time({ res <- testGrid(makeArgSpecs.singleIntegerKey.optionalOneToOne,sizes,overlaps); }); ## user system elapsed ## 22024.65 12308.63 34493.19 

I wrote some code to create log-log plots of the above results. I generated a separate plot for each overlap percentage. It's a little bit cluttered, but I like having all the solution types and join types represented in the same plot.

I used spline interpolation to show a smooth curve for each solution/join type combination, drawn with individual pch symbols. The join type is captured by the pch symbol, using a dot for inner, left and right angle brackets for left and right, and a diamond for full. The solution type is captured by the color as shown in the legend.

 plotRes < - function(res,titleFunc,useFloor=F) { solTypes <- setdiff(names(res),c('size','overlap','joinType','unit')); ## derive from res normMult <- c(microseconds=1e-3,milliseconds=1); ## normalize to milliseconds joinTypes <- getJoinTypes(); cols <- c(merge='purple',data.table.unkeyed='blue',data.table.keyed='#00DDDD',sqldf.unindexed='brown',sqldf.indexed='orange',plyr='red',dplyr='#00BB00',in.place='magenta'); pchs <- list(inner=20L,left='<',right='>',full=23L); cexs < - c(inner=0.7,left=1,right=1,full=0.7); NP <- 60L; ord <- order(decreasing=T,colMeans(res[res$size==max(res$size),solTypes],na.rm=T)); ymajors <- data.frame(y=c(1,1e3),label=c('1ms','1s'),stringsAsFactors=F); for (overlap in unique(res$overlap)) { x1 <- res[res$overlap==overlap,]; x1[solTypes] <- x1[solTypes]*normMult[x1$unit]; x1$unit <- NULL; xlim <- c(1e1,max(x1$size)); xticks <- 10^seq(log10(xlim[1L]),log10(xlim[2L])); ylim <- c(1e-1,10^((if (useFloor) floor else ceiling)(log10(max(x1[solTypes],na.rm=T))))); ## use floor() to zoom in a little more, only sqldf.unindexed will break above, but xpd=NA will keep it visible yticks <- 10^seq(log10(ylim[1L]),log10(ylim[2L])); yticks.minor <- rep(yticks[-length(yticks)],each=9L)*1:9; plot(NA,xlim=xlim,ylim=ylim,xaxs='i',yaxs='i',axes=F,xlab='size (rows)',ylab='time (ms)',log='xy'); abline(v=xticks,col='lightgrey'); abline(h=yticks.minor,col='lightgrey',lty=3L); abline(h=yticks,col='lightgrey'); axis(1L,xticks,parse(text=sprintf('10^%d',as.integer(log10(xticks))))); axis(2L,yticks,parse(text=sprintf('10^%d',as.integer(log10(yticks)))),las=1L); axis(4L,ymajors$y,ymajors$label,las=1L,tick=F,cex.axis=0.7,hadj=0.5); for (joinType in rev(joinTypes)) { ## reverse to draw full first, since it's larger and would be more obtrusive if drawn last x2 <- x1[x1$joinType==joinType,]; for (solType in solTypes) { if (any(!is.na(x2[[solType]]))) { xy <- spline(x2$size,x2[[solType]],xout=10^(seq(log10(x2$size[1L]),log10(x2$size[nrow(x2)]),len=NP))); points(xy$x,xy$y,pch=pchs[[joinType]],col=cols[solType],cex=cexs[joinType],xpd=NA); }; ## end if }; ## end for }; ## end for ## custom legend ## due to logarithmic skew, must do all distance calcs in inches, and convert to user coords afterward ## the bottom-left corner of the legend will be defined in normalized figure coords, although we can convert to inches immediately leg.cex <- 0.7; leg.x.in <- grconvertX(0.275,'nfc','in'); leg.y.in <- grconvertY(0.6,'nfc','in'); leg.x.user <- grconvertX(leg.x.in,'in'); leg.y.user <- grconvertY(leg.y.in,'in'); leg.outpad.w.in <- 0.1; leg.outpad.h.in <- 0.1; leg.midpad.w.in <- 0.1; leg.midpad.h.in <- 0.1; leg.sol.w.in <- max(strwidth(solTypes,'in',leg.cex)); leg.sol.h.in <- max(strheight(solTypes,'in',leg.cex))*1.5; ## multiplication factor for greater line height leg.join.w.in <- max(strheight(joinTypes,'in',leg.cex))*1.5; ## ditto leg.join.h.in <- max(strwidth(joinTypes,'in',leg.cex)); leg.main.w.in <- leg.join.w.in*length(joinTypes); leg.main.h.in <- leg.sol.h.in*length(solTypes); leg.x2.user <- grconvertX(leg.x.in+leg.outpad.w.in*2+leg.main.w.in+leg.midpad.w.in+leg.sol.w.in,'in'); leg.y2.user <- grconvertY(leg.y.in+leg.outpad.h.in*2+leg.main.h.in+leg.midpad.h.in+leg.join.h.in,'in'); leg.cols.x.user <- grconvertX(leg.x.in+leg.outpad.w.in+leg.join.w.in*(0.5+seq(0L,length(joinTypes)-1L)),'in'); leg.lines.y.user <- grconvertY(leg.y.in+leg.outpad.h.in+leg.main.h.in-leg.sol.h.in*(0.5+seq(0L,length(solTypes)-1L)),'in'); leg.sol.x.user <- grconvertX(leg.x.in+leg.outpad.w.in+leg.main.w.in+leg.midpad.w.in,'in'); leg.join.y.user <- grconvertY(leg.y.in+leg.outpad.h.in+leg.main.h.in+leg.midpad.h.in,'in'); rect(leg.x.user,leg.y.user,leg.x2.user,leg.y2.user,col='white'); text(leg.sol.x.user,leg.lines.y.user,solTypes[ord],cex=leg.cex,pos=4L,offset=0); text(leg.cols.x.user,leg.join.y.user,joinTypes,cex=leg.cex,pos=4L,offset=0,srt=90); ## srt rotation applies *after* pos/offset positioning for (i in seq_along(joinTypes)) { joinType <- joinTypes[i]; points(rep(leg.cols.x.user[i],length(solTypes)),ifelse(colSums(!is.na(x1[x1$joinType==joinType,solTypes[ord]]))==0L,NA,leg.lines.y.user),pch=pchs[[joinType]],col=cols[solTypes[ord]]); }; ## end for title(titleFunc(overlap)); readline(sprintf('overlap %.02f',overlap)); }; ## end for }; ## end plotRes() titleFunc <- function(overlap) sprintf('R merge solutions: single-column integer key, 0..1:0..1 cardinality, %d%% overlap',as.integer(overlap*100)); plotRes(res,titleFunc,T); 

R-merge-benchmark-single-column-integer-key-optional-one-to-one-99

R-merge-benchmark-single-column-integer-key-optional-one-to-one-50

R-merge-benchmark-single-column-integer-key-optional-one-to-one-1


Here's a second large-scale benchmark that's more heavy-duty, with respect to the number and types of key columns, as well as cardinality. For this benchmark I use three key columns: one character, one integer, and one logical, with no restrictions on cardinality (that is, 0..*:0..* ). (In general it's not advisable to define key columns with double or complex values due to floating-point comparison complications, and basically no one ever uses the raw type, much less for key columns, so I haven't included those types in the key columns. Also, for information's sake, I initially tried to use four key columns by including a POSIXct key column, but the POSIXct type didn't play well with the sqldf.indexed solution for some reason, possibly due to floating-point comparison anomalies, so I removed it.)

 makeArgSpecs.assortedKey.optionalManyToMany < - function(size,overlap,uniquePct=75) { ## number of unique keys in df1 u1Size <- as.integer(size*uniquePct/100); ## (roughly) divide u1Size into bases, so we can use expand.grid() to produce the required number of unique key values with repetitions within individual key columns ## use ceiling() to ensure we cover u1Size; will truncate afterward u1SizePerKeyColumn <- as.integer(ceiling(u1Size^(1/3))); ## generate the unique key values for df1 keys1 <- expand.grid(stringsAsFactors=F, idCharacter=replicate(u1SizePerKeyColumn,paste(collapse='',sample(letters,sample(4:12,1L),T))), idInteger=sample(u1SizePerKeyColumn), idLogical=sample(c(F,T),u1SizePerKeyColumn,T) ##idPOSIXct=as.POSIXct('2016-01-01 00:00:00','UTC')+sample(u1SizePerKeyColumn) )[seq_len(u1Size),]; ## rbind some repetitions of the unique keys; this will prepare one side of the many-to-many relationship ## also scramble the order afterward keys1 <- rbind(keys1,keys1[sample(nrow(keys1),size-u1Size,T),])[sample(size),]; ## common and unilateral key counts com <- as.integer(size*overlap); uni <- size-com; ## generate some unilateral keys for df2 by synthesizing outside of the idInteger range of df1 keys2 <- data.frame(stringsAsFactors=F, idCharacter=replicate(uni,paste(collapse='',sample(letters,sample(4:12,1L),T))), idInteger=u1SizePerKeyColumn+sample(uni), idLogical=sample(c(F,T),uni,T) ##idPOSIXct=as.POSIXct('2016-01-01 00:00:00','UTC')+u1SizePerKeyColumn+sample(uni) ); ## rbind random keys from df1; this will complete the many-to-many relationship ## also scramble the order afterward keys2 <- rbind(keys2,keys1[sample(nrow(keys1),com,T),])[sample(size),]; ##keyNames <- c('idCharacter','idInteger','idLogical','idPOSIXct'); keyNames <- c('idCharacter','idInteger','idLogical'); ## note: was going to use raw and complex type for two of the non-key columns, but data.table doesn't seem to fully support them argSpecs <- list( default=list(copySpec=1:2,args=list( df1 <- cbind(stringsAsFactors=F,keys1,y1=sample(c(F,T),size,T),y2=sample(size),y3=rnorm(size),y4=replicate(size,paste(collapse='',sample(letters,sample(4:12,1L),T)))), df2 <- cbind(stringsAsFactors=F,keys2,y5=sample(c(F,T),size,T),y6=sample(size),y7=rnorm(size),y8=replicate(size,paste(collapse='',sample(letters,sample(4:12,1L),T)))), keyNames )), data.table.unkeyed=list(copySpec=1:2,args=list( as.data.table(df1), as.data.table(df2), keyNames )), data.table.keyed=list(copySpec=1:2,args=list( setkeyv(as.data.table(df1),keyNames), setkeyv(as.data.table(df2),keyNames) )) ); ## prepare sqldf initSqldf(); sqldf(paste0('create index df1_key on df1(',paste(collapse=',',keyNames),');')); ## upload and create an sqlite index on df1 sqldf(paste0('create index df2_key on df2(',paste(collapse=',',keyNames),');')); ## upload and create an sqlite index on df2 argSpecs; }; ## end makeArgSpecs.assortedKey.optionalManyToMany() sizes <- c(1e1L,1e3L,1e5L); ## 1e5L instead of 1e6L to respect more heavy-duty inputs overlaps <- c(0.99,0.5,0.01); solTypes <- setdiff(getSolTypes(),'in.place'); system.time({ res <- testGrid(makeArgSpecs.assortedKey.optionalManyToMany,sizes,overlaps,solTypes); }); ## user system elapsed ## 38895.50 784.19 39745.53 

The resulting plots, using the same plotting code given above:

 titleFunc < - function(overlap) sprintf('R merge solutions: character/integer/logical key, 0..*:0..* cardinality, %d%% overlap',as.integer(overlap*100)); plotRes(res,titleFunc,F); 

R-merge-benchmark-assorted-key-optional-many-to-many-99

R-merge-benchmark-assorted-key-optional-many-to-many-50

R-merge-benchmark-assorted-key-optional-many-to-many-1

  1. Using merge function we can select the variable of left table or right table, same way like we all familiar with select statement in SQL (EX : Select a.* …or Select b.* from …..)
  2. We have to add extra code which will subset from the newly joined table .

    • SQL :- select a.* from df1 a inner join df2 b on a.CustomerId=b.CustomerId

    • R :- merge(df1, df2, by.x = "CustomerId", by.y = "CustomerId")[,names(df1)]

Same way

  • SQL :- select b.* from df1 a inner join df2 b on a.CustomerId=b.CustomerId

  • R :- merge(df1, df2, by.x = "CustomerId", by.y = "CustomerId")[,names(df2)]

For an inner join on all columns, you could also use fintersect from the data.table -package or intersect from the dplyr -package as an alternative to merge without specifying the by -columns. this will give the rows that are equal between two dataframes:

 merge(df1, df2) # V1 V2 # 1 B 2 # 2 C 3 dplyr::intersect(df1, df2) # V1 V2 # 1 B 2 # 2 C 3 data.table::fintersect(setDT(df1), setDT(df2)) # V1 V2 # 1: B 2 # 2: C 3 

Example data:

 df1 < - data.frame(V1 = LETTERS[1:4], V2 = 1:4) df2 <- data.frame(V1 = LETTERS[2:3], V2 = 2:3)