ch_02/3-making_dataframes_from_api_requests.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Making Pandas DataFrames from API Requests\n",
    "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`.\n",
    "\n",
    "USGS API: https://earthquake.usgs.gov/fdsnws/event/1/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get Data from API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "200"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import datetime as dt\n",
    "import pandas as pd\n",
    "import requests\n",
    "\n",
    "yesterday = dt.date.today() - dt.timedelta(days=1)\n",
    "api = 'https://earthquake.usgs.gov/fdsnws/event/1/query'\n",
    "payload = {\n",
    "    'format': 'geojson',\n",
    "    'starttime': yesterday - dt.timedelta(days=30),\n",
    "    'endtime': yesterday\n",
    "}\n",
    "response = requests.get(api, params=payload)\n",
    "\n",
    "# let's make sure the request was OK\n",
    "response.status_code"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "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.\n",
    "\n",
    "### Isolate the Data from the JSON Response\n",
    "We need to check the structures of the response data to know where our data is."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['type', 'metadata', 'features', 'bbox'])"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "earthquake_json = response.json()\n",
    "earthquake_json.keys()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "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:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'generated': 1604267813000,\n",
       " 'url': 'https://earthquake.usgs.gov/fdsnws/event/1/query?format=geojson&starttime=2020-10-01&endtime=2020-10-31',\n",
       " 'title': 'USGS Earthquakes',\n",
       " 'status': 200,\n",
       " 'api': '1.10.3',\n",
       " 'count': 13706}"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "earthquake_json['metadata']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each element in the JSON array `features` is a row of data for our dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "list"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(earthquake_json['features'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Your data will be different depending on the date you run this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'type': 'Feature',\n",
       " 'properties': {'mag': 1,\n",
       "  'place': '50 km ENE of Susitna North, Alaska',\n",
       "  'time': 1604102395919,\n",
       "  'updated': 1604103325550,\n",
       "  'tz': None,\n",
       "  'url': 'https://earthquake.usgs.gov/earthquakes/eventpage/ak020dz5f85a',\n",
       "  'detail': 'https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=ak020dz5f85a&format=geojson',\n",
       "  'felt': None,\n",
       "  'cdi': None,\n",
       "  'mmi': None,\n",
       "  'alert': None,\n",
       "  'status': 'reviewed',\n",
       "  'tsunami': 0,\n",
       "  'sig': 15,\n",
       "  'net': 'ak',\n",
       "  'code': '020dz5f85a',\n",
       "  'ids': ',ak020dz5f85a,',\n",
       "  'sources': ',ak,',\n",
       "  'types': ',origin,phase-data,',\n",
       "  'nst': None,\n",
       "  'dmin': None,\n",
       "  'rms': 1.36,\n",
       "  'gap': None,\n",
       "  'magType': 'ml',\n",
       "  'type': 'earthquake',\n",
       "  'title': 'M 1.0 - 50 km ENE of Susitna North, Alaska'},\n",
       " 'geometry': {'type': 'Point', 'coordinates': [-148.9807, 62.3533, 5]},\n",
       " 'id': 'ak020dz5f85a'}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "earthquake_json['features'][0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Convert to DataFrame\n",
    "We need to grab the `properties` section out of every entry in the `features` JSON array to create our dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mag</th>\n",
       "      <th>place</th>\n",
       "      <th>time</th>\n",
       "      <th>updated</th>\n",
       "      <th>tz</th>\n",
       "      <th>url</th>\n",
       "      <th>detail</th>\n",
       "      <th>felt</th>\n",
       "      <th>cdi</th>\n",
       "      <th>mmi</th>\n",
       "      <th>...</th>\n",
       "      <th>ids</th>\n",
       "      <th>sources</th>\n",
       "      <th>types</th>\n",
       "      <th>nst</th>\n",
       "      <th>dmin</th>\n",
       "      <th>rms</th>\n",
       "      <th>gap</th>\n",
       "      <th>magType</th>\n",
       "      <th>type</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.00</td>\n",
       "      <td>50 km ENE of Susitna North, Alaska</td>\n",
       "      <td>1604102395919</td>\n",
       "      <td>1604103325550</td>\n",
       "      <td>None</td>\n",
       "      <td>https://earthquake.usgs.gov/earthquakes/eventp...</td>\n",
       "      <td>https://earthquake.usgs.gov/fdsnws/event/1/que...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>,ak020dz5f85a,</td>\n",
       "      <td>,ak,</td>\n",
       "      <td>,origin,phase-data,</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.36</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ml</td>\n",
       "      <td>earthquake</td>\n",
       "      <td>M 1.0 - 50 km ENE of Susitna North, Alaska</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.64</td>\n",
       "      <td>15km NNE of Warner Springs, CA</td>\n",
       "      <td>1604102330860</td>\n",
       "      <td>1604114890185</td>\n",
       "      <td>None</td>\n",
       "      <td>https://earthquake.usgs.gov/earthquakes/eventp...</td>\n",
       "      <td>https://earthquake.usgs.gov/fdsnws/event/1/que...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>,ci39451271,</td>\n",
       "      <td>,ci,</td>\n",
       "      <td>,nearby-cities,origin,phase-data,scitech-link,</td>\n",
       "      <td>23.0</td>\n",
       "      <td>0.06759</td>\n",
       "      <td>0.16</td>\n",
       "      <td>123.0</td>\n",
       "      <td>ml</td>\n",
       "      <td>earthquake</td>\n",
       "      <td>M 0.6 - 15km NNE of Warner Springs, CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.09</td>\n",
       "      <td>15km NNE of Warner Springs, CA</td>\n",
       "      <td>1604102075440</td>\n",
       "      <td>1604102309453</td>\n",
       "      <td>None</td>\n",
       "      <td>https://earthquake.usgs.gov/earthquakes/eventp...</td>\n",
       "      <td>https://earthquake.usgs.gov/fdsnws/event/1/que...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>,ci39451255,</td>\n",
       "      <td>,ci,</td>\n",
       "      <td>,nearby-cities,origin,phase-data,scitech-link,</td>\n",
       "      <td>39.0</td>\n",
       "      <td>0.06308</td>\n",
       "      <td>0.22</td>\n",
       "      <td>91.0</td>\n",
       "      <td>ml</td>\n",
       "      <td>earthquake</td>\n",
       "      <td>M 1.1 - 15km NNE of Warner Springs, CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.34</td>\n",
       "      <td>15km NNE of Warner Springs, CA</td>\n",
       "      <td>1604101989680</td>\n",
       "      <td>1604102996061</td>\n",
       "      <td>None</td>\n",
       "      <td>https://earthquake.usgs.gov/earthquakes/eventp...</td>\n",
       "      <td>https://earthquake.usgs.gov/fdsnws/event/1/que...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>,ci39451263,</td>\n",
       "      <td>,ci,</td>\n",
       "      <td>,nearby-cities,origin,phase-data,scitech-link,</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.06228</td>\n",
       "      <td>0.15</td>\n",
       "      <td>129.0</td>\n",
       "      <td>ml</td>\n",
       "      <td>earthquake</td>\n",
       "      <td>M 0.3 - 15km NNE of Warner Springs, CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.59</td>\n",
       "      <td>13km NE of Indio, CA</td>\n",
       "      <td>1604101849950</td>\n",
       "      <td>1604102079270</td>\n",
       "      <td>None</td>\n",
       "      <td>https://earthquake.usgs.gov/earthquakes/eventp...</td>\n",
       "      <td>https://earthquake.usgs.gov/fdsnws/event/1/que...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>,ci39451231,</td>\n",
       "      <td>,ci,</td>\n",
       "      <td>,nearby-cities,origin,phase-data,scitech-link,</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.09717</td>\n",
       "      <td>0.19</td>\n",
       "      <td>98.0</td>\n",
       "      <td>ml</td>\n",
       "      <td>earthquake</td>\n",
       "      <td>M 0.6 - 13km NE of Indio, CA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    mag                               place           time        updated  \\\n",
       "0  1.00  50 km ENE of Susitna North, Alaska  1604102395919  1604103325550   \n",
       "1  0.64      15km NNE of Warner Springs, CA  1604102330860  1604114890185   \n",
       "2  1.09      15km NNE of Warner Springs, CA  1604102075440  1604102309453   \n",
       "3  0.34      15km NNE of Warner Springs, CA  1604101989680  1604102996061   \n",
       "4  0.59                13km NE of Indio, CA  1604101849950  1604102079270   \n",
       "\n",
       "     tz                                                url  \\\n",
       "0  None  https://earthquake.usgs.gov/earthquakes/eventp...   \n",
       "1  None  https://earthquake.usgs.gov/earthquakes/eventp...   \n",
       "2  None  https://earthquake.usgs.gov/earthquakes/eventp...   \n",
       "3  None  https://earthquake.usgs.gov/earthquakes/eventp...   \n",
       "4  None  https://earthquake.usgs.gov/earthquakes/eventp...   \n",
       "\n",
       "                                              detail  felt  cdi  mmi  ...  \\\n",
       "0  https://earthquake.usgs.gov/fdsnws/event/1/que...   NaN  NaN  NaN  ...   \n",
       "1  https://earthquake.usgs.gov/fdsnws/event/1/que...   NaN  NaN  NaN  ...   \n",
       "2  https://earthquake.usgs.gov/fdsnws/event/1/que...   NaN  NaN  NaN  ...   \n",
       "3  https://earthquake.usgs.gov/fdsnws/event/1/que...   NaN  NaN  NaN  ...   \n",
       "4  https://earthquake.usgs.gov/fdsnws/event/1/que...   NaN  NaN  NaN  ...   \n",
       "\n",
       "              ids sources                                           types  \\\n",
       "0  ,ak020dz5f85a,    ,ak,                             ,origin,phase-data,   \n",
       "1    ,ci39451271,    ,ci,  ,nearby-cities,origin,phase-data,scitech-link,   \n",
       "2    ,ci39451255,    ,ci,  ,nearby-cities,origin,phase-data,scitech-link,   \n",
       "3    ,ci39451263,    ,ci,  ,nearby-cities,origin,phase-data,scitech-link,   \n",
       "4    ,ci39451231,    ,ci,  ,nearby-cities,origin,phase-data,scitech-link,   \n",
       "\n",
       "    nst     dmin   rms    gap magType        type  \\\n",
       "0   NaN      NaN  1.36    NaN      ml  earthquake   \n",
       "1  23.0  0.06759  0.16  123.0      ml  earthquake   \n",
       "2  39.0  0.06308  0.22   91.0      ml  earthquake   \n",
       "3  15.0  0.06228  0.15  129.0      ml  earthquake   \n",
       "4  17.0  0.09717  0.19   98.0      ml  earthquake   \n",
       "\n",
       "                                        title  \n",
       "0  M 1.0 - 50 km ENE of Susitna North, Alaska  \n",
       "1      M 0.6 - 15km NNE of Warner Springs, CA  \n",
       "2      M 1.1 - 15km NNE of Warner Springs, CA  \n",
       "3      M 0.3 - 15km NNE of Warner Springs, CA  \n",
       "4                M 0.6 - 13km NE of Indio, CA  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "earthquake_properties_data = [\n",
    "    quake['properties'] for quake in earthquake_json['features']\n",
    "]\n",
    "df = pd.DataFrame(earthquake_properties_data)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (Optional) Write Data to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('earthquakes.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<div>\n",
    "    <a href=\"./2-creating_dataframes.ipynb\">\n",
    "        <button style=\"float: left;\">&#8592; Previous Notebook</button>\n",
    "    </a>\n",
    "    <a href=\"./4-inspecting_dataframes.ipynb\">\n",
    "        <button style=\"float: right;\">Next Notebook &#8594;</button>\n",
    "    </a>\n",
    "</div>\n",
    "<br>\n",
    "<hr>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}