[R] merge data frames with same column names of different lengths and missing values

[Steven Lubitz]

Hello, I'm switching over from SAS to R and am having trouble merging data frames. The data frames have several columns with the same name, and each has a different number of rows. Some of the values are missing from cells with the same column names in each data frame. I had hoped that when I merged the dataframes, every column with the same name would be merged, with the value in a complete cell overwriting the value in an empty cell from the other data frame. I cannot seem to achieve this result, though I've tried several merge adaptations:

x <- data.frame(item1=c(NA,NA,3,4,5), item2=c(1,NA,NA,4,5), id=1:5)
y <- data.frame(item1=c(NA,2,NA,4,5,6), item2=c(NA,NA,3,4,5,NA), id=1:6)


merge(x,y,by="id") #I lose observations here (n=1 in this example), and my items are duplicated - I do not want this result
  id item1.x item2.x item1.y item2.y
1  1      NA       1      NA      NA
2  2      NA      NA       2      NA
3  3       3      NA      NA       3
4  4       4       4       4       4
5  5       5       5       5       5


merge(x,y,by=c("id","item1","item2")) #again I lose observations (n=4 here) and do not want this result
  id item1 item2
1  4     4     4
2  5     5     5


merge(x,y,by=c("id","item1","item2"),all.x=T,all.y=T) #my rows are duplicated and the NA values are retained - I instead want one row per ID
  id item1 item2
1  1    NA     1
2  1    NA    NA
3  2     2    NA
4  2    NA    NA
5  3     3    NA
6  3    NA     3
7  4     4     4
8  5     5     5
9  6     6    NA

In reality I have multiple data frames with numerous columns, all with this problem. I can do the merge seamlessly in SAS, but am trying to learn and stick with R for my analyses. Any help would be greatly appreciated.

Steve Lubitz
Cardiovascular Research Fellow, Brigham and Women's Hospital and Massachusetts General Hospital