Conditionally Mutate many columns with a similarly named (dynamic?) variable | Mutate to choose between two columns

[dhusmann]

Hi,

I have a dataset with two alternative measures for each variable. I want to create a new data table that chooses between each alternative, depending on the specified conditions. Here is a reproducible example using the iris dataset.

generate the dataset, with two similarly named alternatives

library(tidyverse)
iris_with_two_alternatives <- iris %>%
tibble() %>%
mutate_at(vars(Sepal.Length:Petal.Width), ~.*5) %>%
rename_all(
funs(paste0(., ".alternative"))) %>%
bind_cols(iris)

Pick one of two alternatives

iris_alternative_cols_chosen <- iris_with_two_alternatives %>%
mutate_at(vars(Sepal.Length:Petal.Width),
~case_when(
. > 1 & . < 4 ~ vars(paste0(., ".alternative")),
. <= 1 | . >=5, ~ . ,
. >=4 & . <5, ~ .+1 )) %>%
select(Sepal.Length:Petal.Width)

The above FAILS, producing:

Error:Problem with mutate() input Sepal.Length.
x Case 2 (. > 1 & . < 4 ~ vars(paste0(., ".alternative"))) must be a two-sided formula, not a logical vector.
Input Sepal.Length is (structure(function (..., .x = ..1, .y = ..2, . = ..1) ....

I know at least one issue is in line 4, where I'm providing cases when I want to use the similarly named variable. I don't know how to indicate (wrap? embrace?) that the result of the paste is not just text, but the corresponding variable from the indicated column.

Second, I think I'm supposed to be using mutate(across()), but am having trouble using that in place of mutate_at(), so an additional thanks if you can get it working with mutate(across()).

Thanks!
Dylan

[TimTeaFan]

The short answer is, that you cannot access the variable names neither with mutate_at nor with across and although cur_column can help to access the name and use it as input in a paste for example, it won't work with !! sym(paste()) to actually construct the variable name and evaluate it.

There are several posts on SO with similar issues and I submitted this issue to {dplyr}. Although it is too specific to be integrated into {dplyr}, you will find a couple of workarounds in the issue itself. I hope it helps.

[dhusmann]

Huge help! I was able to use one of your workarounds successfully.

I share the intuition you express in the issue page, this is not such an uncommon task

I had to make a slight change to give both of the alternative variables a slightly different name:

generate dataset, alternate variables named with .v1 or .v2

iris_v1 <- iris %>%
tibble() %>%
rename_all(
funs(paste0(., ".v1")))

iris.v1.AND.v2 <- iris %>%
tibble() %>%
mutate_at(vars(Sepal.Length:Petal.Width), ~.*5) %>%
rename_all(
funs(paste0(., ".v2"))) %>%
bind_cols(iris_v1)

I took your function:

tidyverse workaround: custom function + reduce

gen_vars <- function(df, x) {

mutate(df,
"{x}.Ratio" := case_when(
!! sym(paste0(x, ".Length")) / !! sym(paste0(x, ".Width")) > 2 ~ T,
T ~ F
)
)
}
sym
my_vars <- c("Petal", "Sepal")

iris %>%
reduce(my_vars, gen_vars, .init = .)

And modified it for my purpose, which worked !

gen_vars_mod <- function(df, x) {

mutate(df,
"{x}.iris_or_alternative" := case_when(
!! sym(paste0(x, ".v1")) > 1 & !! sym(paste0(x, ".v1")) < 4 ~ !! sym(paste0(x, ".v2")),
!! sym(paste0(x, ".v1")) <= 1 | !! sym(paste0(x, ".v1")) >=5 ~ !! sym(paste0(x, ".v1")),
!! sym(paste0(x, ".v1")) >=4 & !! sym(paste0(x, ".v1")) <5 ~ !! sym(paste0(x, ".v1"))+1 ,
T~ !! sym(paste0(x, ".v1"))
)
)

}
iris.v1.OR.v2 <- iris.v1.AND.v2 %>%
reduce(my_vars, gen_vars_mod, .init = .) %>%
select(contains("or"), Species.v1)%>%
rename(Species = "Species.v1")

I tried getting it to work with the original dataset, but using this this function did not work

gen_vars_mod <- function(df, x) {

mutate(df,
"{x}.iris_or_alternative" := case_when(
x > 1 & x < 4 ~ !! sym(paste0(x, ".alternative")),
x <= 1 | x >=5 ~ x,
x >=4 x <5. ~ x+1 ,
T~ x
)
)

}

Thank you!