---
title: "Introduction to DTwrappers"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Introduction_to_DTwrappers}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  tidy = TRUE
)
```

## Introduction

As an open-source language, R has many competing methods of data processing.  Selecting the preferred approach can depend on the goals of the work, the background of the practitioner, and the comparative advantages of the methods.  A beginning level user of R may have to simultaneously learn about the structure of the data, new methods of analysis, and the computational tools for implementation.  Coding syntax has a learning curve.  The DTwrappers package can help to simplify the process of building many complex queries and data processing steps.

The DTwrappers package was developed to produce wrapper functions for the data.table package.  Its capabilities encompass a broad range of data processing methods.  This package aims to achieve several goals:

1) Create a simplified syntax for data processing that minimizes the need for complex coding statements, all with the goal of helping beginning level R programmers learn to work with the language and to analyze data.

2) Provide access to a wide range of data processing methods.

3) Utilize data.table's methods in calculations to provide competitive speed and memory efficiency in analysis.

4) Teach users the syntax of data.table's methods by providing translated coding statements for the calculations performed.

While DTwrappers is intended for beginning level analysts, its methods may also be of use to experienced programmers from other languages who are new to working with R.  Furthermore, the package provides translations of all of its methods into data.table's syntax, which can allow users who are learning these methods to see many working examples of coding statements.

This vignette will introduce the methods of DTwrappers and provide working examples in the context of data analysis. We will primarily work with the **iris** data in R.  The following code is used to create a new object **dat** that has randomly reordered the rows of the iris data.

```{r setup}
library(DTwrappers)
data(iris)
n <- nrow(iris)
dt.name <- "dat"

RNGversion(vstr = 3.6)
set.seed(seed = 921)

