Answer: Earthquake data?

I asked a fairly simple question--or at least a question that seems simple when stated like this:  

1.  Can you find data about when and where earthquakes happen and then chart it so we can see location and number over time?  Ideally, you would create (or find) a chart showing number of earthquakes by US state over the past 10 years.  Even better, if that chart could show month-by-month, then we should be able to spot both any seasonality effects AND resolve the question about Oklahoma.  

As several Regular Readers pointed out, the obvious source of earthquake data is the USGS (United States Geological Survey).  But if you don't know that ahead of time, how do you find out? 

The discovery query: 
   
     [ earthquake data ] 

shows us that 8 out of the top 10 results are all links to various web pages in USGS.GOV.  A little drilling into those links shows that the USGS really IS a great source of earthquake data.  (And reading around a little bit reveals that they collect data worldwide; not just for the US or just the Americas.)  

It doesn't take long to figure out that USGS provides a very nice earthquake database that you can pull data from with a single query. 

  http://earthquake.usgs.gov/earthquakes/search/ is the go-to source for this stuff.  

http://earthquake.usgs.gov/earthquakes/search/

I set the date start / end range to 2004-08-01 and 2014-08-01, and the magnitude to be 3, and the geographic region to be West = -128, North = 49, East = -62, South = 20  (that's roughly from the coast off Vancouver to the ocean just east and a bit south of Cuba).  

I asked for the result to be delivered as a CSV file, that way I knew I could then pull it into Google Sheets and do whatever I wanted with it.   (To do that, just click on the radio button that says "CSV" in the upper right.) 

The USGS then runs the query against their backend (which is NOT indexed by Google), and downloads a 1.2Mb file onto your computer.  

I opened up that CSV file in Google Sheets and see something like this:   

(BTW,  here is the link to my earthquakes data spreadsheet.) 

Each earthquake is one row of data, time & date in column 1, lat/long in columns 2 and 3, etc.  

Conveniently, in column N there's a place name that looks like this: 

20km ESE of Anza, California

it's just a city, or a location (20 km ESE of...) AND a state designation. (Or, if you look through the data, you'll see lots of "Mexico" in there as well, along with the closest city names.)

For SearchResearchers, a great practice to follow is to ALWAYS provide the metadata with the Sheet. My common practice is to write a comment and insert it on cell A1 (upper left). That tells me when, where, and how I downloaded the original data.

My metadata comment in the Sheet. ALWAYS do this with your data,
even if you think you won't come back to it.  Maybe ESPECIALLY if you
don't think you'll come back--that's when you'll need it the most. 

Then, as you clean, filter, and munge the data, you NEVER work on the original data. Always make a copy.

As you can see, I made several tabs across the bottom.  ("cleaned up data" "NV" "OK" "CA" "Summary")  

If you look at the "cleaned up data," it's just a copy of the original data (all 7,386 rows) with a couple of extra columns.  The most important for us is that I wrote a little function that would pull the name of the state out of Column N.  (Remember, the name of the state is the text following the last comma--that was the magic spreadsheet function I wrote--check out Column F in the "cleaned up data" for the state names).  

NOW I'm ready to do my state-by-state comparison.  

I decided to look at just the top 3 states with the most earthquakes over the past 10 years.  That's California (CA), Nevada (NV), and Oklahoma (OK).  

I filtered the "cleaned up data" by California (click "Filter," then click on the little triangle at the top of the column to select the values, e.g., "California," that you want to filter-by).  

I copied the entire spreadsheet (now filtered to show only California earthquakes) into a new tab (the "CA" tab) using "Paste Special>Values" which then gives me a sheet of just the California earthquakes.  

Repeat that process for the Nevada quakes and the Oklahoma quakes. 

Now I'm set to pull the data for each of the states by year.  So I made a little table, one row / year, and wrote a function to count the number of earthquakes in each table by year.  

That countif(C2:C4000, "2014") function just counts all of the cells in the C column (which is the year of the earthquake). 

I did that in each of the three states sheets, and then copied all of the state's data into the Summary sheet. 

This answers our original question ("Is Oklahoma having more earthquakes?") with an emphatic YES!  In the early 2000's, Oklahoma had only a tiny number of quakes > 3.0 (onesies and twosies).  Starting in 2009, the number went way up (by a factor of 10X), and has been climbing ever since.  Oklahoma now has more earthquakes / year than California (which was the previous leader).  

And note that the chart only goes to August 1 for 2014: the year's not over yet, so expect both CA and OK numbers to grow.  

As you look at the chart, another question leaps out:  What happened in California during 2010?   Short answer:  I don't know.  Another Search Challenge? 

But the consensus seems to be that fracking in Oklahoma seems to be causally connected to the earthquakes that the region is experiencing.  By doing a search in Scholar for [Oklahoma earthquakes] I found a pretty compelling article in the journal Science, "Sharp increase in central Oklahoma seismicity since 2008 induced by massive wastewater injection" that makes the case that the two are connected.  (Of course, we can't infer that just from correlation, but the Science article goes into great detail about what the causal connection seems to be.)  

Just to double-check, I also poured this same data into Google Fusion Tables.  Why?  I know they have a handy "heat map" display that shows the number of earthquakes as a colored overlay on the map.  (My Fusion Table heatmap for US Earthquakes 2013-2014.)  

(Note that "only" the first 1,000 earthquakes are shown here. Those are the earthquakes for 2013 and 2014, up to August 1 of 2014.)  

As is evident, Oklahoma is feeling more than its "fair share" of quakes.  


Search lessons:  Often, searching out the answer is something that can't be done just by looking up a single number or article.  Sometimes you actually have to find the original data and then wrestle IT to the ground by doing some manipulations.  

In this case, I used spreadsheets to transform the data from a purely CSV format into something I could easily use, both as a line graph and as a heatmap.  

And, of course, always track your metadata.  (I say this through harsh personal experience.  I can't tell you how many data sets I've lost because I couldn't figure out how they'd been derived.  Don't let this happen to you!) 


Special Offer:  I'll make a short video showing HOW I did it.  (If enough people write in with comments asking for more details on "how did you do that spreadsheet trick with the USGS data?")  

Search on!