##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")
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