Analyse your LGA's Census data in 3 easy steps

This is my first LinkedIn post directed at connecting and knowledge sharing with other data analysts/users within Australian LGA's. All LGA's have similar data sets, I would love to collaborate more so that we can all deliver better services to our community.

Census Insights for your LGA

How can we use Census data to discover insights to help deliver more effective services?

Did you know in the suburb of Doveton there are nearly double the number of males than females within the age range of 20 to 24! What services can we deliver to support this demographic?

If you would like to see how we are using #PowerBi to visualise our Census data at the City of Casey click the link: City of Casey - 2016 Census Analysis

But more importantly why not develop your own following the steps below to visualise the age and gender demographics of the suburbs within your LGA in a tornado chart.

Do it yourself via 3 steps:

1. Download your own Census data (Link)

• Select Dataset: 2016 Cultural Diversity
• Under Geographical Areas: Your State Suburbs as row
• Under Selected Person Characteristics: AGE5P as row
• Under Selected Person Characteristics: SEX as row
• Under Geographical Areas: Your Local Government Area as filter
• Should be similar to below (My LGA filter is Casey and I have used Victorian suburbs)

• Select 'Queue Job' (as csv)
• Enter a table name
• Go to 'Saved Tables'
• When your table has completed running you will see 'Completed, click here to download'
• Download file

2. Edit in Excel

• Open in Excel and delete rows 1 to 11
• Add 'Count' as header in 'D1'
• Delete rows where column D = 0
• To fill in all the blanks, Select all column A
• Unmerge
• Under home ribbon, Select 'Find & Replace > Go To Special'
• Select Blank, OK
• Without moving your cursor enter =a2
• Ctrl + Enter
• Repeat for column B
• You will end up with image below

3. Download Power BI (Link)

• Install Power BI
• Get Data > Text/CSV
• Select your CSV file
• Select Load
• The next steps are seen in the image below
• 1. Under Visualizations on the right select the three dots
• Select 'Import from store'
• 2. Search for Tornado
• 3. Add Tornado

• Drop and drag the following fields into your chart
• AGE5P in group
• SEXP Sex into Legend
• Count into Values
• This shows the whole municipality combined, lets add a filter to see by suburb

• Under Visualizations select slicer (white box with a funnel in right bottom corner)
• Drop and drag SSC (UR) to the field
• You now can filter your chart by suburb
• Try to identify a suburb with an uneven distribution
• Your final product, with some aesthetic editing might look a little like below

Note: the years are not sorted in order in your chart, I will show how to fix that in a later blog.

Hope you enjoy identifying interesting trends within your own data.

I would love to see how others are using their Census data to drive decision making within their LGA's. Please help me connect with them by sharing this post or tagging them in the comments. I will be doing more articles to show you how to make your own Power BI charts with Census data like in my report here (Link).

Cheers,

Warren Dean, Senior Data Analyst, City of Casey