```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE, eval = FALSE)

# Nomis API key needed to return over 25,000 rows
api_key <- ""
```

Retrieving mid-2020 population estimates for Trafford by different administrative and statistical geographies.    


|Indicator |Period |Geography |Source |Updated |Licence |
|---|---|---|---|---|---|
|Population estimates |Mid-2020 |Local authority |<a href="https://www.nomisweb.co.uk/query/construct/summary.asp?mode=construct&version=0&dataset=2002" target="_blank">ONS/Nomis</a> |2020-09-16 |<a href="http://www.nationalarchives.gov.uk/doc/open-government-licence/version/3/" target="_blank">OGL v3.0</a> |
|Population estimates |Mid-2020 |Electoral ward |<a href="https://www.nomisweb.co.uk/query/construct/summary.asp?mode=construct&version=0&dataset=2010" target="_blank">ONS/Nomis</a> |2020-09-16 |<a href="http://www.nationalarchives.gov.uk/doc/open-government-licence/version/3/" target="_blank">OGL v3.0</a> |
|Population estimates |Mid-2020 |Middle-layer Super Output Area |<a href="https://www.nomisweb.co.uk/query/construct/summary.asp?mode=construct&version=0&dataset=2010" target="_blank">ONS/Nomis</a> |2020-09-16 |<a href="http://www.nationalarchives.gov.uk/doc/open-government-licence/version/3/" target="_blank">OGL v3.0</a> |
|Population estimates |Mid-2020 |Lower-layer Super Output Area |<a href="https://www.nomisweb.co.uk/query/construct/summary.asp?mode=construct&version=0&dataset=2010" target="_blank">ONS/Nomis</a> |2020-09-16 |<a href="http://www.nationalarchives.gov.uk/doc/open-government-licence/version/3/" target="_blank">OGL v3.0</a> |
|Population estimates |Mid-2020 |Output Area |<a href="https://www.nomisweb.co.uk/query/construct/summary.asp?mode=construct&version=0&dataset=2010" target="_blank">ONS/Nomis</a> |2020-09-16 |<a href="http://www.nationalarchives.gov.uk/doc/open-government-licence/version/3/" target="_blank">OGL v3.0</a> |

#### Load libraries
```{r}
library(tidyverse) ; library(lubridate)
```

