R Markdown

Code preparing datasets for Welfare and Inequality in Australia EDA.

  1. LOCALITY_LGA_2016.xlsx saved to GitHub as my local copy wouldn’t work in Knit
  2. Welfaredata.csv compiled in R using datasets Individual Tax Stats by Territory and Postcode originally from Data.gov.au for 2012-2016 years.

This code also ranks top and bottom Income and Welfare (average) amounts, for comparing best and worst performing (not quite the right word, but it will do) postcodes.

# Load Postcode, State, Locality
#--------------------------------------------------------------------------------------------
urlPC <- "https://github.com/CazMayhem/welfare-proj/blob/master/LOCALITY_LGA_2016.xlsx?raw=true"
p02 <- tempfile()
GET(urlPC, write_disk(p02<- tempfile(fileext = ".xlsx")))
## Response [https://raw.githubusercontent.com/CazMayhem/welfare-proj/master/LOCALITY_LGA_2016.xlsx]
##   Date: 2018-09-30 11:04
##   Status: 200
##   Content-Type: application/octet-stream
##   Size: 1.57 MB
## <ON DISK>  /var/folders/pl/4vb412rs2pd1bn6kf7h3dc840000gn/T//Rtmp7XTz7i/file3c71b071428.xlsx
pc <- as.data.frame(read_excel(p02, sheet = 1, col_names = TRUE))
#convert to data frame
pcDF <- as.data.frame(pc, stringsAsFactors = FALSE)
pcDF2 <- pcDF %>% rename(Postcode = POSTCODE, State = STATE, Locality = LOCALITY_NAME) %>% 
  mutate(Postcode = as.numeric(Postcode))


#shrink down to 1 postcode per row, remove duplicates = 872 = WA, NT, SA
pcDef <- pcDF2 %>% 
  filter(is.na(Postcode)!=TRUE)  %>% 
  select(Postcode, State, Locality) %>% 
  group_by(Postcode) %>% 
  summarise(min(State), min(Locality), n())  %>% 
  rename(State = `min(State)`, Locality = `min(Locality)`) 
pcDef$`n()`  <- NULL

# Read in Welfare Data
#--------------------------------------------------------------------------------------------
data <- read_csv("https://github.com/CazMayhem/welfare-proj/blob/master/welfaredata.csv?raw=true")
## Parsed with column specification:
## cols(
##   .default = col_double(),
##   X1 = col_integer(),
##   SA2 = col_integer(),
##   Year = col_integer(),
##   Postcode = col_integer(),
##   X = col_integer(),
##   State.x = col_character(),
##   No.Population = col_integer(),
##   No.HealthInsurance = col_integer(),
##   No.Tax = col_integer(),
##   No.Salary = col_integer(),
##   Salary = col_integer(),
##   No.Allowances = col_integer(),
##   Allowances = col_integer(),
##   No.Pensions = col_integer(),
##   Pensions = col_integer(),
##   No.Interest = col_integer(),
##   Interest = col_integer(),
##   NoRent = col_integer(),
##   Rent = col_integer(),
##   No.Superannuation = col_integer()
##   # ... with 32 more columns
## )
## See spec(...) for full column specifications.
sub <- c(32:61,8:18,5,2,90)
dataset <- data[, -(sub)]

# remove Infinity values (and beyond....)
d0 <- do.call(data.frame, lapply(dataset, function(x) replace(x, is.infinite(x), NA)))

# add in Total Average Income & Welfare for our graphs
#--------------------------------------------------------------------------------------------
d1 <- d0 %>% rename(State = State.x) %>%
  mutate(AvgIncome = mean(AverageIncome), AvgWelfare = mean(Averagewelfare, na.rm=TRUE))

# find extreme ends of Income/Welfare scale
#--------------------------------------------------------------------------------------------
p1 <- d1 %>% filter(Year==2016) %>% filter(Averagewelfare>=13000) %>%
  select(Year, State, Postcode, Averagewelfare, AverageIncome, AvgIncome, AvgWelfare) %>% mutate(Group="High Welfare") 
