Examples/Modes/Modes.md

In WinVector/rquery: Relational Query Generator for Data Manipulation at Scale

rquery Modes

rqdatatable/rquery is designed to have a number of different modes of use. The primary intended one the considered mode of building up a pipelines from a description of the tables to be acted on.

(Note the R/rquery version of this example can be found here, and the Python/data_algebra version can be found here.)

For our example, lets start with the following example data.

d <- data.frame(
  x = c(1, 2, 3, 4, 5, 6),
  y = c(2, 2, 2, 3, 7, 10),
  g = c('a', 'a', 'a', 'b', 'b' ,'b'),
  stringsAsFactors = FALSE
)

knitr::kable(d)

| x | y | g | |----:|----:|:----| | 1 | 2 | a | | 2 | 2 | a | | 3 | 2 | a | | 4 | 3 | b | | 5 | 7 | b | | 6 | 10 | b |

For our task: let’s find a row with the largest ratio of ‘y’ to ‘x’, per group ‘g’.

The rquery concept is to break this into small sub-goals and steps:

• Find the ratio of ‘y’ to ‘x’.
• Rank the rows by this ratio.
• Mark our chosen rows.

In the standard rquery practice we build up our processing pipeline to follow our above plan. The translation involves some familiarity with the rquery steps, including the row-numbering command row_number().

library(rqdatatable)

## Loading required package: wrapr

## Loading required package: rquery

library(rquery)

ops <- local_td(d) %.>%  # Describe table for later operations
  extend(.,       # add a new column
         ratio := y / x) %.>%
  extend(.,       # rank the rows by group and order
         simple_rank := row_number(),
         partitionby = 'g',
         orderby = 'ratio',
         reverse = 'ratio') %.>%
  extend(.,       # mark the rows we want
         choice := simple_rank == 1)

The ops operator pipeline can than be used to process data.

d %.>%
  ops %.>%
  knitr::kable(.)

| x | y | g | ratio | simple_rank | choice | |----:|----:|:----|----------:|-------------:|:-------| | 1 | 2 | a | 2.0000000 | 1 | TRUE | | 2 | 2 | a | 1.0000000 | 2 | FALSE | | 3 | 2 | a | 0.6666667 | 3 | FALSE | | 6 | 10 | b | 1.6666667 | 1 | TRUE | | 5 | 7 | b | 1.4000000 | 2 | FALSE | | 4 | 3 | b | 0.7500000 | 3 | FALSE |

Another point is: this form documents check-able (and enforceable) pre and post conditions on the calculation. For example such a calculation documents what columns are required by the calculation, and which ones are produced.

# columns produced
column_names(ops)

## [1] "x"           "y"           "g"           "ratio"       "simple_rank"
## [6] "choice"

# columns used
columns_used(ops)

## $d
## [1] "x" "y" "g"

We can in fact make these conditions the explicit basis of an interpretation of these data transforms as category theory arrows.

arrow(ops)

## [
##  'd':
##  c('x', 'y', 'g')
##    ->
##  c('x', 'y', 'g', 'ratio', 'simple_rank', 'choice')
## ]

Another way to use rquery/rqdatatable is in “immediate mode”, where we send the data from pipeline stage to pipeline state.

d %.>%
  extend(.,       # add a new column
         ratio := y / x) %.>%
  extend(.,       # rank the rows by group and order
         simple_rank := row_number(),
         partitionby = 'g',
         orderby = 'ratio',
         reverse = 'ratio') %.>%
  extend(.,       # mark the rows we want
         choice := simple_rank == 1) %.>%
  knitr::kable(.)

| x | y | g | ratio | simple_rank | choice | |----:|----:|:----|----------:|-------------:|:-------| | 1 | 2 | a | 2.0000000 | 1 | TRUE | | 2 | 2 | a | 1.0000000 | 2 | FALSE | | 3 | 2 | a | 0.6666667 | 3 | FALSE | | 6 | 10 | b | 1.6666667 | 1 | TRUE | | 5 | 7 | b | 1.4000000 | 2 | FALSE | | 4 | 3 | b | 0.7500000 | 3 | FALSE |

Immediate mode skips the local_td() step of building a specification for the data to later come, and runs directly on the data. The advantage of this mode is: it is quick for the user. A disadvantage of this mode is: the pipeline is not left for re-use, and there are possibly expensive data conversions (from data.frame to data.table) at each operator stage.

d %.>%
  wrap %.>%       # wrap data in a description
  extend(.,       # add a new column
         ratio := y / x) %.>%
  extend(.,       # rank the rows by group and order
         simple_rank := row_number(),
         partitionby = 'g',
         orderby = 'ratio',
         reverse = 'ratio') %.>%
  extend(.,       # mark the rows we want
         choice := simple_rank == 1) %.>%
  ex %.>%         # signal construction done, and execute
  knitr::kable(.)

| x | y | g | ratio | simple_rank | choice | |----:|----:|:----|----------:|-------------:|:-------| | 1 | 2 | a | 2.0000000 | 1 | TRUE | | 2 | 2 | a | 1.0000000 | 2 | FALSE | | 3 | 2 | a | 0.6666667 | 3 | FALSE | | 6 | 10 | b | 1.6666667 | 1 | TRUE | | 5 | 7 | b | 1.4000000 | 2 | FALSE | | 4 | 3 | b | 0.7500000 | 3 | FALSE |

The difference is: we use the wrap to build a special operator collecting (and checking) pipeline, and then later ex to say we are done specifying steps and to apply the operations to the data. Prior to the ex step the operator pipeline is available as a field called underlying and the set of wrapped data.frames is available as a field called data_map.

