updated data through: Fri, 18 Mar 2011 03:15:39 GMT

updated with Twitter data and chart: Fri, 18 Mar 2011

updated with Nikkei and DOW financials to # of earthquakes on Wed, 16 Mar 2011

special note: It has been interesting to hear some of the comments coming back after people reading this blog. I have received, “great job”, “interesting view of the earthquake data”, to “you suck”, “you are trying to capitalize on it” and others. Funny, I’m not selling a thing below. All we did was bring in data from the USGS site to see the data in a different perspective so we could get a better understanding of the quakes. We explained the tools we used so if you wanted to try it yourself you could. The power of the Internet, everyone can have a comment.  BTW, my favorite comment was why didn’t we do analysis to predict the event so they could have taken steps to mitigate it.     Statistics are important.  If you don’t understand the numbers, you probably don’t understand.

I just wrote a blog about how we mashed up data from the USGS web site so we could get an understanding of the Japan catastrophe.   We wanted to do it because we thought it would be a good training exercise in using Microsoft Excel and PowerPivot.  But when we got the data ready, we were not prepared for the insight we would find.  Below is my best ability to provide some analysis into the earthquake data we have to date.

The following screen shot is the Excel 2010 spreadsheet that we created which you can download the Excel spreadsheet here.   If you want to use the slicing features, you need to have Microsoft PowerPivot installed.  Or you can go to the online report which can be found here, the slicers do not work, but you can see it live.

View Live Report

Download Spreadsheet

Ok, onto the analysis

I believe the interesting information is in raw counts.  These numbers were taken at 3/15/2011 at 2pm Pacific Time.  The numbers are staggering and are basically telling you the area is shaking constantly.

• Total Earth Quakes:  496 534 555 597 Quakes!   Only 600 719 776 847 total world wide in our data set…
• Average Quakes per day: 83 79.9 77.6 77.0 
• Average Quakes per hour: 27.7 30.0  32.3 35.3 
• Average Quake Depth (miles): 16.2 16.5

Then we moved onto looking at the magnitude of the quakes.   You can see the sheer volume of quakes are in the 4.x and the 5.x range.  All very significant.  But they had 40 in the 6.x range.   Even in the 6.x range, those are enough to cause major damage.   40 is basically saying you are experiencing all day over 4 days!

Now we will look at the total number of quakes per day.   You will see they were still experiencing some quakes leading up to March 11 when the big quake occurred.  But the volume of quakes following the next 4 days was amazing.   On March 14, 3 days after the big quake, they had 60 earthquakes!!

Now onto the depth of the quakes.  You can see between 15 and 16 miles down, there was some major action going on.  Something big is happening down there and it makes you wonder what it looks like at that depth.

My final bit of analysis reveals something very interesting.   The quakes really start in the morning and go most of the day and then fall off during the evening hours.   So what is going on during the day that would cause the earthquakes to pickup?  I’m sure there is some scientist out there that can tell us, they just haven’t. 

We just added another chart that puts the financial markets overlaid onto the number of earthquakes per day to give you cause and effect view.  You see a flat line of the Nikkei (gray line) on the 12th and 13th because that was a weekend.  But when the markets opened on Monday, you see the Nikkei start taking a dive.   The green line is the DOW index in the U.S.

We started pulling Twitter Tweets on the event.  We used keywords, Japan #nuclear, Japan #earthquakes, Japan #tsunami.  Interesting to see all the tweets on the different days.  During the last several days, I’m surprised that #nuclear is not higher.  The day of the big quake is when you see the most tweets.  I wish we had data for which country the tweets came from.  That would be very interesting to see.

We will continue to update the data for another week.  I think the statistics are VERY interesting and really show the constant shaking Japan is experiencing every day.  Please download the spreadsheet and continue to add your data and send it back if you find something interesting.

We are all praying for the people of Japan

Regards

Patrick