Is there a way to update existing variables when merging in R?

Question

I have two datasets I want to merge on the variable id, one of which has two possible ids, for example:

df1 <- data.frame(id = c('a', 'b', 'c', 'q', 'z'),
                  id2 = c('NA', 'g', 'NA', 'd', 'e'),
                  var1 = 1:5,
                  var3 = c('hi', 'hello', 'bonjour', 'howdy', 'hi'))
df2 <- data.frame(id = c('a', 'b', 'c', 'd', 'e'),
                  var2 = 6:10,
                  var4 = 20:24)

I currently merge these datasets on the primary linking variable:

merge1 <- merge(x = df1,
                y = df2,
                by = 'id',
                all = TRUE)

I need to re-merge those rows from the first dataframe that have the second id but did not match in the initial merge, so to do that I put them in a separate data frame, take them out of the fully matched dataset, and then merge the two:

df1.remerge <- merge1[which(!is.na(merge1$id2) &
                              is.na(merge1$var2)),] 
df1.remerge$id <- df1.remerge$id2

merged <- merge1[which(is.na(merge1$id2) |
                       !is.na(merge1$var2)),]

merge2 <- merge(x = df1.remerge,
                y = merged,
                by = 'id',
                all = TRUE,
                suffixes = c('.m1', '.m2'))
# where .m1 = the remerged obs from df1 & .m2 = the original merged obs

This, though, creates two sets of the same variables (i.e. I end up with two var1s and two var2s). I can of course manually combine the variables, but I'd prefer not to, since my actual data is quite large (think millions of observations and 30-40 variables) and that seems rather inefficient.

Ultimately I want a dataset that looks roughly like this:

want.final <- data.frame(id = c('a', 'b', 'c', 'd', 'e'),
                         var1 = 1:5,
                         var2 = 6:10,
                         var3 = c('hi', 'hello', 'bonjour', 'howdy', 'hi'),
                         var4 = 20:24)

But what I get with this method is this:

get.final <- data.frame(id = c('a', 'b', 'c', 'd', 'e'),
                        var1.m1 = c('NA', 'NA', 'NA', 4, 5),
                        var1.m2 = c(1, 2, 3, 'NA', 'NA'),
                        var2.m1 = c('NA', 'NA', 'NA', 'NA', 'NA'),
                        var2.m2 = c(6, 7, 8, 9, 10),
                        var3.m1 = c('NA', 'NA', 'NA', 'howdy', 'hi'),
                        var3.m2 = c('hi', 'hello', 'bonjour', 'NA', 'NA'),
                        var4.m1 = c('NA', 'NA', 'NA', 'NA', 'NA'),
                        var4.m2 = c(20, 21, 22, 23, 24))

Does anyone know of a way to re-merge these observations and update the existing variables where they're missing in the master/x dataset and not missing in the using/y? In an ideal world I'd like something like the update option for Stata's merge that does just this.

user_3358272 · Answer

In general, merge and dplyr::*_join will always give you the *.x/*.y variants of a shared-column; data.table is often the same, but its merge-assignment operation can help side-step it.

base R

out <- merge(merge(df1, df2, by="id", all.x=TRUE), df2,
             by.x="id2", by.y="id", all.x = TRUE, suffixes = c("", ".y"))
out$id[is.na(out$var2)] <- out$id2[is.na(out$var2)]
out$var2[is.na(out$var2)] <- out$var2.y[is.na(out$var2)]
out[,c("id2","var2.y")] <- NULL
out
#   id var1 var2
# 1  d    4    9
# 2  e    5   10
# 3  b    2    7
# 4  a    1    6
# 5  c    3    8

data.table

Renaming df2$var2 can be useful here for clarity and conditional reassignment.

library(data.table)
DT1 <- as.data.table(df1)
DT2 <- as.data.table(df2)
setnames(DT2, "var2", "var2new")
DT1[DT2, var2 := var2new, on = .(id)
  ][DT2, c("id", "var2") := .(id2, fifelse(is.na(var2), var2new, var2)), on = .(id2 == id)
  ][, id2 := NULL]
#        id  var1  var2
#      
# 1:      a     1     6
# 2:      b     2     7
# 3:      c     3     8
# 4:      d     4     9
# 5:      e     5    10

user_3817004 · Answer

If I understand correctly, the OP wants to find matching rows between df1$id and df2$id. For those rows in df1 where no matches are found, a second attempt should find matching rows between the alternative id df1$id2 and df2$id. Furthermore, the datasets are quite large (containing millions of rows) and the OP is restricted more or less to base R.

Base R

So, instead of doing multiple merges with datasets of millions of rows, we can resolve the duplicate id columns in df1 first before doing a single merge:

id1 <- df2$id[match(df1$id,  df2$id)]
id2 <- df2$id[match(df1$id2, df2$id)]
df1$id <- ifelse(is.na(id1), id2, id1)
df1$id2 <- NULL
merge(df1, df2)

  id var1    var3 var2 var4
1  a    1      hi    6   20
2  b    2   hello    7   21
3  c    3 bonjour    8   22
4  d    4   howdy    9   23
5  e    5      hi   10   24

Explanation

First, we check if df1$id is included in df2$id which returns id1 as
```
[1] "a" "b" "c" NA  NA
```
Then, we check if df1$id2 is included in df2$id which returns id2 as
```
[1] NA  NA  NA  "d" "e"
```
Now, we can coalesce id1 and id2, i.e., we pick pair-wise the first non-NA value and replace the id column in df1 which becomes
```
[1] "a" "b" "c" "d" "e"
```
The id2 column in df1 is removed as it is no longer needed.
Finally, the modified df1 and df2 are merged on the id column.

Edit: `data.table` approach

As the OP has pointed out that his production dataset consists of millions of observations and 30-40 variables it might be worthwhile to consider a data.table approach. data.table has the := assignment operator which allows for fast update of columns by reference.

Using data.table, the approach above can be implemented by

library(data.table)
setDT(df1)
setDT(df2)
df2[df1[, `:=`(id = fcoalesce(df2[df1, on = "id", x.id], df2[df1, on = "id==id2", x.id]),
          id2 = NULL)], on = "id"]