• Stars
    star
    109
  • Rank 319,077 (Top 7 %)
  • Language
    HTML
  • License
    Other
  • Created almost 7 years ago
  • Updated about 1 year ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

Data Wrangling and Query Generating Operators for R. Distributed under choice of GPL-2 or GPL-3 license.

CRAN_Status_Badge status

rquery

rquery is a piped query generator based on Coddโ€™s relational algebra (updated to reflect lessons learned from working with R, SQL, and dplyr at big data scale in production).

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

Simple column operations (selecting and re-naming columns)

The simple column operations are as follows.

  • drop_columns
  • select_columns
  • rename_columns

These operations are easy to demonstrate.

We set up some simple data.

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

knitr::kable(d)
x y z
1 5 6
1 4 7
2 3 8

For example: drop_columns works as follows. drop_columns creates a new data.frame without certain columns.

library(rquery)
## Loading required package: wrapr
library(rqdatatable)

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

In all cases the first argument of a rquery operator is either the data to be processed, or an earlier rquery pipeline to be extended. We will take about composing rquery operations after we work through examples of all of the basic operations.

We can write the above in piped notation (using the wrapr pipe in this case):

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

Notice the first argument is an explicit โ€œdotโ€ in wrapr pipe notation.

select_columnsโ€™s action is also obvious from example.

d %.>%
  select_columns(., c('x', 'y')) %.>%
  knitr::kable(.)
x y
1 5
1 4
2 3

rename_columns is given as name-assignments of the form 'new_name' = 'old_name':

d %.>%
  rename_columns(., 
                 c('x_new_name' = 'x', 
                   'y_new_name' = 'y')
                 ) %.>%
  knitr::kable(.)
x_new_name y_new_name z
1 5 6
1 4 7
2 3 8

Simple row operations (selecting and re-ordering rows)

The simple row operations are:

  • select_rows
  • order_rows

select_rows keeps the set of rows that meet a given predicate expression.

d %.>%
  select_rows(., x == 1) %.>%
  knitr::kable(.)
x y z
1 5 6
1 4 7

Notes on how to use a variable to specify column names in select_rows can be found here.

order_rows re-orders rows by a selection of column names (and allows reverse ordering by naming which columns to reverse in the optional reverse argument). Multiple columns can be selected in the order, each column breaking ties in the earlier comparisons.

d %.>%
  order_rows(., 
             c('x', 'y'),
             reverse = 'x') %.>%
  knitr::kable(.)
x y z
2 3 8
1 4 7
1 5 6

General rquery operations do not depend on row-order and are not guaranteed to preserve row-order, so if you do want to order rows you should make it the last step of your pipeline.

Creating new columns or replacing columns with new calculated values

The important create or replace column operation is:

  • extend

extend accepts arbitrary expressions to create new columns (or replace existing ones). For example:

d %.>%
  extend(., zzz := y / x) %.>%
  knitr::kable(.)
x y z zzz
1 5 6 5.0
1 4 7 4.0
2 3 8 1.5

We can use = or := for column assignment. In these examples we will use := to keep column assignment clearly distinguishable from argument binding.

extend allows for very powerful per-group operations akin to what SQL calls โ€œwindow functionsโ€. When the optional partitionby argument is set to a vector of column names then aggregate calculations can be performed per-group. For example.

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(.)
x y z max_y shift_z row_number cumsum_z
1 4 7 5 NA 1 7
1 5 6 5 7 2 13
2 3 8 3 NA 1 8

Notice the aggregates were performed per-partition (a set of rows with matching partition key values, specified by partitionby) and in the order determined by the orderby argument (without the orderby argument order is not guaranteed, so always set orderby for windowed operations that depend on row order!).

More on the window functions can be found here. Notes on how to use a variable to specify column names in extend can be found here.

Aggregating or summarizing data

The main aggregation method for rquery is:

  • project

project performs per-group calculations, and returns only the grouping columns (specified by groupby) and derived aggregates. For example:

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

Notice we only get one row for each unique combination of the grouping variables. We can also aggregate into a single row by not specifying any groupby columns.

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

Notes on how to use a variable to specify column names in project can be found here.

Combining results between two data.frames

