Joining multiple datasets on the same column in R using dplyr and purrr
Tags: datascience, r, tidyverse, beginners
Published: 2023-01-28 17:22:00

Joining **multiple datasets** on the same column is a common pattern in data preparation

So let's jump in and explore how we can **leverage R** and the **tidyverse** to join an arbitrary number of datasets on a shared column with **elegant, readable code**!

## Table of Contents 
- [Installing prerequisite packages](#installing-prerequisite-packages)
- [Examining our sample datasets](#examining-our-sample-datasets)
- [Using dplyr to manually join two datasets at a time](#using-dplyr)
- [Understanding the reduce operation](#understanding-the-reduce-operation)
- [Leveraging purrr to join multiple datasets](#leveraging-purrr-reduce)
- [Conclusion](#conclusion)
- [Additional resources](#additional-resources)

{% embed https://www.christophergreening.com %}

---

## Installing prerequisite packages
<a src="#installing-prerequisite-packages"></a>

In this tutorial we'll be using [`dplyr`](https://dplyr.tidyverse.org/) and [`purrr`](https://purrr.tidyverse.org/) from the popular [tidyverse](https://www.tidyverse.org/) collection of packages

The following line of code will **install** them on your machine if they aren't already:

```R
install.packages(c("dplyr", "purrr"))
```

---

## Examining our sample datasets
<a src="#examining-our-sample-datasets"></a>

For the following examples, we'll be using **real-world agricultural data** sourced via [Eurostat](https://ec.europa.eu/eurostat) containing the number of specific livestock animals (`swine`, `bovine`, `sheep`, and `goats`) in a `country` during a given `year`

For example, here is the `goats` dataset
```R
> goats
# A tibble: 1,322 × 3
   country  year goats
   <chr>   <dbl> <dbl>
 1 Albania  2014 904
 2 Albania  2015 932
 3 Albania  2016 941.
 4 Albania  2017 933.
 5 Albania  2018 917.
 6 Albania  2019 863.
 7 Albania  2020 774.
 8 Austria  1993  47
 9 Austria  1994  49.8
10 Austria  1995  54.2
# … with 1,312 more rows
```

Our goal is to **join these datasets** by `country` and `year` into a single `livestock.data` variable containing all the animals like so:

```R
> livestock.data
# A tibble: 1,322 × 6
   country  year bovine goats swine sheep
   <chr>   <dbl>  <dbl> <dbl> <dbl> <dbl>
 1 Albania  2014   500. 904    172. 1896
 2 Albania  2015   506. 932    177. 1918
 3 Albania  2016   497. 941.   184. 1972.
 4 Albania  2017   475. 933.   180. 1926.
 5 Albania  2018   467. 917.   184. 1864.
 6 Albania  2019   416. 863.   184. 1758.
 7 Albania  2020   363. 774.   158. 1558.
 8 Austria  1993  2334.  47     NA   334
 9 Austria  1994  2329.  49.8 3729.  342.
10 Austria  1995  2326.  54.2 3706.  365.
# … with 1,312 more rows
```

---

## Using dplyr::full_join to manually join two datasets at a time
<a src="#using-dplyr"></a>

Let's start with a naive approach and **manually** join our datasets one-by-one on the `country` and `year` columns

```R
by = c("country", "year")
livestock.data <- dplyr::full_join(bovine, goats, by=by)
livestock.data <- dplyr::full_join(livestock.data, swine, by=by)
livestock.data <- dplyr::full_join(livestock.data, sheep, by=by)
```

The above code accomplishes the exercise by:
1. **Manually** stepping through each animal variable
2. **applying a function** that takes two arguments (*in this case* `dplyr::full_join`)
3. and chaining the **output** of one step (`livestock.data`) as the **input** for the next step

While this might work for four datasets, what if we had 100 datasets? 1000 datasets? _n datasets?!_ Suddenly **not** a great solution! 

Let's investigate how we can **improve, automate, and scale** this

---

## Understanding the reduce operation
<a src="#understanding-the-reduce-operation"></a> 

The **reduce** operation is a technique that **combines** all the elements of an **array** (*i.e. an array containing our individual livestock datasets*) into a **single value** (*i.e. the final joined table*).   

The reduce operation accomplishes this by:
1. **Looping** over an array
2. **applying a function** that takes two arguments (*such as* `dplyr::full_join`) 
3. and chaining the **output** of one step as the **input** for the next step

Sound familiar? This is *exactly* what we just performed manually in the previous section except this time we'll be **leveraging R** to do it for us! 

So let's see in practice how we can apply the reduce operation to elegantly join our `livestock.data`

---

## Leveraging purrr::reduce to join multiple datasets
<a src="#leveraging-purrr-reduce"></a>

`purrr` is a package that enhances R's **functional programming toolkit** for working with functions and vectors (*i.e. reducing, mapping, filtering, etc.*)

In this case, we're going to use [`purrr::reduce`](https://purrr.tidyverse.org/reference/reduce.html) in conjunction with [`dplyr::full_join`](https://dplyr.tidyverse.org/reference/mutate-joins.html) to join all of our datasets in one line of **concise, readable code**

```R
livestock.data <- purrr::reduce(
    list(bovine, goats, swine, sheep),
    function(left, right) {
      dplyr::full_join(left, right, by=c("country", "year"))
    }
)
```

And that's it! We accomplished this by:

- 1. **Looping** over a list of our livestock
```R
list(bovine, goats, swine, sheep)
```
- 2. **applying** `dplyr::full_join` which takes two arguments 

```R
function(left, right) {
  dplyr::full_join(left, right, by=c("country", "year"))
}
```

- 3. and chaining the **output** of one step as the **input** for the next step

![Image showing the different datasets joining together in a hierarchical chain that starts with bovine and goats joining into livestock.data, livestock.data joining with swine, and livestock.data finally joining with sheep](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/83uutcma5qxk448vttnn.PNG)

---

## Conclusion
<a src="#conclusion"></a>

Thus the elegance of the reduce operation comes from its **flexibility** to handle _n_ datasets (*assuming your machine has enough memory* :stuck_out_tongue_winking_eye:)

If you want to take this a step further and **practice** with sample code and data, I've pulled together a **full working example** for you to explore on [GitHub](https://github.com/chris-greening/chris-greening-blog/blob/main/posts/Performing%20multiple%20joins%20in%20R%20using%20dplyr%20and%20purrr/code/sample_code.R)!

**Thanks so much for reading** and if you liked my content, be sure to check out some of my other work or **connect** with me on [social media](https://www.linkedin.com/in/chris-greening-dev/) or my [**personal website**](https://www.christophergreening.com/) :smile: 

{% embed https://www.christophergreening.com %}

Cheers!

{% embed https://dev.to/chrisgreening/deploying-a-free-tier-relational-database-with-amazon-rds-3jd2 %}
{% embed https://dev.to/chrisgreening/connecting-to-a-relational-database-using-sqlalchemy-and-python-1619 %} 

---

## Additional resources
<a src="#additional-resources"></a>

- [purrr cheat sheet](https://github.com/rstudio/cheatsheets/blob/main/purrr.pdf)
- [dplyr cheat sheet](https://www.rstudio.com/wp-content/uploads/2015/02/data-wrangling-cheatsheet.pdf)
- [Reduction operator (Wikipedia)](https://en.wikipedia.org/wiki/Reduction_operator)
- [Advanced R by Hadley Wickham](https://adv-r.hadley.nz/functionals.html#reduce)
- [Tidyverse](https://www.tidyverse.org/)