p2 <- d1 %>% filter(Year==2016) %>% filter(Averagewelfare<=4500) %>%
  select(Year, State, Postcode, Averagewelfare, AverageIncome, AvgIncome, AvgWelfare) %>% mutate(Group="Low Welfare") 
p3 <- d1 %>% filter(Year==2016) %>% filter(AverageIncome>=105000) %>%
  select(Year, State, Postcode, Averagewelfare, AverageIncome, AvgIncome, AvgWelfare) %>% mutate(Group="High Income") 
p4 <- d1 %>% filter(Year==2016) %>% filter(AverageIncome<=31000) %>%
  select(Year, State, Postcode, Averagewelfare,AverageIncome, AvgIncome, AvgWelfare) %>% mutate(Group="Low Income") 

n <- max(length(p1), length(p2), length(p3), length(p4))

length(p1) <- n
length(p2) <- n
length(p3) <- n
length(p4) <- n

pcBind <- rbind(p1,p2,p3,p4)

pcIn <- pcBind %>% inner_join(pcDef, by = "Postcode") %>% rename(State = State.x)  %>% mutate(Suburb = str_c(Postcode, " ", Locality))
pcIn$`State.y` <- NULL

Including Plots

You can also embed plots, for example:

# Plot Income-Welfare for Australia
#---------------------------------------------------------
pg1 <- d1 %>% 
  filter(Year==2016) %>% 
  #arrange(AverageIncome) %>% 
  #top_n(20, Averagewelfare) %>% 
  ggplot(aes(x = Averagewelfare, y = AverageIncome, colour = State)) + 
  geom_point() +
  geom_hline(aes(yintercept=AvgIncome, color = "red") ) +
  geom_vline(aes(xintercept=AvgWelfare, color = "blue")) +
  scale_x_continuous(name="Average Welfare") +
  scale_y_continuous(name="Average Income", labels=comma) +
  theme(legend.position="bottom") +
  labs(title="Average Income-Welfare Australia", 
       caption="Source: data.gov.au/dss")

# Plot Income-Welfare for High-Low Postcodes
#---------------------------------------------------------
pg2 <- pcIn %>% 
  filter(Year==2016) %>% 
  ggplot(aes(x = Averagewelfare, y = AverageIncome, colour = Group)) + 
  geom_point() +
  geom_hline(aes(yintercept=AvgIncome, color = "red") ) +
  geom_vline(aes(xintercept=AvgWelfare, color = "blue")) +
  scale_x_continuous(name="Average Welfare") +
  scale_y_continuous(name="Average Income", labels=comma) +
  theme(legend.position="bottom") + 
  geom_text_repel(aes(label=Suburb), size=3) +
  labs(title="High-Low Welfare-Income Postcodes", 
       caption="Source: data.gov.au/dss")

theme_set(theme_cowplot(font_size=10)) # reduce default font size
plot_grid(pg1, pg2)

Code for preparing Welfare/Postcode data by Age by State, dataset: - dss-demographics-march-2018.xlsx from Data.gov.au

Data was summarised by Welfare Type for each age group, by each State from left-to-right. Almost 100 columns. Not in a helpful format for data analysis or EDA. To Tidy the data - data had to be grouped for each State, and “shrunk down”, so ACT, then NSW, then VIC, etc… ie: rearrange the table from wide, to narrow Then it is possible to generate an EDA graph, which I have picked Treemap to show the proportions of Welfare Types, and State population demands. Plus it looks cool, and informative which is the main thing.

urlAge <- "https://data.gov.au/dataset/cff2ae8a-55e4-47db-a66d-e177fe0ac6a0/resource/c9bcd6ed-b6bd-423b-abc5-86b54bd7a591/download/dss-demographics-march-2018.xlsx"
p01 <- tempfile()

