October 27, 2018![How How](/uploads/1/3/7/5/137531291/597849886.png)
Merge – adds variables to a dataset. This document will use –merge– function. Merging two datasets require that both have at least one variable in common (either string or numeric). If string make sure the categories have the same spelling (i.e. Country names, etc.). Explore each dataset separately before merging. Make sure to use all. Datasets in the ‘datasets’ package. Many datasets are included in a package called datasets which is distributed with R so these datasets are instantly available to you for use. For example, two datasets namely cars and pressure are included in this default datasets package. Combining two data sets is easy if the data have a mutual identifier column or if a combination of mutual columns can be used as identifiers. Here we'll use inner.join function from the dplyr library to combine the data (remember the dplyr cheatsheet!). This means that we'll only keep the students who answered the questionnaire in both math and portuguese classes.
In this post in the R:case4base series we will look at one of the most common operations on multiple data frames - merge, also known as JOIN in SQL terms.
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.
To showcase the merging, we will use a very slightly modified dataset provided by Hadley Wickham’s nycflights13 package, mainly the
flights
and weather
data frames. Let’s get right into it and simply show how to perform the different types of joins with base R.![How How](/uploads/1/3/7/5/137531291/597849886.png)
First, we prepare the data and store the columns we will merge by (join on) into
mergeCols
:Now, we show how to perform the 4 merges (joins):
Left (outer) join
Full (outer) join
The key arguments of base
merge
data.frame method are:x, y
- the 2 data frames to be mergedby
- names of the columns to merge on. If the column names are different in the two data frames to merge, we can specifyby.x
andby.y
with the names of the columns in the respective data frames. Theby
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.all
,all.x
,all.y
- default toFALSE
and can be used specify the type of join we want to perform:all = FALSE
(the default) - gives an inner join - combines the rows in the two data frames that match on theby
columnsall.x = TRUE
- gives a left (outer) join - adds rows that are present inx
, even though they do not have a matching row iny
to the result forall = FALSE
all.y = TRUE
- gives a right (outer) join - adds rows that are present iny
, even though they do not have a matching row inx
to the result forall = FALSE
all = TRUE
- gives a full (outer) join. This is a shorthand forall.x = TRUE
andall.y = TRUE
Other arguments include
sort
- ifTRUE
(default), results are sorted on theby
columnssuffixes
- 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 uniqueincomparables
- for single-column merging only, a vector of values that cannot be matched. Any value inx
matching a value in this vector is assigned thenomatch
value (which can be passed using..
)
For this example, let us have a list of all the data frames included in the
nycflights13
package, slightly updated such that they can me merged with the default value for by
, purely for this exercise, and store them into a list called flightsList
:Since
merge
is designed to work with 2 data frames, merging multiple data frames can of course be achieved by nesting the calls to merge: Curse sigil.We can however achieve this same goal much more elegantly, taking advantage of base R’s
Reduce
function:Note that this example is oversimplified and the data was updated such that the default values for
by
give meaningful joins. For example, in the original planes
data frame the column year
would have been matched onto the year
column of the flights
data frame, which is nonsensical as the years have different meanings in the two data frames. This is why we renamed the year
column in the planes
data frame to yearmanufactured
for the above example.Using the tidyverse
The
dplyr
package comes with a set of very user-friendly functions that seem quite self-explanatory:We can also use the “forward pipe” operator
%>%
that becomes very convenient when merging multiple data frames:Using data.table
The
data.table
package provides an S3 method for the merge
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.One important difference worth noting is that the
by
argument is by default constructed differently with data.table.We however provide it explicitly, therefore this difference does not directly affect our example:
Alternatively, we can write
data.table
joins as subsets:For a quick overview, lets look at a basic benchmark without package loading overhead for each of the mentioned packages:
Inner join
How To Join Two Datasets In R Studio
Full (outer) join
Visualizing the results in this case shows base R comes way behind the two alternatives, even with
sort = FALSE
.Note: The benchmarks are ran on a standard droplet by DigitalOcean, with 2GB of memory a 2vCPUs.
List Of R Datasets
No time for reading? Click here to get just the code with commentary
- Animated inner join, left join, right join and full join by Garrick Aden-Buie for an easier understanding
- Joining Data in R with dplyr by Wiliam Surles
- Join (SQL) Wikipedia page
- The nycflights13 package on CRAN
Exactly 100 years ago tomorrow, October 28th, 1918 the independence of Czechoslovakia was proclaimed by the Czechoslovak National Council, resulting in the creation of the first democratic state of Czechs and Slovaks in history.