Lesson notes
These notes follow the live session section by section, so you can revisit it or work through it on your own. They also include material there wasn’t time for live.
You don’t need to run anything to follow these notes. Every step shows the output to expect. If you want to run the code, see Setting up at the end.
What you’ll be able to do
By the end of the session (or of these notes) you should be able to:
- identify what one row of a dataset represents, and state the analytical unit your research question needs (§1–2);
- explain why reducing rows, columns and granularity early, ideally inside the database, lowers memory use and run time, and recognise code that pulls a whole table into R (§3);
- decide which steps of a workflow to compute once and save, and which to rerun (§4);
- state the relationship you expect from a join, and name the checks that would detect a violation (§2, §5);
- distinguish a missing record from a true zero, and list what you would need to know before recoding
NAto0(§6); - identify assumptions in your own workflow that could be made explicit as comments, functions or checks (§7–8).
The session introduces and demonstrates these. Applying them to your own work is the next step, and the self-check and §8 are there to help.
Contents
- Why workflows need different design in the SRS
- Units, identifiers and join relationships
- Working with less data
- Managing computational cost
- Combining linked data safely
- Missingness, structural zeroes and validation
- Reproducible workflows
- Reflect on your own workflow
- Check your understanding
- Quick reference
- Setting up
The worked example. NYC High Volume For-Hire Vehicle trips (Uber, Lyft and similar), 2024.
Research question: how does trip activity vary across NYC pickup zones and over time? Source unit: one row = one trip. Analytical unit: one row = pickup zone × day.
The full code is in code/01_workflow_data_table.R (the same analysis in dplyr: code/02_workflow_dplyr.R). Each section below names the matching step.
Pause and predict. Questions marked ❓ were asked in the session. Try to answer each one before reading on: committing to a prediction is what makes the result stick.
1. Why workflows need different design in the SRS
In the SRS you typically work on a shared virtual machine with a fixed memory ceiling, read from network storage, use a fixed set of package versions without internet access, and have every output checked for disclosure. Memory you waste and computation you repeat slow you down, and can slow down other researchers on the same machine.
❓ What do you expect one row of the trip file to represent?
trips <- fread("data/teaching_csv/fhvhv_tripdata_2024-01_selected.csv")
dim(trips) # 19,663,930 rows x 7 columns
format(object.size(trips), units = "GB") # ~0.8 GB
One row is one trip. That is one month; the year has ~240 million.
❓ Does the research question need one row per trip? No: it needs one row per zone per day.
In the SRS: admissions, attendances, exclusions and benefit spells arrive as events, while analyses usually need person-years, school-years or area-months.
Key point: the structure in which data arrive is not necessarily the structure in which they should be analysed.
2. Units, identifiers and join relationships
Script step 4. The zone lookup has 265 rows, one per taxi zone.
❓ Which column should be unique? Will these checks return any rows?
zones[, .N, by = LocationID][N > 1] # 0 rows: LocationID is a key
zones[, .N, by = Zone][N > 1] # 2 rows: zone NAMES repeat
“Corona” is two zones (IDs 56 and 57), and “Governor’s Island/Ellis Island/Liberty Island” is three (103–105). A name that looks like an identifier is not one. In administrative data the same is true of school names, GP practice names, local-authority names and postcodes. Join on the identifier (URN, pseudonymised person ID).
❓ Does PULocationID uniquely identify a trip? No. Many trips share a zone.
Every join has an expected cardinality: one-to-one, one-to-many, many-to-one or many-to-many. Trips → zones is many-to-one. Decide which you expect before joining, because it tells you what to check afterwards.
Key point: an identifier can identify an entity without identifying a row, and a label that looks like an identifier may be neither.
3. Working with less data
Script steps 1 and 3; code/04_database_dbplyr.R.
The only data.table grammar used in this lesson is
DT[ i, j, by ]
which rows? what to compute? within which groups?
plus :=, which adds a column by reference, without copying the table:
trips[, pickup_date := as.IDate(pickup_datetime)]
The refrain for the whole session: reduce rows, reduce columns, reduce granularity, as early as is analytically legitimate.
Let the database do the work
In the SRS your source data are usually in SQL Server tables. The most important efficiency decision is where the filtering and aggregation run. With DBI + dbplyr you write dplyr code, dbplyr translates it into SQL, the database runs it, and only the result comes back to R.
The session used DuckDB, a database that runs inside R, to stand in for SQL Server. Only the connection line differs:
library(DBI); library(dplyr)
con <- dbConnect(duckdb::duckdb())
dbExecute(con, "CREATE VIEW hvfhv AS SELECT * FROM read_parquet('data/raw_parquet/*.parquet')")
trips_db <- tbl(con, "hvfhv")
trips_db # "?? x 24": all of 2024 (~240M rows), nothing read yet
daily_zone_db <- trips_db |>
filter(pickup_datetime < "2024-02-01") |>
mutate(pickup_date = as.Date(pickup_datetime)) |>
count(PULocationID, pickup_date)
show_query(daily_zone_db) # the SQL the database will run
collect(daily_zone_db) # 8,006 rows, in under a second
❓ Where does the computation happen? In the database. Nothing runs until collect().
Avoid tbl(con, ...) |> collect() |> filter(...). It copies the entire table into R before reducing it, and it is the most common, most expensive mistake when querying SRS databases from R.
In the SRS, the connection looks something like this. Ask SRS support for the exact driver, server and database names.
con <- dbConnect(odbc::odbc(), Driver = "SQL Server", Server = "<server>",
Database = "<database>", Trusted_Connection = "Yes")
events <- tbl(con, I("dbo.<table>"))
To see the T-SQL your code will send to SQL Server without connecting to one, run section 2 of code/04_database_dbplyr.R (simulate_mssql()).
Key point: move and process only what the analysis requires, and push reductions to where the data live.
4. Managing computational cost
Script step 3.
❓ Will aggregating to zone-day produce more rows, fewer, or the same?
daily_zone <- trips[, .(n_trips = .N, mean_base_fare = mean(base_passenger_fare)),
by = .(PULocationID, pickup_date)]
19,663,930 rows (~0.9 GB) become 8,006 rows (~0.2 MB). Save that intermediate, and the event-level table no longer needs to exist:
saveRDS(daily_zone, "derived/zone_day_2024_01.rds")
rm(trips)
- Expensive and stable steps (extraction, cleaning, aggregation): compute once and save.
- Cheap and changing steps (models, tables, plots): rerun freely.
Calling gc() is not the optimisation; designing the workflow so that the huge object stops being needed is.
What the benchmarks showed
Measured before the session on one 14-core Windows laptop. Use these for orders of magnitude, not as package rankings.
| Comparison | Time | Memory | |
|---|---|---|---|
| A | Read the same 1.2 GB CSV: readr::read_csv() vs data.table::fread() | 102 s vs 1.2 s | similar |
| B | Same in-memory aggregation: dplyr vs data.table | 2.2 s vs 2.5 s | 2.6 GB vs 0.45 GB allocated |
| C | data.table, January from CSV (20M rows) | 2.2 s | 2.1 GB peak |
| C | Arrow, whole year from Parquet (240M rows) | 17 s | 1.3 GB peak |
| C | DuckDB, whole year from Parquet (240M rows) | 2.5 s | 1.1 GB peak |
What to take from them:
- A. About 90% of readr’s time here is spent parsing the two datetime columns. readr also guessed the integer zone IDs as doubles, so its table was 1.3 GB against fread’s 0.8 GB. Declare column types when you read large files.
- B. The speed was the same, but the memory was not. On a shared VM, memory is usually the binding constraint.
- C. Changing where the work happens (a database engine over an efficient file format) handled twelve times the data in about the time, and about half the memory, of reading one month into R. That matters more than making in-memory code slightly faster.
object.size() and bench::mark(memory = TRUE) only see memory allocated by R. Arrow, DuckDB and database drivers use memory outside R, which is why section C measures the whole process.
5. Combining linked data safely
Script step 4.
❓ Before joining, should this return any rows?
daily_zone[, .N, by = .(PULocationID, pickup_date)][N > 1] # 0 rows
(This check cannot fail straight after a by aggregation. It matters when the table comes from a file, a colleague or someone else’s SQL.)
State the expectation (many zone-days → one zone), join, and check that the expectation held:
n_before <- nrow(daily_zone)
analysis <- merge(daily_zone, zones, by.x = "PULocationID", by.y = "LocationID", all.x = TRUE)
nrow(analysis) == n_before # TRUE
analysis[is.na(Zone), unique(PULocationID)] # no unmatched IDs
A wrong join that succeeds
Suppose a second source is keyed by zone name:
analysis_bad <- merge(analysis, zones[, .(Zone, service_zone)], by = "Zone")
nrow(analysis_bad) # 8,072, not 8,006
analysis_bad[, sum(n_trips)] - analysis[, sum(n_trips)] # 61,440 trips that never happened
There is no error and no warning. ❓ Would R know this was wrong? No. Make your expectation executable:
- dplyr:
left_join(x, y, by = ..., relationship = "many-to-one")stops with an error if it is violated (seecode/02_workflow_dplyr.R). - data.table / base R:
stopifnot(nrow(after) == nrow(before)), and check the total of a count column before and after.
Key point: successful execution is not evidence of a correct join.
6. Missingness, structural zeroes and validation
Script step 5.
❓ If a zone-day doesn’t appear in daily_zone, have we shown that zero trips happened?
Build the intended observation frame (every zone × every day) and see what is missing:
zone_days <- CJ(PULocationID = zones$LocationID,
pickup_date = seq(as.IDate("2024-01-01"), as.IDate("2024-01-31"), by = "day"))
analysis_complete <- merge(zone_days, daily_zone, by = c("PULocationID", "pickup_date"), all.x = TRUE)
analysis_complete[is.na(n_trips), .N, by = PULocationID]
209 of the 8,215 zone-days have no record:
| ID | Zone | Days with no record | What might it mean? |
|---|---|---|---|
| 1 | Newark Airport | 30 | Out-of-state airport; pickups rare |
| 2 | Jamaica Bay | 17 | Mostly water and parkland |
| 103–105 | Governor’s / Ellis / Liberty Island | 31, 31, 29 | No public road access: plausibly true zeroes |
| 110 | Great Kills Park | 14 | Park |
| 199 | Rikers Island | 26 | Jail: restricted access |
| 264 | “Unknown” | 31 | Not a place. It shouldn’t be in the frame. |
❓ Should we run analysis_complete[is.na(n_trips), n_trips := 0L]? Only with reasons you can write down:
- Was the unit genuinely under observation (frame coverage)?
- Are the source submissions complete for the period?
- Does the absence of a record mean the absence of an event?
- Could linkage or processing upstream have dropped records?
Key point:
NAis a computational state; its meaning comes from the data-generating and observation process.
Validation (more than there was time for live)
Check events before aggregating, while the detail still exists (script step 2). Or run the checks in the database, where the source always remains:
trips_db |>
filter(pickup_datetime < "2024-02-01") |>
summarise(negative_fare = sum(as.integer(base_passenger_fare < 0), na.rm = TRUE),
zero_miles = sum(as.integer(trip_miles == 0), na.rm = TRUE),
n = n()) |>
collect()
# 191 negative fares, 3,286 zero-mile trips, 19,663,930 trips
❓ Do we delete the 191 negative fares? No. Detection prompts investigation (refunds? adjustments? errors?). Then decide, document and count what you excluded.
Conservation checks catch silent losses. After aggregating, sum(n_trips) should equal the number of trips. After a left join onto a frame, the total should be unchanged. Both are in the script as stopifnot() lines.
SQL and R treat missing values differently. SQL aggregates (AVG, SUM, MIN, MAX) ignore NULLs, while R’s mean(c(1, NA)) is NA. The same-looking dplyr code can therefore mean different things in R and in the database; dbplyr warns about this once per session. Relatedly, as.Date() in dbplyr becomes TRY_CAST, which turns unconvertible values into NULL silently.
Aggregation creates small cells. 66 zone-days have fewer than 10 trips. In the SRS, every output goes through statistical disclosure control, so build suppression or rounding thresholds into the pipeline rather than discovering them at output checking.
7. Reproducible workflows
Comments should record decisions and reasons, not narrate syntax:
# Weak:
# Calculate daily summary
# Useful:
# Collapse to zone-day before linkage: downstream models use zone-days,
# and the trip table will not fit in memory alongside the linked data.
Turn repeated decisions into named functions, so the decision lives in one place:
read_trips <- function(path) {
fread(path, select = c("pickup_datetime", "PULocationID", "trip_miles",
"trip_time", "base_passenger_fare"))
}
Structure the project as a pipeline: numbered scripts that each read the previous stage’s saved output. This repository is a small example:
00 download & reduce → saved intermediate → link & validate → analysis
And make sure R never restores a saved workspace. In RStudio: Tools → Global Options → General → Workspace: untick “Restore .RData” and set “Save workspace” to Never. If your analysis only works because of objects left over in .RData, it isn’t reproducible.
❓ Which assumptions that began in your head are now visible or testable in the code? The analytical unit, the time window, the columns kept, the aggregation rule, join keys and cardinality, the observation frame, and the QA expectations.
Key point: reproducibility is more than code that runs twice. The important assumptions are explicit and, where possible, executable.
8. Reflect on your own workflow
Pick one step in your own SRS or TRE work that is slow, memory-hungry, hard to rerun, prone to duplicates, hard to validate, or reliant on an assumption about missing records. Work through the seven questions below. What would you change first?
Check your understanding
Answer each question before opening the answer. They’re designed for new situations, not this dataset.
1. A dataset has one row per GP consultation. Your question is about how many children in each local authority were seen at least once per school year. What is the source unit, and what is the analytical unit?
Answer
Source unit: a consultation. Analytical unit: local authority × school year (via child × school year, to count children rather than consultations). Several consultations per child must collapse before counting, or children with many visits are counted many times.
2. Which of these does the reduction inside the database?
# A
tbl(con, "episodes") |> collect() |> filter(year >= 2015) |> count(person_id)
# B
tbl(con, "episodes") |> filter(year >= 2015) |> count(person_id) |> collect()
Answer
B. In A, collect() copies every row of the table into R before filtering. In B, the filter and count run in the database, and only one row per person comes back.
3. Save once, or rerun each time? (a) linking ten years of school census to hospital records; (b) a sensitivity analysis with a different covariate set; (c) recoding ethnicity into five groups you are still deciding on.
Answer
(a) Save: expensive and stable. (b) Rerun: cheap and changing. (c) Rerun: it’s still changing. Save the stable linked data before the recode, so you can change the recode cheaply.
4. You join a pupil-year table (one row per pupil per year) to a school lookup on the school’s URN. What relationship do you expect, and what two checks would you write?
Answer
Many-to-one (many pupil-years to one school). Check that the URN is unique in the lookup before joining, and that the row count is unchanged after (or use relationship = "many-to-one" in dplyr). Checking for unmatched URNs is a useful third.
5. After linking, some local-authority × month cells have no emergency admissions recorded. Name two things you would want to know before treating them as zero.
Answer
Any two of: whether every provider submitted data for that month (completeness); whether the local authority was in scope for that period (boundary or coverage changes); whether records could have failed to link and been dropped; whether “no record” really means “no event” for this data source.
6. Your script contains df <- df[df$age < 25, ] with no comment. What assumption is hidden, and how would you make it visible?
Answer
Why the cut-off is 25, and whether it applies at a particular date, are both hidden. Name it (max_age <- 25), explain the reason in a comment, and consider a check, such as counting and reporting how many rows were excluded.
Quick reference
Seven questions for any step in a workflow
- What does one row represent? (in the source, and in every intermediate)
- What should the analytical unit be?
- Can rows, columns or granularity be reduced earlier, ideally in the database?
- Is expensive computation being repeated that could be done once and saved?
- What join relationship do I expect, and is it checked?
- What does absence of a record mean? A true zero, not observed, or lost upstream?
- What check would reveal that this step failed?
Patterns side by side
| Task | data.table | dplyr | SQL (via dbplyr) |
|---|---|---|---|
| Keep rows | DT[year == 2024] | filter(df, year == 2024) | WHERE year = 2024 |
| Keep columns | fread(f, select = cols) | select(df, all_of(cols)) | SELECT col1, col2 |
| Add a column | DT[, d := as.IDate(t)] | mutate(df, d = as.Date(t)) | CAST(t AS DATE) AS d |
| Aggregate | DT[, .(n = .N), by = .(id, d)] | count(df, id, d) | GROUP BY id, d |
| Is a key unique? | DT[, .N, by = id][N > 1] | count(df, id) \|> filter(n > 1) | GROUP BY id HAVING COUNT(*) > 1 |
| Guarded join | merge(...) + stopifnot(nrow(...) == n_before) | left_join(..., relationship = "many-to-one") | compare counts before and after |
| Complete frame | CJ(id = ids, d = dates) | tidyr::crossing(id = ids, d = dates) | build, then join |
| Save an intermediate | saveRDS(x, "derived/x.rds") | saveRDS(x, "derived/x.rds") | compute(q) (table in the database) |
Further reading
vignette("datatable-intro", package = "data.table")- dbplyr: https://dbplyr.tidyverse.org/
- Arrow datasets in R: https://arrow.apache.org/docs/r/articles/dataset.html
- Join cardinality checks:
?dplyr::left_join(relationship,unmatched) - ONS Secure Research Service: https://www.ons.gov.uk/aboutus/whatwedo/statistics/requestingstatistics/secureresearchservice
Setting up
You only need this if you want to run the code. You do not need SRS access.
- R ≥ 4.3 and, ideally, RStudio. Open
teaching.Rproj: all paths are relative to the project folder. - Packages:
install.packages(c("data.table", "dplyr", "readr", "tidyr", "arrow", "DBI", "dbplyr", "duckdb")) - Data. About 5.4 GB to download and ~7 GB on disk, from the NYC Taxi & Limousine Commission:
source("code/00_download_and_prepare.R")For a smaller download (January only, ~0.5 GB), run
Sys.setenv(HVFHV_N_MONTHS = "1")first. Everything still works; the “whole year” scripts just use January. - Memory. The January examples need ~3 GB of free RAM. With less, read
data/fallback/fhvhv_2024-01_sample_250k.csvinstead; your counts will differ from these notes. - Run the scripts in order:
01_workflow_data_table.R(or02_workflow_dplyr.R), then03_full_year_arrow.Rand04_database_dbplyr.R. On a recent laptop they take roughly 5 s, 1 min, 20 s and 5 s respectively. The dplyr version is slower because parsing datetimes from CSV is slow, which is itself a lesson.
Things to know:
- Your counts should match these notes exactly if the TLC hasn’t republished the files. Compare the checksums in
data/MANIFEST.csvwithtools::md5sum(). Timings will differ on your machine. - Timestamps are New York local time, stored without a time zone. After the CSV step R labels them “UTC”; the clock times and dates are correct, so don’t convert them.
- Before using any of this in the SRS, check which packages are available in your environment, and follow your project’s output-checking rules. Nothing here needs to leave or enter the SRS: the techniques transfer, not the data.