Dashboard week day 2 saw the Data School come head to head against the New South Wales Transport api. Following a hard and vicious battle – the data schoolers emerged with their heads held high and a dataset ripe for visualisation… Right at the close of business. For me, today’s challenge was all about data cleaning, cleansing, regex, and innovative ways to visualise things that have too much data to visualise in obvious ways!
My completed visualisation is below, read on to figure out my thought process and some of the blockages I had to work around to get from dirty data to a completed viz!
Alteryx
We used data from Transport NSW’s train timetables, as well as an API that returned details about delay times for all trains on the Sydney network at the time of running. Given the datasets, and the time we had, I aimed to create a viz showing the delay times for each Sydney train line and investigate whether the median average income across the line resulted in lower delay times (A common feeling across anybody not on the North Shore line!).
However, the catch is that the delay time data was returned in an application/x-google-protobuf format and required significant cleaning to parse out the data. Text to column, the replace function, multi-row formulas, and regex were invaluable in identifying patterns, flagging patterns, and then massaging the data out. By finding all fields with relevant information and replacing in a pipe (|) to make full use of the text-to-columns tool was invaluable, and combining that with regex statements yielded all the delay data.
After obtaining the delay data at ~3pm, I worked with fellow data schoolers to work out the joins for the Transport NSW timetable files and then added depth to the data by overlaying the Australian Bureau of Statistics Census data regarding median income per household via a spatial match on each station. I was simultaneously overjoyed and terrified to have a clean and workable dataset at 5.30pm!
Tableau
Initially I wanted to use Tableau maps and the new 2018.3 set actions to visualize each stop on a line with each stop sized by the median income of the suburb. However, the area covered by each line and the differences in income meant that the chart was very difficult to understand.
After a brief retrospective on what I had done, and what I wanted to show – I decided to change tack and visualize the information in a different way. Ultimately I ended up using a large radial bar chart to drag the viewer’s attention in and depict delay times for each stop, along with a scatterplot of stops and their median incomes linked together with a highlight filter to fully utilize dashboard interactivity.
It was my first time making a radial bar chart, and I am pleasantly surprised by how striking they can make a dashboard… Expect more of these throughout my dashboard week journey!