Using dplyr and mutate to counting the number of columns that meet a criterion

user_4462690 0
Viewed 328

Minimal example: A small dataframe with 4 columns and a variable that holds the name of a new column I want to create. The new column is TRUE if responses to more than a certain number of questions exceed a threshold, and is FALSE otherwise

df1 <- data.frame(ID = LETTERS[1:5],
                  Q1 = sample(0:10, 5, replace=T),
                  Q2 = sample(0:10, 5, replace=T)
                  Q3 = sample(0:10, 5, replace=T)
                  Q4 = sample(0:10, 5, replace=T)
)

This gives me my dataframe with responses to the various questions:

> df1
  ID Q1 Q2 Q3 Q4
1  A  2  4  5  0
2  B  9  6  6  3
3  C  5  5  3  2
4  D  0  5  3 10
5  E  7  5  6  7

I also define the following constants:

QUESTIONS  <- c("Q1”,  “Q2”,  “Q3”,  “Q4")
MY_NEW_COL <- "New_Col"
THESHOLD1  <- 5
THESHOLD2  <- 2

I want to add a new column named New_Col that is TRUE if more than THRESHOLD2 columns have a value in excess of THRESHOLD1. I can get this to work in a clumsy, but obvious way:

df1 %>%
    mutate(!!MY_NEW_COL := ( (Q1 > THREHOLD1) + (Q2> THREHOLD1) +   
                             (Q3 > THREHOLD1) + (Q4> THREHOLD1)  ) > THRESHOLD2)

This gives the right answer:

  ID Q1 Q2 Q3 Q4 New_Col
1  A  2  4  5  0   FALSE
2  B  9  6  6  3    TRUE
3  C  5  5  3  2   FALSE
4  D  0  5  3 10   FALSE
5  E  7  5  6  7    TRUE

But I would like to systematize this up as there are 17 questions in all. My code, which I show below, gives the wrong answer

df1 %>%
    mutate(!!MY_NEW_COL := sum(all_of(QUESTIONS) > THRESHOLD1)) > THRESHOLD2)

  ID Q1 Q2 Q3 Q4 New_Col
1  A  2  4  5  0    TRUE
2  B  9  6  6  3    TRUE
3  C  5  5  3  2    TRUE
4  D  0  5  3 10    TRUE
5  E  7  5  6  7    TRUE

What am I doing wring, and how can I fix this?

Many thanks in advance

Thomas Philips

dplyr r
3 Answers

As you didnt provide a seed, it is not possible to reproduce your results exactly. The solution to you problem is using across() and rowSums(), such that,

df1 %>%
    mutate(!!MY_NEW_COL := rowSums(across(QUESTIONS) > THESHOLD1) > THESHOLD2)

It gives the output,

  ID Q1 Q2 Q3 Q4 New_Col
1  A  7  9  1  1   FALSE
2  B  3  9  9  7    TRUE
3  C  4  0  6  6   FALSE
4  D  5  1  6 10   FALSE
5  E  6  5  5  1   FALSE
user_104007910

I don't know if the following output is what you have in mind, but I first checked whether all Qs are greater than threshold 1 and if so whether the sum of which are greater than threshold2:

library(dplyr)

f1 <- function(x, threshold1 = 2, threshold2 = 5) {
  df1 <- df1 %>%
    group_by(ID) %>%
    mutate(threshold_1 = if_all(, ~ .x > 2, TRUE),
           sum_Qs = sum(Q1:Q4),
           threshold_2 = if_else(sum_Qs > threshold2 & threshold_1 == TRUE, 
                                 TRUE, FALSE))
  df1
}

f1(df1, 2, 5)

# A tibble: 5 x 8
# Groups:   ID [5]
  ID       Q1    Q2    Q3    Q4 threshold_1 sum_Qs threshold_2
  <chr> <int> <int> <int> <int> <lgl>        <int> <lgl>      
1 A         8     0     1    10 FALSE           27 FALSE      
2 B         2     3     2     8 FALSE           35 FALSE      
3 C         1     8     4     3 FALSE            6 FALSE      
4 D         9     3     3     9 TRUE             9 TRUE       
5 E         1     3     0     1 FALSE            1 FALSE
user_143145200

We can also do

library(dplyr)
library(purrr)
library(magrittr)
 df1 %>% 
   mutate(!! MY_NEW_COL := map(select(cur_data(), starts_with("Q")),
        ~ .x > THESHOLD1) %>%
             reduce(`+`) %>% 
             is_greater_than(THESHOLD2) )
user_37322710
Related