<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/how-to-perform-merges-joins-on-two-or-more-data-frames-with-base-r-tidyverse-and-data-table/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://jozefhajnala.gitlab.io/r/r006-merge/"> Jozef's Rblog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<div>
<h1>Introduction</h1>
<p>In this post in the <a rel="nofollow" href="http://jozefhajnala.gitlab.io/r/categories/rcase4base/" target="_blank">R:case4base</a> series we will look at one of the most common operations on multiple data frames – merge, also known as JOIN in SQL terms.</p>
<blockquote>
<p>We will learn how to do the 4 basic types of join – inner, left, right and full join with base R and show how to perform the same with tidyverse’s dplyr and data.table’s methods. A quick benchmark will also be included.</p>
</blockquote>
<div>
<img alt="Joins" src="https://i2.wp.com/jozefhajnala.gitlab.io/r/img/r006-01-joins-anim.gif?w=456&ssl=1" data-recalc-dims="1">
<p>Joins</p>
</div>
</div>
<div>
<h1>Contents</h1>
<ol>
<li><a rel="nofollow" href="https://jozefhajnala.gitlab.io/r/r006-merge/#merging-joining-two-data-frames-with-base-r" target="_blank">Merging (joining) two data frames with base R</a></li>
<li><a rel="nofollow" href="https://jozefhajnala.gitlab.io/r/r006-merge/#the-arguments-of-merge" target="_blank">The arguments of merge</a></li>
<li><a rel="nofollow" href="https://jozefhajnala.gitlab.io/r/r006-merge/#merging-multiple-data-frames" target="_blank">Merging multiple data frames</a></li>
<li><a rel="nofollow" href="https://jozefhajnala.gitlab.io/r/r006-merge/#alternatives-to-base-r" target="_blank">Alternatives to base R</a></li>
<li><a rel="nofollow" href="https://jozefhajnala.gitlab.io/r/r006-merge/#quick-benchmarking" target="_blank">Quick benchmarking</a></li>
<li><a rel="nofollow" href="https://jozefhajnala.gitlab.io/r/r006-merge/#tldr---just-want-the-code" target="_blank">TL;DR – Just want the code</a></li>
<li><a rel="nofollow" href="https://jozefhajnala.gitlab.io/r/r006-merge/#references" target="_blank">References</a></li>
</ol>
</div>
<div>
<h1>Merging (joining) two data frames with base R</h1>
<p>To showcase the merging, we will use a very slightly modified dataset provided by Hadley Wickham’s <a rel="nofollow" href="https://cran.r-project.org/package=nycflights13" target="_blank">nycflights13</a> package, mainly the <code>flights</code> and <code>weather</code> data frames. Let’s get right into it and simply show how to perform the different types of joins with base R.</p>
<p>First, we prepare the data and store the columns we will merge by (join on) into <code>mergeCols</code>:</p>
<pre><code>dataurl &lt;- &quot;https://jozefhajnala.gitlab.io/r/post/data/&quot;
weather &lt;- readRDS(url(paste0(dataurl, &quot;r006/weather.rds&quot;)))
flights &lt;- readRDS(url(paste0(dataurl, &quot;r006/flights.rds&quot;)))

mergeCols &lt;- c(&quot;time_hour&quot;, &quot;origin&quot;)

