Examples of TPMA tasks

With actual code or pseudo-code to demo some things you can do with the data.

Load the TPMA explorer data and compare the rates for two providers

We can load the rates data two ways

UDAL Data Bricks

The data which feeds the inputs app is available as a parquet stored on Data Bricks.

We load it using the below

# Load the provider rates data
provider_tpma = spark.read.parquet("/Volumes/udal_lake_mart/newhospitalprogramme/files/inputs_data/dev/provider/rates.parquet")

# view the data
display(provider_tpma)

# there is one row per provider, fyear and strategy / tpma

# if we want to focus on consultant_to_consultant_reduction_adult_surgical in 202425

consultant_to_consultant_reduction_adult_surgical_data_2425 = provider_tpma.filter(
    F.col("strategy") == "consultant_to_consultant_reduction_adult_surgical"
    ).filter(
        F.col("fyear") == 202425
    )

# the resultant table is just for this tpma in 202425 and the `crude` and `std_rate` columns give the rate, with one row per provider
display(consultant_to_consultant_reduction_adult_surgical_data_2425)

R Studio (not on UDAL)

The below code will do the equivalent in R studio without needing to log into UDAL Data Bricks. You need {azkit} to be installed and require the environment variables AZ_STORAGE_EP loaded to run azkit::read_azure_parquet

container <- azkit::get_container("inputs-data") 
provider_rates <- azkit::read_azure_parquet(
  container,
  "v5.2/provider/rates.parquet"
)

consultant_to_consultant_reduction_adult_surgical_2425 <- provider_rates |> 
  dplyr::filter(
    fyear==202425,
    strategy=="consultant_to_consultant_reduction_adult_surgical")

Get a count of beddays for a specific TPMA / provider / baseline year

We can do this using the spell-level tables on UDAL Data Bricks.

Taking the TPMA "virtual_wards_efficiencies_heart_failure" as an example.

tpma_filter = F.lit("virtual_wards_efficiencies_heart_failure")
# load the apc table
# epikey is unique in this table
default_apc = spark.table("udal_lake_mart.newhospitalprogramme.default_apc")

# load the mitigators table
# epikey can be duplicated in this table if a particular episode is in scope of more than one tpma
apc_mitig = spark.table("udal_lake_mart.newhospitalprogramme.default_apc_mitigators")

# filter the TPMAs to chosen TPMA
# now an epikey will only appear at most once
apc_mitig_filtered = apc_mitig.filter(F.col("strategy") == tpma_filter)

# left semi join will filter the full hes table to only those where epikey exists under chosen tpma
apc_filtered = default_apc.join(
    apc_mitig_filtered,
    on=["fyear", "provider", "epikey"],
    how="left_semi"
)

# to get the count of bed days, sum (`speldur` + 1) (this is to account for the fact `speldur` is the date diff and a same day discharge would be counted as zero)
# this gives us a count of bed days in scope of chosen TPMA per fyear and provider
bed_days_sum = apc_filtered.groupBy(F.col("fyear"), F.col("provider")).agg(F.sum(F.col("speldur") + 1).alias("total_bed_days"))

display(bed_days_sum)

Determine the proportion of A&E attendances / OP attendances in scope of a TPMA

Get the proportion of inpatient spells in scope of a particular TPMA or any TPMA