n <- 3.2e6 DT.mini <- DT.Spotify.Counts.by.user.product.date[{set.seed(1); sampleInChunks(DT.Spotify.Counts.by.user.product.date, n=n)}] DT.TEST <- DT.mini[, list(MonthsUsed=.N), keyby=list(customerid, product)] Customers.TEST <- DT.TEST[, .N, by=customerid][N>1, customerid] setkeyIfNot(DT.mini, "customerid") # ======================================================= # ## OLD: Old <- quote({ DT.old <- DT.mini[Customers.TEST] cleanSpotify_(DT.old) DT.old <- DT.old[, list( year=year[-1L], month=month[-1L] , ProductFrom = product[- (.N) ] , ProductTo = product[-1L] , ProductDiff=diff(as.integer(product)) ) , keyby=customerid] colsOrdered <- c("customerid", "year", "month", "ProductFrom", "ProductTo", "ProductDiff") setcolorderpt(DT.old, colsOrdered) }) ## NEW: New <- quote( { DT.new <- DT.mini[Customers.TEST , cbind(.SD[-1], ProductFrom=product[-.N]) , .SDcols=c("year", "month", "product") # by-without-by # , keyby=customerid ] ## Fix Names setnames(DT.new, "product", "ProductTo") ## Convert to Factors dict.product <- c(Daypass="D", Open="O", Free="F", Limited="L", DesktopBasic="U", Premium="P") DT.new [, ProductTo := factor(ProductTo, levels=dict.product, labels=names(dict.product))] DT.new [, ProductFrom := factor(ProductFrom, levels=dict.product, labels=names(dict.product))] ## Take Diff DT.new[, ProductDiff := as.numeric(ProductTo) - as.numeric(ProductFrom)] colsOrdered <- c("customerid", "year", "month", "ProductFrom", "ProductTo", "ProductDiff") setcolorderpt(DT.new, colsOrdered) }) a.old <-attributes(DT.old) a.new <-attributes(DT.new) if(all ( mapply(identical, a.old, a.new[names(a.old)]) ) && all ( mapply(identical, a.old[names(a.new)], a.new) ) && all ( eval(Old) == eval(New) ) ) mbench(New, Old, times=5L, check=FALSE) n = 3.2e5 ---------- Measuring 'elapsed' time. Units: milliseconds Number of Repetitions: 5 Expr relative Median Time MAD ----------|-------------|----------------|--------- Old 1 19.26 0.09 New 12 229.23 0.08 n = 6e5 ---------- Measuring 'elapsed' time. Units: milliseconds Number of Repetitions: 5 Expr relative Median Time MAD ----------|-------------|----------------|--------- Old 1 55.76 1.13 New 14 767.45 2.51 n = 3.2e6 --------- Measuring 'elapsed' time. Units: seconds Number of Repetitions: 5 Expr relative Median Time MAD ----------|-------------|----------------|--------- Old 1 1.38 0.04 New 26 35.48 0.02 ## NOTE: Time is in seconds.