Intermediate LSOA data enrichment with Access to Healthy Assets and Hazards data

Intermediate LSOA data enrichment with Access to Healthy Assets and Hazards data#

This stage takes the pseudonymised LSOA data derived from the raw GPS data and enriches it with AHAH data.

# Import the necessary libraries and modules
import logging
from pathlib import Path

from bhf_smarthealth.utils import data_io
from bhf_smarthealth.utils import df_utils
from bhf_smarthealth.config import DEFAULT_AHAH_FIELDS
from bhf_smarthealth.utils.aggregators import mode_with_later_tiebreak

# Initiate logging
logger = logging.getLogger(__name__)
logging.basicConfig(level=logging.INFO)

# Path to the "intermediate" LSOA data and the reference AHAH data
AHAH_DATA_PATH = Path("../../../tests/data/reference/ahah_for_testing.parquet")
LSOA_DATA_PATH = Path("../../../tests/data/intermediate/lsoa_2025-10-09.parquet")

  1. Load in the derived LSOA data

lsoa_df = data_io.load_data_from_file(LSOA_DATA_PATH)

# Show the first five rows
lsoa_df.head()
LSOA_n_samples latest_LSOA21NM latest_LSOA21CD
sheffield_id timestamp
UOS00001 2025-10-09 00:00:00 7 Sheffield 073D E01033264
2025-10-09 00:05:00 4 Sheffield 022H E01033270
2025-10-09 00:55:00 17 Sheffield 011B E01008063
2025-10-09 01:40:00 1 Sheffield 006D E01008138
2025-10-09 01:55:00 9 Sheffield 004D E01007891

  1. Aggregate derived LSOA values into an hourly modal LSOA

If a tiebreak exists when calculating the modal LSOA, the LSOA associated with the latest timestamp is taken

# Dictionary that dictates which aggregate calculations are performed on which fields
agg_dict = {
    "latest_LSOA21CD": mode_with_later_tiebreak,
    "latest_LSOA21NM": mode_with_later_tiebreak,
}

# Apply the aggregation
lsoa_modal_df = df_utils.apply_agg_over_interval(
    lsoa_df,
    agg_dict=agg_dict,
    time_period="1h",  # 1 hour
)

  1. Rename the columns for clarity

lsoa_modal_df = df_utils.rename_columns(
    df=lsoa_modal_df,
    col_names=[
        "latest_LSOA21CD",
        "latest_LSOA21NM",
    ],
)
lsoa_modal_df.head()
latest_LSOA21CD latest_LSOA21NM
timestamp
2025-10-09 00:00:00 E01008063 Sheffield 011B
2025-10-09 01:00:00 E01007891 Sheffield 004D
2025-10-09 02:00:00 E01007390 Barnsley 030B
2025-10-09 03:00:00 E01007457 Barnsley 029E
2025-10-09 04:00:00 E01007353 Barnsley 020F

  1. Load in the reference AHAH data ready to be joined to the aggregated dataset

Note that use of this dataset requires attribution:

“The data for this research have been provided by the Geographic Data Service (geods.ac.uk), a Smart Data Research UK Investment: ES/Z504464/1.”

ahah_df = data_io.load_data_from_file(AHAH_DATA_PATH)

# Show first five rows
ahah_df.head()
LSOA21CD ah4blue ah4dent ah4gp ah4hosp ah4phar ah4leis ah4pubs ah4ffood ah4gpas ... ah4g_rnk ah4e_rnk ah4r_rnk ah4h_pct ah4g_pct ah4e_pct ah4r_pct ah4ahah ah4ahah_rnk ah4ahah_pct
6987 E01007349 6.2489 4.0517 4.4788 2.6851 2.4753 7.8121 3.9223 3.7386 0.4295 ... 34646 19568 7881 81 82 46 19 23.0148 27201 64
6991 E01007353 2.3809 1.0516 1.5069 1.2947 1.1472 3.7182 0.8328 0.7208 0.3575 ... 24692 18916 35870 25 58 45 85 19.9964 21263 50
7028 E01007390 4.9572 1.5597 2.4075 1.2549 1.8103 2.4129 1.2290 1.3465 0.3975 ... 34019 21256 27976 38 80 50 66 21.4113 24220 57
7059 E01007424 6.5558 6.1045 7.2558 3.4741 3.5114 7.3611 2.9262 3.0673 0.4863 ... 28016 19150 7960 88 66 45 19 21.8908 25173 60
7091 E01007457 4.3442 2.9063 2.8211 2.2374 2.1592 2.5880 1.9938 2.4178 0.5427 ... 11503 16376 19495 60 27 39 46 13.1279 5679 14

5 rows × 58 columns

  1. Merge this reference data (only the fields required in the final output table) with the hourly-aggregated LSOAs

lsoa_ahah_df = (
    lsoa_modal_df.reset_index()  # required to maintain the datetime column
    .merge(
        ahah_df[DEFAULT_AHAH_FIELDS],
        how="inner",
        left_on=["latest_LSOA21CD"],
        right_on=["LSOA21CD"],
    )
    .set_index("datetime")  # reset the DataFrame datetimeindex column
)

# Show the first five rows
lsoa_ahah_df.head()

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
/tmp/ipykernel_2083/2282981879.py in ?()
      5         how="inner",
      6         left_on=["latest_LSOA21CD"],
      7         right_on=["LSOA21CD"],
      8     )
----> 9     .set_index("datetime")  # reset the DataFrame datetimeindex column
     10 )
     11 
     12 # Show the first five rows

~/work/BHF-SmartHealth/BHF-SmartHealth/.venv/lib/python3.12/site-packages/pandas/core/frame.py in ?(self, keys, drop, append, inplace, verify_integrity)
   6140                     if not found:
   6141                         missing.append(col)
   6142 
   6143         if missing:
-> 6144             raise KeyError(f"None of {missing} are in the columns")
   6145 
   6146         if inplace:
   6147             frame = self

KeyError: "None of ['datetime'] are in the columns"

  1. Remove unecessary columns

This removes the sensitive LSOA fields from the dataframe

lsoa_ahah_df = df_utils.remove_columns(
    df=lsoa_ahah_df, col_names=["LSOA21CD", "latest_LSOA21CD", "latest_LSOA21NM"]
)

# Show the first five rows
lsoa_ahah_df.head()