4.1: Combining Datasets (Joins & Binds)

Author

Ellen Bledsoe

Combining Datasets (Joins & Binds)

Learning Outcomes

  • Students will be able to explain the difference between joins and binds.
  • Students will be able to use joins to merge two datasets by a shared identifier.
  • Students will be able to use bind_rows() to append rows from one dataset to another.
  • Students will be able to export a combined dataset using write_csv().

The Context

After the series of incidents where a number of the collars made by Budget Collars LLC seem to be failing, our team decided to try and replace as many of them as possible. Besides, their battery life is far inferior.

We’re placing as many collars on as many seals as we can, and we are starting to run short on collars.

One of our intrepid data science team members found an old box of collars and some data on all of the collars (it was a real challenge getting this data off of an old floppy drive, but we managed).

Our Tasks

We’re going to spend the next lessons tackling two tasks:

1) First, we’ll work to join data sets together.

Our main goal is to create a single data set that includes the data we have previously looked at for collars, the data on the new collars, as well as the additional data on the old collars.

This is a little tricky, as the collar IDs need to be matched up with their counterparts across datasets, and new collars need to be added. Unfortunately, the new collars have IDs and some additional data, but we don’t know the maker of the new collars.

2) Second, we will try to identify the maker of mystery collars.

In order to do this, we will skim the surface of machine learning. We can use a common classification algorithm, K-Nearest Neighbors (KNN), to predict which maker made which of our unidentified collars. Don’t worry, we won’t go into too much detail, but having a general idea of how it works will be useful.

Combining Data (Joins and Binds)

Oftentimes, we have a lot of data for one project that are related but storing all of the data in one file would add unnecessary redundancy (e.g., data in certain rows would need to be repeated too often). Other times, data has been collected separately and needs to be combined before analysis.

Being able to join together data from related tables is a key skill in data science, and for working with larger data structures (databases with their own languages, like SQL).

The Data

Let’s load in the tidyverse and the data we’re working with.

library(tidyverse)
collars <- read_csv("data/collar_data.csv")
new_collars <- read_csv("data/new_collars.csv")
old_collars_new_data <- read_csv("data/old_collars_new_data.csv")

First, let’s explore our data. We want to focus on 2 things here:

  1. the columns: which ones match columns in other datasets
  2. collar identity: which datasets have matching collars or new collars
head(collars)
# A tibble: 6 × 5
  collar_id maker          battery_life signal_distance  fail
      <dbl> <chr>                 <dbl>           <dbl> <dbl>
1         1 Collarium Inc.        110.            4188.     0
2         2 Collarium Inc.         98.1           4166.     0
3         3 Collarium Inc.        103.            4183.     0
4         4 Collarium Inc.         97.8           4212.     0
5         5 Collarium Inc.        120.            4158.     0
6         6 Collarium Inc.        121.            4191.     0
head(new_collars)
# A tibble: 6 × 6
  collar_id maker battery_life signal_distance antenna_length weight
      <dbl> <lgl>        <dbl>           <dbl>          <dbl>  <dbl>
1       129 NA           104.            4322.           5.86   21.8
2       138 NA            91.2           4297.           6.78   20.1
3       134 NA            88.0           4279.           6.77   23.0
4       140 NA            79.9           4253.           7.91   22.9
5       104 NA           127.            4179.           4.82   23.3
6       118 NA           127.            4208.           5.67   23.6
head(old_collars_new_data)
# A tibble: 6 × 4
  collar_id maker              antenna_length weight
      <dbl> <chr>                       <dbl>  <dbl>
1        30 Collarium Inc.               6.74   20.2
2        51 Budget Collars LLC           5.99   25.1
3        60 Budget Collars LLC           5.56   18.4
4        19 Collarium Inc.               4.94   23.1
5        53 Budget Collars LLC           4.95   18.6
6        25 Collarium Inc.               5.17   24.4

Diagramming

In small groups, talk through the process of combining these three datasets. Think about the following:

  • which columns match and which ones don’t?
  • which rows match and which ones don’t?
  • does the order in which we combine datasets matter?

Draw out a diagram that represents how this process might go.

Instructor Note: collars and old_collars_new_data share collar_id and maker, these get joined.

new_collars has different collars entirely, these get appended with bind_rows().

The order matters: join first (to enrich the existing collar data), then bind (to add the new collars). If they bind first they will lose the ability to match on collar_id.

Joins vs. Binds

Now that we’ve decided on a process for how to combine our data, let’s figure out which functions we are going to use to accomplish this task.

