Address a column with the variable value of another column in the same row using the pipe operator

user_7460688 0
Viewed 150

An example data.frame:

library(tidyverse)
example <- data.frame(matrix(sample.int(15),5,3),
                      sample(c("A","B","C"),5,replace=TRUE) ) %>%
                      `colnames<-`( c("A","B","C","choose") ) %>% print()

Output:

   A  B  C choose
1  9 12  4      A
2  7  8 13      C
3  5  1  2      A
4 15  3 11      C
5 14  6 10      B

The column "choose" indicates which value should be selected from the columns A,B,C

My humble solution for the column "result" :

cols <- c(A=1,B=2,C=3)
col_index <- cols[example$choose]
xy <- cbind(1:nrow(example),col_index)
example %>% mutate(result = example[xy])

Output:

   A  B  C choose result
1  9 12  4      A      9
2  7  8 13      C     13
3  5  1  2      A      5
4 15  3 11      C     11
5 14  6 10      B      6

I'am sure there is a more elegant solution with dplyr, but my attemps with "rowwise" or "accross" failed.

Is it possible to get here a one-line-solution?

dplyr r pipe
2 Answers

The efficient option is to make use of row/column indexing

example$result <- example[1:3][cbind(seq_len(nrow(example)), 
          match(example$choose, names(example)))]

with dplyr, we may use get with rowwise

library(dplyr)
example %>%
    rowwise %>%
    mutate(result = get(choose)) %>%
    ungroup

Or instead of get use cur_data()

example %>% 
   rowwise %>%
   mutate(result = cur_data()[[choose]]) %>%
   ungroup

Or the vectorized option with row/column indexing

example %>% 
   mutate(result = select(., where(is.numeric))[cbind(row_number(), 
       match(choose, names(example)))])
user_37322710

Here is an alternative way:

library(dplyr)
library(tidyr)

example %>% 
  pivot_longer(
    -choose,
  ) %>% 
  filter(choose == name) %>% 
  select(result=value) %>% 
  bind_cols(example) 
  

  result     A     B     C choose
   <int> <int> <int> <int> <chr> 
1      9     6     9     1 B     
2     14     5     2    14 C     
3      7     8     7     3 B     
4     15    15     4    12 A     
5     11    13    10    11 C
user_133216470
Related