04_calculate_indicators.py

"""
Calculate metrics a.k.a. indicators for each area (LSOA, MSOA) using the formulae defined in
`data_sources.yaml`.

The formulae variables referencing columns in the source data, e.g.

    new_column_name__pc: 100 * (column1_name + column2_name) / column3_name

Units are usually given after a final `__` suffix, e.g. `__pc` for percent.
"""

import pandas as pd

import config


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

    # Separately calculate metrics 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 = pd.read_csv(config.combined_data_dir / 'lsoa_data.csv')
    msoa_data = pd.read_csv(config.combined_data_dir / 'msoa_data.csv')
    lsoa_2011_data = pd.read_csv(config.combined_data_dir / 'lsoa_2011_data.csv')
    
    # Provide a consistent index in the output (geography code followed by geography), and no matter
    # what these columns were called in the original data, call them lsoa21cd and lsoa21nm (or
    # similar)
    lsoa_indicators = lsoa_data[['geography code', 'geography']].set_axis(['lsoa21cd', 'lsoa21nm'], axis=1).copy()
    msoa_indicators = msoa_data[['geography code', 'geography']].set_axis(['msoa21cd', 'msoa21nm'], axis=1).copy()
    lsoa_2011_indicators = lsoa_2011_data[['LSOA code (2011)', 'LSOA name (2011)']].set_axis(['lsoa11cd', 'lsoa11nm'], axis=1).copy()

    # Each data source can have more than one indicator defined in `data_sources.yaml`, so loop
    # through them
    for data_source in config.data_sources:
        if 'indicators' in data_source:
            if data_source['type'] == 'census_lsoa_metric':
                for indicator, formula in data_source['indicators'].items():
                    # Evaluate the formula specified in `data_sources.yaml` in the context of the
                    # DataFrame (so variable in the formula are referencing columns in the
                    # DataFrame)
                    lsoa_indicators[indicator] = lsoa_data.eval(formula)
            elif data_source['type'] == 'census_msoa_metric':
                for indicator, formula in data_source['indicators'].items():
                    # See above
                    msoa_indicators[indicator] = msoa_data.eval(formula)
            elif data_source['type'] == 'imd_lsoa_2011_metric':
                for indicator, formula in data_source['indicators'].items():
                    # See above
                    lsoa_2011_indicators[indicator] = lsoa_2011_data.eval(formula)

    # The indicator variables become the new columns of the output
    lsoa_indicators.to_csv(config.indicators_data_dir / 'lsoa_indicators.csv', index=False)
    msoa_indicators.to_csv(config.indicators_data_dir / 'msoa_indicators.csv', index=False)
    lsoa_2011_indicators.to_csv(config.indicators_data_dir / 'lsoa_2011_indicators.csv', index=False)