Use a dynamcially created variable to select column in mutate-CodePudding

repro

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

Hello 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.

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.

CodePudding user response：

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

CodePudding user response：

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