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

Updated spatial analysis functionality using ‘sf’ instead of ‘rgal’.

This analysis aims to compare welfare inequality by region. Is there any impacted based on where someone lives? We can look at postcode and LGA data.

### 2016 Population, Income, Tax, Pensions, Allowances by Postcode
### --------------------------------------------------------------------------------------------
urlTax <- "https://data.gov.au/dataset/d170213c-4391-4d10-ac24-b0c11768da3f/resource/8c86b6d5-0485-4387-b12b-ad26a9da2033/download/taxstats2016individual06taxablestatusstateterritorypostcodetaxableincome.xlsx"
p01 <- tempfile()
GET(urlTax, write_disk(p01<- tempfile(fileext = ".xlsx")))
## Response [https://data.gov.au/data/dataset/d170213c-4391-4d10-ac24-b0c11768da3f/resource/8c86b6d5-0485-4387-b12b-ad26a9da2033/download/taxstats2016individual06taxablestatusstateterritorypostcodetaxableincome.xlsx]
##   Date: 2024-03-05 01:55
##   Status: 200
##   Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
##   Size: 8.56 MB
## <ON DISK>  /var/folders/9v/0xb5nw2d62j80bcqmrgf9bnr0000gn/T//RtmpT2rvG8/file326461ab668a.xlsx
# read in 2016 tax data
dTaxIn <- as.data.frame(read_excel(p01, sheet = 3, col_names = TRUE, skip = 2))

# select required columns
dTaxn <- dTaxIn[, c(1,2,3,18,19,28,29,30,31,100,101,140)]
colnames(dTaxn) <- c("State","Postcode","Population.No","Salary.No","Salary",
                     "Allowances.No", "Allowances", "Pensions.No", "Pensions",
                     "Rent.No", "Rent","Private.Health.No")

# create Year value across all data rows
rep2016 <- rep("2016", length(dTaxn$State))

# add the Year column
# remove postcode rows that are not numeric, contain text 'other|Overseas'
# convert Postcode to numeric
dTax <- dTaxn %>% mutate(Year = rep2016) %>% 
        filter(!grepl('other|Overseas', Postcode)) %>% 
        mutate(Postcode=as.character(Postcode))

# create list of postcoces
dAusPC <- dTax[, c('Postcode')]

# Calc Avg $Allowance per Postcode - totals are not suitable
dTax0 <- dTax %>% filter(substr(State,1,4)!="Over") %>% 
         mutate(`Avg Allowance` = ifelse(Allowances.No==0, 0, Allowances/Allowances.No),
                `Avg Pension` = ifelse(Pensions.No==0, 0, Pensions/Pensions.No),
                `Avg Income` = ifelse(Salary.No==0, 0, Salary/Salary.No),
                `Avg Rent` = ifelse(Rent.No==0, 0, Rent/Rent.No),
                Income.PC = Salary.No/Population.No*100,
                Allowances.PC = Allowances.No/Population.No*100,
                Pension.PC = Pensions.No/Population.No*100,
                Rent.PC = Rent.No/Population.No*100,
                PrivateHealth.PC = Private.Health.No/Population.No*100)
# Plus Avg Welfare
dTax0 <- dTax0 %>% mutate(`Avg Welfare`= `Avg Allowance`+`Avg Pension`)

# extract TAS for geospatial analysis, remove redundant variables
dTax <- dTax0 %>% filter(State=="TAS")
dTax$Year<-NULL

2016 taxation data prepared for this analysis, initial detailed plus average for comparison.

