Taking Off with the Path Function

Have passport: willing to travel. Few things evoke such a sense of endless possibility and adventure than looking up at the departures board in an airport.

I sometimes get that same feeling of endless possibility when I’m looking at a rich dataset that I’ve just connected to Tableau. Haven’t you ever wanted to build a viz that shows the path between two points? When you look up at the departures board don’t you want to be able to map all those routes? Well you can build this kind of viz in Tableau and it’s all based around the path shelf.

Flight routes in Tableau by Chris Love

The Path Shelf

The Path shelf lets you connect points in you viz in a particular drawing order. Think of it as a connect-the-dots style of drawing. To do this you need to have your data arranged in a particular order. You need to have all your destinations (both starting and ending locations) in one column, with one row for each your ‘Start’ and ‘End’ locations.
For example here is part of a fictional dataset for where I went on Thursday, Friday and Saturday. So I have one column for all the locations and then separate rows for ‘Start’ (eg Home) and ‘End’ (eg Work). To establish the direction I want the line drawn (the path order), I have put a 1 for the row that is the start of the journey, and a 2 for the end of the journey.
Lastly I need to give each journey pair a unique identifying key Path ID. For a detailed tutorial about setting your data up check out here.

Build a Hub and Spokes Style viz

Once you have your data in the right structure you’re ready to build your path map!

Drag your latitude and longitude onto the rows shelf
On the Marks card, change the Marks type to Line. Note: the Path shelf is only available when the Line or Polygon mark type are used
Separate each journey by dragging your unique key path ID onto the Detail on the Marks card (or onto the Colour)
Drag the Path Order onto the Path shelf in the Marks card. Right-click to change the Path Order into a Dimension (to stop it being aggregated as a sum)

Using this method you can build fantastic Hub and Spoke diagrams like this one by VizWiz

What if my Data Has Arrival and Departure Information in Separate Columns?

If you dataset has its ‘Start’ and ‘End’ locations in two separate columns, like in the example below, then you can use UNION (a custom SQL function) to re-shape the data before you bring it into Tableau.

The UNION function is a SQL command that joins two datasets together. In this case we want to split our original dataset into two new datasets – in one our ‘Location’ is going to be our ‘Start’ column and in the other it’ll be our ‘End’ column. When we UNION these two new tables we will have all our locations in one column.
To use a custom SQL function we need to open our excel file using the legacy data connector. This can be a bit tricky to find. When you navigate to your excel file, don’t select ‘Open’ in the bottom right hand corner, instead click the small arrow icon to select ‘Legacy Connector’ from the drop down list.

When you select the ‘Legacy Connector’ you get the option to use custom SQL. You can then type your SQL into this tab when you drag in onto the ‘Drag Sheets Here’ pane.

Now that you can write your custom SQL statement the UNION syntax looks like this:
SELECT [DataTableName$].[Start] AS [Location],
[DataTableName $].[Start] + '-' + [Data$].[End] AS [PathID],
1 AS [PathOrder]
FROM [DataTableName$]

UNION

SELECT [DataTableName $].[ End] AS [Location],
[Data$].[Quantity] AS [Quantity],
[DataTableName $].[ Start] + '-' + [Data$].[End] AS [PathID],
2 AS [PathOrder]
FROM [DataTableName $]

We are doing two important things in this query above:

First we are making a PathID field that will be called ‘Start’-‘End’, for example the first two rows in the re-shaped dataset would be ‘London-Kyoto’.
Second we have added a new column called ’PathOrder’ that isn’t in our original dataset. For the first half of the UNION statement we have made this new column’s value ‘1’ and in the second half it’s ‘2’.

Check out this post by VizWiz to see the custom SQL method in action.

My Route Has Many Lay-Over Points

So far we’ve only looked at mapping paths that have two steps – a start and end point and no other points in-between. What do you do when you want to map something that has many points between its start and end points? Mapping routes with many points follows the same logic as mapping routes with only a start and end – you start at path order 1 and for each subsequent point on you path your path order increases by one. This is what I have done in mapping out some of my work colleagues dream holiday routes below.

A great example of this in this viz looking at London’s bike share routes by Chris McGrillen. If you look at how the data is set up some of the routes have over 100 points in them.

What Else Can I Use the Path Shelf For?

You can use the Path shelf for anything where you have a sequential path to follow. Try adding a datetime to the path shelf to make movements in space over time. This is how this storm map was made. Check out this video to see how to make this storm map yourself.