1_production_databases/06_proj_storm.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Postgres for Data Engineers Project: Storing Tropical Storm Data\n",
    "#### These are exercises done as part of <a href = \"www.dataquest.io\"> DataQuest</a>'s Data Engineer Path\n",
    "This is not replicated for commercial use; strictly personal development.<br>\n",
    "All exercises are (c) DataQuest, with slight modifications so they use my PostGres server on my localhost<br><br>\n",
    "Download Tropical Storm data from the <a href = \"https://data.world/dhs/historical-tropical-storm\">data.world</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color = 'blue'>Ok before we create the table `stormdata` in our database `dq_exercises`. I want to examine our data so we can use the correct datatypes.</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data = pd.read_csv('https://dq-content.s3.amazonaws.com/251/storm_data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FID</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>MONTH</th>\n",
       "      <th>DAY</th>\n",
       "      <th>AD_TIME</th>\n",
       "      <th>BTID</th>\n",
       "      <th>NAME</th>\n",
       "      <th>LAT</th>\n",
       "      <th>LONG</th>\n",
       "      <th>WIND_KTS</th>\n",
       "      <th>PRESSURE</th>\n",
       "      <th>CAT</th>\n",
       "      <th>BASIN</th>\n",
       "      <th>Shape_Leng</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001</td>\n",
       "      <td>1957</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>1800Z</td>\n",
       "      <td>63</td>\n",
       "      <td>NOTNAMED</td>\n",
       "      <td>22.5</td>\n",
       "      <td>-140.0</td>\n",
       "      <td>50</td>\n",
       "      <td>0</td>\n",
       "      <td>TS</td>\n",
       "      <td>Eastern Pacific</td>\n",
       "      <td>1.140175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2002</td>\n",
       "      <td>1961</td>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>1200Z</td>\n",
       "      <td>116</td>\n",
       "      <td>PAULINE</td>\n",
       "      <td>22.1</td>\n",
       "      <td>-140.2</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>TS</td>\n",
       "      <td>Eastern Pacific</td>\n",
       "      <td>1.166190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2003</td>\n",
       "      <td>1962</td>\n",
       "      <td>8</td>\n",
       "      <td>29</td>\n",
       "      <td>0600Z</td>\n",
       "      <td>124</td>\n",
       "      <td>C</td>\n",
       "      <td>18.0</td>\n",
       "      <td>-140.0</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>TS</td>\n",
       "      <td>Eastern Pacific</td>\n",
       "      <td>2.102380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004</td>\n",
       "      <td>1967</td>\n",
       "      <td>7</td>\n",
       "      <td>14</td>\n",
       "      <td>0600Z</td>\n",
       "      <td>168</td>\n",
       "      <td>DENISE</td>\n",
       "      <td>16.6</td>\n",
       "      <td>-139.5</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>TS</td>\n",
       "      <td>Eastern Pacific</td>\n",
       "      <td>2.121320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2005</td>\n",
       "      <td>1972</td>\n",
       "      <td>8</td>\n",
       "      <td>16</td>\n",
       "      <td>1200Z</td>\n",
       "      <td>251</td>\n",
       "      <td>DIANA</td>\n",
       "      <td>18.5</td>\n",
       "      <td>-139.8</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>H1</td>\n",
       "      <td>Eastern Pacific</td>\n",
       "      <td>1.702939</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FID  YEAR  MONTH  DAY AD_TIME  BTID      NAME   LAT   LONG  WIND_KTS  \\\n",
       "0  2001  1957      8    8   1800Z    63  NOTNAMED  22.5 -140.0        50   \n",
       "1  2002  1961     10    3   1200Z   116   PAULINE  22.1 -140.2        45   \n",
       "2  2003  1962      8   29   0600Z   124         C  18.0 -140.0        45   \n",
       "3  2004  1967      7   14   0600Z   168    DENISE  16.6 -139.5        45   \n",
       "4  2005  1972      8   16   1200Z   251     DIANA  18.5 -139.8        70   \n",
       "\n",
       "   PRESSURE CAT            BASIN  Shape_Leng  \n",
       "0         0  TS  Eastern Pacific    1.140175  \n",
       "1         0  TS  Eastern Pacific    1.166190  \n",
       "2         0  TS  Eastern Pacific    2.102380  \n",
       "3         0  TS  Eastern Pacific    2.121320  \n",
       "4         0  H1  Eastern Pacific    1.702939  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['FID',\n",
       " 'YEAR',\n",
       " 'MONTH',\n",
       " 'DAY',\n",
       " 'AD_TIME',\n",
       " 'BTID',\n",
       " 'NAME',\n",
       " 'LAT',\n",
       " 'LONG',\n",
       " 'WIND_KTS',\n",
       " 'PRESSURE',\n",
       " 'CAT',\n",
       " 'BASIN',\n",
       " 'Shape_Leng']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(data.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "59228"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FID</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>MONTH</th>\n",
       "      <th>DAY</th>\n",
       "      <th>AD_TIME</th>\n",
       "      <th>BTID</th>\n",
       "      <th>NAME</th>\n",
       "      <th>LAT</th>\n",
       "      <th>LONG</th>\n",
       "      <th>WIND_KTS</th>\n",
       "      <th>PRESSURE</th>\n",
       "      <th>CAT</th>\n",
       "      <th>BASIN</th>\n",
       "      <th>Shape_Leng</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001</td>\n",
       "      <td>1957</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>1800Z</td>\n",
       "      <td>63</td>\n",
       "      <td>NOTNAMED</td>\n",
       "      <td>22.5</td>\n",
       "      <td>-140.0</td>\n",
       "      <td>50</td>\n",
       "      <td>0</td>\n",
       "      <td>TS</td>\n",
       "      <td>Eastern Pacific</td>\n",
       "      <td>1.140175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2002</td>\n",
       "      <td>1961</td>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>1200Z</td>\n",
       "      <td>116</td>\n",
       "      <td>PAULINE</td>\n",
       "      <td>22.1</td>\n",
       "      <td>-140.2</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>TS</td>\n",
       "      <td>Eastern Pacific</td>\n",
       "      <td>1.166190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2003</td>\n",
       "      <td>1962</td>\n",
       "      <td>8</td>\n",
       "      <td>29</td>\n",
       "      <td>0600Z</td>\n",
       "      <td>124</td>\n",
       "      <td>C</td>\n",
       "      <td>18.0</td>\n",
       "      <td>-140.0</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>TS</td>\n",
       "      <td>Eastern Pacific</td>\n",
       "      <td>2.102380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004</td>\n",
       "      <td>1967</td>\n",
       "      <td>7</td>\n",
       "      <td>14</td>\n",
       "      <td>0600Z</td>\n",
       "      <td>168</td>\n",
       "      <td>DENISE</td>\n",
       "      <td>16.6</td>\n",
       "      <td>-139.5</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>TS</td>\n",
       "      <td>Eastern Pacific</td>\n",
       "      <td>2.121320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2005</td>\n",
       "      <td>1972</td>\n",
       "      <td>8</td>\n",
       "      <td>16</td>\n",
       "      <td>1200Z</td>\n",
       "      <td>251</td>\n",
       "      <td>DIANA</td>\n",
       "      <td>18.5</td>\n",
       "      <td>-139.8</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>H1</td>\n",
       "      <td>Eastern Pacific</td>\n",
       "      <td>1.702939</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FID  YEAR  MONTH  DAY AD_TIME  BTID      NAME   LAT   LONG  WIND_KTS  \\\n",
       "0  2001  1957      8    8   1800Z    63  NOTNAMED  22.5 -140.0        50   \n",
       "1  2002  1961     10    3   1200Z   116   PAULINE  22.1 -140.2        45   \n",
       "2  2003  1962      8   29   0600Z   124         C  18.0 -140.0        45   \n",
       "3  2004  1967      7   14   0600Z   168    DENISE  16.6 -139.5        45   \n",
       "4  2005  1972      8   16   1200Z   251     DIANA  18.5 -139.8        70   \n",
       "\n",
       "   PRESSURE CAT            BASIN  Shape_Leng  \n",
       "0         0  TS  Eastern Pacific    1.140175  \n",
       "1         0  TS  Eastern Pacific    1.166190  \n",
       "2         0  TS  Eastern Pacific    2.102380  \n",
       "3         0  TS  Eastern Pacific    2.121320  \n",
       "4         0  H1  Eastern Pacific    1.702939  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data.columns = [['fid', 'year', 'month', 'day', 'ad_time']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color = 'blue'>The pandas.DataFrame function <a href = \"https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.info.html\">`info()`</a> will tell us what datatype each column is. Note that it refers to strings as objects.</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 59228 entries, 0 to 59227\n",
      "Data columns (total 14 columns):\n",
      "FID           59228 non-null int64\n",
      "YEAR          59228 non-null int64\n",
      "MONTH         59228 non-null int64\n",
      "DAY           59228 non-null int64\n",
      "AD_TIME       59228 non-null object\n",
      "BTID          59228 non-null int64\n",
      "NAME          59228 non-null object\n",
      "LAT           59228 non-null float64\n",
      "LONG          59228 non-null float64\n",
      "WIND_KTS      59228 non-null int64\n",
      "PRESSURE      59228 non-null int64\n",
      "CAT           59228 non-null object\n",
      "BASIN         59228 non-null object\n",
      "Shape_Leng    59228 non-null float64\n",
      "dtypes: float64(3), int64(7), object(4)\n",
      "memory usage: 6.3+ MB\n"
     ]
    }
   ],
   "source": [
    "data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color = 'blue'>When determining which datatype to make our data, it can be helpful to see the `max_len` of each data column, so I write a for-loop to both see `max_len` and store it in a dictionary, if we need it later for some reason:</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "FID 5\n",
      "YEAR 4\n",
      "MONTH 2\n",
      "DAY 2\n",
      "AD_TIME 5\n",
      "BTID 4\n",
      "NAME 9\n",
      "LAT 4\n",
      "LONG 7\n",
      "WIND_KTS 3\n",
      "PRESSURE 4\n",
      "CAT 2\n",
      "BASIN 15\n",
      "Shape_Leng 9\n"
     ]
    }
   ],
   "source": [
    "col_len_dict = {}\n",
    "for col in list(data.columns):\n",
    "    max_len = 0\n",
    "    for i in range(1,len(data)):\n",
    "        if max_len < len(str(data[col][i])):\n",
    "            max_len = len(str(data[col][i]))\n",
    "    print(col + \" \" + str(max_len))\n",
    "    col_len_dict[col] = max_len"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color = 'blue'>Upon first glance, we'll want to find out the precision and scope of decimals and length of strings required to store this data. So we'll do the following steps:<br>\n",
    "- Remove the 'Z' character from `AD_TIME`, convert to timestamp <br>\n",
    "- See the max and min numbers for <br>\n",
    "&nbsp;&nbsp;&nbsp;&nbsp;- `LONG`<br>\n",
    "&nbsp;&nbsp;&nbsp;&nbsp;- `LAT`<Br>\n",
    "&nbsp;&nbsp;&nbsp;&nbsp;- `Shape_Leng`</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data['AD_TIME'] = [datetime.strptime(a[0:2]+\":\"+a[2:4], \"%H:%M\").time() for a in data['AD_TIME']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LONG min = -180.0\n",
      "LONG max = 180.0\n",
      "\n",
      "LAT min = 4.2\n",
      "LAT max = 69.0\n",
      "\n",
      "Shape_Leng min = 0.0\n",
      "Shape_Leng max = 11.18034\n",
      "\n",
      "WIND_KTS min = 10\n",
      "WIND_KTS max = 165\n",
      "\n",
      "PRESSURE min = 0\n",
      "PRESSURE max = 1024\n"
     ]
    }
   ],
   "source": [
    "print(\"LONG min = \"+str(min(data['LONG'])))\n",
    "print(\"LONG max = \"+str(max(data['LONG'])))\n",
    "print()\n",
    "print(\"LAT min = \"+str(min(data['LAT'])))\n",
    "print(\"LAT max = \"+str(max(data['LAT'])))\n",
    "print()\n",
    "print(\"Shape_Leng min = \"+str(min(data['Shape_Leng'])))\n",
    "print(\"Shape_Leng max = \"+str(max(data['Shape_Leng'])))\n",
    "print()\n",
    "print(\"WIND_KTS min = \"+str(min(data['WIND_KTS'])))\n",
    "print(\"WIND_KTS max = \"+str(max(data['WIND_KTS'])))\n",
    "print()\n",
    "print(\"PRESSURE min = \"+str(min(data['PRESSURE'])))\n",
    "print(\"PRESSURE max = \"+str(max(data['PRESSURE'])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sqlalchemy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color = 'blue'>Remember if you run into errors with the following code, you can always remove a created table with the following code:</font>\n",
    "```python\n",
    "conn = psycopg2.connect(\"dbname=dq_exercises user=nmolivo\")\n",
    "cur = conn.cursor()\n",
    "cur.execute(\"DROP TABLE stormdata\")\n",
    "conn.commit()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <font color = 'blue'> CREATE TABLE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color = 'blue'>First I'm going to make all our column names lowercase. This helps our code compile easier, as we interface to SQL using Python</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data.columns = [['fid', 'year', 'month', 'day', 'ad_time', 'btid', 'name', 'lat', 'long', 'wind_kts', 'pressure', 'cat', 'basin', 'shape_len']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "engine = create_engine('postgresql+psycopg2://nmolivo:MYPASSWORD@localhost/dq_exercises')\n",
    "data.to_sql('stormdata', engine, dtype = {'fid': sqlalchemy.types.INT, \\\n",
    "                                         'year':sqlalchemy.types.INT, \\\n",
    "                                         'month': sqlalchemy.types.INT, \\\n",
    "                                         'day': sqlalchemy.types.INT, \\\n",
    "                                         'ad_time': sqlalchemy.types.TIME(timezone=False), \\\n",
    "                                         'btid': sqlalchemy.types.CHAR(length=4), \\\n",
    "                                         'name': sqlalchemy.types.CHAR(length=9), \\\n",
    "                                         'lat': sqlalchemy.types.NUMERIC(precision=4, scale=2, asdecimal=True), \\\n",
    "                                         'long': sqlalchemy.types.NUMERIC(precision=7, scale=4, asdecimal=True), \\\n",
    "                                         'wind_kts': sqlalchemy.types.INT, \\\n",
    "                                         'pressure': sqlalchemy.types.INT, \\\n",
    "                                         'cat': sqlalchemy.types.CHAR(length=2), \\\n",
    "                                         'basin': sqlalchemy.types.CHAR(length=15), \\\n",
    "                                         'shape_len': sqlalchemy.types.NUMERIC(precision=9, scale=7, asdecimal=True)})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "with engine.connect() as conn:\n",
    "    conn.execute('ALTER TABLE stormdata ADD PRIMARY KEY (\"fid\");')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <font color = 'blue'>CREATE USERS</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(\"dbname=dq_exercises user=nmolivo\")\n",
    "cur = conn.cursor()\n",
    "cur.execute(\"\"\"\n",
    "CREATE USER stormadmin WITH CREATEDB PASSWORD 'admin123';\n",
    "CREATE GROUP stormusers NOLOGIN;\n",
    "REVOKE ALL ON stormdata FROM stormusers;\n",
    "GRANT SELECT ON stormdata TO stormusers;\n",
    "\"\"\")\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <font color = 'blue'>INSERT NEW DATA</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data2 = pd.read_csv('https://dq-content.s3.amazonaws.com/251/storm_data_additional.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fid</th>\n",
       "      <th>date</th>\n",
       "      <th>btid</th>\n",
       "      <th>name</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>wind_kts</th>\n",
       "      <th>pressure</th>\n",
       "      <th>cat</th>\n",
       "      <th>basin</th>\n",
       "      <th>shape_len</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>97fc91afc6acbb8df4563a90b8b1c4fa</td>\n",
       "      <td>1851-06-25 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NOTNAMED</td>\n",
       "      <td>28.0</td>\n",
       "      <td>-94.8</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>H1</td>\n",
       "      <td>North Atlantic</td>\n",
       "      <td>0.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>174b0313d3601872c6fd2c65150eef1c</td>\n",
       "      <td>1851-06-25 06:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NOTNAMED</td>\n",
       "      <td>28.0</td>\n",
       "      <td>-95.4</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>H1</td>\n",
       "      <td>North Atlantic</td>\n",
       "      <td>0.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>74f4a8b3a417f9509ce5f285f5666a99</td>\n",
       "      <td>1851-06-25 12:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NOTNAMED</td>\n",
       "      <td>28.0</td>\n",
       "      <td>-96.0</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>H1</td>\n",
       "      <td>North Atlantic</td>\n",
       "      <td>0.509902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a17379f02b1d82946a49cd865931e8ad</td>\n",
       "      <td>1851-06-25 18:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NOTNAMED</td>\n",
       "      <td>28.1</td>\n",
       "      <td>-96.5</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>H1</td>\n",
       "      <td>North Atlantic</td>\n",
       "      <td>0.509902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>013c8fb523008502e67c70c9ddc17dc5</td>\n",
       "      <td>1851-06-26 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NOTNAMED</td>\n",
       "      <td>28.2</td>\n",
       "      <td>-97.0</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>H1</td>\n",
       "      <td>North Atlantic</td>\n",
       "      <td>0.608276</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                fid                 date  btid      name  \\\n",
       "0  97fc91afc6acbb8df4563a90b8b1c4fa  1851-06-25 00:00:00     1  NOTNAMED   \n",
       "1  174b0313d3601872c6fd2c65150eef1c  1851-06-25 06:00:00     1  NOTNAMED   \n",
       "2  74f4a8b3a417f9509ce5f285f5666a99  1851-06-25 12:00:00     1  NOTNAMED   \n",
       "3  a17379f02b1d82946a49cd865931e8ad  1851-06-25 18:00:00     1  NOTNAMED   \n",
       "4  013c8fb523008502e67c70c9ddc17dc5  1851-06-26 00:00:00     1  NOTNAMED   \n",
       "\n",
       "    lat  long  wind_kts  pressure cat           basin  shape_len  \n",
       "0  28.0 -94.8        80         0  H1  North Atlantic   0.600000  \n",
       "1  28.0 -95.4        80         0  H1  North Atlantic   0.600000  \n",
       "2  28.0 -96.0        80         0  H1  North Atlantic   0.509902  \n",
       "3  28.1 -96.5        80         0  H1  North Atlantic   0.509902  \n",
       "4  28.2 -97.0        70         0  H1  North Atlantic   0.608276  "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data2.columns = [['fid', 'date', 'btid', 'name', 'lat', 'long', 'wind_kts', 'pressure', 'cat', 'basin', 'shape_len']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "FID 32\n",
      "DATETIME 19\n",
      "BTID 4\n",
      "NAME 9\n",
      "LAT 4\n",
      "LONG 7\n",
      "WIND_KTS 3\n",
      "PRESSURE 4\n",
      "CAT 2\n",
      "BASIN 15\n",
      "Shape_Leng 9\n"
     ]
    }
   ],
   "source": [
    "col_len_dict2 = {}\n",
    "for col in list(data2.columns):\n",
    "    max_len = 0\n",
    "    for i in range(0,len(data2)):\n",
    "        if max_len < len(str(data2[col][i])):\n",
    "            max_len = len(str(data2[col][i]))\n",
    "    print(col + \" \" + str(max_len))\n",
    "    col_len_dict2[col] = max_len"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<Font color = 'blue'>Wow this data looks completely different than our last data!! To insert it, we will need to modify our existing table to accept new datatypes. I used the <a href = \"https://www.postgresql.org/docs/7.4/static/functions-formatting.html\">PostgreSQL Documentation</a> as a reference for date formatting.</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(\"dbname=dq_exercises user=nmolivo\")\n",
    "cur = conn.cursor()\n",
    "cur.execute(\"\"\"\n",
    "ALTER TABLE stormdata ADD COLUMN date TIMESTAMP;\n",
    "UPDATE stormdata SET date = to_date(year || '-' || month || '-' || day || ' ' || ad_time, 'YYYY-MM-DD HH24:MI:SS');\n",
    "\"\"\")\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color ='blue'>Remove extra columns</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(\"dbname=dq_exercises user=nmolivo\")\n",
    "cur = conn.cursor()\n",
    "cur.execute(\"\"\"\n",
    "ALTER TABLE stormdata DROP COLUMN day;\n",
    "ALTER TABLE stormdata DROP COLUMN month;\n",
    "ALTER TABLE stormdata DROP COLUMN year;\n",
    "ALTER TABLE stormdata DROP COLUMN ad_time;\n",
    "\"\"\")\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color = 'blue'>Change columns to proper datatypes</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(\"dbname=dq_exercises user=nmolivo\")\n",
    "cur = conn.cursor()\n",
    "cur.execute(\"\"\"\n",
    "ALTER TABLE stormdata ALTER COLUMN fid TYPE VARCHAR(32);\n",
    "\"\"\")\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color = 'blue'>To insert our second dataframe, `data2` into our table, we'll need to<br>\n",
    "1. Make sure the columns are in the correct order<br>\n",
    "2. Turn the dataframe into a list of tuples representing each row.</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data2 = data2[['fid', 'btid', 'name', 'lat', 'long', 'wind_kts', 'pressure', 'cat', 'basin', 'shape_len', 'date']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "values = data2.values.tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color = 'blue'>Here it is, the big insert!</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sql = \"INSERT INTO stormdata(fid, btid, name, lat, long, wind_kts, pressure, cat, basin, shape_len, date) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)\"\n",
    "conn = psycopg2.connect(\"dbname=dq_exercises user=nmolivo\")\n",
    "cur = conn.cursor()\n",
    "cur.executemany(sql, values)\n",
    "conn.commit()\n",
    "cur.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color = 'blue'>Always check that your appends and merges worked correctly. I checked the following number against the table records count seen using Postico, and it worked!</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "118456"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(data) + len(data2)"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}