Joining Data with dplyr

Lesson 8 of 14 in Coddy's Data Manipulation in R course.

Joining data is a crucial operation in data manipulation, allowing you to combine multiple data frames based on common columns. The dplyr package in R provides efficient functions for various types of joins. Let's explore the main join operations: inner join, left join, right join, and full join.

1. Preparing Data

First, let's create two sample data frames to demonstrate join operations:

# Load dplyr
library(dplyr)

# Create sample data frames
employees <- data.frame(
  emp_id = c(1, 2, 3, 4),
  name = c("Alice", "Bob", "Charlie", "David"),
  department = c("HR", "IT", "Finance", "IT")
)

salaries <- data.frame(
  emp_id = c(1, 2, 3, 5),
  salary = c(50000, 60000, 55000, 65000)
)

2. Inner Join

An inner join returns only the rows that have matching values in both data frames:

inner_join_result <- inner_join(employees, salaries, by = "emp_id")
print(inner_join_result)

Output:

  emp_id   name department salary
1      1  Alice         HR  50000
2      2    Bob         IT  60000
3      3 Charlie    Finance  55000

3. Left Join

A left join returns all rows from the left data frame and the matched rows from the right data frame:

left_join_result <- left_join(employees, salaries, by = "emp_id")
print(left_join_result)

Output:

  emp_id   name department salary
1      1  Alice         HR  50000
2      2    Bob         IT  60000
3      3 Charlie    Finance  55000
4      4  David         IT     NA

4. Right Join

A right join returns all rows from the right data frame and the matched rows from the left data frame:

right_join_result <- right_join(employees, salaries, by = "emp_id")
print(right_join_result)

Output:

  emp_id   name department salary
1      1  Alice         HR  50000
2      2    Bob         IT  60000
3      3 Charlie    Finance  55000
4      5   <NA>       <NA>  65000

5. Full Join

A full join returns all rows when there is a match in either left or right data frame:

full_join_result <- full_join(employees, salaries, by = "emp_id")
print(full_join_result)

Output:

  emp_id   name department salary
1      1  Alice         HR  50000
2      2    Bob         IT  60000
3      3 Charlie    Finance  55000
4      4  David         IT     NA
5      5   <NA>       <NA>  65000

6. Specifying Join Columns

If the column names are different in the two data frames, you can specify the join columns explicitly:

# Assuming 'salaries' has 'employee_id' instead of 'emp_id'
salaries_alt <- salaries %>% rename(employee_id = emp_id)

join_result <- inner_join(employees, salaries_alt, by = c("emp_id" = "employee_id"))
print(join_result)

7. Add New Columns

Adding or Modifying Columns with mutate() The mutate() function in dplyr allows you to add new columns or modify existing ones in a data frame. It's often used after join operations to perform calculations or transformations on the joined data:

Basic usage:

result <- data_frame %>% mutate(new_column = calculation)

Example:

# Add a bonus column to the joined data
result_with_bonus <- inner_join(employees, salaries, by = "emp_id") %>%
  mutate(bonus = salary * 0.1)

You can also use mutate() to perform more complex calculations or use conditional logic:

# Add a performance category based on salary
result_with_category <- inner_join(employees, salaries, by = "emp_id") %>%
	mutate(performance_category = ifelse(salary < 55000, "Needs Improvement",
                                  ifelse(salary < 60000, "Meets Expectations",
                                  "Exceeds Expectations")))

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

Challenge

Easy

Create a function that performs a series of join operations on two data frames containing information about books and their ratings. The function should:

Perform an inner join to combine books with their ratings
Perform a left join to include all books, even those without ratings
Perform a right join to include all ratings, even for books not in the book list
Perform a full join to include all books and all ratings
For each join result, calculate and add a column for the number of ratings
Return a list containing all four join results

Try it yourself

# Read all input lines
con <- file("stdin", "r")
all_lines <- suppressWarnings(readLines(con))

# Find the index where the second data frame starts
separator_index <- which(grepl("^book_id,rating,user_id", all_lines))

# Split the input into two parts
books_data <- all_lines[1:(separator_index - 1)]
ratings_data <- all_lines[separator_index:length(all_lines)]

# Load required library
suppressPackageStartupMessages(library(dplyr))

# Convert string inputs to data frames
books <- read.csv(text = paste(books_data, collapse = "\n"), stringsAsFactors = FALSE)
ratings <- read.csv(text = paste(ratings_data, collapse = "\n"), stringsAsFactors = FALSE)


# TODO: Write your code below
# Perform the required join operations and calculations

# Function to perform join operations
perform_joins <- function(books, ratings) {
  # Your code here
  
  # Return a list containing all four join results
  list(
    inner_join = NULL,
    left_join = NULL,
    right_join = NULL,
    full_join = NULL
  )
}

# Call the function and store the results
results <- perform_joins(books, ratings)

# Print the results
print(results)

All lessons in Data Manipulation in R

1Data Basics

Understanding Data Structures Dataframe Indexing Subsetting Data Data Type Conversion The Pipe Operator in R

4Advanced Manipulation

String Manipulation Date and Time Handling Custom Functions

2Data Transformation

Intro to dplyr Aggregating with dplyrJoining Data with dplyr

3Reshaping Data

Wide to Long Format Long to Wide Format Handling Missing Data