awkreader: Pre-Filtering and Pattern Searching for Combined File Reading

The hardware and bandwidth for this mirror is donated by dogado GmbH, the Webhosting and Full Service-Cloud Provider. Check out our Wordpress Tutorial.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]dogado.de.

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:

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.

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)
#>          user             item rating
#>        <char>           <char>  <int>
#>   1: a7gzXxfI 0JFCjVx2P1RMzy3h      4
#>   2: a7gzXxfI 0kG80toKp2msfAut      5
#>   3: a7gzXxfI 1Bji5PQIOKXaMGZq      3
#>   4: a7gzXxfI 1fg4sLgEFzAtOqCa      5
#>   5: a7gzXxfI 3k7Wf4yv0RV6vi4K      5
#>  ---                                 
#> 196: QPW5X7ci uNp5n9ziPoSjwab6      1
#> 197: QPW5X7ci uPU8XKJD4wo3Twss      1
#> 198: QPW5X7ci uXjCOKMvr1gPaxTg      4
#> 199: QPW5X7ci vPI43TEe3CMQUM5U      3
#> 200: QPW5X7ci wN8YPrJls7N3vGjC      1
#>                                                                              file
#>                                                                            <char>
#>   1:  /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>   2:  /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>   3:  /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>   4:  /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>   5:  /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>  ---                                                                             
#> 196: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv
#> 197: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv
#> 198: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv
#> 199: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv
#> 200: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv

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:

combined.fread(the.files = the.files, nrows = 5)
#>        user             item rating
#>      <char>           <char>  <int>
#> 1: a7gzXxfI 0JFCjVx2P1RMzy3h      4
#> 2: a7gzXxfI 0kG80toKp2msfAut      5
#> 3: a7gzXxfI 1Bji5PQIOKXaMGZq      3
#> 4: a7gzXxfI 1fg4sLgEFzAtOqCa      5
#> 5: a7gzXxfI 3k7Wf4yv0RV6vi4K      5
#>                                                                           file
#>                                                                         <char>
#> 1: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 2: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 3: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 4: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 5: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv

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:

combined.fread(the.files = the.files, nrows = 5, file.header = "source_file")
#>        user             item rating
#>      <char>           <char>  <int>
#> 1: a7gzXxfI 0JFCjVx2P1RMzy3h      4
#> 2: a7gzXxfI 0kG80toKp2msfAut      5
#> 3: a7gzXxfI 1Bji5PQIOKXaMGZq      3
#> 4: a7gzXxfI 1fg4sLgEFzAtOqCa      5
#> 5: a7gzXxfI 3k7Wf4yv0RV6vi4K      5
#>                                                                    source_file
#>                                                                         <char>
#> 1: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 2: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 3: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 4: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 5: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv

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

combined.fread(the.files = the.files, nrows = 5, include.filename = F)
#>        user             item rating
#>      <char>           <char>  <int>
#> 1: a7gzXxfI 0JFCjVx2P1RMzy3h      4
#> 2: a7gzXxfI 0kG80toKp2msfAut      5
#> 3: a7gzXxfI 1Bji5PQIOKXaMGZq      3
#> 4: a7gzXxfI 1fg4sLgEFzAtOqCa      5
#> 5: a7gzXxfI 3k7Wf4yv0RV6vi4K      5

It is also possible to specify which variables to include:

combined.fread(the.files = the.files, the.variables = c("item", "rating"), nrows = 5)
#>                item rating
#>              <char>  <int>
#> 1: 0JFCjVx2P1RMzy3h      4
#> 2: 0kG80toKp2msfAut      5
#> 3: 1Bji5PQIOKXaMGZq      3
#> 4: 1fg4sLgEFzAtOqCa      5
#> 5: 3k7Wf4yv0RV6vi4K      5
#>                                                                           file
#>                                                                         <char>
#> 1: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 2: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 3: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 4: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 5: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv

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:

combined.fread(the.files = the.files, the.variables = c("item", "rating"), nrows = 5, return.data.table = F)
#>               item rating
#> 1 0JFCjVx2P1RMzy3h      4
#> 2 0kG80toKp2msfAut      5
#> 3 1Bji5PQIOKXaMGZq      3
#> 4 1fg4sLgEFzAtOqCa      5
#> 5 3k7Wf4yv0RV6vi4K      5
#>                                                                          file
#> 1 /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 2 /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 3 /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 4 /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 5 /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv

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:

combined.fread(the.files = the.files, nrows = 5, drop = c(1,3), include.filename = F)
#>                item
#>              <char>
#> 1: 0JFCjVx2P1RMzy3h
#> 2: 0kG80toKp2msfAut
#> 3: 1Bji5PQIOKXaMGZq
#> 4: 1fg4sLgEFzAtOqCa
#> 5: 3k7Wf4yv0RV6vi4K

combined.fread(the.files = the.files, nrows = 5, drop = c("user", "rating"), include.filename = F)
#>                item
#>              <char>
#> 1: 0JFCjVx2P1RMzy3h
#> 2: 0kG80toKp2msfAut
#> 3: 1Bji5PQIOKXaMGZq
#> 4: 1fg4sLgEFzAtOqCa
#> 5: 3k7Wf4yv0RV6vi4K

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:

combined.fread(the.files = the.files, the.variables = c("item", "rating"), return.as = "code")
#> [1] "'/usr/bin/gawk' -f \"BEGIN { FS=\",\"; OFS=\",\" } FNR <= 1 { next } {  print $2,$3,FILENAME }\" '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv'"

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

combined.fread(the.files = the.files, the.variables = c("item", "rating"), nrows = 5, return.as = "all")
#> $result
#>                item rating
#>              <char>  <int>
#> 1: 0JFCjVx2P1RMzy3h      4
#> 2: 0kG80toKp2msfAut      5
#> 3: 1Bji5PQIOKXaMGZq      3
#> 4: 1fg4sLgEFzAtOqCa      5
#> 5: 3k7Wf4yv0RV6vi4K      5
#>                                                                           file
#>                                                                         <char>
#> 1: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 2: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 3: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 4: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 5: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 
#> $code
#> [1] "'/usr/bin/gawk' -f \"BEGIN { FS=\",\"; OFS=\",\" } FNR <= 1 { next } {  print $2,$3,FILENAME }\" '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv'"

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:

# Count records in all CSV files in the data directory
combined.fread(the.files = data.path, file.pattern = "*.csv")
#>             user             item rating
#>           <char>           <char>  <int>
#>      1: a7gzXxfI 0JFCjVx2P1RMzy3h      4
#>      2: a7gzXxfI 0kG80toKp2msfAut      5
#>      3: a7gzXxfI 1Bji5PQIOKXaMGZq      3
#>      4: a7gzXxfI 1fg4sLgEFzAtOqCa      5
#>      5: a7gzXxfI 3k7Wf4yv0RV6vi4K      5
#>     ---                                 
#>  99996: mbPQOZV1 yO8XGg7a9UHbqatL      3
#>  99997: mbPQOZV1 ySHGYLNC7XtywfIZ      2
#>  99998: mbPQOZV1 yeZcUlaFqrZmEB9a      3
#>  99999: mbPQOZV1 yrltwtIEX93JzLYx      5
#> 100000: mbPQOZV1 zfL4EAaUxSMqxtX1      4
#>                                                                                  file
#>                                                                                <char>
#>      1:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>      2:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>      3:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>      4:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>      5:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>     ---                                                                              
#>  99996: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
#>  99997: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
#>  99998: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
#>  99999: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
#> 100000: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.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:

