---
title: 'awkreader:  Pre-Filtering and Pattern Searching for Combined File Reading'
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Introduction_to_awk}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

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

```{r setup}
library(awkreader)
```

## Introduction

Reading, aggregating, and subsetting data from files is a fundamental operation in many analyses in R.  Many methods of reading files are available through R and its extension packages.  The usual practice is to read in each file separately, input the entire data set, and then use subsequent calculations within R to perform aggregation and filtering.  Performing these steps as part of reading the files presents an opportunity to reduce the amount of computational memory and programming steps needed to produce the intended data set.  The awkreader package is constructed to solve these problem.  It provides simple methods to read multiple files, pre-filter the data as part of the reading, and to search for patterns in the records.

The awkreader package is built on a translation of R code that produces coding statements in the AWK language.  This command-line code is sufficiently flexible to read multiple files, pre-filter the inputs, and search for patterns in the data.  AWK commands can be processed to read data using the **fread** function from the **data.table** package.  The awkreader package allows a user to specify simple inputs with a syntax familiar to users of R.  By translating the code, awkreader gives users access to some of the capabilities of programming in AWK without having to learn the language.

The awkreader package introduces few new methods:

* **combined.fread**:  A method to read multiple files of the same structure.

* **filtered.fread**:  A method to introduce filtering conditions that are written in R's syntax.  Any row of data that satisfy's the filter's logical test will be included.  This can be employed while reading multiple files of the same structure.

* **pattern.fread**:  A method to search for one or more patterns in the files.  Any row that matches the combination of specified patterns will be included.  This method may also be applied in reading multiple files of the same structure.

* **record.count**:  A method to count the total number of records across files without loading the full data into memory. Filtering conditions can be applied so that only rows satisfying the logical tests are counted.

* **aggregated.fread**: A method for calculating summaries like sum, mean, median, etc., directly during the file-reading process. This allows for grouped aggregations across multiple files while drastically reducing memory overhead.

These methods assume that the data in the files have a reasonably consistent structure.  The assumptions include:

* Each file includes a header row.

* The variables are formatted in the same order across the files.

* Consistent delimiters are used.

The applications of the awkreader package can be quite beneficial in the following contexts:

* **Combined Reads**:  When the relevant data are stored in multiple files (e.g. by date or by account), using a single command to read and aggregate the files is more efficient and requires fewer programming steps.

* **Filtered Reads**:  By applying the filtering logic within the reading process, the resulting data will only be a subset of the contents of the original files.  This reduces the data that is stored and used in R to only the relevant records.  In large file systems, this can introduce significant computational savings and extra data from a set of original files that would otherwise exceed the system's memory.

* **Pattern Searching**:  Similarly, searching for patterns is a separate way of pre-filtering the data in the reading process.

* **Record Counting**: When only the volume or frequency of data is needed, reading the entire dataset is inefficient. Pushing the counting operation to AWK saves time and memory.

* **Summarizing data**: When the goal is to produce reporting metrics or grouped summaries, aggregating the data during the read step prevents memory bottlenecks and circumvents the need to build imperative staging tables.

## Data Files

