## making into taxonomy format...

library(pacman)
p_load(here,dplyr,readr,stringr,readxl,tidyr)
here()

dat = read_xlsx(here("journal.pntd.0006118.s003.xlsx")) %>% 
  rename(ISO_A2_L1_name=district) %>% select(ISO_A2_L1_name,starts_with("cas1")) %>% 
  mutate(ISO_A2_L1_name = recode(ISO_A2_L1_name,"Ssembabule" = "Sembabule",
                                 "Bukomansimbi" = "Bukomansibi"))
lu = read_xlsx(here("look_up.xlsx"))

##check all match
left_join(dat,lu) %>% filter(is.na(ISO_A2_L1))

dat = left_join(dat,lu) %>% 
  dplyr::select(-`Parent subdivision`,-`Subdivision category`) %>%
  gather(year,sCh,-ISO_A2_L1_name,-ISO_A2_L1) %>% 
  mutate(year = str_replace_all(year,"cas","20") %>% as.numeric) 

## make TL-TR and other variables
dat = dat %>% mutate(TL = paste0(year,"-01-01"),TR = paste0(year,"-12-31"),
                     who_region="AFR",ISO_A1="UGA",Primary=TRUE,Phantom=NA)

## adding phantom observations here since some districts may have been added and want to 
## make clear this is meant to represent all of Uganda
rc = bind_rows(dat,
          dat %>% group_by(year,TL,TR,who_region,ISO_A1,Primary) %>% summarize(sCh=sum(sCh)) %>% mutate(Phantom=TRUE))

write_csv(rc,here("5015.csv"),na="")
