Tableau Tip: Create hub & spoke diagrams using the Path shelf

I’m in constant learning mode with Tableau.  I don’t think a day has gone by when I haven’t learned something.  I love the Tableau community because everyone freely shares ideas from which they hope others borrow.  This technique of from-to patterns is no exception; it’s definitely not a new topic I’m covering here.  But I do like documenting the processes I follow, which is more or less why I started this blog in the first place.

Paths are not a feature I use often, so in my inevitable quest for all things Tableau, I decided to find some data that would provide a good learning experience.  The data for this post comes from the Bureau of Transportation Statistics.

Here’s a sample of the data. 

Each origin airport/destination airport combination is represented as one row in the spreadsheet. This is great, but in order to use Tableau’s Path functionality, you need to have two records for each origin/destination combination.  The first for the starting point, and the second for the ending point. 

You can accomplish this quite easily through Custom SQL.  The example below contains all the columns I need from the spreadsheet.

• Those before the red box are inherent in the spreadsheet.
• The SQL inside the red box is for the fields I have to add to make the paths work.
1. You must specify the path.  In this case, 1 is the starting point, 2 the ending point.
2. I added additional latitude and longitude fields to capture the starting and ending points.  That is, I use the origin lat/long when my path = 1 and the destination lat/long when my path = 2.
3. I included a concatenated string for the route, which I will use on the Level of Detail shelf.
4. Union the SQL for path 1 to itself, but change the path to 2 and change the lat/long to the destination lat/long. Notice that the Route calculation stays to same.

In Tableau, all you have to do is:

1. Double-click on both Latitude and Longitude to draw the map
2. Add Route to the Level of Detail shelf
3. Change the Mark Type to line
4. Add the Path Order dimension to the Path shelf

You now have a giant mess of lines all over the place that represent every single route in the data.  That’s all you need to do. 

The trick is that you need to write the custom SQL in order to have your data in a usable format.

I wanted to take the viz a bit farther though, so I created this interactive version on Tableau Public.

Powered by Tableau

I included:

• Filters options for Origin Airport, Destination Airport, and Airline (I chose ATL as my initial view since it’s the world’s busiest airport)
• Colors to indicate the length of the route
• Clicking on a color highlights the routes in the selected flight range and also highlights the destination airports on the bottom map
• A map of the number of passengers that flew to each destination, indicated by the size of the bubble
• Custom tooltips, which include the great-circle distance calculation

This is a pretty fun viz to interact with.  Choose all origin airports and you see all of the routes.  Choose the airport you most frequently fly from.  Choose your preferred airline.  The three New York airports are particularly interesting (EWR, LGA, JFK).

There are lots of other data elements that could be included, but I haven’t gotten to.  Think about adding options to color the bubbles and lines by things like frequency of delays, average ticket price, etc.

Wouldn’t this be really cool to do with your friend networks on Facebook?  Oh wait, we did that in a recent hack-a-thon.