27_Data Input and Output.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Input and Output\n",
    "\n",
    "This notebook is the reference code for getting input and output, pandas can read a variety of file types using its pd.read_ methods. Let's take a look at the most common data types:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CSV\n",
    "\n",
    "### CSV Input"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    a   b   c   d\n",
       "0   0   1   2   3\n",
       "1   4   5   6   7\n",
       "2   8   9  10  11\n",
       "3  12  13  14  15"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('example')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CSV Output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.to_csv('example',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Excel\n",
    "Pandas can read and write excel files, keep in mind, this only imports data. Not formulas or images, having images or macros may cause this read_excel method to crash. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Excel Input"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    a   b   c   d\n",
       "0   0   1   2   3\n",
       "1   4   5   6   7\n",
       "2   8   9  10  11\n",
       "3  12  13  14  15"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_excel('Excel_Sample.xlsx',sheetname='Sheet1')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Excel Output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel('Excel_Sample.xlsx',sheet_name='Sheet1')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## HTML\n",
    "\n",
    "You may need to install htmllib5,lxml, and BeautifulSoup4. In your terminal/command prompt run:\n",
    "\n",
    "    conda install lxml\n",
    "    conda install html5lib\n",
    "    conda install BeautifulSoup4\n",
    "\n",
    "Then restart Jupyter Notebook.\n",
    "(or use pip install if you aren't using the Anaconda Distribution)\n",
    "\n",
    "Pandas can read table tabs off of html. For example:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### HTML Input\n",
    "\n",
    "Pandas read_html function will read tables off of a webpage and return a list of DataFrame objects:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_html('http://www.fdic.gov/bank/individual/failed/banklist.html')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Bank Name</th>\n",
       "      <th>City</th>\n",
       "      <th>ST</th>\n",
       "      <th>CERT</th>\n",
       "      <th>Acquiring Institution</th>\n",
       "      <th>Closing Date</th>\n",
       "      <th>Updated Date</th>\n",
       "      <th>Loss Share Type</th>\n",
       "      <th>Agreement Terminated</th>\n",
       "      <th>Termination Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>First CornerStone Bank</td>\n",
       "      <td>King of Prussia</td>\n",
       "      <td>PA</td>\n",
       "      <td>35312</td>\n",
       "      <td>First-Citizens Bank &amp; Trust Company</td>\n",
       "      <td>May 6, 2016</td>\n",
       "      <td>July 12, 2016</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Trust Company Bank</td>\n",
       "      <td>Memphis</td>\n",
       "      <td>TN</td>\n",
       "      <td>9956</td>\n",
       "      <td>The Bank of Fayette County</td>\n",
       "      <td>April 29, 2016</td>\n",
       "      <td>August 4, 2016</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>North Milwaukee State Bank</td>\n",
       "      <td>Milwaukee</td>\n",
       "      <td>WI</td>\n",
       "      <td>20364</td>\n",
       "      <td>First-Citizens Bank &amp; Trust Company</td>\n",
       "      <td>March 11, 2016</td>\n",
       "      <td>June 16, 2016</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Hometown National Bank</td>\n",
       "      <td>Longview</td>\n",
       "      <td>WA</td>\n",
       "      <td>35156</td>\n",
       "      <td>Twin City Bank</td>\n",
       "      <td>October 2, 2015</td>\n",
       "      <td>April 13, 2016</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>The Bank of Georgia</td>\n",
       "      <td>Peachtree City</td>\n",
       "      <td>GA</td>\n",
       "      <td>35259</td>\n",
       "      <td>Fidelity Bank</td>\n",
       "      <td>October 2, 2015</td>\n",
       "      <td>April 13, 2016</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Premier Bank</td>\n",
       "      <td>Denver</td>\n",
       "      <td>CO</td>\n",
       "      <td>34112</td>\n",
       "      <td>United Fidelity Bank, fsb</td>\n",
       "      <td>July 10, 2015</td>\n",
       "      <td>July 12, 2016</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Edgebrook Bank</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>57772</td>\n",
       "      <td>Republic Bank of Chicago</td>\n",
       "      <td>May 8, 2015</td>\n",
       "      <td>July 12, 2016</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Doral BankEn Espanol</td>\n",
       "      <td>San Juan</td>\n",
       "      <td>PR</td>\n",
       "      <td>32102</td>\n",
       "      <td>Banco Popular de Puerto Rico</td>\n",
       "      <td>February 27, 2015</td>\n",
       "      <td>May 13, 2015</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Capitol City Bank &amp; Trust Company</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>GA</td>\n",
       "      <td>33938</td>\n",
       "      <td>First-Citizens Bank &amp; Trust Company</td>\n",
       "      <td>February 13, 2015</td>\n",
       "      <td>April 21, 2015</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Highland Community Bank</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>20290</td>\n",
       "      <td>United Fidelity Bank, fsb</td>\n",
       "      <td>January 23, 2015</td>\n",
       "      <td>April 21, 2015</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>First National Bank of Crestview</td>\n",
       "      <td>Crestview</td>\n",
       "      <td>FL</td>\n",
       "      <td>17557</td>\n",
       "      <td>First NBC Bank</td>\n",
       "      <td>January 16, 2015</td>\n",
       "      <td>January 15, 2016</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Northern Star Bank</td>\n",
       "      <td>Mankato</td>\n",
       "      <td>MN</td>\n",
       "      <td>34983</td>\n",
       "      <td>BankVista</td>\n",
       "      <td>December 19, 2014</td>\n",
       "      <td>January 6, 2016</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Frontier Bank, FSB D/B/A El Paseo Bank</td>\n",
       "      <td>Palm Desert</td>\n",
       "      <td>CA</td>\n",
       "      <td>34738</td>\n",
       "      <td>Bank of Southern California, N.A.</td>\n",
       "      <td>November 7, 2014</td>\n",
       "      <td>January 6, 2016</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>The National Republic Bank of Chicago</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>916</td>\n",
       "      <td>State Bank of Texas</td>\n",
       "      <td>October 24, 2014</td>\n",
       "      <td>January 6, 2016</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>NBRS Financial</td>\n",
       "      <td>Rising Sun</td>\n",
       "      <td>MD</td>\n",
       "      <td>4862</td>\n",
       "      <td>Howard Bank</td>\n",
       "      <td>October 17, 2014</td>\n",
       "      <td>March 26, 2015</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>GreenChoice Bank, fsb</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>28462</td>\n",
       "      <td>Providence Bank, LLC</td>\n",
       "      <td>July 25, 2014</td>\n",
       "      <td>July 28, 2015</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Eastside Commercial Bank</td>\n",
       "      <td>Conyers</td>\n",
       "      <td>GA</td>\n",
       "      <td>58125</td>\n",
       "      <td>Community &amp; Southern Bank</td>\n",
       "      <td>July 18, 2014</td>\n",
       "      <td>July 11, 2016</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>The Freedom State Bank</td>\n",
       "      <td>Freedom</td>\n",
       "      <td>OK</td>\n",
       "      <td>12483</td>\n",
       "      <td>Alva State Bank &amp; Trust Company</td>\n",
       "      <td>June 27, 2014</td>\n",
       "      <td>March 25, 2016</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Valley Bank</td>\n",
       "      <td>Fort Lauderdale</td>\n",
       "      <td>FL</td>\n",
       "      <td>21793</td>\n",
       "      <td>Landmark Bank, National Association</td>\n",
       "      <td>June 20, 2014</td>\n",
       "      <td>June 29, 2015</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Valley Bank</td>\n",
       "      <td>Moline</td>\n",
       "      <td>IL</td>\n",
       "      <td>10450</td>\n",
       "      <td>Great Southern Bank</td>\n",
       "      <td>June 20, 2014</td>\n",
       "      <td>June 26, 2015</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Slavie Federal Savings Bank</td>\n",
       "      <td>Bel Air</td>\n",
       "      <td>MD</td>\n",
       "      <td>32368</td>\n",
       "      <td>Bay Bank, FSB</td>\n",
       "      <td>May 30, 2014</td>\n",
       "      <td>June 15, 2015</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Columbia Savings Bank</td>\n",
       "      <td>Cincinnati</td>\n",
       "      <td>OH</td>\n",
       "      <td>32284</td>\n",
       "      <td>United Fidelity Bank, fsb</td>\n",
       "      <td>May 23, 2014</td>\n",
       "      <td>May 28, 2015</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>AztecAmerica Bank En Espanol</td>\n",
       "      <td>Berwyn</td>\n",
       "      <td>IL</td>\n",
       "      <td>57866</td>\n",
       "      <td>Republic Bank of Chicago</td>\n",
       "      <td>May 16, 2014</td>\n",
       "      <td>July 18, 2014</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Allendale County Bank</td>\n",
       "      <td>Fairfax</td>\n",
       "      <td>SC</td>\n",
       "      <td>15062</td>\n",
       "      <td>Palmetto State Bank</td>\n",
       "      <td>April 25, 2014</td>\n",
       "      <td>July 18, 2014</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Vantage Point Bank</td>\n",
       "      <td>Horsham</td>\n",
       "      <td>PA</td>\n",
       "      <td>58531</td>\n",
       "      <td>First Choice Bank</td>\n",
       "      <td>February 28, 2014</td>\n",
       "      <td>March 3, 2015</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Millennium Bank, National Association</td>\n",
       "      <td>Sterling</td>\n",
       "      <td>VA</td>\n",
       "      <td>35096</td>\n",
       "      <td>WashingtonFirst Bank</td>\n",
       "      <td>February 28, 2014</td>\n",
       "      <td>March 03, 2015</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Syringa Bank</td>\n",
       "      <td>Boise</td>\n",
       "      <td>ID</td>\n",
       "      <td>34296</td>\n",
       "      <td>Sunwest Bank</td>\n",
       "      <td>January 31, 2014</td>\n",
       "      <td>April 12, 2016</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>The Bank of Union</td>\n",
       "      <td>El Reno</td>\n",
       "      <td>OK</td>\n",
       "      <td>17967</td>\n",
       "      <td>BancFirst</td>\n",
       "      <td>January 24, 2014</td>\n",
       "      <td>March 25, 2016</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>DuPage National Bank</td>\n",
       "      <td>West Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>5732</td>\n",
       "      <td>Republic Bank of Chicago</td>\n",
       "      <td>January 17, 2014</td>\n",
       "      <td>February 19, 2015</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Texas Community Bank, National Association</td>\n",
       "      <td>The Woodlands</td>\n",
       "      <td>TX</td>\n",
       "      <td>57431</td>\n",
       "      <td>Spirit of Texas Bank, SSB</td>\n",
       "      <td>December 13, 2013</td>\n",
       "      <td>December 29, 2014</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>515</th>\n",
       "      <td>ANB Financial, NA</td>\n",
       "      <td>Bentonville</td>\n",
       "      <td>AR</td>\n",
       "      <td>33901</td>\n",
       "      <td>Pulaski Bank and Trust Company</td>\n",
       "      <td>May 9, 2008</td>\n",
       "      <td>August 28, 2012</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>516</th>\n",
       "      <td>Hume Bank</td>\n",
       "      <td>Hume</td>\n",
       "      <td>MO</td>\n",
       "      <td>1971</td>\n",
       "      <td>Security Bank</td>\n",
       "      <td>March 7, 2008</td>\n",
       "      <td>August 28, 2012</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>517</th>\n",
       "      <td>Douglass National Bank</td>\n",
       "      <td>Kansas City</td>\n",
       "      <td>MO</td>\n",
       "      <td>24660</td>\n",
       "      <td>Liberty Bank and Trust Company</td>\n",
       "      <td>January 25, 2008</td>\n",
       "      <td>October 26, 2012</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518</th>\n",
       "      <td>Miami Valley Bank</td>\n",
       "      <td>Lakeview</td>\n",
       "      <td>OH</td>\n",
       "      <td>16848</td>\n",
       "      <td>The Citizens Banking Company</td>\n",
       "      <td>October 4, 2007</td>\n",
       "      <td>August 28, 2012</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>519</th>\n",
       "      <td>NetBank</td>\n",
       "      <td>Alpharetta</td>\n",
       "      <td>GA</td>\n",
       "      <td>32575</td>\n",
       "      <td>ING DIRECT</td>\n",
       "      <td>September 28, 2007</td>\n",
       "      <td>August 28, 2012</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>520</th>\n",
       "      <td>Metropolitan Savings Bank</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>35353</td>\n",
       "      <td>Allegheny Valley Bank of Pittsburgh</td>\n",
       "      <td>February 2, 2007</td>\n",
       "      <td>October 27, 2010</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>521</th>\n",
       "      <td>Bank of Ephraim</td>\n",
       "      <td>Ephraim</td>\n",
       "      <td>UT</td>\n",
       "      <td>1249</td>\n",
       "      <td>Far West Bank</td>\n",
       "      <td>June 25, 2004</td>\n",
       "      <td>April 9, 2008</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>522</th>\n",
       "      <td>Reliance Bank</td>\n",
       "      <td>White Plains</td>\n",
       "      <td>NY</td>\n",
       "      <td>26778</td>\n",
       "      <td>Union State Bank</td>\n",
       "      <td>March 19, 2004</td>\n",
       "      <td>April 9, 2008</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523</th>\n",
       "      <td>Guaranty National Bank of Tallahassee</td>\n",
       "      <td>Tallahassee</td>\n",
       "      <td>FL</td>\n",
       "      <td>26838</td>\n",
       "      <td>Hancock Bank of Florida</td>\n",
       "      <td>March 12, 2004</td>\n",
       "      <td>June 5, 2012</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>524</th>\n",
       "      <td>Dollar Savings Bank</td>\n",
       "      <td>Newark</td>\n",
       "      <td>NJ</td>\n",
       "      <td>31330</td>\n",
       "      <td>No Acquirer</td>\n",
       "      <td>February 14, 2004</td>\n",
       "      <td>April 9, 2008</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>525</th>\n",
       "      <td>Pulaski Savings Bank</td>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>PA</td>\n",
       "      <td>27203</td>\n",
       "      <td>Earthstar Bank</td>\n",
       "      <td>November 14, 2003</td>\n",
       "      <td>July 22, 2005</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>526</th>\n",
       "      <td>First National Bank of Blanchardville</td>\n",
       "      <td>Blanchardville</td>\n",
       "      <td>WI</td>\n",
       "      <td>11639</td>\n",
       "      <td>The Park Bank</td>\n",
       "      <td>May 9, 2003</td>\n",
       "      <td>June 5, 2012</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>527</th>\n",
       "      <td>Southern Pacific Bank</td>\n",
       "      <td>Torrance</td>\n",
       "      <td>CA</td>\n",
       "      <td>27094</td>\n",
       "      <td>Beal Bank</td>\n",
       "      <td>February 7, 2003</td>\n",
       "      <td>October 20, 2008</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>528</th>\n",
       "      <td>Farmers Bank of Cheneyville</td>\n",
       "      <td>Cheneyville</td>\n",
       "      <td>LA</td>\n",
       "      <td>16445</td>\n",
       "      <td>Sabine State Bank &amp; Trust</td>\n",
       "      <td>December 17, 2002</td>\n",
       "      <td>October 20, 2004</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>529</th>\n",
       "      <td>Bank of Alamo</td>\n",
       "      <td>Alamo</td>\n",
       "      <td>TN</td>\n",
       "      <td>9961</td>\n",
       "      <td>No Acquirer</td>\n",
       "      <td>November 8, 2002</td>\n",
       "      <td>March 18, 2005</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>530</th>\n",
       "      <td>AmTrade International BankEn Espanol</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>GA</td>\n",
       "      <td>33784</td>\n",
       "      <td>No Acquirer</td>\n",
       "      <td>September 30, 2002</td>\n",
       "      <td>September 11, 2006</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>531</th>\n",
       "      <td>Universal Federal Savings Bank</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>29355</td>\n",
       "      <td>Chicago Community Bank</td>\n",
       "      <td>June 27, 2002</td>\n",
       "      <td>April 9, 2008</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>532</th>\n",
       "      <td>Connecticut Bank of Commerce</td>\n",
       "      <td>Stamford</td>\n",
       "      <td>CT</td>\n",
       "      <td>19183</td>\n",
       "      <td>Hudson United Bank</td>\n",
       "      <td>June 26, 2002</td>\n",
       "      <td>February 14, 2012</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>533</th>\n",
       "      <td>New Century Bank</td>\n",
       "      <td>Shelby Township</td>\n",
       "      <td>MI</td>\n",
       "      <td>34979</td>\n",
       "      <td>No Acquirer</td>\n",
       "      <td>March 28, 2002</td>\n",
       "      <td>March 18, 2005</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>534</th>\n",
       "      <td>Net 1st National Bank</td>\n",
       "      <td>Boca Raton</td>\n",
       "      <td>FL</td>\n",
       "      <td>26652</td>\n",
       "      <td>Bank Leumi USA</td>\n",
       "      <td>March 1, 2002</td>\n",
       "      <td>April 9, 2008</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>535</th>\n",
       "      <td>NextBank, NA</td>\n",
       "      <td>Phoenix</td>\n",
       "      <td>AZ</td>\n",
       "      <td>22314</td>\n",
       "      <td>No Acquirer</td>\n",
       "      <td>February 7, 2002</td>\n",
       "      <td>February 5, 2015</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536</th>\n",
       "      <td>Oakwood Deposit Bank Co.</td>\n",
       "      <td>Oakwood</td>\n",
       "      <td>OH</td>\n",
       "      <td>8966</td>\n",
       "      <td>The State Bank &amp; Trust Company</td>\n",
       "      <td>February 1, 2002</td>\n",
       "      <td>October 25, 2012</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>537</th>\n",
       "      <td>Bank of Sierra Blanca</td>\n",
       "      <td>Sierra Blanca</td>\n",
       "      <td>TX</td>\n",
       "      <td>22002</td>\n",
       "      <td>The Security State Bank of Pecos</td>\n",
       "      <td>January 18, 2002</td>\n",
       "      <td>November 6, 2003</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>538</th>\n",
       "      <td>Hamilton Bank, NAEn Espanol</td>\n",
       "      <td>Miami</td>\n",
       "      <td>FL</td>\n",
       "      <td>24382</td>\n",
       "      <td>Israel Discount Bank of New York</td>\n",
       "      <td>January 11, 2002</td>\n",
       "      <td>September 21, 2015</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>539</th>\n",
       "      <td>Sinclair National Bank</td>\n",
       "      <td>Gravette</td>\n",
       "      <td>AR</td>\n",
       "      <td>34248</td>\n",
       "      <td>Delta Trust &amp; Bank</td>\n",
       "      <td>September 7, 2001</td>\n",
       "      <td>February 10, 2004</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>540</th>\n",
       "      <td>Superior Bank, FSB</td>\n",
       "      <td>Hinsdale</td>\n",
       "      <td>IL</td>\n",
       "      <td>32646</td>\n",
       "      <td>Superior Federal, FSB</td>\n",
       "      <td>July 27, 2001</td>\n",
       "      <td>August 19, 2014</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>541</th>\n",
       "      <td>Malta National Bank</td>\n",
       "      <td>Malta</td>\n",
       "      <td>OH</td>\n",
       "      <td>6629</td>\n",
       "      <td>North Valley Bank</td>\n",
       "      <td>May 3, 2001</td>\n",
       "      <td>November 18, 2002</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>542</th>\n",
       "      <td>First Alliance Bank &amp; Trust Co.</td>\n",
       "      <td>Manchester</td>\n",
       "      <td>NH</td>\n",
       "      <td>34264</td>\n",
       "      <td>Southern New Hampshire Bank &amp; Trust</td>\n",
       "      <td>February 2, 2001</td>\n",
       "      <td>February 18, 2003</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>543</th>\n",
       "      <td>National State Bank of Metropolis</td>\n",
       "      <td>Metropolis</td>\n",
       "      <td>IL</td>\n",
       "      <td>3815</td>\n",
       "      <td>Banterra Bank of Marion</td>\n",
       "      <td>December 14, 2000</td>\n",
       "      <td>March 17, 2005</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>544</th>\n",
       "      <td>Bank of Honolulu</td>\n",
       "      <td>Honolulu</td>\n",
       "      <td>HI</td>\n",
       "      <td>21029</td>\n",
       "      <td>Bank of the Orient</td>\n",
       "      <td>October 13, 2000</td>\n",
       "      <td>March 17, 2005</td>\n",
       "      <td>none</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>545 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      Bank Name             City  ST   CERT  \\\n",
       "0                        First CornerStone Bank  King of Prussia  PA  35312   \n",
       "1                            Trust Company Bank          Memphis  TN   9956   \n",
       "2                    North Milwaukee State Bank        Milwaukee  WI  20364   \n",
       "3                        Hometown National Bank         Longview  WA  35156   \n",
       "4                           The Bank of Georgia   Peachtree City  GA  35259   \n",
       "5                                  Premier Bank           Denver  CO  34112   \n",
       "6                                Edgebrook Bank          Chicago  IL  57772   \n",
       "7                          Doral BankEn Espanol         San Juan  PR  32102   \n",
       "8             Capitol City Bank & Trust Company          Atlanta  GA  33938   \n",
       "9                       Highland Community Bank          Chicago  IL  20290   \n",
       "10             First National Bank of Crestview        Crestview  FL  17557   \n",
       "11                           Northern Star Bank          Mankato  MN  34983   \n",
       "12       Frontier Bank, FSB D/B/A El Paseo Bank      Palm Desert  CA  34738   \n",
       "13        The National Republic Bank of Chicago          Chicago  IL    916   \n",
       "14                               NBRS Financial       Rising Sun  MD   4862   \n",
       "15                        GreenChoice Bank, fsb          Chicago  IL  28462   \n",
       "16                     Eastside Commercial Bank          Conyers  GA  58125   \n",
       "17                       The Freedom State Bank          Freedom  OK  12483   \n",
       "18                                  Valley Bank  Fort Lauderdale  FL  21793   \n",
       "19                                  Valley Bank           Moline  IL  10450   \n",
       "20                  Slavie Federal Savings Bank          Bel Air  MD  32368   \n",
       "21                        Columbia Savings Bank       Cincinnati  OH  32284   \n",
       "22                 AztecAmerica Bank En Espanol           Berwyn  IL  57866   \n",
       "23                        Allendale County Bank          Fairfax  SC  15062   \n",
       "24                           Vantage Point Bank          Horsham  PA  58531   \n",
       "25        Millennium Bank, National Association         Sterling  VA  35096   \n",
       "26                                 Syringa Bank            Boise  ID  34296   \n",
       "27                            The Bank of Union          El Reno  OK  17967   \n",
       "28                         DuPage National Bank     West Chicago  IL   5732   \n",
       "29   Texas Community Bank, National Association    The Woodlands  TX  57431   \n",
       "..                                          ...              ...  ..    ...   \n",
       "515                           ANB Financial, NA      Bentonville  AR  33901   \n",
       "516                                   Hume Bank             Hume  MO   1971   \n",
       "517                      Douglass National Bank      Kansas City  MO  24660   \n",
       "518                           Miami Valley Bank         Lakeview  OH  16848   \n",
       "519                                     NetBank       Alpharetta  GA  32575   \n",
       "520                   Metropolitan Savings Bank       Pittsburgh  PA  35353   \n",
       "521                             Bank of Ephraim          Ephraim  UT   1249   \n",
       "522                               Reliance Bank     White Plains  NY  26778   \n",
       "523       Guaranty National Bank of Tallahassee      Tallahassee  FL  26838   \n",
       "524                         Dollar Savings Bank           Newark  NJ  31330   \n",
       "525                        Pulaski Savings Bank     Philadelphia  PA  27203   \n",
       "526       First National Bank of Blanchardville   Blanchardville  WI  11639   \n",
       "527                       Southern Pacific Bank         Torrance  CA  27094   \n",
       "528                 Farmers Bank of Cheneyville      Cheneyville  LA  16445   \n",
       "529                               Bank of Alamo            Alamo  TN   9961   \n",
       "530        AmTrade International BankEn Espanol          Atlanta  GA  33784   \n",
       "531              Universal Federal Savings Bank          Chicago  IL  29355   \n",
       "532                Connecticut Bank of Commerce         Stamford  CT  19183   \n",
       "533                            New Century Bank  Shelby Township  MI  34979   \n",
       "534                       Net 1st National Bank       Boca Raton  FL  26652   \n",
       "535                                NextBank, NA          Phoenix  AZ  22314   \n",
       "536                    Oakwood Deposit Bank Co.          Oakwood  OH   8966   \n",
       "537                       Bank of Sierra Blanca    Sierra Blanca  TX  22002   \n",
       "538                 Hamilton Bank, NAEn Espanol            Miami  FL  24382   \n",
       "539                      Sinclair National Bank         Gravette  AR  34248   \n",
       "540                          Superior Bank, FSB         Hinsdale  IL  32646   \n",
       "541                         Malta National Bank            Malta  OH   6629   \n",
       "542             First Alliance Bank & Trust Co.       Manchester  NH  34264   \n",
       "543           National State Bank of Metropolis       Metropolis  IL   3815   \n",
       "544                            Bank of Honolulu         Honolulu  HI  21029   \n",
       "\n",
       "                   Acquiring Institution        Closing Date  \\\n",
       "0    First-Citizens Bank & Trust Company         May 6, 2016   \n",
       "1             The Bank of Fayette County      April 29, 2016   \n",
       "2    First-Citizens Bank & Trust Company      March 11, 2016   \n",
       "3                         Twin City Bank     October 2, 2015   \n",
       "4                          Fidelity Bank     October 2, 2015   \n",
       "5              United Fidelity Bank, fsb       July 10, 2015   \n",
       "6               Republic Bank of Chicago         May 8, 2015   \n",
       "7           Banco Popular de Puerto Rico   February 27, 2015   \n",
       "8    First-Citizens Bank & Trust Company   February 13, 2015   \n",
       "9              United Fidelity Bank, fsb    January 23, 2015   \n",
       "10                        First NBC Bank    January 16, 2015   \n",
       "11                             BankVista   December 19, 2014   \n",
       "12     Bank of Southern California, N.A.    November 7, 2014   \n",
       "13                   State Bank of Texas    October 24, 2014   \n",
       "14                           Howard Bank    October 17, 2014   \n",
       "15                  Providence Bank, LLC       July 25, 2014   \n",
       "16             Community & Southern Bank       July 18, 2014   \n",
       "17       Alva State Bank & Trust Company       June 27, 2014   \n",
       "18   Landmark Bank, National Association       June 20, 2014   \n",
       "19                   Great Southern Bank       June 20, 2014   \n",
       "20                         Bay Bank, FSB        May 30, 2014   \n",
       "21             United Fidelity Bank, fsb        May 23, 2014   \n",
       "22              Republic Bank of Chicago        May 16, 2014   \n",
       "23                   Palmetto State Bank      April 25, 2014   \n",
       "24                     First Choice Bank   February 28, 2014   \n",
       "25                  WashingtonFirst Bank   February 28, 2014   \n",
       "26                          Sunwest Bank    January 31, 2014   \n",
       "27                             BancFirst    January 24, 2014   \n",
       "28              Republic Bank of Chicago    January 17, 2014   \n",
       "29             Spirit of Texas Bank, SSB   December 13, 2013   \n",
       "..                                   ...                 ...   \n",
       "515       Pulaski Bank and Trust Company         May 9, 2008   \n",
       "516                        Security Bank       March 7, 2008   \n",
       "517       Liberty Bank and Trust Company    January 25, 2008   \n",
       "518         The Citizens Banking Company     October 4, 2007   \n",
       "519                           ING DIRECT  September 28, 2007   \n",
       "520  Allegheny Valley Bank of Pittsburgh    February 2, 2007   \n",
       "521                        Far West Bank       June 25, 2004   \n",
       "522                     Union State Bank      March 19, 2004   \n",
       "523              Hancock Bank of Florida      March 12, 2004   \n",
       "524                          No Acquirer   February 14, 2004   \n",
       "525                       Earthstar Bank   November 14, 2003   \n",
       "526                        The Park Bank         May 9, 2003   \n",
       "527                            Beal Bank    February 7, 2003   \n",
       "528            Sabine State Bank & Trust   December 17, 2002   \n",
       "529                          No Acquirer    November 8, 2002   \n",
       "530                          No Acquirer  September 30, 2002   \n",
       "531               Chicago Community Bank       June 27, 2002   \n",
       "532                   Hudson United Bank       June 26, 2002   \n",
       "533                          No Acquirer      March 28, 2002   \n",
       "534                       Bank Leumi USA       March 1, 2002   \n",
       "535                          No Acquirer    February 7, 2002   \n",
       "536       The State Bank & Trust Company    February 1, 2002   \n",
       "537     The Security State Bank of Pecos    January 18, 2002   \n",
       "538     Israel Discount Bank of New York    January 11, 2002   \n",
       "539                   Delta Trust & Bank   September 7, 2001   \n",
       "540                Superior Federal, FSB       July 27, 2001   \n",
       "541                    North Valley Bank         May 3, 2001   \n",
       "542  Southern New Hampshire Bank & Trust    February 2, 2001   \n",
       "543              Banterra Bank of Marion   December 14, 2000   \n",
       "544                   Bank of the Orient    October 13, 2000   \n",
       "\n",
       "           Updated Date Loss Share Type Agreement Terminated Termination Date  \n",
       "0         July 12, 2016            none                  NaN              NaN  \n",
       "1        August 4, 2016            none                  NaN              NaN  \n",
       "2         June 16, 2016            none                  NaN              NaN  \n",
       "3        April 13, 2016            none                  NaN              NaN  \n",
       "4        April 13, 2016            none                  NaN              NaN  \n",
       "5         July 12, 2016            none                  NaN              NaN  \n",
       "6         July 12, 2016            none                  NaN              NaN  \n",
       "7          May 13, 2015            none                  NaN              NaN  \n",
       "8        April 21, 2015            none                  NaN              NaN  \n",
       "9        April 21, 2015            none                  NaN              NaN  \n",
       "10     January 15, 2016            none                  NaN              NaN  \n",
       "11      January 6, 2016            none                  NaN              NaN  \n",
       "12      January 6, 2016            none                  NaN              NaN  \n",
       "13      January 6, 2016            none                  NaN              NaN  \n",
       "14       March 26, 2015            none                  NaN              NaN  \n",
       "15        July 28, 2015            none                  NaN              NaN  \n",
       "16        July 11, 2016            none                  NaN              NaN  \n",
       "17       March 25, 2016            none                  NaN              NaN  \n",
       "18        June 29, 2015            none                  NaN              NaN  \n",
       "19        June 26, 2015            none                  NaN              NaN  \n",
       "20        June 15, 2015            none                  NaN              NaN  \n",
       "21         May 28, 2015            none                  NaN              NaN  \n",
       "22        July 18, 2014            none                  NaN              NaN  \n",
       "23        July 18, 2014            none                  NaN              NaN  \n",
       "24        March 3, 2015            none                  NaN              NaN  \n",
       "25       March 03, 2015            none                  NaN              NaN  \n",
       "26       April 12, 2016            none                  NaN              NaN  \n",
       "27       March 25, 2016            none                  NaN              NaN  \n",
       "28    February 19, 2015            none                  NaN              NaN  \n",
       "29    December 29, 2014            none                  NaN              NaN  \n",
       "..                  ...             ...                  ...              ...  \n",
       "515     August 28, 2012            none                  NaN              NaN  \n",
       "516     August 28, 2012            none                  NaN              NaN  \n",
       "517    October 26, 2012            none                  NaN              NaN  \n",
       "518     August 28, 2012            none                  NaN              NaN  \n",
       "519     August 28, 2012            none                  NaN              NaN  \n",
       "520    October 27, 2010            none                  NaN              NaN  \n",
       "521       April 9, 2008            none                  NaN              NaN  \n",
       "522       April 9, 2008            none                  NaN              NaN  \n",
       "523        June 5, 2012            none                  NaN              NaN  \n",
       "524       April 9, 2008            none                  NaN              NaN  \n",
       "525       July 22, 2005            none                  NaN              NaN  \n",
       "526        June 5, 2012            none                  NaN              NaN  \n",
       "527    October 20, 2008            none                  NaN              NaN  \n",
       "528    October 20, 2004            none                  NaN              NaN  \n",
       "529      March 18, 2005            none                  NaN              NaN  \n",
       "530  September 11, 2006            none                  NaN              NaN  \n",
       "531       April 9, 2008            none                  NaN              NaN  \n",
       "532   February 14, 2012            none                  NaN              NaN  \n",
       "533      March 18, 2005            none                  NaN              NaN  \n",
       "534       April 9, 2008            none                  NaN              NaN  \n",
       "535    February 5, 2015            none                  NaN              NaN  \n",
       "536    October 25, 2012            none                  NaN              NaN  \n",
       "537    November 6, 2003            none                  NaN              NaN  \n",
       "538  September 21, 2015            none                  NaN              NaN  \n",
       "539   February 10, 2004            none                  NaN              NaN  \n",
       "540     August 19, 2014            none                  NaN              NaN  \n",
       "541   November 18, 2002            none                  NaN              NaN  \n",
       "542   February 18, 2003            none                  NaN              NaN  \n",
       "543      March 17, 2005            none                  NaN              NaN  \n",
       "544      March 17, 2005            none                  NaN              NaN  \n",
       "\n",
       "[545 rows x 10 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "____"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_____\n",
    "_____\n",
    "# SQL (Optional)\n",
    "\n",
    "* Note: If you are completely unfamiliar with SQL you can check out my other course: \"Complete SQL Bootcamp\" to learn SQL."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The pandas.io.sql module provides a collection of query wrappers to both facilitate data retrieval and to reduce dependency on DB-specific API. Database abstraction is provided by SQLAlchemy if installed. In addition you will need a driver library for your database. Examples of such drivers are psycopg2 for PostgreSQL or pymysql for MySQL. For SQLite this is included in Python’s standard library by default. You can find an overview of supported drivers for each SQL dialect in the SQLAlchemy docs.\n",
    "\n",
    "\n",
    "If SQLAlchemy is not installed, a fallback is only provided for sqlite (and for mysql for backwards compatibility, but this is deprecated and will be removed in a future version). This mode requires a Python database adapter which respect the Python DB-API.\n",
    "\n",
    "See also some cookbook examples for some advanced strategies.\n",
    "\n",
    "The key functions are:\n",
    "\n",
    "* read_sql_table(table_name, con[, schema, ...])\t\n",
    "    * Read SQL database table into a DataFrame.\n",
    "* read_sql_query(sql, con[, index_col, ...])\t\n",
    "    * Read SQL query into a DataFrame.\n",
    "* read_sql(sql, con[, index_col, ...])\t\n",
    "    * Read SQL query or database table into a DataFrame.\n",
    "* DataFrame.to_sql(name, con[, flavor, ...])\t\n",
    "    * Write records stored in a DataFrame to a SQL database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "engine = create_engine('sqlite:///:memory:')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_sql('data', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_df = pd.read_sql('data',con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index   a   b   c   d\n",
       "0      0   0   1   2   3\n",
       "1      1   4   5   6   7\n",
       "2      2   8   9  10  11\n",
       "3      3  12  13  14  15"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Great Job!"
   ]
  }
 ],
 "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}