Convert set of columns using conversion key

Question

My dataframe has a set of columns that starts with "i10_dx". I would like to use the conversion key I have to create a new set of columns starting with "ccs" that corresponds to the former set of columns.

This is a snippet of dataframe

df <- data.frame(visitlink = c(1001976, 1001976, 1002247), 
                 i10_dx1 = c("C7931", "C7949", NA), 
                 i10_dx2 = c("G9519", "C7951", "C3490"), 
                 i10_dx3 = c("G936", "C7931", "C7931"), 
                 i10_dx4 = c("C7949", NA, "I10"))

This is the conversion key

key <- data.frame(icd10 = c("I10", "C3490", "C7931", "C7949", "C7951", "G936", "G9519"), 
                  ccs = c("CIR007", "NEO022", "NEO070", "NEO070", "NEO070", "NVS020", "NVS020"))

Answer 1

A dplyr solution with across() + match():

library(dplyr)

df %>%
  mutate(across(i10_dx1:i10_dx4, ~ key$ccs[match(.x, key$icd10)],
                .names = "{sub('i10_dx', 'ccs', .col)}"))

#   visitlink i10_dx1 i10_dx2 i10_dx3 i10_dx4   ccs1   ccs2   ccs3   ccs4
# 1   1001976   C7931   G9519    G936   C7949 NEO070 NVS020 NVS020 NEO070
# 2   1001976   C7949   C7951   C7931    <NA> NEO070 NEO070 NEO070   <NA>
# 3   1002247    <NA>   C3490   C7931     I10   <NA> NEO022 NEO070 CIR007

Alternatively, transform the data to long, left join it with the lookup table key, and transform back to wide.

library(tidyr)
library(dplyr)

df %>%
  mutate(rowid = row_number()) %>%
  pivot_longer(i10_dx1:i10_dx4, names_prefix = "i10_dx", values_to = "i10_dx") %>%
  left_join(key, by = join_by(i10_dx == icd10)) %>%
  pivot_wider(values_from = c(i10_dx, ccs), names_sep = '')

# # A tibble: 3 × 10
#   visitlink rowid i10_dx1 i10_dx2 i10_dx3 i10_dx4 ccs1   ccs2   ccs3   ccs4  
#       <dbl> <int> <chr>   <chr>   <chr>   <chr>   <chr>  <chr>  <chr>  <chr> 
# 1   1001976     1 C7931   G9519   G936    C7949   NEO070 NVS020 NVS020 NEO070
# 2   1001976     2 C7949   C7951   C7931   NA      NEO070 NEO070 NEO070 NA    
# 3   1002247     3 NA      C3490   C7931   I10     NA     NEO022 NEO070 CIR007