Question Comment joindre (fusionner) des trames de données (interne, externe, gauche, droite)?


Étant donné deux trames de données:

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

Comment puis-je faire le style de base de données, c'est-à-dire, style sql, rejoint? C'est, comment puis-je obtenir:

  • Un jointure interne de df1 et df2:
    Renvoie uniquement les lignes dans lesquelles la table de gauche a des clés correspondantes dans la table de droite.
  • Un jointure externe de df1 et df2:
    Renvoie toutes les lignes des deux tables, joint les enregistrements de gauche qui ont des clés correspondantes dans la bonne table.
  • UNE jointure externe gauche (ou simplement jointure gauche) de df1 et df2
    Retourne toutes les lignes de la table de gauche, et toutes les lignes avec les clés correspondantes de la table de droite.
  • UNE jointure externe droite de df1 et df2
    Retourne toutes les lignes de la table de droite, et toutes les lignes avec les clés correspondantes de la table de gauche.

Crédit supplémentaire:

Comment puis-je faire une instruction de sélection de style SQL?


925
2017-08-19 13:18


origine


Réponses:


En utilisant le merge fonction et ses paramètres facultatifs:

Jointure interne:  merge(df1, df2) fonctionnera pour ces exemples parce que R joint automatiquement les cadres par des noms communs de variables, mais vous voudrez probablement spécifier merge(df1, df2, by = "CustomerId") pour vous assurer que vous correspondiez uniquement aux champs souhaités. Vous pouvez également utiliser le by.x et by.y paramètres si les variables correspondantes ont des noms différents dans les différentes trames de données.

Jointure externe:  merge(x = df1, y = df2, by = "CustomerId", all = TRUE)

Extérieur gauche:  merge(x = df1, y = df2, by = "CustomerId", all.x = TRUE)

Extérieur droit:  merge(x = df1, y = df2, by = "CustomerId", all.y = TRUE)

Croix rejoindre:  merge(x = df1, y = df2, by = NULL)

Tout comme avec la jointure interne, vous voudrez probablement passer explicitement "CustomerId" à R comme variable correspondante.  Je pense qu'il est presque toujours préférable d'indiquer explicitement les identifiants sur lesquels vous voulez fusionner; il est plus sûr si les data.deames d'entrée changent de manière inattendue et plus facile à lire plus tard.

Vous pouvez fusionner sur plusieurs colonnes en donnant by un vecteur, par exemple, by = c("CustomerId", "OrderId").