str(dTax)
## 'data.frame':    110 obs. of  22 variables:
##  $ State            : chr  "TAS" "TAS" "TAS" "TAS" ...
##  $ Postcode         : chr  "7000" "7004" "7005" "7007" ...
##  $ Population.No    : num  8750 4638 8396 1873 7282 ...
##  $ Salary.No        : num  6909 3406 5833 1442 5872 ...
##  $ Salary           : num  3.89e+08 1.95e+08 3.34e+08 8.88e+07 3.28e+08 ...
##  $ Allowances.No    : num  896 415 570 156 580 ...
##  $ Allowances       : num  5638061 2762675 3701935 1026966 3658769 ...
##  $ Pensions.No      : num  400 296 477 96 444 433 594 590 23 557 ...
##  $ Pensions         : num  3521239 2704570 4339290 863963 4427734 ...
##  $ Rent.No          : num  1345 775 1407 290 992 ...
##  $ Rent             : num  25778889 15598467 33456219 4129448 16542482 ...
##  $ Private.Health.No: num  643 381 583 121 459 394 365 309 34 358 ...
##  $ Avg Allowance    : num  6292 6657 6495 6583 6308 ...
##  $ Avg Pension      : num  8803 9137 9097 9000 9972 ...
##  $ Avg Income       : num  56280 57195 57187 61556 55831 ...
##  $ Avg Rent         : num  19166 20127 23778 14239 16676 ...
##  $ Income.PC        : num  79 73.4 69.5 77 80.6 ...
##  $ Allowances.PC    : num  10.24 8.95 6.79 8.33 7.96 ...
##  $ Pension.PC       : num  4.57 6.38 5.68 5.13 6.1 ...
##  $ Rent.PC          : num  15.4 16.7 16.8 15.5 13.6 ...
##  $ PrivateHealth.PC : num  7.35 8.21 6.94 6.46 6.3 ...
##  $ Avg Welfare      : num  15096 15794 15592 15583 16281 ...

Previous analysis excluded Postcode from the model because it was categorical, so that each data row remained independent. I would like to extend the original analysis by re-introducing Postcode and LGA data to investigate if detailed geographic location influences welfare dependency. Furthermore, by adding Qualification data into this analysis, I would like to investigate if there is a reduction of unemployment in these areas where there are higher qualified people.

# select required columns for simple scatterplot
pt <- dTax0[, c('Postcode', 'State', 'Avg Income', 'Avg Welfare')]

AusIncomeAvg <- mean(dTax0$`Avg Income`)
AusWelfareAvg <- mean(dTax0$`Avg Pension` + dTax0$`Avg Allowance`)

# Scatterplot - Average Income/Welfare in Australia
ggplot(pt, aes(x=`Avg Welfare`, y=`Avg Income`, color=State)) +
  geom_point() + 
  ggtitle("Average Income/Welfare in Australia") +
  geom_hline(yintercept=AusIncomeAvg, color = "green") + 
  geom_vline(xintercept=AusWelfareAvg, color = "orange") +
  scale_y_continuous(label=comma) +
  scale_x_continuous(label=comma) + 
  theme(plot.title = element_text(hjust = 0.5)) +
  theme(panel.background = element_blank()) + 
  theme(panel.border = element_blank(), 
        panel.grid.major = element_blank(),
        panel.grid.minor = element_blank())

Introduce spatial analysis libraries

Analysis updated to use ‘sf’ package: simple features support, a standardized way for encoding spatial vector data.

https://r-spatial.org/book/07-Introsf.html

setwd("/Users/paipac/Documents/Data/")

# Extract out lat / lon co-ordinates by Postcode - using sf: objects with simple features
# https://r-spatial.github.io/sf/articles/sf1.html
kml.pc <- read_sf("AustralianPostcodesSimplified.kml.xml", "AustralianPostcodesSimplified1")
p <- kml.pc

# drop Z or M dimensions
pzm <- st_zm(p)

# find the geometric center of each postcode geometry
pct <- st_centroid(pzm)

# convert sf to dataframe
pzm_df <- as.data.frame(pzm)
pct_df <- as.data.frame(pct)

# get the x, y co-ordinates only
pct_xy <- as.data.frame(st_coordinates(st_geometry(pct)))
pct_xy <- cbind(pct_df, pct_xy) %>%
          rename(Postcode=Name, lon=X, lat=Y) 
          # %>% select(Postcode, lon, lat)

# calculate the Avg Welfare size for our points
ptsMean <- inner_join(dTax0, pct_xy, by="Postcode") %>%
                      mutate(avg_welf_val = `Avg Welfare`/1000)