head(flights)</code></pre>
<pre><code>##   year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## 1 2013     1   1      517            515         2      830            819
## 2 2013     1   1      533            529         4      850            830
## 3 2013     1   1      542            540         2      923            850
## 4 2013     1   1      544            545        -1     1004           1022
## 5 2013     1   1      554            600        -6      812            837
## 6 2013     1   1      554            558        -4      740            728
##   arr_delay carrier flight tailnum origin dest air_time distance hour
## 1        11      UA   1545  N14228    EWR  IAH      227     1400    5
## 2        20      UA   1714  N24211    LGA  IAH      227     1416    5
## 3        33      AA   1141  N619AA    JFK  MIA      160     1089    5
## 4       -18      B6    725  N804JB    JFK  BQN      183     1576    5
## 5       -25      DL    461  N668DN    LGA  ATL      116      762    6
## 6        12      UA   1696  N39463    EWR  ORD      150      719    5
##   minute           time_hour
## 1     15 2013-01-01 05:00:00
## 2     29 2013-01-01 05:00:00
## 3     40 2013-01-01 05:00:00
## 4     45 2013-01-01 05:00:00
## 5      0 2013-01-01 06:00:00
## 6     58 2013-01-01 05:00:00</code></pre>
<pre><code>head(weather)</code></pre>
<pre><code>##   origin year month day hour  temp  dewp humid wind_dir wind_speed
## 1    EWR 2013     1   1    1 39.02 26.06 59.37      270   10.35702
## 2    EWR 2013     1   1    2 39.02 26.96 61.63      250    8.05546
## 3    EWR 2013     1   1    3 39.02 28.04 64.43      240   11.50780
## 4    EWR 2013     1   1    4 39.92 28.04 62.21      250   12.65858
## 5    EWR 2013     1   1    5 39.02 28.04 64.43      260   12.65858
## 6    EWR 2013     1   1    6 37.94 28.04 67.21      240   11.50780
##   wind_gust precip pressure visib           time_hour
## 1        NA      0   1012.0    10 2013-01-01 01:00:00
## 2        NA      0   1012.3    10 2013-01-01 02:00:00
## 3        NA      0   1012.5    10 2013-01-01 03:00:00
## 4        NA      0   1012.2    10 2013-01-01 04:00:00
## 5        NA      0   1011.9    10 2013-01-01 05:00:00
## 6        NA      0   1012.4    10 2013-01-01 06:00:00</code></pre>
<p>Now, we show how to perform the 4 merges (joins):</p>
<div>
<h3>Inner join</h3>
<pre><code>inner &lt;- merge(flights, weather, by = mergeCols)</code></pre>
</div>
<div>
<h3>Left (outer) join</h3>
<pre><code>left  &lt;- merge(flights, weather, by = mergeCols, all.x = TRUE)</code></pre>
</div>
<div>
<h3>Right (outer) join</h3>
<pre><code>right &lt;- merge(flights, weather, by = mergeCols, all.y = TRUE)</code></pre>
</div>
<div>
<h3>Full (outer) join</h3>
<pre><code>full &lt;- merge(flights, weather, by = mergeCols, all = TRUE)</code></pre>
</div>
<div>
<h3>Other join types</h3>
<pre><code># Cross Join (Cartesian product)
cross &lt;- merge(flights, weather, by = NULL)

# Natural Join
natural &lt;- merge(flights, weather)</code></pre>
</div>
</div>
<div>
<h1>The arguments of merge</h1>
<p>The key arguments of base <code>merge</code> data.frame method are:</p>
<ul>
<li><code>x, y</code> – the 2 data frames to be merged</li>
<li><code>by</code> – names of the columns to merge on. If the column names are different in the two data frames to merge, we can specify <code>by.x</code> and <code>by.y</code> with the names of the columns in the respective data frames. The <code>by</code> argument can also be specified by number, logical vector or left unspecified, in which case it defaults to the intersection of the names of the two data frames. From best practice perspective it is advisable to always specify the argument explicitly, ideally by column names.</li>
<li><code>all</code>, <code>all.x</code>, <code>all.y</code> – default to <code>FALSE</code> and can be used specify the type of join we want to perform:
<ul>
<li><code>all = FALSE</code> (the default) – gives an inner join – combines the rows in the two data frames that match on the <code>by</code> columns</li>
<li><code>all.x = TRUE</code> – gives a left (outer) join – adds rows that are present in <code>x</code>, even though they do not have a matching row in <code>y</code> to the result for <code>all = FALSE</code></li>
<li><code>all.y = TRUE</code> – gives a right (outer) join – adds rows that are present in <code>y</code>, even though they do not have a matching row in <code>x</code> to the result for <code>all = FALSE</code></li>
<li><code>all = TRUE</code> – gives a full (outer) join. This is a shorthand for <code>all.x = TRUE</code> and <code>all.y = TRUE</code></li>
</ul>
</li>
</ul>
<p>Other arguments include</p>
<ul>
<li><code>sort</code> – if <code>TRUE</code> (default), results are sorted on the <code>by</code> columns</li>
<li><code>suffixes</code> – length 2 character vector, specifying the suffixes to be used for making the names of columns in the result which are not used for merging unique</li>
<li><code>incomparables</code> – for single-column merging only, a vector of values that cannot be matched. Any value in <code>x</code> matching a value in this vector is assigned the <code>nomatch</code> value (which can be passed using <code>...</code>)</li>
</ul>
</div>
<div>
<h1>Merging multiple data frames</h1>
<p>For this example, let us have a list of all the data frames included in the <code>nycflights13</code> package, slightly updated such that they can me merged with the default value for <code>by</code>, purely for this exercise, and store them into a list called <code>flightsList</code>:</p>
<pre><code>flightsList &lt;- readRDS(url(paste0(dataurl, &quot;r006/nycflights13-list.rds&quot;)))
lapply(flightsList, function(x) c(toString(dim(x)), toString(names(x))))</code></pre>
<pre><code>## $flights
## [1] &quot;336776, 19&quot;                                                                                                                                                                     
## [2] &quot;year, month, day, dep_time, sched_dep_time, dep_delay, arr_time, sched_arr_time, arr_delay, carrier, flight, tailnum, origin, dest, air_time, distance, hour, minute, time_hour&quot;
## 
## $weather
## [1] &quot;26115, 15&quot;                                                                                                             
## [2] &quot;origin, year, month, day, hour, temp, dewp, humid, wind_dir, wind_speed, wind_gust, precip, pressure, visib, time_hour&quot;
## 
## $airlines
## [1] &quot;16, 2&quot;         &quot;carrier, name&quot;
## 
## $airports
## [1] &quot;1458, 8&quot;                                           
## [2] &quot;origin, airportname, lat, lon, alt, tz, dst, tzone&quot;
## 
## $planes
## [1] &quot;3322, 9&quot;                                                                            
## [2] &quot;tailnum, yearmanufactured, type, manufacturer, model, engines, seats, speed, engine&quot;</code></pre>
<p>Since <code>merge</code> is designed to work with 2 data frames, merging multiple data frames can of course be achieved by nesting the calls to merge:</p>
<pre><code>multiFull &lt;- merge(merge(merge(merge(
  flightsList[[1L]],
  flightsList[[2L]], all = TRUE),
  flightsList[[3L]], all = TRUE),
  flightsList[[4L]], all = TRUE),
  flightsList[[5L]], all = TRUE)</code></pre>
