How to count occurrences of a specific value across multiple columns?-CodePudding

I want to count how many times a specific value occurs across multiple columns and put the number of occurrences in a new column. My dataset has a lot of missing values but only if the entire row consists solely of NA's, it should return NA. If possible, I would prefer something that works with dplyr pipelines.

Example dataset:

df <- data.frame(c1 = sample(1:4, 20, replace = TRUE),
                 c2 = sample(1:4, 20, replace = TRUE),
                 c3 = sample(1:4, 20, replace = TRUE),
                 c4 = sample(1:4, 20, replace = TRUE),
                 c5 = sample(1:4, 20, replace = TRUE))

for (i in 1:5) {
  df[sample(1:20, 1), sample(1:5, 1)] <- NA
  df[sample(1:20, 1), ] <- NA
}

   c1 c2 c3 c4 c5
1   1  2  4  4  1
2   2  2  1  3  4
3   2  4  4  3  3
4   4  2  3  2  1
5   4  2  4  1  3
6  NA  1  2  4  4
7   3 NA  4 NA  4
8  NA NA NA NA NA
9   1  3  3  2  2
10 NA NA NA NA NA

I have tried with rowwise() and rowSums. Some non-working examples here:

# First attempt
df <- df %>%
  rowwise() %>%
  mutate(count2 = sum(c_across(c1:c5, ~.x %in% 2)))

# Second attempt
df <- df %>%
  rowwise() %>%
  mutate(count2 = sum(c_across(select(where(c1:c5 %in% 2)))))

# With rowSums
df <- df %>%
  rowwise() %>%
  mutate(count4 = rowSums(select(c1:c5 %in% 4), na.rm = TRUE))

CodePudding user response：

How about this:

library(dplyr)
df <- data.frame(c1 = sample(1:4, 20, replace = TRUE),
                 c2 = sample(1:4, 20, replace = TRUE),
                 c3 = sample(1:4, 20, replace = TRUE),
                 c4 = sample(1:4, 20, replace = TRUE),
                 c5 = sample(1:4, 20, replace = TRUE))

for (i in 1:5) {
  df[sample(1:20, 1), sample(1:5, 1)] <- NA
  df[sample(1:20, 1), ] <- NA
}

df %>% 
  rowwise() %>% 
  mutate(count2 = sum(na.omit(c_across(c1:c5)) == 2), 
         count2 = ifelse(all(is.na(c_across(c1:c5))), NA, count2))
#> # A tibble: 20 × 6
#> # Rowwise: 
#>       c1    c2    c3    c4    c5 count2
#>    <int> <int> <int> <int> <int>  <int>
#>  1    NA    NA    NA    NA    NA     NA
#>  2     2     2     3     4     2      3
#>  3     1     1     1     4     4      0
#>  4     2     3     3     2     4      2
#>  5    NA    NA    NA    NA    NA     NA
#>  6     1     1     1     2     1      1
#>  7     3     3     2     3     4      1
#>  8     1     1     4     3     4      0
#>  9    NA    NA    NA    NA    NA     NA
#> 10    NA    NA    NA    NA    NA     NA
#> 11     2     3     3     4     1      1
#> 12     2     1     4     2    NA      2
#> 13     4     4     2    NA     2      2
#> 14     4     2     3     3     2      2
#> 15     1     3     4     2     2      2
#> 16     1     1     3     3     2      1
#> 17     1     1     1     4     4      0
#> 18     2     4     4    NA     1      1
#> 19    NA    NA    NA    NA    NA     NA
#> 20     4     1     1    NA     4      0

^{Created on 2022-12-08 by the reprex package (v2.0.1)}