Assign results of rowSums to a new column in R

Question

I have the below dataframe which contains number of products sold in each quarter by a salesman.

Ag      Q1  Q2  Q3  Q4
s121    4   3   0   0
S431    0   0   2   1   
S246    0   0   0   2
S444    2   2   2   2

I am trying to create a calculated column C which is basically sum of all columns where the value is not zero. So basically number of quarters a salesman has been active.

Expected output:

Ag      Q1  Q2  Q3  Q4 C
s121    4   3   0   0   2
S431    0   0   2   1   2
S246    0   0   0   2   1
S444    2   2   2   2   4

I have tried

df$C <- df[rowSums(df[,c(Q1,Q2,Q3,Q4)])]

but this gives undefined columns selected error. Can someone please help me rectify this?

score 1 · Answer 1 · answered Nov 25 '19 at 09:27

Simply this way:

dat$C <- rowSums(dat[, -1] != 0)
dat

#     Ag Q1 Q2 Q3 Q4 C
# 1 s121  4  3  0  0 2
# 2 S431  0  0  2  1 2
# 3 S246  0  0  0  2 1
# 4 S444  2  2  2  2 4

Data:

dat <- structure(
  list(
    Ag = c("s121", "S431", "S246", "S444"),
    Q1 = c(4L,
           0L, 0L, 2L),
    Q2 = c(3L, 0L, 0L, 2L),
    Q3 = c(0L, 2L, 0L, 2L),
    Q4 = c(0L, 1L, 2L, 2L)
  ),
  class = "data.frame",
  row.names = c(NA,-4L)
)

jay.sf · Answer 2 · 2019-11-25T09:30:07.893

Use apply(), and what you want is the length()s.

dat <- transform(dat, C=apply(dat[-1], 1, function(x) length(x[x != 0])))
dat
#     Ag Q1 Q2 Q3 Q4 C
# 1 s121  4  3  0  0 2
# 2 S431  0  0  2  1 2
# 3 S246  0  0  0  2 1
# 4 S444  2  2  2  2 4

You could also do this with rowSums() when you convert to "logical".

transform(dat, C=rowSums(as.data.frame(lapply(dat[-1], as.logical))))
#     Ag Q1 Q2 Q3 Q4 C
# 1 s121  4  3  0  0 2
# 2 S431  0  0  2  1 2
# 3 S246  0  0  0  2 1
# 4 S444  2  2  2  2 4

Data

dat <- structure(list(Ag = c("s121", "S431", "S246", "S444"), Q1 = c(4L, 
0L, 0L, 2L), Q2 = c(3L, 0L, 0L, 2L), Q3 = c(0L, 2L, 0L, 2L), 
    Q4 = c(0L, 1L, 2L, 2L)), row.names = c(NA, -4L), class = "data.frame")

score 0 · Answer 3 · answered Nov 25 '19 at 09:37

Probably @utubun's approach is the most simplest but here are few more base R options.

df$C <-  apply(df[-1] != 0, 1, sum)
df
#    Ag Q1 Q2 Q3 Q4 C
#1 s121  4  3  0  0 2
#2 S431  0  0  2  1 2
#3 S246  0  0  0  2 1
#4 S444  2  2  2  2 4

Or with Reduce and lapply

df$C <- Reduce(`+`, lapply(df[-1], function(x) x != 0))

data

df <- structure(list(Ag = c("s121", "S431", "S246", "S444"), Q1 = c(4L, 
0L, 0L, 2L), Q2 = c(3L, 0L, 0L, 2L), Q3 = c(0L, 2L, 0L, 2L), 
Q4 = c(0L, 1L, 2L, 2L)), row.names = c(NA, -4L), class = "data.frame")

Assign results of rowSums to a new column in R

3 Answers3

Data