Iron Viz entry: Blame the Weather
I decided to build a viz looking at the impact of the weather, specifically precipitation, on flight delays. Since Ben's data set only contained flight data, I needed a way to join the weather data for each day to all of the flights in the data set. The only issue with this is I couldn't find a data set that contained weather specifically for airports. Since nothing was available, I started looking at possible data sources, and I found an API endpoint that offers CSV data for airports from WeatherUnderground. Now I needed to be able to scrape all of that data for every airport and compile it into one file.
To do this, I used the trusty curl command. In order to retrieve the csv and store it in a file, I used the following command:
curl "http://www.wunderground.com/history/airport/KPDT/2014/1/1/CustomHistory.html?dayend=1&monthend=1&yearend=2015&req_city=&req_state=&req_statename=&reqdb.zip=&reqdb.magic=&reqdb.wmo=&format=1" >> output.csv
Once I had the complete weather data for all of the airports, I needed to be able to join all of the weather data to each flight. With over 5 million records, using Excel was out of the question. I tried to do a data blend in Tableau, but I was not able to get the weather data tied to the flight records in a way that gave me the drill-down I was looking for. I've been using Alteryx a bit lately, and I decided that was my best course for this project.
For this viz, I wanted do do a small multiple map that showed the flight paths. In order to do that, I needed to be able to create a path order as Andy Kriebel's tutorial shows. To do this in Alteryx, I first joined the geocoordinates from the airports file to the origin airport records, and then I joined the file again to get the geoocordinates for the destinations as well. Once I had everything joined, I was able to create new fields for latitude, longitude, path order, and route as indicated in Andy's tutorial.
Now that I finally had my complete data set built, it was time to start vizzing. I had an idea about doing small multiple maps. I had seen another post that Ben Jones had done on small multiple maps, and I've really been wanting to do a viz like that. After looking at the data, I identified the top 10 airports in terms of overall flights, and I decided to narrow my viz to just those. With only the top 10 airports, there are still 1.2 million flight records, so this met the requirements for the contest.
Now that I had the Z scores calculated, I put together a quick heatmap of the airports by month to look at which months were more affected by the weather. As soon as I saw the heatmap, I started thinking about how to build a user interface with the heatmap as the primary control for the dashboard. Since you can click the columns for the months, rows for the airports, and individual cells for the intersection of the rows and columns, you could conceivably control a dashboard without the use of filters. I thought this would be a much more immersive user experience since the user can simply click the heatmap to filter the views.
As I previously mentioned, I wanted to be able to build a small multiple view of the top 10 airports. Once I had the map put together, I was able to color the paths of each flight by the Z scores I had previously calculated. The Z score easily lets you see which flights have longer flight delays when there is precipitation.
This was a really fun viz to build even though it was quite a bit of work to get the data set together. I'm really happy Tableau upgraded Public so that it would allow over a million records. I hope this viz helps people find new insights about the impact of precipitation on flight delays. Check it out!