create a new column by reading other columns based on one column's values

[xiaobai5883]

i saw some similar posts but couldn't work out mine.. T_T
basically i have a data like below from column rate1 to ind.. and i wanted to create another column "outcome" to extract the value from column rate1 to rate3 depending on column ind..
i could do it with case_when but when there are 20 rates.. i'm thinking there must be a way to "dynamically call" the column value??
please help?? thanks~!!!
sample photo:

sample code:
library(tidyverse)

df <- data.frame(stringsAsFactors = FALSE,
rate1 = c(8, 6, 1, 10, 10),
rate2 = c(6, 2, 2, 4, 3),
rate3 = c(10, 7, 4, 8, 4),
ind = c(1, 3, 1, 2, 3)
)

df <- df %>%
mutate(outcome = case_when(ind == 1 ~ rate1,
ind == 2 ~ rate2,
ind == 3 ~ rate3))

[nirgrahamuk]

Hi!

To help us help you, could you please prepare a reproducible example (reprex) illustrating your issue? Please have a look at this guide, to see how to create one:

FAQ: How to do a minimal reproducible example ( reprex ) for beginners

A minimal reproducible example consists of the following items: A minimal dataset, necessary to reproduce the issue The minimal runnable code necessary to reproduce the issue, which can be run on the given dataset, and including the necessary information on the used packages. Let's quickly go over each one of these with examples: Minimal Dataset (Sample Data) You need to provide a data frame that is small enough to be (reasonably) pasted on a post, but big enough to reproduce your issue. Let's say, as an example, that you are working with the iris data frame head(iris) #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> 1 5.1 3.5 1.4 0.…

---

Short Version

You can share your data in a forum friendly way by passing the data to share to the dput() function.
If your data is too large you can use standard methods to reduce it before sending to dput().
When you come to share the dput() text that represents your data, please be sure to format your post with triple backticks on the line before your code begins to format it appropriately.

```
( example_df <- structure(list(Sepal.Length = c(5.1, 4.9, 4.7, 4.6, 5, 5.4, 4.6, 
5, 4.4, 4.9), Sepal.Width = c(3.5, 3, 3.2, 3.1, 3.6, 3.9, 3.4, 
3.4, 2.9, 3.1), Petal.Length = c(1.4, 1.4, 1.3, 1.5, 1.4, 1.7, 
1.4, 1.5, 1.4, 1.5), Petal.Width = c(0.2, 0.2, 0.2, 0.2, 0.2, 
0.4, 0.3, 0.2, 0.2, 0.1), Species = structure(c(1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L), .Label = c("setosa", "versicolor", "virginica"
), class = "factor")), row.names = c(NA, -10L), class = c("tbl_df", 
"tbl", "data.frame")))
```

[xiaobai5883]

hi nirgrahamuk.. i've modified my question with some codes added in..
can you please help??
thanks~!

[lars]

Hi xiaobai5883 ,

I couldn't find yet an easier way of achieving this, but the following is providing the results you're looking for.

library(tidyverse)

df <- tibble(rate1 = c(8, 6, 1, 10, 10),
             rate2 = c(6, 2, 2, 4, 3),
             rate3 = c(10, 7, 4, 8, 4),
             ind = c(1, 3, 1, 2, 3)
             )
df
#> # A tibble: 5 x 4
#>   rate1 rate2 rate3   ind
#>   <dbl> <dbl> <dbl> <dbl>
#> 1     8     6    10     1
#> 2     6     2     7     3
#> 3     1     2     4     1
#> 4    10     4     8     2
#> 5    10     3     4     3

df %>% 
  ## a temporary id is needed for correct pivot handling and filling missing values
  mutate(id = row_number()) %>%
  pivot_longer(cols = starts_with("rate"), names_to = "rate_type", values_to = "value") %>% 
  ## determine outcome value based on the value as specifiec in the `ind` column
 mutate(outcome = ifelse(str_detect(rate_type, str_c("rate", ind, "$")), value, NA)) %>% 
  ## replace NA with the outcome value belonging to that id 
  group_by(id) %>% 
  fill(outcome, .direction = "updown") %>%
  ungroup() %>% 
  ## transform the dataset back to the original format
  pivot_wider(names_from = rate_type, values_from = "value") %>%
  ## keep the desired columns and place them in the correct order
  select(starts_with("rate"), ind, outcome)
#> # A tibble: 5 x 5
#>   rate1 rate2 rate3   ind outcome
#>   <dbl> <dbl> <dbl> <dbl>   <dbl>
#> 1     8     6    10     1       8
#> 2     6     2     7     3       7
#> 3     1     2     4     1       1
#> 4    10     4     8     2       4
#> 5    10     3     4     3       4

^{Created on 2021-03-08 by the reprex package (v1.0.0)}

HTH

EDIT 09MAR
The solution of @nirgrahamuk with the custom build case_build() function is definitely more elegant and thanks to his provided example I noticed that my solution didn't handle all observation correctly. I couldn't help it and fixed it above by modifying the pattern in str_detect().

[nirgrahamuk]

# make example data
set.seed(42)
df <- data.frame(
  ind = sample.int(20,20,replace=TRUE),
  rate1 = sample.int(20,20,replace=TRUE),
  rate2 = sample.int(20,20,replace=TRUE),
  rate3 = sample.int(20,20,replace=TRUE),
  rate4 = sample.int(20,20,replace=TRUE),
  rate5 = sample.int(20,20,replace=TRUE),
  rate6 = sample.int(20,20,replace=TRUE),
  rate7 = sample.int(20,20,replace=TRUE),
  rate8 = sample.int(20,20,replace=TRUE),
  rate9 = sample.int(20,20,replace=TRUE),
  rate10 = sample.int(20,20,replace=TRUE),
  rate11 = sample.int(20,20,replace=TRUE),
  rate12 = sample.int(20,20,replace=TRUE),
  rate13 = sample.int(20,20,replace=TRUE),
  rate14 = sample.int(20,20,replace=TRUE),
  rate15 = sample.int(20,20,replace=TRUE),
  rate16 = sample.int(20,20,replace=TRUE),
  rate17 = sample.int(20,20,replace=TRUE),
  rate18 = sample.int(20,20,replace=TRUE),
  rate19 = sample.int(20,20,replace=TRUE),
  rate20 = sample.int(20,20,replace=TRUE)
)

# possible solution
library(tidyverse)
library(rlang)
library(glue)
case_build <- function(indexvar,cols){
  imap_chr(cols,
     ~ glue("{indexvar} == {.y} ~ {.x}")) %>% 
    paste0(collapse=",") -> subs
  
  parse_expr(glue("case_when({subs})"))
}

df2 <- df %>%
  mutate(outcome = eval(case_build("ind",
                              paste0("rate",1:20))))