télécharger - word bordure de page personnalisée




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

É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, à savoir, le style sql, jointures ? C'est, comment puis-je obtenir:

  • Une 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.
  • Une 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 une 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?


  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)]

De la même façon

  • 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)]


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é étonnamment merge(..., all.x = TRUE, all.y = TRUE) pour être plus rapide que dplyr::full_join() . C'est avec dplyr v0.4

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

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


Il y a l'approche data.table 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 :
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 fonction de join trouvée dans le paquetage plyr

library(plyr)

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

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

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

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


Il y a quelques bons exemples de faire cela au Riki 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 ...")


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 existe deux méthodes, une de [.data.table lors du passage de second data.table en tant que premier argument à sous-ensemble, une autre façon est d'utiliser la fonction de merge 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 indices data.table lors de 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

Nouveau en 2014:

Surtout si vous êtes également intéressé par la manipulation de données en général (y compris le tri, filtrage, sous-ensemble, résumé, etc.), vous devriez certainement jeter un oeil à dplyr , qui vient avec une variété de fonctions conçues pour faciliter votre travail. cadres 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, ...) : renvoie toutes les lignes de x où y a des valeurs correspondantes dans y, en ne conservant 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 dans y, en ne gardant que les colonnes de x

Tout est here dans les moindres détails.

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


Vous pouvez faire des jointures en utilisant le dplyr paquetage dplyr 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)

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.

dplyr est très bon et performant. En plus des autres réponses, voici son statut de

v0.1.3 (4/2014)

Les commentaires de Per 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)) - ie conserve toutes les lignes dans les deux trames de données.




r-faq