> ## Content Index
> Fetch the complete content index at: https://www.bambooweekly.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Bamboo Weekly #3: Earthquakes (solutions)
- URL: https://www.bambooweekly.com/earthquakes-solution/
- Published: 2023-02-16T16:01:24.000Z
- Updated: 2026-08-07T09:27:29.000Z
- Description: Get practice working with CSV files, working with datetime values, string values, and cleaning data.
- Author: Reuven M. Lerner
- Tags: csv, datetime, strings, cleaning

## This week’s topic: Earthquakes

Yesterday, I asked you to download information from the USGS, and to answer some questions about earthquakes that have taken place since January 1st, 2000:

1. Read the downloaded CSV file (which will be called \`query.csv\`, but which I renamed to \`earthquake-data.csv\` on my computer) into Pandas.
2. How many seismic events take place each year? In which of the last 20 years did we have the greatest number of such events?
3. What are common magnitudes? Looking only at the integer portion of the magnitudes, how often does each value occur?
4. How many seismic events took place in Turkey on February 6th? What was their average magnitude? What was the mean magnitude?
5. Are earthquakes common in Turkey? From the "place" column, extract the text following the final comma, and get the 30 most common places in the world with earthquakes.
6. Are serious earthquakes common in Turkey? Rerun the previous query, but only look for those with a magnitude of 5 or greater.

## Discussion

First, I downloaded a CSV file from the USGS:

[https://earthquake.usgs.gov/fdsnws/event/1/query.csv?starttime=2000-01-01%2000:00:00&endtime=2023-02-15%2000:00:00&minmagnitude=2.5&orderby=time&producttype=losspager](https://earthquake.usgs.gov/fdsnws/event/1/query.csv?starttime=2000-01-01%2000:00:00&endtime=2023-02-15%2000:00:00&minmagnitude=2.5&orderby=time&producttype=losspager&ref=bambooweekly.com)

On my system, the file was named \`query.csv\`. I renamed it to \`earthquake-data.csv\`. The file is a normal CSV file, with column headers. What do those columns mean? For that, we need to use the “data dictionary” provided with the data set, which explains the headers, values, and meanings.

In our case, the data dictionary is on the USGS site: https://earthquake.usgs.gov/data/comcat/

In order to figure out what each column in our downloaded CSV means, look at the column name, and then find the corresponding name in the data dictionary. For example, the “mag” column contains the magnitude, and is fully documented [here](https://earthquake.usgs.gov/data/comcat/?ref=bambooweekly.com#mag), including a description of the data type, the range, and the meaning of the column.

I could have read the CSV into Pandas with the simplest possible call to [read\_csv](https://www.bambooweekly.com/pandas-read-csv/):

```
df = pd.read_csv(filename)
```

But because we’re going to be using the “time” column to retrieve time-and-date-related things, I asked Pandas to parse it as a datetime column:

```
df = pd.read_csv(filename, parse_dates=['time'])
```

If I had a truly large amount of data, then I would probably fidget a bit with the dtypes, as well as reduce the number of columns I read into memory. But I found that the entire data set, with more than 8,000 events, was less than 6 MB in size, and thus decided not to spend time on it.

With the data frame read into memory, I was ready to answer the first questions:

### How many seismic events take place each year? In which of the last 20 years did we have the greatest number of such events?

In other words: We have data from many different years, and want to know how many times each year appears in the data set.

Thanks to our use of “parse\_dates” when we read the CSV file, the “time” column has a dtype of “timestamp”. We can use the “dt” accessor on a timestamp [to retrieve different parts of it](https://pandas.pydata.org/pandas-docs/stable/search.html?q=dt&ref=bambooweekly.com), including the year:

```
df['time'].dt.year
```

This gives us a series of integers, the years in which the measurements were taken. How can we found out how many times each year appears in the data set? With my favorite method, [value\_counts](https://www.bambooweekly.com/pandas-value-counts/). Not only will value\_counts tell us how many times each year appears in the data set, it’ll also sort it for us from the most common to the list common:

```
df['time'].dt.year.value_counts()
```

### Next, I wanted to find out how frequently each magnitude of an earthquake occurs.

*Side note: Researching this week’s question, I was reminded that the measurement scale is logarithmic, meaning that a magnitude-6 earthquake is 10 times larger than a magnitude 5 earthquake. I didn’t realize, however, that the damage done isn’t 10 times greater — it can be more than 30 times greater. Yikes. And I also learned that we no longer use the Richter scale, even though people commonly refer to it that way. And that the scale describes the logarithm of the amplitude of the earthquake’s waves, as measured on a seismograph.*

It might make sense to use value\_counts on the “mag” column, which describes the magnitude of each earthquake. But then we’ll get a separate result for each individual value. Given that these are floating-point values, we’ll get a ton of results, making it hard to understand them.

I thus asked you to only look at the integer portion of the magnitude. My favorite way to do that is simply to call “astype” on a column, and convert it to be an integer — in this case, an 8-bit integer:

```
df['mag'].astype(np.int8)
```

With the magnitudes now in our hands as integers, we can count how often each magnitude appears with our friend value\_counts:

```
df['mag'].astype(np.int8).value_counts()
```

We can see that magnitude-7 earthquakes, like the one that took place in Turkey and Syria, are pretty rare. (Remember that this data set only includes readings with a magnitude of 2.5 and greater, so many smaller earthquakes have been ignored.)

### What percentage of earthquakes in our data set were 7 or greater? We can find out by passing “normalize=True” to “value\_counts”:

```
df['mag'].astype(np.int8).value_counts(normalize=True)
```

Next, I wanted to find out how many seismic events occurred in Turkey on February 6th, 2023\. (And yes, the earthquake took place on the Turkey-Syria border, but I’ll treat it as being in Turkey for the sake of ease.)

In order to answer this, we’ll need to filter our data set in two ways: First, by finding all of those rows in which the “place” contains the string “Turkey,” and then by finding rows in which the “time” is on February 6th, 2023.

Let’s start with the time factor: We could write a complex set of queries to find rows in which the year is 2023, the month is 2, and the day is 6\. But in many ways, it’s easier to turn this column into the data frame’s index, thus making our data frame into a time series:

df.set\_index('time')

With the “time” column now set to be our index, we can retrieve all of the rows from February 2nd, 2023, regardless of time, with the following:

```
df.set_index('time').loc['2023-02-06']
```

But of course, this retrieves all of the rows, from all around the world, for which this is true. We’re only interested in those that took place in Turkey.

If we have a string column (which the “place” column is), then we can use a number of string methods on it using [the “str” accessor](https://pandas.pydata.org/pandas-docs/stable/user%5Fguide/text.html?ref=bambooweekly.com). These methods include all of the regular Python methods, some additional methods implementing behavior from Python operators, and also some methods borrowed from other languages and libraries.

To find the rows in which “Turkey” is mentioned in the “place” column, we can say:

```
df['place'].str.contains('Turkey')
```

This returns a boolean series. We can then apply the boolean series to all of df, returning all of the rows for Turkey.

Except that this isn’t *quite* true, at least not yet. That’s because some of the rows lack a value for “place.” They are set to be NaN, aka “not a number” or “missing data.” We could delete all of the rows in “df” containing any NaN values, but that seems a bit extreme. Instead, I’d like to remove all of the rows containing NaN in the “place” column:

```
df = df.dropna(subset='place')
```

Note that calling “dropna” returns a new data frame. It doesn’t modify the existing one. (And yes, you can pass inplace=True, but the core Pandas developers have warned that this is a bad idea, and that “inplace” will go away in a future version.)

Having removed NaN values for “place”, we can now get all of the rows having to do with Turkey:

```
df.loc[df['place'].str.contains('Turkey')]
```

And then we can apply to that the index-setting-and-filtering code from before:

```
df.loc[df['place'].str.contains('Turkey')].set_index('time').loc['2023-02-06']
```

But we’re not interested in all of the data from that day in Turkey. We just want the “mag” column. When the “loc” accessor can take a single argument, it functions as a row selector, and retrieves all of the data frame’s columns. But when we pass it two arguments, the second is a column selector. We can thus restrict the retrieval:

```
df.loc[df['place'].str.contains('Turkey')].set_index('time').loc['2023-02-06', 'mag']
```

And if we want to get the descriptive statistics for the magnitude of earthquakes in Turkey on February 6th, we can say:

```
df.loc[df['place'].str.contains('Turkey')].set_index('time').loc['2023-02-06', 'mag'].describe()
```

All told, we see that there were nine (!) earthquakes on that day, with a median measurement of 6.0, and a maximum of 7.8\. [Wikipedia’s entry on the Richter scale](https://en.wikipedia.org/wiki/Richter%5Fmagnitude%5Fscale?ref=bambooweekly.com#Richter%5Fmagnitudes) says that in a 6.0 earthquake, “poorly designed structures receive moderate to severe damage.” And an earthquake of 7.8? The article says it “causes damage to most buildings, some to partially or completely collapse or receive severe damage.”

Indeed, that’s what we saw happen, much to our horror.

Finally, I was curious to know if Turkey commonly has earthquakes. To find this out, I asked you to use the location following the comma in the “place” column. If we were in pure Python, then we could do that with “[str.split](https://docs.python.org/3/library/stdtypes.html?highlight=str%20split&ref=bambooweekly.com#str.split)”, then grabbing the final element of the resulting list. We can do the same thing here via the “str” accessor:

```
df['place'].str.split(',').str.get(-1)
```

Notice that we had to use “str” twice here: The first time, we split on comma, returning a list. We then used “str” again — yes, on a list! — to retrieve the final item, via the index -1.

Maybe it’s just me, but I hate having the leading and trailing whitespace that resulted from this call to split. So I decided to use “str” a third time, invoking “[str.strip](https://docs.python.org/3/library/stdtypes.html?highlight=str%20strip&ref=bambooweekly.com#str.strip)” to remove the whitespace:

```
df['place'].str.split(',').str.get(-1).str.strip()
```

I found the 30 most common locations by invoking “value\_counts” and then limiting the output to the first 30 elements:

```
df['place'].str.split(',').str.get(-1).str.strip().value_counts().head(30)
```

We see that Turkey actually hasn’t been a very common location for earthquakes in the last 23 years. What if we limited our search to earthquakes of magnitude 5 or more?

To get that result, I re-ran the above query — but instead of running it on df, the entire data frame, I ran it on the result of filtering df’s rows with a magnitude 5 or more:

```
df.loc[df['mag'] >= 5, 'place'].str.split(',').str.get(-1).str.strip().value_counts().head(30)
```

Notice that once again, I use “loc” along with two arguments, the row selector (where mag >= 5) and the column selector (place). After this filtering, we see that Turkey doesn’t have a history of major earthquakes, at least relative to other places in the world.

That’s it for this week’s analysis. Paid subscribers can participate in the discussion, but even if you’re not a paid subscriber, I’d love to hear your thoughts on this edition of Bamboo Weekly. And if you have ideas for future data sets, just send them along.

Meanwhile, here’s my Jupyter notebook: [https://drive.google.com/file/d/12AeffGZfiCyr\_yA0G3\_zOqJBAB2DdNE5/view?usp=drive\_link](https://drive.google.com/file/d/12AeffGZfiCyr%5FyA0G3%5FzOqJBAB2DdNE5/view?usp=drive%5Flink&ref=bambooweekly.com)

See you next Wednesday!

Reuven