CODEWITHSUNDEEP
rmergeleft-join
Jada Chang
Jada Chang

Reputation: 25

Replace certain records in a column of a table with another table by matching other columns

I want to replace some records in t1$x3 with another table t2 (note that all other columns are same except x3):

t1 <- data.frame(x1 = c(1,7,3,4,2,6),
                 x2 = c("I","R","R","I","I","R"),
                 x3 = c("a","a","a","a","a","a"))
t2 <- data.frame(x1 = c(4,2,6),
                 x2 = c("I","I","R"),
                 x3 = c("b","b","b"))
t1
#   x1 x2 x3
# 1  1  I  a
# 2  7  R  a
# 3  3  R  a
# 4  4  I  a
# 5  2  I  a
# 6  6  R  a

t2
#   x1 x2 x3
# 1  4  I  b
# 2  2  I  b
# 3  6  R  b

The result is supposed to be like this：

data.frame(x1 = c(1,7,3,4,2,6),
           x2 = c("I","R","R","I","I","R"),
           x3 = c("a","a","a","b","b","b"))

#   x1 x2 x3
# 1  1  I  a
# 2  7  R  a
# 3  3  R  a
# 4  4  I  b
# 5  2  I  b
# 6  6  R  b

How can I do it?

Upvotes: 2

Views: 77

Answers (3)

Ronak Shah
Ronak Shah

Reputation: 388817

I think this would give what you want.

Convert x3 column to character in t1 and t2and then check the condition

t1$x3 <- as.character(t1$x3)
t2$x3 <- as.character(t2$x3)
t1[t1$x1 %in% t2$x1 & as.character(t1$x2) %in% as.character(t2$x2), "x3"] <- t2$x3

> t1
#  x1 x2 x3
#1  1  I  a
#2  7  R  a
#3  3  R  a
#4  4  I  b
#5  2  I  b
#6  6  R  b

Upvotes: 0

akrun
akrun

Reputation: 886968

We can try with data.table. It should be very fast as we are assigning (:=) in place. Convert the 'data.frame' to 'data.table' (setDT(t1)), join with 't2' on 'x1' and 'x2', then assign 'i.x3' values to 'x3'. 

library(data.table)
setDT(t1)[t2, x3 := i.x3, on = c("x1", "x2")]
t1
#   x1 x2 x3
#1:  1  I  a
#2:  7  R  a
#3:  3  R  a
#4:  4  I  b
#5:  2  I  b
#6:  6  R  b

Or we can use match

t1$x3 <- factor(t1$x3, levels = c('a','b'))
t1[match(do.call(paste,t2[-3]), do.call(paste, t1[-3])), 'x3'] <- t2$x3
t1
# x1 x2 x3
#1  1  I  a
#2  7  R  a
#3  3  R  a
#4  4  I  b
#5  2  I  b
#6  6  R  b

Upvotes: 2

zx8754
zx8754

Reputation: 56034

Left join then update:

library(dplyr)

left_join(t1, t2, by = c("x1", "x2"), all.x = TRUE) %>% 
  mutate(x3 = ifelse(is.na(x3.y), as.character(x3.x), as.character(x3.y))) %>% 
  select(-c(x3.x, x3.y))

#   x1 x2 x3
# 1  1  I  a
# 2  7  R  a
# 3  3  R  a
# 4  4  I  b
# 5  2  I  b
# 6  6  R  b

Using base R:

# left join 
res <- merge(t1, t2, by = c("x1", "x2"), all.x = TRUE)
# update x3
res$x3 <- ifelse(is.na(res$x3.y), as.character(res$x3.x), as.character(res$x3.y))
# subset and reorder
res <- res[match(t1$x1, res$x1), c("x1", "x2", "x3")]

Upvotes: 3

Related Questions