combined.fread(the.files = data.path, file.pattern = "csv", recursive = TRUE)
#>             user             item rating
#>           <char>           <char>  <int>
#>      1: a7gzXxfI 0JFCjVx2P1RMzy3h      4
#>      2: a7gzXxfI 0kG80toKp2msfAut      5
#>      3: a7gzXxfI 1Bji5PQIOKXaMGZq      3
#>      4: a7gzXxfI 1fg4sLgEFzAtOqCa      5
#>      5: a7gzXxfI 3k7Wf4yv0RV6vi4K      5
#>     ---                                 
#>  99996: mbPQOZV1 yO8XGg7a9UHbqatL      3
#>  99997: mbPQOZV1 ySHGYLNC7XtywfIZ      2
#>  99998: mbPQOZV1 yeZcUlaFqrZmEB9a      3
#>  99999: mbPQOZV1 yrltwtIEX93JzLYx      5
#> 100000: mbPQOZV1 zfL4EAaUxSMqxtX1      4
#>                                                                                  file
#>                                                                                <char>
#>      1:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>      2:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>      3:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>      4:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>      5:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>     ---                                                                              
#>  99996: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
#>  99997: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
#>  99998: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
#>  99999: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
#> 100000: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv

When return.as = “all” or “code” is specified, the method will output the AWK scripts it generated to perform the counting:

combined.fread(the.files = the.files, return.as = "code")
#> [1] "'/usr/bin/gawk' -f \"BEGIN { FS=\",\"; OFS=\",\" } FNR <= 1 { next } {  print $1,$2,$3,FILENAME }\" '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv'"

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:

combined.fread(the.files = data.path, skip = 0)
#>             user             item rating
#>           <char>           <char>  <int>
#>      1: a7gzXxfI 0JFCjVx2P1RMzy3h      4
#>      2: a7gzXxfI 0kG80toKp2msfAut      5
#>      3: a7gzXxfI 1Bji5PQIOKXaMGZq      3
#>      4: a7gzXxfI 1fg4sLgEFzAtOqCa      5
#>      5: a7gzXxfI 3k7Wf4yv0RV6vi4K      5
#>     ---                                 
#>  99996: mbPQOZV1 yO8XGg7a9UHbqatL      3
#>  99997: mbPQOZV1 ySHGYLNC7XtywfIZ      2
#>  99998: mbPQOZV1 yeZcUlaFqrZmEB9a      3
#>  99999: mbPQOZV1 yrltwtIEX93JzLYx      5
#> 100000: mbPQOZV1 zfL4EAaUxSMqxtX1      4
#>                                                                                  file
#>                                                                                <char>
#>      1:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>      2:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>      3:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>      4:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>      5:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>     ---                                                                              
#>  99996: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
#>  99997: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
#>  99998: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
#>  99999: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
#> 100000: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
# 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:

combined.fread(the.files = data.path, skip = list(skip.data.rows = 4, skip.metadata.rows = 0))
#>             user             item rating
#>           <char>           <char>  <int>
#>      1: a7gzXxfI 0JFCjVx2P1RMzy3h      4
#>      2: a7gzXxfI 0kG80toKp2msfAut      5
#>      3: a7gzXxfI 1Bji5PQIOKXaMGZq      3
#>      4: a7gzXxfI 1fg4sLgEFzAtOqCa      5
#>      5: a7gzXxfI 3k7Wf4yv0RV6vi4K      5
#>     ---                                 
#>  99996: mbPQOZV1 yO8XGg7a9UHbqatL      3
#>  99997: mbPQOZV1 ySHGYLNC7XtywfIZ      2
#>  99998: mbPQOZV1 yeZcUlaFqrZmEB9a      3
#>  99999: mbPQOZV1 yrltwtIEX93JzLYx      5
#> 100000: mbPQOZV1 zfL4EAaUxSMqxtX1      4
#>                                                                                  file
#>                                                                                <char>
#>      1:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>      2:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>      3:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>      4:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>      5:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>     ---                                                                              
#>  99996: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
#>  99997: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
#>  99998: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
#>  99999: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
#> 100000: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv

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:

combined.fread(the.files = data.path, delim = ",")
#>             user             item rating
#>           <char>           <char>  <int>
#>      1: a7gzXxfI 0JFCjVx2P1RMzy3h      4
#>      2: a7gzXxfI 0kG80toKp2msfAut      5
#>      3: a7gzXxfI 1Bji5PQIOKXaMGZq      3
#>      4: a7gzXxfI 1fg4sLgEFzAtOqCa      5
#>      5: a7gzXxfI 3k7Wf4yv0RV6vi4K      5
#>     ---                                 
#>  99996: mbPQOZV1 yO8XGg7a9UHbqatL      3
#>  99997: mbPQOZV1 ySHGYLNC7XtywfIZ      2
#>  99998: mbPQOZV1 yeZcUlaFqrZmEB9a      3
#>  99999: mbPQOZV1 yrltwtIEX93JzLYx      5
#> 100000: mbPQOZV1 zfL4EAaUxSMqxtX1      4
#>                                                                                  file
#>                                                                                <char>
#>      1:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>      2:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>      3:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>      4:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>      5:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>     ---                                                                              
#>  99996: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
#>  99997: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
#>  99998: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
#>  99999: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv
#> 100000: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_999.csv

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:

filtered.fread(the.files = the.files, the.filter = NULL, nrows = 5, include.filename = F)
#>        user             item rating
#>      <char>           <char>  <int>
#> 1: a7gzXxfI 0JFCjVx2P1RMzy3h      4
#> 2: a7gzXxfI 0kG80toKp2msfAut      5
#> 3: a7gzXxfI 1Bji5PQIOKXaMGZq      3
#> 4: a7gzXxfI 1fg4sLgEFzAtOqCa      5
#> 5: a7gzXxfI 3k7Wf4yv0RV6vi4K      5

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

## Write filtering language in R's syntax
filtered.fread(the.files = the.files, the.filter = "rating == 5", nrows = 5)
#>        user             item rating
#>      <char>           <char>  <int>
#> 1: a7gzXxfI 0kG80toKp2msfAut      5
#> 2: a7gzXxfI 1fg4sLgEFzAtOqCa      5
#> 3: a7gzXxfI 3k7Wf4yv0RV6vi4K      5
#> 4: a7gzXxfI 6qI9cBWT76jxm42G      5
#> 5: a7gzXxfI DGsBS6Lsw4m4RzM9      5
#>                                                                           file
#>                                                                         <char>
#> 1: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 2: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 3: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 4: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 5: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv

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

filtered.fread(the.files = the.files, the.filter = "rating >= 3 & item == '1fg4sLgEFzAtOqCa'")
#>        user             item rating
#>      <char>           <char>  <int>
#> 1: a7gzXxfI 1fg4sLgEFzAtOqCa      5
#>                                                                           file
#>                                                                         <char>
#> 1: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv

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).

filtered.fread(the.files = the.files, the.filter = 'rating >= 3 & item == "1fg4sLgEFzAtOqCa"')
#>        user             item rating
#>      <char>           <char>  <int>
#> 1: a7gzXxfI 1fg4sLgEFzAtOqCa      5
#>                                                                           file
#>                                                                         <char>
#> 1: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv

The logical OR operator may also be used:

filtered.fread(the.files = the.files, the.filter = 'rating == 3 | rating == 4', nrows = 5)
#>        user             item rating
#>      <char>           <char>  <int>
#> 1: a7gzXxfI 0JFCjVx2P1RMzy3h      4
#> 2: a7gzXxfI 1Bji5PQIOKXaMGZq      3
#> 3: a7gzXxfI 5jdyCFBIYbLOb5O8      3
#> 4: a7gzXxfI 5pmnpnGWfPgzz9Iy      3
#> 5: a7gzXxfI 6bvosF9URUFrrTOF      4
#>                                                                           file
#>                                                                         <char>
#> 1: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 2: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 3: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 4: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 5: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv

We can also use negations with the logical NOT operator:

filtered.fread(the.files = the.files, the.filter = 'rating != 1 & rating != 2 & rating != 3 & rating != 4', nrows = 5)
#>        user             item rating
#>      <char>           <char>  <int>
#> 1: a7gzXxfI 0kG80toKp2msfAut      5
#> 2: a7gzXxfI 1fg4sLgEFzAtOqCa      5
#> 3: a7gzXxfI 3k7Wf4yv0RV6vi4K      5
#> 4: a7gzXxfI 6qI9cBWT76jxm42G      5
#> 5: a7gzXxfI DGsBS6Lsw4m4RzM9      5
#>                                                                           file
#>                                                                         <char>
#> 1: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 2: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 3: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 4: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 5: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv

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:

filtered.fread(the.files = the.files, the.filter = 'rating >= 3 & item %in% c("1fg4sLgEFzAtOqCa", "6qI9cBWT76jxm42G")')
#>        user             item rating
#>      <char>           <char>  <int>
#> 1: a7gzXxfI 1fg4sLgEFzAtOqCa      5
#> 2: a7gzXxfI 6qI9cBWT76jxm42G      5
#>                                                                           file
#>                                                                         <char>
#> 1: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 2: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv

filtered.fread(the.files = the.files, the.filter = 'rating >= 3 & item %in% c("1fg4sLgEFzAtOqCa", "6qI9cBWT76jxm42G")', return.as = "code")
#> [1] "'/usr/bin/gawk' -f \"BEGIN { FS=\",\"; OFS=\",\" } FNR <= 1 { next } { if($3 >= 3 && ($2==\"1fg4sLgEFzAtOqCa\"||$2==\"6qI9cBWT76jxm42G\")) print $1,$2,$3,FILENAME }\" '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv'"

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:

two.items <- c("1fg4sLgEFzAtOqCa", "6qI9cBWT76jxm42G")
filtered.fread(the.files = the.files, the.filter = 'rating >= 3 & item %in% two.items', return.as = "all")
#> $result
#>        user             item rating
#>      <char>           <char>  <int>
#> 1: a7gzXxfI 1fg4sLgEFzAtOqCa      5
#> 2: a7gzXxfI 6qI9cBWT76jxm42G      5
#>                                                                           file
#>                                                                         <char>
#> 1: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 2: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 
#> $code
#> [1] "'/usr/bin/gawk' -f \"BEGIN { FS=\",\"; OFS=\",\" } FNR <= 1 { next } { if($3 >= 3 && ($2==\"1fg4sLgEFzAtOqCa\"||$2==\"6qI9cBWT76jxm42G\")) print $1,$2,$3,FILENAME }\" '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv'"

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).

filtered.fread(the.files = the.files, the.filter = 'rating %nin% c(1:2, 4)', nrows = 5)
#>        user             item rating
#>      <char>           <char>  <int>
#> 1: a7gzXxfI 0kG80toKp2msfAut      5
#> 2: a7gzXxfI 1Bji5PQIOKXaMGZq      3
#> 3: a7gzXxfI 1fg4sLgEFzAtOqCa      5
#> 4: a7gzXxfI 3k7Wf4yv0RV6vi4K      5
#> 5: a7gzXxfI 5jdyCFBIYbLOb5O8      3
#>                                                                           file
#>                                                                         <char>
#> 1: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 2: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 3: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 4: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 5: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv

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.

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)
#>        user             item rating
#>      <char>           <char>  <int>
#> 1: a7gzXxfI 1fg4sLgEFzAtOqCa      5
#> 2: a7gzXxfI 6qI9cBWT76jxm42G      5
#> 3: FBaBChSF 6qI9cBWT76jxm42G      4
#> 4: zeOSzWxL 6qI9cBWT76jxm42G      5
#> 5: pBaqmBhe 1fg4sLgEFzAtOqCa      4
#>                                                                             file
#>                                                                           <char>
#> 1:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 2:   /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 3: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_109.csv
#> 4: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_114.csv
#> 5: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_155.csv
print(the.output$code[1:2])
#> [1] "'/usr/bin/gawk' -f \"BEGIN { FS=\",\"; OFS=\",\" } FNR <= 1 { next } { if($3 >= 4 && ($2==\"1fg4sLgEFzAtOqCa\"||$2==\"6qI9cBWT76jxm42G\")) print $1,$2,$3,FILENAME }\" '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_100.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1000.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_101.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_102.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_103.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_104.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_105.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_106.csv'" 
#> [2] "'/usr/bin/gawk' -f \"BEGIN { FS=\",\"; OFS=\",\" } FNR <= 1 { next } { if($3 >= 4 && ($2==\"1fg4sLgEFzAtOqCa\"||$2==\"6qI9cBWT76jxm42G\")) print $1,$2,$3,FILENAME }\" '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_107.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_108.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_109.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_11.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_110.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_111.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_112.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_113.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_114.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_115.csv'"

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:

filtered.fread(the.files = the.files, header = FALSE, the.filter = "V3==5", include.filename = F, drop = c("V1", "V2"), return.as = "all")
#> $result
#>        V3
#>     <int>
#>  1:     5
#>  2:     5
#>  3:     5
#>  4:     5
#>  5:     5
#>  6:     5
#>  7:     5
#>  8:     5
#>  9:     5
#> 10:     5
#> 11:     5
#> 12:     5
#> 13:     5
#> 14:     5
#> 15:     5
#> 16:     5
#> 17:     5
#> 18:     5
#> 19:     5
#> 20:     5
#> 21:     5
#> 22:     5
#> 23:     5
#> 24:     5
#> 25:     5
#> 26:     5
#> 27:     5
#> 28:     5
#> 29:     5
#> 30:     5
#> 31:     5
#> 32:     5
#> 33:     5
#> 34:     5
#>        V3
#> 
#> $code
#> [1] "'/usr/bin/gawk' -f \"BEGIN { FS=\",\"; OFS=\",\" } FNR <= 0 { next } { if($3 == 5) print $3 }\" '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv'"

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:

pattern.fread(the.files = the.files, the.patterns = "5n9ziP", return.as = "all")
#> $result
#>        user             item rating
#>      <char>           <char>  <int>
#> 1: QPW5X7ci uNp5n9ziPoSjwab6      1
#>                                                                            file
#>                                                                          <char>
#> 1: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv
#> 
#> $code
#> [1] "awk 'BEGIN { FS=\",\"; OFS=\",\" } FNR <= 1 { next } /5n9ziP/ {print $1,$2,$3,FILENAME}' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv'"

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:

pattern.fread(the.files = the.files, the.patterns = "5n9ziP", tf = FALSE, return.as = "all")
#> $result
#>          user             item rating
#>        <char>           <char>  <int>
#>   1: a7gzXxfI 0JFCjVx2P1RMzy3h      4
#>   2: a7gzXxfI 0kG80toKp2msfAut      5
#>   3: a7gzXxfI 1Bji5PQIOKXaMGZq      3
#>   4: a7gzXxfI 1fg4sLgEFzAtOqCa      5
#>   5: a7gzXxfI 3k7Wf4yv0RV6vi4K      5
#>  ---                                 
#> 195: QPW5X7ci t9ZFC8lCATNXDNmV      4
#> 196: QPW5X7ci uPU8XKJD4wo3Twss      1
#> 197: QPW5X7ci uXjCOKMvr1gPaxTg      4
#> 198: QPW5X7ci vPI43TEe3CMQUM5U      3
#> 199: QPW5X7ci wN8YPrJls7N3vGjC      1
#>                                                                              file
#>                                                                            <char>
#>   1:  /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>   2:  /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>   3:  /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>   4:  /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>   5:  /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#>  ---                                                                             
#> 195: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv
#> 196: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv
#> 197: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv
#> 198: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv
#> 199: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv
#> 
#> $code
#> [1] "awk 'BEGIN { FS=\",\"; OFS=\",\" } FNR <= 1 { next } ! /5n9ziP/ {print $1,$2,$3,FILENAME}' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv'"

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.

pattern.fread(the.files = the.files, the.patterns = c("QPW5X7c", "ziPoS"), connectors = "and", return.as = "result")
#>        user             item rating
#>      <char>           <char>  <int>
#> 1: QPW5X7ci uNp5n9ziPoSjwab6      1
#>                                                                            file
#>                                                                          <char>
#> 1: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv

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

pattern.fread(the.files = the.files, the.patterns = c("ThHYoPWn4IVJ", "ziPoS", "jTXm3t"), connectors = c("or", "or"))
#>        user             item rating
#>      <char>           <char>  <int>
#> 1: a7gzXxfI 4icPV3jTXm3tGREj      1
#> 2: QPW5X7ci 3HThHYoPWn4IVJlm      4
#> 3: QPW5X7ci uNp5n9ziPoSjwab6      1
#>                                                                            file
#>                                                                          <char>
#> 1:  /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 2: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv
#> 3: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv

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

