Making Pandas DataFrames from API Requests

In this example, we will use the U.S. Geological Survey’s API to grab a JSON object of earthquake data and convert it to a pandas.DataFrame.

USGS API: https://earthquake.usgs.gov/fdsnws/event/1/

Get Data from API

import datetime as dt
import pandas as pd
import requests

yesterday = dt.date.today() - dt.timedelta(days=1)
api = 'https://earthquake.usgs.gov/fdsnws/event/1/query'
payload = {
    'format': 'geojson',
    'starttime': yesterday - dt.timedelta(days=30),
    'endtime': yesterday
}
response = requests.get(api, params=payload)

# let's make sure the request was OK
response.status_code

Response of 200 means OK, so we can pull the data out of the result. Since we asked the API for a JSON payload, we can extract it from the response with the json() method.

Isolate the Data from the JSON Response

We need to check the structures of the response data to know where our data is.

earthquake_json = response.json()
earthquake_json.keys()

dict_keys(['type', 'metadata', 'features', 'bbox'])

The USGS API provides information about our request in the metadata key. Note that your result will be different, regardless of the date range you chose, because the API includes a timestamp for when the data was pulled:

earthquake_json['metadata']

{'generated': 1686247399000,
 'url': 'https://earthquake.usgs.gov/fdsnws/event/1/query?format=geojson&starttime=2023-05-08&endtime=2023-06-07',
 'title': 'USGS Earthquakes',
 'status': 200,
 'api': '1.14.0',
 'count': 11706}

Each element in the JSON array features is a row of data for our dataframe.

type(earthquake_json['features'])

list

Your data will be different depending on the date you run this.

earthquake_json['features'][0]

{'type': 'Feature',
 'properties': {'mag': 0.5,
  'place': '17km SE of Anza, CA',
  'time': 1686095356120,
  'updated': 1686178472172,
  'tz': None,
  'url': 'https://earthquake.usgs.gov/earthquakes/eventpage/ci40479680',
  'detail': 'https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=ci40479680&format=geojson',
  'felt': None,
  'cdi': None,
  'mmi': None,
  'alert': None,
  'status': 'reviewed',
  'tsunami': 0,
  'sig': 4,
  'net': 'ci',
  'code': '40479680',
  'ids': ',ci40479680,',
  'sources': ',ci,',
  'types': ',nearby-cities,origin,phase-data,scitech-link,',
  'nst': 21,
  'dmin': 0.07016,
  'rms': 0.26,
  'gap': 86,
  'magType': 'ml',
  'type': 'earthquake',
  'title': 'M 0.5 - 17km SE of Anza, CA'},
 'geometry': {'type': 'Point', 'coordinates': [-116.5405, 33.447, 11.37]},
 'id': 'ci40479680'}

Convert to DataFrame

We need to grab the properties section out of every entry in the features JSON array to create our dataframe.

earthquake_properties_data = [
    quake['properties'] for quake in earthquake_json['features']
]
df = pd.DataFrame(earthquake_properties_data)
df.head()

	mag	place	time	updated	tz	url	detail	felt	cdi	mmi	...	ids	sources	types	nst	dmin	rms	gap	magType	type	title
0	0.50	17km SE of Anza, CA	1686095356120	1686178472172	None	https://earthquake.usgs.gov/earthquakes/eventp...	https://earthquake.usgs.gov/fdsnws/event/1/que...	NaN	NaN	NaN	...	,ci40479680,	,ci,	,nearby-cities,origin,phase-data,scitech-link,	21.0	0.070160	0.26	86.0	ml	earthquake	M 0.5 - 17km SE of Anza, CA
1	1.24	6 km W of Blanchard, Oklahoma	1686095288900	1686140204481	None	https://earthquake.usgs.gov/earthquakes/eventp...	https://earthquake.usgs.gov/fdsnws/event/1/que...	0.0	1.0	NaN	...	,ok2023lavp,	,ok,	,dyfi,origin,phase-data,	64.0	0.089083	0.44	42.0	ml	earthquake	M 1.2 - 6 km W of Blanchard, Oklahoma
2	5.20	southeast of the Loyalty Islands	1686094693572	1686095577040	None	https://earthquake.usgs.gov/earthquakes/eventp...	https://earthquake.usgs.gov/fdsnws/event/1/que...	NaN	NaN	NaN	...	,us7000k6rg,	,us,	,origin,phase-data,	32.0	2.755000	0.82	85.0	mb	earthquake	M 5.2 - southeast of the Loyalty Islands
3	0.24	10 km NNE of Government Camp, Oregon	1686094611590	1686118209350	None	https://earthquake.usgs.gov/earthquakes/eventp...	https://earthquake.usgs.gov/fdsnws/event/1/que...	NaN	NaN	NaN	...	,uw61931036,	,uw,	,origin,phase-data,	3.0	0.032620	0.04	239.0	ml	earthquake	M 0.2 - 10 km NNE of Government Camp, Oregon
4	-0.01	8 km NNE of Government Camp, Oregon	1686094117310	1686117953180	None	https://earthquake.usgs.gov/earthquakes/eventp...	https://earthquake.usgs.gov/fdsnws/event/1/que...	NaN	NaN	NaN	...	,uw61931031,	,uw,	,origin,phase-data,	4.0	0.011510	0.04	173.0	ml	earthquake	M 0.0 - 8 km NNE of Government Camp, Oregon

5 rows × 26 columns

(Optional) Write Data to CSV

df.to_csv('earthquakes.csv', index=False)