CODEWITHSUNDEEP
rdata.tablelookup
Efejiro Ashano
Efejiro Ashano

Reputation: 43

Replace column values in table with values from lookup based on matches in R using data.table

I'd like to replace values in the table column with corresponding values of matches in the lookup column. I achieved this with data.table package "assign by reference" taking one value (and it's replacement) after the other, but I thought I could do something more modular.

In summary, I do this:

# Build a table and a lookup
code<- c("ABC","EBC","ABC","EBC","OOO","PPP","ABC")
sn <- c(1:7)
old<- c("ABC","EBC")
new<- c("CBa","CBe")

lookup <- data.frame(old,new)
table <-data.frame(code,sn)

# Set data.table as TRUE for both
setDT(table)
setDT(lookup)

# Attempt reassignment
table[code %in% lookup$old, code := lookup$new[which(lookup$old==code)]]
table

..and I get this:

> table
   code sn
1:  CBa  1
2:  CBe  2
3: <NA>  3
4: <NA>  4
5:  OOO  5
6:  PPP  6
7: <NA>  7

However, what I really hope for is this results:

> table
   code sn
1:  CBa  1
2:  CBe  2
3:  CBa  3
4:  CBe  4
5:  OOO  5
6:  PPP  6
7:  CBa  7

In essence: the replacement is made based on the lookup with all values assigned (not just the first two). What am I missing? I have tried looking for other solutions around but suggestions don't quite seem to be what I am looking for. Grateful for any answers.

Upvotes: 4

Views: 718

Answers (2)

ThomasIsCoding
ThomasIsCoding

Reputation: 101343

A base R option can work (but highly recommend @akrun's data.table solution, super elegant!)

transform(
  table,
  code = replace(
    code,
    code %in% lookup$old,
    setNames(lookup$new, lookup$old)[code][code %in% lookup$old]
  )
)

which gives

   code sn
1:  CBa  1
2:  CBe  2
3:  CBa  3
4:  CBe  4
5:  OOO  5
6:  PPP  6
7:  CBa  7

Or a longer data.table option

> lookup[table, on = .(old = code)][, new := fcoalesce(new, old)][, old := NULL][]
   new sn
1: CBa  1
2: CBe  2
3: CBa  3
4: CBe  4
5: OOO  5
6: PPP  6
7: CBa  7

Upvotes: 1

akrun
akrun

Reputation: 887118

We can do a join on the 'code' and 'old' from table and lookup respectively

table[lookup, code := new, on = .(code = old)]

-output

 table
   code sn
1:  CBa  1
2:  CBe  2
3:  CBa  3
4:  CBe  4
5:  OOO  5
6:  PPP  6
7:  CBa  7

Upvotes: 4

Related Questions