CODEWITHSUNDEEP
r
Ariya Woodard
Ariya Woodard

Reputation: 59

Count several rows and make a new column in R

I want to count several rows (x1-x4) and make a new column (x1_x4) in R looks like the below picture. Can anyone help me?

df <- data.frame(ID = c(1,2,3,4,5,6,7,8,9,10), 
                 x1 = c(0,NA,0,1,0,0,1,1,1,NA),   
                 x2 = c(0,NA,1,0,0,NA,0,1,0,0),    
                 x3 = c(0,NA,0,1,1,0,1,1,1,0), 
                 x4 = c(0,NA,0,0,0,0,1,1,1,1))

enter image description here

Upvotes: 2

Views: 66

Answers (4)

hello_friend
hello_friend

Reputation: 5788

Base R one (obfuscated) expression:

within(df, {x1_x4 <- apply(df[,grepl("^x", names(df))], 1,
       function(x){ifelse(all(is.na(x)), NA_integer_, sum(x, na.rm = TRUE))})})

Upvotes: 0

s_baldur
s_baldur

Reputation: 33498

vars <- paste0("x", 1:4)
df$x1_x4 <- rowSums(df[vars], na.rm = TRUE)
df[rowSums(is.na(df[vars]), na.rm = TRUE) == 4, "x1_x4"] <- NA
df
#    ID x1 x2 x3 x4 x1_x4
# 1   1  0  0  0  0     0
# 2   2 NA NA NA NA    NA
# 3   3  0  1  0  0     1
# 4   4  1  0  1  0     2
# 5   5  0  0  1  0     1
# 6   6  0 NA  0  0     0
# 7   7  1  0  1  1     3
# 8   8  1  1  1  1     4
# 9   9  1  0  1  1     3
# 10 10 NA  0  0  1     1

Upvotes: 0

tmfmnk
tmfmnk

Reputation: 39858

One dplyr solution could be:

df %>%
 rowwise() %>%
 mutate(x1_x4 = any(!is.na(c_across(-ID)))^NA * sum(c_across(-ID), na.rm = TRUE))

      ID    x1    x2    x3    x4 x1_x4
   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
 1     1     0     0     0     0     0
 2     2    NA    NA    NA    NA    NA
 3     3     0     1     0     0     1
 4     4     1     0     1     0     2
 5     5     0     0     1     0     1
 6     6     0    NA     0     0     0
 7     7     1     0     1     1     3
 8     8     1     1     1     1     4
 9     9     1     0     1     1     3
10    10    NA     0     0     1     1

Upvotes: 0

GKi
GKi

Reputation: 39647

You can use rowSums and test with apply if all are NA.

df$x1_x4 <- rowSums(df[-1], TRUE)
df$x1_x4[apply(is.na(df[2:5]), 1, all)] <- NA
#   ID x1 x2 x3 x4 x1_x4
#1   1  0  0  0  0     0
#2   2 NA NA NA NA    NA
#3   3  0  1  0  0     1
#4   4  1  0  1  0     2
#5   5  0  0  1  0     1
#6   6  0 NA  0  0     0
#7   7  1  0  1  1     3
#8   8  1  1  1  1     4
#9   9  1  0  1  1     3
#10 10 NA  0  0  1     1

Upvotes: 3

Related Questions