### Dump example data

Source: https://docs.opensafely.org/ehrql/reference/cli

Exports tutorial example data to the local directory.

```bash
ehrql dump-example-data [--help] [--dst-dir DST_DIR]
```

--------------------------------

### Install macOS Applications

Source: https://docs.opensafely.org/install-python

Install Docker, GitHub Desktop, and Visual Studio Code using Homebrew casks.

```bash
brew install --cask docker github visual-studio-code
```

--------------------------------

### Install OpenSAFELY CLI

Source: https://docs.opensafely.org/install-macos

Install the OpenSAFELY CLI package using pip.

```bash
pip install opensafely
```

--------------------------------

### Full example of parameter usage in ehrQL

Source: https://docs.opensafely.org/ehrql/reference/language

A complete example demonstrating parameter retrieval and usage within a dataset definition.

```python
from ehrql import create_dataset, get_parameter
from ehrql.tables.core import patients


max_age = get_parameter(name="max_age", type=int, default=20)
sex = get_parameter(name="sex", default="male")

dataset = create_dataset()
age = patients.age_on("2025-01-01")

dataset.define_population((age < max_age) & (patients.sex==sex))
```

--------------------------------

### Install Homebrew

Source: https://docs.opensafely.org/install-python

Install Homebrew on macOS, which also installs Xcode Command Line Tools.

```bash
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
```

--------------------------------

### Example codelists.txt content

Source: https://docs.opensafely.org/codelist-project

An example of a populated codelists.txt file containing multiple codelist references.

```text
opensafely/aplastic-anaemia/58ac196d
opensafely/asplenia/3ce9e642
opensafely/current-asthma/2020-05-06
primis-covid19-vacc-uptake/bmi_stage/v1.2
```

--------------------------------

### Install OpenSAFELY CLI with pipx

Source: https://docs.opensafely.org/install-linux

Use pipx to install the OpenSAFELY CLI into an isolated virtual environment.

```bash
pipx install opensafely
```

--------------------------------

### Install pyenv via Homebrew

Source: https://docs.opensafely.org/install-python

Use Homebrew to install the pyenv version manager.

```bash
brew install pyenv
```

--------------------------------

### View command output

Source: https://docs.opensafely.org/getting-started/how-to/use-github-codespaces-in-your-project

Example of a successful execution output from the OpenSAFELY CLI.

```text
=> generate_dataset
Completed successfully
```

--------------------------------

### OpenSAFELY CLI Help Output

Source: https://docs.opensafely.org/install-macos

Example output showing the available commands and usage for the OpenSAFELY CLI.

```text
usage: opensafely [-h] [--version] COMMAND ...

optional arguments:
  -h, --help  show this help message and exit
  --version   show program's version number and exit

available commands:

  COMMAND
    help      Show this help message and exit
    run       Run project.yaml actions locally
    codelists
              Commands for interacting with https://www.opencodelists.org/
```

--------------------------------

### Configure output in project.yaml

Source: https://docs.opensafely.org/ehrql/explanation/output-formats

Example configuration for an extraction action in a project.yaml file.

```yaml
version: "5.0"

actions:
  extract_data:
    run: ehrql:v1 generate-dataset "./dataset_definition.py" --output "outputs/data_extract.arrow"
    outputs:
      highly_sensitive:
        population: outputs/data_extract.arrow
```

--------------------------------

### Verify opensafely CLI installation

Source: https://docs.opensafely.org/getting-started/tutorial/create-a-github-codespace

Run the opensafely command in the terminal to confirm the software is installed and view available commands.

```bash
$ opensafely
usage: opensafely [-h] [--version] COMMAND ...

optional arguments:
  -h, --help  show this help message and exit
  --version   show program's version number and exit

available commands:

  COMMAND
    help      Show this help message and exit
    run       Run project.yaml actions locally
    codelists
          Commands for interacting with https://codelists.opensafely.org/
    pull      Command for updating the docker images used to run OpenSAFELY studies locally
    upgrade   Upgrade the opensafely cli tool.
```

--------------------------------

### ehrql dump-example-data

Source: https://docs.opensafely.org/ehrql/reference/cli

Dumps example data for the ehrQL tutorial to the current directory.

```APIDOC
## ehrql dump-example-data

### Description
Dump example data for the ehrQL tutorial to the current directory.

### Parameters
#### Options
- **-h, --help** - Optional - Show this help message and exit
- **-d, --dst-dir** (string) - Optional - Destination folder ('example-data' by default)
```

--------------------------------

### Fetch example dummy data

Source: https://docs.opensafely.org/ehrql/explanation/vscode-extension

Command to download core ehrQL dummy tables for local development.

```bash
opensafely exec ehrql:v1 dump-example-data
```

--------------------------------

### Project configuration for event-level outputs

Source: https://docs.opensafely.org/ehrql/explanation/event-level-data

Example project.yaml configuration for generating and outputting event-level datasets.

```yaml
generate_dataset:
  run: ehrql:v1 generate-dataset analysis/dataset_definition.py
    --output output/cohort/:arrow
  outputs:
    highly_sensitive:
      cohort: output/cohort/*.arrow
```

--------------------------------

### Boolean parameter command line examples

