prep older population.r

##
## Over-70s populations in Local Authorities, Local Resilience FOrums, 
##
## source: Estimates of the population for the UK, England and Wales, Scotland and Northern Ireland
## data: https://www.ons.gov.uk/peoplepopulationandcommunity/populationandmigration/populationestimates/datasets/populationestimatesforukenglandandwalesscotlandandnorthernireland
##
library(tidyverse)
library(readxl)
library(Hmisc)
library(httr)

source("load lookup tables.r")

# get LA names and codes
lad_names = load_lads(2019)

##
## download from ONS
##
pop_url = "https://www.ons.gov.uk/file?uri=%2fpeoplepopulationandcommunity%2fpopulationandmigration%2fpopulationestimates%2fdatasets%2fpopulationestimatesforukenglandandwalesscotlandandnorthernireland%2fmid20182019laboundaries/ukmidyearestimates20182019ladcodes.xls"
GET(pop_url, write_disk(tf <- tempfile(fileext = ".xls")))

pop = read_excel(tf, sheet = "MYE2-All", skip = 3)

unlink(tf); rm(tf)

##
## count number of over-70s in each LA
##
pop_70_lad = pop %>% 
  # keep only Local Authorities
  filter(Code %in% lad_names$LAD19CD) %>% 
  
  # convert age columns to long format
  pivot_longer(cols = `0`:`90`, names_to = "Age", values_to = "n") %>% 
  mutate(Age = as.integer(Age)) %>% 
  
  # keep only over-70s
  filter(Age >= 70) %>% 
  
  # sum
  group_by(Code) %>% 
  summarise(`No. people over 70` = sum(n)) %>% 
  
  # bucket into quintiles
  mutate(Over70_q = as.integer(cut2(`No. people over 70`, g = 5))) %>% 
  rename(LAD19CD = Code)

write_csv(pop_70_lad, "output/population-LA.csv")

rm(lad_names, pop_url, pop)