22  Joining data together

Briefly go over the bigger picture (found in the introduction section) and remind everyone the ‘what’ and ‘why’ of what we are doing, and where we are at.

22.1 Learning objectives

  1. Describe some ways to join or “bind” data and identify which join is appropriate for a given situation. Then apply dplyr’s full_join() function to join two datasets together into one single dataset.

22.2 📖 Reading task: The many ways to join data

Time: ~10 minutes.

Walk through and describe these images and the different type of joins after they’ve read it.

The ability to join datasets together is a fundamental component of data processing and transformation. In many cases, it is required to do before you can meaningfully do any work. Like in our case, we have essentially three datasets that aren’t particularly useful on their own and only really benefit by being joined together. We join them to be able to make comparisons, analyse the variables together, and to work on one dataset rather than three.

But joining datasets can be tricky. There are many ways to join datasets and you often have to do several checks of the data and prepare it in the right way for your needs, like we’ve done, to get a useful and correct output in the end. It can be really easy to not realise that some data was dropped or not included or that extra data was added. It can be extremely frustrating to check and confirm things, but ultimately necessary to continue with doing your analyses and work correctly.

There are many ways to join datasets in dplyr, all of which are described in the ?dplyr::join and ?dplyr::semi_join help documentation. While there are several, only a few are most commonly used. These are left_join(), right_join(), and full_join().

left_join(x, y)

Join all rows and columns in y that match rows and columns in x. Columns that exist in y but not x are joined to x. The number of rows in the joined data is equal to the number of rows in x. If there are some rows in y that don’t match with x, those rows are not included, and so are assigned NA values in the output data.

A diagram showing two data frames, one green and one blue, with three columns each. Each data frame has one different column and one different row. The output data frame shows that all columns are output, but only the rows that match with the left data frame (the green one) are included in the output.
Figure 22.1: Left joining in dplyr. Notice how the last row in the blue data (the row with d in column A) is not included in the outputted data on the right. Modified from the Posit dplyr cheat sheet. Image license is CC-BY-SA.
right_join(x, y)

The opposite of left_join(). Join all rows and columns in x that match rows and columns in y. Columns that exist in x but not y are joined to y. Like left_join(), but the opposite, the number of rows in the joined data is equal to the number of rows in y. If there are some rows in x that don’t match with y, those rows are not included and are given an NA value. This function can be useful when used while piping with |> when you are processing data in a sequence of steps and want to continue piping but join a different way then left_join().

A diagram showing two data frames, one green and one blue, with three columns each. Each data frame has one different column and one different row. The output data frame shows that all columns are output, but only the rows that match with the right data frame (the blue one) are included in the output.
Figure 22.2: Right joining in dplyr. Notice how the last row in the green data (the row with c in column A) is not included in the outputted data on the right. Modified from the Posit dplyr cheat sheet. Image license is CC-BY-SA.
full_join(x, y)

Join all rows and columns in y that match rows and columns in x. Columns and rows that exist in y but not x are joined to x. A full join keeps all the data from both x and y. This can be useful if you want to make sure you have all the data from both datasets, even if it results in more missing values in the output data.

A diagram showing two data frames, one green and one blue, with three columns each. Each data frame has one different column and one different row. The output data frame shows that all columns are output, and all rows are included in the output, even if they don't match with the other data frame.
Figure 22.3: Full joining in dplyr. Notice how all rows and columns are included in the outputted data on the right, and that some missingness is introduced because those values don’t exist when the data are combined in this way. Modified from the Posit dplyr cheat sheet. Image license is CC-BY-SA.
CautionSticky/hat up!

When you’re ready to continue, place the sticky/paper hat on your computer to indicate this to the teacher 👒 🎩

Caution

👷 This part is being updated. Once ready, it will be shown here.

22.3 Key takeaways

Quickly cover this and get them to do the survey before moving on to the discussion activity.

  • Knowing how to join datasets together is a fundamental part of data processing and transformation. But it can be also tricky and a source of unexpected issues later on if not done well or not checked.
  • Use left_join(), right_join(), and full_join() to join two datasets together.

22.4 Code used in session

This lists some, but not all, of the code used in the section. Some code is incorporated into Markdown content, so is harder to automatically list here in a code chunk. The code below also includes the code from the exercises.

participant_details |>
  left_join(cgm_data)
participant_details |>
  right_join(cgm_data)
participant_details |>
  full_join(cgm_data)
dime_data <- participant_details |>
  full_join(cgm_data)
dime_data
dime_data <- participant_details |>
  full_join(cgm_data) |>
  full_join(sleep_data)
dime_data
dime_data <- participant_details |>
  full_join(cgm_data) |>
  full_join(sleep_data)
write_csv(dime_data, here("data/dime.csv"))
usethis::use_git_ignore("data/dime.csv")
save.image(here::here("_temp/joins.RData"))