How to count columns that match a condition rowwise?

user_4184556 0
Viewed 52

Take this data.frame:

demo<-structure(list(q5a = c(1, 1, 10, 10, 8, 7, 8, 8, 2, 10), q5b = c(10, 
6, 10, 7, 5, 10, 8, 8, 8, 8), q5c = c(5, 6, 10, 8, 8, 6, 10, 
10, 1, 9), q5d = c(10, 2, 10, 10, 10, 9, 10, 10, 10, NA), q5e = c(5, 
NA, 10, 10, 8, 9, 10, 10, 1, 8), q5f = c(1, 2, 10, 8, 8, 9, 10, 
10, 1, 6), q5g = c(10, 4, 8, 10, 2, 8, 8, 8, 10, 6), q5h = c(1, 
1, 10, 10, 9, 8, 10, 10, 1, 6), q5i = c(5, 3, 10, 6, 4, 2, 6, 
10, 1, 3), q5j = c(10, 10, 1, 1, 2, 6, 8, 6, 9, 1), q5k = c(10, 
2, 10, 10, 10, 10, 10, 10, 10, 6), q5l = c(10, 5, 10, 10, 9, 
10, 10, 10, 10, 8), q5m = c(6, 4, 10, 10, 9, 10, 10, 10, 9, 8
), q5n = c(10, 1, 10, 10, 5, 10, 10, 8, 10, 9), q5o = c(10, 4, 
10, 10, 5, 10, 8, 8, 10, 8)), row.names = c(NA, -10L), class = c("tbl_df", 
"tbl", "data.frame"))

I would like to create a summary variable that tells me how many variables of each row equal 10.

Better yet, if these columns were part of a much bigger data frame (q1, q2, q3 etc.), how can I create a new variable that takes ONLY those columns that start with q5 and do that calculation on them? I prefer working with the tidyverse, but base solutions are also welcome.

Desired end result should look like this:

# A tibble: 10 x 15
     q5a   q5b   q5c   q5d   q5e   q5f   q5g   q5h   q5i   q5j   q5k   q5l   q5m   q5n   q5o tens
   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
 1     1    10     5    10     5     1    10     1     5    10    10    10     6    10    10   8
 2     1     6     6     2    NA     2     4     1     3    10     2     5     4     1     4   1
 3    10    10    10    10    10    10     8    10    10     1    10    10    10    10    10  13
 4    10     7     8    10    10     8    10    10     6     1    10    10    10    10    10  10

etc...

I have a feeling it should be very easy, but it's been a while since I worked on R and I just can't figure it out. Thank you!

r
3 Answers

One option could be:

demo %>%
 mutate(tems = rowSums(select(., starts_with("q5")) == 10, na.rm = TRUE))

     q5a   q5b   q5c   q5d   q5e   q5f   q5g   q5h   q5i   q5j   q5k   q5l   q5m   q5n   q5o  tems
   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
 1     1    10     5    10     5     1    10     1     5    10    10    10     6    10    10     8
 2     1     6     6     2    NA     2     4     1     3    10     2     5     4     1     4     1
 3    10    10    10    10    10    10     8    10    10     1    10    10    10    10    10    13
user_59645570

We can use sum with c_across after doing a rowwise

library(dplyr)
demo %>%
   rowwise %>% 
   mutate(tems = sum(c_across(starts_with('q5')) == 10, na.rm = TRUE)) %>%
   ungroup

-output

# A tibble: 10 x 16
#     q5a   q5b   q5c   q5d   q5e   q5f   q5g   q5h   q5i   q5j   q5k   q5l   q5m   q5n   q5o  tems
#   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <int>
# 1     1    10     5    10     5     1    10     1     5    10    10    10     6    10    10     8
# 2     1     6     6     2    NA     2     4     1     3    10     2     5     4     1     4     1
# 3    10    10    10    10    10    10     8    10    10     1    10    10    10    10    10    13
# ...

Or using collapse

library(collapse)
demo$tems <- dapply(gvr(demo, '^q5'),  function(x)
        fsum(x == 10), MARGIN = 1)
demo$tems
#[1]  8  1 13 10  2  6  9  9  6  1
user_37322710

A data.table option

setDT(demo)[, tens := rowSums(.SD == 10, na.rm = TRUE), .SDcols = patterns("^q5")]

gives

    q5a q5b q5c q5d q5e q5f q5g q5h q5i q5j q5k q5l q5m q5n q5o tens
 1:   1  10   5  10   5   1  10   1   5  10  10  10   6  10  10    8
 2:   1   6   6   2  NA   2   4   1   3  10   2   5   4   1   4    1
 3:  10  10  10  10  10  10   8  10  10   1  10  10  10  10  10   13
 4:  10   7   8  10  10   8  10  10   6   1  10  10  10  10  10   10
 5:   8   5   8  10   8   8   2   9   4   2  10   9   9   5   5    2
 6:   7  10   6   9   9   9   8   8   2   6  10  10  10  10  10    6
 7:   8   8  10  10  10  10   8  10   6   8  10  10  10  10   8    9
 8:   8   8  10  10  10  10   8  10  10   6  10  10  10   8   8    9
 9:   2   8   1  10   1   1  10   1   1   9  10  10   9  10  10    6
10:  10   8   9  NA   8   6   6   6   3   1   6   8   8   9   8    1
user_121587570
Related