How to Merge Two Data Frames By Column in R
04.18.2021
Let's say you have a list of users in one data frame and a list of their purchases in a second data frame. You'd like to combine these data frames into one based on the user id. In this article, we will learn how to use joins in R to combine data frames by column.
The basic way to merge two data frames is to use the merge function. We supply the two data frames and the column that we want to merge on.
users <- data.frame(
userId = c(1, 2, 3, 4),
name = c("Moe", "Larry", "Curly", "Harry")
)
purchases <- data.frame(
userId = c(1, 2, 3, 3, 4),
products = c("Prod1", "Prod2", "Prod3", "Prod1", "Prod2")
)
merge(users, purchases, by="userId")
# userId name products
# 1 1 Moe Prod1
# 2 2 Larry Prod2
# 3 3 Curly Prod3
# 4 3 Curly Prod1
# 5 4 Harry Prod2This type of join is known as an "inner join" and will include only items that match. For example, if one of the users didn't have a purchase, it would not be shown. Let's take a look at the same merge, but remove the purchase from Larry.
users <- data.frame(
userId = c(1, 2, 3, 4),
name = c("Moe", "Larry", "Curly", "Harry")
)
purchases <- data.frame(
userId = c(1, 3, 3, 4),
products = c("Prod1", "Prod3", "Prod1", "Prod2")
)
merge(users, purchases, by="userId")
# userId name products
# 1 1 Moe Prod1
# 2 3 Curly Prod3
# 3 3 Curly Prod1
# 4 4 Harry Prod2If we want to show Larry, even though they don't have a purchase, we can use a left join which will join and keep everything in our left table (which is users in this case). To do this, we use the all.x= TRUE property on the merge function.
users <- data.frame(
userId = c(1, 2, 3, 4),
name = c("Moe", "Larry", "Curly", "Harry")
)
purchases <- data.frame(
userId = c(1, 3, 3, 4),
products = c("Prod1", "Prod3", "Prod1", "Prod2")
)
merge(users, purchases, by="userId", all.x= TRUE)
# userId name products
# 1 1 Moe Prod1
# 2 2 Larry <NA>
# 3 3 Curly Prod3
# 4 3 Curly Prod1
# 5 4 Harry Prod2In a similar manner, if we have a product, but we don't have the user data, maybe the user was deleted, we can use a right join to display the product.
users <- data.frame(
userId = c(1, 2, 3, 4),
name = c("Moe", "Larry", "Curly", "Harry")
)
purchases <- data.frame(
userId = c(1, 3, 3, 4, 5),
products = c("Prod1", "Prod3", "Prod1", "Prod2", "OrphanProduct")
)
merge(users, purchases, by="userId", all.y=TRUE)
# userId name products
# 1 1 Moe Prod1
# 2 3 Curly Prod3
# 3 3 Curly Prod1
# 4 4 Harry Prod2
# 5 5 <NA> OrphanProductIf we would like to do both the left and right, we can use the outer join. This will show both tables even if there are missing values.
users <- data.frame(
userId = c(1, 2, 3, 4),
name = c("Moe", "Larry", "Curly", "Harry")
)
purchases <- data.frame(
userId = c(1, 3, 3, 4, 5),
products = c("Prod1", "Prod3", "Prod1", "Prod2", "OrphanProduct")
)
merge(users, purchases, by="userId", all=TRUE)
# userId name products
# 1 1 Moe Prod1
# 2 2 Larry <NA>
# 3 3 Curly Prod3
# 4 3 Curly Prod1
# 5 4 Harry Prod2
# 6 5 <NA> OrphanProductThese joins will help you with most of your day to day tasks. There are a few other joins to look into. Also, many libraries have added functions that are a bit eaier to use. We will learn these later on.