GET(urlAge, write_disk(tf01 <- tempfile(fileext = ".aspx")))
## Response [https://data.gov.au/dataset/cff2ae8a-55e4-47db-a66d-e177fe0ac6a0/resource/c9bcd6ed-b6bd-423b-abc5-86b54bd7a591/download/dss-demographics-march-2018.xlsx]
##   Date: 2018-09-30 11:04
##   Status: 200
##   Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
##   Size: 1.62 MB
## <ON DISK>  /var/folders/pl/4vb412rs2pd1bn6kf7h3dc840000gn/T//Rtmp7XTz7i/file3c720c89e46.aspx
dsSrc <- as.data.frame(read_excel(tf01, sheet = 7, col_names = TRUE, skip = 2))

# I'm sure this can be done more clean via Functions - but lack of time prevents for now
r0 <- dsSrc[ ,c(1:10)]
rep <- rep("ACT", length(r0[1]))
colnames(r0) <- c("Payment type","0-15","16-20","21-24","25-34","35-44","45-54","55-64","65 and over","Total")
df0 <- as.data.frame(r0%>%mutate(State = rep))
dfNames <- colnames(df0)

r1 <- dsSrc[ ,c(1,11:19)]
rep <- rep("NSW", length(r1[1]))
df1 <- as.data.frame(r1%>%mutate(State = rep))
colnames(df1) <- dfNames

r2 <- dsSrc[ ,c(1,20:28)]
rep <- rep("NT", length(r2[1]))
df2 <- as.data.frame(r2%>%mutate(State = rep))
colnames(df2) <- dfNames

r3 <- dsSrc[ ,c(1,29:37)]
rep <- rep("QLD", length(r3[1]))
df3 <- as.data.frame(r3%>%mutate(State = rep))
colnames(df3) <- dfNames

r4 <- dsSrc[ ,c(1,38:46)]
rep <- rep("SA", length(r4[1]))
df4 <- as.data.frame(r4%>%mutate(State = rep))
colnames(df4) <- dfNames

r5 <- dsSrc[ ,c(1,47:55)]
rep <- rep("TAS", length(r5[1]))
df5 <- as.data.frame(r5%>%mutate(State = rep))
colnames(df5) <- dfNames

r6 <- dsSrc[ ,c(1,56:64)]
rep <- rep("VIC", length(r6[1]))
df6 <- as.data.frame(r6%>%mutate(State = rep))
colnames(df6) <- dfNames

r7 <- dsSrc[ ,c(1,65:73)]
rep <- rep("WA", length(r7[1]))
df7 <- as.data.frame(r7%>%mutate(State = rep))
colnames(df7) <- dfNames

r8 <- dsSrc[ ,c(1,74:82)]
rep <- rep("Unknown", length(r8[1]))
df8 <- as.data.frame(r8%>%mutate(State = rep))
colnames(df8) <- dfNames

r9 <- dsSrc[ ,c(1,83:91)]
rep <- rep("AUS", length(r9[1]))
df9 <- as.data.frame(r9%>%mutate(State = rep))
colnames(df9) <- dfNames

#join all individual data frames  
dfAge <- rbind(df1, df2, df3, df4, df5, df6, df7, df8, df9 )

#delete column headings from source data
dfAge <- dfAge %>% filter(`Payment type`!="Payment type") %>%
  rename(`Welfare Category number of Individuals by State` = Total)

#fix dodgy values
dfAge[dfAge=="<5"]<-5
dfAge[dfAge=="n.p."]<-0
dfAge[dfAge=="n/a"]<-0
dfAge[is.na(dfAge)==TRUE]<-0

#convert all number columns to ...numeric
dfAge[,2:10] <- sapply(dfAge[,2:10], as.numeric)

#Add new column which will represent Welfare Categories/Type
dfAge <- dfAge %>% mutate(WelfareType = `Payment type`)
 
