This has turned out to be an epic weekend. The Summer Games are wrapping up, NFL preseason football has started, and what did I happen catch this evening after coming inside from the 85 degree heat wave here in Seattle? That’s right, Shark Week has kicked off over on the Discovery Channel. If there is a better trifecta out there, I’m going to be hard pressed to find it.
I’ve always been interested in sharks so naturally one of the first things that came to mind this year was whether I could hunt down (I guess that pun was intended) some data on shark attacks. There are a couple of places where you can find some information, but finding pristine data here doesn’t look possible. Nonetheless, if you head over to the Global Shark Attack File, you can get to an incident log that you can download in Excel. Once we’ve got the data in Excel, you are not too far away from being able to display it on map with Excel 2013.
The Shark Attack File contains data about locations, but as you will see here in a bit, the data is far from perfect. I don’t have exact coordinates and as you can see from a snapshot of the data, if you wanted this to be perfect, you would have some work in front of you.
That said, one of the features of the maps in Power View is that the data can be automatically geocoded by Bing. This means that even for cases where the data is not absolutely perfect (think about the times where you’ve used Bing to get directions and didn’t spell something correctly and it still was able to resolve your route), it still gives it a best shot effort. The nice thing about having Power View in Excel though, is that if you notice the data is not being plotted on the map correctly, you can just go in and fix the data and the spreadsheet and your map will be updated.
Let’s get started. I’ve imported the data into Excel as you can see here. The data goes way back to even dates before 1554 where apparently it was a good idea to be wearing armor in the event that you were bitten by shark.
Next, we go to the ribbon in Excel and Insert –> Power View. This recreates the table in the Power View sheet that is generated. Go ahead and delete that table. Now, go to the model explorer over on the right and add Country and Case ID to the table. Then, from the drop zone, select the Case ID field and switch it to a count.
Next, go to the Design tab of the ribbon and choose the option to change the table into a map.
And there you have it. By the way, there is another new feature in Power View that allows you to add hyperlinks to your report. Since I pulled the Excel file down from the Shark Attack File, I’ll place a link to this on the report. To do that, simply insert a text box and type in the text along with the URL. Here is the textbox I added to the report.
Finally, I want to drill into the actual locations where the shark attacks occurred instead of just looking at the Country level of data. To do this, I’m going to flip over to the Power Pivot window and add a couple of calculated fields. I added one to represent “Country and Area” and then I added another one for “Location and Area”. This will allow us to drill down a couple levels. Mark these two fields as Geographic Type = Place, which you can do rom from the Advanced section of Power Pivot.
Switch back to Excel and the Power View sheet and you should see your new calculated fields. Add them to the Locations drop zone.
Now you can drill down (double click) on the Countries to see the states and the locations of the attacks. Here I’ve drilled into California.
If you want to see which of these attacks were fatal, simply click on the Fatal Field.
You’ll notice here that the data is not perfect, as some of the attacks look like they occurred many miles away from the shore (maybe there are aquariums in the area), but if you hover over those data points in question you’ll see that the location information for those points is just not defined well enough. Still, for five minutes of your time with a data set that is far from “clean”, this definitely gives you a very good idea of where shark attacks are occurring.
In future Shark Week related posts, I’ll add in a few more pieces of functionality, but for now, they are starting to show sharks jumping out of the water again on the television and I’m not going to miss that 🙂
Download this workbook here.