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") 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)
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)
The translations available are listed in the package variable rqdatatable:::data_table_extend_fns
.
str(rqdatatable:::data_table_extend_fns)
Any scripts or data that you put into this service are public.
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.