Merging in R

Steve Simon

2005-07-26

Merging is tricky in any program, and you have to be sure that you know what you are doing. I have found that keeping an index variable will allow me to backtrack and find information in the original files if needed. Here's a simple example of how this would work.

# randomly select 20 letters without replacement.
x0 <- sample(letters,20)
x1 <- sample(letters,20)
x0
##  [1] "l" "g" "r" "u" "v" "o" "p" "h" "j" "n" "q" "s" "w" "b" "c" "y" "x" "e" "z"
## [20] "t"
x1
##  [1] "u" "i" "y" "v" "s" "h" "z" "q" "k" "n" "t" "e" "l" "b" "d" "c" "o" "f" "g"
## [20] "r"

The intersect functions lets us know which letters are in both x0 and x1.

intersect(x0,x1)
##  [1] "l" "g" "r" "u" "v" "o" "h" "n" "q" "s" "b" "c" "y" "e" "z" "t"

The setdiff function lets us know which letters are in x0 but not x1 (reverse the arguments to get letters in x1 but not in x1).

setdiff(x0,x1)
## [1] "p" "j" "w" "x"
setdiff(x1,x0)
## [1] "i" "k" "d" "f"

By default, the merge function includes only those values in common with the two files. Note that the id1 and id2 values tells us that the first row of x2 comes from the 19th and 8th rows of x0 and x1, respectively. Notice also that the merged file is sorted by the intersection of the two files.

x2 <- merge(list(x=x0,id0=1:20),list(x=x1,id1=1:20))
x2
##    x id0 id1
## 1  b  14  14
## 2  c  15  16
## 3  e  18  12
## 4  g   2  19
## 5  h   8   6
## 6  l   1  13
## 7  n  10  10
## 8  o   6  17
## 9  q  11   8
## 10 r   3  20
## 11 s  12   5
## 12 t  20  11
## 13 u   4   1
## 14 v   5   4
## 15 y  16   3
## 16 z  19   7

To include all values, including mismatches, specify all=T. Notice now that the merged file is sorted by the values of x0, followed by the sorted values of x1 that are not found in x0. An NA for an index value tells you that the letter is not found in the original x0 or x1 vector.

x3 <- merge(list(x=x0,id0=1:20),list(x=x1,id1=1:20),all=T)
x3
##    x id0 id1
## 1  b  14  14
## 2  c  15  16
## 3  d  NA  15
## 4  e  18  12
## 5  f  NA  18
## 6  g   2  19
## 7  h   8   6
## 8  i  NA   2
## 9  j   9  NA
## 10 k  NA   9
## 11 l   1  13
## 12 n  10  10
## 13 o   6  17
## 14 p   7  NA
## 15 q  11   8
## 16 r   3  20
## 17 s  12   5
## 18 t  20  11
## 19 u   4   1
## 20 v   5   4
## 21 w  13  NA
## 22 x  17  NA
## 23 y  16   3
## 24 z  19   7

The %in% operator is also useful. The %in% operator compares each element of the first set against all the elements of the second set and returns TRUE if there is a match.

x0 %in% x1
##  [1]  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE FALSE  TRUE  TRUE  TRUE
## [13] FALSE  TRUE  TRUE  TRUE FALSE  TRUE  TRUE  TRUE

This function is not symmetric, of course,

x1 %in% x0
##  [1]  TRUE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE  TRUE  TRUE  TRUE
## [13]  TRUE  TRUE FALSE  TRUE  TRUE FALSE  TRUE  TRUE

Using the %in% function, you can re-create the intersect function,

x0[x0 %in% x1]
##  [1] "l" "g" "r" "u" "v" "o" "h" "n" "q" "s" "b" "c" "y" "e" "z" "t"

or the setdiff function,

x0[!x0 %in% x1]
## [1] "p" "j" "w" "x"

A less intuitive version of the %in% operator is the match function. Additional information on merging can be found at

You can find an earlier version of this page on my original website.