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.
Basic Application of merge Function in R. First, we need to create some data frames that we can. Source: R/join.r. The mutating joins add columns from y to x, matching rows based on the keys: innerjoin : includes all rows in x and y. Leftjoin : includes all rows in x. Rightjoin : includes all rows in y. Fulljoin : includes all rows in x or y.
To showcase the merging, we will use a very slightly modified dataset provided by Hadley Wickham’s nycflights13 package, mainly the
weather data frames. Let’s get right into it and simply show how to perform the different types of joins with base R.
First, we prepare the data and store the columns we will merge by (join on) into
Now, we show how to perform the 4 merges (joins):
Left (outer) join
R Join Multiple Columns
Full (outer) join
The key arguments of base
merge data.frame method are:
x, y- the 2 data frames to be merged
by- names of the columns to merge on. If the column names are different in the two data frames to merge, we can specify
by.ywith the names of the columns in the respective data frames. The
byargument 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.y- default to
FALSEand 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 the
all.x = TRUE- gives a left (outer) join - adds rows that are present in
x, even though they do not have a matching row in
yto the result for
all = FALSE
all.y = TRUE- gives a right (outer) join - adds rows that are present in
y, even though they do not have a matching row in
xto the result for
all = FALSE
all = TRUE- gives a full (outer) join. This is a shorthand for
all.x = TRUEand
all.y = TRUE
Other arguments include
TRUE(default), results are sorted on the
suffixes- 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
incomparables- for single-column merging only, a vector of values that cannot be matched. Any value in
xmatching a value in this vector is assigned the
nomatchvalue (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
merge is designed to work with 2 data frames, merging multiple data frames can of course be achieved by nesting the calls to merge:
We can however achieve this same goal much more elegantly, taking advantage of base R’s
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
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:
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:
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.
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.
R Join Columns Must Be Present In Data
Did you find this post helpful or interesting? Help others find it by sharing:
If you browse through our technical blog posts you’ll see quite a few devoted to the data analysis functionality in the R packge
dplyr. This is due to the fact that we are constantly finding fun new functions to play with. We wanted to devote this small post to an unexpectedly useful function called
anti_join() from the
For most data analysis tasks you may have two tables you want to join based on a common ID. This is straightforward in any data analysis package. But occasionally, especially in quality assurance types of settings, we find ourselves wanting to identify the records from one table that did NOT match the other table. For example,
anti_join came in handy for us in a setting where we were trying to re-create an old table from the source data. We then wanted to be able to identify the records from the original table that did not exist in our updated table. This is where
anti_join comes in, especially when you’re dealing with a multi-column ID.
We’ll start with a relatively simple example.
To identify the rows that exist in table1 but not in table2 you could use any number of strategies:
You might ask why
anti_join is an advance given the other easy solutions we’re showing above. We find it most useful when our common ID is a combination of multiple columns. So let’s use another example where we have a multi-column common ID:
With a two-column unique ID using
match() is more challenging. You could create a single ID by concatenating the state/county fields but this adds a messy extra step. Instead anti_join() is your savior: