README.Rmd

---
output: github_document
---

<!-- README.md is generated from README.Rmd. Please edit that file -->

```{r, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  fig.path = "man/figures/README-",
  out.width = "100%"
)
Sys.setenv(LANG = "en")
options(pillar.sigfig = 5, width = 100)
```

# `dataverifyr` - A Lightweight, Flexible, and Fast Data Validation Package that Can Handle All Sizes of Data


<!-- badges: start -->

[![](https://www.r-pkg.org/badges/version/dataverifyr)](https://www.r-pkg.org/pkg/dataverifyr) [![CRAN RStudio mirror downloads](https://cranlogs.r-pkg.org/badges/dataverifyr)](https://www.r-pkg.org/pkg/dataverifyr) [![R-CMD-check](https://github.com/DavZim/dataverifyr/actions/workflows/R-CMD-check.yaml/badge.svg)](https://github.com/DavZim/dataverifyr/actions/workflows/R-CMD-check.yaml)

<!-- badges: end -->

The goal of `dataverifyr` is to allow a wide variety of flexible data validation checks (verifications).
That means, you can specify a set of rules (R expressions) and compare any arbitrary dataset against it.

The package is built in such a way, that it adapts to your type of data and choice of data package (data.frame, data.table, tibble, arrow, or SQL connection) and chooses the right data backend automatically, this is especially handy when large or complicated datasets are involved.
That way, you can concentrate on writing the rules and making sure that your data is valid rather than spending time writing boilerplate code.

The package is lightweight as all the heavy dependencies are Suggests-only, that means if you want to use `data.table` for the task, you don't need to install the other packages (`arrow`, `DBI`, etc) unless you explicitly tell R to install all suggested packages as well when installing the package.

The backend for your analysis is automatically chosen based on the type of input dataset as well as the available packages.
By using the underlying technologies and handing over all evaluation of code to the backend, this package can deal with all sizes of data the backends can deal with.


## Installation

You can install the development version of `dataverifyr` like so:

``` r
# development version
# devtools::install_github("DavZim/dataverifyr")

# CRAN release
install.packages("dataverifyr")
```

## Example

This is a basic example which shows you how to

1.  create a rule set manually, consisting of R expressions
2.  check if a dataset matches all given rules
3.  save and load the rules to a yaml-file for better maintainability

Note that each rule is an R expression that is evaluated within the dataset.
Our first rule, for example, states that we believe all values of the `mpg` variable are in the range 10 to 30 (exclusive).
At the moment rules work in a window/vectorized approach only, that means that a rule like this will work `mpg > 10 * wt`, whereas a rule like this `sum(mpg) > 0` will not work as it aggregates values.

```{r example, message=FALSE}
library(dataverifyr)

# define a rule set within our R code; alternatively in a yaml file
rules <- ruleset(
  rule(mpg > 10 & mpg < 30), # mpg goes up to 34
  rule(cyl %in% c(4, 8)), # missing 6 cyl
  rule(vs %in% c(0, 1), allow_na = TRUE)
)

# print the rules
rules

# check if the data matches our rules
res <- check_data(mtcars, rules)
res
```

As we can see, our dataset `mtcars` does not conform to all of our rules.
We have four fails (fail=rule is not met) for the first rule `mpg > 10 & mpg < 30` (there are `mpg` values up to 33.9) and seven fails for the second rule `cyl %in% c(4, 8)` (there are `cyl` values of 6), while the third rule `vs %in% c(0, 1)` is always met.

To see which values do not meet our expectations, use the `filter_fails()` function
```{r filterfails}
filter_fails(res, mtcars, per_rule = TRUE)
```

We can also visualize the results using the `plot_res()` function.

```{r plotres}
plot_res(res)
```

Note that you can also save and load a ruleset to and from a `yaml` file
```{r yaml_rules}
write_rules(rules, "example_rules.yaml")
r2 <- read_rules("example_rules.yaml")
identical(rules, r2)
```
The resulting `example_rules.yaml` looks like this
```{r ex_yaml, results="asis", echo=FALSE}
cat(paste(c("```yaml", readLines("example_rules.yaml"), "```"), collapse = "\n"))
```

One helpful use case is to use this functionality to assert that your data has the right values in a custom read function like so:

```{r assert, eval=FALSE}
read_custom <- function(file, rules) {
  data <- read.csv(file) # or however you read in your data
  # if the check_data detects a fail: the read_custom function will stop
  check_data(data, rules, xname = file,
             stop_on_fail = TRUE, stop_on_warn = TRUE, stop_on_error = TRUE)
  # ...
  data
}
# nothing happens when the data matches the rules
data <- read_custom("correct_data.csv", rules)

# an error is thrown when warnings or errors are found
data <- read_custom("wrong_data.csv", rules)
#> Error in check_data(data, rules, stop_on_fail = TRUE, stop_on_error = TRUE, stop_on_warn = TRUE) : 
#>   In dataset 'wrong_data.csv' found 2 rule fails, 1 warnings, 1 errors
```


## Backends

At the moment the following backends are supported.
Note that they are automatically chosen based on data type and package availability.
Eg, when the dataset is a `dplyr::tbl()` connected to an `SQLite` database, the package will automatically choose `RSQLite`/`DBI`/`dbplyr` for the task.
To see which backend `dataverifyr` would use for a task, you can use `detect_backend(data)`.

```{r backends, echo=FALSE, results="asis"}
# setup the table of backends in R... base markdown is not nice with this formatting...
# create a data.frame needed for the table
d <- function(b, d, c, cc, status = "✔️") data.frame(b, status, d, c, cc)
# formats something as a code-block
code <- function(x) paste0("```R\n", x, "\n```")

r <- do.call(rbind, list(
  d(
    "`base-R`", "`data.frame`",
    code("data <- data.frame(x = 1:10)\ncheck_data(data, rs)"),
    "When `data.table` or `dplyr` are available, they are used for faster speeds."
  ),
  d(
    "[`dplyr`](https://dplyr.tidyverse.org/)", "`tibble`",
    code("library(dplyr)\ndata <- tibble(x = 1:10)\ncheck_data(data, rs)"),
    ""
  ),
  d(
    "[`data.table`](https://r-datatable.com)", "`data.table`",
    code("library(data.table)\ndata <- data.table(x = 1:10)\ncheck_data(data, rs)"),
    ""
  ),
  d(
    "[`arrow`](https://arrow.apache.org/docs/r/)", "`Table`, `ArrowTabular`, `ArrowObject`",
    code("library(arrow)\ndata <- arrow_table(x = 1:10)\n# Alternatively:\ndata <- read_parquet(\n  file,\n  as_data_frame = FALSE\n)\ncheck_data(data, rs)"),
    ""
  ),
  d(
    "[`arrow`](https://arrow.apache.org/docs/r/)", "`FileSystemDataset`, `Dataset`, `ArrowObject`",
    code("library(arrow)\ndata <- open_dataset(dir)\ncheck_data(data, rs)"),
    "Especially handy for large datasets"
  ),
  d(
    "[`RSQLite`](https://rsqlite.r-dbi.org/), [`DBI`](https://dbi.r-dbi.org/), and [`dbplyr`](https://dbplyr.tidyverse.org/)", "`tbl_SQLiteConnection`, `tbl_dbi`, `tbl_sql`, `tbl_lazy`, `tbl`",
    code('library(DBI)\ncon <- dbConnect(RSQLite::SQLite())\n# dbWriteTable(con, tablename, data)\ntbl <- dplyr::tbl(con, tablename)\ncheck_data(tbl, rs)\n\ndbDisconnect(con)'),
    "Note that missing values are converted to `0` when using sqlite by default ([c.f. this SO answer](https://stackoverflow.com/a/57746647/3048453))"
  ),
  d(
    "[`duckdb`](https://duckdb.org/docs/api/r.html), [`DBI`](https://dbi.r-dbi.org/), and [`dbplyr`](https://dbplyr.tidyverse.org/)", "`tbl_duckdb_connection`, `tbl_dbi`, `tbl_sql`, `tbl_lazy`, `tbl`",
    code('library(DBI)\ncon <- dbConnect(duckdb::duckdb())\n# dbWriteTable(con, tablename, data)\ntbl <- dplyr::tbl(con, tablename)\ncheck_data(tbl, rs)\n\ndbDisconnect(con, shutdown = TRUE)'),
    ""
  ),
  
  d(
    "[`RPostgres`](https://rpostgres.r-dbi.org/), [`DBI`](https://dbi.r-dbi.org/), and [`dbplyr`](https://dbplyr.tidyverse.org/)", "`tbl_PqConnection`, `tbl_dbi`, `tbl_sql`, `tbl_lazy`, `tbl`",
    code('library(DBI)\ncon <- dbConnect(\n  RPostgres::Postgres(), \n  ...\n)\n# dbWriteTable(con, tablename, data)\ntbl <- dplyr::tbl(con, tablename)\ncheck_data(tbl, rs)\n\ndbDisconnect(con)'),
"Not tested, but should work out-of-the-box using [`DBI`](https://dbi.r-dbi.org/)", status = "❓"
  )
))
r <- setNames(r, c("Backend / Library", "Status", "Data Type", "Example Code", "Comment"))
dataverifyr:::simple_table(r, align = "lclll")
```

Note that the `rs` object in the example code above refers to a `ruleset()`.
Larger complete examples can be found below.


## Larger Example using the `arrow` backend

For a more involved example, using a different backend, let's say we have a larger dataset of taxi trips from NY (see also the official [source of the data](https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page)), that we have saved as a local arrow dataset (using parquet as a data format), where we want to make sure that some variables are in-line with our expectations/rules.

### 1 Download and Prepare Data

First we prepare the data by downloading it and writing the dataset to `.parquet` files.
This needs to be done only once and is shown for reproducibility reasons only, the actual `dataverifyr` code is shown below the next block

```{r taxi1, eval=requireNamespace("arrow", quietly=TRUE), message=FALSE, warning=FALSE}
library(arrow)
url <- "https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2018-01.parquet"
file <- "yellow_tripdata_2018-01.parquet"
if (!file.exists(file)) download.file(url, file, method = "curl")
file.size(file) / 1e6 # in MB

# quick check of the filesize
d <- read_parquet(file)
dim(d)
names(d)

# write the dataset to disk
write_dataset(d, "nyc-taxi-data")
```


### 2 Create Rules in `yaml`

Next, we can create some rules that we will use to check our data.
As we saw earlier, we can create the rules in R using the `rule()` and `ruleset()` functions, there is however, the (in my opinion) preferred option to separate the code from the rules by writing the rules in a separate yaml file and reading them into R.

First we display the hand-written contents of the `nyc_data_rules.yaml` file.

```{r nycrules, echo=FALSE, results="asis"}
rs <- ruleset(
  rule(passenger_count >= 0 & passenger_count <= 10),
  rule(trip_distance >= 0 & trip_distance <= 1000),
  rule(payment_type %in% c(0, 1, 2, 3, 4))
)
write_rules(rs, "nyc_data_rules.yaml")
cat(paste(c("```yaml", readLines("nyc_data_rules.yaml"), "```"),
          collapse = "\n"))
```

Then, we can load, display, and finally check the rules against the data

```{r taxi2, eval=requireNamespace("arrow", quietly=TRUE)}
rules <- read_rules("nyc_data_rules.yaml")
rules
```

### 3 Verify that the Data matches the given Rules

Now we can check if the data follows our rules or if we have unexpected data points:

```{r taxi3, eval=requireNamespace("arrow", quietly=TRUE)}
# open the dataset 
ds <- open_dataset("nyc-taxi-data/")

# perform the data validation check
res <- check_data(ds, rules)
res

plot_res(res)
```

Using the power of `arrow`, we were able to scan 8+mln observations for three rules in about 1.5 seconds (YMMV).
As we can see from the results, there is one unexpected value, lets quickly investigate using the `filter_fails()` function, which filters a dataset for the failed rule matches

```{r taxi4, eval=requireNamespace("arrow", quietly=TRUE)}
res |>
  filter_fails(ds) |> 
  # only select a couple of variables for brevity
  dplyr::select(tpep_pickup_datetime, tpep_dropoff_datetime, trip_distance)
```

As we can see, this is probably a data error (a trip distance of 190k miles in 1 minute seems - ehm stellar...).

## Using a `DBI` Backend

If you have a `SQLite` or `duckdb` database, you can use the package like this

```{r duckdb, eval=requireNamespace("duckdb", quietly = TRUE) & requireNamespace("dplyr", quietly = TRUE) & requireNamespace("DBI", quietly = TRUE), message=FALSE, warning=FALSE}
library(DBI)
library(dplyr)

# connect to a duckdb database
con <- dbConnect(duckdb::duckdb("duckdb-database.duckdb"))
# for demo purposes write the data once
dbWriteTable(con, "mtcars", mtcars)

# create a tbl connection, which can be used in the checks
tbl <- tbl(con, "mtcars")

# create rules
rules <- ruleset(
  rule(mpg > 10 & mpg < 30),
  rule(cyl %in% c(4, 8)),
  rule(vs %in% c(0, 1), allow_na = TRUE)
)

# check rules
res <- check_data(tbl, rules)
res

filter_fails(res, tbl, per_rule = TRUE)

# lastly disconnect from the database again
dbDisconnect(con, shutdown = TRUE)
```


# Alternative Data Validation R Libraries

If this library is not what you are looking for, the following might be good alternatives to validate your data:

- [`pointblank`](https://rstudio.github.io/pointblank/)
- [`validate`](https://github.com/data-cleaning/validate)
- [`data.validator`](https://github.com/Appsilon/data.validator)

```{r cleanup, echo=FALSE}
unlink("example_rules.yaml")
unlink("nyc-taxi-data", recursive = TRUE)
unlink("nyc_data_rules.yaml")
unlink("duckdb-database.duckdb")
```