# Randomizes the order of the rows.
dat <- data.table::as.data.table(x = iris[sample(x = 1:n, size = n, replace = F),])
head(dat)
```

The data records the measurements in centimeters of the lengths and widths of the sepals and petals of sampled irises.  The species are setosa, versicolor, and virginica.  The sample includes a total of `r n` observations, which are split evenly among the species.

###  Applications of DTwrapper functions

The DTwrappers package offers the following methods:

* **dt.choose.rows**:  Extracting a subset of the rows (observationsn or records) of the data.
* **dt.choose.cols**:  Extracting a subset of the variables (columns of the data).
* **dt.first.k.rows**:  Extracts the first (up to) k rows in each specified subgroup of a data set.
* **dt.last.k.rows**:  Extracts the last (up to) k rows in each specified subgroup of a data set.
* **dt.count.rows**:  Showing how many records are in the data, either overall or in groups.
* **dt.define.variable**:  Adding a new variable to an existing data.frame.
* **dt.remove.variables**:  Removing one or more variables from an existing data.frame.
* **dt.sort**:  Sorting a data.frame based upon the values of specified variables.
* **dt.calculate**:  Applying other functions in R to one or more variable, either separately or in combination.

These functions can be called quite simply, but they include many features that can facilitate more complex queries.  For instance, dt.calculate can be used for simple calculations of a mean or standard deviation, but these measures can be applied on specified subsets of the data, performed separately in subgroups, and scaled to simultaneous calculations on a large number of variables.

The following section will provide examples of these methods.

### 1) Function: dt.choose.rows

Reducing the data by extracting a subset of relevant rows is a fundamental step in many analyses.  The dt.choose.rows method is built as a wrapper function of data.table's filtering method (the i step).  A series of logical tests on variables within the data may be specified.  Options also exist to return a data.table coding statement (result = "code") for educational purposes or both the result and the code together (result = "all").


##### Example 1a) Choose the first 3 rows of the dataset.


We can extract the first 3 rows by providing the name of the data (dt.name) and a filter (1:3 as a numeric variable).

```{r display first 3 rows of iris dataset}
dt.choose.rows(dt.name = "dat", the.filter = 1:3)
```

We can also set the value of the.filter using a character version "1:3":

```{r display first 3 rows of iris dataset with filter}
dt.choose.rows(dt.name = "dat", the.filter = "1:3")
```

We can then display the code by setting return.as to "code":

```{r display first 3 rows of iris dataset with code}
dt.choose.rows(dt.name = "dat", the.filter = "1:3", return.as = "code")
```

Setting return.as = "all" will return a list (object) with elements for the result and the code:

```{r display first 3 rows of iris dataset with code and filter}
dt.choose.rows(dt.name = "dat", the.filter = "1:3", return.as = "all")
```


##### Example 1b) Choose rows based on a logical test

Logical tests may be used to extract rows that meet specified conditions.  Here we utilize the.filter by providing a logical test (as a character variable) to extract all of the rows in which the Sepal.Length is less than 4.4:

```{r chose rows when the sepal length is less than 4.4}
dt.choose.rows(dt.name = "dat", the.filter = "Sepal.Length < 4.4", return.as = "all")
```

Values of the.filter may also be specified as expressions:

```{r choose rows with filter as expression}
dt.choose.rows(dt.name = "dat", the.filter = expression(Sepal.Length < 4.4), return.as = "all")
```

More complex filtering may also be used:

```{r choose rows more complex filtering}
dt.choose.rows(dt.name = "dat", the.filter = "Sepal.Width >= 3 & Sepal.Length < 4.8 & Species == 'setosa'", return.as = "all")
```

Notice that the value 'setosa' was specified within the filter using single quotation marks, while the value of the.filter was wrapped in double quotation marks.  This can be switched, but they should not be mixed together.  Any confusion around this can be resolved by specifying the.filter as an expression.

### 2) Function: dt.choose.cols

This function selects a subset of columns from a data.frame or data.table.  It is built as a wrapper function of data.table's selection step (using .SD in the j step while specifying the .SDcols argument).  Grouped operations may be used to make these selections of columns and rows in each category. We will illustrate its usage in the examples below.

##### Example 2a) Choose all of the columns for the first 5 rows.

Similar to dt.choose.rows, one can specify the parameters dt.name and the.filter to extract rows from the data.  Then the relevant variables (columns) may be selected using the parameter the.variables.  Setting this value to "." (the default) will choose all of the rows.

We will begin by selecting all of the variables from the first 5 rows:

```{r chose all columns for iris with first 5 entries}
dt.choose.cols(dt.name = "dat", the.variables = ".", the.filter = "1:5", return.as = "all")
```


##### Example 2b) Select a subset of the variables

Setting the.variables to a character vector of variable names can be used to extract a subset of the columns:


```{r chose species and sepal length for first 3 entries}
dt.choose.cols(dt.name = "dat", the.variables = c("Species", "Sepal.Length"), the.filter = "1:3", return.as = "all")
```

Notice that the translation to data.table includes a call to .SD and .SDcols.  The structure of dt.choose.cols simplifies this step.  Furthermore, its coding translation can help those learning data.table to work more closely with these operators.

##### Example 2c) Grouping data and choosing a subset of rows for each group

Grouping operations help in building more complex queries.  When the grouping.variables parameter is specified, then the data will be partitioned into subgroups.  Then, within each subgroup, the first.k parameter can be used to extract the first (up to) k rows of data:

```{r chose first 2 rows of data for each species and print sepal length and sepal width for the flowers}
dt.choose.cols(dt.name = "dat", the.variables = c("Sepal.Length", "Sepal.Width"), grouping.variables = "Species", first.k = 2, return.as = "all")
```

This can equivalently be specified using the dt.first.k.rows function:

```{r first2_per_group}
dt.first.k.rows(dt.name = "dat", k = 2, the.variables = c("Sepal.Length", "Sepal.Width"), grouping.variables = "Species", return.as = "all")
```

Within dt.choose.cols, we can also use last.k to select (up to) the last k rows for each subgroup:

```{r chose last 2 rows of data for each species and print sepal length and sepal width for the flowers}
dt.choose.cols(dt.name = "dat", the.variables = c("Sepal.Length", "Sepal.Width"), grouping.variables = "Species", last.k = 2, return.as = "all")
```

This can equivalently be calculated with the function dt.last.k.rows:

```{r dt.last.k.rows}
dt.last.k.rows(dt.name = "dat", k = 2, the.variables = c("Sepal.Length", "Sepal.Width"), grouping.variables = "Species", return.as = "all")
```

More generally, a vector of rows to select for each group may also be specified using the row.indices parameter of dt.choose.cols:

```{r row indices}
dt.choose.cols(dt.name = "dat", the.variables = c("Sepal.Length", "Sepal.Width"), grouping.variables = "Species", row.indices = c(3,5,7), return.as = "all")
```

The dt.choose.cols method allows the user to 1) reduce the data to a subset, 2) group the data by one or more variables, 3) select columns of data, and 4) provide specialized subsets such as the first k, last k, or selected rows within each group.  These operations involve a number of steps that might otherwise be challenging for new analysts to construct.  The data.table code is somewhat more compact but requires knowledge of its specialized operators .N, .SD, and .SDcols. Meanwhile, the translated code also allows users to gain familiarity with data.table's methods.

### 3) Function: dt.count.rows

This function counts the number of qualifying rows in a data.table or data.frame object. It is built as a wrapper function of data.table's counting method (the .N operator).  These counts may be comprehensive for the entire table or conducted in groups.  The full data can also be filtered for qualifying cases prior to counting the rows.

##### Example 3a) Counting the Number of Rows in a data set 

This method will provide a simple count, which is similar to the nrow function:

```{r Count the number of rows in the dataset iris}
dt.count.rows(dt.name = "dat", return.as = "all")
```

##### Example 3b) Counting with filtering

Counts may also be specified in a subset of extracted rows using the.filter:

```{r Count number of rows where species is "Setosa"}
dt.count.rows(dt.name = "dat", the.filter = "Species == 'setosa'", return.as = "all")
```

##### Example 3c) Counting in groups

Counting can also be performed in subgroups of the data:

```{r Count in subgroups}
dt.count.rows(dt.name = "dat", the.filter = "Petal.Length > 1.7", grouping.variables = "Species", return.as = "all")
```

The name of the counting variable (N by default) may also be specified:

```{r Count in subgroups with new name}
dt.count.rows(dt.name = "dat", the.filter = "Petal.Length > 1.7", grouping.variables = "Species", count.name = "Total Qualifying Rows", return.as = "all")
```


### 4) Function: dt.define.variable

This function allows a user to add a new variable to an existing data.frame or data.table.  It can also be used to update the values of previously defined variables.  It is built as a wrapper function of data.table's method of defining new variables by reference using the := operator. The new values can be stated either through a statement of the calculation or by directly providing a vector of values. These updates can also be performed on a subset of the data by incorporating a filter.  

##### Example 4a) Creating a new variable by value

We will now create a new variable called Category with the constant value "Flower: Iris" in all rows of the data.

```{r define category as a new column with iris as its value}
dt.define.variable(dt.name = "dat", variable.name = "Category", the.values = "Flower: Iris", return.as = "all", specification = "by.value")
```

Setting specification = 'by.value' allows us to assign specific values to the variable rather than providing calculations. 

##### Example 4b) Creating a new variable with a calculated expression

Now we will create a new variable called Max_Sepal_Length_Species.  This is meant to convey the maximum value of Sepal Length for each species.  We can calculate this by applying the max() function to the Sepal.Length variable while grouping by species.  Here we define this new variable using specification = "by.expression" because it calls for a calculation rather than directly supplying the values.

```{r define max sepal length species variable}
dt.define.variable(dt.name = "dat", variable.name = "Max_Sepal_Length_Species", the.values = "max(Sepal.Length)", specification = "by.expression", grouping.variables = "Species", sortby.group = TRUE, return.as = "all")
```

##### Example 4c) Creating a new variable in subsets of rows

It is also possible to define new variables in specified rows by applying an appropriate filter.  Here we define a new variable that only applies to the setosa flowers:

```{r define.variable.in.subset}
dt.define.variable(dt.name = "dat", variable.name = "setosa_sl_below_5", the.values = "Sepal.Length < 5", specification = "by.expression", the.filter = "Species == 'setosa'", return.as = "all")
```

The new values are only supplied in the rows corresponding to setosa flowers.  For the other rows of the data, the new variable is set with missing values.


### 5) Function: dt.remove.variables

This function is designed to remove columns of data.  Note that once a variable is removed, its values cannot be retrieved.

##### Example 5) Removing a variable from a dataset

```{r removing category as a variable}
dt.remove.variables(dt.name = "dat", the.variables = c("Category", "setosa_sl_below_5"), return.as = "all")
```

Reviewing the translated code, this step is achieved by setting the variables to remove equal to a value of NULL.  Note that this operation cannot be performed in subsets of the rows of the data; it removes all measurements of the selected variables.  However, dt.define.variable can be used to set specific values to missing (NA) in subsets if that is sufficient.

### 6) Function: dt.sort

This function sorts the rows of a data.frame or data.table based on selected columns.  It is built as a light wrapper function of data.table's setorderv() function.  

#### Example 6a) Sorting data by increasing values of a variable

```{r sort species and sepal length in increasing order }
dt.sort(dt.name = "dat", sorting.variables = c("Species", "Sepal.Length"), sort.increasing = TRUE, return.as = "all")
```

The sorting proceeds in the order of the specified sorting.variables.  In the above example, the data are sorted by Species first.  Then, within groups of Species, the data are additionally sorted by increasing Sepal.Length.  


#### Example 6b) Sorting data in decreasing order

The data can be sorted in decreasing order by specifying sort.increasing = FALSE:

```{r sort species and sepal length in decreasing order }
dt.sort(dt.name = "dat", sorting.variables = c("Species", "Sepal.Length"), sort.increasing = FALSE, return.as = "all")
```


#### Example 6c) Selecting the ordering of each sorting variable.

Each sorting variable's ordering can be specified as either increasing (TRUE) or decreasing (FALSE):

```{r species in increasing order and sepal length in decreasing order}
dt.sort(dt.name = "dat", sorting.variables = c("Species", "Sepal.Length"), sort.increasing = c(T, F), return.as = "all")
```

### 7) Function: dt.calculate

The prior operations of selecting rows or columns, filtering the data, counting the number of rows, defining or removing variables, and sorting the data provide helpful tools.  However, most analyses of data rely upon some form of calculation.  This may apply to a single variable or be repeated across many variables.  Considerations of filtering and grouping variables may also apply.  The dt.calculate function is designed to facilitate calculations using additional methods in R to one or more variables of the data.  

##### Example 7a) Applying One Function to One Variable

Here we demonstrate a calculation of the mean of the Sepal.Length:

```{r calculate_one_fn_one_variable}
dt.calculate(dt.name = "dat", the.functions = "mean", the.variables = "Sepal.Length", return.as = "all")
```

##### Example 7b) Applying multiple functions to one variable

This can be extended to multiple functions:

```{r calculate_multiple_fn}
dt.calculate(dt.name = "dat", the.functions = c("mean", "median", "sd"), the.variables = "Sepal.Length", return.as = "all")
```

##### Example 7c) Adding parameters to the functions

It is also possible to supply additional arguments to the functions by specifying the other.params variable:

```{r dt.calculate.parameters}
dt.calculate(dt.name = "dat", the.functions = c("mean", "median", "sd"), the.variables = "Sepal.Length", other.params = "na.rm = T", return.as = "all")
```

Note that when multiple functions are specified, the supplied values of other.params must be compatible with all of these functions.  So, for instance, setting na.rm = T can apply to the mean, median, and standard deviation functions, but it would not necessarily work with other functions -- e.g. nrow() -- that might be considered.

##### Example 7d) dt.calculate with filtering and grouping statements

Calculations can be extended to allow for filtering and grouping:

```{r dt.calculate.filter.group}
dt.calculate(dt.name = "dat", the.functions = c("mean", "median", "sd"), the.variables = "Sepal.Length", the.filter = "Sepal.Length > 3.5", grouping.variables = "Species", return.as = "all")
```

##### Example 7e) Applying multiple functions to multiple variables

This can also be scaled to include more than one variable:

```{r dt.calculate.filter.group.mult.variables}
dt.calculate(dt.name = "dat", the.functions = c("mean", "median", "sd"), the.variables = c("Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width"), the.filter = "Sepal.Length > 3.5", grouping.variables = "Species", return.as = "all")
```

##### Example 7f) Tables in wide format

The table.format variable can be used to shift the results to wide format:

```{r dt.calculate.filter.group.mult.variables.wide}
#dt.calculate(dt.name = "dat", the.functions = c("mean", "median", "sd"), the.variables = c("Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width"), the.filter = "Sepal.Length > 3.5", grouping.variables = "Species", table.format = "wide", return.as = "all")
```

When wide formats are used for calculations on multiple variables and functions, then the results create columns with names of the form variable_function.  Additionally, to create tables in wide format, the translated data.table statement does not use multiple lapply statements but rather enumerates each function as applied to each variable.



##### Example 7g) Customized functions

Whether the function to apply exists as a base method, from a loaded package, or is a customized function defined by the user, dt.calculate is able to make use of the method.  Here we create a simple function called `triple mean` that calculates 3 times the mean value:

```{r adding triple mean as a function in dt.calculate}
`triple mean` <- function(x, na.rm = T){
  return(3 * mean(x = x, na.rm = na.rm))
}