pattern.fread(the.files = the.files, the.patterns = c("W5X7", "ziPoS", "jTXm3t"), connectors = c("and", "or"), return.as = "all")
#> $result
#>        user             item rating
#>      <char>           <char>  <int>
#> 1: a7gzXxfI 4icPV3jTXm3tGREj      1
#> 2: QPW5X7ci uNp5n9ziPoSjwab6      1
#>                                                                            file
#>                                                                          <char>
#> 1:  /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 2: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv
#> 
#> $code
#> [1] "awk 'BEGIN { FS=\",\"; OFS=\",\" } FNR <= 1 { next } /W5X7/  &&  /ziPoS/  ||  /jTXm3t/ {print $1,$2,$3,FILENAME}' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv' '/tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv'"

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

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"))
#>      Class    Sex    Age Survived     N
#>     <char> <char> <char>   <char> <int>
#>  1:    2nd   Male  Child       No     0
#>  2:    3rd   Male  Child       No    35
#>  3:   Crew   Male  Child       No     0
#>  4:    1st Female  Child       No     0
#>  5:    2nd Female  Child       No     0
#>  6:    3rd Female  Child       No    17
#>  7:   Crew Female  Child       No     0
#>  8:    1st Female  Adult       No     4
#>  9:    2nd Female  Adult       No    13
#> 10:    3rd Female  Adult       No    89
#> 11:   Crew Female  Adult       No     3
#> 12:    2nd   Male  Child      Yes    11
#> 13:    3rd   Male  Child      Yes    13
#> 14:   Crew   Male  Child      Yes     0
#> 15:    1st Female  Child      Yes     1
#> 16:    2nd Female  Child      Yes    13
#> 17:    3rd Female  Child      Yes    14
#> 18:   Crew Female  Child      Yes     0
#> 19:    1st Female  Adult      Yes   140
#> 20:    2nd Female  Adult      Yes    80
#> 21:    3rd Female  Adult      Yes    76
#> 22:   Crew Female  Adult      Yes    20
#>      Class    Sex    Age Survived     N
#>                                                                file
#>                                                              <char>
#>  1: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#>  2: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#>  3: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#>  4: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#>  5: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#>  6: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#>  7: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#>  8: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#>  9: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#> 10: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#> 11: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#> 12: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#> 13: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#> 14: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#> 15: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#> 16: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#> 17: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#> 18: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#> 19: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#> 20: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#> 21: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#> 22: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/titanic.csv
#>                                                                file

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:

record.count(the.files = the.files)
#>                                                                            file
#>                                                                          <char>
#> 1:  /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 2: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv
#>    count
#>    <int>
#> 1:   110
#> 2:    90

Similar to filtered.fread, you can supply logical filters to count only the rows that satisfy specific criteria:

record.count(the.files = the.files, the.filter = "rating == 5")
#>                                                                            file
#>                                                                          <char>
#> 1:  /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_1.csv
#> 2: /tmp/RtmpD4az91/Rinst4479f64d3b80/awkreader/extdata/ratings_data/file_10.csv
#>    count
#>    <int>
#> 1:    18
#> 2:    16

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:

aggregated.fread(
  the.files = the.files,
  group.by = "item",
  summarize.with = list(mean = "rating", median = "rating")
)
#>                  item mean_rating median_rating
#>                <char>       <num>         <num>
#>   1: XGmGelJqBc9l12LH           2             2
#>   2: p9Qgir3dXYy7kE2E           3             3
#>   3: Remip8fDYVzDTbRo           1             1
#>   4: TUTEQ0EBBpWWh1SJ           4             4
#>   5: DIyaQEDDWimMwu11           2             2
#>  ---                                           
#> 191: TJqsbFjDPBbOiW5t           1             1
#> 192: ntMGfmzeh1FLMOzB           2             2
#> 193: axRCvecenGMDjnzO           5             5
#> 194: uXjCOKMvr1gPaxTg           4             4
#> 195: hC49fOk2BuAz0GHM           1             1