Source: https://docs.opensafely.org/ehrql/how-to/parameterise-ehrql

Examples of command line inputs that resolve to False for a boolean parameter.

```text
... -- --has_diagnosis 0
... -- --has_diagnosis False
... -- --has_diagnosis false

```

--------------------------------

### Example output dataset CSV

Source: https://docs.opensafely.org/ehrql/tutorials/dummy-data/provide-dummy-tables

Sample output format for the generated dataset after processing.

```csv
patient_id,age,sex,first_event_date
3,49,male,1988-12-06
4,74,unknown,1967-06-01
5,23,unknown,1998-09-03
6,64,unknown,
7,40,unknown,
8,68,unknown,
9,49,unknown,1974-10-17
10,50,unknown,1973-04-05
...
```

--------------------------------

### Install OpenSAFELY CLI with a specific Python version

Source: https://docs.opensafely.org/install-linux

Use a specific Python interpreter path when installing the CLI, useful if managing multiple versions via pyenv.

```bash
pipx install opensafely --python ~/.pyenv/shims/python3.10
```

--------------------------------

### Verify Python Installation

Source: https://docs.opensafely.org/install-python

Check the installed Python version in the Anaconda Prompt.

```bash
python --version
```

--------------------------------

### Defining the Run Command in action.yaml

Source: https://docs.opensafely.org/actions-reusable

Example of specifying the runtime image and entry point script within the action.yaml file.

```yaml
run: python:v2 python action/cli.py
```

--------------------------------

### Install Python via pyenv

Source: https://docs.opensafely.org/install-python

Download and install the latest 3.10 version of Python using pyenv.

```bash
pyenv install 3.10:latest
```

--------------------------------

### Example CLI execution output

Source: https://docs.opensafely.org/ehrql/explanation/running-ehrql

Standard log output displayed when running an ehrQL dataset generation command.

```text
2023-04-19 08:53:41 [info     ] Compiling dataset definition from dataset_definition.py [ehrql.main]
2023-04-19 08:53:41 [info     ] Generating dummy dataset       [ehrql.main]
2023-04-19 08:53:41 [info     ] Reading CSV data from example-data [ehrql.main]
2023-04-19 08:53:41 [info     ] Building dataset and writing results [ehrql.main]
```

--------------------------------

### Pipeline execution logs

Source: https://docs.opensafely.org/ehrql/explanation/running-ehrql

Example output displayed in the terminal during the execution of the OpenSAFELY pipeline.

```text
$ opensafely run run_all

Running actions: generate_dataset, summarise_dataset

jobrunner.run loop started
generate_dataset: Preparing your code and workspace files
...
summarise_dataset: Extracting output file: output/summary.txt
summarise_dataset: Finished recording results
summarise_dataset: Completed successfully
summarise_dataset: Cleaning up container and volume

=> generate_dataset
   Completed successfully

   log file: metadata/generate_dataset.log
   outputs:
     output/dataset.csv.gz  - highly_sensitive

=> summarise_dataset
   Completed successfully

   log file: metadata/summarise_dataset.log
   outputs:
     output/summary.txt  - moderately_sensitive
```

--------------------------------

### Install local R packages in v2

Source: https://docs.opensafely.org/requesting-libraries

Use this command to install your own local packages for post-processing released outputs within the r:v2 environment.

```R
install.packages("PACKAGENAME")
```

--------------------------------

### Execute notebook in project.yaml

Source: https://docs.opensafely.org/opensafely-cli

Example configuration for running a Jupyter notebook using nbconvert within the OpenSAFELY environment.

```yaml
run: python:v2 jupyter nbconvert --execute analysis/notebook.ipynb
```

--------------------------------

### Example addresses table content

Source: https://docs.opensafely.org/ehrql/tutorials/dummy-data/provide-dummy-tables

Sample content of the generated addresses.csv file.

```csv
patient_id,msoa_code,start_date
1,E02000001,1997-11-17
1,E02000001,1991-04-16
1,,1997-05-05
1,E02000001,1961-07-14
2,E02000001,1976-09-19
2,,1979-10-25
2,,1964-07-01
2,E02000001,1972-05-21
2,E02000001,1960-09-18
2,,1956-04-27
2,E02000001,1991-12-09
2,E02000001,1970-06-09
3,E02000001,1993-07-08
3,E02000001,2015-03-14
3,E02000001,1995-11-28
...
```

--------------------------------

### Define a dummy dataset

Source: https://docs.opensafely.org/ehrql/tutorials/dummy-data/provide-dummy-tables

Example of a dataset definition file configured with dummy data for testing purposes.

```python
from ehrql import create_dataset
from ehrql.tables.core import patients
from ehrql.tables.tpp import addresses

dataset = create_dataset()
dataset.configure_dummy_data(population_size=50)
age = patients.age_on("2024-01-01")

possible_msoas = ["E02000001", "E02000002", "E02000003", "E02000004"]

address = (
    addresses
    .where(addresses.msoa_code.is_in(possible_msoas))
    .sort_by(addresses.end_date)
    .last_for_patient()
)

no_address = ~addresses.exists_for_patient()

dataset.age = age
dataset.msoa = address.msoa_code

dataset.define_population(
    patients.exists_for_patient()
    & (address.exists_for_patient() | no_address)
)
```

