ch_02/2-creating_dataframes.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating DataFrames\n",
    "We will create `DataFrame` objects from other data structures in Python, by reading in a CSV file, and by querying a database.\n",
    "\n",
    "## About the Data\n",
    "In this notebook, we will be working with earthquake data from September 18, 2018 - October 13, 2018 (obtained from the US Geological Survey (USGS) using the [USGS API](https://earthquake.usgs.gov/fdsnws/event/1/))\n",
    "\n",
    "## Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime as dt\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating a `Series` object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.548814\n",
       "1    0.715189\n",
       "2    0.602763\n",
       "3    0.544883\n",
       "4    0.423655\n",
       "Name: random, dtype: float64"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.random.seed(0) # set a seed for reproducibility\n",
    "pd.Series(np.random.rand(5), name='random')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating a `DataFrame` object from a `Series` object\n",
    "Use the `to_frame()` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      0\n",
       "0   0.0\n",
       "1   2.5\n",
       "2   5.0\n",
       "3   7.5\n",
       "4  10.0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.Series(np.linspace(0, 10, num=5)).to_frame()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating a `DataFrame` from Python Data Structures\n",
    "### From a dictionary of list-like structures\n",
    "The dictionary values can be lists, NumPy arrays, etc. as long as they have length (generators don't have length so we can't use them here):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>random</th>\n",
       "      <th>text</th>\n",
       "      <th>truth</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-04-17</th>\n",
       "      <td>0.548814</td>\n",
       "      <td>hot</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-04-18</th>\n",
       "      <td>0.715189</td>\n",
       "      <td>warm</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-04-19</th>\n",
       "      <td>0.602763</td>\n",
       "      <td>cool</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-04-20</th>\n",
       "      <td>0.544883</td>\n",
       "      <td>cold</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-04-21</th>\n",
       "      <td>0.423655</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              random  text  truth\n",
       "date                             \n",
       "2019-04-17  0.548814   hot  False\n",
       "2019-04-18  0.715189  warm   True\n",
       "2019-04-19  0.602763  cool   True\n",
       "2019-04-20  0.544883  cold  False\n",
       "2019-04-21  0.423655  None   True"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.random.seed(0) # set seed so result is reproducible\n",
    "pd.DataFrame(\n",
    "    {\n",
    "        'random': np.random.rand(5),\n",
    "        'text': ['hot', 'warm', 'cool', 'cold', None],\n",
    "        'truth': [np.random.choice([True, False]) for _ in range(5)]\n",
    "    }, \n",
    "    index=pd.date_range(\n",
    "        end=dt.date(2019, 4, 21),\n",
    "        freq='1D',\n",
    "        periods=5, \n",
    "        name='date'\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### From a list of dictionaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.2</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.2</td>\n",
       "      <td>Alaska</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.2</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mag       place\n",
       "0  5.2  California\n",
       "1  1.2      Alaska\n",
       "2  0.2  California"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame([\n",
    "    {'mag': 5.2, 'place': 'California'},\n",
    "    {'mag': 1.2, 'place': 'Alaska'},\n",
    "    {'mag': 0.2, 'place': 'California'},\n",
    "])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### From a list of tuples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(0, 0, 0), (1, 1, 1), (2, 4, 8), (3, 9, 27), (4, 16, 64)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list_of_tuples = [(n, n**2, n**3) for n in range(5)]\n",
    "list_of_tuples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>n</th>\n",
       "      <th>n_squared</th>\n",
       "      <th>n_cubed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   n  n_squared  n_cubed\n",
       "0  0          0        0\n",
       "1  1          1        1\n",
       "2  2          4        8\n",
       "3  3          9       27\n",
       "4  4         16       64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(\n",
    "    list_of_tuples, \n",
    "    columns=['n', 'n_squared', 'n_cubed']\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### From a NumPy array"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>n</th>\n",
       "      <th>n_squared</th>\n",
       "      <th>n_cubed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   n  n_squared  n_cubed\n",
       "0  0          0        0\n",
       "1  1          1        1\n",
       "2  2          4        8\n",
       "3  3          9       27\n",
       "4  4         16       64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(\n",
    "    np.array([\n",
    "        [0, 0, 0],\n",
    "        [1, 1, 1],\n",
    "        [2, 4, 8],\n",
    "        [3, 9, 27],\n",
    "        [4, 16, 64]\n",
    "    ]), columns=['n', 'n_squared', 'n_cubed']\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating a `DataFrame` object from the contents of a CSV File\n",
    "\n",
    "### Finding information on the file before reading it in\n",
    "Before attempting to read in a file, we can use the command line to see important information about the file that may determine how we read it in. We can run command line code from Jupyter Notebooks (thanks to IPython) by using `!` before the code.\n",
    "\n",
    "#### Number of lines (row count)\n",
    "For example, we can find out how many lines are in the file by using the `wc` utility (word count) and counting lines in the file (`-l`). The file has 9,333 lines:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9333 data/earthquakes.csv\n"
     ]
    }
   ],
   "source": [
    "!wc -l data/earthquakes.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Windows users**: if the above doesn't work for you (depends on your setup), then use this instead:\n",
    "\n",
    "```\n",
    "!find /c /v \"\" data\\earthquakes.csv\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "#### File size\n",
    "We can find the file size by using `ls` to list the files in the `data` directory, and passing in the flags `-lh` to include the file size in human readable format. Then we use `grep` to find the file in question. Note that `|` passes the result of `ls` to `grep`. The `grep` utility is used for finding items that match patterns.\n",
    "\n",
    "This tells us the file is 3.4 MB:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-rw-r--r-- 1 stefanie stefanie 3.4M Aug 24 17:39 earthquakes.csv\n"
     ]
    }
   ],
   "source": [
    "!ls -lh data | grep earthquakes.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Windows users**: if the above doesn't work for you (depends on your setup), then use this instead:\n",
    "\n",
    "```\n",
    "!dir data | findstr \"earthquakes.csv\"\n",
    "```\n",
    "\n",
    "We can even capture the result of a command and use it in our Python code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['-rw-r--r-- 1 stefanie stefanie 3.4M Aug 24 17:39 earthquakes.csv']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "files = !ls -lh data\n",
    "[file for file in files if 'earthquake' in file]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Windows users**: if the above doesn't work for you (depends on your setup), then use this instead:\n",
    "\n",
    "```\n",
    "files = !dir data\n",
    "[file for file in files if 'earthquake' in file]\n",
    "```\n",
    "\n",
    "\n",
    "#### Examining a few rows\n",
    "We can use `head` to look at the top `n` rows of the file. With the `-n` flag, we can specify how many. This shows use that the first row of the file contains headers and that it is comma-separated (just because the file extension is `.csv` doesn't it contains comma-separated values):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "alert,cdi,code,detail,dmin,felt,gap,ids,mag,magType,mmi,net,nst,place,rms,sig,sources,status,time,title,tsunami,type,types,tz,updated,url\n",
      ",,37389218,https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=ci37389218&format=geojson,0.008693,,85.0,\",ci37389218,\",1.35,ml,,ci,26.0,\"9km NE of Aguanga, CA\",0.19,28,\",ci,\",automatic,1539475168010,\"M 1.4 - 9km NE of Aguanga, CA\",0,earthquake,\",geoserve,nearby-cities,origin,phase-data,\",-480.0,1539475395144,https://earthquake.usgs.gov/earthquakes/eventpage/ci37389218\n"
     ]
    }
   ],
   "source": [
    "!head -n 2 data/earthquakes.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Windows users**: if the above doesn't work for you (depends on your setup), then use this instead:\n",
    "\n",
    "```\n",
    "n = 2\n",
    "with open('data/earthquakes.csv', 'r') as file:\n",
    "    for _ in range(n):\n",
    "        print(file.readline())\n",
    "```\n",
    "\n",
    "\n",
    "Just like `head` gives rows from the top, `tail` gives rows from the bottom. This can help us check that there is no extraneous data on the bottom of the field, like perhaps some metadata about the fields that actually isn't part of the dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ",,38063935,https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=ci38063935&format=geojson,0.01698,,39.0,\",ci38063935,\",0.66,ml,,ci,24.0,\"9km NE of Aguanga, CA\",0.1,7,\",ci,\",reviewed,1537228864470,\"M 0.7 - 9km NE of Aguanga, CA\",0,earthquake,\",focal-mechanism,geoserve,nearby-cities,origin,phase-data,scitech-link,\",-480.0,1537305830770,https://earthquake.usgs.gov/earthquakes/eventpage/ci38063935\n"
     ]
    }
   ],
   "source": [
    "!tail -n 1 data/earthquakes.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Column count\n",
    "We can use `awk` to find the column count. This is a utility for pattern scanning and processing. The `-F` flag allows us to specify the delimiter (comma, in this case). Then we specify what to do for each record in the file. We choose to print `NF` which is a predefined variable whose value is the number of fields in the current record. Here, we say `exit` so that we print the number of fields (columns, here) in the first row of the file, then we stop. \n",
    "\n",
    "This tells us we have 26 data columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "26\n"
     ]
    }
   ],
   "source": [
    "!awk -F',' '{print NF; exit}' data/earthquakes.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Windows users**: if the above or below don't work for you (depends on your setup), then use this instead:\n",
    "\n",
    "```\n",
    "import os\n",
    "\n",
    "with open('data/earthquakes.csv', 'rb') as file:\n",
    "    file.seek(-2, os.SEEK_END)\n",
    "    while file.read(1) != b'\\n':\n",
    "        file.seek(-2, os.SEEK_CUR)\n",
    "    print(file.readline().decode())\n",
    "```\n",
    "\n",
    "\n",
    "Since we know the 1st line of the file had headers, and the file is comma-separated, we can also count the columns by using `head` to get headers and parsing them in Python:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "26"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "headers = !head -n 1 data/earthquakes.csv\n",
    "len(headers[0].split(','))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading in the file\n",
    "Our file is small in size, has headers in the first row, and is comma-separated, so we don't need to provide any additional arguments to read in the file with `pd.read_csv()`, but be sure to check the [documentation](http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html) for possible arguments:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/earthquakes.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we can also pass in a URL. Let's read this same file from GitHub:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\n",
    "    'https://github.com/stefmolin/'\n",
    "    'Hands-On-Data-Analysis-with-Pandas-2nd-edition'\n",
    "    '/blob/master/ch_02/data/earthquakes.csv?raw=True'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas is usually very good at figuring out which options to use based on the input data, so we often won't need to add arguments to the call; however, there are many options available should we need them, some of which include the following:\n",
    "\n",
    "| Parameter | Purpose |\n",
    "| --- | --- |\n",
    "| `sep` | Specifies the delimiter |\n",
    "| `header` | Row number where the column names are located; the default option has `pandas` infer whether they are present |\n",
    "| `names` | List of column names to use as the header |\n",
    "| `index_col` | Column to use as the index |\n",
    "| `usecols` | Specifies which columns to read in |\n",
    "| `dtype` | Specifies data types for the columns | \n",
    "| `converters` | Specifies functions for converting data in certain columns |\n",
    "| `skiprows` | Rows to skip |\n",
    "| `nrows` | Number of rows to read at a time (combine with `skiprows` to read a file bit by bit) |\n",
    "| `parse_dates` | Automatically parse columns containing dates into datetime objects |\n",
    "| `chunksize` | For reading the file in chunks |\n",
    "| `compression` | For reading in compressed files without extracting beforehand |\n",
    "| `encoding` | Specifies the file encoding |\n",
    "\n",
    "## Writing a `DataFrame` Object to a CSV File\n",
    "Note that the index of `df` is just row numbers, so we don't want to keep it. Therefore, we pass `index=False` to the `to_csv()` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('output.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Writing a `DataFrame` Object to a Database\n",
    "Note the `if_exists` parameter. By default, it will give you an error if you try to write a table that already exists. Here, we don't care if it is overwritten. Lastly, if we are interested in appending new rows, we set that to `'append'`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "with sqlite3.connect('data/quakes.db') as connection:\n",
    "    pd.read_csv('data/tsunamis.csv').to_sql(\n",
    "        'tsunamis', connection, index=False, if_exists='replace'\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating a `DataFrame` Object by Querying a Database\n",
    "Using a SQLite database. Otherwise you need to install [SQLAlchemy](https://www.sqlalchemy.org/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>alert</th>\n",
       "      <th>type</th>\n",
       "      <th>title</th>\n",
       "      <th>place</th>\n",
       "      <th>magType</th>\n",
       "      <th>mag</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>earthquake</td>\n",
       "      <td>M 5.0 - 165km NNW of Flying Fish Cove, Christm...</td>\n",
       "      <td>165km NNW of Flying Fish Cove, Christmas Island</td>\n",
       "      <td>mww</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1539459504090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>green</td>\n",
       "      <td>earthquake</td>\n",
       "      <td>M 6.7 - 262km NW of Ozernovskiy, Russia</td>\n",
       "      <td>262km NW of Ozernovskiy, Russia</td>\n",
       "      <td>mww</td>\n",
       "      <td>6.7</td>\n",
       "      <td>1539429023560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>green</td>\n",
       "      <td>earthquake</td>\n",
       "      <td>M 5.6 - 128km SE of Kimbe, Papua New Guinea</td>\n",
       "      <td>128km SE of Kimbe, Papua New Guinea</td>\n",
       "      <td>mww</td>\n",
       "      <td>5.6</td>\n",
       "      <td>1539312723620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>green</td>\n",
       "      <td>earthquake</td>\n",
       "      <td>M 6.5 - 148km S of Severo-Kuril'sk, Russia</td>\n",
       "      <td>148km S of Severo-Kuril'sk, Russia</td>\n",
       "      <td>mww</td>\n",
       "      <td>6.5</td>\n",
       "      <td>1539213362130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>green</td>\n",
       "      <td>earthquake</td>\n",
       "      <td>M 6.2 - 94km SW of Kokopo, Papua New Guinea</td>\n",
       "      <td>94km SW of Kokopo, Papua New Guinea</td>\n",
       "      <td>mww</td>\n",
       "      <td>6.2</td>\n",
       "      <td>1539208835130</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   alert        type                                              title  \\\n",
       "0   None  earthquake  M 5.0 - 165km NNW of Flying Fish Cove, Christm...   \n",
       "1  green  earthquake            M 6.7 - 262km NW of Ozernovskiy, Russia   \n",
       "2  green  earthquake        M 5.6 - 128km SE of Kimbe, Papua New Guinea   \n",
       "3  green  earthquake         M 6.5 - 148km S of Severo-Kuril'sk, Russia   \n",
       "4  green  earthquake        M 6.2 - 94km SW of Kokopo, Papua New Guinea   \n",
       "\n",
       "                                             place magType  mag           time  \n",
       "0  165km NNW of Flying Fish Cove, Christmas Island     mww  5.0  1539459504090  \n",
       "1                  262km NW of Ozernovskiy, Russia     mww  6.7  1539429023560  \n",
       "2              128km SE of Kimbe, Papua New Guinea     mww  5.6  1539312723620  \n",
       "3               148km S of Severo-Kuril'sk, Russia     mww  6.5  1539213362130  \n",
       "4              94km SW of Kokopo, Papua New Guinea     mww  6.2  1539208835130  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "with sqlite3.connect('data/quakes.db') as connection:\n",
    "    tsunamis = pd.read_sql('SELECT * FROM tsunamis', connection)\n",
    "\n",
    "tsunamis.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<div>\n",
    "    <a href=\"./1-pandas_data_structures.ipynb\">\n",
    "        <button style=\"float: left;\">&#8592; Previous Notebook</button>\n",
    "    </a>\n",
    "    <a href=\"./3-making_dataframes_from_api_requests.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
}