Cfrgtkky

Have been looking for a solution for counting unique values of a column in a data data table in a rolling fashion, found rollmean but have not found something like rollunique.

What is a good practice for achieving this using data table?

Thanks

1) An option is to use zoo::rollapply

sample data:

library(data.table)

set.seed(0L)

sz <- 1e5L

winsz <- 5L

DT <- data.table(ID=sample(letters, sz, replace=TRUE))

sample usage using zoo:rollapply*:

DT[, numUniq := zoo::rollapplyr(ID, winsz, uniqueN, fill=NA_integer_)]

2) Another option is to write your own windowing:

DT[, numUniq2 := replace(

    sapply(1:.N, function(n) uniqueN(ID[max(n-winsz+1, 1L):n])),

    .I < winsz,

    NA_integer_)]

3) Another option is to use data.table::shift

DT[, numUniq3 := replace(

    apply(setDT(shift(ID, 0L:(winsz-1L))), 1L, uniqueN),

    1L:.N < winsz,

    NA_integer_)]

output:

        ID numUniq numUniq2 numUniq3

     1:  x      NA       NA       NA

     2:  g      NA       NA       NA

     3:  j      NA       NA       NA

     4:  o      NA       NA       NA

     5:  x       4        4        4

    ---                             

 99996:  k       4        4        4

 99997:  a       4        4        4

 99998:  f       4        4        4

 99999:  z       4        4        4

100000:  c       5        5        5

Benchmarking

timing code:

microbenchmark::microbenchmark(

    zooRoll=DT[, numUniq := zoo::rollapplyr(ID, winsz, uniqueN, fill=NA)],

    sapply=DT[, numUniq2 := replace(

        vapply(1L:.N, function(n) uniqueN(ID[max(n-winsz+1L, 1L):n]), integer(1L)),

        1L:.N < winsz,

        NA_integer_)],

    shift=DT[, numUniq3 := replace(

        apply(setDT(shift(ID, 0L:(winsz-1L))), 1L, uniqueN),

        1L:.N < winsz,

        NA_integer_)],

    times=3L)

timings:

Unit: seconds

    expr      min       lq     mean   median       uq      max neval

 zooRoll 1.723915 1.774423 1.837433 1.824931 1.894191 1.963451     3

  sapply 1.214608 1.224971 1.230763 1.235333 1.238840 1.242348     3

   shift 1.188266 1.234769 1.266852 1.281272 1.306145 1.331018     3

see also:

• Is there a _fast_ way to run a rolling regression inside data.table?




• R data.table sliding window