<p>We can however achieve this same goal much more elegantly, taking advantage of base R’s <code>Reduce</code> function:</p>
<pre><code># For Inner Join
multi_inner &lt;- Reduce(
  function(x, y, ...) merge(x, y, ...), 
  flightsList
)

# For Full (Outer) Join
multi_full &lt;- Reduce(
  function(x, y, ...) merge(x, y, all = TRUE, ...),
  flightsList
)</code></pre>
<p>Note that this example is oversimplified and the data was updated such that the default values for <code>by</code> give meaningful joins. For example, in the original <code>planes</code> data frame the column <code>year</code> would have been matched onto the <code>year</code> column of the <code>flights</code> data frame, which is nonsensical as the years have different meanings in the two data frames. This is why we renamed the <code>year</code> column in the <code>planes</code> data frame to <code>yearmanufactured</code> for the above example.</p>
</div>
<div>
<h1>Alternatives to base R</h1>
<div>
<h2>Using the tidyverse</h2>
<p>The <code>dplyr</code> package comes with a set of very user-friendly functions that seem quite self-explanatory:</p>
<pre><code>library(dplyr)
inner_dplyr &lt;- inner_join(flights, weather, by = mergeCols)
left_dplyr  &lt;- left_join(flights,  weather, by = mergeCols)
right_dplyr &lt;- right_join(flights, weather, by = mergeCols)
full_dplyr  &lt;- full_join(flights,  weather, by = mergeCols)</code></pre>
<p>We can also use the “forward pipe” operator <code>%>%</code> that becomes very convenient when merging multiple data frames:</p>
<pre><code>inner_dplyr &lt;- flights %>% inner_join(weather, by = mergeCols)
left_dplyr  &lt;- flights %>% left_join(weather,  by = mergeCols)
right_dplyr &lt;- flights %>% right_join(weather, by = mergeCols)
full_dplyr  &lt;- flights %>% full_join(weather,  by = mergeCols)</code></pre>
</div>
<div>
<h2>Using data.table</h2>
<p>The <code>data.table</code> package provides an S3 method for the <code>merge</code> generic that has a very similar structure to the base method for data frames, meaning its use is very convenient for those familiar with that method. In fact the code is exactly the same as the base one for our example use.</p>
<blockquote>
<p>One important difference worth noting is that the <code>by</code> argument is by default constructed differently with data.table.</p>
</blockquote>
<p>We however provide it explicitly, therefore this difference does not directly affect our example:</p>
<pre><code>library(data.table)
weather &lt;- as.data.table(weather)
flights &lt;- as.data.table(flights)
setkeyv(weather, mergeCols)
setkeyv(flights, mergeCols)

