MEMBERSHIP PROGRAM

What I did

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.

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.

TIP: For those of you that may be new to Tableau, I would highly recommend that you use the forum if you’re approaching something you’ve never done before.  You’ll often find that someone has already done something similar and it’ll save you a lot of time versus re-inventing the work yourself.

1. Connect to your data source, select Single Table, then select Custom SQL
2. Create an inner join on a second instance of the table where the locations from the two instances are not equal (refer to the SQL script in the detailed instructions)
3. Click OK, then Extract the data.  For me, I’m looking at 7271 stores, so the self join will result in about 50M records.  Leverage the power of Tableau’s data extracts!
4. Double-click your latitude and longitude fields to start building the map.  You may need to set the geographic role of the fields if you don’t have them named Latitude and Longitude.
5. Use the Great Circle Distance formula by creating a calculated field named Distance (or the name of your choice)
   
   The formula is:
   3959 * ACOS
(
SIN(RADIANS([Lat])) * SIN(RADIANS([Lat2])) +
COS(RADIANS([Lat])) * COS(RADIANS([Lat2])) * COS(RADIANS([Long2]) - RADIANS([Long]))
)

NOTE: To calculate miles, use 3959 as the first number.  For kilometers, use 6371 as the first number (thanks to Shawn Wallwork for the comment)

 
6. On the Marks card, in the list, select Line. This will create lines between all locations on the map.  Start with only a couple locations if you have a huge dataset, otherwise it could take some time to draw all of the lines.