Create public Trust-LTLA mapping from the NHS mapping

Note: The code presented in this vignette is for indicative purposes only as generating the mapping requires confidential data. If you have access to the raw data save it as data-raw/trust-ltla-mapping/trust_ltla_mapping_raw_2020-11-30.csv in the package repository and then run this code to regenerate the mapping.

Aim

To create a Trust-LTLA mapping that can be made public (i.e. that has been anonymised).

Summary

The NHS mapping is a mapping between NHS sites (e.g. hospitals) and lower-tier local authorities. In summary, we:

  1. Map NHS sites to NHS Trusts
  2. Summarise the mapping by Trust and LTLA
  3. Exclude any Trust-LTLA pairs where the number of spells is less than 10
  4. Summarise the mapping to keep (i) p_trust, the proportion of admissions at a given Trust that come from a given LTLA, and (ii) p_ltla, the proportion of admissions from a given LTLA that go to a given Trust.

We also repeat this for Trust-UTLA (since case data is publicly available by UTLA) and STP-UTLA.

library(readr)
library(dplyr)
library(janitor)
library(usethis)
# Site-Trust lookup
trust_lookup <- readr::read_csv(file = here::here("data-raw", "raw", "england_trusts", "trust_list.csv"),
                            col_names = FALSE) %>%
  dplyr::select(trust_code = X1, stp_code = X4) %>%
  unique()

# Mergers
trust_mergers <- readr::read_csv(file = here::here("data-raw", "raw", "england_trusts", "trust_mergers.csv"), col_names = TRUE) 


# LTLA-UTLA lookup
ltla_lookup <- readr::read_csv(file = here::here("data-raw", "raw", "england_ltla", "ltla_utla_list.csv"),
                               col_names = TRUE) %>%
  dplyr::select(ltla_code = LTLA19CD, utla_code = UTLA19CD)

# NHS site-LTLA mapping
nhs_mapping_raw <- readr::read_csv(file = here::here("data-raw", "trust-ltla-mapping", "trust_ltla_mapping_raw_2020-11-30.csv")) %>%
  janitor::clean_names() %>%
  dplyr::rename(site_code = der_provider_site_code,
                ltla_code = der_postcode_dist_unitary_auth,
                n = spells)
# Summarise to create Trust-LTLA mapping
nhs_mapping_top <- nhs_mapping_raw %>%
  dplyr::mutate(trust_code = stringr::str_sub(site_code, 1, 3)) %>%
  dplyr::left_join(trust_mergers, by = c("trust_code" = "trust_code_old")) %>%
  dplyr::mutate(trust_code = ifelse(!is.na(trust_code_new), trust_code_new, trust_code)) %>%
  dplyr::group_by(trust_code, ltla_code) %>%
  dplyr::summarise(n = sum(n, na.rm = TRUE),
                   .groups = "drop") %>%
  dplyr::select(trust_code, ltla_code, n)
# Drop Trust-LTLA pairs where n < 10
nhs_mapping <- nhs_mapping_top %>%
  dplyr::filter(n >= 10)

# Create p_trust and p_ltla
nhs_mapping_trust <- nhs_mapping %>%
  dplyr::group_by(trust_code) %>%
  dplyr::mutate(p_trust = n/sum(n)) %>% 
  dplyr::select(-n)

nhs_mapping_ltla <- nhs_mapping %>%
  dplyr::group_by(ltla_code) %>%
  dplyr::mutate(p_ltla = n/sum(n)) %>% 
  dplyr::select(-n)

# Final mapping
trust_ltla_public <- nhs_mapping_trust %>%
  dplyr::left_join(nhs_mapping_ltla, by = c("trust_code", "ltla_code"))
nhs_mapping <- nhs_mapping_top %>%
  dplyr::left_join(ltla_lookup, by = "ltla_code") %>%
  dplyr::group_by(trust_code, utla_code) %>%
  dplyr::summarise(n = sum(n),
                   .groups = "drop")

# Drop Trust-LTLA pairs where n < 10
nhs_mapping <- nhs_mapping %>%
  dplyr::filter(n >= 10)

# Create p_trust and p_ltla
nhs_mapping_trust <- nhs_mapping %>%
  dplyr::group_by(trust_code) %>%
  dplyr::mutate(p_trust = n/sum(n)) %>% 
  dplyr::select(-n)

nhs_mapping_utla <- nhs_mapping %>%
  dplyr::group_by(utla_code) %>%
  dplyr::mutate(p_utla = n/sum(n)) %>% 
  dplyr::select(-n)

# Final mapping
trust_utla_public <- nhs_mapping_trust %>%
  dplyr::left_join(nhs_mapping_utla, by = c("trust_code", "utla_code"))
nhs_mapping <- nhs_mapping_top %>%
  dplyr::left_join(trust_lookup, by = "trust_code") %>%
  dplyr::left_join(ltla_lookup, by = "ltla_code") %>%
  dplyr::group_by(stp_code, utla_code) %>%
  dplyr::summarise(n = sum(n),
                   .groups = "drop")

# Drop Trust-LTLA pairs where n < 10
nhs_mapping <- nhs_mapping %>%
  dplyr::filter(n >= 10)

# Create p_trust and p_ltla
nhs_mapping_stp <- nhs_mapping %>%
  dplyr::group_by(stp_code) %>%
  dplyr::mutate(p_stp = n/sum(n)) %>% 
  dplyr::select(-n)

nhs_mapping_utla <- nhs_mapping %>%
  dplyr::group_by(utla_code) %>%
  dplyr::mutate(p_utla = n/sum(n)) %>% 
  dplyr::select(-n)

# Final mapping
stp_utla_public <- nhs_mapping_stp %>%
  dplyr::left_join(nhs_mapping_utla, by = c("stp_code", "utla_code"))
saveRDS(object = trust_ltla_public,
        file = here::here("data-raw", "trust-ltla-mapping", "trust_ltla_mapping_public.rds"))
readr::write_csv(x = trust_ltla_public,
                 path = here::here("data-raw", "trust-ltla-mapping", "trust_ltla_mapping_public.csv"))


saveRDS(object = trust_utla_public,
        file = here::here("data-raw", "trust-ltla-mapping", "trust_utla_mapping_public.rds"))
readr::write_csv(x = trust_utla_public,
                 path = here::here("data-raw", "trust-ltla-mapping", "trust_utla_mapping_public.csv"))

saveRDS(object = stp_utla_public,
        file = here::here("data-raw", "trust-ltla-mapping", "stp_utla_mapping_public.rds"))
readr::write_csv(x = stp_utla_public,
                 path = here::here("data-raw", "trust-ltla-mapping", "stp_utla_mapping_public.csv"))

trust_utla_mapping <- trust_utla_public
usethis::use_data(trust_utla_mapping,  overwrite = TRUE)

trust_ltla_mapping <- trust_ltla_public
usethis::use_data(trust_utla_mapping,  overwrite = TRUE)