prep loneliness.r

##
## Load and process loneliness risks - Local Authorities
##
library(tidyverse)
library(janitor)
library(Hmisc)

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

##
## load lookup tables
##
lookup_lsoa_msoa_lad = load_lookup_lsoa_msoa_lad()
lookup_lsoa_lad_ni = load_lookup_sa_lgd() %>% select(LSOA11CD, LAD19CD = LAD18CD) %>% distinct()
lad_17_19 = read_csv("data/LAD 2017 to LAD 2019 codes.csv")  # lookup of Local Authority codes from 2017 to 2019

# wards
lookup_msoa_ward = load_lookup_msoa_ward(2017)
lookup_iz_ward = load_lookup_iz_ward()

##
## load loneliness risk data
##
# generated by code in https://github.com/matthewgthomas/loneliness
eng_msoa_loneliness = read_csv("https://github.com/matthewgthomas/loneliness/raw/master/England/msoa_loneliness.csv")
wal_msoa_loneliness = read_csv("https://github.com/matthewgthomas/loneliness/raw/master/Wales/msoa_loneliness.csv")
sco_msoa_loneliness = read_csv("https://github.com/matthewgthomas/loneliness/raw/master/Scotland/msoa_loneliness.csv")
ni_msoa_loneliness  = read_csv("https://github.com/matthewgthomas/loneliness/raw/master/NI/msoa_loneliness.csv")

##
## England
##
# of the MSOAs with above-average loneliness risks (i.e. `loneills_2018` > 0), cut into two categories: lonely_q = 2 will be highest-risk category
eng_msoa_loneliness_high = eng_msoa_loneliness %>% 
  select(msoa11cd, loneills_2018) %>% 
  filter(loneills_2018 > 0) %>% 
  mutate(lonely_q = as.integer(cut2(loneills_2018, g = 2)))

# summarise loneliness risks into LADs - calculate proportion of highest-risk MSOAs (in the whole nation) within each LAD
lonely_eng = lookup_lsoa_msoa_lad %>% 
  # get full list of MSOAs
  filter(startsWith(MSOA11CD, "E")) %>% 
  select(MSOA11CD, LAD17CD) %>% 
  distinct() %>% 
  
  # get 2019 LA codes
  left_join(lad_17_19, by = "LAD17CD") %>% 
  
  # get quantiles for the above-average MSOAs
  left_join(eng_msoa_loneliness_high, by = c("MSOA11CD" = "msoa11cd")) %>% 
  
  # if the MSOA has a quantile of `NA`, it means it's not above average - just label this as '3'
  mutate(lonely_q = replace_na(lonely_q, 0)) %>% 
  
  # label deciles by whether they're in top 10 then summarise by this label
  tabyl(LAD19CD, lonely_q) %>% 
  
  # calculate proportion of loneliest MSOAs in each LAD
  mutate(Prop_loneliest = `2` / (`0` + `1` + `2`)) %>% 
  
  mutate(Loneliness_q = as.integer(cut2(Prop_loneliest, g = 5))) %>% 
  
  select(LAD19CD, Prop_loneliest, Loneliness_q)

# summarise loneliness risks into wards - calculate proportion of highest-risk MSOAs (in the whole nation) within each ward
lonely_ward_eng = lookup_msoa_ward %>% 
  # get full list of MSOAs
  filter(startsWith(MSOA11CD, "E")) %>% 
  
  # get quantiles for the above-average MSOAs
  left_join(eng_msoa_loneliness_high, by = c("MSOA11CD" = "msoa11cd")) %>% 
  
  # if the MSOA has a quantile of `NA`, it means it's not above average - just label this as '3'
  mutate(lonely_q = replace_na(lonely_q, 0)) %>% 
  
  # label deciles by whether they're in top 10 then summarise by this label
  tabyl(WD17CD, lonely_q) %>% 
  
  # calculate proportion of loneliest MSOAs in each LAD
  mutate(Prop_loneliest = `2` / (`0` + `1` + `2`)) %>% 
  
  mutate(Loneliness_q = calc_risk_quantiles(Prop_loneliest)) %>% 
  
  select(WD17CD, Prop_loneliest, Loneliness_q)


