Getting started with nhsbsa

The hardware and bandwidth for this mirror is donated by METANET, the Webhosting and Full Service-Cloud Provider.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]metanet.ch.

The NHSBSA Open Data Portal

The NHS Business Services Authority (NHSBSA) Open Data Portal publishes open datasets about NHS activity in England — prescribing, dental, pharmaceutical and contractor data among them. All of it is freely available under the Open Government Licence.

The portal runs on CKAN, a widely used open-source data catalogue. CKAN organises data into datasets (called “packages”) which each contain one or more resources — the individual files (usually CSV) that you can download, and, for tabular resources, query row by row.

nhsbsa is a thin, low-level client for this portal. It wraps the CKAN API and returns plain data for you to work with: tibbles for tabular results and lists for metadata. It contains no knowledge of any particular dataset, so you supply the dataset identifiers and interpret the results yourself. If you are familiar with the CKAN API, the package will feel familiar too: function names and arguments mirror the API.

The API and the response envelope

Every request goes to a CKAN action under

https://opendata.nhsbsa.net/api/3/action/<action>

and comes back as a JSON envelope of the form

{ "success": true, "result": ... }

or, on failure,

{ "success": false, "error": { "message": "..." } }

nhsbsa handles this envelope for you: it checks the success flag, returns the result, and otherwise raises an informative error. If the portal cannot be reached (for example with no internet connection) it fails gracefully with a clear message rather than an obscure low-level error.

The full set of actions is documented in the CKAN Action API reference, and the portal will return the documentation for any individual action it supports, e.g. https://opendata.nhsbsa.net/api/3/action/help_show?name=datastore_search_sql.

What the package wraps

The package wraps the useful read subset of the portal’s actions, in four small groups (the package reference index is organised the same way). If you need an action that is not yet wrapped, please open an issue.

Datasets — discover and inspect datasets (“packages”): nhsbsa_package_list() (every dataset id), nhsbsa_package_search() (search), and nhsbsa_package_show() (one dataset’s metadata, including its resources).
Resources — work with the files in a dataset: nhsbsa_resource_show() (one resource’s metadata), plus two convenience helpers, nhsbsa_list_resources() and nhsbsa_download_resource().
Datastore — query the rows of a tabular resource without downloading it: nhsbsa_datastore_search() and nhsbsa_datastore_search_sql().
Catalogue — list the portal’s taxonomy: nhsbsa_organization_list(), nhsbsa_group_list() and nhsbsa_tag_list().

Almost every function maps one-to-one onto a CKAN action of the same name. The two exceptions are the resource helpers, which combine an action with a little extra work and so are not pure wrappers:

nhsbsa_list_resources() calls nhsbsa_package_show() and reshapes its nested resources into a tibble — one row per file, including each file’s download url.
nhsbsa_download_resource() resolves a single resource (again via nhsbsa_package_show()) and then streams that resource’s file url to disk. The download itself is an ordinary HTTP request, not a CKAN action.

(nhsbsa_group_list() is included for completeness; this portal currently defines no groups and so returns an empty vector — it organises data by organisation and tags instead.) Most read-only functions also accept .return_raw = TRUE, which returns the full parsed response envelope instead of the processed result — useful when you need fields the helper does not surface, such as a datastore query’s total.

Working with the returned objects

nhsbsa_package_show(), nhsbsa_resource_show() and nhsbsa_package_search() return potentially large nested lists. To make them easier to scan, they print a tidy summary, and tibble::as_tibble() turns them into a table — a dataset into its resources, and a search into one row per matching dataset:

pkg <- nhsbsa_package_show("english-prescribing-data-epd")
pkg

tibble::as_tibble(pkg)

