CODEWITHSUNDEEP
rdata.table
IVIM
IVIM

Reputation: 2367

How to efficiently replace one set of values with another set of values in data.table using a lookup table?

I need merge two datasets, in which the same observations are named differently. For this, I need to observations in one of them according a a lookup date, prior to merging these sets. How can I do in data.table parlance?

The following example, obviously does not work:

# dataset:
dt <- data.table( chapter=as.character(11:15) );dt

# lookup table:
dtLookup <- data.table(
  old = c("11", "12", "14", "15"),
  new = c("101", "102", "105", "104")
)

# updated data.table with values replaced according to lookup table:

# I can do it this way, but it's obviously very slow not taking the data.table advantage

for(i in 1:nrow(dtLookup) ) {
 dt[chapter == dtLookup$old[i], chapter:= dtLookup$new[i]]
};

Upvotes: 0

Views: 187

Answers (1)

Nathan Werth
Nathan Werth

Reputation: 5263

Combine the old and new values in another data.table and do a "lookup assignment".

replacements <- data.table(
  old = c("12", "11", "14", "15"),
  new = c("11", "12", "15", "16")
)

dt[
  replacements,
  on = c(chapter = "old"),
  chapter := new
]

dt
#    chapter
# 1:      12
# 2:      11
# 3:      13
# 4:      15
# 5:      16

Upvotes: 1

Related Questions