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

user_16946790 0
Viewed 127

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
recode r
2 Answers

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

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
user_121587570
Related