Read in UN DESA bilateral stock data (2020) into R

un_stock.R

library(tidyverse)
library(readxl)

download.file(url = "https://www.un.org/development/desa/pd/sites/www.un.org.development.desa.pd/files/undesa_pd_2020_ims_stock_by_sex_destination_and_origin.xlsx", 
              destfile = "undesa_pd_2020_ims_stock_by_sex_destination_and_origin.xlsx",  mode = "wb")

d <- read_excel(path = "undesa_pd_2020_ims_stock_by_sex_destination_and_origin.xlsx", 
                sheet = 2, skip = 10, na = "..", guess_max = 1e5) %>%
  select(-1, -3, -5) %>%
  pivot_longer(cols = -(1:4), names_to = "year", values_to = "stock") %>%
  rename("dest" = 1, 
         "dest_code" = 2,
         "orig" = 3, 
         "orig_code" = 4) %>%
  mutate(sex = rep(c("b", "m", "f"), each = 7, length.out = n()),
         year = str_sub(year, end = 4))
d