Replace comma separated values in R from a lookup table

user_14972081 0
Viewed 156
c1 <- c(1,2,3,4,5)  
c2 <- c("x","y","z","a", "b")  
t1 <- data.frame(c1,c2)  
t1  
c3 <- c(10,20,30,40,50)  
c4 <- c(2,3,4,1,3)  
c5 <- c(1,4,5,2,1)  
t2 <- data.frame(c3,c4,c5)  
t2 <- tidyr::unite(t2, c4, c4, c5, na.rm = TRUE, sep = ',')  
t1 table: lookup table   
c1 c2    
1  x  
2  y  
3  z  
4  a  
5  b  
t2 table:   
c3     c4  
10     2,1  
20     3,4   
30     4,5  
40     1,2  
50     3,1

Now I want to replace the c4 (comma separated column) in t4 from c2 column of t1
new t2 table:

 c3 c4  
10   y,x  
20   z,a  
30   a,b  
40   x,y  
50   z,x

I tried solving it by writing a sql query in R but no solution.

r lookup
2 Answers

Split c4 string on comma, match each individual value with c1 to get corresponding c2 value and combine them into one string.

t2$c4 <- sapply(strsplit(t2$c4, ','), function(x) 
                toString(t1$c2[match(x, t1$c1)]))

Using data.table syntax :

library(data.table)
setDT(t2)
t2[,c4 := sapply(strsplit(c4, ','),function(x) toString(t1$c2[match(x, t1$c1)]))]

#   c3   c4
#1: 10 y, x
#2: 20 z, a
#3: 30 a, b
#4: 40 x, y
#5: 50 z, x
user_39629140

One stringr and dplyr option could be:

t2 %>%
 mutate(c4 = str_replace_all(c4, setNames(t1$c2, t1$c1)))

  c3  c4
1 10 y,x
2 20 z,a
3 30 a,b
4 40 x,y
5 50 z,x
user_59645570
Related