Recoding values in second data frame based on values in a different data frame

Question

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.

zone	code
1	12345
2	23456
2	23457
2	23458
3	45678
3	45679

the second data frame looks like this:

to_code	fr_code
23456	12345
23457	23456
45678	23457
45678	23458
12345	45678
12345	23457

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

to_code	from_code
2	1
2	2
3	2
3	2
1	3
1	2

Answer 1

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))

Answer 2

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