Mutate across multiple variables, using a list of third variables in R

user_9975513 0
Viewed 825

I need to obtain values of a list of grades (math, language, sci, etc.) conditional on the presence of valid values in 2016 (validity_2016=="yes"), into a new variable called grades_{subjects} (eg. grades_math).

df<-tibble(person = c("Alice", "Bob", "Mary"),
           validity_2016 = c(NA, "yes", NA),
           likes_ham = c("no", "yes", "yes"),
           grades_math_2015=c(6,2,4),
           grades_math_2016=c(3,5,7),
           grades_language_2015=c(7,1,9),
           grades_language_2016=c(3,6,7),
           grades_sci_2015=c(7,1,9),
           grades_sci_2016=c(3,6,7))

I was wondering the viability to use dplyr's mutate_at or mutate(across, in the following way:

dplyr::mutate(across(grades_math_2016, grades_language_2016,grades_sci_2016),
~dplyr::case_when(!is.na(validity_2016)~list(grades_math_2015,grades_language_2015,grades_sci_2015)~.),
.names="{col}"))

The outcomes should look like this:

df<-tibble(person = c("Alice", "Bob", "Mary"),
               validity_2016 = c(NA, "yes", NA),
               likes_ham = c("no", "yes", "yes"),
               grades_math_2015=c(6,2,4),
               grades_math_2016=c(3,5,7),
               grades_language_2015=c(7,1,9),
               grades_language_2016=c(3,6,7),
               grades_sci_2015=c(7,1,9),
               grades_sci_2016=c(3,6,7),
               grades_math=c(6,5,4),
               grades_language=c(7,6,7),
               grades_sci=c(7,6,9))
dplyr r
2 Answers

I'd recommend using a mutate and ifelse for each subject. Something like:

df2 = df %>%
  mutate(grades_math = ifelse(validatiy_2016 == "yes", grades_math_2016, grades_math_2015))

The downside if this approach is you need to repeat it for each subject. This could be automated with something like:

out_cols = c("grades_math", "grades_sci")

for(col in out_cols){
  c15 = paste0(col,"_2015")
  c16 = paste0(col,"_2016")
  df = df %>% mutate(!!sym(col) := ifelse(validaity_2016 == "yes", !!sym(c16), !!sym(c15)))
}

Where !!sym(x) takes the text saved in the variable x and turns it into a variable name (e.g. if x = "sci" then !!sym(x) gives us the variable sci instead of the text "sci" or the variable x).

user_77429810

tidyverse and rlang example:

This example uses mutate and case_when to assign the variables as you described. I wrapped it in a function in case this is something you would do often.

library(tidyverse)
library(rlang)

make_grade_columns <- function(df, condition_col, year_view){
  year_column_names <- colnames(df)[str_detect(colnames(df), year_view) & colnames(df) != condition_col & !str_detect(colnames(df), "validity")]
  year_prior_column_names <- colnames(df)[str_detect(colnames(df), as.character(as.numeric(year_view) - 1)) & colnames(df) != condition_col]
  return_col_names <- str_remove(year_column_names, "_\\d\\d\\d\\d")
  df <- df  %>% mutate(
    !!return_col_names[1] := case_when(
      (df %>% select(!!!condition_col)) == "yes" ~ !! sym(year_column_names[1]),
                                               T ~ !! sym(year_prior_column_names[1])),
    !!return_col_names[2] := case_when(
      (df %>% select(!!!condition_col)) == "yes" ~ !! sym(year_column_names[2]),
                                               T ~ !! sym(year_prior_column_names[2])),
    !!return_col_names[3] := case_when(
      (df %>% select(!!!condition_col)) == "yes" ~ !! sym(year_column_names[3]),
                                               T ~ !! sym(year_prior_column_names[3])))
  
  return(df)
}

make_grade_columns(df, "validity_2016", "2016") %>% 
 select(person, validity_2016, grades_math, grades_sci, grades_language) 

# # A tibble: 3 x 5
# person validity_2016    grades_math   grades_sci   grades_language
# <chr>  <chr>                  <dbl>      <dbl>           <dbl>
# 1 Alice  NA                      6          7               7
# 2 Bob    yes                     5          6               6
# 3 Mary   NA                      4          9               9

Suppose you changed it up and wanted to see the grades on the IF they answer "yes" to likes_ham. Simply have that as your conditioning column for the function.

make_grade_columns(df, "likes_ham", "2016")%>% 
  select(person, likes_ham, grades_math, grades_sci, grades_language) 
# # A tibble: 3 x 5
# person likes_ham    grades_math grades_sci grades_language
# <chr>  <chr>              <dbl>      <dbl>           <dbl>
# 1 Alice  no                  6          7               7
# 2 Bob    yes                 5          6               6
# 3 Mary   yes                 7          7               7

The function will take the yes answer and return the values from the year. If the answer is "no" then it will return the value from the year prior instead.

user_119967840
Related