I want to recode the values in my second data frame based on the corresponding value in a different data frame.

for example, here's what the first data frame looks like. Each code is assigned to a corresponding zone.

the second data frame looks like this:

but I want to recode it based on the corresponding zones, so it would look like this:

CodePudding user response：

We can use match to get the index of the matched 'code' from 'df1' with the values of the columns in df2 and extract the corresponding 'zone'

library(dplyr)
df3 <- df2 %>% 
   mutate(across(c(to_code, fr_code), ~ df1$zone[match(.x, df1$code)]))

Or in base R

df3 <- df2

df3[c("to_code", "fr_code")] <- lapply(df3[c("to_code", "fr_code")],
      function(x) df1$zone[match(x, df1$code)])

-output

df3
  to_code fr_code
1       2       1
2       2       2
3       3       2
4       3       2
5       1       3
6       1       2

data

df1 <- structure(list(zone = c(1L, 2L, 2L, 2L, 3L, 3L), code = c(12345L, 
23456L, 23457L, 23458L, 45678L, 45679L)), class = "data.frame", row.names = c(NA, 
-6L))

df2 <- structure(list(to_code = c(23456L, 23457L, 45678L, 45678L, 12345L, 
12345L), fr_code = c(12345L, 23456L, 23457L, 23458L, 45678L, 
23457L)), class = "data.frame", row.names = c(NA, -6L))

CodePudding user response：

We can try the following base R code using match

> df2[] <- with(df1, zone[match(unlist(df2), code)])
> df2
  to_code fr_code
1       2       1
2       2       2
3       3       2
4       3       2
5       1       3
6       1       2