Untitled

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")