Data Viz Done Right

August 19, 2012

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

25 comments

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. 

image

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.

image

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

image

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.

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.

25 comments :

  1. Brilliant !!

    Any way to incorporate radius maps to indicate average/median distances?

    ReplyDelete
  2. I hadn't tried that, but I imagine you could do it with a calculated field, a dual axis, and making them circles. Give it a shot!

    ReplyDelete
    Replies
    1. I agree, Andy. Much easier with dual axis!

      Delete
  3. Ujval, check out this discussion on concentric circles. http://community.tableausoftware.com/message/132740#132740

    ReplyDelete
  4. Thanks !!

    Btw, congratulations on your move to the West Coast !

    Regards,
    Ujval

    PS - If you follow the WSJ, they are falling in the Nielsen trap of having absolutely horrible graphics. They had one to illustrate the tenure of the board of directors at JP Morgan and they used a radial chart which makes no sense at all

    ReplyDelete
  5. This is great! I'm embarrassed to say that I've ignored the path shelf because I couldn't figure it out. I've been asked how to do a network map in Tableau and didn't have a clue. Now I do! Thank-you for your detailed instructions Andy - always appreciate your work.

    ReplyDelete
    Replies
    1. Great! I'm glad I was of help to you.

      Delete
  6. Hi Andy

    Anyway that you can share a downloadable version of the custom sql code? thanks in advance, ps great share, thanks!

    ReplyDelete
  7. is it possible to Create hub & spoke diagrams using the Path shelf without Latitude and Longitude? I am trying to make a visualization showing on the left the list of "FROM" groups with their value and maybe a bullet then a line to a center bubble (hub) showing the group name "Solution Advisory" with on the right, lines to bullets showing the list of "TO" groups with their numbers. Basically showing the path which items come from into the group "Solution Advisory" and then where the items go when they leave. Not sure if this is possible or if I have my data organized correctly so any help would be much appreciated. There is much more data where I will filter on "Group"

    Using Tableau 8

    ReplyDelete
    Replies
    1. Are you looking for something like a slope graph?

      http://vizwiz.blogspot.com/2013/01/alberto-cairo-three-steps-to-become.html

      Or a parallel coordinates chart?

      http://public.tableausoftware.com/views/ParallelCoordinatesExample/ParallelCoordinatesExample

      Delete
  8. Hello Andy,
    An airline's hubs and spokes route system illustration was thought to be desirable for a school project. Only the cities/airport codes involved would be used without lat/long info (if possible). Could this be accomplished relatively easy with the public version of Tableau?
    Thanks,
    Tom

    ReplyDelete
  9. Tom, Tableau's default maps do not support airport codes. You could load them into the lat/long tool I have a link to on the right of the blog to get the lat/long coordinates.

    ReplyDelete
  10. I love your example. However, i am having some issues mapping the paths (lines) and the destinations (points) on the same graph. I am trying to show transportation paths from distribution centers to markets indicating market demand by the thickness of the lines and total landed cost per kilo by the size of the destination points. I can do them on separate maps, but not together. Any advice?

    Thanks

    Noelle

    ReplyDelete
    Replies
    1. Noelle, if you can email me a packaged workbook, I'd be more than happy to look at it for you.

      Delete
  11. What is your email? I cant seem to find it.

    Thanks for your help!

    ReplyDelete
  12. I have city names instead of airports and interested to know movements across cities using hub and spoke visualisation.

    ReplyDelete
    Replies
    1. There's a link on the right side of this blog to a tool that will generate the lat/long for you. Give that a shot.

      Delete
  13. Why is it that when i add labels to points, the lines disappear? Is anyone else having this issue?

    ReplyDelete
    Replies
    1. If you download the workbook I created, you'll notice it's a dual axis chart. The secondary axis is set to Circles for the mark type. Adding the labels to those should not cause the lines to disappear.

      Delete
  14. Hi Andy, thanks for a great blog on path maps ... it really helped but can we have curved path between the the 2 points ? Also can I make a dotted line between my paths if it not yet traversed but will be traveled next ?

    ReplyDelete
  15. Andy, could you perhaps attach the data file? I am unable to find it at http://www.rita.dot.gov/bts.

    Cheers

    Jan

    ReplyDelete
  16. Andy, I am new to Tableau and your blog has been incredibly helpful, thank you!! I have created a route map, but am having trouble with the labels appearing correctly. My route map is directional, which means LAX-SYD is a different route than SYD-LAX. I can plot the lines and see some of the labels, but there remains a glitch that I cannot resolve. I want the labels to appear at the start, and only the start, of each line. I either see just one of the labels at both ends of the line, or both labels on top of each other at both ends of the line. Any guidance you can provide would be greatly appreciated. And thanks again for all of the help you already have provided.

    ReplyDelete
    Replies
    1. It's a bit hard to guess what's wrong without seeing the problem, but if I had to guess, I'd say you need to turn labels off for the dots and on for the lines. Then on the line, make sure it's only on for line ends and allow overlapping.

      Now, what you need to do it create a calc field that tells Tableau what text to show. For example, if you have Path 1 (beginning) and Path 2 (end), then create a calc field that is something like:

      IIF([Path]=2,[Airport],NULL)

      Then place that field on the label shelf.

      Delete