🎓 A1Learner
🔍
0 of 33 lessons completed

R Merging Data Frames

merge() joins two data frames into one wider frame. Rows are matched on a column that both frames share.

Join two frames on a shared column

Both frames hold an id column, and the scores frame lists its rows out of order. The join matches each person with a score, and the rows come back sorted by id.

people <- data.frame(id = c(1, 2, 3), name = c("Ana", "Ben", "Cid"))
scores <- data.frame(id = c(3, 1, 2), score = c(70, 90, 80))
print(merge(people, scores))

Name the shared column with by

The by argument states the shared column by name. The result is the same join as before.

people <- data.frame(id = c(1, 2, 3), name = c("Ana", "Ben", "Cid"))
scores <- data.frame(id = c(3, 1, 2), score = c(70, 90, 80))
print(merge(people, scores, by = "id"))

Keep unmatched rows with all.x

Dee has no score row, so a plain join would drop Dee. all.x = TRUE keeps the row, and the score column holds NA for it.

people <- data.frame(id = c(1, 2, 3, 4), name = c("Ana", "Ben", "Cid", "Dee"))
scores <- data.frame(id = c(3, 1, 2), score = c(70, 90, 80))
print(merge(people, scores, all.x = TRUE))

A shared column that does not exist

Neither frame holds a column called nope. The join stops with an error, and nothing prints.

people <- data.frame(id = c(1, 2), name = c("Ana", "Ben"))
scores <- data.frame(id = c(1, 2), score = c(90, 80))
print(merge(people, scores, by = "nope"))

Example

members <- data.frame(id = c(2, 1, 3), member = c("Ben", "Ana", "Cid"))
points <- data.frame(id = c(1, 2, 3), points = c(40, 55, 35))
print(merge(members, points))

Key points

  • merge() joins two data frames into one frame.
  • Rows are matched on the column both frames share.
  • The by argument names the shared column directly.
  • The joined rows come back sorted by the shared column.
  • Rows with no match in the other frame drop out of a plain join.
  • all.x = TRUE keeps every row of the first frame.
  • A kept row with no match holds NA in the columns that came from the second frame.

In this example the members frame lists its rows out of order. The join matches each member with a points value, and the rows come back sorted by id.

Note

A by value that names no column of the frames stops the join with an error.

Try it Yourself

Edit the code, then press Run.

Running is simulated · rdrr.io R Snippets

Exercise

What happens to a row of the first frame that has no match when all.x = TRUE?