Income-Welfare inequality by postcode

Australian postcodes with below average low incomes <$47k and above average welfare >17k (pensions, newstart)

# filter below average income and above average welfare - 1/4 number of rows
ptsMean <- ptsMean %>% filter(`Avg Income`<47000 & `Avg Welfare`>17236)

# convert dataframe to sf object
ptsMean_sf <- st_as_sf(ptsMean)

# show map of Australia - similar to GoogleMaps original version
mapview(ptsMean_sf["Postcode"], 
        map.types = 'OpenStreetMap',
        alpha.regions = 0.5, 
        cex = 6,   
        fgb = FALSE, legend = FALSE)

Qualification data and inequality

Investigate if having a qualification has any effect on inequality.

https://leaflet-extras.github.io/leaflet-providers/preview/

setwd("/Users/paipac/Documents/Data/")

# Qualification by LGA data (no lat/long - need to get these)
rQual <- read_csv("LGA_Qualification_2016_TAS.csv")
## Rows: 28 Columns: 25
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## dbl (25): f_advdip_and_dip_total, m_cert_lev_tot_total, m_bachdeg_total, f_c...
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
# fix lga_code
dTmp <- as.data.frame(rQual) %>% rename(lga_code = lga_code_2016) %>% mutate(lga_code = as.character(lga_code))

# reduce columns
dQual <- dTmp[,c(9,1,10,13,20,23,4,11,17,24,14,3,5,12,16,25,2,6,22)]
OA.Qualify <- dQual %>% mutate(f_cert=f_cert_i_ii_total+f_cert_iii_iv_total+f_cert_lev_tot_total+f_cert_levl_nfd_total,
                               m_cert=m_cert_i_ii_total+m_cert_iii_iv_total+m_cert_lev_tot_total+m_cert_levl_nfd_total,
                               f_grad=f_advdip_and_dip_total+f_bachdeg_total+f_graddip_and_gradcert_total+f_pgrad_deg_total,
                               m_grad=m_advdip_and_dip_total+m_bachdeg_total+m_graddip_and_gradcert_total+m_pgrad_deg_total) %>%
              mutate(cert_total=f_cert+m_cert, grad_total=f_grad+m_grad)

# remove non required columns
OA.Qualify[2:19]<-NULL

# LGA Code Aust.
lga_csv <- read_csv("lga_aus.csv")
## Rows: 577 Columns: 5
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (2): LGA_NAME11, STE_NAME11
## dbl (3): LGA_CODE11, STE_CODE11, AREA_SQKM
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
# fix lga_code col name
lga_csv <- lga_csv[, c('LGA_CODE11', 'LGA_NAME11')] %>% 
           rename(lga_code=LGA_CODE11, lga_name=LGA_NAME11) %>% 
           mutate(lga_code = as.character(lga_code))

# combine qualification data with LGA info
OA.QualifyLGA <- OA.Qualify %>% inner_join(lga_csv, by="lga_code")

# function to extract LGA name only, removing additional unwanted text
# so we can join datasets by this feature
lga_name <- function(x) {  
  # substr(x, 1, str_length(x)-4)
  x
}

# Get Shape file data for TAS - by LGA, lat, long only - DSS Payments
aus_LGA <- read_sf("Australian LGA Boundaries.kml.xml")
# View(aus_LGA)

df_aus_LGA = as.data.frame(aus_LGA)
# View(df_aus_LGA)

# add new lga_name only
df_aus_LGA <- cbind(df_aus_LGA, lga_name = sapply(df_aus_LGA$`Name`, `lga_name`))

OA.QualifyGEOM <- OA.QualifyLGA %>% inner_join(df_aus_LGA, by="lga_name") %>% 
                  subset(select=-c(Name, Description))
# convert dataframe to sf object
OA.Qualify_sf <- st_as_sf(OA.QualifyGEOM)

#----------------------------------------------------------------------
# import ABS data - dss-demographics-march-2016.xlsx
#----------------------------------------------------------------------
Output.XL <- as.data.frame(read_excel("dss-demographics-march-2016.xlsx", sheet = 12, col_names = TRUE, skip = 2))