To combine multiple tables in rquery one uses what we call the natural_join operator. In the rquery natural_join, rows are matched by column keys and any two columns with the same name are coalesced (meaning the first table with a non-missing values supplies the answer). This is easiest to demonstrate with an example.

Letโ€™s set up new example tables.

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)
k x y
a 1 1
a NA NA
b 3 NA
d_right <- data.frame(
  k = c('a', 'b', 'q'),
  y = c(10, 20, 30),
  stringsAsFactors = FALSE
)

knitr::kable(d_right)
k y
a 10
b 20
q 30

To perform a join we specify which set of columns our our row-matching conditions (using the by argument) and what type of join we want (using the jointype argument). For example we can use jointype = 'LEFT' to augment our d_left table with additional values from d_right.

natural_join(d_left, d_right,
             by = 'k',
             jointype = 'LEFT') %.>%
  knitr::kable(.)
k x y
a 1 1
a NA 10
b 3 20

In a left-join (as above) if the right-table has unique keys then we get a table with the same structure as the left-table- but with more information per row. This is a very useful type of join in data science projects. Notice columns with matching names are coalesced into each other, which we interpret as โ€œtake the value from the left table, unless it is missing.โ€

General conversion of record layouts

Record transformation is โ€œsimple once you get itโ€. However, we suggest reading up on that as a separate topic here.

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(.)
x y z
1 4 7
2 3 8

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(.)
x y z
1 4 7
2 3 8

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(.)
x y z
1 4 7
2 3 8

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(.)
x y z
1 4 7
2 3 8

And for SQL we have the following.

raw_connection <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
RSQLite::initExtension(raw_connection)
db <- rquery_db_info(
  connection = raw_connection,
  is_dbi = TRUE,
  connection_options = rq_connection_tests(raw_connection))

cat(to_sql(ops, db))
## SELECT
##  `x`,
##  `y`,
##  `z`
## FROM (
##  SELECT * FROM (
##   SELECT
##    `x`,
##    `y`,
##    `z`,
##    row_number ( ) OVER (  PARTITION BY `x` ORDER BY `y`, `z` ) AS `row_number`
##   FROM (
##    SELECT
##     `x`,
##     `y`,
##     `z`
##    FROM
##     `d`
##    ) tsql_85643797925389488634_0000000000
##  ) tsql_85643797925389488634_0000000001
##  WHERE `row_number` = 1
## ) tsql_85643797925389488634_0000000002
# clean up
DBI::dbDisconnect(raw_connection)

For more SQL examples, please see here.

Pipeline principles

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. # Background

There are many prior relational algebra inspired specialized query languages. Just a few include:

rquery is realized as a thin translation to an underlying SQL provider. We are trying to put the Codd relational operators front and center (using the original naming, and back-porting SQL progress such as window functions to the appropriate relational operator).

Some related work includes:

Installing

To install rquery please try install.packages("rquery").

Note

rquery is intended to work with โ€œtame column namesโ€, that is column names that are legitimate symbols in R and SQL.

The previous rquery introduction is available here.

More Repositories

1

zmPDSwR

Example R scripts and data for "Practical Data Science with R" 1st edition by Nina Zumel and John Mount (Manning Publications)
HTML
477
star
2

vtreat

vtreat is a data frame processor/conditioner that prepares real-world data for predictive modeling in a statistically sound manner. Distributed under choice of GPL-2 or GPL-3 license.
HTML
283
star
3

Examples

Various examples for different articles
HTML
152
star
4

wrapr

Wrap R for Sweet R Code
R
135
star
5

PDSwR2

Code, Data, and Examples for Practical Data Science with R 2nd edition (Nina Zumel and John Mount) https://github.com/WinVector/PDSwR2
HTML
130
star
6

pyvtreat

vtreat is a data frame processor/conditioner that prepares real-world data for predictive modeling in a statistically sound manner. Distributed under a BSD-3-Clause license.
Python
119
star
7

data_algebra

Codd method-chained SQL generator and Pandas data processing in Python.
Python
114
star
8

WVPlots

Pre-packaged plots in R
R
84
star
9

replyr

Patches for using dplyr with Databases and Big Data
HTML
67
star
10

BigDataRStrata2017

All material for "Modeling big data with R, sparklyr, and Apache Spark" Strata Hadoop 2017.
HTML
63
star
11