##
## Wales
##
# of the MSOAs with above-average loneliness risks (i.e. `loneills_2018` > 0), cut into two categories: lonely_q = 2 will be highest-risk category
wal_msoa_loneliness_high = wal_msoa_loneliness %>% 
  select(msoa11cd, loneills_2018) %>% 
  filter(loneills_2018 > 0) %>% 
  mutate(lonely_q = as.integer(cut2(loneills_2018, g = 2)))

# summarise loneliness risks into LADs - calculate proportion of highest-risk MSOAs (in the whole nation) within each LAD
lonely_wal = lookup_lsoa_msoa_lad %>% 
  # get full list of MSOAs
  filter(startsWith(MSOA11CD, "W")) %>% 
  select(MSOA11CD, LAD17CD) %>% 
  distinct() %>% 
  
  # get 2019 LA codes
  left_join(lad_17_19, by = "LAD17CD") %>% 
  
  # get quantiles for the above-average MSOAs
  left_join(wal_msoa_loneliness_high, by = c("MSOA11CD" = "msoa11cd")) %>% 
  
  # if the MSOA has a quantile of `NA`, it means it's not above average - just label this as '3'
  mutate(lonely_q = replace_na(lonely_q, 0)) %>% 
  
  # label deciles by whether they're in top 10 then summarise by this label
  tabyl(LAD19CD, lonely_q) %>% 
  
  # calculate proportion of loneliest MSOAs in each LAD
  mutate(Prop_loneliest = `2` / (`0` + `1` + `2`)) %>% 
  
  mutate(Loneliness_q = as.integer(cut2(Prop_loneliest, g = 5))) %>% 
  
  select(LAD19CD, Prop_loneliest, Loneliness_q)

# summarise loneliness risks into wards - calculate proportion of highest-risk MSOAs (in the whole nation) within each ward
lonely_ward_wal = lookup_msoa_ward %>% 
  # get full list of MSOAs
  filter(startsWith(MSOA11CD, "W")) %>% 
  
  # get quantiles for the above-average MSOAs
  left_join(wal_msoa_loneliness_high, by = c("MSOA11CD" = "msoa11cd")) %>% 
  
  # if the MSOA has a quantile of `NA`, it means it's not above average - just label this as '3'
  mutate(lonely_q = replace_na(lonely_q, 0)) %>% 
  
  # label deciles by whether they're in top 10 then summarise by this label
  tabyl(WD17CD, lonely_q) %>% 
  
  # calculate proportion of loneliest MSOAs in each LAD
  mutate(Prop_loneliest = `2` / (`0` + `1` + `2`)) %>% 
  
  mutate(Loneliness_q = calc_risk_quantiles(Prop_loneliest)) %>% 
  mutate(Loneliness_q = ifelse(Prop_loneliest == 1, 5, Loneliness_q)) %>%  # there are only four real clusters of data in Wales, so manually set quintile to 5 is 100% of MSOAs in the ward are lonely
  
  select(WD17CD, Prop_loneliest, Loneliness_q)


##
## Scotland
##
# of the MSOAs with above-average loneliness risks (i.e. `loneills_2018` > 0), cut into two categories: lonely_q = 2 will be highest-risk category
sco_msoa_loneliness_high = sco_msoa_loneliness %>% 
  select(msoa11cd = InterZone, loneills_2018) %>% 
  filter(loneills_2018 > 0) %>% 
  mutate(lonely_q = as.integer(cut2(loneills_2018, g = 2)))

