Recreate dplyr summarise in data.table

Question

Just out of curiosity, is there a way of recreating the summary output using data.table instead of dplyr?

dt1 <- data.table(
  uid=c("A00111", "A00112","A00113","A00211","A00212","A00213","A00214","A00311","A00312"),
  area=c("A001", "A001","A001","A002","A002","A002","A002","A003","A003"),
  price=c(325147,NA,596020,257409,241206,248371,261076,595218,596678),
  type=c("Type1","Type2","Type3","Type2","Type3","Type2","Type2","Type2","Type3"))

summary <- dt1 %>% group_by(area) %>% summarise(
    Total_Number = length(uid),
    Total_Number_Check = unique(length(uid)),
    Number_of_Type_1 = length(uid[type=="Type1"]),
    Mean_Price_Type_1 = mean(price[type=="Type1"],na.rm = TRUE),
    Number_of_Type_2 = length(uid[type=="Type2"]),
    Mean_Price_Type_2 = mean(price[type=="Type2"],na.rm = TRUE),
    Number_of_Type_3 = length(uid[type=="Type3"]),
    Mean_Price_Type_3 = mean(price[type=="Type3"],na.rm = TRUE))

Yes, simply `dt1[, .(...), by = area]` (replace the `...` with all the stuff from `summarise`) — David Arenburg, Jul 09 '19 at 08:28
A lot of simplification can be done in the `...` part. For instance `length(uid)` can be replaced by `.N`, `length(uid[type=="Type1"])` with `sum(type=="Type1")`, etc. — s_baldur, Jul 09 '19 at 08:38

Wimpel · Accepted Answer · 2019-07-09T09:11:11.020

Here is a go with data.table

The comment by @DavidArenburg above is the default way to summarise with data.table.

However, I did not create the summarise in 1 go, since you might have more than 3 type-variables. If so, it would not be workable to summarise (by hand) >10 types. It would become a long (boring) code.

So I first summarised by area (DT1), and then summarised again by area AND by type. Then casted the result of the second summary to wide format (DT2), and left-joined DT2 to DT1.

So the code below will work for any number of areas, and any number of types.

library( data.table )
#summarise by area
DT1 <- dt1[ , .( Total_Number = .N, 
                 Total_Number_Check = uniqueN( uid ) ), 
            by = .(area)]
#summarise by area AND type and cast to wide format
DT2 <- dcast( dt1[ , .( Number_of = .N, 
                        Mean_Price = mean( price, na.rm = TRUE ) ), 
                   by = .(area, type)], 
              area ~ type, 
              value.var = c("Number_of", "Mean_Price") )
#join
DT1[DT2, on = .(area)]

#    area Total_Number Total_Number_Check Number_of_Type1 Number_of_Type2 Number_of_Type3 Mean_Price_Type1
# 1: A001            3                  3               1               1               1            325147
# 2: A002            4                  4              NA               3               1                NA
# 3: A003            2                  2              NA               1               1                NA
# Mean_Price_Type2 Mean_Price_Type3
# 1:                NA            596020
# 2:          255618.7            241206
# 3:          595218.0            596678

Recreate dplyr summarise in data.table

1 Answers1