ons_nonukpopn <- function(url, date, range, type = "new") {
url1 <- url
GET(url1, write_disk(tf <- tempfile(fileext = ".xls")))
df <- read_excel(tf, sheet = "1.1", range)
colnames(df) <- colnames(df) |> stringr::str_replace_all("\n", " ")
df <- if (type == "new") {
df |>
# filter(`Area Code` %in% districts$district_code) |>
select(`Area Code`,
Name,
`United Kingdom Estimate`,
`Non-United Kingdom Estimate`) |>
mutate(across(
c(`United Kingdom Estimate`, `Non-United Kingdom Estimate`),
~ .x %>% as.numeric()
))
} else if (type == "old") {
df |>
select(1:9) |>
filter(if_any(everything(), ~ !is.na(.))) |>
purrr::discard( ~ all(is.na(.))) |>
select(1, `Area Name`, `estimate...6`, `estimate...8`) |>
rename(
"Area Code" = 1,
"United Kingdom Estimate" = "estimate...6",
"Non-United Kingdom Estimate" = "estimate...8"
) |>
# filter(`Area Code` %in% districts$district_code) |>
mutate(across(
c(`United Kingdom Estimate`, `Non-United Kingdom Estimate`),
~ .x %>% as.numeric()
)) |>
mutate(across(
c(`United Kingdom Estimate`, `Non-United Kingdom Estimate`),
~ .x * 1000
))
}
df |>
mutate(Date = date, .before = 1)
}
ons_nonukpopn_list <- list()
ons_nonukpopn_list[["June 2021"]] <- ons_nonukpopn("https://www.ons.gov.uk/file?uri=/peoplepopulationandcommunity/populationandmigration/internationalmigration/datasets/populationoftheunitedkingdombycountryofbirthandnationality/july2020tojune2021/populationbycountryofbirthandnationalityjul20tojun21.xls",
date = "June 2021",
"A5:AS402")