--------------------------------

### Pass command-line parameters to dataset definition

Source: https://docs.opensafely.org/ehrql/reference/language

Examples of passing user-defined parameters via the command line.

```bash
generate-dataset path/to/dataset_definition.py -- --max_age 70 --sex female
```

```bash
generate-dataset path/to/dataset_definition.py -- --sex male female
```

```bash
generate-dataset path/to/dataset_definition.py -- --sex male --sex female
```

--------------------------------

### Define population in ehrQL

Source: https://docs.opensafely.org/ehrql/how-to/errors

Examples showing how to define a population and fix errors related to missing population definitions.

```python
from ehrql import create_dataset
from ehrql.tables.core import patients

dataset = create_dataset()
dataset.age = patients.age_on("2023-01-01")
```

```bash
opensafely exec ehrql:v1 generate-dataset analysis/dataset_definition.py
```

```text
A population has not been defined; define one with define_population()
```

```python
from ehrql import create_dataset
from ehrql.tables.core import patients

dataset = create_dataset()
dataset.age = patients.age_on("2023-01-01")
dataset.define_population(dataset.age > 16) # Here we have now defined a population for the dataset.
```

--------------------------------

### Import multiple core tables

Source: https://docs.opensafely.org/ehrql/explanation/backend-tables

Example of importing multiple core tables for use in a dataset definition.

```python
from ehrql.tables.core import patients, practice_registrations, clinical_events, medications
```

--------------------------------

### Example dataset output

Source: https://docs.opensafely.org/ehrql/tutorials/dummy-data/provide-dummy-tables

The expected output format containing patient_id, age, and msoa columns.

```csv
[info   ] Building dataset and writing results
patient_id,age,msoa
1,66,E02000001
2,67,E02000001
3,49,E02000001
4,75,E02000001
7,39,E02000001
8,71,E02000001
9,49,E02000001
12,71,E02000001
14,41,E02000001
17,67,E02000001
```

--------------------------------

### Running a Reusable Action in project.yaml

Source: https://docs.opensafely.org/actions-reusable

Example configuration for executing a reusable action within a study's project.yaml file, including versioning, arguments, and configuration options.

```yaml
actions:
  generate_dataset:
    run: ehrql:v1 generate-dataset analysis/dataset_definition.py --output output/dataset.csv.gz
    outputs:
      highly_sensitive:
        dataset: output/dataset.csv.gz

  run_a_reusable_action:
    # We will run version `v1.0.0` of the reusable action called `a_reusable_action`.
    # The reusable action accepts an argument; in this case, a path to a file.
    run: a_reusable_action:v1.0.0 output/dataset.csv.gz
    # The reusable action accepts a configuration option;
    # in this case, an output format.
    config:
      output-format: PNG
    needs: [generate_dataset]
    outputs:
      moderately_sensitive:
        output: output/output_from_a_reusable_action.png
```

--------------------------------

### Reinstall OpenSAFELY via pipx

Source: https://docs.opensafely.org/install-macos

Command to reinstall the OpenSAFELY CLI using pipx if previous installations encounter issues.

```bash
pipx reinstall opensafely --python ~/.pyenv/shims/python3.10
```

--------------------------------

### Define and create an ehrQL dataset

Source: https://docs.opensafely.org/ehrql/tutorials/introduction-to-ehrql/building-a-dataset

This example demonstrates the full workflow of defining a population and assigning columns to a dataset object. The resulting object must be named 'dataset' for the extraction process.

```python
from ehrql import create_dataset, codelist_from_csv, days, show
from ehrql.tables.core import patients, practice_registrations, clinical_events, medications

index_date = "2024-03-31"

diabetes_codes = codelist_from_csv("codelists/nhsd-primary-care-domain-refsets-dm_cod.csv", column="code")
resolved_codes = codelist_from_csv("codelists/nhsd-primary-care-domain-refsets-dmres_cod.csv", column="code")
proteinuria_codes = codelist_from_csv("codelists/nhsd-primary-care-domain-refsets-prt_cod.csv", column="code")
microalbuminuria_codes = codelist_from_csv("codelists/nhsd-primary-care-domain-refsets-mal_cod.csv", column="code")
ace_codes = codelist_from_csv("codelists/opensafely-ace-inhibitor-medications.csv", column="code")
arb_codes = codelist_from_csv("codelists/opensafely-angiotensin-ii-receptor-blockers-arbs.csv", column="code")

previous_events = clinical_events.where(clinical_events.date.is_on_or_before(index_date))
recent_meds = medications.where(medications.date.is_on_or_between(index_date - days(180), index_date))

aged_17_or_older = (index_date - patients.date_of_birth).years >= 17
was_alive = patients.date_of_death.is_null() | (patients.date_of_death > index_date)
was_registered = (
    practice_registrations.where(practice_registrations.start_date <= index_date)
    .except_where(practice_registrations.end_date < index_date)
    .exists_for_patient()
)

last_diagnosis_date = (
    previous_events
    .where(clinical_events.snomedct_code.is_in(diabetes_codes))
    .sort_by(clinical_events.date)
    .last_for_patient()
    .date
)
last_resolved_date = (
    previous_events
    .where(clinical_events.snomedct_code.is_in(resolved_codes))
    .sort_by(clinical_events.date)
    .last_for_patient()
    .date
)

has_unresolved_diabetes = last_diagnosis_date.is_not_null() & (
    last_resolved_date.is_null() | (last_resolved_date < last_diagnosis_date)
)

on_register = aged_17_or_older & was_alive & was_registered & has_unresolved_diabetes

has_proteinuria_diagnosis = (
    previous_events
    .where(clinical_events.snomedct_code.is_in(proteinuria_codes))
    .exists_for_patient()
)

has_microalbuminuria_diagnosis = (
    previous_events
    .where(clinical_events.snomedct_code.is_in(microalbuminuria_codes))
    .exists_for_patient()
)

has_arb_treatment = (
    recent_meds
    .where(medications.dmd_code.is_in(arb_codes))
    .exists_for_patient()
)

has_ace_treatment = (
    recent_meds
    .where(medications.dmd_code.is_in(ace_codes))
    .exists_for_patient()
)

dataset = create_dataset()
dataset.define_population(on_register)

dataset.prt_or_mal = has_proteinuria_diagnosis | has_microalbuminuria_diagnosis
dataset.ace_or_arb = has_arb_treatment | has_ace_treatment

show(dataset)
```