# Note that this is identical to the code for base 
# The data.table method is called automatically for objects of class data.table
inner_dt &lt;- merge(flights, weather, by = mergeCols)
left_dt  &lt;- merge(flights, weather, by = mergeCols, all.x = TRUE)
right_dt &lt;- merge(flights, weather, by = mergeCols, all.y = TRUE)
full_dt  &lt;- merge(flights, weather, by = mergeCols, all = TRUE)</code></pre>
<p>Alternatively, we can write <code>data.table</code> joins as subsets:</p>
<pre><code>inner_dt &lt;- flights[weather, on = mergeCols, nomatch = 0]
left_dt  &lt;- weather[flights, on = mergeCols]
right_dt &lt;- flights[weather, on = mergeCols]</code></pre>
</div>
</div>
<div>
<h1>Quick benchmarking</h1>
<p>For a quick overview, lets look at a basic benchmark without package loading overhead for each of the mentioned packages:</p>
<p><br>
<br>
</p>
<div>
<h2>Inner join</h2>
<pre><code>bench_inner &lt;- microbenchmark::microbenchmark(times = 100,
  base        = base::merge.data.frame(flights, weather, by = mergeCols),
  base_nosort = base::merge.data.frame(flights, weather, by = mergeCols, sort = FALSE),
  dt_merge    = merge(flights, weather, by = mergeCols),
  dt_subset   = flights[weather, on = mergeCols, nomatch = 0], 
  dplyr       = inner_join(flights, weather, by = mergeCols),
  dplyr_pipe  = flights %>% inner_join(weather, by = mergeCols)
)</code></pre>
<p></p>
<div></div>
</div>
<div>
<h2>Full (outer) join</h2>
<pre><code>bench_outer &lt;- microbenchmark::microbenchmark(times = 100,
  base        = base::merge.data.frame(flights, weather, by = mergeCols, all = TRUE),
  base_nosort = base::merge.data.frame(flights, weather, by = mergeCols, all = TRUE, sort = FALSE),
  dt_merge    = merge(flights, weather, by = mergeCols, all = TRUE),
  dplyr       = full_join(flights, weather, by = mergeCols),
  dplyr_pipe  = flights %>% full_join(weather, by = mergeCols)
)</code></pre>
<p></p>
<div></div>
<blockquote>
<p>Visualizing the results in this case shows base R comes way behind the two alternatives, even with <code>sort = FALSE</code>.</p>
</blockquote>
<p>Note: The benchmarks are ran on a <a rel="nofollow" href="https://www.digitalocean.com/docs/droplets/overview/" target="_blank">standard droplet</a> by DigitalOcean, with 2GB of memory a 2vCPUs.</p>
</div>
</div>
<div>
<h1>TL;DR – Just want the code</h1>
<blockquote>
<p>No time for reading? <a rel="nofollow" href="https://jozefhajnala.gitlab.io/r/post/data/r006-merge.r" target="_blank">Click here to get just the code with commentary</a></p>
</blockquote>
</div>
<div>
<h1>References</h1>
<ul>
<li>Animated <a rel="nofollow" href="https://github.com/gadenbuie/tidyexplain/raw/master/images/inner-join.gif" target="_blank">inner join</a>, <a rel="nofollow" href="https://github.com/gadenbuie/tidyexplain/raw/master/images/left-join.gif" target="_blank">left join</a>, <a rel="nofollow" href="https://github.com/gadenbuie/tidyexplain/raw/master/images/right-join.gif" target="_blank">right join</a> and <a rel="nofollow" href="https://video.twimg.com/tweet_video/DknFKJfU8AA9H_i.mp4" target="_blank">full join</a> by Garrick Aden-Buie for an easier understanding</li>
<li><a rel="nofollow" href="https://stat.ethz.ch/R-manual/R-devel/library/base/html/merge.html" target="_blank">Base merge help</a></li>
<li><a rel="nofollow" href="https://dplyr.tidyverse.org/reference/join.html" target="_blank">Join two tbls together with dplyr</a></li>
<li><a rel="nofollow" href="https://www.rdocumentation.org/packages/data.table/versions/1.11.8/topics/merge?tap_a=5644-dce66f&tap_s=10907-287229" target="_blank">Merge two data.tables</a></li>
<li><a rel="nofollow" href="https://rpubs.com/williamsurles/293454" target="_blank">Joining Data in R with dplyr</a> by Wiliam Surles</li>
<li><a rel="nofollow" href="https://en.wikipedia.org/wiki/Join_(SQL)" target="_blank">Join (SQL)</a> Wikipedia page</li>
<li><a rel="nofollow" href="https://cran.r-project.org/package=nycflights13" target="_blank">The nycflights13</a> package on CRAN</li>
</ul>
<blockquote>
<p><img alt="Coat of arms of Slovakia"><br>
Exactly 100 years ago tomorrow, October 28th, 1918 the <a rel="nofollow" href="https://en.wikipedia.org/wiki/First_Czechoslovak_Republic" target="_blank">independence of Czechoslovakia</a> was proclaimed by the Czechoslovak National Council, resulting in the creation of the <a rel="nofollow" href="https://en.wikipedia.org/wiki/History_of_Czechoslovakia_(1918%E2%80%9338)" target="_blank">first democratic state of Czechs and Slovaks</a> in history.</p>
</blockquote>
<h2>Did you find the article helpful or interesting? Help others find it by sharing</h2>
<div></div>
</div>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://jozefhajnala.gitlab.io/r/r006-merge/"> Jozef's Rblog</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/ppR--WPprxA" height="1"></a></div>