You can seamlessly apply multiple grouping variables and aggregations in a single pass. AWK will calculate all specified metrics across the provided grouping variables:

aggregated.fread(
 the.files = the.files,
 summarize.with = list(mean = list("rating"), sd = list("rating"), median = list("rating")),
 group.by = c("user", "item")
)
#>          user             item mean_rating sd_rating median_rating
#>        <char>           <char>       <int>    <lgcl>         <int>
#>   1: QPW5X7ci NOBytBvmEF477pVN           4        NA             4
#>   2: a7gzXxfI 5jdyCFBIYbLOb5O8           3        NA             3
#>   3: a7gzXxfI JJUio3cQraJicnFI           2        NA             2
#>   4: QPW5X7ci uPU8XKJD4wo3Twss           1        NA             1
#>   5: a7gzXxfI 6qZePfWoiYPE4ZIT           3        NA             3
#>  ---                                                              
#> 196: a7gzXxfI Xe7ZCoCZyNxlven3           3        NA             3
#> 197: QPW5X7ci d5mi7tVnmIYnZuIs           5        NA             5
#> 198: a7gzXxfI xNqC48ZwwC4xXhrV           5        NA             5
#> 199: a7gzXxfI aEzeufPVFCaRfSN8           2        NA             2
#> 200: a7gzXxfI ZnufF1ZwpzTH3ziQ           1        NA             1

Additionally, you can calculate the sample size (number of observations) for each group:

aggregated.fread(
  the.files = the.files,
  group.by = "item",
  summarize.with = list(mean = "rating", sd = "rating", sample.size = TRUE)
)
#>                  item mean_rating sd_rating     n
#>                <char>       <num>     <num> <int>
#>   1: XGmGelJqBc9l12LH           2        NA     1
#>   2: p9Qgir3dXYy7kE2E           3        NA     1
#>   3: Remip8fDYVzDTbRo           1        NA     1
#>   4: TUTEQ0EBBpWWh1SJ           4        NA     1
#>   5: DIyaQEDDWimMwu11           2        NA     1
#>  ---                                             
#> 191: TJqsbFjDPBbOiW5t           1        NA     1
#> 192: ntMGfmzeh1FLMOzB           2        NA     1
#> 193: axRCvecenGMDjnzO           5        NA     1
#> 194: uXjCOKMvr1gPaxTg           4        NA     1
#> 195: hC49fOk2BuAz0GHM           1        NA     1

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:

aggregated.fread(
  the.files = the.files,
  group.by = "item",
  summarize.with = list(mean = "sqrt(rating)", sd = "log(rating)")
)
#>                  item mean_sqrt_rating sd_log_rating
#>                <char>            <num>         <num>
#>   1: XGmGelJqBc9l12LH          1.41421            NA
#>   2: p9Qgir3dXYy7kE2E          1.73205            NA
#>   3: Remip8fDYVzDTbRo          1.00000            NA
#>   4: TUTEQ0EBBpWWh1SJ          2.00000            NA
#>   5: DIyaQEDDWimMwu11          1.41421            NA
#>  ---                                                
#> 191: TJqsbFjDPBbOiW5t          1.00000            NA
#> 192: ntMGfmzeh1FLMOzB          1.41421            NA
#> 193: axRCvecenGMDjnzO          2.23607            NA
#> 194: uXjCOKMvr1gPaxTg          2.00000            NA
#> 195: hC49fOk2BuAz0GHM          1.00000            NA

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:

aggregated.fread(
  the.files = the.files,
  group.by = "user",
  summarize.with = list(median = "rating"),
  sample.size.median = -1
)
#>        user median_rating
#>      <char>         <num>
#> 1: a7gzXxfI       4.52009
#> 2: QPW5X7ci       3.67984

Using Correlation

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

aggregated.fread(
  the.files = the.files,
  group.by = "user",
  summarize.with = list(cor = "sqrt(item), log(item)")
)
#>        user cor_sqrt_item_log_item
#>      <char>                 <lgcl>
#> 1: a7gzXxfI                     NA
#> 2: QPW5X7ci                     NA

These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.
Health stats visible at Monitor.