Dashboard Week |Day 5 -Bike Counters in Seattle

by Anastasija Starkina

Today is the last day of the training and it feels surreal that 4 months have passed so quickly. Todays task was to extract data from seattle.gov.us regarding the cycling traffic in Seattle. There were a total of  10 datasets that we had to extract and combine together. I used Alteryx for for this part. Initially, I have used API from the web site to extract the data.

Data Extraction: First Attempt.

I have extracted each API in separate text file. Alternatively (and much quicker) you can simply put all  the URLs in one text output. I spent a lot of time rewriting workflows but now I can easily do it with my eyes closed!

After downloading and parsing the the link, I used cross tab tool to organize data in coherent table. Finally I have renamed some fields and sorted the table by ascending date. At this point, my team realized that the API we all have used is  was actually inaccurate. There is a  limitation of how much JSON  implementation of various browsers can handle (e.g. around 40MB ).

So instead of using an API, I have used csv download links to extract the data. Below is the second workflow that I've produced:

This time I have included all 10 links into one text input. After downloading the data and parsing it (first on a space and secondly on a coma) I then had to think of how to organize the data. Some of the counters data had pedestrian data, and the other included data for different directions. I decided to work on each counter separately by initially splitting them using formula tool. I use dynamic rename to take field names from first row of data, deselected pedestrian data  and kept only bike totals for each location. Where necessary, I used  formula tool to calculate only bike point totals. Then I used a transpose tool to create a location column and did a union on that. My final dataset looked like this:

Record ID - is a ID of a counter

Date: The date and hour of day object(s) are detected by the sensor.

Location- Counter name

Value-  The total number of cyclists recorded, in all directions, in one hour. This field adds together data from the North and South columns and where applicable of West and East.

This is the final version of my dashboard:

It can be filtered by counter and year. The axis is dynamic and can be changed from month to hour.

I am grateful for all the experience I got during the last four month and cannot wait to apply it at my first placement!  


Anastasija Starkina

Thu 30 Sep 2021

Tue 28 Sep 2021

Mon 27 Sep 2021

Sun 01 Aug 2021