create vulnerability index - wards.r

##
## Aggregate England's MSOA-level Vulnerability Index into Wards, Local Authorities and Local Resilience Forums
##
library(tidyverse)
library(readxl)
library(janitor)
library(sf)

source("functions.r")
source("load lookup tables.r")

# run "create vulnerability index - MSOA - England.r" to make this dataset
vi = read_csv("output/vulnerability-MSOA-UK.csv")


##
## lookup tables
##
# need 2017's ward codes to match up with Community Needs Index
msoa_ward = load_lookup_msoa_ward(2017) %>%  # for some reason this file doesn't contain all WD17CDs for England and Wales
  select(MSOA11CD, WD17CD)

iz_ward = load_lookup_dz_iz_lad() %>% 
  select(MSOA11CD, WD17CD) %>% 
  distinct()

# load NI SOA to Wards lookup table (provided by Brian Johnston at Queens University Belfast)
soa_ward = read_excel("data/WARD-SOA.xlsx", sheet = "SOA to Wards") %>% 
  select(MSOA11CD = SOA_CODE, WD17CD = WardCode)

# append lookups
msoa_ward = bind_rows(msoa_ward, iz_ward, soa_ward)

# Wards (December 2017) Names and Codes in the United Kingdom
ward_names = read_csv("https://opendata.arcgis.com/datasets/63773bdd52e34745be3db659663d5662_0.csv") %>% 
  select(WD17CD, WD17NM)


##
## aggregate vulnerability domains into Local Authorities by calculating proportions of 20% most-vulnerable neighbourhoods in each LA
##
vi_ward = vi %>% 
  left_join(msoa_ward, by = c("Code" = "MSOA11CD"))

# clinical vulnerability 
clinical = vi_ward %>% 
  # label MSOAs by whether they're in top 20% most-vulnerable then summarise by this label
  mutate(Top20 = ifelse(`Clinical Vulnerability decile` >= 9, "Top20", "Other")) %>% 
  tabyl(WD17CD, Top20) %>% 
  
  # calculate proportion of most deprived LSOAs
  mutate(`Proportion of neighbourhoods in 20% most clinically vulnerable` = Top20 / (Top20 + Other)) %>% 
  
  # split into quintiles
  # mutate(`Clinical Vulnerability quintile` = calc_risk_quantiles(`Proportion of neighbourhoods in 20% most clinically vulnerable`, quants = 5)) %>% 
  select(-Other, -Top20)

# health/wellbeing vulnerability 
health_wellbeing = vi_ward %>% 
  # label MSOAs by whether they're in top 20% most-vulnerable then summarise by this label
  mutate(Top20 = ifelse(`Health/Wellbeing Vulnerability decile` >= 9, "Top20", "Other")) %>% 
  tabyl(WD17CD, Top20) %>% 
  
  # calculate proportion of most deprived LSOAs
  mutate(`Proportion of neighbourhoods in 20% most health/wellbeing vulnerable` = Top20 / (Top20 + Other)) %>% 
  
  # split into quintiles
  # mutate(`Health/Wellbeing Vulnerability quintile` = calc_risk_quantiles(`Proportion of neighbourhoods in 20% most health/wellbeing vulnerable`, quants = 5)) %>% 
  select(-Other, -Top20)

# economic vulnerability
economic = vi_ward %>% 
  # label MSOAs by whether they're in top 20% most-vulnerable then summarise by this label
  mutate(Top20 = ifelse(`Economic Vulnerability decile` >= 9, "Top20", "Other")) %>% 
  tabyl(WD17CD, Top20) %>% 
  
  # calculate proportion of most deprived LSOAs
  mutate(`Proportion of neighbourhoods in 20% most economically vulnerable` = Top20 / (Top20 + Other + NA_)) %>% 
  
  # split into quintiles
  # mutate(`Social Vulnerability quintile` = calc_risk_quantiles(`Proportion of neighbourhoods in 20% most socially vulnerable`, quants = 5)) %>% 
  select(-Other, -Top20, -NA_)

# social vulnerability 
social_props = vi_ward %>% 
  # label MSOAs by whether they're in top 20% most-vulnerable then summarise by this label
  mutate(Top20 = ifelse(`Social Vulnerability decile` >= 9, "Top20", "Other")) %>% 
  tabyl(WD17CD, Top20) %>% 
  
  # calculate proportion of most deprived LSOAs
  mutate(`Proportion of neighbourhoods in 20% most socially vulnerable` = Top20 / (Top20 + Other)) %>% 
  
  # split into quintiles
  # mutate(`Social Vulnerability quintile` = calc_risk_quantiles(`Proportion of neighbourhoods in 20% most socially vulnerable`, quants = 5)) %>% 
  select(-Other, -Top20)