--------------------------------

### Launch JupyterLab

Source: https://docs.opensafely.org/opensafely-cli

Starts a JupyterLab session in the current directory. Specific versions can be targeted by appending a version tag.

```bash
opensafely launch jupyter
```

```bash
opensafely launch jupyter:v1
```

--------------------------------

### Handle invalid feature names starting with underscores

Source: https://docs.opensafely.org/ehrql/how-to/errors

Examples showing how to fix feature names that incorrectly start with an underscore.

```python
from ehrql import create_dataset
from ehrql.tables.core import patients

dataset = create_dataset()
age = patients.age_on("2023-01-01")
dataset.define_population(age > 16)
dataset._age = age
```

```bash
opensafely exec ehrql:v1 generate-dataset analysis/dataset_definition.py
```

```text
Traceback (most recent call last):
  File "/workspace/analysis/dataset_definition.py", line 7, in <module>
    dataset._age = age
    ^^^^^^^^^^^^^
AttributeError: Variable names must start with a letter, and contain only alphanumeric characters and underscores (you defined a variable '_age')
```

```python
from ehrql import create_dataset
from ehrql.tables.core import patients

dataset = create_dataset()
age = patients.age_on("2023-01-01")
dataset.define_population(age > 16)
dataset.age = age # _age feature renamed to remove the leading underscores.
```

--------------------------------

### Launch RStudio Server

Source: https://docs.opensafely.org/opensafely-cli

Starts an RStudio Server session in the current repository. Specific versions can be targeted by appending a version tag.

```bash
opensafely launch rstudio
```

```bash
opensafely launch rstudio:v1
```

--------------------------------

### View help for launch command

Source: https://docs.opensafely.org/opensafely-cli

Displays available options for the launch command.

```bash
opensafely launch --help
```

--------------------------------

### Generate datasets with specific output formats

Source: https://docs.opensafely.org/ehrql/explanation/output-formats

Examples of using the --output flag to specify .arrow or .csv.gz formats during dataset generation.

```bash
opensafely exec ehrql:v1 generate-dataset "./dataset-definition.py" --dummy-tables "example-data/" --output "./outputs/data_extract.arrow"
```

```bash
opensafely exec ehrql:v1 generate-dataset "./dataset-definition.py" --dummy-tables "example-data/" --output "./outputs/data_extract.csv.gz"
```

--------------------------------

### Read and Write Data Outputs

Source: https://docs.opensafely.org/actions-scripts

Examples for reading and writing compressed data formats in Python, R, and Stata. Note that Stata requires specific utilities like arrowload and manual decompression for CSV files.

```python
import pandas as pd
import pyarrow.feather as feather

# read arrow output from ehrql, decompressed automatically
# as a pandas.DataFrame
read_df = feather.read_feather("output/dataset.arrow")
# as a pyarrow.Table
read_arrow = feather.read_table("output/dataset.arrow")

# read compressed CSV output from ehrql
pd.read_csv("output/dataset.csv.gz")

# write compressed arrow file
df.to_feather("output/model.arrow", compression="zstd")
```

```r
# read arrow output from ehrql, decompressed automatically
df <- arrow::read_feather("output/dataset.arrow")

# read compressed CSV output from ehrql
df <- readr::read_csv("output/dataset.csv.gz")

# write a compressed feather file
arrow::write_feather(df, "output/model.arrow", compression="zstd")
```

```stata
// read arrow output from ehrql
// stata itself does not directly support .arrow. However, OpenSAFELY's Stata Docker
// image contains the arrowload library that can load .arrow files in Stata.

. arrowload /path/to/arrow/file

// read compressed CSV output from ehrql
// stata cannot handle compressed CSV files directly, so unzip first to a plain CSV file
// the unzipped file will be discarded when the action finishes.
!gunzip output/dataset.csv.gz

// now import the uncompressed CSV using delimited
import delimited using output/dataset.csv

// save in compressed dta.gz format
gzsave output/model.dta.gz

// load a compressed .dta.gz file
gzload output/dataset.dta.gz
```

