Today was our last day of #Dashboardweek, what a week has been! The data was about United States Interstate Traffic. Because Friday is presentation day at the DS, we only had until one to prepare the data and create a visualisation. This time the data was already available to download as a flat-file straight from the website. However, there was some prep to do and we were only allowed to use Tableau Prep this time.

The Prep

The prep was relatively simple, we had to clean the data, pivot some of it and union both flat-files we downloaded. One of the “more complicated” steps included editing the Latitude and Longitude, so it makes sense in Tableau. In the dataset these fields were written as long integers without the decimals needed for the Lat/Lon and also all the fields were written as positive numbers. After having a look at the “common” Lat/Lon in the United States, I noticed an issue: the latitude had two numbers before the decimal and the longitude could vary between two and three. To overcome this problem, I wrote the following formulas:

Longitude: Most longitudes in the US are 8 characters, if they are 9 it means that I should put the decimal after the 3 character, they are also negative numbers. I turned them into a string and after that into a geographic field.

FLOAT(“-“+REGEXP_EXTRACT([Longitude],IF LEN([Longitude])>8 then “(\d\d\d)” ELSE “(\d\d)” END)+”.”+REGEXP_EXTRACT([Longitude],”\d\d(\d+)”))

Latitude: Latitudes in the US are two numbers in the main area. I applied the same logic as in the longitude field, turning it into a string and afterwards into a geographic role.

FLOAT(REGEXP_EXTRACT([Latitude],”(\d\d)”)+”.”+REGEXP_EXTRACT([Latitude],”\d\d(\d+)”))

The worse part of the data prep is that running the workflow has taken over 20 minutes in Tableau Prep which hasn’t allowed me to work very fast and therefore you only get one real chance to run it.

At the end the workflow took so long in loading that in order to build something I ended up asking Brian for his data. However, after building the dashboard I restarted Tableau Prep and the workflow run in less than three minutes.

The Building

The building was very straight forward as the prep left me with just a few fields. The data had date and time, station, state and number of cars. Because the original visualisation was a map, I tried to follow that and built a map, a line chart and a BAN. Each part of the dashboard allows you to see different elements of the data: How many cars are currently on the roads, where they are and their changes over time.

This was the result:

If you have any doubts or comments, feel free to use the box below or contact me in Twitter @DiegoTParker