dt.calculate(dt.name = "dat", the.variables = c("Sepal.Length", "Sepal.Width"), the.functions = c("mean", "sd", "triple mean"), grouping.variables = "Species", table.format = "long", return.as = "all")
```




##### Example 7h) Functions of multiple variables

The examples of dt.calculate provided so far are meant to apply to individual variables.  Calculating the mean or the standard deviation is separately applied to each variable.  However, some functions -- e.g. rowSums or linear regression -- are meant to apply to multiple variables with a single calculation.  These can be incorporated into dt.calculate by specifying individual.variables = FALSE.  Here we will review a few examples of these methods.

First, we will calculate the row sums of the numeric variables for the first 5 rows:

```{r rowSums}
dt.calculate(dt.name = "dat", the.functions = "rowSums", the.variables = c("Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width"), the.filter = 1:5, individual.variables = FALSE, return.as = "all")
```

##### Example 7i) Linear regression in subgroups

Here we will construct an example of extracting the table of estimated coefficients from a linear regression that is separately performed in each species.  To do so, we will first define a customized extraction function:

```{r get.lm.coefs}
get.lm.coefs <- function(data, formula){
  require(data.table)
  mod <- lm(formula = formula, data = data)
  the.coefs <- as.data.table(x = summary(mod)$coefficients, keep.rownames = TRUE)
  setnames(x = the.coefs, old = "rn", new = "Variable")
  return(the.coefs)
}
```

Then we will apply this function to a linear regression of Sepal.Length in terms of the other numeric variables, calculated separately for each species:

```{r linear regression using dt.calculate}
## linear regression 
dt.calculate(dt.name = "dat", the.functions = "get.lm.coefs", grouping.variables = "Species", other.params = "formula = Sepal.Length ~ Sepal.Width + Petal.Length + Petal.Width", return.as = "all", individual.variables = F, add.function.name = F)
```





#### Conclusion

DTwrappers presents a wide range of data processing applications.  They are all built with the goal of reducing the requirements to learn computer programming.  These methods are not without some degree of syntax.  However, these methods are intended to reduce the learning curve needed to perform data analysis.  DTwrappers can allow a user to focus on the core analytical problems without the requirement of specialized coding operators or the necessary ordering of multiple functions.  For some beginning analysts, the DTwrappers package may help them acclimate to working with data and utilizing more advanced methods.  Programmers with experience in other languages can also more easily transfer that knowledge by using DTwrappers.

The running time complexity of DTwrappers is a function of its translations along with processing the data using data.table's methods.  The translation step includes some degree of additional overhead.  However, this portion is not tied to the overall sample size of the data.  In practice, the running time performance of DTwrappers should not be substantially different from that of data.table, especially at larger sample sizes.

Additionally, it should be noted that the DTwrappers is intended to serve as a supplement to existing data processing packages rather than as a replacement.  Its methods may be beneficial for a subset of R's users.  Determining the relative utility of the package is an area of further exploration.
