03_join_tables.py

"""
Join metrics at each geography level (LSOA, LSOA) into one file.
"""

import pandas as pd

import config


def combine_and_save(data: list[pd.DataFrame], filename: str):
    """
    Combine a list of DataFrames `data` column-wise and ensuring there are no duplicates, saving to
    the csv `filename`.
    """
    data = pd.concat(data, axis=1, verify_integrity=True)
    data = data.reset_index()
    data.to_csv(config.combined_data_dir / filename, index=False)


if __name__ == '__main__':
    # Ensure that the directories we are outputting data to exist
    config.combined_data_dir.mkdir(parents=True, exist_ok=True)

    # Separately combine data for Census LSOA-level, Census MSOA-level and IMD LSOA-level, according
    # to the data types defined in the `data_sources.yaml` config

    lsoa_data = []
    msoa_data = []
    lsoa_2011_data = []

    for data_source in config.data_sources:
        if data_source['type'] == 'census_lsoa_metric':
            data = pd.read_csv(config.subset_data_dir / (data_source['name'] + '.csv'))
            data = data.set_index(['date', 'geography', 'geography code'])
            lsoa_data.append(data)
        elif data_source['type'] == 'census_msoa_metric':
            data = pd.read_csv(config.subset_data_dir / (data_source['name'] + '.csv'))
            data = data.set_index(['date', 'geography', 'geography code'])
            msoa_data.append(data)
        elif data_source['type'] == 'imd_lsoa_2011_metric':
            data = pd.read_csv(config.subset_data_dir / (data_source['name'] + '.csv'))
            data = data.set_index(['LSOA code (2011)', 'LSOA name (2011)', 'Local Authority District code (2019)', 'Local Authority District name (2019)'])
            lsoa_2011_data.append(data)

    combine_and_save(lsoa_data, 'lsoa_data.csv')
    combine_and_save(msoa_data, 'msoa_data.csv')
    combine_and_save(lsoa_2011_data, 'lsoa_2011_data.csv')