#### Retrieve data
```{r}
# MSOA names
# Source: House of Commons Library
# URL: https://houseofcommonslibrary.github.io/msoanames
msoa_names <- read_csv("https://houseofcommonslibrary.github.io/msoanames/MSOA-Names-1.14.csv") %>% 
  select(msoa11cd, msoa11hclnm)

# Local authority
la <- read_csv("http://www.nomisweb.co.uk/api/v01/dataset/NM_2002_1.data.csv?geography=1820327969&date=latest&gender=0...2&c_age=101...191&measures=20100&select=date_name,geography_name,geography_code,gender_name,c_age_name,measures_name,obs_value,obs_status_name") %>% 
  mutate(geography = "Local authority")

# Electoral ward
ward <- read_csv("http://www.nomisweb.co.uk/api/v01/dataset/NM_2010_1.data.csv?geography=1660945005...1660945019,1660945021,1660945020,1660945022...1660945025&date=latest&gender=0...2&c_age=101...191&measures=20100&select=date_name,geography_name,geography_code,gender_name,c_age_name,measures_name,obs_value,obs_status_name") %>% 
  mutate(geography = "Electoral ward")

# Middle-layer Super Output Area
msoa <- read_csv("http://www.nomisweb.co.uk/api/v01/dataset/NM_2010_1.data.csv?geography=1245709510...1245709537&date=latest&gender=0...2&c_age=101...191&measures=20100&select=date_name,geography_name,geography_code,gender_name,c_age_name,measures_name,obs_value,obs_status_name") %>% 
  left_join(msoa_names, by = c("GEOGRAPHY_CODE" = "msoa11cd")) %>%
  mutate(GEOGRAPHY_NAME = msoa11hclnm,
         geography = "MSOA") %>% 
  select(-msoa11hclnm)

# Lower-layer Super Output Area
lsoa <- read_csv(paste0("http://www.nomisweb.co.uk/api/v01/dataset/NM_2010_1.data.csv?uid=", api_key, "&geography=1249908541...1249908544,1249908617,1249908620,1249908548...1249908551,1249908553,1249908573,1249908618,1249908619,1249908621,1249908545...1249908547,1249908577,1249908578,1249908554...1249908556,1249908560,1249908563,1249908587,1249908589,1249908591,1249908614,1249908615,1249908557...1249908559,1249908562,1249908564,1249908588,1249908590,1249908611,1249908616,1249908630...1249908634,1249908552,1249908561,1249908565,1249908629,1249908635,1249908574...1249908576,1249908612,1249908613,1249908579,1249908581,1249908582,1249908586,1249908597,1249908598,1249908601...1249908603,1249908530,1249908531,1249908596,1249908606,1249908610,1249908529,1249908592...1249908595,1249908580,1249908583...1249908585,1249908604,1249908536...1249908540,1249908534,1249908605,1249908607...1249908609,1249908523,1249908524,1249908527,1249908599,1249908600,1249908522,1249908526,1249908528,1249908532,1249908535,1249908533,1249908622,1249908627,1249908628,1249908642,1249908636,1249908638...1249908640,1249908643,1249908525,1249908623,1249908624,1249908637,1249908641,1249908510,1249908512,1249908521,1249908625,1249908626,1249908506...1249908508,1249908511,1249908519,1249908515,1249908516,1249908520,1249908571,1249908572,1249908509,1249908513,1249908514,1249908517,1249908518,1249908566...1249908570&date=latest&gender=0...2&c_age=101...191&measures=20100&select=date_name,geography_name,geography_code,gender_name,c_age_name,measures_name,obs_value,obs_status_name")) %>% 
  mutate(geography = "LSOA")

# Output Area
oa <- read_csv(paste0("http://www.nomisweb.co.uk/api/v01/dataset/NM_2010_1.data.csv?uid=", api_key, "&geography=1254126722...1254127431,1254260803...1254260823&date=latest&gender=0...2&c_age=101...191&measures=20100&select=date_name,geography_name,geography_code,gender_name,c_age_name,measures_name,obs_value,obs_status_name")) %>% 
  mutate(geography = "OA")
```

#### Tidy data
```{r}
all_geographies <- bind_rows(la, ward, msoa, lsoa, oa) %>% 
  select(period = DATE_NAME,
         area_code = GEOGRAPHY_CODE,
         area_name = GEOGRAPHY_NAME,
         gender = GENDER_NAME,
         age = C_AGE_NAME,
         count = OBS_VALUE,
         geography) %>% 
  mutate(period = ymd(str_c(period, "06-30", sep = "-")),
         gender = fct_recode(gender, "Females" = "Female" , "Males" = "Male", "Persons" = "Total"),
         age = as.integer(str_trim(str_replace_all(age, "Age.|\\+", ""))))  %>% 
  spread(age, count) %>% 
  mutate(all_ages = rowSums(select(., `0`:`90`)),
         aged_0_to_15 = rowSums(select(., `0`:`15`)),
         aged_16_to_64 = rowSums(select(., `16`:`64`)),
         aged_65_and_over = rowSums(select(., `65`:`90`))) %>% 
  select(period, area_code, area_name, geography, gender, all_ages, aged_0_to_15, aged_16_to_64, aged_65_and_over, everything()) 
```

#### Write data
```{r}
write_csv(filter(all_geographies, geography == "Local authority"), 
          "mid-year_population_estimates_local_authority.csv")

write_csv(filter(all_geographies, geography == "Electoral ward"), 
          "mid-year_population_estimates_ward.csv")

write_csv(filter(all_geographies, geography == "MSOA"), 
          "mid-year_population_estimates_msoa.csv")

write_csv(filter(all_geographies, geography == "LSOA"), 
          "mid-year_population_estimates_lsoa.csv")

write_csv(filter(all_geographies, geography == "OA"), 
          "mid-year_population_estimates_oa.csv")

write_csv(all_geographies, "mid-year_population_estimates_all_geographies.csv")
```

