Joining Data with `left_join()`

1. The Concept of a Mutating Join

When we combine datasets, we are typically performing what dplyr calls a "mutating join." This means we are adding new columns (variables) from a second data frame (y) to a primary, or "base," data frame (x), matching rows based on a shared "key" variable, like a patient_id.

The book "Reproducible Medical Research with R" provides a clear overview of this concept. We will focus on left_join, which is the most common type of join you will encounter.

The core principle of a left_join(x, y) is:

• Keep all rows from the left data frame, x.
• Look for matching rows in the right data frame, y, based on the key variable(s).
• If a match is found, add the columns from y to the corresponding row of x.
• If a row in x has no match in y, the new columns are filled with NA (Not Available).
• Rows in y that have no match in x are discarded.

This animation provides an excellent visual of the process. The data frame on the left (x) is our base. Notice how it keeps all its original rows (1, 2, 3), while pulling in matching data from the right data frame (y). Row 4 from y is dropped because it has no corresponding key in x.

2. Performing a left_join

Let's see this in action. The syntax is straightforward, especially within a pipe chain. The video from The Rise Academy uses a helpful Venn diagram to explain the logic and then walks through a clear, practical example.

As you saw, the basic structure is left_join(x, y, by = "key_column"). The by argument tells dplyr which column(s) to use for matching. If the key columns have the same name in both data frames, you can simply provide the name in quotes.

3. Joining on Columns with Different Names

In the real world, your key columns will not always have the same name. For example, a demographics table might use patient_id while a lab results table uses subject_identifier. dplyr handles this gracefully.

"The Epidemiologist R Handbook" is an excellent resource that mirrors the kind of work you aim to do. It walks through the complete syntax for joins, including how to handle mismatched column names.

The key syntax to remember is by = c("column_in_x" = "column_in_y"). The name on the left of the = corresponds to the column in the left data frame, and the name on the right corresponds to the column in the right data frame.

4. Let's Practice

Now, let's apply left_join() to our clinical trial data. In our previous work, we used the sGS column (Surgical Gleason Score), which is a numeric rating of cancer aggressiveness. Let's say we have a separate key, provided by a pathologist, that maps these numeric scores to descriptive risk categories. Our task is to join this information into our main dataset.

Open your clinical-trial-practice RStudio Project and create a new R script named 13-joining-data.R.

1. Load packages and recreate the main data frame. Add this code to your script and run it.

   library(tidyverse)
   library(medicaldata)
   
   blood_storage_modified <- blood_storage %>%
     mutate(
       PSADensity = PreopPSA / PVol,
       AgeCat = case_when(
         Age < 70 ~ "Under 70",
         Age >= 70 ~ "70 and over"
       )
     )
   

2. Create the "key" data frame. This table provides the textual description for each Gleason score. Run this code to create it.

   gleason_risk_key <- tibble::tribble(
     ~score, ~risk_category,
     6,      "Low Grade",
     7,      "Intermediate Grade",
     8,      "High Grade",
     9,      "High Grade",
     10,     "Very High Grade"
   )
   
   
   
   
   # Let's look at our key
   print(gleason_risk_key)
   

   You will see a small table mapping the numeric score to its risk_category.

3. Your Task: Join the gleason_risk_key information into the blood_storage_modified data frame.

   • The blood_storage_modified data frame should be your left data frame.
   • The gleason_risk_key data frame should be your right data frame.
   • The key column in blood_storage_modified is sGS. The key column in gleason_risk_key is score.
   • Use left_join() and the correct by syntax for mismatched column names.
   • Store the result in a new object called full_clinical_data.
   • After the join, use glimpse(full_clinical_data) to inspect the result. You should see a new risk_category column at the end.

full_clinical_data <- blood_storage_modified %>%
  left_join(gleason_risk_key, by = c("sGS" = "score"))




# Inspect the result
glimpse(full_clinical_data)




# You can also view a summary of the new column to see the result of the join
full_clinical_data %>%
  count(sGS, risk_category)




# --- Output of count() ---
# # A tibble: 7 × 3
#     sGS risk_category        n
#   <fct> <chr>            <int>
# 1 5     NA                   2
# 2 6     Low Grade           67
# 3 7     Intermediate Grade 233
# 4 8     High Grade          26
# 5 9     High Grade          15
# 6 10    Very High Grade      1
# 7 NA    NA                   1
#
# Notice the NA for sGS 5, because it wasn't in our gleason_risk_key table.

Common Pitfalls

Joining data can sometimes fail or produce unexpected results. Always check your work with glimpse() or count(). The most common reasons for a failed join are:

• Mismatched data types: Trying to join a character column (e.g., "01") to a numeric column (e.g., 1). They look the same but are not. Use glimpse() to check data types beforehand.
• Mismatched values: Subtle differences like "St. Mark's" vs. "St Marks" or extra whitespace can cause matches to fail. Careful data cleaning, as seen in the Epidemiologist Handbook, is often a prerequisite for joining.

Conclusion

You have now added one of the most fundamental data wrangling skills to your toolkit: combining datasets with left_join(). You've learned how to merge tables based on key columns, even when their names don't match, and you understand that this process can introduce missing values. This ability to synthesize information from multiple sources is a massive step towards conducting comprehensive data analysis for your research goals.

Key Takeaways:

• left_join(x, y) is a mutating join that adds columns from y to x.
• It keeps all rows from the left data frame (x).
• Rows in x with no match in y will have NA in the newly added columns.
• The by argument specifies the key column(s) for matching. Use by = "key" if names are the same, and by = c("key_x" = "key_y") if they differ.
• Always inspect your data after a join to ensure it worked as expected.

Preview of the Next Lesson:
Our practice exercise demonstrated a common outcome of joining data: the creation of NA values. Handling these missing values is a critical step in preparing a dataset for analysis and visualization. In the next lesson, we will focus specifically on this task, learning how to use tidyr::drop_na() to selectively remove rows that have missing data in important columns.