Additional execution options are as follows.

We can use local_td to convert tables to table descriptions and delay execution.

ops <- d %.>%
  local_td %.>%   # capture table description
  extend(.,       # add a new column
         ratio := y / x) %.>%
  extend(.,       # rank the rows by group and order
         simple_rank := row_number(),
         partitionby = 'g',
         orderby = 'ratio',
         reverse = 'ratio') %.>%
  extend(.,       # mark the rows we want
         choice := simple_rank == 1)

We can then execute using the pipe.

# execute
d %.>%
  ops %.>%
  knitr::kable(.)

| x | y | g | ratio | simple_rank | choice | |----:|----:|:----|----------:|-------------:|:-------| | 1 | 2 | a | 2.0000000 | 1 | TRUE | | 2 | 2 | a | 1.0000000 | 2 | FALSE | | 3 | 2 | a | 0.6666667 | 3 | FALSE | | 6 | 10 | b | 1.6666667 | 1 | TRUE | | 5 | 7 | b | 1.4000000 | 2 | FALSE | | 4 | 3 | b | 0.7500000 | 3 | FALSE |

Or by the ex_data_table() method, which is useful for pipelines that contain many tables.

ex_data_table(  # useful for multi table pipelines
  ops,
  tables = list('d' = d)) %.>%
  knitr::kable(.)

| x | y | g | ratio | simple_rank | choice | |----:|----:|:----|----------:|-------------:|:-------| | 1 | 2 | a | 2.0000000 | 1 | TRUE | | 2 | 2 | a | 1.0000000 | 2 | FALSE | | 3 | 2 | a | 0.6666667 | 3 | FALSE | | 6 | 10 | b | 1.6666667 | 1 | TRUE | | 5 | 7 | b | 1.4000000 | 2 | FALSE | | 4 | 3 | b | 0.7500000 | 3 | FALSE |

The wrapping of data as a different kind of rquery pipeline is an example of using the “decorator pattern” (which can be considered as an object oriented variation of the functional monad pattern, ref). However, these are technical considerations that are the package developer’s problem- not problems for the package users. Think of these terms as examples of things developers worry about so users don’t have to worry about them.

set.seed(2019)
n_rows <- 1000000
d_large <- data.frame(
  x = rnorm(n = n_rows),
  y = rnorm(n = n_rows),
  g = sample(paste0('v_', seq_len(n_rows/10)), 
             size = n_rows, 
             replace = TRUE),
  stringsAsFactors = FALSE
)

f_compiled <- function(dat) {
  dat %.>% ops  # use pre-compiled pipeline
}

f_immediate <- function(dat) {
  dat %.>%
    extend(.,       # add a new column
           ratio := y / x) %.>%
    extend(.,       # rank the rows by group and order
           simple_rank := row_number(),
           partitionby = 'g',
           orderby = 'ratio',
           reverse = 'ratio') %.>%
    extend(.,       # mark the rows we want
           choice := simple_rank == 1)
}

f_wrapped <- function(dat) {
  dat %.>%
    wrap %.>%       # wrap data in a description
    extend(.,       # add a new column
           ratio := y / x) %.>%
    extend(.,       # rank the rows by group and order
           simple_rank := row_number(),
           partitionby = 'g',
           orderby = 'ratio',
           reverse = 'ratio') %.>%
    extend(.,       # mark the rows we want
           choice := simple_rank == 1) %.>%
    ex              # signal construction done, and execute
}

And we can also time data.table itself (without the translation overhead, though we are adding in the time to convert the data.frame).

library(data.table)

## 
## Attaching package: 'data.table'

## The following object is masked from 'package:wrapr':
## 
##     :=

f_data_table = function(dat) {
  dat <- data.table(dat)
  dat[ , ratio := y / x
       ][order(-ratio) , simple_rank := 1:.N, by = list(g)
          ][ , choice := simple_rank == 1]
}

f_data_table(d) %.>%
  knitr::kable(.)

| x | y | g | ratio | simple_rank | choice | |----:|----:|:----|----------:|-------------:|:-------| | 1 | 2 | a | 2.0000000 | 1 | TRUE | | 2 | 2 | a | 1.0000000 | 2 | FALSE | | 3 | 2 | a | 0.6666667 | 3 | FALSE | | 4 | 3 | b | 0.7500000 | 3 | FALSE | | 5 | 7 | b | 1.4000000 | 2 | FALSE | | 6 | 10 | b | 1.6666667 | 1 | TRUE |

library(microbenchmark)

timings <- microbenchmark(
  rquery_compiled = f_compiled(d_large),
  rquery_immediate = f_immediate(d_large),
  rquery_wrapped = f_wrapped(d_large),
  data.table = f_data_table(d_large),
  times = 10L
)

print(timings)

## Unit: milliseconds
##              expr      min       lq     mean   median       uq      max neval
##   rquery_compiled 355.6070 362.0623 410.5594 392.4430 455.6678 505.2771    10
##  rquery_immediate 505.3830 626.1687 633.3046 631.6362 663.1505 714.9453    10
##    rquery_wrapped 354.6824 375.7951 418.0807 425.7250 449.1765 508.4214    10
##        data.table 336.3786 361.1043 394.1618 389.8038 433.0612 456.3295    10

Notice, the speed differences are usually not that large for short pipelines. Then intent is: pipeline construction and data conversion steps should be cheap compared to the actual processing steps.

For those interested: we add dplyr and dtplyr to the timing comparisons here.

WinVector/rquery documentation built on Aug. 24, 2023, 11:12 a.m.