Today we’ve passed the middle mark of Dashboard Week. The challenge was to visualize public transport links for one of the major cities listed by Andy. I was to do Philadelphia. Same as the previous challenges, we needed to get the data (with an API), clean it and visualize.

Challenge here: /andy-kriebel/ds6-dbw-d3/

Getting the API was easy. The train service provider in Philadelphia, SEPTA had an open API with no key required. The challenge for the day was what came after that, the data preparation.

The API: http://www3.septa.org/hackathon/

My Alteryx Workflow was pretty long. I had 2 inputs: the API base and a list of all the stations. I needed to join in the base API before every station name. This was easy to do by just creating same row number IDs in both files and joining on the new created Row ID.

Next step was to create the URL to call all stations at the same time. For this, I used the Formula tool by appending the two string columns. After this, I was able to get the whole data with only one call with the Download tool.

The data needed some more prep by removing unnecessary fields and splitting twice on different delimiters with Text to Colum. However, I did not have a field to group by on my Crosstab, so I had to use a Multirow formula to create a new column giving me a number each time the next set of data was pulled.

The decision what I was going to pull out from my data was simple as the data itself was limited in a few aspects. I had great information on delays however so I decided to focus on them. You can see the published finished dashboard:  https://public.tableau.com/views/PhiladelphiasSeptaTrainService/PhiladelphiasSEPTATrainServiceWherearethebiggestdelays?:embed=y&:display_count=yes&publish=yes

For the background map, I used the one provided by SEPTA itself showing all their routes. I stripped the map down to only the route layer and plotted my stations.

Background Map: http://www.pasda.psu.edu/uci/DataSummary.aspx?dataset=3054

This was a brief summary of my work on day 3 of Dashboard Week. Two more challenges this week.