How can I replace several values in a data frame from another one if they are repeated more than once

user_16588144 0
Viewed 34

I have a data frame for the A matrix that looks like this:

  V1 V2 V3
1  1  1  1
2  2  1  0
3  2  2  1
4  3  1  0
5  3  2  0
6  3  3  1

and I also have a cross reference data frame that has the original ID and the new ID that looks like this:

  ID newID
1 SE1     1
2  T3     2
3  R5     3
4  R7     4
5  R9     5
6 A11     6

how can I use to obtain a data frame like this (after creating the new columns ID1 and ID2)?

  V1 V2 V3 ID1 ID2
1  1  1  1 SE1 SE1
2  2  1  0 T3  SE1
3  2  2  1 T3  T3
4  3  1  0 R5  SE1
5  3  2  0 R5  T3
6  3  3  1 R5  R5
dataframe r for-loop merge replace
3 Answers

We could use inner_join twice:

library(dplyr)

df %>% 
    inner_join(df1, by=c("V1"="newID")) %>% 
    inner_join(df1, by=c("V2"="newID")) %>% 
    rename(ID1 = ID.x, ID2 = ID.y)


  V1 V2 V3 ID1 ID2
1  1  1  1 SE1 SE1
2  2  1  0  T3 SE1
3  2  2  1  T3  T3
4  3  1  0  R5 SE1
5  3  2  0  R5  T3
6  3  3  1  R5  R5
user_133216470

You could deframe your reference data.frame creating a lookup table:

ref <- tibble::deframe(ref[c("newID", "ID")])
#>     1     2     3     4     5     6 
#> "SE1"  "T3"  "R5"  "R7"  "R9" "A11"

Now you could just use mutate:

library(dplyr)

data %>% 
  mutate(ID1 = ref[V1],
         ID2 = ref[V2])

to get

# A tibble: 6 x 5
     V1    V2    V3 ID1   ID2  
  <dbl> <dbl> <dbl> <chr> <chr>
1     1     1     1 SE1   SE1  
2     2     1     0 T3    SE1  
3     2     2     1 T3    T3   
4     3     1     0 R5    SE1  
5     3     2     0 R5    T3   
6     3     3     1 R5    R5
user_125052510

An option in base R is to create a named vector and use to match and replace

v1 <- with(df2, setNames(ID, newID))
df1[paste0("ID", 1:2)] <- lapply(df1[-3], function(x) v1[as.character(x)])

-output

> df1
  V1 V2 V3 ID1 ID2
1  1  1  1 SE1 SE1
2  2  1  0  T3 SE1
3  2  2  1  T3  T3
4  3  1  0  R5 SE1
5  3  2  0  R5  T3
6  3  3  1  R5  R5

data

df1 <- structure(list(V1 = c(1L, 2L, 2L, 3L, 3L, 3L), V2 = c(1L, 1L, 
2L, 1L, 2L, 3L), V3 = c(1L, 0L, 1L, 0L, 0L, 1L)), class = "data.frame", 
row.names = c("1", 
"2", "3", "4", "5", "6"))

df2 <- structure(list(ID = c("SE1", "T3", "R5", "R7", "R9", "A11"), 
    newID = 1:6), class = "data.frame", row.names = c("1", "2", 
"3", "4", "5", "6"))
user_37322710
Related