Code preparing datasets for Welfare and Inequality in Australia EDA.
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
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!