Count non-zero values of column in R

Question

Suppose i have data frame like this one

DF

Id X Y Z 
1  1 5 0
1  2 0 0
1  3 0 5
1  4 9 0
1  5 2 3
1  6 5 0 
2  1 5 0
2  2 4 0
2  3 0 6
2  4 9 6
2  5 2 0
2  6 5 2 
3  1 5 6
3  2 4 0
3  3 6 5
3  4 9 0
3  5 2 0
3  6 5 0

I want to count the number of non zero entries for variable Z in a particular Id and record that value in a new column Count, so the new data frame will look like

DF1

Id X Y Z Count 
1  1 5 0  2
1  2 4 0  2
1  3 6 5  2
1  4 9 0  2
1  5 2 3  2
1  6 5 0  2 
2  1 5 0  3
2  2 4 0  3
2  3 6 6  3
2  4 9 6  3
2  5 2 0  3
2  6 5 2  3
3  1 5 6  2
3  2 4 0  2
3  3 6 5  2
3  4 9 0  2
3  5 2 0  2
3  6 5 0  2

Answer 1

We can use base R ave

Counting the number of non-zero values for column Z grouped by Id

df$Count <- ave(df$Z, df$Id, FUN = function(x) sum(x!=0))
df$Count

#[1] 2 2 2 2 2 2 3 3 3 3 3 3 2 2 2 2 2 2

Answer 2

You can try this, it gives you exactly what you want:

library(data.table)
dt <- data.table(df)

dt[, Count := sum(Z != 0), by = Id]

dt
#     Id X Y Z Count
#  1:  1 1 5 0     2
#  2:  1 2 0 0     2
#  3:  1 3 0 5     2
#  4:  1 4 9 0     2
#  5:  1 5 2 3     2
#  6:  1 6 5 0     2
#  7:  2 1 5 0     3
#  8:  2 2 4 0     3
#  9:  2 3 0 6     3
# 10:  2 4 9 6     3
# 11:  2 5 2 0     3
# 12:  2 6 5 2     3
# 13:  3 1 5 6     2
# 14:  3 2 4 0     2
# 15:  3 3 6 5     2
# 16:  3 4 9 0     2
# 17:  3 5 2 0     2
# 18:  3 6 5 0     2

Answer 3

This will also work:

df$Count <- rep(aggregate(Z~Id, df[df$Z != 0,], length)$Z, table(df$Id))

   Id X Y Z Count
1   1 1 5 0     2
2   1 2 0 0     2
3   1 3 0 5     2
4   1 4 9 0     2
5   1 5 2 3     2
6   1 6 5 0     2
7   2 1 5 0     3
8   2 2 4 0     3
9   2 3 0 6     3
10  2 4 9 6     3
11  2 5 2 0     3
12  2 6 5 2     3
13  3 1 5 6     2
14  3 2 4 0     2
15  3 3 6 5     2
16  3 4 9 0     2
17  3 5 2 0     2
18  3 6 5 0     2