--------------------------------

### Complete dataset definition for controls

Source: https://docs.opensafely.org/case-control-studies

Full example of the dataset_definition_controls.py file combining table loading, population definition, and event extraction.

```python
import datetime

from ehrql import codelist_from_csv, create_dataset, table_from_file
from ehrql.tables.core import clinical_events


CONTROLS = "output/matched_matches.arrow"
codelist = codelist_from_csv("codelists/codelist.csv")

matched_patients = table_from_file(
  CONTROLS,
  columns={
    "age": int,
    "sex": str,
    "index_date": datetime.date
  }
)

dataset = create_dataset()
dataset.define_population(matched_patients.exists_for_patient())
events_in_codelist = clinical_events.where(
    clinical_events.snomedct_code.is_in(codelist)
)
dataset.has_event_in_codelist = events_in_codelist.where(
    events_in_codelist.date.is_on_or_after(matched_patients.index_date)
).exists_for_patient()
```

--------------------------------

### Example quantity values

Source: https://docs.opensafely.org/ehrql/reference/schemas/raw.tpp

Examples of structured text found in the quantity field for medications.

```text
10ml - 0.5%
100 mililitres
1 pack of 28 capsule(s)
63 tablet
21 tablet(s) - 400mg
1 op - 8.75 cm x 1 m (e)
```

--------------------------------

### Define a measure with ehrQL

Source: https://docs.opensafely.org/ehrql/explanation/measures

This example demonstrates how to define a measure using the INTERVAL placeholder to evaluate data over specific time ranges.

```python
from ehrql import INTERVAL, case, create_measures, months, when
from ehrql.tables.core import medications, patients

# Every measure definitions file must include this line
measures = create_measures()

# Disable disclosure control for demonstration purposes.
# Values will neither be suppressed nor rounded.
measures.configure_disclosure_control(enabled=False)

# Small codelist for demonstration purposes; the real list would be longer
atorvastatin_tablets = [
    "39733211000001101",
    "39695411000001103",
    "39733011000001106",
    "39733111000001107",
]

atorvastatin_80mg_tablets = [
    "39733211000001101",
]

# The use of the special INTERVAL placeholder below is the key part of
# any measure definition as it allows the definition to be evaluated
# over a range of different intervals, rather than a fixed pair of dates
rx_in_interval = medications.where(
    medications.date.is_during(INTERVAL)
)
atorvastatin_rx = rx_in_interval.where(
    medications.dmd_code.is_in(atorvastatin_tablets)
)
atorvastatin_80_rx = rx_in_interval.where(
    medications.dmd_code.is_in(atorvastatin_80mg_tablets)
)

has_recorded_sex = patients.sex.is_in(["male", "female"])

measures.define_measure(
    name="atorva_80",
    numerator=atorvastatin_80_rx.exists_for_patient(),
    denominator=atorvastatin_rx.exists_for_patient() & has_recorded_sex,
    group_by={
        "sex": patients.sex
    },
    intervals=months(6).starting_on("2022-01-01"),
)
```

--------------------------------

### View CLI help documentation

Source: https://docs.opensafely.org/opensafely-cli

Access built-in help for the CLI or specific subcommands.

```bash
opensafely --help
```

```bash
opensafely run --help
```

--------------------------------

### Filter registrations by start date

Source: https://docs.opensafely.org/ehrql/tutorials/introduction-to-ehrql/more-complex-transformations

Create a boolean series to identify registrations that started before the index date.

```python
from ehrql import show
from ehrql.tables.core import patients, practice_registrations, clinical_events, medications

index_date = "2024-03-31"

show(
    practice_registrations.start_date,
    practice_registrations.start_date <= index_date
)
```

--------------------------------

### Access Stata documentation

Source: https://docs.opensafely.org/ehrql/explanation/output-formats

Commands to launch Stata and view the arrowload help documentation.

```bash
opensafely exec stata-mp stata
```

```stata
. help arrowload
```

--------------------------------

### Generate intervals starting on a date

Source: https://docs.opensafely.org/ehrql/reference/language

Generate a list of time intervals covering a duration starting from a specific date.

```python
weeks(3).starting_on("2000-01-01")
```

```python
[
    (date(2000, 1, 1), date(2000, 1, 7)),
    (date(2000, 1, 8), date(2000, 1, 14)),
    (date(2000, 1, 15), date(2000, 1, 21)),
]
```

--------------------------------

### Create dummy tables

Source: https://docs.opensafely.org/ehrql/tutorials/dummy-data/provide-dummy-tables

Command to output dummy tables to a specified directory.

```bash
opensafely exec ehrql:v1 create-dummy-tables analysis/dataset_definition_london_adults.py dummy_tables
```

--------------------------------

### Date Normalization

Source: https://docs.opensafely.org/ehrql/reference/features

Methods to normalize dates to the start of a period.

```APIDOC
## Date Normalization

### Description
Adjusts a date to the first day of its year or month.

### Usage
- `p.d1.to_first_of_year()`: Returns the date adjusted to January 1st of the same year.
- `p.d1.to_first_of_month()`: Returns the date adjusted to the 1st day of the same month.
```

--------------------------------

### Normalize Dates

