##Load Payment Demographic 2013

urldss0 <- "https://data.gov.au/dataset/cff2ae8a-55e4-47db-a66d-e177fe0ac6a0/resource/6b2343f4-09d3-4b03-a1fe-41897f710c6c/download/dssdemographicsdecember2013.xlsx"
p7 <- tempfile()

GET(urldss0, write_disk(tf7 <- tempfile(fileext = ".xlsx")))
## Response [https://data.gov.au/dataset/cff2ae8a-55e4-47db-a66d-e177fe0ac6a0/resource/6b2343f4-09d3-4b03-a1fe-41897f710c6c/download/dssdemographicsdecember2013.xlsx]
##   Date: 2018-09-19 13:58
##   Status: 200
##   Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
##   Size: 668 kB
## <ON DISK>  /var/folders/pl/4vb412rs2pd1bn6kf7h3dc840000gn/T//RtmpVtcmeB/file11627a2df109.xlsx
dss2013 <- as.data.frame(read_excel(tf7, sheet = 7, col_names = TRUE, skip = 2))
## Warning in read_fun(path = path, sheet_i = sheet, limits = limits, shim =
## shim, : Expecting numeric in A2909 / R2909C1: got 'Other'
## Warning in read_fun(path = path, sheet_i = sheet, limits = limits, shim =
## shim, : Expecting numeric in A2910 / R2910C1: got 'Australia'
d2013 <- rep("2013", length(dss2013$Postcode))

dss2013 <- dss2013%>%mutate(Year = 2013)

names2013 <- colnames(dss2013)

##Load Payment Demographic 2014
##Load Payment Demographic 2015
##Load Payment Demographic 2016

Welfare data from data.gov.au - department of social security statistics 2013-2016 - total number across all Welfare categories

glimpse(table3)
## Observations: 11,619
## Variables: 29
## $ Postcode                                               <chr> "200", ...
## $ `ABSTUDY (Living allowance)`                           <dbl> 10, 10,...
## $ `ABSTUDY (Non-living allowance)`                       <dbl> 10, 10,...
## $ `Age Pension`                                          <dbl> 10, 171...
## $ Austudy                                                <dbl> 10, 10,...
## $ `Carer Allowance`                                      <dbl> 10, 20,...
## $ `Carer Allowance (Child Health Care Card only)`        <dbl> 10, 10,...
## $ `Carer Payment`                                        <dbl> 10, 10,...
## $ `Commonwealth Seniors Health Card`                     <dbl> 10, 34,...
## $ `Double Orphan Pension`                                <dbl> 10, 10,...
## $ `Disability Support Pension`                           <dbl> 10, 295...
## $ `Family Tax Benefit Part A`                            <dbl> 10, 135...
## $ `Family Tax Benefit Part B`                            <dbl> 10, 122...
## $ `Health Care Card`                                     <dbl> 10, 308...
## $ `Low Income Card`                                      <dbl> 10, 22,...
## $ `Newstart Allowance`                                   <dbl> 10, 224...
## $ `Parenting Payment (Partnered)`                        <dbl> 10, 10,...
## $ `Parenting Payment (Single)`                           <dbl> 10, 27,...
## $ `Partner Allowance`                                    <dbl> 10, 10,...
## $ `Pensioner Concession Card`                            <dbl> 10, 505...
## $ `Sickness Allowance`                                   <dbl> 10, 10,...
## $ `Special Benefit`                                      <dbl> 10, 10,...
## $ `Wife Pension (Partner on Age Pension)`                <dbl> 10, 10,...
## $ `Wife Pension (Partner on Disability Support Pension)` <dbl> 10, 10,...
## $ `Widow Allowance`                                      <dbl> 10, 10,...
## $ `Widow B Pension`                                      <dbl> 10, 10,...
## $ `Youth Allowance (other)`                              <dbl> 10, 10,...
## $ `Youth Allowance (student and apprentice)`             <dbl> 10, 10,...
## $ Year                                                   <dbl> 2013, 2...
# get total welfare count - sum of all welfare count columns, return just postcode and total count
tb3 <- table3 %>%
  mutate(Postcode2=ifelse(str_length(as.numeric(Postcode))<=3,str_c("0",Postcode),Postcode)) %>%
  mutate(Total=rowSums(.[2:28], na.rm = TRUE)) %>%
  select(Postcode2, Total) %>%
  rename(postcode = Postcode2)

# read PostCodes - convert to 4 length char, eg: 200 => 0200
pcRead <- read_tsv("https://raw.githubusercontent.com/CazMayhem/welfare-proj/master/auspostcodes.txt", col_names=FALSE)  
## Parsed with column specification:
## cols(
##   X1 = col_character(),
##   X2 = col_character(),
##   X3 = col_character(),
##   X4 = col_character(),
##   X5 = col_character(),
##   X6 = col_character(),
##   X7 = col_character(),
##   X8 = col_character(),
##   X9 = col_character(),
##   X10 = col_double(),
##   X11 = col_double(),
##   X12 = col_integer()
## )
# add column names
colnames(pcRead) <- c("country", "postcode", "suburb", "state desc", "state", "locality", "7", "8", "9", "lat", "long", "class")

# extract single-row postcode, state, lat, long (co-ordinates)
pc <- pcRead %>%
  group_by(postcode, state, lat, long) %>%
  filter(!is.na(lat), !is.na(long)) %>%
  summarise(n = n(), is.na=TRUE)
pc
## # A tibble: 13,554 x 6
## # Groups:   postcode, state, lat [?]
##    postcode state   lat  long     n is.na
##    <chr>    <chr> <dbl> <dbl> <int> <lgl>
##  1 0200     ACT   -35.3  149.     1 TRUE 
##  2 0221     ACT   -35.3  149.     1 TRUE 
##  3 0800     NT    -12.5  131.     1 TRUE 
##  4 0801     NT    -12.5  131.     1 TRUE 
##  5 0803     NT    -12.4  131.     1 TRUE 
##  6 0804     NT    -12.4  131.     1 TRUE 
##  7 0810     NT    -12.4  131.     1 TRUE 
##  8 0810     NT    -12.4  131.     1 TRUE 
##  9 0810     NT    -12.4  131.     1 TRUE 
## 10 0810     NT    -12.4  131.     1 TRUE 
## # ... with 13,544 more rows
# join welfare count data to postcode coordinates
tb3PC <- tb3 %>%
  left_join(pc, by = "postcode")

Including Plots

Lets view Total Welfare by Post Code:

## Warning in validateCoords(lng, lat, funcName): Data contains 145 rows with
## either missing or invalid lat/lon values and will be ignored