# data cleaning - replace non numerics, eg: "<5" with "1"
Output.XL <- Output.XL %>% 
  mutate_all(funs(str_replace(., "<5", "1")))  %>% 
  mutate_all(funs(str_replace(., "n.p", "0")))  %>% 
  mutate_all(funs(str_replace(., "0.", "0")))

# convert all but first 2 columns to numeric
Output.XL2 <- cbind(Output.XL[,1:2], sapply(Output.XL[,3:29], as.numeric))

# fix some of the column headings, create Unemployment and total Carer
Output.XL3 <- Output.XL2 %>% 
      mutate(Unemploy=`Newstart Allowance`+`Youth Allowance (other)`+`Youth Allowance (student and apprentice)`, 
             Carer.Pay=`Carer Allowance`+`Carer Allowance (Child Health Care Card only)`+`Carer Payment`, 
             Parenting.Pay=`Parenting Payment Partnered`+`Parenting Payment Single`) %>%
      rename(lga_code=LGA, 
             FamilyA.Pay=`Family Tax Benefit A`, FamilyB.=`Family Tax Benefit B`,
             Age.Pension=`Age Pension`,Newstart.Pay=`Newstart Allowance`, 
             Youth.Allow=`Youth Allowance (other)`,
             Youth.AllowStudent=`Youth Allowance (student and apprentice)`,
             Disability=`Disability Support Pension`)

# Combine Qualification & LGA shape data
OA.Census <- merge(Output.XL3, OA.QualifyGEOM, by.x="lga_code", by.y="lga_code")

# remove unwanted cols
OA.Census$f_cert<-NULL
OA.Census$m_cert<-NULL
OA.Census$f_grad<-NULL
OA.Census$m_grad<-NULL
OA.Census$X4carer_pay<-NULL
OA.Census$X2youth_all<-NULL
OA.Census$X6carer_all<-NULL

# convert to sf spatial frame
OA.Census_sf <- st_as_sf(OA.Census)

# plot our DSS data - rendering takes forever ... shows 9 out of 19 
# plot(OA.Census_sf, border = 'lightgrey')

#--------------------------------------------------------------------------------------------
# Point Pattern Analysis - example - CRS("+init=EPSG:27700")
#--------------------------------------------------------------------------------------------
# houses <- read.csv("CamdenHouseSales15.csv")  
dssPoint <- ptsMean[,c("Population.No","lon","lat","Avg Income","Avg Allowance","Avg Pension","Income.PC","Allowances.PC","Pension.PC")]

House.Points <- SpatialPointsDataFrame(dssPoint[,2:3], dssPoint, proj4string = CRS("+init=EPSG:4283"))
proj4string(House.Points) <- CRS("+init=EPSG:4283")

# creates a coloured dot map
t1 <- tm_shape(OA.Census_sf) + tm_borders(alpha=.4) +
      tm_shape(House.Points) + tm_dots(col = "Pension.PC", palette = "Reds", size="Pension.PC", style = "quantile", title="Pension %") 

# creates a coloured dot map
t2 <- tm_shape(OA.Census_sf) + tm_borders(alpha=.4) +
      tm_shape(House.Points) + tm_dots(col = "Income.PC", palette = "Blues", size="Income.PC", style = "quantile", title="Income $") 

Qualification effects on inequality

Previous findings identified Income (Salary) and Population as the two most important predictors to determine Welfare expenditure. We identified that Tasmania had the lowest average income and highest proportion of welfare to income, plus it has the biggest population proportion on the Age Pension. Graph 4 shows that nearly 80% of Tasmania earns less than the Australian average income, which from our data is approximately $53,000. Graph 5 shows geographical spread of residents on the Age Pension, with possible age pension population clusters to the north (Launceston) and south- east (Hobart).

