Using dplyr to replace multiple columns of a tibble with a lookup from another

Question

I have a tibbles that look like this:

        B       C       D       E       F       G
1 260-098 260-073 260-051 260-057 260-055 260-009
2 260-098 260-073 260-051 260-057 260-055 260-009
3 260-098 260-009 260-051 260-057 260-055 260-005

and I have a database table that contains the following:

  roomID rnumber
  1      1 260-005
  2      2 260-009
  3      3 260-051
  4      4 260-055
  5      5 260-057
  6      6 260-073
  7      7 260-098

I would like to replace the entries of the tibble with the matching roomID. I thought I could do it with mutate_all, e.g.

 mutate_all(function(x){
      as_tibble(x) %>% 
        left_join(roomTbl, by = c("x" = "rnumber")) %>% 
        select(roomID)
     })

but I don't know what to use as the name of the anonymous column in the join. I have tried replacing "x" with names(x) but R does not like it.

score 2 · Accepted Answer · answered Nov 26 '17 at 11:07

Perhaps something like this?

x = scan(what = "character")
260-098 260-073 260-051 260-057 260-055 260-009
260-098 260-073 260-051 260-057 260-055 260-009
260-098 260-009 260-051 260-057 260-055 260-005


x = matrix(x,ncol=6,byrow=TRUE)
colnames(x) = LETTERS[2:7]
x = data.frame(x)

y = scan(what = "character")
1 260-005
2 260-009
3 260-051
4 260-055
5 260-057
6 260-073
7 260-098

y = matrix(y,ncol=2,  byrow = TRUE)
colnames(y) = c("roomID", "rnumber")
y = data.frame(y)

x = x %>% gather() %>% left_join(y, by = c("value" = "rnumber")) %>% 
  select(-value) %>% group_by(key) %>%  mutate(id=1:n()) %>% 
  spread(key = key, value = roomID)

score 2 · Answer 2 · edited Jan 18 '21 at 02:38

You can use dplyr::mutate_all() but you need a vectorized function to do the matching, since you are trying to iterate over each row in each column. Here I use match:

myTbl %>% mutate_all(~(roomTbl$roomID[match(., roomTbl$rnumber)]))

data read in:

myTbl <- read.table(text = "B       C       D       E       F       G
260-098 260-073 260-051 260-057 260-055 260-009
260-098 260-073 260-051 260-057 260-055 260-009
260-098 260-009 260-051 260-057 260-055 260-005",
header = TRUE, stringsAsFactors = FALSE)

roomTbl <- read.table(text = "roomID rnumber
1 260-005
2 260-009
3 260-051
4 260-055
5 260-057
6 260-073
7 260-098",
header = TRUE, stringsAsFactors = FALSE)

Using dplyr to replace multiple columns of a tibble with a lookup from another

2 Answers2