Source: https://docs.opensafely.org/ehrql/reference/features

Adjust dates to the start of their respective year or month.

```ehrql
p.d1.to_first_of_year()
```

```ehrql
p.d1.to_first_of_month()
```

--------------------------------

### Execute dataset generation with a dummy file

Source: https://docs.opensafely.org/ehrql/tutorials/dummy-data/provide-a-dummy-dataset

Run the ehrQL generation command specifying the path to the custom dummy dataset.

```bash
opensafely exec ehrql:v1 generate-dataset analysis/dataset_definition.py --dummy-data-file analysis/dummy_dataset.csv
```

--------------------------------

### Run a pipeline action

Source: https://docs.opensafely.org/getting-started/tutorial/run-the-project-pipeline

Execute a predefined action from the project pipeline using the OpenSAFELY CLI.

```bash
opensafely run generate_dataset
```

--------------------------------

### Generate and use dummy tables

Source: https://docs.opensafely.org/ehrql/tutorials/dummy-data/provide-dummy-tables

Commands to generate a dataset, create dummy tables, and run a dataset definition against those tables.

```bash
opensafely exec ehrql:v1 generate-dataset analysis/dummy_data_definition_london_adults.py
```

```bash
opensafely exec ehrql:v1 create-dummy-tables analysis/dummy_data_definition_london_adults.py dummy_tables
```

```bash
opensafely exec ehrql:v1 generate-dataset analysis/dataset_definition_london_adults.py --dummy-tables dummy_tables
```

--------------------------------

### Incorrect population definition

Source: https://docs.opensafely.org/ehrql/tutorials/dummy-data/provide-a-dummy-dataset

An example of an impossible population definition in the dataset definition file.

```python
...
dataset.define_population((age < 18) & (age > 80))
...
```

--------------------------------

### Generate dummy tables with ehrql

Source: https://docs.opensafely.org/ehrql/reference/cli

Creates dummy tables from a definition file and writes them to a specified directory.

```bash
ehrql create-dummy-tables DEFINITION_FILE [DUMMY_TABLES_PATH] [--help]
      [ -- ... PARAMETERS ...]
```

--------------------------------

### Execute a project action

Source: https://docs.opensafely.org/opensafely-cli

Run a specific action defined in the project.yaml file.

```bash
opensafely run make_graph
```

--------------------------------

### Upgrade the OpenSAFELY CLI

Source: https://docs.opensafely.org/opensafely-cli

Commands for upgrading the CLI tool depending on the currently installed version.

```bash
opensafely upgrade
```

```bash
pip install --upgrade opensafely
```

--------------------------------

### Generate dummy tables via CLI

Source: https://docs.opensafely.org/ehrql/tutorials/dummy-data/provide-dummy-tables

Use the opensafely command to generate dummy tables from a dataset definition file and save them to a specified directory.

```bash
opensafely exec ehrql:v1 create-dummy-tables analysis/dataset_definition.py dummy_tables
```

--------------------------------

### Execute ehrQL dataset generation with dummy tables

Source: https://docs.opensafely.org/ehrql/tutorials/dummy-data/provide-dummy-tables

Run the dataset definition using a specified directory of dummy tables.

```bash
opensafely exec ehrql:v1 generate-dataset analysis/dataset_definition.py --dummy-tables dummy_tables
```

--------------------------------

### Dataset generation terminal output

Source: https://docs.opensafely.org/getting-started/tutorial/generate-a-first-dataset

Example of the compilation and build logs displayed in the terminal during execution.

```text
…
[info     ] Compiling dataset definition from analysis/dataset_definition.py
[info     ] Building dataset and writing results
…
```

--------------------------------

### Generate a dataset using dummy tables

Source: https://docs.opensafely.org/ehrql/how-to/dummy-data

Executes the dataset definition using the provided folder of dummy CSV tables as the backend data source.

```bash
opensafely exec ehrql:v1 generate-dataset dataset_definition.py --dummy-tables dummy-folder
```

--------------------------------

### Event Frame Structure

Source: https://docs.opensafely.org/ehrql/reference/cheatsheet

Example of an event frame containing multiple rows per patient.

```text
patient_id event_date event_code
123        2020-04-01 abc
123        2020-04-02 def
123        2021-01-01 ghi

```

--------------------------------

### Patient Frame Structure

Source: https://docs.opensafely.org/ehrql/reference/cheatsheet

Example of a patient frame containing one row per patient.

```text
patient id date_of_birth sex
123        1980-01-01    m
456        1990-06-06    f
789        2020-01-01    i

```

--------------------------------

### Run all actions

Source: https://docs.opensafely.org/actions-pipelines

Executes every action defined in the project.yaml file using the built-in run_all command.

```bash
opensafely run run_all
```

--------------------------------

### Run a specific action

Source: https://docs.opensafely.org/actions-pipelines

Executes a single action defined in the project.yaml file using dummy data.

```bash
opensafely run generate_dataset
```

```bash
opensafely run run_model
```

--------------------------------

### Import and use show() function

Source: https://docs.opensafely.org/ehrql/explanation/vscode-extension

Basic syntax for importing and using the show() function to inspect ehrQL elements.

```python
from ehrql import show
```

```python
show(<element>)
```

--------------------------------