##
## Include ward-level indicators
##
# local Community Needs Index and calculate 
community_needs_ward = read_csv("data/community-needs-ward.csv") %>% 
  select(-`Left behind area?`, -`Community Needs Index rank`) 
  

# calculate new social vulnerability score (theres nothing else to add at the moment)
social = social_props %>% 
  mutate(Country = get_country(WD17CD))

# split into separate countries
v_eng = social %>% 
  filter(Country == "England") %>% 
  
  left_join(community_needs_ward, by = c("WD17CD" = "Code")) %>% 
  # the CMI is already in ranks, so manually calculate ranks for % socially vulnerable neighbourhoods before calculating overall domain score
  mutate(Soc_vul_rank = rank2(`Proportion of neighbourhoods in 20% most socially vulnerable`)) %>%
  
  calc_domain_scores(domain = "Social", rank.indicators = FALSE, keep.interim.indicators = T)


v_wal = social %>% 
  filter(Country == "Wales") %>% 
  calc_domain_scores(domain = "Social")

v_sco = social %>% 
  filter(Country == "Scotland") %>% 
  calc_domain_scores(domain = "Social")

v_ni = social %>% 
  filter(Country == "Northern Ireland") %>% 
  calc_domain_scores(domain = "Social")

# recombine
social = bind_rows(v_eng, v_wal, v_sco, v_ni) %>% 
  select(-Country)
  
  calc_domain_scores(domain = "Social", rank.indicators = FALSE)


##
## Calculate new overall vulnerability index
##
vulnerability = clinical %>% 
  left_join(health_wellbeing, by = "WD19CD") %>% 
  left_join(economic, by = "WD19CD") %>% 
  left_join(social, by = "WD19CD") %>% 
  
  mutate(Country = get_country(WD19CD))

# split into separate datasets for each country then calculate vulnerability scores - until can work out how to get this code working:
#   group_by(Country) %>% 
#   calc_domain_scores() %>% 
#   ungroup() %>% 
v_eng = vulnerability %>% 
  filter(Country == "England") %>% 
  calc_domain_scores()

v_wal = vulnerability %>% 
  filter(Country == "Wales") %>% 
  calc_domain_scores()

v_sco = vulnerability %>% 
  filter(Country == "Scotland") %>% 
  calc_domain_scores()

v_ni = vulnerability %>% 
  filter(Country == "Northern Ireland") %>% 
  calc_domain_scores()

# recombine
vulnerability = bind_rows(v_eng, v_wal, v_sco, v_ni) %>% 
  select(WD19CD, starts_with("Vulnerability"))

# merge all proportions togethers
vulnerability_all = clinical %>% 
  left_join(health_wellbeing, by = "WD19CD") %>% 
  left_join(economic, by = "WD19CD") %>% 
  left_join(social, by = "WD19CD") %>% 
  left_join(vulnerability, by = "WD19CD") %>% 
  
  mutate(Country = get_country(WD19CD)) %>% 
  
  # calculate quintiles
  group_by(Country) %>% 
  mutate(`Clinical Vulnerability quintile` = calc_risk_quantiles(`Proportion of neighbourhoods in 20% most clinically vulnerable`, quants = 5),
         `Health/Wellbeing Vulnerability quintile` = calc_risk_quantiles(`Proportion of neighbourhoods in 20% most health/wellbeing vulnerable`, quants = 5),
         `Economic Vulnerability quintile` = calc_risk_quantiles(`Proportion of neighbourhoods in 20% most economically vulnerable`, quants = 5),
         `Social Vulnerability quintile` = calc_risk_quantiles(`Proportion of neighbourhoods in 20% most socially vulnerable`, quants = 5),
         `Vulnerability quintile` = calc_risk_quantiles(`Vulnerability rank`, quants = 5)) %>% 
  ungroup() %>% 
  
  # get names
  left_join(ward_names, by = "WD19CD") %>% 
  select(Code = WD19CD, Name = WD19NM, everything(), -Country)


##
## save
##
vulnerability_all %>% write_csv("output/vulnerability-ward.csv")

vulnerability_all %>% 
  select(Code, Name, ends_with("quintile")) %>% 
  write_csv("output/vulnerability-scores-ward.csv")

# Wards (December 2019) Boundaries UK BGC
# source: https://geoportal.statistics.gov.uk/datasets/wards-december-2019-boundaries-uk-bgc
wards = read_sf("https://opendata.arcgis.com/datasets/d2dce556b4604be49382d363a7cade72_0.geojson")

wards %>% 
  left_join(vulnerability_all, by = c("WD19CD" = "Code")) %>% 
  select(Code = WD19CD, Name, everything(), -FID, -WD19NM, -WD19NMW, -Shape__Area, -Shape__Length, -BNG_E, -BNG_N, -LONG, -LAT) %>% 
  write_sf("output/vulnerability-ward.geojson")