Search code examples
raggregate

group by count when count is zero in r

I use aggregate function to get count by group. The aggregate function only returns count for groups if count > 0. This is what I have

dt <- data.frame(
n  = c(1,2,3,4,5,6),
id = c('A','A','A','B','B','B'),
group = c("x","x","y","x","x","x"))

applying the aggregate function

my.count <- aggregate(n ~ id+group, dt, length)

now see the results

my.count[order(my.count$id),]

I get following

id group   n
1  A     x 2
3  A     y 1
2  B     x 3

I need the following (the last row has zero that i need)

id group   n
1  A     x 2
3  A     y 1
2  B     x 3
4  B     y 0

thanks for you help in in advance

Solution

  • aggregate with drop=FALSE worked for me.

    my.count <- aggregate(n ~ id+group, dt, length, drop=FALSE)
my.count[is.na(my.count)] <- 0
my.count
#   id group n
# 1  A     x 2
# 2  B     x 3
# 3  A     y 1
# 4  B     y 0