Si les noms de colonnes à fusionner ne sont pas identiques, vous pouvez spécifier, par exemple, 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` est le nom de la colonne dans la seconde trame de données. (Ceux-ci peuvent aussi être des vecteurs si vous avez besoin de fusionner sur plusieurs colonnes.)


1028
2017-08-19 15:15



Je recommande de vérifier Le paquet sqldf de Gabor Grothendieck, ce qui vous permet d'exprimer ces opérations 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)")

Je trouve la syntaxe SQL plus simple et plus naturelle que son équivalent R (mais cela peut refléter juste mon biais RDBMS).

Voir Le GitHub de Gabor pour plus d'informations sur les jointures.


182
2017-08-20 17:54



Il y a le data.table approche pour une jointure interne, qui est très efficace en termes de temps et de mémoire (et nécessaire pour certains grands noms de données):

library(data.table)

dt1 <- data.table(df1, key = "CustomerId") 
dt2 <- data.table(df2, key = "CustomerId")

joined.dt1.dt.2 <- dt1[dt2]

merge fonctionne également sur data.tables (car il est générique et appelle merge.data.table)

merge(dt1, dt2)

data.table documenté sur stackoverflow:
Comment faire une opération de fusion data.table
Traduction de jointures SQL sur des clés étrangères vers la syntaxe R data.table
Des alternatives efficaces à fusionner pour de plus grands data.frames R
Comment faire une jointure externe gauche basique avec data.table dans R?

Une autre option est la join fonction trouvée dans le plyr paquet

library(plyr)

join(df1, df2,
     type = "inner")

#   CustomerId Product   State
# 1          2 Toaster Alabama
# 2          4   Radio Alabama
# 3          6   Radio    Ohio

Options pour type: inner, left, right, full.

De ?join: Contrairement à merge, [join] préserve l'ordre de x quel que soit le type de jointure utilisé.


166
2018-03-11 06:24



Vous pouvez faire des jointures en utilisant Hadley Wickham génial dplyr paquet.

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)

Mutations de jointures: ajout de colonnes à df1 à l'aide de correspondances dans 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)

Filtrage des jointures: filtre les lignes de df1, ne modifie pas les colonnes

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



Il y a quelques bons exemples de faire cela à la R Wiki. Je vais voler un couple ici:

Fusionner la méthode

Puisque vos clés sont nommées de la même manière, la méthode la plus courte pour faire une jointure interne est merge ():

merge(df1,df2)

une jointure interne complète (tous les enregistrements des deux tables) peut être créée avec le mot clé "all":

merge(df1,df2, all=TRUE)

une jointure externe gauche de df1 et df2:

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

une jointure externe droite de df1 et df2:

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

vous pouvez les retourner, les gifler et les abaisser pour obtenir les deux autres jointures externes que vous avez demandées :)

Méthode de l'indice

Une jointure externe gauche avec df1 sur la gauche en utilisant une méthode d'indice serait:

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

L'autre combinaison de jointures externes peut être créée en utilisant l'exemple d'indice de jointure externe gauche. (oui, je sais que c'est l'équivalent de dire "je vais le laisser comme un exercice pour le lecteur ...")


71
2017-08-19 15:15



Nouveau en 2014:

Surtout si vous êtes également intéressé par la manipulation de données en général (y compris le tri, le filtrage, la sous-sélection, le résumé, etc.), vous devriez certainement jeter un coup d'œil à dplyr, qui vient avec une variété de fonctions toutes conçues pour faciliter votre travail spécifiquement avec des cadres de données et certains autres types de bases de données. Il offre même une interface SQL assez élaborée, et même une fonction pour convertir (le plus) du code SQL directement en R.

Les quatre fonctions liées à la jointure dans le paquet dplyr sont (pour citer):

  • inner_join(x, y, by = NULL, copy = FALSE, ...): retourne toutes les lignes de x où il y a des valeurs correspondantes dans y, et toutes les colonnes de x et y
  • left_join(x, y, by = NULL, copy = FALSE, ...): retourne toutes les lignes de x, et toutes les colonnes de x et y
  • semi_join(x, y, by = NULL, copy = FALSE, ...): retourne toutes les lignes de x où il y a des valeurs correspondantes y, en ne gardant que les colonnes de x.
  • anti_join(x, y, by = NULL, copy = FALSE, ...): retourne toutes les lignes de x où il n'y a pas de valeurs correspondantes en y, en ne gardant que les colonnes de x

C'est tout ici en détail.

La sélection des colonnes peut être effectuée par select(df,"column"). Si ce n'est pas assez SQL-ish pour vous, alors il y a le sql() fonction, dans laquelle vous pouvez entrer le code SQL tel quel, et il fera l'opération que vous avez spécifié, tout comme vous écriviez en R tout le long (pour plus d'informations, s'il vous plaît se référer à la dplyr / base de données vignette). Par exemple, s'il est appliqué correctement, sql("SELECT * FROM hflights") sélectionnera toutes les colonnes de la table dplyr "hflights" (un "tbl").


62
2018-01-29 17:43



Mise à jour des méthodes data.table pour joindre des ensembles de données. Voir ci-dessous des exemples pour chaque type de jointure. Il y a deux méthodes, une de [.data.table lors du passage second data.table comme premier argument à sous-ensemble, une autre façon est d'utiliser merge fonction qui a expédié à la méthode rapide data.table.

Mise à jour le 2016-04-01 - et ce n'est pas une blague d'imbéciles!
Dans la version 1.9.7 des jointures data.table, il est désormais possible d'utiliser l'index existant, ce qui réduit énormément le timing d'une jointure. Le code et le benchmark ci-dessous n'utilisent PAS les index data.table à la jointure. Si vous recherchez une jointure en temps réel, vous devez utiliser les index 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

En dessous des tests de banc d'essai, les bases R, sqldf, dplyr et data.table.
Benchmark teste les ensembles de données non indexés / non indexés. Vous pouvez obtenir des performances encore meilleures si vous utilisez des clés sur vos data.tables ou des index avec sqldf. Base R et dplyr n'ont pas d'index ou de clé, donc je n'ai pas inclus ce scénario dans l'indice de référence.
Le benchmark est effectué sur des ensembles de données 5M-1 lignes, il y a 5M-2 valeurs communes sur la colonne de jointure donc chaque scénario (gauche, droite, plein, intérieur) peut être testé et joint n'est pas trivial à effectuer.

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



dplyr depuis 0.4 implémenté toutes ces jointures y compris outer_join, mais il valait la peine de noter que pour les premières versions, il ne proposait pas outer_join, et par conséquent, il y avait beaucoup de code utilisateur de hacky contournant pendant un bon moment (vous pouvez toujours trouver cela dans les réponses SO et Kaggle de cette période). 

Join-related Faits saillants:

v0.5 (6/2016)

  • Gestion pour le type POSIXct, les fuseaux horaires, les doublons, les différents niveaux de facteur. Mieux erreurs et avertissements.
  • Nouvel argument de suffixe pour contrôler le suffixe des noms de variable dupliqués reçus (# 1296)

v0.4.0 (1/2015)

  • Implémenter la jointure droite et la jointure externe (# 96)
  • Mutations de jointures, qui ajoutent de nouvelles variables à une table à partir de lignes correspondantes dans une autre. Filtrage des jointures, qui filtrent les observations d'une table selon qu'elles correspondent ou non à une observation dans l'autre table.

v0.3 (10/2014)

  • On peut maintenant left_join par différentes variables dans chaque table: df1%>% left_join (df2, c ("var1" = "var2"))

v0.2 (5/2014)

  • * _join () ne réorganise plus les noms de colonnes (# 324)

v0.1.3 (4/2014)

Solutions de contournement par les commentaires de Hadley dans ce numéro:

  • right_join(x, y) est le même que left_join (y, x) en termes de lignes, seules les colonnes seront des ordres différents. Facilement travaillé avec select (new_column_order)
  • outer_join est fondamentalement union (left_join (x, y), right_join (x, y)) - c'est-à-dire conserve toutes les lignes dans les deux trames de données.

24
2018-04-13 10:39



En joignant deux trames de données avec ~ 1 million de lignes chacune, une avec 2 colonnes et l'autre avec ~ 20, j'ai trouvé de façon surprenante merge(..., all.x = TRUE, all.y = TRUE) être plus rapide alors dplyr::full_join(). C'est avec dplyr v0.4

La fusion prend ~ 17 secondes, full_join prend ~ 65 secondes.

Un peu de réflexion, puisque je par défaut dplyr généralement pour les tâches de manipulation.


18
2018-02-26 18:11