### Example of an invalid dummy dataset

Source: https://docs.opensafely.org/ehrql/tutorials/dummy-data/provide-a-dummy-dataset

A CSV file containing a data type error in the age column.

```csv
patient_id,age,sex,first_event_date
1,twenty,male,2022-10-02
2,24,male,2022-07-30
...
```

--------------------------------

### Generate and use base dummy dataset

Source: https://docs.opensafely.org/ehrql/how-to/dummy-data

Commands to generate an initial dataset file and subsequently use that file as dummy data for further development.

```bash
opensafely exec ehrql:v1 generate-dataset dataset_definition.py --output dataset.csv
```

```bash
opensafely exec ehrql:v1 generate-dataset dataset_definition.py --dummy-data-file dataset.csv
```

--------------------------------

### Using different parameter types

Source: https://docs.opensafely.org/ehrql/how-to/parameterise-ehrql

Demonstrates casting parameters to int, float, and Path types.

```python
from pathlib import Path

from ehrql import get_parameter

# interpret as integer
param1 = get_parameter("param1", type=int, default=1)
# interpret as integer
param2 = get_parameter("param2", type=float, default=1.0)
# interpret as Path
param3 = get_parameter("param3", type=Path, default=Path("output"))

```

--------------------------------

### Filter by interval

Source: https://docs.opensafely.org/ehrql/explanation/measures

Examples of filtering data based on time intervals, including the use of the INTERVAL placeholder.

```python
rx_in_interval = medications.where(
    medications.date.is_during(INTERVAL)
)
```

```python
rx_in_interval = medications.where(
    medications.date.is_on_or_between("2022-01-01", "2022-01-31")
)
```

```python
rx_in_interval = medications.where(
    medications.date.is_on_or_between(INTERVAL.start_date, INTERVAL.end_date)
)
```

```python
rx_in_interval = medications.where(
    medications.date.is_during(INTERVAL)
)
```

--------------------------------

### Debug ehrQL syntax errors

Source: https://docs.opensafely.org/ehrql/explanation/vscode-extension

Example of an invalid ehrQL query and the resulting traceback error message.

```python
show(
    medications.where(medications.date >= "2016-01-01").sort_by(medications.dat).first_for_patient()
)
```

```text
Error loading file 'dataset_definition.py':

Traceback (most recent call last):
  File "/home/becky/datalab/ehrql/dataset_definition.py", line 21, in <module>
    medications.where(medications.date >= "2016-01-01").sort_by(medications.dat).first_for_patient()
                                                                ^^^^^^^^^^^^^^^
AttributeError: 'medications' object has no attribute 'dat'
```

--------------------------------

### Show multiple ehrQL series

Source: https://docs.opensafely.org/ehrql/explanation/vscode-extension

Examples of displaying multiple variables or series in a single output table.

```python
show(patients.sex, clinical_events.count_for_patient())
```

```python
show(clinical_events.date, clinical_events.numeric_value)
```

--------------------------------

### Use INTERVAL.start_date placeholder

Source: https://docs.opensafely.org/ehrql/reference/language

Use the start_date attribute of the INTERVAL placeholder for inclusive start date filtering.

```python
clinical_events.date.is_on_or_after(INTERVAL.start_date)
```

--------------------------------

### Initialize a Dataset

Source: https://docs.opensafely.org/ehrql/reference/language

Create a new dataset object to define the population and variables.

```python
dataset = create_dataset()
```

--------------------------------

### Normalize dates to year start

Source: https://docs.opensafely.org/ehrql/reference/language

Replaces dates in a series with the first day of their respective calendar year.

```python
patients.date_of_death.to_first_of_year()
```

--------------------------------

### Basic ehrQL CLI Usage

Source: https://docs.opensafely.org/ehrql/reference/cli

General syntax for invoking the ehrQL command line tool.

```bash
ehrql [--help] [--version] COMMAND_NAME ...
```

--------------------------------

### Example patients.csv dummy data

Source: https://docs.opensafely.org/ehrql/tutorials/dummy-data/provide-dummy-tables

Sample CSV content for the patients table used in dummy data testing.

```csv
patient_id,date_of_birth,date_of_death,sex
1,1901-01-01,,unknown
2,2025-01-01,,intersex
3,1970-04-01,,male
4,1945-08-01,,unknown
5,1996-09-01,,unknown
6,1955-07-01,,unknown
7,1979-11-01,,unknown
8,1951-09-01,,unknown
9,1970-06-01,1991-07-08,unknown
10,1969-06-01,,unknown
```

--------------------------------

### Execute dataset generation with dummy data

Source: https://docs.opensafely.org/ehrql/how-to/dummy-data

Run the dataset definition using a specific CSV file as the source for dummy data.

```bash
opensafely exec ehrql:v1 generate-dataset dataset_definition.py --dummy-data-file dummy.csv
```

--------------------------------

### Display practice registrations

Source: https://docs.opensafely.org/ehrql/tutorials/introduction-to-ehrql/more-complex-transformations

Use the show function to inspect the practice_registrations table.

```python
from ehrql import show
from ehrql.tables.core import patients, practice_registrations, clinical_events, medications

index_date = "2024-03-31"

show(practice_registrations)
```

--------------------------------

