Pregunta 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, se une? Es decir, cómo obtengo:

  • Un unir internamente de df1 y df2:
    Devuelva solo las filas en las que la tabla de la izquierda tenga las teclas correspondientes en la tabla derecha.
  • Un unió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.
  • UN 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.
  • UN unió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 declaración de selección de estilo SQL?


925
2017-08-19 13:18


origen


Respuestas:


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

Unir internamente:  merge(df1, df2) funcionará para estos ejemplos porque R une automáticamente los marcos por nombres de variables comunes, pero lo más probable es que desee especificar merge(df1, df2, by = "CustomerId") para asegurarse de que estaba haciendo coincidir solo los campos que deseaba. También puedes usar el by.x y by.y parámetros 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 externa:  merge(x = df1, y = df2, by = "CustomerId", all.x = TRUE)

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

Cross join:  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 fusionarse en varias columnas dando by un vector, por ejemplo, by = c("CustomerId", "OrderId").

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


1028
2017-08-19 15:15



Yo recomendaría echarle un vistazo Paquete sqldf de Gabor Grothendieck, lo que le permite expresar 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 sintaxis SQL es más simple y más natural que su equivalente R (pero esto solo puede reflejar mi sesgo RDBMS).

Ver Gabor's sqldf GitHub para más información sobre las uniones.


182
2017-08-20 17:54



Ahí está el tabla de datos enfoque 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 merge.data.table)

merge(dt1, dt2)

data.table documentada en stackoverflow:
Cómo hacer una operación de fusión de data.table
La traducción de SQL se une en claves externas a la sintaxis 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?

Sin embargo, otra opción es la join función que se encuentra en el plyr paquete

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.

De ?join: Diferente a merge, [join] conserva el orden de x sin importar qué tipo de combinación se use.


166
2018-03-11 06:24



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

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.

138
2018-02-06 21:35



Hay algunos buenos ejemplos de hacer esto en el 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 ...")


71
2017-08-19 15:15



Nuevo en 2014:

Especialmente si también está interesado en la manipulación de datos en general (incluida la 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 marcos de datos 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 x y y
  • 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 columnas de x

Es todo aquí en gran detalle.

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


62
2018-01-29 17:43



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 primer argumento al subconjunto, otra forma es usar merge función 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

55
2017-12-11 09:23