Data Extraction for OpenPrescribing Seasonality

Author

Nasir Abdulrasheed

1 Setup

Load packages and set up variables for BNF codes and data date range (May 2016 to April 2026).

Warning

Before running the notebook for data fetching, kindly check the dates and BNF codes to ensure you are not wasting BigQuery resources.

Code

library(tidyverse)
library(opr)
library(arrow)
library(gt)
library(here)
library(glue)

# dev set of 4 drugs
SERTRALINE_HCL <- "0403030Q0%"
ESCITALOPRAM <- "0403030X0%"
LORATADINE <- "0304010D0%"
FEXOFENADINE_HCL <- "0304010E0%"
DEV_CODES <- c(SERTRALINE_HCL, ESCITALOPRAM, FEXOFENADINE_HCL, LORATADINE)

# test drug set
FLUPENTIXOL <- "0402020G0%"
PALIPERIDONE <- "0402020AB%"
ZUCLOPENTHIXOL <- "0402020Z0%"
NORETHISTERONE <- "0604012P0%"
CELECOXIB <- "1001010AH%"
TEST_CODES <- c(
  FLUPENTIXOL,
  PALIPERIDONE,
  ZUCLOPENTHIXOL,
  NORETHISTERONE,
  CELECOXIB
)

DATE_START <- "2016-05-01"
DATE_END <- "2026-04-01"

2 Drug definition

Table 1 and Table 4 show the list of chemicals and sections whose data will be extracted for this study.

3 Fetch data

Connect to BigQuery. See here.

CONN <- connect_bq()

Query the normalised_prescribing table to fetch the data for the BNF codes. The function below takes the connection object and either display the query or collect the data, based on the display_query argument:

fetch_presc_data <- function(code, display_query = TRUE) {
  df <- get_normalised_prescribing(
    con = CONN,
    bnf_codes = code,
    start_date = DATE_START,
    end_date = DATE_END
  ) |>
    select(-net_cost, -actual_cost)

  if (display_query) {
    show_query(df)
  } else {
    collect(df)
  }
}

# fetches given products details from the bnf table
fetch_bnf_codes <- function(bnf_codes, display_query = TRUE) {
  df <- tbl(CONN, "bnf") |>
    filter(presentation_code %in% bnf_codes) |>
    select(
      -ends_with("_code"),
      -presentation,
      chemical_code,
      presentation_code
    )

  if (display_query) {
    show_query(df)
  } else {
    collect(df)
  }
}

This emits the query for fetching the prescribing data for the development drugs as an example:

fetch_presc_data(DEV_CODES, display_query = TRUE)

3.1 Development drugs

The data for the four drugs/chemicals in Table 1 will be used in developing the pipeline in the first instance, while other drugs will used for testing after implementation is complete.

Table 1: Drugs for initial pipeline development

Code

drug_names <- c(
  "Sertraline",
  "Escitalopram",
  "Loratadine",
  "Fexofenadine"
)

data.frame(
  `Chemical Name` = drug_names,
  `BNF Code` = DEV_CODES,
  check.names = FALSE
) |>
  gt() |>
  tab_style(
    style = cell_text(weight = "bold"),
    locations = cells_column_labels(everything())
  )

Table 2: Data for Sertraline, Escitalopram, Loratadine, and fexofenadine

Code

df_presc_dev <- fetch_presc_data(DEV_CODES, display_query = FALSE)

head(df_presc_dev, 5) |> gt()

Table 3: BNF details for Sertraline, Escitalopram, Loratadine, and fexofenadine

Code

df_bnf_dev <- unique(df_presc_dev$bnf_code) |>
  fetch_bnf_codes(display_query = FALSE)

head(df_bnf_dev, 5) |> gt()

df_dev_drugs <- df_presc_dev |>
  left_join(df_bnf_dev, c("bnf_code" = "presentation_code"))

save_file <- glue("df_dev_drugs_{DATE_START}_{DATE_END}.parquet")
write_parquet(
  df_dev_drugs,
  here("data-raw", "nasir", save_file)
)

head(df_dev_drugs, 5) |> gt()

3.2 Test drugs

These drugs are used to test the output of the pipeline after full implementation.

Table 4: Drugs for testing the pipeline

Code

drug_names <- c(
  "Flupentixol",
  "Paliperidone",
  "Zuclopenthixol",
  "Norethisterone",
  "Celecoxib"
)

data.frame(
  `Chemical Name` = drug_names,
  `BNF Code` = TEST_CODES,
  check.names = FALSE
) |>
  gt() |>
  tab_style(
    style = cell_text(weight = "bold"),
    locations = cells_column_labels(everything())
  )

Code

df_presc_test <- fetch_presc_data(TEST_CODES, display_query = FALSE)

df_bnf_test <- unique(df_presc_test$bnf_code) |>
  fetch_bnf_codes(display_query = FALSE)

df_test_drugs <- df_presc_test |>
  left_join(df_bnf_test, c("bnf_code" = "presentation_code"))

save_file <- glue("df_test_drugs_{DATE_START}_{DATE_END}.parquet")
write_parquet(
  df_test_drugs,
  here("data-raw", "nasir", save_file)
)

head(df_test_drugs, 5) |> gt()

3.3 NHSE regions

Fetch all NHS regions for likely sensitivity analysis later on:

Table 5: All NHSE commissioning regions

Code

df_regions <- tbl(CONN, "regional_teams") |> collect()


write_parquet(df_regions, here("data-raw", "nasir", "regional_teams.parquet"))

gt(df_regions)