### Example ehrQL error traceback

Source: https://docs.opensafely.org/ehrql/how-to/errors

A sample Python traceback showing an AttributeError caused by an invalid variable name.

```text
Traceback (most recent call last):
  File "/workspace/analysis/dataset_definition.py", line 7, in <module>
    dataset._age = age
    ^^^^^^^^^^^^^
AttributeError: Variable names must start with a letter, and contain only alphanumeric characters and underscores (you defined a variable '_age')
```

--------------------------------

### Example dummy dataset output

Source: https://docs.opensafely.org/ehrql/tutorials/dummy-data/generate-a-dummy-dataset

Sample CSV output generated by the ehrQL tool when running the dataset definition.

```csv
patient_id,age,sex,first_event_date
1,65,unknown,1961-07-26
2,67,intersex,1967-11-13
3,49,unknown,1974-02-08
4,74,unknown,1983-10-07
5,23,intersex,2000-02-09
6,64,male,
7,40,unknown,
8,68,male,
9,49,male,1971-05-23
10,50,intersex,1970-06-28
```

--------------------------------

### Create Event Table

Source: https://docs.opensafely.org/ehrql/reference/language

Initialize an EventTable using the add_event_table method.

```python
dataset.add_event_table(
    "vaccinations",
    date=vaccination_events.date,
)
```

--------------------------------

### Defining test data structure

Source: https://docs.opensafely.org/ehrql/how-to/test-dataset-definition

Example of defining test data for a single patient with both one-row-per-patient and many-rows-per-patient tables.

```python
test_data = {
    1: {
        "patients": {
            "date_of_birth": date(2020, 1, 1),
            "sex": "female"},
        "medications": [
            {
                # First prescription of asthma medication
                "date": date(2010, 1, 1),
                "dmd_code": "39113311000001107",
            },
            {
                # Second prescription of asthma medication
                "date": date(2020, 1, 1),
                "dmd_code": "39113311000001107",
            },
        ],
        # Add expectations for patient 1 here
        # See next section of this guide
    },
}
```

--------------------------------

### Output dummy dataset to file

Source: https://docs.opensafely.org/ehrql/tutorials/dummy-data/generate-a-dummy-dataset

Use the --output flag to save the generated dummy data to a specific file path.

```bash
opensafely exec ehrql:v1 generate-dataset analysis/dataset_definition.py --output output/dataset.csv
```

--------------------------------

### Handle execution errors

Source: https://docs.opensafely.org/ehrql/explanation/running-ehrql

Example of error messages displayed when a dataset definition contains invalid ehrQL code.

```text
$ opensafely exec ehrql:v1 generate-dataset dataset_definition.py --dummy-tables example-data --output output/dataset.csv
2023-04-21 17:53:42 [info     ] Compiling dataset definition from dataset_definition.py [ehrql.main]
Error loading file 'dataset_definition.py':
```

```text
Traceback (most recent call last):
  File "/workspace/dataset_definition.py", line 10, in <module>
    dataset.med_date = latest_asthma_med.dat
                       ^^^^^^^^^^^^^^^^^^^
AttributeError: 'medications' object has no attribute 'dat'
```

--------------------------------

### Run interactive sessions with opensafely exec

Source: https://docs.opensafely.org/opensafely-cli

Commands to launch interactive environments for Stata, R, Python, and Bash using the OpenSAFELY CLI.

```bash
opensafely exec stata-mp
```

```bash
opensafely exec r
```

```bash
opensafely exec python
```

```bash
opensafely exec python ipython
```

```bash
opensafely exec IMAGE bash
```

--------------------------------

### Define a measure for dummy data

Source: https://docs.opensafely.org/ehrql/how-to/dummy-measures-data

Example measures definition used to demonstrate custom dummy measures input.

```python
from ehrql import create_measures, years
from ehrql.measures import INTERVAL
from ehrql.tables.core import patients, clinical_events

events_in_interval = clinical_events.where(clinical_events.date.is_during(INTERVAL))
had_event = events_in_interval.exists_for_patient()
intervals = years(2).starting_on("2020-01-01")
measures = create_measures()

measures.define_measure(
    "had_event_by_sex",
    numerator=had_event,
    denominator=patients.exists_for_patient(),
    group_by={"sex": patients.sex},
    intervals=intervals,
)
```

--------------------------------

### Implementing logic without helper functions

Source: https://docs.opensafely.org/ehrql/tutorials/introduction-to-ehrql/simple-transformations

Demonstrates how to achieve the same results using raw date arithmetic and null checks instead of helper functions.

```python
from ehrql import show
from ehrql.tables.core import patients, practice_registrations, clinical_events, medications

index_date = "2024-03-31"

aged_17_or_older = (index_date - patients.date_of_birth).years >= 17
is_alive = patients.date_of_death.is_null() | (patients.date_of_death > index_date)

show(
    aged_17_or_older,
    is_alive,
    aged_17_or_older & is_alive
)
```

--------------------------------

### Check if date is on or between with reversed bounds

Source: https://docs.opensafely.org/ehrql/reference/features

Demonstrates behavior when the start date is after the end date in an inclusive range check.

```python
p.d1.is_on_or_between(date(2010, 1, 4), date(2010, 1, 2))
```