prep frailty.r

##
## Prep Dr Foster's Frailty Index - England only
##
library(tidyverse)
library(readxl)

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

##
## load data
##
# can't share this data publicly
frailty = read_excel("C:/Users/040026704/Documents/Data science/Data/NHS/DF Frailty data - BRC 21042020.xlsx", 
                     sheet = "National", col_types = c("text", "text", "text", "numeric", "numeric"))

# LSOA to MSOA lookup table
lookup_lsoa_msoa = load_lookup_lsoa_msoa_lad() %>% 
  select(LSOA11CD, MSOA11CD) %>% 
  distinct()

# aggregate into MSOAs by taking highest frailty proportion in each LSOA
frailty_msoa = frailty %>% 
  left_join(lookup_lsoa_msoa, by = "LSOA11CD") %>% 
  group_by(MSOA11CD) %>% 
  summarise(`Proportion of frail patients` = max(`Proportion of frail patients`, na.rm = TRUE))

# calculate ranks
frailty_msoa$`Frailty rank` = rank2(frailty_msoa$`Proportion of frail patients`)

# save
frailty_msoa %>% 
  select(MSOA11CD, `Frailty rank`) %>% 
  write_csv("data/frailty-MSOA-England.csv")