Recently I had been asked to develop some example dashboards for an Airline and this involved quite some research on how best to do this. The first thought that came to my head was “I want a map showing the Airline routes!”
I learned quite a bit and now what to show you how easily this can be done.
Preparing the Data
It would be absolutely brilliant if the data was all well presented already for what you want to do right? That is something that we can get done with Tableau’s new “Project Maestro“, however, we won’t go into that now. What does this mean? It simply means we’ll have to do it the manual way.
Initially, you would need the dataset, but most of the time the data you have from your engagement isn’t enough. For Airlines/ Transportation in particular (Can include trains etc…) I always append my data with the extra information available here: https://openflights.org/data.html
For this particular walkthrough, I have added the Airlines data (airlines.dat) and Airports data (airports.dat).
Initially, my routes data looks like this:
Although it looks simple enough, the truth is that for what we want to achieve this is rather unusable. We need to transform the data.
1) Add a Key which we will call a route ID. to create this ID I will use the CONCATENATE function in Excel. My function looks like this; “=CONCATENATE(B2,”-“,D2,”-“,F2)“. Once you have done this simply copy it down to the end of your data and then copy and paste as values.
2) We will then rename the “Source Airport” field to simply Arrival/ Departure, and same with Arrival/ Destination ID, simply rename as Airport ID.
3) Now, this is the tricky part, you need to add the contents of the Destination airport under the line of the arrival airport. You also need to keep the SAME ROUTE ID.
Once you have done it, it should look something like this:
Now is where the fun begins 🙂
Connecting the information with Tableau
Connecting to an excel file is not complicated at all. You need to then bring in the file where you have your data set and join the data as per below:
For the above, I connected Routes to Airlines using “Airport ID” as the joining field, and I connected Routes and Airports, using the “Airport ID” field.
Visually showing the routes
This is probably the most straightforward part. You need to select the Latitude and Longitude measures (BUT NOT THE AUTO-GENERATED ONES) and add it to the columns and rows respectively.
In the marks, you will then need to select “Line” from the drop-down menu.
Once you have done this you will then need to drag and drop on to the new Line square in the card the field “Route ID”.
This should then give you something like this:
This is now ready for you to visually edit the lines to how best you want to see it fit. At its simplest, you can make it colourful by bringing in the airline names:
Hope you would find this useful. I’ll leave the rest to your imagination! Let me know what you come up with and Happy Vizzing!