New Introduction to rquery

Introduction

rquery is a data wrangling system designed to express complex data manipulation as a series of simple data transforms. This is in the spirit of R’s base::transform(), or dplyr’s dplyr::mutate() and uses a pipe in the style popularized in R with magrittr. The operators themselves follow the selections in Codd’s relational algebra, with the addition of the traditional SQL “window functions.” More on the background and context of rquery can be found here.

The R/rquery version of this introduction is here, and the Python/data_algebra version of this introduction is here.

In transform formulations data manipulation is written as transformations that produce new data.frames, instead of as alterations of a primary data structure (as is the case with data.table). Transform system can use more space and time than in-place methods. However, in our opinion, transform systems have a number of pedagogical advantages.

In rquery’s case the primary set of data operators is as follows:

• drop_columns
• select_columns
• rename_columns
• select_rows
• order_rows
• extend
• project
• natural_join
• convert_records (supplied by the cdata package).

These operations break into a small number of themes:

• Simple column operations (selecting and re-naming columns).
• Simple row operations (selecting and re-ordering rows).
• Creating new columns or replacing columns with new calculated values.
• Aggregating or summarizing data.
• Combining results between two data.frames.
• General conversion of record layouts (supplied by the cdata package).

The point is: Codd worked out that a great number of data transformations can be decomposed into a small number of the above steps. rquery supplies a high performance implementation of these methods that scales from in-memory scale up through big data scale (to just about anything that supplies a sufficiently powerful SQL interface, such as PostgreSQL, Apache Spark, or Google BigQuery).

We will work through simple examples/demonstrations of the rquery data manipulation operators.

rquery operators

d <- data.frame(
  x = c(1, 1, 2),
  y = c(5, 4, 3),
  z = c(6, 7, 8)
)

knitr::kable(d)

library(rquery)

drop_columns(d, c('y', 'z'))
##    x
## 1: 1
## 2: 1
## 3: 2

d %.>%
  drop_columns(., c('y', 'z')) %.>%
  knitr::kable(.)

d %.>%
  select_columns(., c('x', 'y')) %.>%
  knitr::kable(.)

d %.>%
  select_rows(., x == 1) %.>%
  knitr::kable(.)

d %.>%
  order_rows(., 
             c('x', 'y'),
             reverse = 'x') %.>%
  knitr::kable(.)

d %.>%
  extend(., zzz := y / x) %.>%
  knitr::kable(.)

shift <- data.table::shift

d %.>%
  extend(.,
         max_y := max(y),
         shift_z := shift(z),
         row_number := row_number(),
         cumsum_z := cumsum(z),
         partitionby = 'x',
         orderby = c('y', 'z')) %.>%
  knitr::kable(.)

d %.>%
  project(.,
         max_y := max(y),
         count := n(),
         groupby = 'x') %.>%
  knitr::kable(.)

d %.>%
  project(.,
         max_y := max(y),
         count := n()) %.>%
  knitr::kable(.)

d_left <- data.frame(
  k = c('a', 'a', 'b'),
  x = c(1, NA, 3),
  y = c(1, NA, NA),
  stringsAsFactors = FALSE
)

knitr::kable(d_left)

d_right <- data.frame(
  k = c('a', 'b', 'q'),
  y = c(10, 20, 30),
  stringsAsFactors = FALSE
)

knitr::kable(d_right)

natural_join(d_left, d_right,
             by = 'k',
             jointype = 'LEFT') %.>%
  knitr::kable(.)

Composing operations

We could, of course, perform complicated data manipulation by sequencing rquery operations. For example to select one row with minimal y per-x group we could work in steps as follows.

. <- d
. <- extend(.,
            row_number := row_number(),
            partitionby = 'x',
            orderby = c('y', 'z'))
. <- select_rows(.,
                 row_number == 1)
. <- drop_columns(.,
                  "row_number")
knitr::kable(.)

The above discipline has the advantage that it is easy to debug, as we can run line by line and inspect intermediate values. We can even use the Bizarro pipe to make this look like a pipeline of operations.

d ->.;
  extend(.,
         row_number := row_number(),
         partitionby = 'x',
         orderby = c('y', 'z')) ->.;
  select_rows(.,
              row_number == 1)  ->.;
  drop_columns(.,
               "row_number")    ->.;
  knitr::kable(.)

Or we can use the wrapr pipe on the data, which we call “immediate mode” (for more on modes please see here).

d %.>%
  extend(.,
         row_number := row_number(),
         partitionby = 'x',
         orderby = c('y', 'z')) %.>%
  select_rows(.,
              row_number == 1)  %.>%
  drop_columns(.,
               "row_number")    %.>%
  knitr::kable(.)

rquery operators can also act on rquery pipelines instead of acting on data. We can write our operations as follows:

ops <- local_td(d) %.>%
  extend(.,
         row_number := row_number(),
         partitionby = 'x',
         orderby = c('y', 'z')) %.>%
  select_rows(.,
              row_number == 1)  %.>%
  drop_columns(.,
               "row_number")

cat(format(ops))
## mk_td("d", c(
##   "x",
##   "y",
##   "z")) %.>%
##  extend(.,
##   row_number := row_number(),
##   partitionby = c('x'),
##   orderby = c('y', 'z'),
##   reverse = c()) %.>%
##  select_rows(.,
##    row_number == 1) %.>%
##  drop_columns(.,
##    c('row_number'))

And we can re-use this pipeline, both on local data and to generate SQL to be run in remote databases. Applying this operator pipeline to our data.frame d is performed as follows.

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

What we are trying to illustrate above: there is a continuum of notations possible between:

• Working over values with explicit intermediate variables.
• Working over values with a pipeline.
• Working over operators with a pipeline.

Being able to see these as all related gives some flexibility in decomposing problems into solutions. We have some more advanced notes on the differences in working modalities here and here.

Conclusion

rquery supplies a very teachable grammar of data manipulation based on Codd’s relational algebra and experience with pipelined data transforms (such as base::transform(), dplyr, and data.table).

For in-memory situations rquery uses data.table as the implementation provider (through the small adapter package rqdatatable) and is routinely faster than any other R data manipulation system except data.table itself.

For bigger than memory situations rquery can translate to any sufficiently powerful SQL dialect, allowing rquery pipelines to be executed on PostgreSQL, Apache Spark, or Google BigQuery.

In addition the data_algebra Python package supplies a nearly identical system for working with data in Python. The two systems can even share data manipulation code between each other (allowing very powerful R/Python inter-operation or helping port projects from one to the other).