erDiagram
full_cohort ||--o| extract_hip_fracture : "pnr"
full_cohort ||--o| extract_ses : "pnr + index_date"
full_cohort ||--o| extract_nmi : "pnr + index_date"
full_cohort {
string pnr PK
date index_date PK
int exposed
string sex
date foed_dag
}
extract_hip_fracture {
string pnr FK
date event_date
}
extract_ses {
string pnr FK
date index_date FK
string education_cat
string occupation_cat
string income_cat
}
extract_nmi {
string pnr FK
date index_date FK
int nmi
}
Link your extracts
Joins, bind_rows() and calculating follow-up time - assemble your extracts into one dataset
Your extracts from Phase 6, Extract from LPR and Phase 11 are stored as separate RDS files. Once each extract is down to one row per person (Long ↔︎ wide format), you join them on pnr into one analysis-ready dataset. Finally, you calculate follow-up time and the event variable.
Paths and variable names in the examples are generic - replace them with your own.
First get each extract down to one row per person. left_join() only behaves predictably when both tables have one row per pnr. Many register extracts have multiple rows per person (long format). Reduce them to one row per person with group_by() + slice() or pivot_wider() - explained in Long ↔︎ wide format. This page assumes your extracts are already in that shape.
Joins - link two tables
In register-based research you almost always work with data from two or more tables that need to be linked. All joins in dplyr take two tables and one key column they share (e.g. pnr).
When you join your finished person-level extracts onto the cohort, the key is usually pnr. Inside a register it is other keys: joining contact and diagnosis in LPR, for instance, happens on recnum or dw_ek_kontakt, not pnr (see the overview of LPR join keys). A value that is measured relative to the index date (socioeconomic position, a comorbidity score) is joined on pnr + index_date, so it still lands on the right row when the same person is in the cohort with more than one index date. The ER diagram below shows both - extracts joined onto the cohort:
The key is marked PK (primary key) in the cohort and FK (foreign key) in each extract. The symbol ||--o| is standard ER notation (“crow’s foot”): || = exactly one, o| = zero or one. So the line reads: each person in the cohort matches zero or one row in the extract.
This describes the shape you must ensure before the join - not something left_join() checks or fixes. If an extract has several rows per pnr, left_join() happily proceeds and duplicates the cohort rows (row explosion). That is why the callout above tells you to get each extract down to one row per person first, and why check 1 in Validate your join afterwards (did the row count grow?).
The joins you will use
library(dplyr) # left_join, inner_join, semi_join, anti_join
# left_join: keep ALL rows from x, add columns from y
# Persons in x without a match in y get NA.
result <- cohort %>%
left_join(outcome, by = "pnr") # all cohort members retained; outcome = NA if no event
# inner_join: keep only rows with a match in BOTH tables
result <- lpr_adm %>%
inner_join(lpr_diag, by = "recnum") # only contacts with at least one diagnosis
# right_join: keep all rows from y (rarely used)
# full_join: keep all rows from both (rarely used)Filter joins - change rows only, not columns
# bef is a lazy register and cohort is a table in R: tibble(pnr = ...) with
# copy = TRUE sends the cohort's pnr into the database, so the two can be compared there.
# semi_join: keep rows in x THAT HAVE a match in y
bef_in_cohort <- bef %>%
semi_join(tibble(pnr = cohort$pnr), by = "pnr", copy = TRUE) # only BEF rows for cohort members
# anti_join: keep rows in x THAT DO NOT HAVE a match in y
bef_not_in_cohort <- bef %>%
anti_join(tibble(pnr = cohort$pnr), by = "pnr", copy = TRUE) # only BEF rows for non-cohort members
# also used for diagnostics - see "Validate your join" belowOverview: all joins in one table
| Function | Keeps rows from | Used for |
|---|---|---|
left_join(x, y) |
All from x | Add outcomes/covariates to cohort - NA if no match |
inner_join(x, y) |
Only matches in both | Join contacts and diagnoses - only want rows with both |
right_join(x, y) |
All from y | Rarely |
full_join(x, y) |
All from both | Rarely |
semi_join(x, y) |
Only x with match in y | Filter a register to only cohort members |
anti_join(x, y) |
Only x without match in y | Find everyone in BEF NOT in the cohort |
Join with multiple keys
Multiple keys only become relevant when at least one of the tables has several rows per person - otherwise pnr alone is enough. With multiple keys all the listed columns must agree before two rows count as a match (pnr and year, not pnr or year).
The typical case is panel data: registers with one row per person per year. BEF, AKM and so on have a pnr row for every calendar year. Join two of them on pnr alone and each of the person’s rows is paired with all of the person’s rows in the other table: with 5 years in BEF and 5 in AKM you get every combination, 5 × 5 = 25 rows instead of 5. Add the year as an extra key and those 25 are filtered down to the 5 where the year also matches - 2018 against 2018:
# Join on pnr AND year: a row matches only when BOTH columns are equal
bef_akm <- bef %>%
left_join(akm, by = c("pnr", "year")) # 2018 BEF joins only to 2018 AKMIf instead you join two extracts that each have one row per person (e.g. finished person-level extracts onto the cohort), pnr alone is enough - there is no extra dimension to keep track of.
Do not assume “one row per person per year” - check it. BEF on DARTER does contain duplicate (pnr, year) rows, and adding the year as a key does not save you from those: a person with two 2018 rows still doubles. Count first, and reduce to one row per person per year if you find any.
# Are there duplicates? n > 1 means the same person appears twice in one year.
bef %>%
count(pnr, year) %>%
filter(n > 1) %>%
count() %>%
collect()
bef <- bef %>% distinct(pnr, year, .keep_all = TRUE) # keep one row per person-yearleft_join() will not warn you. It just returns more rows than you started with, which is why check 1 in Validate your join is to compare nrow() before and after.
Validate your join
Joins rarely fail with an error message - but they can produce silently wrong results. Always check these three things after a left_join():
# Before join: what is nrow?
nrow(cohort) # e.g. 4,823
# Join:
cohort2 <- cohort %>% left_join(outcome, by = "pnr")
# Check 1: did the row count increase? Duplicated keys in outcome give extra rows.
nrow(cohort2) # should still be 4,823
# Check 2: how many got a match?
sum(!is.na(cohort2$event_date)) # number with outcome
sum(is.na(cohort2$event_date)) # number without - expected censored?
# Check 3: who did NOT match? (diagnostic - not necessarily an error)
missing <- anti_join(cohort, outcome, by = "pnr") # pnr's not found in outcome
nrow(missing) # 0 = all matched; > 0 = investigateanti_join() as diagnostics anti_join(cohort, outcome, by = "pnr") returns the cohort members that are not found in the outcome table. Use it for “who in my cohort has no death date record?” (all alive - expected) or “who is missing from register X?” (unexpected - investigate).
Stack tables vertically - bind_rows()
bind_rows() and left_join() solve two fundamentally different problems:
bind_rows() |
left_join() |
|
|---|---|---|
| What it does | Stacks tables vertically - adds rows | Combines tables horizontally - adds columns |
| Requires | That the columns have the same names | That the tables share one common key column |
| Result | More rows, same number of columns | Same number of rows, more columns |
| Matches on key? | No - all rows from both tables | Yes - match on pnr or other key |
| Typical use | LPR2 + LPR3 → one combined register | Link outcome or covariate to cohort table |
bind_rows() is not a join. It does not look at pnr and does not match. It simply places table 2’s rows below table 1’s rows - like pasting two Excel sheets together vertically.
# bind_rows: LPR2 and LPR3 have the same columns (pnr, date_contact, icd3)
# → stack them vertically into one combined diagnosis register
lpr2_dx # 45,000 rows - diagnoses up to March 2019
lpr3_dx # 32,000 rows - diagnoses from March 2019 onwards
all_dx <- bind_rows(lpr2_dx, lpr3_dx) # 77,000 rows - both periods combinedColumns missing in one table (e.g. a column that only exists in LPR3) are automatically filled with NA for rows from the other table.
Counting diagnoses across this combined all_dx? Expect a jump around 2019-2020 that isn’t a real change in disease occurrence - see why.
When do you use which? bind_rows() - when you have two versions of the same register (LPR2 + LPR3, exposed + comparators) and want to merge them into one. left_join() - when you want to attach a new variable (outcome, date of death, age) to your cohort table.
Practical examples: join date of death and emigration to the cohort
read_register() requires fastreg set up with the path to your registers - see Parquet and fastreg if you did not convert them from SAS yourself.
Date of death (DOD)
library(fastreg) # read_register()
library(dplyr) # %>%, semi_join(), left_join(), collect()
library(tibble) # tibble()
# Attach date of death (DOD) to the cohort -------------------------------------
# Cohort data from Phase 10. Keep only the columns that identify a row and the
# ones Phase 10 owns. The file also holds working columns from the matching
# (death_date, first_outcome_date ...); attach those fresh here, so no column
# ends up twice (death_date.x / death_date.y).
cohort <- readRDS("path/to/full_cohort.rds") %>%
select(pnr, index_date, exposed, sex, foed_dag,
any_of(c("match_id", "crossover_date"))) # any_of(): kept if the column exists
# Death dates from DOD:
dod <- read_register("dod") %>% # without fastreg: open_dataset("E:/workdata/[projectnumber]/cleaned-data/parquet-registers/dod/")
rename_with(tolower) %>% # standardise column names
semi_join(tibble(pnr = cohort$pnr), by = "pnr", copy = TRUE) %>% # only the cohort's pnr's
select(pnr, death_date = doddato) %>% # rename doddato to death_date
collect() # fetch into R
# Join: all cohort members retained; the living get death_date = NA
cohort <- cohort %>%
left_join(dod, by = "pnr") # attach death date - NA = still aliveEmigration date (VNDS)
Emigration censors just like death - the person leaves the study on the day they emigrate. VNDS contains one row per migration event. Take the first exit after the index date, not simply the first ever: a person may have left and come back, and an exit before index censors nothing. Whether the person was resident at index at all is decided in Phase 10.
# Attach emigration date (VNDS) to the cohort ----------------------------------
# Emigration dates from VNDS:
vnds <- read_register("vnds") %>% # without fastreg: open_dataset("E:/workdata/[projectnumber]/cleaned-data/parquet-registers/vnds/")
rename_with(tolower) %>% # standardise column names
semi_join(tibble(pnr = cohort$pnr), by = "pnr", copy = TRUE) %>% # only the cohort's pnr's
filter(indud_kode == "U") %>% # "U" = udvandring/emigration (VNDS holds both in- and out-migration)
select(pnr, haend_dato) %>%
collect()
emigration <- cohort %>% # the cohort has pnr + index_date
distinct(pnr, index_date) %>% # one row per person and index date
left_join(vnds, by = "pnr", relationship = "many-to-many") %>% # several index dates x several exits per person is expected here
filter(haend_dato > index_date) %>% # only exits AFTER index - those are the ones that censor
group_by(pnr, index_date) %>% # per person AND index date: the answer depends on the index date
arrange(haend_dato) %>% # oldest date first
slice(1) %>% # first exit after that index date
ungroup() %>% # release grouping
select(pnr, index_date, emigration_date = haend_dato) # rename haend_dato to emigration_date
# Join: all cohort members retained; non-emigrants get emigration_date = NA
cohort <- cohort %>%
left_join(emigration, by = c("pnr", "index_date")) # NA = no exit after indexThe date of death is one date per person, so pnr alone is the right key. The emigration date depends on the index date, so it is keyed on pnr and index_date: a person who was drawn as a comparison person on two different dates then gets the right exit for each of them.
Derived variables: create new columns
Once the extracts are joined, you almost always need to compute new variables from the columns you now have. That is mutate(): it adds a column calculated from existing ones.
library(dplyr) # mutate, if_else, case_when
cohort <- cohort %>%
mutate(
# Age at index: days between two dates / 365.25 (a year is 365.25 days, leap years)
age_at_index = as.numeric(index_date - foed_dag) / 365.25,
# BMI from height (m) and weight (kg)
bmi = weight_kg / (height_m^2),
# Categorise a continuous variable into groups
age_group = case_when(
age_at_index < 50 ~ "<50",
age_at_index < 65 ~ "50-64",
TRUE ~ "65+" # everything else (here: 65 and over)
)
)Two habits worth keeping:
- Compute a variable once and reuse it, so age at index is the same number in the matching and in Table 1.
if_else()for yes/no,case_when()for several categories - and use the same type in every branch (1L/0L, or text in all of them). Both are in the Function guide.
The censoring date and the event indicator are derived variables too, but they need dates from several extracts at once, so they come next.
Calculate follow-up time and event variable
Before you can analyse, each cohort member needs a censoring date (when follow-up ends) and an event variable (did they experience the outcome?).
The censoring date is the earliest of: event date, date of death, emigration date, the date a comparison person becomes exposed themselves (crossover) and end of study period. death_date and emigration_date were joined on in the practical examples above; the outcome date is joined on first here.
# Calculate follow-up time and event variable ----------------------------------
# The outcome extract from Phase 9/11: pnr + event_date, one row per person
hip_fracture <- readRDS("path/to/extract_hip_fracture.rds") %>%
distinct(pnr, event_date)
cohort <- cohort %>% left_join(hip_fracture, by = "pnr") # NA = no event
study_end <- as.Date("2024-12-31") # replace with your actual study end date (the same date as STUDY_END if you matched a comparison cohort in Phase 10)
# format: "yyyy-mm-dd" (ISO 8601 - R's standard)
cohort <- cohort %>%
mutate(
# Censoring date = the earliest of all possible stopping reasons
censor_date = pmin(
event_date,
death_date,
emigration_date,
crossover_date, # comparison person becomes exposed (NA for everyone else)
study_end,
na.rm = TRUE
),
# Follow-up time in days (Cox) and in years (rates, Poisson)
time_days = as.numeric(censor_date - index_date),
followup_years = time_days / 365.25,
# Outcome variable: 1 = outcome occurred before censoring, 0 = censored
outcome = as.integer(!is.na(event_date) & event_date <= censor_date)
) %>%
filter(time_days > 0) # drop zero/negative follow-up (see below)pmin() compares vectors position by position and returns the smallest value per person - it is the vectorised version of min(). na.rm = TRUE ensures that a missing death date (= alive) does not make the censoring date NA.
Crossover. A comparison person who later becomes exposed stops being a comparison person on that day, so follow-up is censored at crossover_date (made in Phase 10, NA for the exposed and for comparison persons who never become exposed). Without that line, time after their own exposure is counted as unexposed time. If your cohort has no crossover_date column (no comparison group, or one that can never become exposed), leave the line out. The alternative to censoring is to let the person switch group at crossover and contribute exposed time from then on. That needs a dataset with several rows per person, see Time-varying exposure.
Zero or negative follow-up. Someone whose outcome, death or emigration is recorded on the index date itself gets time_days = 0 and was never at risk; an event before index (a prevalent case that slipped through) gives a negative time. Both break the analysis (log(0) in Poisson, meaningless Cox time), so the filter() above drops them - count how many, and if same-day events are common in your data, decide deliberately whether they belong in the study.
Competing risks? If a competing event (typically death) must be handled separately (see Time-to-event), then instead of a binary outcome (0/1) build a status with three values - what happened first:
# Competing risks: a status with three values ----------------------------------
cohort <- cohort %>%
mutate(
# status_code: which date "won" the pmin() above?
status_code = case_when(
!is.na(event_date) & event_date <= censor_date ~ 1, # outcome first
!is.na(death_date) & death_date <= censor_date ~ 2, # death first (competing risk)
TRUE ~ 0 # otherwise censored (emigration/study end)
)
)Assemble the final analysis dataset
By now your cohort is built in Phase 10: a table with pnr and index_date (and exposed/case if you have a comparison group - renamed to exposure in the final select()) - one row per person, unless your comparison group was matched with replacement, in which case the same pnr can carry several rows, one per index_date it was matched on. The recipe below works for both: everything measured relative to the index date is joined on pnr + index_date, see comparison cohort’s callout on it. Your outcomes, covariates and censoring variables are extracted as separate RDS files (Phase 9, Phase 11). This last step joins them onto the cohort with left_join(), calculates follow-up time, and keeps only the columns the analysis needs.
End with select() - keep only the columns the analysis requires.
cohort_final <- cohort %>%
select(
pnr,
index_date,
exposure = exposed, # renamed: the analysis pages call it `exposure`
censor_date,
time_days,
followup_years,
outcome,
status_code, # from the competing-risks block above
age, # age, nmi and the three _cat columns are added in the
sex, # complete recipe below (steps 4 and 5)
nmi,
occupation_cat,
education_cat,
income_cat
)
saveRDS(cohort_final, "path/to/analysis.rds") # save the final datasetComplete recipe - from cohort to analysis-ready dataset
# Complete recipe: cohort -> analysis-ready dataset ----------------------------
library(fastreg) # read_register()
library(dplyr) # left_join, semi_join, mutate, group_by, slice
library(tibble) # tibble()
# 0. Load your cohort ----------------------------------------------------------
# One row per person and index date. Keep only the columns that identify a row
# and the ones Phase 10 owns; everything else is attached fresh below, so no
# column ends up twice (death_date.x / death_date.y).
cohort <- readRDS("path/to/full_cohort.rds") %>%
select(pnr, index_date, exposed, sex, foed_dag,
any_of(c("match_id", "crossover_date"))) # any_of(): kept if the column exists
n_start <- nrow(cohort) # to check below that no join adds or loses rows
# THE JOIN KEY. A date that belongs to the person (outcome, death) is joined on
# pnr. Anything measured RELATIVE TO THE INDEX DATE (emigration after index, SES,
# NMI) is joined on pnr + index_date: with a comparison group matched with
# replacement the same pnr has several rows, one per index date, and a join on
# pnr alone would then silently multiply the rows.
# 1. Prevalent cases are already excluded --------------------------------------
# People who already had the outcome before index are not at risk of getting it.
# They were removed in Phase 10 (build-study-population.qmd, Step 3: the
# `prevalent` object and filter(!pnr %in% prevalent)), so full_cohort.rds holds
# only people at risk at index. The filter in step 4 catches any that slipped through.
# 2. Link the outcome ----------------------------------------------------------
# One row per person with the FIRST event date after index, or no row at all.
# left_join keeps everyone, so people with no event get NA - which is exactly
# what "no event" means here, and what the outcome indicator in step 4 reads.
hip_fracture <- readRDS("path/to/extract_hip_fracture.rds") %>% # pnr + event_date
distinct(pnr, event_date) # one row per person
cohort <- cohort %>% left_join(hip_fracture, by = "pnr")
# 3. Link censoring: death date and emigration ---------------------------------
deaths <- read_register("dod") %>% # without fastreg: open_dataset("E:/workdata/[projectnumber]/cleaned-data/parquet-registers/dod/")
rename_with(tolower) %>%
semi_join(tibble(pnr = cohort$pnr), by = "pnr", copy = TRUE) %>%
select(pnr, death_date = doddato) %>% collect()
vnds_data <- read_register("vnds") %>% # without fastreg: open_dataset("E:/workdata/[projectnumber]/cleaned-data/parquet-registers/vnds/")
rename_with(tolower) %>%
semi_join(tibble(pnr = cohort$pnr), by = "pnr", copy = TRUE) %>%
filter(indud_kode == "U") %>%
select(pnr, haend_dato) %>% collect()
# Emigration needs the first exit AFTER index, not the first ever: someone who
# lived abroad as a child and moved back is not censored at that childhood exit.
# So attach index_date first, filter, and only then take the earliest remaining,
# per person AND index date.
vnds_data <- cohort %>%
distinct(pnr, index_date) %>%
left_join(vnds_data, by = "pnr", relationship = "many-to-many") %>% # several index dates x several exits per person is expected
filter(haend_dato > index_date) %>%
group_by(pnr, index_date) %>% arrange(haend_dato) %>% slice(1) %>% ungroup() %>%
select(pnr, index_date, emigration_date = haend_dato)
cohort <- cohort %>%
left_join(deaths, by = "pnr") %>%
left_join(vnds_data, by = c("pnr", "index_date"))
stopifnot(nrow(cohort) == n_start) # a join that multiplied rows stops here
# 4. Follow-up time and the outcome indicator ----------------------------------
# censor_date = the FIRST of the ways follow-up can end. pmin() with
# na.rm = TRUE takes the earliest date that actually exists, so someone who never
# died, never emigrated and never had the event simply ends at study_end.
# crossover_date = the day a comparison person becomes exposed themselves (from
# Phase 10; NA for everyone else). Leave that line out if your cohort has no such column.
# outcome = 1 only if the outcome happened at or before that date: a diagnosis
# recorded after the person emigrated does not count. status_code adds death as
# a competing event (0 = censored, 1 = outcome, 2 = death; outcome wins a tie).
study_end <- as.Date("2024-12-31") # replace with your actual study end date (the last date your data cover; the same date as STUDY_END in Phase 10)
cohort <- cohort %>%
mutate(
censor_date = pmin(event_date, death_date, emigration_date, crossover_date,
study_end, na.rm = TRUE),
time_days = as.numeric(censor_date - index_date),
followup_years = time_days / 365.25, # 365.25 = leap years
outcome = as.integer(!is.na(event_date) & event_date <= censor_date),
status_code = case_when(
outcome == 1 ~ 1,
!is.na(death_date) & death_date <= censor_date ~ 2,
TRUE ~ 0
),
age = as.numeric(index_date - foed_dag) / 365.25 # age at index, for every row
) %>%
filter(time_days > 0) # no follow-up = never at risk (event/death/emigration on index day)
# 5. Link covariates (SES, comorbidity) ----------------------------------------
# sex and age are already on the cohort (step 0 and step 4). The extracts below
# are measured relative to the index date, so they are joined on pnr + index_date.
# distinct() makes sure each extract has ONE row per key: the same person can be
# drawn into two matched sets on the same date, and two identical rows on each
# side would otherwise become four.
ses_data <- readRDS("path/to/extract_ses.rds") %>% # education_cat, occupation_cat, income_cat
distinct(pnr, index_date, .keep_all = TRUE)
nmi_data <- readRDS("path/to/extract_nmi.rds") %>% # NMI score - see nmi.qmd
distinct(pnr, index_date, .keep_all = TRUE)
n_before <- nrow(cohort)
cohort <- cohort %>%
left_join(ses_data, by = c("pnr", "index_date")) %>%
left_join(nmi_data, by = c("pnr", "index_date"))
stopifnot(nrow(cohort) == n_before) # same number of rows as before the joins
# 6. Keep ONLY the columns the analysis requires -------------------------------
cohort_final <- cohort %>%
select(pnr, index_date, exposure = exposed, censor_date, time_days, followup_years,
outcome, status_code, age, sex, nmi, occupation_cat, education_cat, income_cat)
saveRDS(cohort_final, "path/to/analysis.rds") # save the final dataset
nrow(cohort_final) # n_start minus the rows dropped for zero follow-up in step 4
names(cohort_final) # did every join actually add the columns you expected?Next steps
You now have one analysis-ready dataset with one row per person (one row per person and index date, if your comparison group was matched with replacement). The next step is the analysis:
See also
- Long ↔︎ wide format: get each extract down to one row per person
- Functions: overview: all functions for transforming data
- Extract from LPR: practical example of long register data and inner_join
Further depth (in English):
- Joining data in The Epidemiologist R Handbook.
- Joins in R for Data Science: keys, mutating vs. filtering joins and what happens when a key has duplicates.