Search code examples
rdata-processing

Replace the value in column with respect to other two columns values

Data1
  A     B      C 
  a     1     a,b
  b     6     c,b
  c     5     b,c
  d     3     a,d

So now i want to make new column "D"

where it replace values as per column A and B values, for example.

  C      D
 a,b    1,6
 c,b    5,6
 b,c    6,5
 a,d    1,3
Solution

  • We can use chartr. Create the old and new arguments for chartr by pasteing the key/value columns i.e. 'A' and 'B' and use x as the 'C' column to replace the substring in 'C' that matches with 'A' to that of 'B' 

    Data1$D <- with(Data1, chartr(paste(A,collapse=""), paste(B,collapse=""), C))
Data1$D
#[1] "1,6" "5,6" "6,5" "1,3"

    Update

    This should also work if the 'x' is from a different dataset

    with(Data1, chartr(paste(A,collapse=""), paste(B,collapse=""), Data2$Col1))
#[1] "1,5" "6,3" "1,1" "1,5"

    data

    Data1 <- structure(list(A = c("a", "b", "c", "d"), B = c(1L, 6L, 5L, 3L
), C = c("a,b", "c,b", "b,c", "a,d")), .Names = c("A", "B", "C"
), class = "data.frame", row.names = c(NA, -4L))

Data2 <- data.frame(Col1 = c("a,c", "b,d", "a,a", "a,c"), stringsAsFactors=FALSE)