seplyr

Improved Standard Evaluation Interfaces for Common Data Manipulation Tasks
R
49
star
12

cdata

Higher order fluid or coordinatized data transforms in R. Distributed under choice of GPL-2 or GPL-3 license.
R
44
star
13

CampaignPlanner

Example code for Lesson on Response Campaign planning
HTML
38
star
14

rqdatatable

Implement the rquery piped query algebra in R using data.table. Distributed under choice of GPL-2 or GPL-3 license.
R
37
star
15

Logistic

Experimental logistic regression code supporting multiple result categories, many levels of categorical modeling variables, good optimization, L2 regularization and more.
Java
35
star
16

AutoDiff

Example automatic differentiation code in Scala
Scala
30
star
17

sigr

Concise formatting of significances in R (GPL3 license).
HTML
27
star
18

ExploreModels

Code and data for "The Geometry of Classifiers"
R
26
star
19

WinVector.github.io

Viewable pages from WinVector LLC view at: http://winvector.github.io
HTML
23
star
20

NestedModelsTalk

Support materials for WinVector talk
19
star
21

CampaignPlanner_v3

Shiny demo of A/B test planning and evaluation (improved UI for A/B testing method taught in free video course)
R
17
star
22

WVLPSolver

Experimental pure Java revised simplex linear program solver (Apache 2.0 license)
Java
15
star
23

Locality-Sensitive-Hashing-Example

Simple example of Locality Sensitive Hashing
Java
14
star
24

RcppDynProg

Dynamic Programming implemented in Rcpp. Includes example partition and out of sample fitting applications.
C++
14
star
25

ODSCWest2017

Win-Vector LLC ODSC West 2017 presentation materials (will be populated by the day of the conference)
HTML
14
star
26

kcomp

Demonstration of parametric bootstrap to find k for kmeans
HTML
10
star
27

ValidatingModelsInR

Slides and code for "Validating Models in R" Strata 2016 RDay http://conferences.oreilly.com/strata/hadoop-big-data-ca/public/schedule/detail/48053
HTML
10
star
28

SQLScrewdriver

Iterate through database tables (by JDBC) and TSV(tab separated values)/CSV(comma separated values) and load/dump data.
Java
8
star
29

wvpy

Tools to convert from Jupyter notebooks to and from Python .py files, and render.
HTML
8
star
30

FastBaseR

Examples of fast grouped row-wise operations in R (no C, C++, data.table, or dplyr used).
R
6
star
31

VectorDemo

Tutorial on using vectors in data science projects.
Jupyter Notebook
3
star
32

OutOfCore

Example of out of core coding techniques
Java
2
star
33

Importance-Sampling

Importance Sampling Example
Java
2
star
34

ExampleRPackage

Example of how to build a simple R package
R
2
star
35

ClassifierMetrics

Some examples of measuring classifier performance in R
HTML
2
star
36

QSurvival

Quasi observation based survival package for R.
R
2
star
37

LStep

Trivial demonstration of a diverging Newton-Raphson step when solving a logistic regression
Java
2
star
38

JXREF

Java based XML tool to help check Manning Agile Author XML for cross reference problems (Java based, GPL3+ license)
Java
1
star
39

ExperimentInspector

Java code to build synthetic data sets that match reported summary totals. Helps explore possible range of variation.
Java
1
star
40

crosspca

Cross-validated PCA/PCR demonstration based on the work: http://www.win-vector.com/blog/2016/05/pcr_part2_yaware/
R
1
star
41

SessionExample

Example code for articles on sessionizing data.
1
star
42

daccum

Example library to accumulate data frame rows in R
R
1
star
43

YConditionalRegularizedModel

Example of a neural net model, with regularization on y-conditional activation patterns
Jupyter Notebook
1
star
44

ATasteOfDataScience

Working an example of supervised machine learning in Python
Jupyter Notebook
1
star
45

CVRTSEncoder

Spectral encoding of categorical variables using model residual trajectories
R
1
star
46

wvu

Win Vector LLC Python data science teaching tools (graphs and data manipulation)
HTML
1
star
47

BreakingNestedModelBias

Support materials for Win-Vector blog article
HTML
1
star