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:

  1. identify what one row of a dataset represents, and state the analytical unit your research question needs (§1–2);
  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);
  3. decide which steps of a workflow to compute once and save, and which to rerun (§4);
  4. state the relationship you expect from a join, and name the checks that would detect a violation (§2, §5);
  5. distinguish a missing record from a true zero, and list what you would need to know before recoding NA to 0 (§6);
  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

  1. Why workflows need different design in the SRS
  2. Units, identifiers and join relationships
  3. Working with less data
  4. Managing computational cost
  5. Combining linked data safely
  6. Missingness, structural zeroes and validation
  7. Reproducible workflows
  8. Reflect on your own workflow
  9. Check your understanding
  10. Quick reference
  11. 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 (see code/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: NA is 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

  1. What does one row represent? (in the source, and in every intermediate)
  2. What should the analytical unit be?
  3. Can rows, columns or granularity be reduced earlier, ideally in the database?
  4. Is expensive computation being repeated that could be done once and saved?
  5. What join relationship do I expect, and is it checked?
  6. What does absence of a record mean? A true zero, not observed, or lost upstream?
  7. 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


Setting up

You only need this if you want to run the code. You do not need SRS access.

  1. R ≥ 4.3 and, ideally, RStudio. Open teaching.Rproj: all paths are relative to the project folder.
  2. Packages:
    install.packages(c("data.table", "dplyr", "readr", "tidyr", "arrow",
                       "DBI", "dbplyr", "duckdb"))
    
  3. 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.

  4. Memory. The January examples need ~3 GB of free RAM. With less, read data/fallback/fhvhv_2024-01_sample_250k.csv instead; your counts will differ from these notes.
  5. Run the scripts in order: 01_workflow_data_table.R (or 02_workflow_dplyr.R), then 03_full_year_arrow.R and 04_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.csv with tools::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.

Back to top

Lesson text CC BY 4.0; code MIT. Please cite if you use or adapt these materials. Data: NYC Taxi & Limousine Commission.

This site uses Just the Docs, a documentation theme for Jekyll.