Clean Bancnet ATM municipality name

This notebook attemtps to clean the municipality data in the list of Bancnet ATMs in the Philippines by fuzzy merging with a clean list of municipalities.

Read in data. The clean list of provinces and municipalities comes from a random github user but it appears to be good.

bn <- read_excel(file.path(data_path, "BancNet ATMs as of March 31, 2020.xls"))
colnames(bn) <- tolower(colnames(bn))

git_repo <- "https://raw.githubusercontent.com/clavearnel/philippines-region-province-citymun-brgy/master/csv/"

muni <- read_csv(paste0(git_repo, "refcitymun.csv"))
prov <- read_csv(paste0(git_repo, "refprovince.csv"))
region <- read_csv(paste0(git_repo, "refregion.csv"))

Look at provinces from the bancnet data which don’t match with provinces from the other source.

bn %>% 
  mutate(cityprov = str_trim(toupper(cityprov))) %>% 
  distinct(cityprov) %>% 
  anti_join(prov, by = c("cityprov" = "provDesc"))

# Merge muni with prov to get province name and clean up the province and muni names
# by removing white space and capitalizing
muni_m <- left_join(muni, prov, by = 'provCode') %>% 
  mutate(provDesc= str_trim(toupper(provDesc)), 
         citymunDesc = str_trim(toupper(citymunDesc)))

# Generate unique list of muni names from the bancnet data
bn_muni <- bn %>% select(region, cityprov, district) %>% distinct()

# fuzzy match the bancnet data to the other list
merged <- stringdist_inner_join(bn_muni, muni_m,  by = c("cityprov" = "provDesc",  "district" = "citymunDesc"))

# Drop rows where the bancnet info matches the other source exactly
merged <- merged %>% filter((cityprov != provDesc)  |( district !=citymunDesc))

# Generate new columns based on alphabetic order and then drop pairs of rows
# where A is mapped to B and B to A. 
merged <- merged %>% 
  mutate(first = ifelse(district < citymunDesc, district, citymunDesc),
         second = ifelse(district > citymunDesc, district, citymunDesc))
merged <- merged %>% distinct(cityprov, first, second, .keep_all = TRUE)

# Drop the temp columns
merged <- merged %>% select(-first, -second)
write_csv(merged, file.path(data_path, "candidate_muni_matches.csv"))