Extracting a value based on a value of a column in R

Question

I have the below data frame

AccountNumber <- 123456

Tenure <- 3

x1<-0.890625

x2<-0.894634

x3<-0.814514

x4<-0.875413

x5<- 0.8765712

df <- data.frame(AccountNumber, Tenure,x1,x2,x3,x4,x5)

Input

Based on the Tenure column , i have to create a new column Rate . If the Tenure is equal to 3 then , then Rate will pick the value 0.814514 from the third column(x3), If Tenure is 2 then Rate will pick the value 0.894634(from column x2) and so on .

Output

Welcome to StackOverflow! Please read the info about [how to ask a good question](http://stackoverflow.com/help/how-to-ask) and how to give a [reproducible example](http://stackoverflow.com/questions/5963269). This will make it much easier for others to help you. — Sotos, Jul 28 '22 at 11:40
How many different Tenures do you have? If there are only few you could do `library(dplyr) df %>% mutate(Rate = case_when(Tenure == 3 ~ x3, Tenure == 2 ~ x2...))` — Julian, Jul 28 '22 at 12:44
@Julian Tenure can vary from 1-60. I have just illustrated an example of a row — narayanpr23, Jul 28 '22 at 12:47
also there are more columns ( x1 to x60). the idea is to check the value of tenure for each account and match it to the column value. So if tenure is 60 for an account . Then it should fetch the value in column x60 and that will be its Rate — narayanpr23, Jul 28 '22 at 12:55

Julian · Answer 1 · 2022-07-28T13:19:57.263

0

Based on Dynamic `case_when` that allows for different number of conditions and conditions itself a suggestion (you would have to change the sequence to 1:60):

library(dplyr) 
library(stringr)
library(purrr)


number_of_variables <- c(paste0("x", seq(1:3)))
  

all_conditions <- paste0("Tenure == ", seq(1:3)) %>% 
  as.list() %>% 
  purrr::set_names(number_of_variables)

code <- purrr::imap(all_conditions, ~ str_c(.x, " ~ ", .y)) %>% 
  str_c(collapse = ", ") %>% str_c("case_when(",.,")")

df %>% 
  mutate(Rate = !!parse_quo(code, env = rlang::caller_env()))

edited Jul 28 '22 at 13:19

answered Jul 28 '22 at 13:07

Julian

6,586
2
9
33

I am getting the following error :-Error in local_error_context(dots = dots, .index = i, mask = mask) : promise already under evaluation: recursive default argument reference or earlier problems? – narayanpr23 Jul 28 '22 at 13:17
Maybe try to restart the session and try again? – Julian Jul 28 '22 at 13:21
Did it work for you now? – Julian Aug 29 '22 at 14:29

score 0 · Answer 2 · answered Jul 28 '22 at 13:34

0

An idea via base R can be,

mapply(function(x, y)x[y], 
      split(df[-c(1:2)], seq(nrow(df))), 
      split(df$Tenure, seq_along(df$Tenure)))

$`1.X1`
[1] 7

$`2.X3`
[1] 7

$`3.X2`
[1] 4

$`4.X3`
[1] 10

$`5.X1`
[1] 5

answered Jul 28 '22 at 13:34

Sotos

51,121
6
32
66

Extracting a value based on a value of a column in R

2 Answers2