Doing data mapping for each value in a df-CodePudding

I have a data set like this

df1<-data.frame(ID=c(1,2,3,4),colA=c(101,102,103,104),colB=c(201,202,203,204))

df2<-data.frame(var_id=c(101,102,103,104,201,202,203,204),var_value=c("A","B","C","D","E","F","G","H"))

I want to map any value in df1 that is in df2$var_id with the corresponding string in df2$var_value.

Desired output

df1<-data.frame(ID=c(1,2,3,4),colA=c("A","B","C","D"),colB=c("E","F","G","H"))

I have tried write a function, and then do lapply, but it only display one var_value

CodePudding user response：

cols = c("colA", "colB")
df1[cols] <- lapply(df1[cols], \(x) df2$var_value[match(x, df2$var_id)])
df1
#   ID colA colB
# 1  1    A    E
# 2  2    B    F
# 3  3    C    G
# 4  4    D    H

CodePudding user response：

You can join twice.

library(dplyr)

df1 %>%
  left_join(df2, by = c("colA" = "var_id")) %>%
  left_join(df2, by = c("colB" = "var_id")) %>%
  select(ID, colA = var_value.x, colB = var_value.y)

#   ID colA colB
# 1  1    A    E
# 2  2    B    F
# 3  3    C    G
# 4  4    D    H

CodePudding user response：

with the tidyverse you can apply a function across several columns:

library(tidyverse)


df1 |>
  mutate(across(colA:colB, \(x) map_chr(x, \(y) with(df2,var_value[y == var_id] ))))
#>   ID colA colB
#> 1  1    A    E
#> 2  2    B    F
#> 3  3    C    G
#> 4  4    D    H

#or 

df1 |>
  mutate(across(colA:colB, \(x) with(df2, var_value[match(x, var_id)])))
#>   ID colA colB
#> 1  1    A    E
#> 2  2    B    F
#> 3  3    C    G
#> 4  4    D    H