CODEWITHSUNDEEP
rdplyrdata.table
HerClau
HerClau

Reputation: 171

Group efficiently with data.table in R

It would be possible to abbreviate the following script:

  1. not to use so many chained operations.
  2. Avoid using .SD and by as much as possible
library(data.table)
DT<-structure(list(title = c("a", "a", "a", "a", "b", "b", "b", "b", "c", "c", "c", "c", "d", "d", "d", "d"), date = c("12-07-2020", "13-07-2020", "14-07-2020", "15-07-2020", "12-07-2020", "13-07-2020", 
         "14-07-2020", "15-07-2020", "12-07-2020", "13-07-2020", "14-07-2020", "15-07-2020", 
         "12-07-2020", "13-07-2020", "14-07-2020", "15-07-2020"), 
bucket = c(1, 1, 1, 4, 9, 7, 10, 10, 8, 5, 5, 5, 8, 10, 9, 10), 
score = c(86, 22, 24, 54, 66, 76, 43, 97, 9, 53, 45, 40, 21, 99, 91, 90)),
 row.names = c(NA, -16L), class = c("data.table","data.frame"))
 
DT[DT[, .I[bucket == min(bucket)], by = title]$V1]
DT[, .SD[which(bucket == min(bucket))], by =title][,
  `:=`(avg_score = mean(score)), by = .(title)][,
    .SD[.N,c(1,2,4)], by = .(title)]

the original code is a scrip using dplyr .:RStudio Community

tt <- data %>% 
group_by(title) %>% 
filter(bucket == min(bucket)) %>% 
mutate(avg_score = mean(score)) %>% 
slice_max(date) %>% 
select(-score)
>
title date       bucket avg_score
  <chr> <chr>       <dbl>     <dbl>
1 a     14-07-2020      1        44
2 b     13-07-2020      7        76
3 c     15-07-2020      5        46
4 d     12-07-2020      8        21
>

Upvotes: 2

Views: 100

Answers (1)

s_baldur
s_baldur

Reputation: 33488

Here is a solution with no chaining nor .SD:

# Convert from character to Date to be able to select the max
DT[, date := as.Date(date, "%d-%m-%Y")]

DT[,
   {
     mb <- which(bucket == min(bucket))
     .(
       date = max(date[mb]), bucket = bucket[mb][1L], avg_score = mean(score[mb])
     )
   }, 
   by = title]

#    title       date bucket avg_score
# 1:     a 2020-07-14      1        44
# 2:     b 2020-07-13      7        76
# 3:     c 2020-07-15      5        46
# 4:     d 2020-07-12      8        21

Upvotes: 2

Related Questions