Count rows in data table with certain values by group

Question

I have a a data table that somewhat looks like this:

Property    Type
1           apartment
1           office
2           office
2           office
3           apartment
3           apartment
3           office

I now want to count offices and apartments by property:

Property    Type       number_of_offices    number_of_apartments
       1    apartment                  1                       1
       1    office                     1                       1
       2    office                     2                       0
       2    office                     2                       0
       3    apartment                  1                       2
       3    apartment                  1                       2
       3    office                     1                       2

I tried

my.DT <- myDT[,.(Type=Type, number_of_offices=nrow(my.DT[my.DT$Type=="office",]), number_of_apartments=nrow(my.DT$Type=="apparment",], by="Property")

However, this only gives me the total counts for the whole data table. Does anyone have a solution?

Answer 1

You can solve it as follows:

cols <- c("number_of_offices", "number_of_apartments")
df[, (cols) := .(sum(Type == "office"), sum(Type == "apartment")), Property]

# Property      Type number_of_offices number_of_apartments
# 1:        1 apartment                 1                    1
# 2:        1    office                 1                    1
# 3:        2    office                 2                    0
# 4:        2    office                 2                    0
# 5:        3 apartment                 1                    2
# 6:        3 apartment                 1                    2
# 7:        3    office                 1                    2

Answer 2

Is there a particular reason why you want to merge the grouped counts with my.df?

You can try this, which will give you the counts grouped by Property and Type. Then merge with the original myDT:

grouped = myDT[, .N, by=c('Property','Type')]
myDT = merge(myDT, grouped[Type == 'apartment', list(Property,N)], by='Property', all.x=TRUE)
myDT = merge(myDT, grouped[Type == 'office', list(Property,N)], by='Property', all.x=TRUE)
setnames(myDT, c('N.x','N.y'), c('Number of appartments','Number of offices'))
myDT[is.na(myDT)] <- 0

> myDT
   Property      Type Number of appartments Number of offices
1:        1 apartment                     1                 1
2:        1    office                     1                 1
3:        2    office                     0                 2
4:        2    office                     0                 2
5:        3 apartment                     2                 1
6:        3 apartment                     2                 1
7:        3    office                     2                 1