Use a dynamcially created variable to select column in mutate

Question

I am trying to use the value of vector_of_names[position] in the code above to dynamically select a column from data which to use for the value "age" using mutate.

vector_of_names <- c("one","two","three")

id <- c(1,2,3,4,5,6)
position <- c(1,1,2,2,1,1)
one <- c(32,34,56,77,87,98)
two <- c(45,67,87,NA,33,56)
three <- c(NA,NA,NA,NA,NA,60)
data <- data.frame(id,position,one,two,three)

attempt <- data %>% 
  mutate(age=vector_of_names[position])

I see a similar question here but the various answer fail as I am using a variable within the data "posistion" on which to select the column from the vector of names which is never recognised as I suspect is is looking outside of the data.

I am taking this approach as the number of columns "one","two" and "three" is not known before hand but the vector of their names is, and so they need to be selected dynamically.

Could you share what final result you are expecting to see? – denisafonin Oct 22 '21 at 13:04 — denisafonin, Oct 22 '21 at 13:04

tmfmnk · Answer 1 · 2021-10-22T13:13:00.893

3

You could do:

data %>% 
    rowwise() %>%
    mutate(age = c_across(all_of(vector_of_names))[position])

     id position   one   two three   age
  <dbl>    <dbl> <dbl> <dbl> <dbl> <dbl>
1     1        1    32    45    NA    32
2     2        1    34    67    NA    34
3     3        2    56    87    NA    87
4     4        2    77    NA    NA    NA
5     5        1    87    33    NA    87
6     6        1    98    56    60    98

If you want to be more explicit about what values should be returned:

named_vector_of_names <- setNames(seq_along(vector_of_names), vector_of_names)

data %>% 
    rowwise() %>%
    mutate(age = get(names(named_vector_of_names)[match(position, named_vector_of_names)]))

edited Oct 22 '21 at 13:13

answered Oct 22 '21 at 13:04

tmfmnk

38,881
4
47
67

This is an excellent answer and works perfectly thank you., is it the 'all_of' that allows the use of variables to select columns? – Richard Packer Oct 22 '21 at 14:42
Yes, with `all_of()`, you can use column names stored in a character vector. – tmfmnk Oct 22 '21 at 14:44

score 1 · Answer 2 · answered Oct 22 '21 at 13:19

Base R vectorized option using matrix subsetting.

data$age <- data[vector_of_names][cbind(1:nrow(data), data$position)]
data

#  id position one two three age
#1  1        1  32  45    NA  32
#2  2        1  34  67    NA  34
#3  3        2  56  87    NA  87
#4  4        2  77  NA    NA  NA
#5  5        1  87  33    NA  87
#6  6        1  98  56    60  98

Use a dynamcially created variable to select column in mutate

2 Answers2