# Assign the first object in the list to experiment_1 object
experiment_1 <- experimental_data[[1]]
# Assign the second object in the list to experiment_2 object
experiment_2 <- experimental_data[[2]]Merging tables
This lesson introduces the merge() function for combining tables in R.
R, Merge
Approximate time: 20 min
Learning Objectives
- Explain the difference between combining tables by position and by a key.
- Use
cbind()to combine columns when row order is known to match. - Use
merge()to combine tables based on a shared key. - Explain why row-aware methods are safer than position-based methods.
Merging Tables
In the process of data wrangling, oftentimes you will encounter a situation where you have two data frames that you’d like to combine into a single data frame. Let’s look at a few ways that you could approach this task.
Adding columns with cbind()
Let’s look at the two tables we read in during the Loops lesson. First, let’s assign the objects from the two slots in the list to objects in our environment:
# Print out experiment_1
experiment_1 Sample_ID Treatment Sex Replicate
1 1 Control M 1
2 2 Control M 2
3 3 Control M 3
4 4 Control M 4
5 5 Control F 1
6 6 Control F 2
7 7 Control F 3
8 8 Control F 4
9 9 Case M 1
10 10 Case M 2
11 11 Case M 3
12 12 Case M 4
13 13 Case F 1
14 14 Case F 2
15 15 Case F 3
16 16 Case F 4
# Print out experiment_2
experiment_2 Sample_ID Date_sampled Tissue
1 1 9/14/26 Brain
2 2 9/14/26 Stomach
3 3 10/11/26 Brain
4 4 10/11/26 Stomach
5 5 9/14/26 Brain
6 6 9/14/26 Stomach
7 7 10/11/26 Brain
8 8 10/11/26 Stomach
9 9 9/14/26 Brain
10 10 9/14/26 Stomach
11 11 10/11/26 Brain
12 12 10/11/26 Stomach
13 13 9/14/26 Brain
14 14 9/14/26 Stomach
15 15 10/11/26 Brain
16 16 10/11/26 Stomach
By inspecting the data, we could see that the data in experiment_1 and experiment_2 have the same length and the Sample_IDs are in the same order. In this case, we could use the cbind function to append columns to our data frame:
# Create combined_experiment data frame and add columns from experiment_2 using cbind()
combined_experiment <- cbind(experiment_1,
Tissue = experiment_2$Tissue,
Date_sampled = experiment_2$Date_sampled)
# Print out combined_experiment
combined_experiment Sample_ID Treatment Sex Replicate Tissue Date_sampled
1 1 Control M 1 Brain 9/14/26
2 2 Control M 2 Stomach 9/14/26
3 3 Control M 3 Brain 10/11/26
4 4 Control M 4 Stomach 10/11/26
5 5 Control F 1 Brain 9/14/26
6 6 Control F 2 Stomach 9/14/26
7 7 Control F 3 Brain 10/11/26
8 8 Control F 4 Stomach 10/11/26
9 9 Case M 1 Brain 9/14/26
10 10 Case M 2 Stomach 9/14/26
11 11 Case M 3 Brain 10/11/26
12 12 Case M 4 Stomach 10/11/26
13 13 Case F 1 Brain 9/14/26
14 14 Case F 2 Stomach 9/14/26
15 15 Case F 3 Brain 10/11/26
16 16 Case F 4 Stomach 10/11/26
Our combined_experiment object now has a data frame with the two datasets successfully combined. However, this only worked because the datasets were already aligned in the same order. Thus, position-based methods for merging data can work if the data is aligned properly.
Because cbind() is a position-based method, it combines objects column by column. It does not look at the values in Sample_ID or any other column. It assumes that the rows are already in the correct order. In other words, cbind() combines rows by their positions: the first row of one table is combined with the first row of the other table, the second row with the second row and so on.
Often, you will encounter data sets where the rows in each data frame are not aligned or where you do not know whether the rows are aligned. In this case, you’ll want to use key-based methods for merging tables. Key-based methods look for a common key provided by the user and shared between the tables to link the data together.
In order to demonstrate this, let’s reorder experiment_1 to be sorted by Replicate rather than Sample_ID:
# Rearrange experiment_1 by the Replicate column
experiment_1_replicate <- experiment_1 %>%
arrange(Replicate)Now if we try the cbind() approach we did above, we can see why this method could be dangerous to use:
# Print out the combined_experiment
combined_experiment Sample_ID Treatment Sex Replicate Tissue Date_sampled
1 1 Control M 1 Brain 9/14/26
2 2 Control M 2 Stomach 9/14/26
3 3 Control M 3 Brain 10/11/26
4 4 Control M 4 Stomach 10/11/26
5 5 Control F 1 Brain 9/14/26
6 6 Control F 2 Stomach 9/14/26
7 7 Control F 3 Brain 10/11/26
8 8 Control F 4 Stomach 10/11/26
9 9 Case M 1 Brain 9/14/26
10 10 Case M 2 Stomach 9/14/26
11 11 Case M 3 Brain 10/11/26
12 12 Case M 4 Stomach 10/11/26
13 13 Case F 1 Brain 9/14/26
14 14 Case F 2 Stomach 9/14/26
15 15 Case F 3 Brain 10/11/26
16 16 Case F 4 Stomach 10/11/26
# Use cbind on the rearranged experiment_1 dataset
combined_experiment_rearranged <- cbind(experiment_1_replicate,
Tissue = experiment_2$Tissue,
Date_sampled = experiment_2$Date_sampled)
# Print out the combined_experiment_rearranged
combined_experiment_rearranged Sample_ID Treatment Sex Replicate Tissue Date_sampled
1 1 Control M 1 Brain 9/14/26
2 5 Control F 1 Stomach 9/14/26
3 9 Case M 1 Brain 10/11/26
4 13 Case F 1 Stomach 10/11/26
5 2 Control M 2 Brain 9/14/26
6 6 Control F 2 Stomach 9/14/26
7 10 Case M 2 Brain 10/11/26
8 14 Case F 2 Stomach 10/11/26
9 3 Control M 3 Brain 9/14/26
10 7 Control F 3 Stomach 9/14/26
11 11 Case M 3 Brain 10/11/26
12 15 Case F 3 Stomach 10/11/26
13 4 Control M 4 Brain 9/14/26
14 8 Control F 4 Stomach 9/14/26
15 12 Case M 4 Brain 10/11/26
16 16 Case F 4 Stomach 10/11/26
Let’s take a close look at what happened by evaluating how Sample_ID 5 behaves in both tables. When the data was arranged correctly with the rows aligned, the result is:
# Print out Sample_ID 5 of the combined_experiment
combined_experiment[combined_experiment$Sample_ID == 5, ] Sample_ID Treatment Sex Replicate Tissue Date_sampled
5 5 Control F 1 Brain 9/14/26
But in our re-ordered dataset, we see that the data looks like:
# Print out Sample_ID 5 of the rearranged combined_experiment
combined_experiment_rearranged[combined_experiment_rearranged$Sample_ID == 5, ] Sample_ID Treatment Sex Replicate Tissue Date_sampled
2 5 Control F 1 Stomach 9/14/26
The tissues between both data frames are different! This is because when you used cbind to add columns to experiment_1, it did it in a way that was position-based and therefore unaware of the rows that we were combining on. The position-based approach only worked initially because the rows had been pre-sorted into the same order prior to combining them. As a result, combining tables in a position-based way is generally a poor strategy and combining rows using a key-based strategy is much safer!
Combining tables with merge()
The merge() function in R allows the user to combine tables in a key-based manner.
# Merge experiment_1 and experiment_2 by the key in the Sample_ID columns
merged_experiment <- merge(x = experiment_1,
y = experiment_2,
by = "Sample_ID")
# Print out the merged_experiment
merged_experiment Sample_ID Treatment Sex Replicate Date_sampled Tissue
1 1 Control M 1 9/14/26 Brain
2 2 Control M 2 9/14/26 Stomach
3 3 Control M 3 10/11/26 Brain
4 4 Control M 4 10/11/26 Stomach
5 5 Control F 1 9/14/26 Brain
6 6 Control F 2 9/14/26 Stomach
7 7 Control F 3 10/11/26 Brain
8 8 Control F 4 10/11/26 Stomach
9 9 Case M 1 9/14/26 Brain
10 10 Case M 2 9/14/26 Stomach
11 11 Case M 3 10/11/26 Brain
12 12 Case M 4 10/11/26 Stomach
13 13 Case F 1 9/14/26 Brain
14 14 Case F 2 9/14/26 Stomach
15 15 Case F 3 10/11/26 Brain
16 16 Case F 4 10/11/26 Stomach
The merge() function accepts two data frames and matches their rows using the column specified by the by argument. Here, rows are matched when they have the same key in Sample_ID. By default, merge() returns only rows with matching key values in both data frames. This behavior is called an inner join.
Let’s see how it performs with our rearranged dataset now:
# Create the merged dataframe with the rearranged dataset
merged_experiment_replicate <- merge(x = experiment_1_replicate,
y = experiment_2,
by = "Sample_ID")
# Print out merged_experiment_replicate
merged_experiment_replicate Sample_ID Treatment Sex Replicate Date_sampled Tissue
1 1 Control M 1 9/14/26 Brain
2 2 Control M 2 9/14/26 Stomach
3 3 Control M 3 10/11/26 Brain
4 4 Control M 4 10/11/26 Stomach
5 5 Control F 1 9/14/26 Brain
6 6 Control F 2 9/14/26 Stomach
7 7 Control F 3 10/11/26 Brain
8 8 Control F 4 10/11/26 Stomach
9 9 Case M 1 9/14/26 Brain
10 10 Case M 2 9/14/26 Stomach
11 11 Case M 3 10/11/26 Brain
12 12 Case M 4 10/11/26 Stomach
13 13 Case F 1 9/14/26 Brain
14 14 Case F 2 9/14/26 Stomach
15 15 Case F 3 10/11/26 Brain
16 16 Case F 4 10/11/26 Stomach
We can see that the values have been matched correctly, even though the input tables had different row orders. We can confirm that Sample_ID 5 is now the same in both tables:
# Print out Sample_ID 5 of the merged_experiment
merged_experiment[merged_experiment$Sample_ID == 5, ] Sample_ID Treatment Sex Replicate Date_sampled Tissue
5 5 Control F 1 9/14/26 Brain
# Print out Sample_ID 5 of the merged_experiment that was rearranged
merged_experiment_replicate[merged_experiment_replicate$Sample_ID == 5, ] Sample_ID Treatment Sex Replicate Date_sampled Tissue
5 5 Control F 1 9/14/26 Brain
Hopefully, it is clear why using a key-based method for merging together tables is much safer than using a position-based method like cbind().
left_join()/right_join() from dplyr
The dplyr package provides several join functions for combining tables. Two of these are left_join() and right_join(). Below is an example of how to use left_join().
# Use left_join() to combine tables by Sample_ID
left_join(x = experiment_1_replicate,
y = experiment_2,
by = "Sample_ID") Sample_ID Treatment Sex Replicate Date_sampled Tissue
1 1 Control M 1 9/14/26 Brain
2 5 Control F 1 9/14/26 Brain
3 9 Case M 1 9/14/26 Brain
4 13 Case F 1 9/14/26 Brain
5 2 Control M 2 9/14/26 Stomach
6 6 Control F 2 9/14/26 Stomach
7 10 Case M 2 9/14/26 Stomach
8 14 Case F 2 9/14/26 Stomach
9 3 Control M 3 10/11/26 Brain
10 7 Control F 3 10/11/26 Brain
11 11 Case M 3 10/11/26 Brain
12 15 Case F 3 10/11/26 Brain
13 4 Control M 4 10/11/26 Stomach
14 8 Control F 4 10/11/26 Stomach
15 12 Case M 4 10/11/26 Stomach
16 16 Case F 4 10/11/26 Stomach
left_join() and right_join() are mostly equivalent when both tables contain the same join keys. They differ when a key exists in one table but not the other.
left_join(x, y) retains all rows from the first table, x. If a row in x has no matching key in y, the columns contributed by y contain NA. Rows whose keys occur only in y are omitted.
right_join(x, y) retains all rows from the second table, y. If a row in y has no matching key in x, the columns contributed by x contain NA. Rows whose keys occur only in x are omitted.
The full_join() function also exists and it retains all rows from both tables and fills unmatched values with NA.
Create the following dataframes:
# Gene-expression measurements for five samples
gene_expression <- data.frame(
Sample_ID = c("S01", "S02", "S03", "S04", "S05"),
TP53_expression = c(12.4, 8.7, 15.1, 10.2, 9.8)
)
# Cytokine measurements for five samples
cytokine_data <- data.frame(
Samples = c("S03", "S01", "S06", "S02", "S04"),
IL6_concentration = c(4.8, 2.1, 7.3, 3.5, 5.2)
)# Print out gene_expression
gene_expression Sample_ID TP53_expression
1 S01 12.4
2 S02 8.7
3 S03 15.1
4 S04 10.2
5 S05 9.8
# Print out cytokine_data
cytokine_data Samples IL6_concentration
1 S03 4.8
2 S01 2.1
3 S06 7.3
4 S02 3.5
5 S04 5.2
- Use
merge()to combine the data frames without changing any of the column names.
Hint: Look up the arguments for by.x and by.y
Is
Sample_IDorSampleretained as the column name?How many rows are in the merged data frame?
Which samples are included in the result?
Which samples were excluded, and why?
Add the
all = TRUEargument to yourmergecommand. How does the output change?