How can I map a key index into each element of a data.table or data.frame object in R with a key/pair index?

user_18368552 0
Viewed 29

I currently have the following data.table in R:

tab <- data.table(Col1=c("Grade1","Grade2","Grade2"),Col2=c("Grade3","Grade3","Grade1"),Col3=c("Grade1","Grade2","Grade3"))
> tab
     Col1   Col2   Col3
1: Grade1 Grade3 Grade1
2: Grade2 Grade3 Grade2
3: Grade2 Grade1 Grade3

I would like to map it according to an index/value table:

kvtab <- data.table(Key=c("Grade1","Grade2","Grade3"),Value=c(1,2,3))
> kvtab
      Key Value
1: Grade1     1
2: Grade2     2
3: Grade3     3

so that I can get:

   Col1 Col2 Col3
1:    1    3    1
2:    2    3    2
3:    2    1    3

How can I create the joins or merges here when there are multiple columns?

data.table r
2 Answers

Repeated update joins

This joins each column of tab with the lookup table kvtab and replaces the values accordingly:

library(data.table)
for (col in colnames(tab)) tab[kvtab, on = sprintf("%s==Key", col), (col) := Value][]
tab

     Col1   Col2   Col3
1:      1      3      1
2:      2      3      2
3:      2      1      3

Trivial solutions

which only work for the given sample dataset by may fail for other usecases

Removing the substring Grade from each element:

tab[, lapply(.SD, function(x) stringr::str_remove(x, "Grade"))]

Or, turning each column into a factor and returning the factor levels:

tab[, lapply(.SD, function(x) as.integer(factor(x, levels = kvtab$Key)))]

    Col1  Col2  Col3
   <int> <int> <int>
1:     1     3     1
2:     2     3     2
3:     2     1     3

Note that the levels parameter is required to ensure that all columns share the same factor levels.

user_38170040

With lapply and match:

tab[,colnames(tab):=lapply(.SD,function(x) kvtab$Value[match(x,kvtab$Key)])][]

   Col1 Col2 Col3
1:    1    3    1
2:    2    3    2
3:    2    1    3
user_135133280
Related