On the 4th day of the dashboard week, we are working on the 2021 Census data from ABS. Although we have worked with this data set during the training, to finish this challenge we still need a lot more work to be put into data exploring and data cleansing.

  • Preparing the data for Census data analysis

First of all, Census data has multiple tables based on census question sets. Which tables should we use based on the stories we want to tell on our dashboard. So after quick exploring, what I aimed for was to do a bit of analysis based on housing and renting. Then I could pick the tables from the dataset.

Census data files have unique structures. So to prepare the data we should do some trasformation steps. Also census data files are based on different level of spatial features. Here the files I choose are based on suburb level. It’s also a good idea to get spatial data related to suburbs to supplement the data and procvide visual on maps. So my data preparing has two parts.

  • Census data preparing

The Alteryx workflow for Census data is shown below.

In this process, I combined different tables into one and also have another separated one. The key things to consider during this process are:

  1. By carefully reading the metadata info, choose only the relevant data columns you need.
  2. Cross check the different tables to make sure they have matching data.
  • Spatial data preparing

A good way to supplement the Census data, is to add in some spatial data which I did on this challenge. The spatial data I used was from the Tomtom spatial addon for Alteryx. I picked four different type of data, Train stations, Schools,  Entertaiment facilities and Shopping centers. The workflow to do this is shown below.

To prepare these data, some key things need to be done in the workflows:

  1. A reverse geocoding needs to be done to get the suburb details in order to match with the census data.
  2. Adding trade area spatial objects in addition to the point data already in there.

After these data preparation, I can build the dashboard.

  • Dashboard design

First let’s have a look of the dashboard.

The dashboard has following design thinkings:

  1. Provide both detailed data and the spatial map data for different suburbs help people get needed informations.
  2. The use of the trade area spatial objects created in Alteryx workflow helped show facilities on map for each suburb.
  3. The use of the parameters and calculated fields created based on these parameters make the top N action on filtered data work.

To conclude, working with census data is both challenging and fun. Will work on it later again. Thanks for reading the blog.

The Data School
Author: The Data School