{"id":298,"date":"2013-11-08T15:36:59","date_gmt":"2013-11-08T20:36:59","guid":{"rendered":"http:\/\/homepages.uc.edu\/~yaozo\/wordpress\/?p=298"},"modified":"2013-11-08T15:36:59","modified_gmt":"2013-11-08T20:36:59","slug":"how-to-join-data-frames-in-r-inner-outer-left-right","status":"publish","type":"post","link":"https:\/\/zhuoyao.net\/index.php\/2013\/11\/08\/how-to-join-data-frames-in-r-inner-outer-left-right\/","title":{"rendered":"How to join data frames in R (inner, outer, left, right)?"},"content":{"rendered":"
<\/div>\n
\n
\n\n\n\n\n
\u00a0<\/a><\/td>\n\n
\n

Given two data frames<\/p>\n

df1 = data.frame(CustomerId=c(1:6),Product=c(rep(\"Toaster\",3),rep(\"Radio\",3)))\ndf2 = data.frame(CustomerId=c(2,4,6),State=c(rep(\"Alabama\",2),rep(\"Ohio\",1)))\n\n> df1\n  CustomerId Product\n           1 Toaster\n           2 Toaster\n           3 Toaster\n           4   Radio\n           5   Radio\n           6   Radio\n\n> df2\n  CustomerId   State\n           2 Alabama\n           4 Alabama\n           6    Ohio<\/code><\/pre>\n
How can I do database style, i.e., sql style, joins? That is, how do I get:<\/p>\n
    \n
  • An inner join of df1 and df1<\/li>\n
  • An outer join of df1 and df2<\/li>\n
  • A left outer join of df1 and df2<\/li>\n
  • A right outer join of df1 and df2<\/li>\n<\/ul>\n
    P.S. IKT-JARQ (I Know This – Just Adding R Questions)<\/p>\n
    Extra credit:<\/p>\n
    How can I do a sql style select statement?<\/p>\n<\/div>\n
<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
<\/a><\/p>\n
\n\n\n\n\n
<\/td>\n\n
\n

By using the merge function and its optional parameters:<\/p>\n

Inner join:\u00a0merge(df1, df2)<\/code>\u00a0will work for these examples because R automatically joins the frames by common variable names, but you would most likely want to specify\u00a0merge(df1, df2, by=\"CustomerId\")<\/code>\u00a0to make sure that you were matching on only the fields you desired. You can also use the by.x and by.y parameters if the matching variables have different names in the different data frames.<\/p>\n

Outer join:\u00a0merge(x = df1, y = df2, by = \"CustomerId\", all = TRUE)<\/code><\/p>\n

Left outer:\u00a0merge(x = df1, y = df2, by = \"CustomerId\", all.x=TRUE)<\/code><\/p>\n

Right outer:\u00a0merge(x = df1, y = df2, by = \"CustomerId\", all.y=TRUE)<\/code><\/p>\n

Cross join:\u00a0merge(x = df1, y = df2, by = NULL)<\/code><\/p>\n

Just as with the inner join, you would probably want to explicitly pass “CustomerId” to R as the matching variable.<\/span>\u00a0I think it’s almost always best to explicitly state the identifiers on which you want to merge; it’s safer if the input data.frames change unexpectedly and easier to read later on.<\/p>\n<\/div>\n<\/td>\n<\/tr>\n

<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
<\/div>\n
\n\n\n\n
<\/td>\n\n
\n

I would recommend checking out\u00a0Gabor Grothendieck’s sqldf package<\/a>, which allows you to express these operations in SQL.<\/p>\n

library(sqldf)\n\n## inner join\ndf3 <- sqldf(\"SELECT CustomerId, Product, State \n       FROM df1 JOIN df2 USING(CustomerID)\")\n\n## left join (substitute 'right' for right join)\ndf4 <- sqldf(\"SELECT CustomerId, Product, State \n       FROM df1 LEFT JOIN df2 USING(CustomerID)\")<\/code><\/pre>\n
I find the SQL syntax to be simpler and more natural than its R equivalent (but this may just reflect my RDBMS bias).<\/p>\n
See\u00a0Gabor’s sqldf Google Code page<\/a>\u00a0for more information<\/p>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
\u00a0 Given two data frames 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… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-298","post","type-post","status-publish","format-standard","hentry","category-r"],"_links":{"self":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/298","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/comments?post=298"}],"version-history":[{"count":0,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/298\/revisions"}],"wp:attachment":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/media?parent=298"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/categories?post=298"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/tags?post=298"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}