# summarise loneliness risks into LADs - calculate proportion of highest-risk MSOAs (in the whole nation) within each LAD
lonely_sco = lookup_lsoa_msoa_lad %>% 
  # get full list of MSOAs
  filter(startsWith(MSOA11CD, "S")) %>% 
  select(MSOA11CD, LAD17CD) %>% 
  distinct() %>% 
  
  # get 2019 LA codes
  left_join(lad_17_19, by = "LAD17CD") %>% 
  
  # get quantiles for the above-average MSOAs
  left_join(sco_msoa_loneliness_high, by = c("MSOA11CD" = "msoa11cd")) %>% 
  
  # if the MSOA has a quantile of `NA`, it means it's not above average - just label this as '3'
  mutate(lonely_q = replace_na(lonely_q, 0)) %>% 
  
  # label deciles by whether they're in top 10 then summarise by this label
  tabyl(LAD19CD, lonely_q) %>% 
  
  # calculate proportion of loneliest MSOAs in each LAD
  mutate(Prop_loneliest = `2` / (`0` + `1` + `2`)) %>% 
  
  mutate(Loneliness_q = as.integer(cut2(Prop_loneliest, g = 5))) %>% 
  
  select(LAD19CD, Prop_loneliest, Loneliness_q)

# summarise loneliness risks into wards - calculate proportion of highest-risk MSOAs (in the whole nation) within each ward
lonely_ward_sco = lookup_iz_ward %>% 
  # get quantiles for the above-average MSOAs
  left_join(sco_msoa_loneliness_high, by = c("MSOA11CD" = "msoa11cd")) %>% 
  
  # if the MSOA has a quantile of `NA`, it means it's not above average - just label this as '3'
  mutate(lonely_q = replace_na(lonely_q, 0)) %>% 
  
  # label deciles by whether they're in top 10 then summarise by this label
  tabyl(WD17CD, lonely_q) %>% 
  
  # calculate proportion of loneliest MSOAs in each LAD
  mutate(Prop_loneliest = `2` / (`0` + `1` + `2`)) %>% 
  
  mutate(Loneliness_q = calc_risk_quantiles(Prop_loneliest)) %>% 
  
  select(WD17CD, Prop_loneliest, Loneliness_q)


##
## Northern Ireland
##
# of the MSOAs with above-average loneliness risks (i.e. `loneills_2018` > 0), cut into two categories: lonely_q = 2 will be highest-risk category
ni_msoa_loneliness_high = ni_msoa_loneliness %>% 
  select(LSOA11CD = SOA_CODE, loneills_2018) %>% 
  filter(loneills_2018 > 0) %>% 
  mutate(lonely_q      = as.integer(cut2(loneills_2018, g = 2)))

# summarise loneliness risks into LADs - calculate proportion of highest-risk MSOAs (in the whole nation) within each LAD
lonely_ni = lookup_lsoa_lad_ni %>% 
  # get quantiles for the above-average MSOAs
  left_join(ni_msoa_loneliness_high, by = "LSOA11CD") %>% 
  
  # if the MSOA has a quantile of `NA`, it means it's not above average - just label this as '3'
  mutate(lonely_q = replace_na(lonely_q, 0)) %>% 
  
  # label deciles by whether they're in top 10 then summarise by this label
  tabyl(LAD19CD, lonely_q) %>% 
  
  # calculate proportion of loneliest MSOAs in each LAD
  mutate(Prop_loneliest = `2` / (`0` + `1` + `2`)) %>% 
  
  mutate(Loneliness_q = as.integer(cut2(Prop_loneliest, g = 5))) %>% 
  
  select(LAD19CD, Prop_loneliest, Loneliness_q)


##
## combine into UK-wide dataframes
##
lonely_lad = bind_rows(lonely_eng, lonely_wal, lonely_sco, lonely_ni)
write_csv(lonely_lad, "output/loneliness-LA.csv")

lonely_ward = bind_rows(lonely_ward_eng, lonely_ward_wal, lonely_ward_sco)
write_csv(lonely_ward, "output/loneliness-ward.csv")

rm(lookup_lsoa_msoa_lad, lad_17_19, lookup_lsoa_lad_ni, lookup_iz_ward, lookup_msoa_ward,
   eng_msoa_loneliness, eng_msoa_loneliness_high, wal_msoa_loneliness, wal_msoa_loneliness_high, 
   sco_msoa_loneliness, sco_msoa_loneliness_high, ni_msoa_loneliness, ni_msoa_loneliness_high, 
   lonely_eng, lonely_wal, lonely_sco, lonely_ni,
   lonely_ward_eng, lonely_ward_wal, lonely_ward_sco)