Introduction
For day 2 of dashboard week, our task was to use the data (that can be found here) on the National Geospatial Information Agency (NGIA). However, we had to supplement it with data found elsewhere. Originally I wanted to find shipping routes, poverty data, or social inequality data. However, due to time constraints, I wasn’t able to achieve those tasks. What I was able to do was to combine Exclusive Economic Zones (EEZ) and country borders together with the longitude and latitude in the XML document of pirate attacks. In doing this I was able to assign the attacks to whichever country was closest. I may be making assumptions about where the attackers originated from, and in the majority of cases I think this is the case, but there is the possibility that I could be wrong.
In matching the incidents with a country, I had found something by accident: that some attacks occurred outside international borders. I was able to use this in Tableau to visually see if the types, and number, of attacks, differed when outside the international borders.
Preparation
To begin I will go through my Alteryx workflow.
It starts with obtaining the data form the NGIA, I use an append tool, which was absolute in the end to get the Naval Regions as a numeric field, as they were in Roman numeral format. I could not have done this viz without some help from my colleague Fairuz Kahn, who cleaned a lot of the data. Below is showing some of the steps used to get the data and clean it.
Next, I created points from the given Lat and Long from the XML file. I matched this to the exclusive economic zone (EEZ) data, to get whether or not the incident was within international borders. There were many different calculations a lot of us did to get the “Victims” type down to a workable level, as there were over 1000. We thought we could group some of them, but it proved quite difficult. We came up with a few solutions, but all victim types will be flawed in some way, because of the data entry.
The output is put into a hyper file and visualised in Tableau.
Below is the final workflow as a whole.
Visualisation
Before I proceeded with Tableau I needed to remember what the task was for today. I had to supplement the data, I had already done that with the EEZ spatial file but wanted to seek out more. So I used a dataset of annual crude oil prices. Below is the finished product. On the left there is a map showing each incident, I coloured them by the region they are in – white is if outside the borders. On the right, there is a bar chart showing incidents over time, clicking on the bar here will “activate” the oil price and incident analysis below it. I found that merchant ships were targeted very heavily, to begin with, but as time went on, less so. The type to “Take its place” is Tankers. With the oil price increase, this seems to make sense, as now they have become more valuable. On the Bottom right there is another bar graph that shows the top 5 of any region chosen, grey indicates no incident happened – like kidnapping, ransom, or death. Black means that something bad happened.
To get the oil price data into Tableau, I did a simple blend on the date. Here is a link to the Viz
TL;DR
Made an alteryx workflow with spatial joins, then supplemented with oil data in Tableau. Found that merchant’s vessels are targeted, but increasingly tankers are being attacked. Surprisingly Indonesia had the highest volume of piracy.