If you compare both graphs below, you can see that some of the darker-blue “income” circles, have corresponding lighter, “pension” circles, where a greater proportion of the population are earning an income and lesser proportion are on the age pension. Similar in reverse, you can see some of the darker-red, “Pension” circles, have corresponding lighter- blue, “income” circles, where a greater proportion of population are on the age pension and small proportion are earning an income. However, there are a couple of postcodes with both dark blue “income” and dark red “pension” circles – located at the three biggest towns (indicated by the arrows). This supports our initial assignment two findings, proportionally higher income alongside higher “welfare” as a result of the age pension.

# show multiple tmap plots - then revert back to current plot view mode
current.mode <- tmap_mode("plot")
## tmap mode set to plotting
tmap_arrange(t1, t2, widths = c(.5, .5))

tmap_mode(current.mode)
## tmap mode set to plotting

Compare Employment, Pension, Qualification

To investigate if having a qualification has a reducing effect on unemployment for the same areas, we can use spatial autocorrelation on Unemployment and Graduate Qualification predictors. We can see that both have stronger cluster relationships closer to the two major cities in Tasmania. Both predictors have similar counts and quantiles which is ideal for comparison. Unemployment is higher than qualified graduates along the west coast. Which makes sense as it is mostly unpopulated coastline and national park. There do seem to be more LGA areas with higher number of qualified graduates and less unemployed (bar one or two areas). So this might indicate that having a qualification could lead to lower unemployment. The highest concentration (cluster) of Age Pension population hotspots are at located at Launceston and Hobart.

setwd("/Users/paipac/Documents/Data/")

#----------------------------------------------------------------------
# map out Graduate Qualified
#----------------------------------------------------------------------
library(tmap)
t1 <- tm_shape(OA.Census_sf) + 
  tm_fill("Unemploy", palette = "Reds", style = "quantile", title = "Unemployment") + 
  tm_borders(alpha=.4)  

# Spatial Autocorrelaton - Pension
t2 <- tm_shape(OA.Census_sf) + 
  tm_fill("Age.Pension", palette = "Reds", style = "quantile", title = "Pension") + 
  tm_borders(alpha=.4)  

# Spatial Autocorrelaton - Disability
t3 <- tm_shape(OA.Census_sf) + 
  tm_fill("Disability", palette = "Reds", style = "quantile", title = "Disability") + 
  tm_borders(alpha=.4)  

Spatial autocorrelation

To investigate if having a qualification has a reducing effect on unemployment for the same areas, we can use spatial autocorrelation on Unemployment and Graduate Qualification predictors. We can see that both have stronger cluster relationships closer to the two major cities in Tasmania. Both predictors have similar counts and quantiles which is ideal for comparison. Unemployment is higher than qualified graduates along the west coast. Which makes sense as it is mostly unpopulated coastline and national park. There do seem to be more LGA areas with higher number of qualified graduates and less unemployed (bar one or two areas). So this might indicate that having a qualification could lead to lower unemployment. The highest concentration (cluster) of Age Pension population hotspots are at located at Launceston and Hobart.

options(warn = -1)
# show multiple tmap plots - then revert back to current plot view mode
current.mode <- tmap_mode("plot")
## tmap mode set to plotting
tmap_arrange(t1, t2, t3, ncol = 3)
## Some legend labels were too wide. These labels have been resized to 0.63, 0.55. Increase legend.width (argument of tm_layout) to make the legend wider and therefore the labels larger.
## Some legend labels were too wide. These labels have been resized to 0.63, 0.55, 0.55. Increase legend.width (argument of tm_layout) to make the legend wider and therefore the labels larger.
## Some legend labels were too wide. These labels have been resized to 0.63, 0.55. Increase legend.width (argument of tm_layout) to make the legend wider and therefore the labels larger.

tmap_mode(current.mode)
## tmap mode set to plotting

References

Australian Bureau of Stastistics: http://stat.data.abs.gov.au
CSV data available from: https://github.com/CazMayhem/STDS-Work
Plotting for Google Maps: https://www.rdocumentation.org/packages/ggmap/versions/2.6.1
Spatial analysis package: https://r-spatial.org/book/07-Introsf.html
Map formatting: https://leaflet-extras.github.io/leaflet-providers/preview/
Markdown: https://ourcodingclub.github.io/2016/11/24/rmarkdown-1.html