Conditional merge/replacement in R

Question

I have two data frames:

df1
x1  x2
1   a
2   b
3   c
4   d

and

df2
x1  x2
2   zz
3   qq

I want to replace some of the values in df1$x2 with values in df2$x2 based on the conditional match between df1$x1 and df2$x2 to produce:

df1
x1  x2
1   a
2   zz
3   qq
4   d

Answer 1

use match(), assuming values in df1 are unique.

df1 <- data.frame(x1=1:4,x2=letters[1:4],stringsAsFactors=FALSE)
df2 <- data.frame(x1=2:3,x2=c("zz","qq"),stringsAsFactors=FALSE)

df1$x2[match(df2$x1,df1$x1)] <- df2$x2
> df1
  x1 x2
1  1  a
2  2 zz
3  3 qq
4  4  d

If the values aren't unique, use :

for(id in 1:nrow(df2)){
  df1$x2[df1$x1 %in% df2$x1[id]] <- df2$x2[id]
}

Answer 2

We can use {powerjoin}, and handle the conflicting columns with coalesce_yx

library(powerjoin)
df1 <- data.frame(x1 = 1:4, x2 = letters[1:4], stringsAsFactors = FALSE)
df2 <- data.frame(x1 = 2:3, x2 = c("zz", "qq"), stringsAsFactors = FALSE)

power_left_join(df1, df2, by = "x1", conflict = coalesce_yx)
#>   x1 x2
#> 1  1  a
#> 2  2 zz
#> 3  3 qq
#> 4  4  d

Answer 3

The first part of Joris' answer is good, but in the case of non-unique values in df1, the row-wise for-loop will not scale well on large data.frames.

You could use a data.table "update join" to modify in place, which will be quite fast:

library(data.table)
setDT(df1); setDT(df2)
df1[df2, on = .(x1), x2 := i.x2]

Or, assuming you don't care about maintaining row order, you could use SQL-inspired dplyr:

library(dplyr)
union_all(
  inner_join( df1["x1"], df2 ), # x1 from df1 with matches in df2, x2 from df2
  anti_join(  df1, df2["x1"] )  # rows of df1 with no match in df2
) # %>% arrange(x1) # optional, won't maintain an arbitrary row order

Either of these will scale much better than the row-wise for-loop.

Answer 4

I see that Joris and Aaron have both chosen to build examples without factors. I can certainly understand that choice. For the reader with columns that are already factors there would also be to option of coercion to "character". There is a strategy that avoids that constraint and which also allows for the possibility that there may be indices in df2 that are not in df1 which I believe would invalidate Joris Meys' but not Aaron's solutions posted so far:

df1 <- data.frame(x1=1:4,x2=letters[1:4])
df2 <- data.frame(x1=c(2,3,5), x2=c("zz", "qq", "xx") )

It requires that the levels be expanded to include the intersection of both factor variables and then also the need to drop non-matching columns (= NA values) in match(df1$x1, df2$x1)

 df1$x2 <- factor(df1$x2 , levels=c(levels(df1$x2), levels(df2$x2)) )
 df1$x2[na.omit(match(df2$x1,df1$x1))] <- df2$x2[which(df2$x1 %in% df1$x1)]
 df1
#-----------
  x1 x2
1  1  a
2  2 zz
3  3 qq
4  4  d

(Note that recent versions of R do not have stringsAsFactors set to TRUE in the data.frame function defaults, unlike it was for most of the history of R.)

Answer 5

It can be done with dplyr.

library(dplyr)

full_join(df1,df2,by = c("x1" = "x1")) %>% 
  transmute(x1 = x1,x2 = coalesce(x2.y,x2.x))

  x1 x2
1  1  a
2  2 zz
3  3 qq
4  4  d

Answer 6

You can do it by matching the other way too but it's more complicated. Joris's solution is better but I'm putting this here also as a reminder to think about which way you want to match.

df1 <- data.frame(x1=1:4, x2=letters[1:4], stringsAsFactors=FALSE)
df2 <- data.frame(x1=2:3, x2=c("zz", "qq"), stringsAsFactors=FALSE)
swap <- df2$x2[match(df1$x1, df2$x1)]
ok <- !is.na(swap)
df1$x2[ok] <- swap[ok]

> df1
  x1 x2
1  1  a
2  2 zz
3  3 qq
4  4  d

Answer 7

new here, but using the following dplyr approach seems to work as well
similar but slightly different to one of the answers above

df3 <- anti_join(df1, df2, by = "x1")
df3 <- rbind(df3, df2)
df3

Answer 8

As of dplyr 1.0.0 there is a function specifically for this:

library(dplyr)
df1 <- data.frame(x1=1:4,x2=letters[1:4],stringsAsFactors=FALSE)
df2 <- data.frame(x1=2:3,x2=c("zz","qq"),stringsAsFactors=FALSE)


rows_update(df1, df2, by = "x1")

See https://stackoverflow.com/a/65254214/2738526