Use a key to replace multiple values within one variable with their character match in R-CodePudding

I have a dataframe (df) of products for example (df$id) which contains a variable of 1 or more digits separated by commas (df$cat) where each number corresponds to a specific 'category'. (One product can be assigned to multiple categories).

I'd like to use a key (key) to change these from a digit to a character string


df <- data.frame(id=c("id1","id2","id3","id4","id5","id6","id7","id8"),
                 cat=c("0,2,6","0","2","2,6","4,6","6","6","6"))

> head(df)
   id   cat
1 id1 0,2,6
2 id2     0
3 id3     2
4 id4   2,6
5 id5   4,6
6 id6     6

key <- data.frame(cat=c("0","2","4","6"),
                 name=c("kitchen","bathroom","dining","hall"))

So I would end up with


df.d <- data.frame(id=c("id1","id2","id3","id4","id5","id6","id7","id8"),
                 cat=c("0,2,6","0","2","2,6","4,6","6","6","6"),
                 location=c("kitchen,bathroom,hall","kitchen","bathroom","bathroom,hall","dining,hall","hall","hall","hall"))

head(df.d)
   id   cat              location
1 id1 0,2,6 kitchen,bathroom,hall
2 id2     0               kitchen
3 id3     2              bathroom
4 id4   2,6         bathroom,hall
5 id5   4,6           dining,hall
6 id6     6                  hall
7 id7     6                  hall
8 id8     6                  hall

Was trying to use dplyr's recode but no success

CodePudding user response：

one approach:

library(dplyr)

## pulling TWO columns returns the 1. column as vector,
## named with the 2. column
key_vector <- key |> pull(name, cat)

df.d <- 
    df |>
    rowwise() |>
    mutate(location = paste(key_vector[unlist(strsplit(cat, ','))],
                            collapse = ', '))

df.d |> head(3)
# # A tibble: 3 x 3
# # Rowwise: 
#   id    cat   location               
#   <chr> <chr> <chr>                  
# 1 id1   0,2,6 kitchen, bathroom, hall
# 2 id2   0     kitchen                
# 3 id3   2     bathroom