1

I'm curious if there is a function in R that can accomplish the task of looking up a corresponding value from another dataset and replacing several columns of data based on that lookup table.

The data would look like this:

ID value_1  value_2  value_3
----------------------------
1  A         -         B
2  A         C         A
3  C         A         C
4  -         -         -

The lookup table would look like this:

letters  amount
---------------
   A      5
   B      6
   C      20

and the intended output should look something like this:

ID value_1  value_2  value_3
----------------------------
1  5         -         6
2  5         20        5
3  20        5         20
4  -         -         -
moku
  • 4,099
  • 5
  • 30
  • 52
  • This is not the same as that question he is only joining with a single column. I need to fill several columns based on one column. – moku Jan 20 '15 at 16:10
  • You could try `df1[-1] <- 'dim<-'(as.character(factor(as.matrix(df1[-1]), labels=c('-', 5, 6, 20))), c(4,3))` – akrun Jan 20 '15 at 16:10

1 Answers1

2

You could try match

df1[-1] <- `dim<-`(df2$amount[match(as.matrix(df1[-1]),
                                  df2$letters)], c(4,3))

df1[is.na(df1)] <- '-' 
#wouldn't recommend to replace `NA` as it will convert to `character` class
df1
#  ID value_1 value_2 value_3
#1  1       5       -       6
#2  2       5      20       5
#3  3      20       5      20
#4  4       -       -       -

Or an option using mutate_each from dplyr

library(dplyr)
df1 %>% 
    mutate_each(funs(df2$amount[match(., df2$letters)]), -ID)
#    ID value_1 value_2 value_3
#1  1       5      NA       6
#2  2       5      20       5
#3  3      20       5      20
#4  4      NA      NA      NA
akrun
  • 874,273
  • 37
  • 540
  • 662
  • thxs for you answer not to open this back up but how would you use that second option if you have more variables then ID in the dataset but you want to exclude them during the matching process? Like ID, Name, Country, DMA, Value_1, Value_2, Value_3 how would I exclude ID, Name, Country, DMA in the matching process. – moku Jan 21 '15 at 21:22
  • @moku You can use `mutate_each(funs(df2$amount[match(., df2$letters)]), starts_with('Value'))` Please check `?select` – akrun Jan 22 '15 at 02:52