The stats19 package supports two engines for reading
and filtering data: readr (the default) and
duckdb. The duckdb engine is particularly
useful for working with large datasets, as it can filter data at the
database level before loading it into R, significantly reducing memory
usage and processing time.
This vignette demonstrates that the two engines produce equivalent results, then shows how to cache the data as Parquet for faster repeated reads.
We will use the 2024 collision data to compare the two engines.
# Read with readr (default)
col_readr = get_stats19(year = 2024, type = "collision", engine = "readr", silent = TRUE)
# Read with duckdb
col_duckdb = get_stats19(year = 2024, type = "collision", engine = "duckdb", silent = TRUE)Both engines should return the same number of records and the same number of fatal collisions.
We can use the waldo package to check for any
differences between the first 10 records.
if (requireNamespace("waldo", quietly = TRUE)) {
waldo::compare(head(col_readr, 10), head(col_duckdb, 10))
}
#> `old$enhanced_severity_collision` is a double vector (NA, 3, NA, 3, 3, ...)
#> `new$enhanced_severity_collision` is a character vector (NA, '3', NA, '3', '3', ...)
#>
#> `old$collision_adjusted_severity_serious` is a double vector (1, 0, 0.11617, 0, 0, ...)
#> `new$collision_adjusted_severity_serious` is a character vector ('1', '0', '0.11617', '0', '0', ...)
#>
#> `old$collision_adjusted_severity_slight` is a double vector (0, 1, 0.88383, 1, 1, ...)
#> `new$collision_adjusted_severity_slight` is a character vector ('0', '1', '0.88383', '1', '1', ...)When working with the full historical dataset (from 1979 onwards),
the duckdb engine is highly recommended. The following code
demonstrates how to use the duckdb engine with a
where clause to filter the data efficiently.
# This chunk is not evaluated because it requires downloading ~1.5GB of data
# and can take several minutes to run with the readr engine.
# Download and read all collisions since 1979, but only keep those with speed_limit = 30
crashes_30mph = get_stats19(year = 1979, type = "collision",
engine = "duckdb",
where = "speed_limit = 30")The duckdb engine can be more than 50 times faster than
the readr engine when performing such filtered reads on
large files.
The duckdb engine reads the .csv files
directly. That is fast, but it still parses text every time you call
get_stats19(). Converting the files once to Parquet makes
repeated reads faster again: Parquet files are compressed, typed and
columnar, so a query only touches the columns it needs.
Parquet files are written to the directory returned by
get_parquet_directory(), which is inside your data
directory by default. Set the STATS19_PARQUET_DIRECTORY
environment variable, or call set_parquet_directory(), to
keep them somewhere else:
stats19_to_parquet() converts the raw files already in
your data directory. There is no need to download them first:
dl_stats19() does that.
library(stats19)
# Convert every collisions CSV in the data directory into one Parquet file
stats19_to_parquet(type = "collision")
# Casualties, vehicles and collisions in one call
stats19_to_parquet(type = "all")Useful arguments:
years converts only the years you ask for, for example
years = 2021:2025.partition_by writes a Hive-partitioned directory
instead of a single file, for example
partition_by = "collision_year". Partitioning is worth it
when you usually query a single year.compression is "zstd" by default.
"snappy" is slightly faster to write and larger on
disk.max_mem_gb caps DuckDB’s memory use (4 GB by default)
and temp_dir sets where it spills to disk, which matters
when converting the 1979-latest files.overwrite = FALSE leaves an existing Parquet file
alone.The function returns the path it wrote, invisibly.
Set engine = "parquet" to read from the cache. If a
Parquet file is missing, or does not cover the years you asked for,
stats19 warns and falls back to the CSV files it has on disk.
To keep the data in DuckDB and only pull the results into R, use
output_format = "duckdb". This returns a lazy
dbplyr table, so dplyr verbs are translated
into SQL and no rows are transferred until you call
collect():
library(dplyr)
tbl = get_stats19(year = 2021:2025, type = "collision", output_format = "duckdb")
tbl |>
filter(collision_severity == "Fatal") |>
count(collision_year) |>
collect()Two things to know about output_format = "duckdb":
dbplyr package, and it is only available
for data that lives in DuckDB, so a year covered by no CSV and no
Parquet file will come back empty.Parquet files are not rebuilt when new CSVs arrive. After downloading a new year, convert again, otherwise queries for that year find a cache that stops short of it and fall back to reading CSV:
dl_stats19(year = 2026, type = "collision")
# rebuild so the cache includes 2026
stats19_to_parquet(type = "collision")The same applies when DfT reissues an existing year: the cached copy keeps the old values until you rebuild it.