Merging data frames

Problem

You want to merge two data frames on a given column from each (like a join in SQL).

Solution

  1. # Make a data frame mapping story numbers to titles
  2. stories <- read.table(header=TRUE, text='
  3.    storyid  title
  4.     1       lions
  5.     2      tigers
  6.     3       bears
  7. ')
  9. # Make another data frame with the data and story numbers (no titles)
  10. data <- read.table(header=TRUE, text='
  11.     subject storyid rating
  12.           1       1    6.7
  13.           1       2    4.5
  14.           1       3    3.7
  15.           2       2    3.3
  16.           2       3    4.1
  17.           2       1    5.2
  18. ')
  20. # Merge the two data frames
  21. merge(stories, data, "storyid")
  22. #>   storyid  title subject rating
  23. #> 1       1  lions       1    6.7
  24. #> 2       1  lions       2    5.2
  25. #> 3       2 tigers       1    4.5
  26. #> 4       2 tigers       2    3.3
  27. #> 5       3  bears       1    3.7
  28. #> 6       3  bears       2    4.1

If the two data frames have different names for the columns you want to match on, the names can be specified:

  1. # In this case, the column is named 'id' instead of storyid
  2. stories2 <- read.table(header=TRUE, text='
  3.    id       title
  4.     1       lions
  5.     2      tigers
  6.     3       bears
  7. ')
  9. # Merge on stories2$id and data$storyid.
  10. merge(x=stories2, y=data, by.x="id", by.y="storyid")
  11. #>   id  title subject rating
  12. #> 1  1  lions       1    6.7
  13. #> 2  1  lions       2    5.2
  14. #> 3  2 tigers       1    4.5
  15. #> 4  2 tigers       2    3.3
  16. #> 5  3  bears       1    3.7
  17. #> 6  3  bears       2    4.1
  19. # Note that the column name is inherited from the first data frame (x=stories2).

It is possible to merge on multiple columns:

  1. # Make up more data
  2. animals <- read.table(header=T, text='
  3.    size type         name
  4.   small  cat         lynx
  5.     big  cat        tiger
  6.   small  dog    chihuahua
  7.     big  dog "great dane"
  8. ')
  10. observations <- read.table(header=T, text='
  11.    number  size type
  12.         1   big  cat
  13.         2 small  dog
  14.         3 small  dog
  15.         4   big  dog
  16. ')
  18. merge(observations, animals, c("size","type"))
  19. #>    size type number       name
  20. #> 1   big  cat      1      tiger
  21. #> 2   big  dog      4 great dane
  22. #> 3 small  dog      2  chihuahua
  23. #> 4 small  dog      3  chihuahua

Notes

After merging, it may be useful to change the order of the columns. See ../Reordering the columns in a data frame.