I was listening to Planetary Radio the other night and they had an interesting interview with Rosaly Lopes, a researcher at the Jet Propulsion Laboratory who has discovered more volcanoes than anyone else. Her discoveries were of volcanoes on other planets and satellites. This interview got me thinking — just how many active volcanoes are there on Earth and where are they? This looks like a good job for Excel and pivot tables.
Time to go to the web and start hunting around. Very quickly I encountered a number of web sites (e.g. here and here) with lists of “active” volcanoes. Depending on your definition of active volcano, I have found sites that list between 400 and 1500 items. For no particular reason, I ended up focusing on the Volcano World web site from Oregon State University. They have a list of 430 volcanoes that was easy to import into Excel and will give me a feel for the number of volcanoes and where they are. Once the list is in Excel, we can start to ask questions about the data and get some answers.
One other quick point — we have not discovered all of the volcanoes yet. For example, you occasionally hear of a previously unknown underwater volcano being discovered. So these lists do change occasionally. Also, the lists sometimes combine two nearby volcanoes in a single entry, like Tanaga and Takawangha. So you may see different numbers of volcanoes on different lists.
My question was about the number and location of the volcanoes. I also want to know what countries have the most volcanoes. I used a pivot table to divide the volcanoes up by country. Table 1 shows the results.
|Papua New Guinea||17|
|Cape Verde Islands||2|
|St. Kitts and Nevis||2|
|Democratic Republic of Congo||2|
|South Atlantic Ocean||1|
|Canary Islands (Spain)||1|
|Lesser Sunda Islands||1|
I was surprised that the US had so many volcanoes. Let’s take a closer look at the volcano count in the US by state. Just out of curiosity, I will separate out the Aleutian Islands from Alaska so that I can see where the volcanoes are in that area. Table 2 shows this data.
|Other Pacific Islands||2|
Now I want to look at the distribution percentage of volcanoes by state. I will recombine the Aleutians with Alaska. I will list non-state volcanoes in the “other Pacific Islands” category (e.g. Guguan and Pagan Islands). Table 3 shows this data.
|Alaska and Aleutians||48.15%|
|Other Pacific Islands||2.47%|
So nearly half of the US volcanoes are in Alaska and the Aleutians.
What I learned here was that the US has many more volcanoes than I would have thought and nearly half of them are in Alaska. I also showed that pivot tables are great for slicing up data like this.