Working with data values (dictionary). dataframes and recoding in R

user_10198914 0
Viewed 173

I am trying to work with a data.frame that contains 30 or so variables. These variables all have different numeric values (e.g., "0" or "32"). The numeric values map to strings (e.g., in variable Q1, "0" is "Urban"; in variable Q6, "32" is "Not available").

I saw some uses of recode online, and an older plyr package that did this mapping, as well as the match function, but I didn't find anything that matches the exact structure I have.

I provide a reproducible example below:

  test <- as.data.frame(c("1", "2", "3"))
  colnames(test) <- "Q1"
    
  dictionary <- as.data.frame(c("1", "2", "3"))
  dictionary$values <- c("dog", "fish", "cat")
  dictionary$question <- c("Q1", "Q1", "Q1")
  colnames(dictionary)[1] <- "keys"

So, here, dictionary$question contains the question to map to; and then the "keys" and "values" provide the mapping. So, in the test dataframe, I have one variable (Q1) that takes three possible values, "1", "2", or "3". I need to map these back to "dog", "fish", "cat".

But I need an automatic way to map the column in the target dataframe to the rows in the dictionary, and then translate the values, since in practice I have over 1000 possible values and 30 variables.

Edit: what I expect is a function or command that goes like this:

fun(test, dictionary)

Output: a data.frame with c("dog", "fish", "cat").

Or, if test was c("1", "1", "1"), it would be c("dog", "dog", "dog")

match r dictionary
1 Answers

If there are many columns, loop across the 'test' columns, match and replace the values from a named vector created from the subset of 'dictionary' where the 'question' matches with the corresponding column name (cur_column()) and then use coalesce to fill any NA values with original data values

library(dplyr)
library(tibble)
test %>%
      mutate(across(everything(), ~ 
     coalesce(deframe(subset(dictionary, question == cur_column(), 
          select= -question))[as.character(.)], as.character(.))))
    Q1
1  dog
2 fish
3  cat

Or to prevent multiple calls to as.character, do it once

test %>%
   mutate(across(everything(), as.character),
          across(everything(), 
           ~coalesce(deframe(subset(dictionary, question == cur_column(), 
          select= -question))[.], .)))
 Q1
1  dog
2 fish
3  cat

Or using base R

lst1 <- split(dictionary[-3], dictionary$question)
test[names(lst1)] <- Map(function(x, y) {
    tmp <- with(y,  setNames(values, keys)[as.character(x)])
     tmp[is.na(tmp)] <- x[is.na(tmp)]
     tmp}, test[names(lst1)], lst1)
user_37322710
Related