In order to demonstrate the capabilities of the awkreader package, we have posted the following files to **[ratings-data folder](https://drive.google.com/drive/folders/1QiVqV_HGhVWNrMFJcqPbKUz7jetw4anb)**:

* **Ratings Data**:  This folder contains many files of simulated ratings.  Each file contains variables for the user's identifier, product's identifier, and an integer rating from 1 to 5.  The data are separated into individual files for each user. All of the files are comma separated values (CSV) files with the same headers and ordering of the variables.

* **Titanic Data**:  A copy of the publicly available Titanic data -- also accessible using data(Titanic) in R -- is posted as a CSV file.

## AWK and Operating Systems

AWK is a command line programming language.  Some operating systems (e.g. Mac OS and Linux) include native installations of the program.  Windows operating systems require an installation of AWK to use the awkreader package. These installations can vary somewhat in terms of their coding syntax.  As a result, awkreader's translations to AWK code differ based upon the operating system.  The coding examples will demonstrate the differences in the required specifications and the translations that are produced.

## Examples

Here we will demonstrate the usages of the awkreader package along with its capabilities in reading and filtering multiple files.

For the purpose of the following examples, we will assume that the data files mentioned above are loaded in a local directory.

### Using combined.fread

The purpose of combined.fread is to read and aggregate data from multiple files.


Here we will use list.files to generate a character vector of all of the ratings data files.  Then we will use combined.fread to read and aggregate the first two files.

```{r }
data.path <- system.file("extdata", "ratings_data", package = "awkreader")

all.files <- list.files(path = data.path, full.names = TRUE)

the.files <- all.files[1:2]

combined.fread(the.files = the.files)
```

When specifying a value of **nrows**, the program will read in at most this number of rows of data.  The order is determined by the.files:

```{r }
combined.fread(the.files = the.files, nrows = 5)
```

The result includes a column (called "file" by default) that shows the source file of each row of data.  This is helpful for the purpose of aggregation.  The name of this column header can also be specified:

```{r }
combined.fread(the.files = the.files, nrows = 5, file.header = "source_file")
```

When not needed, the variable specifying the source file may be excluded by setting include.filename = FALSE:


```{r }
combined.fread(the.files = the.files, nrows = 5, include.filename = F)
```


It is also possible to specify which variables to include:

```{r }
combined.fread(the.files = the.files, the.variables = c("item", "rating"), nrows = 5)
```

The default value "." for the.variables will return all of the variables in the source files.  Additionally, if no valid variables are specified, then the method reverts to reading in all of the variables.

The program is set to return the data as a data.table object by default.  However, this may be switched to a data.frame:

```{r }
combined.fread(the.files = the.files, the.variables = c("item", "rating"), nrows = 5, return.data.table = F)
```

It is also possible to specify variables that should not be included in the data using the drop parameter.  This may be specified by column index or name, as shown in the examples below:

```{r }
combined.fread(the.files = the.files, nrows = 5, drop = c(1,3), include.filename = F)

combined.fread(the.files = the.files, nrows = 5, drop = c("user", "rating"), include.filename = F)
```

Rather than outputting the data, the method can be specified to instead display the AWK statements that would read in the data as shell commands:

```{r }
combined.fread(the.files = the.files, the.variables = c("item", "rating"), return.as = "code")
```

It is also possible to return a list object including both the resulting data and the code:

```{r }
combined.fread(the.files = the.files, the.variables = c("item", "rating"), nrows = 5, return.as = "all")
```
The methods also natively supports directory-level searches and file pattern matching (e.g., wildcards or extensions). This allows you to point combined.fread at an entire folder:

```{r}
# Count records in all CSV files in the data directory
combined.fread(the.files = data.path, file.pattern = "*.csv")
```
The file.pattern parameter accepts multiple types of inputs, such as simple extensions (".csv", "csv"), custom regex strings ("\\.csv$"), or wildcard expansions ("*.csv"). Alternatively, you can pass a wildcard path directly to the.files, which the function handles gracefully.

If you want to search inside subfolders within the directory, you can set the recursive parameter to TRUE:

```{r}
combined.fread(the.files = data.path, file.pattern = "csv", recursive = TRUE)
```
When return.as = "all" or "code" is specified, the method will output the AWK scripts it generated to perform the counting:

```{r}
combined.fread(the.files = the.files, return.as = "code")
```

#### Skipping Unnecessary Rows
Sometimes you might need to bypass a few rows before applying operations to your data. To handle this, the method provides the skip parameter, which flexibly accepts an integer, a character string, or a list:

```{r}
combined.fread(the.files = data.path, skip = 0)
# combined.fread(the.files = data.path, skip = "pattern_to_match")
```
**Note on behavior:**

**If an integer is provided:** The function blindly skips that many lines at the top of the file. Use this approach only when skipping file metadata, as the function will assume the row immediately following the skipped lines is your header.

**If a character string is provided:** The function scans the first 100 lines of the file to find the first row matching that string. It then skips all preceding metadata rows, effectively treating the matched row as your header.

Alternatively, you can provide a list to the skip parameter for precise, separate control over skipping data rows versus metadata rows. This explicit method automatically preserves the header in between:

```{r}
combined.fread(the.files = data.path, skip = list(skip.data.rows = 4, skip.metadata.rows = 0))
```
You can even mix types within this list. For instance, skip.metadata.rows can accept a character string to dynamically locate the header, while skip.data.rows uses an integer to skip a fixed number of data entries immediately following it.

#### Delimiters
To support various file types and increase versatility, you can specify custom delimiters via the delim parameter:

```{r}
combined.fread(the.files = data.path, delim = ",")
```

A couple of caveats apply to the AWK coding statements that are generated:

* The column headers are separately added to the resulting data.  In this instance, directly running the AWK command will not produce the correct column headers alone.

* The value of nrows is separately specified as part of data.table's fread method.  It is not directly included in the AWK coding statements.  When batching the data, an additional step is used in post-processing to limit the results to the number of rows.

### Using filtered.fread

The filtered.fread method extends combined.fread by introducing the capability to apply filtering statements while reading the data.  The filters are written as logical tests using R's syntax.

When no filter is applied (using NULL, NA, or a blank character string ""), the full data will be read.  Notice that most of the inputs to filtered.fread correspond to those of combined.fread:

```{r }
filtered.fread(the.files = the.files, the.filter = NULL, nrows = 5, include.filename = F)
```

Simple filters can then be introduced in a character value using language that follows R's coding syntax:

```{r }
## Write filtering language in R's syntax
filtered.fread(the.files = the.files, the.filter = "rating == 5", nrows = 5)
```

More complex logical tests may be used.  Here we demonstrate the logical AND operator:

```{r }
filtered.fread(the.files = the.files, the.filter = "rating >= 3 & item == '1fg4sLgEFzAtOqCa'")
```

Note that in specifying a value for the item, it is provided in quotation marks that do not match the broader specification of the.filter.  If the outside quotations are double quotations, then the inside should be single quotation marks (and vice versa).

```{r }
filtered.fread(the.files = the.files, the.filter = 'rating >= 3 & item == "1fg4sLgEFzAtOqCa"')
```

The logical OR operator may also be used:

```{r }
filtered.fread(the.files = the.files, the.filter = 'rating == 3 | rating == 4', nrows = 5)
```

We can also use negations with the logical NOT operator:

```{r }
filtered.fread(the.files = the.files, the.filter = 'rating != 1 & rating != 2 & rating != 3 & rating != 4', nrows = 5)
```

R also includes a subsetting %in% operator that returns TRUE if the value of the left hand side equals at least one value in the right hand side:

```{r }
filtered.fread(the.files = the.files, the.filter = 'rating >= 3 & item %in% c("1fg4sLgEFzAtOqCa", "6qI9cBWT76jxm42G")')

filtered.fread(the.files = the.files, the.filter = 'rating >= 3 & item %in% c("1fg4sLgEFzAtOqCa", "6qI9cBWT76jxm42G")', return.as = "code")
```

In the AWK coding statement, this use of %in% is translated to a series of OR statements for all of the values of the right hand side.  Keep in mind that this could lead to a very long translation for vectors with a large number of unique values.

As an extension, filtered.fread is also designed to evaluate existing variables in R as part of the specification of the.filter:

```{r }
two.items <- c("1fg4sLgEFzAtOqCa", "6qI9cBWT76jxm42G")
filtered.fread(the.files = the.files, the.filter = 'rating >= 3 & item %in% two.items', return.as = "all")
```

Notice here that the translated code is based on the value of the variable **two.items**.  This allows the method to use existing variables that are not contained within the scope of the files to be read by AWK.

The logical NOT IN operator %nin% is used to negate an %in% operator.  While not standard to base R, %nin% is used by some extension packages.  In particular, (x %nin% y) is equivalent to !(x %in% y).

```{r }
filtered.fread(the.files = the.files, the.filter = 'rating %nin% c(1:2, 4)', nrows = 5)
```

With the possibility for complex filters and aggregations from many files, AWK's limits on the maximum length of a coding statement may be triggered.  In this case, the methods proceed with a batched approach that reads a fraction of the files at once.  The resulting data are aggregated.  However, when result = "all" or result = "code", the coding statements are returned as a vector based on the batches.

In the following example, we will filter, read, and aggregate data from all 2000 data files in batches of 100.

```{r }
the.output <- filtered.fread(the.files = all.files, the.filter = 'rating >= 4 & item %in% two.items', include.filename = T, num.files.per.batch = 10, show.warnings = FALSE, return.as = "all", nrows = 5)

print(the.output$result)
print(the.output$code[1:2])
```

Some of the batches may include no relevant data that satisfies the.filter.  In those cases, a warning will be generated by data.table's fread command.  These warnings can be suppressed with show.warnings = FALSE.

#### Headers
Some datasets may not include a header row. To handle files without headers, set header = FALSE. The function will automatically assign default data.table column names (V1, V2, V3, ...). Any filter statements can then be written using these default names:

```{r}
filtered.fread(the.files = the.files, header = FALSE, the.filter = "V3==5", include.filename = F, drop = c("V1", "V2"), return.as = "all")
```

### Using pattern.fread

As a complement to applying logical filters, pattern matching may also be used as a means of extracting a subset of data read from multiple files.

Here we will extract the rows that match a specific pattern:

```{r dfd}
pattern.fread(the.files = the.files, the.patterns = "5n9ziP", return.as = "all")
```

It is also possible to search for negated patterns.  Here we use the **tf** parameter to specify whether a pattern should be searched for as it is (TRUE) or negated (FALSE).  When negated, pattern.fread returns the complement of what would otherwise be produced:

```{r }
pattern.fread(the.files = the.files, the.patterns = "5n9ziP", tf = FALSE, return.as = "all")
```

When multiple patterns are supplied, they can be connected using logical operators.  Specifying "and" in the connectors parameter would return records that include both patterns.

```{r }
pattern.fread(the.files = the.files, the.patterns = c("QPW5X7c", "ziPoS"), connectors = "and", return.as = "result")
```

It is also possible to connect patterns with an OR operator:

```{r }
pattern.fread(the.files = the.files, the.patterns = c("ThHYoPWn4IVJ", "ziPoS", "jTXm3t"), connectors = c("or", "or"))
```

When mixing AND and OR, the logical operation will proceed with no parentheses:

```{r }
pattern.fread(the.files = the.files, the.patterns = c("W5X7", "ziPoS", "jTXm3t"), connectors = c("and", "or"), return.as = "all")
```

Finally, we can turn to the Titanic data to search for specific patterns:

```{r }
titanic.file <- system.file("extdata", "titanic.csv", package = "awkreader")
pattern.fread(the.files = titanic.file, the.patterns = c("Female", "Child", "1st"), tf = c(T, T, F), connectors = c("or", "and"))
```

### Using record.count
The record.count method allows you to quickly count the number of rows across multiple files without reading the actual dataset into memory. This is highly efficient when you only need volume metrics or want to check data size prior to a larger read.

You can calculate a simple count of all records in the specified files:

```{r}
record.count(the.files = the.files)
```
Similar to filtered.fread, you can supply logical filters to count only the rows that satisfy specific criteria:

```{r}
record.count(the.files = the.files, the.filter = "rating == 5")
```

### Using aggregated.fread
The aggregated.fread method pushes summary computations (such as means, sums, or medians) directly to the file-reading level. Instead of loading raw, row-level data into R and aggregating it afterward, the calculations are processed by AWK on the fly.

You can compute metrics across specific variables, optionally grouping the output. For example, to calculate the mean and median ratings for each item:

```{r}
aggregated.fread(
  the.files = the.files,
  group.by = "item",
  summarize.with = list(mean = "rating", median = "rating")
)
```
You can seamlessly apply multiple grouping variables and aggregations in a single pass. AWK will calculate all specified metrics across the provided grouping variables:

```{r}
aggregated.fread(
 the.files = the.files,
 summarize.with = list(mean = list("rating"), sd = list("rating"), median = list("rating")),
 group.by = c("user", "item")
)
```
Additionally, you can calculate the sample size (number of observations) for each group:

```{r}
aggregated.fread(
  the.files = the.files,
  group.by = "item",
  summarize.with = list(mean = "rating", sd = "rating", sample.size = TRUE)
)
```
For convenience, the sample size parameter accepts multiple aliases: "n", "count", "sample_size", "samplesize", or "n_obs".

You can also apply inline mathematical functions (those that can be evaluated row-by-row without storing the entire vector in memory) directly within the aggregation statements:

```{r}
aggregated.fread(
  the.files = the.files,
  group.by = "item",
  summarize.with = list(mean = "sqrt(rating)", sd = "log(rating)")
)
```
#### Computing Streaming Medians
Calculating medians on massive, distributed datasets typically requires holding all values in memory. To optimize memory usage, aggregated.fread calculates medians using the streaming P-Square algorithm.

You can restrict the maximum number of observations per group used by this algorithm via the sample.size.median parameter. A positive integer will cap the sample, speeding up calculation times on large files. The default is -1 (or 0), which means the algorithm will process every row without sampling limitations:

```{r}
aggregated.fread(
  the.files = the.files,
  group.by = "user",
  summarize.with = list(median = "rating"),
  sample.size.median = -1
)
```

#### Using Correlation
The syntax for correlation is slightly different from other metrics, as it requires exactly two variables to operate on:

```{r}
aggregated.fread(
  the.files = the.files,
  group.by = "user",
  summarize.with = list(cor = "sqrt(item), log(item)")
)
```