#Temp DF store for now, while we work on Welfare Type
dfTmp <- dfAge 
dfTmp$WelfareType[dfTmp$`Payment type`=="ABSTUDY (Living Allowance)"]<-"ABSTUDY"
dfTmp$WelfareType[dfTmp$`Payment type`=="ABSTUDY (Non-Living Allowance)"]<-"ABSTUDY"
dfTmp$WelfareType[dfTmp$`Payment type`=="Carer Allowance"]<-"Carer Payment"
dfTmp$WelfareType[dfTmp$`Payment type`=="Carer Payment"]<-"Carer Payment"
dfTmp$WelfareType[dfTmp$`Payment type`=="Family Tax Benefit A"]<-"Family Tax Benefit"
dfTmp$WelfareType[dfTmp$`Payment type`=="Age Pension"]<-"Age Pension"
dfTmp$WelfareType[dfTmp$`Payment type`=="Disability Support Pension"]<-"Disability Pension"
dfTmp$WelfareType[dfTmp$`Payment type`=="Sickness Allowance"]<-"Disability Pension"
dfTmp$WelfareType[dfTmp$`Payment type`=="Newstart Allowance"]<-"Newstart Allowance"
dfTmp$WelfareType[dfTmp$`Payment type`=="Parenting Payment Partnered"]<-"Parenting Payment"
dfTmp$WelfareType[dfTmp$`Payment type`=="Parenting Payment Single"]<-"Parenting Payment"
dfTmp$WelfareType[dfTmp$`Payment type`=="Widow Allowance"]<-"Widow Pension"
dfTmp$WelfareType[dfTmp$`Payment type`=="Widow B Pension"]<-"Widow Pension"
dfTmp$WelfareType[dfTmp$`Payment type`=="Wife Pension (Partner on Age Pension)"]<-"Disability Pension"
dfTmp$WelfareType[dfTmp$`Payment type`=="Wife Pension (Partner on Disability Support Pension)"]<-"Widow Pension"
dfTmp$WelfareType[dfTmp$`Payment type`=="Youth Allowance (other)"]<-"Youth Allowance"
dfTmp$WelfareType[dfTmp$`Payment type`=="Youth Allowance (student and apprentice)"]<-"Youth Allowance"

#Now save only the rows we want, eg: exclude Health Care Card
dfAge <- dfTmp %>% filter(WelfareType=="ABSTUDY" |
                             WelfareType=="Carer Payment" |  
                             WelfareType=="Family Tax Benefit" |
                             WelfareType=="Age Pension" |
                             WelfareType=="Newstart Allowance"  |
                             WelfareType=="Parenting Payment"  |
                             WelfareType=="Widow Pension"  |
                             WelfareType=="Disability Pension"  |
                             WelfareType=="Youth Allowance")
#View(dfAge)

Code for showing Welfare number splits by Category across each State - using a cool Treemap. A Treemap graph is pretty easy. The hard part was wrangling the data into a Tidy format.

# treemap
#----------------------------------------------------------
dfAge %>% filter(State!="AUS", State!="Unknown") %>%
  treemap(index=c("WelfareType","State"), 
          vSize="Welfare Category number of Individuals by State", 
          type="index", 
          palette="Set1",
          fontsize.labels=c(15,12),                # size of labels. Give the size per level of aggregation: size for group, size for subgroup, sub-subgroups...
          fontcolor.labels=c("black","white"),    # Color of labels
          fontface.labels=c(2,2),                  # Font of labels: 1,2,3,4 for normal, bold, italic, bold-italic...
          align.labels=list(
            c("left", "top"), 
            c("right", "bottom")
          ),                                       # Where to place labels in the rectangle?
          overlap.labels=0.5,                      # number between 0 and 1 that determines the tolerance of the overlap between labels. 0 means that labels of lower levels are not printed if higher level labels overlap, 1  means that labels are always printed. In-between values, for instance the default value .5, means that lower level labels are printed if other labels do not overlap with more than .5  times their area size.
          inflate.labels=F              
)

options(warn = oldw)

The end!