How to count the number of occurrences of a given value for each row?

Question

I'm sure this is a really easy fix but I can't seem to find the answer... I am trying to create a column at the end of my dataframe that is a sum of the number of times a specific value (say "1") appears across that row. So for example, if I started with the following dataframe:

X1 <- c(5,1,7,8,1,5)
X2 <- c(5,0,0,2,3,7)
X3 <- c(6,2,3,4,1,7)
X4 <- c(1,1,5,2,1,7)

df <- data.frame(id,X1,X2,X3,X4)

  id X1 X2 X3 X4
1  1  5  5  6  1
2  2  1  0  1  1
3  3  7  0  3  5
4  4  8  2  4  2
5  5  1  3  2  1
6  6  5  7  7  7

and I was trying to identify how many times the value "1" appears across that row, I would want the output to look like this:

  id X1 X2 X3 X4 one_appears
1  1  5  5  6  1           2
2  2  1  0  1  1           3
3  3  7  0  3  5           0
4  4  8  2  4  2           0
5  5  1  3  2  1           2
6  6  5  7  7  7           0

Thanks very much in advance!

Does this answer your question? [Count occurrences of value in a set of variables in R (per row)](https://stackoverflow.com/questions/24015557/count-occurrences-of-value-in-a-set-of-variables-in-r-per-row) — AkselA, Jan 18 '23 at 18:44

Chamkrai · Answer 1 · 2023-01-19T09:41:13.403

7

library(tidyverse)

df %>% 
  mutate(
    one = rowSums(across(everything(), ~ .x == 1))
  )

# A tibble: 6 × 6
     id    X1    X2    X3    X4   one
  <int> <dbl> <dbl> <dbl> <dbl> <dbl>
1     1     5     5     6     1     2
2     2     1     0     2     1     2
3     3     7     0     3     5     0
4     4     8     2     4     2     0
5     5     1     3     1     1     3
6     6     5     7     7     7     0

EDIT:

df %>% 
  mutate(
    one = rowSums(across(starts_with("X"), ~ .x == 1))
  )

df %>% 
  mutate(
    one = rowSums(across(X1:X4, ~ .x == 1))
  )

edited Jan 19 '23 at 09:41

answered Jan 18 '23 at 17:51

Chamkrai

5,912
1
4
14

1

please add `library(dplyr)` to your excellent answer. – TarJae Jan 18 '23 at 18:12
Thank you! Is there any way to exclude certain columns (e.g., id)? Say if I only wanted to do rowSums for [2:5]? – user21027866 Jan 18 '23 at 18:12
Nevermind sorry, I figured it out - thank you! – user21027866 Jan 18 '23 at 18:23
@user21027866 Yes, sorry for the late reply. I have added a few lines showing you how that could be done in different ways, perhaps the same as your solution. – Chamkrai Jan 19 '23 at 09:42

score 5 · Accepted Answer · answered Jan 18 '23 at 17:49

We can use rowSums on a logical matrix

df$one_appears <- rowSums(df == 1, na.rm = TRUE)

-output

> df
  id X1 X2 X3 X4 one_appears
1  1  5  5  6  1           2
2  2  1  0  1  1           3
3  3  7  0  3  5           0
4  4  8  2  4  2           0
5  5  1  3  2  1           2
6  6  5  7  7  7           0

score 2 · Answer 3 · answered Jan 18 '23 at 17:57

Another option using apply with sum:

id <- c(1:6)
X1 <- c(5,1,7,8,1,5)
X2 <- c(5,0,0,2,3,7)
X3 <- c(6,2,3,4,1,7)
X4 <- c(1,1,5,2,1,7)

df <- data.frame(id,X1,X2,X3,X4)
df$one_appear = apply(df, 1, \(x) sum(x == 1))
df
#>   id X1 X2 X3 X4 one_appear
#> 1  1  5  5  6  1          2
#> 2  2  1  0  2  1          2
#> 3  3  7  0  3  5          0
#> 4  4  8  2  4  2          0
#> 5  5  1  3  1  1          3
#> 6  6  5  7  7  7          0

^{Created on 2023-01-18 with reprex v2.0.2}

score 0 · Answer 4 · answered Jan 18 '23 at 18:23

This answer may not be the best of the approach, but an alternative that I tried so thought to share

code

library(dplyr)

X1 <- c(5,1,7,8,1,5)
X2 <- c(5,0,0,2,3,7)
X3 <- c(6,2,3,4,1,7)
X4 <- c(1,1,5,2,1,7)

df <- data.frame(X1,X2,X3,X4) %>% rowwise %>% 
mutate(across(starts_with('X'), function(x) ifelse(x==1,1,NA), .names = 'Y_{col}'), 
                                         one_appears=sum(across(starts_with('Y')), na.rm = T)
                                         )

How to count the number of occurrences of a given value for each row?

4 Answers4

code