The hardware and bandwidth for this mirror is donated by METANET, the Webhosting and Full Service-Cloud Provider.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]metanet.ch.

Grouped Sampling

John Mount

2023-08-19

This is an example of the current idiomatic way to sample per-group using rqdatatable or rquery.

The idea is to use a random order and per-group row numbering. This works well in-memory.

library("rqdatatable")

## Loading required package: wrapr

## Loading required package: rquery

n <- 100000
set.seed(325235)
d <- data.frame(
  x = sample(letters, n, replace = TRUE),
  y = sample(letters, n, replace = TRUE),
  z = sample(letters, n, replace = TRUE),
  id = seq_len(n),
  stringsAsFactors = FALSE)

grouping_vars <- qc(x, y, z)

sample_ops <- local_td(d) %.>%
  extend_nse(., one := 1) %.>%
  extend_nse(., ord := runif(sum(one))) %.>%
  pick_top_k(., 
             k = 5,
             partitionby = grouping_vars,
             orderby = "ord")

samp <- ex_data_table(sample_ops)
head(samp)

##   x y z    id one        ord row_number
## 1 a a a 99342   1 0.29592013          1
## 2 a a a 41989   1 0.45891485          2
## 3 a a a 12254   1 0.99895914          3
## 4 a a b 30135   1 0.09357431          1
## 5 a a b 20897   1 0.22383060          2
## 6 a a b 59731   1 0.54500068          3

And the database version is very similar (on databases with window functions).

The main issue is landing the random order without having to translate the R runif(sum(one)) code into database operations.

library("rquery")

db <- DBI::dbConnect(RPostgreSQL::PostgreSQL(),
                     host = 'localhost',
                     port = 5432,
                     user = 'johnmount',
                     password = '')

rq_copy_to(db, "d", d,
           overwrite = TRUE,
           temporary = TRUE)

sample_ops <- local_td(d) %.>%
  extend_nse(., ord := random()) %.>%
  pick_top_k(., 
             k = 5,
             partitionby = grouping_vars,
             orderby = "ord")

samp <- execute(db, sample_ops, allow_executor = FALSE)

DBI::dbDisconnect(db)

The main issue is the different notation used in each pipeline to land the random column.

We can unify this by supplying translations from some common database notations (such as no-argument random()) to the data.table implementation.

sample_ops <- local_td(d) %.>%
  extend_nse(., ord := random()) %.>%
  pick_top_k(., 
             k = 5,
             partitionby = grouping_vars,
             orderby = "ord")

samp <- ex_data_table(sample_ops)
head(samp)

##   x y z    id        ord row_number
## 1 a a a 12254 0.47605028          1
## 2 a a a 41989 0.61569890          2
## 3 a a a 99342 0.87579154          3
## 4 a a b 25056 0.02373051          1
## 5 a a b 59731 0.03065273          2
## 6 a a b 30135 0.25620100          3

The translations available are listed in the package variable rqdatatable:::data_table_extend_fns.

str(rqdatatable:::data_table_extend_fns)

## List of 6
##  $ ngroup    :List of 2
##   ..$ data.table_version: chr ".GRP"
##   ..$ need_one_col      : logi TRUE
##  $ rank      :List of 2
##   ..$ data.table_version: chr "cumsum(rqdatatable_temp_one_col)"
##   ..$ need_one_col      : logi TRUE
##  $ row_number:List of 2
##   ..$ data.table_version: chr "cumsum(rqdatatable_temp_one_col)"
##   ..$ need_one_col      : logi TRUE
##  $ n         :List of 2
##   ..$ data.table_version: chr "sum(rqdatatable_temp_one_col)"
##   ..$ need_one_col      : logi TRUE
##  $ random    :List of 2
##   ..$ data.table_version: chr "runif(.N)"
##   ..$ need_one_col      : logi FALSE
##  $ rand      :List of 2
##   ..$ data.table_version: chr "runif(.N)"
##   ..$ need_one_col      : logi FALSE

These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.