Skip to main content

Assemble and prepare the dataset

Joins, pivots and preparing your analysis dataset

Turn register extracts (LPR, LMDB, BEF) into one analysis-ready dataset in R: reduce each to one row per person, reshape where needed, then join them.
Published

November 19, 2026

You arrive at this phase with separate RDS files: your cohort from Phase 10 and the variables you extracted in Phase 11 - outcomes, socioeconomics and comorbidity (plus demographics from Phase 6 if relevant). You leave with one analysis-ready dataset ready for Phase 13 - Analysis.

In short: Reshape each extract to one row per person (with group_by() + slice() or pivot_wider()), then join them on pnr with left_join() into one analysis-ready dataset.

Before you can analyse, you need to:

  1. Shape each extract into one row per person (most registers are in long format with many rows per person)
  2. Assemble them into one analysis-ready dataset with left_join()

That is what this phase is about. The technique for step 1 is either group_by() + slice() or pivot_wider() - the choice depends on what you want out of the register. Step 2 is always left_join().

What is an analysis-ready dataset?

An analysis-ready dataset has one row per person and one column per variable - what we call wide format:

pnr index_date exposure censor_date followup_years outcome age sex nmi
001 2015-03-01 1 2022-07-14 7.4 0 58 2 12
002 2016-11-20 0 2019-11-20 3.0 1 63 1 7

The example uses a cohort study with exposure, outcome and followup_years - the column names every Phase 13 page uses (the full list, including time_days and status_code, is built in Link your extracts). For a prevalence study the column structure will look different - typically without follow-up time, but with a cross-sectional date and covariates measured at that point. The techniques in this phase (joins, pivots, group_by() + slice()) are the same regardless of study design.

The registers you extract from - LPR, LMDB, BEF - are almost always in long format with many rows per person. This phase shows how to reshape and assemble them.

What have you already saved?

Each extraction from Phases 6, 9 and 11 etc. should be saved as its own RDS file - one register, one topic. Example of what you typically have:

File Contents Key columns
full_cohort.rds Cohort with index date pnr, index_date, exposed, sex, foed_dag
extract_hip_fracture.rds First hip fracture diagnosis pnr, event_date (NA = none)
extract_nmi.rds Mortality risk (NMI) at baseline pnr, index_date, nmi
extract_ses.rds Socioeconomic variables pnr, index_date, education_cat, occupation_cat, income_cat

Date of death and emigration have no file of their own here: Link your extracts reads them from DOD and VNDS when it attaches them.

The goal of this phase is to assemble them into one dataset with left_join() - one new column per file. The cohort is the backbone: each extract is joined on via pnr (or pnr + index_date when the value is measured at index) and adds its columns - all cohort members are kept, and people with no match get NA.

Assemble only what you need. It is tempting to keep all columns “just in case”. Do not. Always end with select() and explicitly choose the columns the analysis requires. A dataset with only the necessary columns is far easier to debug and understand.

Two tools for shaping data

The two central operations in this phase are pivots and joins - they sound similar but do fundamentally different things:

Pivots Joins
What it does Reshapes one table - changes the form, not the content Combines two tables - adds columns from another table
Input One table Two tables with a shared key (typically pnr)
Output Same data, new shape Wider table with columns from both
Used for Long → wide format (pivot_wider) or wide → long (pivot_longer) Assemble cohort + outcomes + covariates into one dataset

Rule of thumb: Use pivot when you want to change the shape of one table. Use join when you want to combine two tables.

This phase’s pages

Two core pages take you from separate extracts to one analysis-ready dataset:

  1. Long ↔︎ wide format: get each extract down to one row per person. Understand the difference between long and wide format, and use either group_by() + slice() (one representative value per person) or pivot_wider() (several time points side by side).
  2. Link your extracts: join the extracts on pnr (left_join(), inner_join(), semi_join(), anti_join()), stack two versions of the same register vertically (bind_rows()), validate the join, and calculate follow-up time and event variable. This is where the final dataset is assembled.

The order is deliberate: reshape first, then join. You cannot join predictably until each extract has one row per person.

Two optional add-on pages you only take if your project needs them:

  • Missing data: how to understand and handle missing values, including multiple imputation.
  • Time-varying variables: what to do when a variable changes during follow-up (and when you do not need to do anything).

Next steps

Start by getting each extract down to one row per person:

→ Long ↔︎ wide format

See also

TipFurther information

Further depth in The Epidemiologist R Handbook:

Back to top