They are still plain lists underneath, so $, [[ and str() work as usual, and .return_raw = TRUE (or unclass()) gives the unclassed list.

tibble::as_tibble() on a dataset gives the same table as nhsbsa_list_resources() — the difference is just the entry point: nhsbsa_list_resources(id, pattern) fetches (and optionally filters) in one call, while as_tibble() reuses metadata you have already fetched, avoiding a second request.

From the portal website to the API

It helps to think of the package as a programmatic version of the portal website. The things you click there map onto API calls:

Browsing all datasets is nhsbsa_package_list() (identifiers) or nhsbsa_package_search() (richer records, with searching and paging).
Clicking a tag, say #Prescribing, takes the website to /dataset/?tags=Prescribing. A filter query on the tags field returns the datasets carrying that tag:
```
nhsbsa_package_search(fq = 'tags:"Prescribing"')$count
```
This returns more datasets than the website shows for the same tag, because the website’s dataset view hides the Freedom of Information disclosure log by default. To match what the website displays, exclude that organisation as well. tibble::as_tibble() turns the result into one row per dataset, so you can pull out the titles to compare against the website directly:
```
nhsbsa_package_search(
  fq = 'tags:"Prescribing" -organization:freedom-of-information-disclosure-log'
) |>
  as_tibble() |>
  pull(title)
```
Tags are case-sensitive; list them with nhsbsa_tag_list(), and the organisations you can filter on with nhsbsa_organization_list().
Opening a dataset’s page (e.g. /dataset/english-prescribing-data-epd) corresponds to nhsbsa_package_show("english-prescribing-data-epd"), whose resources element lists the files shown on that page. nhsbsa_list_resources() tidies those resources into a tibble.
The “Download” button on a resource fetches the file that nhsbsa_download_resource() streams to disk; the data preview / “Data API” for a resource is backed by the datastore that nhsbsa_datastore_search() queries.

A worked example

Find a dataset

nhsbsa_package_list() returns the identifier of every dataset; use it when you want to scan or search the ids yourself:

datasets <- nhsbsa_package_list()
length(datasets)
head(datasets)

When you do not already know the id, search for one with nhsbsa_package_search(). It prints a tidy summary — the match count and a table of the matching datasets; tibble::as_tibble() returns that table to work with:

nhsbsa_package_search(q = "prescribing", rows = 5)

Inspect a dataset’s resources

A dataset is a container of resources (files). nhsbsa_list_resources() lists them as a tibble, including the download url of each:

resources <- nhsbsa_list_resources("english-prescribing-data-epd")
nrow(resources)
resources |>
  select(name, format, url) |>
  slice_head(n = 6)

Filter by a pattern matched against the resource name:

nhsbsa_list_resources("english-prescribing-data-epd", pattern = "202401") |>
  select(name, id, last_modified)

nhsbsa_resource_show() returns the full metadata for a single resource (by its id), and prints a summary if you need more detail than the table above:

nhsbsa_resource_show(resources$id[[1]])

Download a resource file

Identify a single resource — by resource_id, or by a pattern that matches exactly one resource name — and stream its file to disk. You choose the destination directory (it must already exist), and the file is saved there under its own name; here we use a (smaller) resource from the BNF code dataset and save to a temporary directory:

bnf <- nhsbsa_list_resources("bnf-code-information-current-year")
path <- nhsbsa_download_resource(
  "bnf-code-information-current-year",
  resource_id = bnf$id[[1]],
  directory = tempdir()
)
basename(path)

Query rows without downloading the whole file

Not every resource can be queried row by row. The datastore is a separate, queryable copy of the tabular resources (CSVs); non-tabular files such as PDFs can only be downloaded. Where a resource is in the datastore, you can query it directly.

Two things to know about this portal specifically:

The datastore identifies a resource by its name (e.g. "EPD_202401", shown in the name column of nhsbsa_list_resources()), not by its id.
The datastore_active metadata flag is unreliable here (it is often FALSE even for resources that are queryable), so rather than trusting it, simply try the query — CSV resources are generally queryable by name, and a non-tabular resource returns an error.

Ways to query datastore data

There are two functions, and on this portal they have a clear division of labour: use nhsbsa_datastore_search() to read rows, and nhsbsa_datastore_search_sql() to filter or aggregate them.

Reading rows with `nhsbsa_datastore_search()`

nhsbsa_datastore_search() returns rows from a resource. You can choose and order columns with fields, sort with sort, and page with limit/offset. Field names are case-sensitive and must match the resource’s columns exactly (for the EPD they are upper case, e.g. PCO_CODE):

nhsbsa_datastore_search(
  resource_id = "EPD_202401",
  fields = c("PCO_CODE", "BNF_CHEMICAL_SUBSTANCE", "ITEMS"),
  sort = "ITEMS desc",
  limit = 5
)

The datastore returns at most one page of rows per request. When more rows exist than were returned, nhsbsa_datastore_search() warns you and explains how to page through the rest by increasing offset:

nhsbsa_datastore_search(
  resource_id = "EPD_202401",
  fields = c("PCO_CODE", "ITEMS"),
  limit = 5,
  offset = 5
)

CKAN’s datastore_search also defines filters (exact field matching) and q (full-text search) parameters, and nhsbsa_datastore_search() exposes them for completeness. Be aware that this portal’s datastore does not apply them — they return no matching rows — so to filter by value, use SQL instead.

Filtering and aggregating with SQL

nhsbsa_datastore_search_sql() runs a read-only SQL query, which is the reliable way to filter, compute expressions, aggregate and sort on this portal. The portal requires the resource_id alongside the query, and you reference the same resource name in the FROM clause:

# Filter to one organisation
nhsbsa_datastore_search_sql(
  resource_id = "EPD_202401",
  sql = "SELECT PCO_CODE, BNF_CHEMICAL_SUBSTANCE, ITEMS
         FROM `EPD_202401`
         WHERE PCO_CODE = 'W2U3Z'
         LIMIT 5"
)

# Aggregate: total items prescribed per organisation
nhsbsa_datastore_search_sql(
  resource_id = "EPD_202401",
  sql = "SELECT PCO_CODE, SUM(ITEMS) AS items
         FROM `EPD_202401`
         GROUP BY PCO_CODE
         ORDER BY items DESC
         LIMIT 5"
)

The SQL string is sent to the API verbatim, so you are responsible for paging (via LIMIT/OFFSET) and for quoting identifiers correctly.

These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.

Getting started with nhsbsa

The NHSBSA Open Data Portal

The API and the response envelope

What the package wraps

Working with the returned objects

From the portal website to the API

A worked example

Find a dataset

Inspect a dataset’s resources

Download a resource file

Query rows without downloading the whole file

Ways to query datastore data

Reading rows with nhsbsa_datastore_search()

Filtering and aggregating with SQL

Reading rows with `nhsbsa_datastore_search()`