shift() in data.table v1.9.6 is slow for many groups #1534

Activity

1. arunsrinivasan commented on Mar 3, 2016

   

   arunsrinivasan

   on Mar 3, 2016

   Member

   More actions

   That's not surprising. This'll go away when gforce is optimised for :=. It's on the list for this release, I believe.
2. jangorecki

   added

   performance

   on Mar 3, 2016
3. ben519 commented on Nov 10, 2018

   

   ben519

   on Nov 10, 2018

   More actions

   +1 for this performance enhancement. shift() is the main bottleneck in a lot of my code. Seems that for a fixed number of rows, the time it takes to run shift() is proportional to the number of groups in the data.

   library(data.table)
   
   # Build table to store timings
   timings <- CJ(RowCount = 10^7, Groups = 10^c(0:7))
   timings[, SizePerGroup := RowCount/Groups]
   
   # Loop through each experiment
   for(i in 1:nrow(dt)){
     print(paste0("Iteration: ", i))
     
     # Build dataset
     timings_i <- timings[i]
     dt <- data.table(Grp = rep(seq_len(timings_i$Groups), each = timings_i$SizePerGroup))
     dt[, Value := sample(100, size = .N, replace = T)]
     
     # Measure the time it takes to insert a column indicating the previous value by group
     elapsed <- system.time(dt[, PrevValueByGrp := shift(Value, type = "lag"), by = Grp])["elapsed"]
     timings[i, Elapsed := elapsed]
   }
   
   library(ggplot2)
   ggplot(timings, aes(x = Groups, y = Elapsed))+geom_line()+geom_point()
   

   
4. franknarf1 commented on Nov 10, 2018

   

   franknarf1

   on Nov 10, 2018

   Contributor

   More actions

   @ben519 Fyi, for the special case of when your code looks like that, there's a shortcut:

   library(data.table)
   dt <- data.table(Grp = rep(seq_len(1e6), each=10L))
   dt[, Value := sample(100L, size = .N, replace = TRUE)]
   
   system.time(dt[, PrevValueByGrp := shift(Value, type = "lag"), by = Grp][])
   #    user  system elapsed 
   #   19.50    0.80   20.34
   system.time(dt[, v := shift(Value, type = "lag")][rowid(Grp)==1L, v := NA][])
   #    user  system elapsed 
   #    1.00    0.87    1.25 
   
   dt[, all.equal(v, PrevValueByGrp)]
   # [1] TRUE
   

   👍React with 👍3Reacted by Ben Gorman, Jan Gorecki and Ingo Lange
5. MichaelChirico

   added

   GForceissues relating to optimized grouping calculations (GForce)issues relating to optimized grouping calculations (GForce)

   on Feb 25, 2019
6. mattdowle

   added this to the 1.14.3 milestone on Oct 15, 2021
7. mattdowle

   closed this as completedin #5205on Oct 20, 2021
8. jangorecki

   modified the milestones: 1.14.9, 1.15.0 on Oct 29, 2023