We have 2 main methods of combining datasets, and they work in different ways.

Joins

Joins are arguably the more complicated of the two types of ways to combine data, but they are, therefore, the more flexible and useful.

The magic of joins comes because they match up columns of data based on unique identifiers in each row of data.

In the following diagram, the two example data frames have the column x1 in common, and each of the values in x1 are unique (no repeats in the same data frame). When combining the datasets, all of the columns are added, and their rows are matched up to their respective values in the x1 column.

This can happen a couple ways, depending on which data frame is the reference and how much data you want to retain.

There are four main types of joins, and they differ in which rows they keep:

  • left_join(): keeps all rows from the left (first) dataset and adds matching columns from the right dataset. (Rows in the right with no match are dropped, rows in the left with no match get NA for the right-side columns.)
  • right_join(): keeps all rows from the right dataset.
  • inner_join(): keeps only rows that have a match in both datasets.
  • full_join(): keeps all rows from both datasets, filling NA where there is no match.

For our task in Step 1, we are using left_join() because we want to keep all of our original collars (the left dataset) and add the new measurements from old_collars_new_data (the right dataset). Since every collar in collars has a matching entry in old_collars_new_data, a full_join() would actually give the same result here.

Binds

The other way we can combine data sets is through binds. Binds act similarly to gluing datasets together.

They don’t match up data based on unique identifiers; instead they match up data by column name (bind_rows()) or row position (bind_cols())

How should we go about combining our three datasets? Come up with a plan that you think will work.

Instructor Note: Use the same plan from the Diagramming section.

Join first, then bind. The common mistake is trying to bind_rows() everything first. If they do, point out that binding just stacks rows without matching on collar_id, so the old collars’ new measurements (antenna_length, weight) would no longer line up with the right collars.

Task 1: Combine Our Data

Step 1

Our first step is to merge the old collar data with the new data about those old collars.

# use a left join
# full join would accomplish the same thing in this case because we don't have any missing rows
old_collars_combined <- collars %>% 
  left_join(old_collars_new_data, by = c("collar_id", "maker"))

If we take a look at our new data frame, we should hopefully see that the values for antenna_length and weight have been matched up with their respective collar ID.

Could we have used another tactic to combine these two datasets? What would the pros and cons be?

Instructor Note: A full_join() would produce the same result here since all collar IDs appear in both datasets. The key advantage of left_join() is that it explicitly matches on collar_id and maker.

Step 2

Now we need to add the new collars to our dataset. What is our best method for combining?

full <- bind_rows(old_collars_combined, new_collars)

Let’s take a look at our new data frame! Have we correctly accomplished our first task?

head(full)
# A tibble: 6 × 7
  collar_id maker       battery_life signal_distance  fail antenna_length weight
      <dbl> <chr>              <dbl>           <dbl> <dbl>          <dbl>  <dbl>
1         1 Collarium …        110.            4188.     0           7.44   21.9
2         2 Collarium …         98.1           4166.     0           5.39   21.8
3         3 Collarium …        103.            4183.     0           4.82   24.8
4         4 Collarium …         97.8           4212.     0           5.27   18.2
5         5 Collarium …        120.            4158.     0           7.65   20.8
6         6 Collarium …        121.            4191.     0           4.95   23.0
tail(full)
# A tibble: 6 × 7
  collar_id maker battery_life signal_distance  fail antenna_length weight
      <dbl> <chr>        <dbl>           <dbl> <dbl>          <dbl>  <dbl>
1       141 <NA>          73.7           4279.    NA           7.53   23.4
2       121 <NA>         130.            4254.    NA           5.70   20.9
3       150 <NA>          74.3           4321.    NA           5.68   24.0
4       124 <NA>         123.            4161.    NA           5.37   23.1
5       109 <NA>         129.            4182.    NA           5.84   22.1
6       142 <NA>          85.8           4322.    NA           6.47   23.7

You’ll notice the new collars show NA for maker, that’s expected. We don’t know who made them yet, and predicting that missing maker is exactly what we’ll do with KNN in the next lesson.

Saving our New Data

Now that we’ve accomplished our first task, we are going to want to use this combined dataset to accomplish our next task, which is using a classification algorithm to help us predict which maker made the mystery collars.

To save our data as a .csv file that we can use in another analysis, we are going to use a function that exports the dataset (write_csv()) instead of importing it (read_csv()).

The write_csv() function requires the name of the dataframe to export as the first argument and the name of the file we want to create as the second argument.

write_csv(full, "data/all_collar_data.csv")

If we look over in our Files tab, you should see your new .csv file!