Choosing the right column based on a vector of column names

user_4172083 0
Viewed 48

I'm trying to pull values from columns based on the values in a vector. I'm not sure I have the right words to describe the problem, but the code should help.

This feels related to coalesce maybe not?

library(tidyverse)
# Starting table

dat <- 
tibble(
  A = 1:10,
  B = 31:40,
  C = 101:110,
  value = c("A", "C", "B", "A", "B", "C", "C", "B", "A", "A")
)

I want:

dat %>%
  mutate(
    output = c(1, 102, 33, 4, 35, 106, 107, 38, 9, 10)
  )

I could do

dat %>% 
  mutate(
    output = 
      case_when(value == "A" ~ A,
                value == "B" ~ B,
                value == "C" ~ C)
  )

but my real application has many values and I want to take advantage of value having the matching info

Is there a function that does:

dat %>%
mutate(output = grab_the_right_column(value))

Thanks!

dplyr tidy r
2 Answers

The rowwise approach would be less efficient, but it is compact within the tidyverse approaches to get the column value based on the column name for each row.

library(dplyr)
dat %>% 
   rowwise %>%
   mutate(output = get(value)) %>%
   ungroup

-output

# A tibble: 10 x 5
#       A     B     C value output
#   <int> <int> <int> <chr>  <int>
# 1     1    31   101 A          1
# 2     2    32   102 C        102
# 3     3    33   103 B         33
# 4     4    34   104 A          4
# 5     5    35   105 B         35
# 6     6    36   106 C        106
# 7     7    37   107 C        107
# 8     8    38   108 B         38
# 9     9    39   109 A          9
#10    10    40   110 A         10

These type of issues are more efficient with a row/column indexing approach from base R. Create a matrix of row sequence and the matching index of columns with the 'value' column and the column names to extract the element

dat$output <- as.data.frame(dat)[,1:3][cbind(seq_len(nrow(dat)), match(dat$value, names(dat)[1:3]))]
user_37322710

You can also use purrr and pmap():

library(dplyr)
library(purrr)

dat%>%mutate(output=
        pmap(., ~{
                v1<-c(...)
                v1[names(v1)==v1[['value']]]
                }
             )%>%
        as.numeric()%>%
        unlist)

# A tibble: 10 x 5
       A     B     C value output
   <int> <int> <int> <chr>  <dbl>
 1     1    31   101 A          1
 2     2    32   102 C        102
 3     3    33   103 B         33
 4     4    34   104 A          4
 5     5    35   105 B         35
 6     6    36   106 C        106
 7     7    37   107 C        107
 8     8    38   108 B         38
 9     9    39   109 A          9
10    10    40   110 A         10
user_139723330
Related