What is the most efficient way to replace a vector's values in a data.table's column with correlating values from another data.table?

user_11507503 0
Viewed 96

Here's a scaled down sample of my problem. I have a data.table with a column of multiple IDs in vector form. These IDs all correspond to words in another data.table.

ID.table <- data.table(IDs = list(c(4, 5, 6), c(2, 3, 4)))
word.table <- data.table(ID = c(1, 2, 3, 4, 5, 6), word = c("This", "is", "a", "test", "sentence", "."))

which yields

     IDs
1: 4,5,6
2: 2,3,4

and

   ID     word
1:  1     This
2:  2       is
3:  3        a
4:  4     test
5:  5 sentence
6:  6        .

I need to convert all the IDs in ID.table to the corresponding words in word.table, like in the following.

               IDs
1: test,sentence,.
2:       is,a,test

I know I can do this using a for loop and looping through every vector in ID.table, but my actual table has thousands of rows, which means it runs very slowly.

row <- 1
for(ID.row in ID.table[, IDs]){
  word.row <- word.table[ID %in% ID.row]$word
  ID.table[row] <- word.row
  
  row <- row + 1
}

Is there a more efficient way to do this?

EDIT: I made a mistake by listing sequential IDs starting from 1 in word.table. ID.table and word.table would look something more like this.

           IDs
1: 608,609,610
2: 606,607,608

and

     ID     word
1:  605     This
2:  606       is
3:  607        a
4:  608     test
5:  609 sentence
6:  610        .

where each row of ID.table will be a vector of sequential numbers not starting from 1, and the ID column of word.table will have not-always sequential ID numbers not starting from 1.

data.table tidyverse r
2 Answers

You can use match :

library(data.table)

ID.table[, IDs := lapply(IDs,function(x) word.table$word[match(x,word.table$ID)])]
ID.table

#               IDs
#1: test,sentence,.
#2:       is,a,test

If you are ok with using tidyverse functions another option is to unnest the IDs and join with word.table.

library(dplyr)

ID.table %>%
  mutate(row = row_number()) %>%
  tidyr::unnest(IDs) %>%
  left_join(word.table, by = c('IDs' = 'ID')) %>%
  group_by(row) %>%
  summarise(Ids = list(word)) %>%
  select(-row)
user_39629140

We could pass a named vector to match and replace by looping over the list column 'IDs' and assign (:=) the output back to IDs

ID.table[, IDs := lapply(IDs, function(x) 
       setNames(word.table$word, word.table$ID)[as.character(x)])]

and if the IDs are in sequence, it is more easier i.e. use the IDs as a numeric index to replace the corresponding values from 'word' column

ID.table[, IDs := lapply(IDs, function(x) word.table$word[x])]
ID.table
#              IDs
#1: test,sentence,.
#2:       is,a,test

It may be also better to do this once without looping by unlisting, replace the values, then relist

ID.table[, IDs := relist(word.table$word[unlist(IDs)], skeleton= IDs)]

NOTE: Both methods are simple and more direct and efficient

Or using a compact tidyverse method

library(purrr)
library(dplyr)
ID.table %>% 
      mutate(IDs = map(IDs, ~ word.table$word[.x]))
#              IDs
#1: test,sentence,.
#2:       is,a,test

This wouldn't change the original attribute structure of data.table

Benchmarks

On a slightly bigger dataset

ID.table1 <- ID.table[rep(seq_len(.N), 1e6)]
ID.table2 <- copy(ID.table1)
ID.table3 <- copy(ID.table1)
ID.table4 <- copy(ID.table1)

system.time(ID.table1[, IDs := lapply(IDs, function(x) 
       setNames(word.table$word, word.table$ID)[as.character(x)])])
#user  system elapsed 
# 29.971   0.492  30.264 
       
system.time(ID.table2[, IDs := lapply(IDs, function(x) word.table$word[x])])
#user  system elapsed 
#  8.079   0.086   8.097 

system.time(ID.table3[, IDs := relist(word.table$word[unlist(IDs)], skeleton= IDs)])
# user  system elapsed 
# 14.085   0.109  14.081 

system.time(ID.table4 %>% 
      mutate(IDs = map(IDs, ~ word.table$word[.x])))
#user  system elapsed 
#  3.724   0.018   3.734
user_37322710
Related