south_africa.R

## Mapping household income by ward in South Africa - data from the 2011 Census

## Author: Kyle Walker.  Please share and re-use as much as you'd like!

library(readxl)
library(rgdal)
library(dplyr)
library(tidyr)
library(magrittr)

# Data source: http://africaopendata.org/dataset/census-2011/resource/cad44a9f-a4f4-4fcc-8f6e-c73940da1835

xl <- "annual-household-income-for-the-national-province-district-municipality-ward.xls"

dat <- read_excel(xl, skip = 6)

nms <- c('ward', paste0('i', sapply(1:12, as.character)))

names(dat) <- nms


wards <- grep("Ward", dat$ward)

dat2 <- dat[wards, ]

dat2[, 11:13] <- sapply(dat2[, 11:13], as.numeric)

dat2 %<>% separate(ward, c("ward_id", "extra"), sep = ":", )

dat2$ward_id <- gsub(" ", "", dat2$ward_id, fixed = TRUE)
  
# pctlow: percent of households earning under R38200 annually (income bins 1-5). 
# This cut-off was chosen due to the way the data were aggregated; 
# the average household income for poor households in South Africa is R32911. 
# See http://beta2.statssa.gov.za/publications/Report-03-10-06/Report-03-10-06March2014.pdf


dat2 %<>%  
  mutate(
    lowinc = i1 + i2 + i3 + i4 + i5, 
    totalhh = rowSums(dat2[, 3:14], na.rm = TRUE)
  ) %>%
  mutate(pctlow = round(100 * (lowinc / totalhh), 2)) %>%
  arrange(ward_id)

    
# Spatial data source: http://www.demarcation.org.za/index.php/downloads/boundary-data/boundary-data-main-files/wards
# Polygons simplified in QGIS before importing into R.  

sp_wards <- readOGR(dsn = getwd(), layer = "sa_0001", stringsAsFactors = FALSE)

sp_wards@data <- merge(sp_wards@data, dat2, 
                     by.x = "WARD_ID", by.y = "ward_id", sort = FALSE)

writeOGR(sp_wards, dsn = getwd(), layer = "sa_wards", 
         driver = "ESRI Shapefile", overwrite_layer =  TRUE)


# Zip up your shapefile and upload to CartoDB!
# CartoDB map: http://kwalkertcu.cartodb.com/viz/31b05dbe-d334-11e4-ace7-0e0c41326911/embed_map