1

Say we have this dataframe :

df <- data.frame(
  "ID" = c("001", "002","003"),
  "V1" = c("1", "1","2"),
  "V2" = c("F","M","F"),
  "V3" = c(0,1,NA)
)

And this lookup table :

lookup <- data.frame(
  "value" = c("1", "2","F","M",0,1),
  "label" = c("May2018", "Dec2019",'Female','Male',"No","Yes"),
  "var_name" = c("V1","V1","V2","V2","V3","V3")
)

I want to create 'uncoded' variables based on the lookup table

I tried this but for some reasons, it's not working for V3, any clue ?

df[,paste0('V1','_unc')] <- lookup$label[match(unlist(df[,'V1']), lookup[,'value'])]
df[,paste0('V2','_unc')] <- lookup$label[match(unlist(df[,'V2']), lookup[,'value'])]
df[,paste0('V3','_unc')] <- lookup$label[match(unlist(df[,'V3']), lookup[,'value'])]

> df
   ID V1 V2 V3  V1_unc V2_unc  V3_unc
1 001  1  F  0 May2018 Female      No
2 002  1  M  1 May2018   Male May2018
3 003  2  F  1 Dec2019 Female <NA>

I tried also in a loop (same problem with V3):

for (var in c('V1','V2','V3')) {
  df[,paste0(var,'_unc')] <- lookup$label[match(unlist(df[,var]), lookup[,'value'])]
}
pouet3787
  • 43
  • 3
  • Can you show the expected output – akrun Jun 10 '19 at 18:13
  • 1
    I think you need `lst1 <- Map(function(x, y) y$label[match(x, y$value)], df[-1], split(lookup[1:2], lookup$var_name)); df[paste0(names(lst1), '-unc')] <- lst1` – akrun Jun 10 '19 at 18:22
  • Another version of akrun's approach similar to your for loop: `lookups <- split(lookup, lookup$var_name); for (vn in names(lookups)) df[[paste(vn, "_unc")]] <- with(lookups[[vn]], label[match(df[[vn]], value)])` For lookup via multiple columns, maybe see "update joins" with the data.table package. – Frank Jun 10 '19 at 19:09

0 Answers0