tornadoes_part2_webscraping_posgres.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"images/GAlogo.png\" style=\"float: left; margin: 15px; height: 100px\">\n",
    "\n",
    "# CAPSTONE PROJECT\n",
    "## US TORNADOES AND HOW TO PREDICT THEIR MAGNITUDE\n",
    "### Notebook for importing the mulitple csv files and copying them into local PostgreSQL database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "from sqlalchemy import create_engine\n",
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# To avoid display of warnings in Jupyter Notebook:\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Link to all the csv files:\n",
    "link = \"https://www1.ncdc.noaa.gov/pub/data/swdi/stormevents/csvfiles/\"\n",
    "response = requests.get(link)\n",
    "html = response.text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Making a soup out of the html and creating the list of file names:\n",
    "soup = BeautifulSoup(html)\n",
    "elements = soup.findAll(\"a\",{\"class\":\"\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(67238, 51)\n"
     ]
    },
    {
     "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>BEGIN_YEARMONTH</th>\n",
       "      <th>BEGIN_DAY</th>\n",
       "      <th>BEGIN_TIME</th>\n",
       "      <th>END_YEARMONTH</th>\n",
       "      <th>END_DAY</th>\n",
       "      <th>END_TIME</th>\n",
       "      <th>EPISODE_ID</th>\n",
       "      <th>EVENT_ID</th>\n",
       "      <th>STATE</th>\n",
       "      <th>STATE_FIPS</th>\n",
       "      <th>...</th>\n",
       "      <th>END_RANGE</th>\n",
       "      <th>END_AZIMUTH</th>\n",
       "      <th>END_LOCATION</th>\n",
       "      <th>BEGIN_LAT</th>\n",
       "      <th>BEGIN_LON</th>\n",
       "      <th>END_LAT</th>\n",
       "      <th>END_LON</th>\n",
       "      <th>EPISODE_NARRATIVE</th>\n",
       "      <th>EVENT_NARRATIVE</th>\n",
       "      <th>DATA_SOURCE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>195004</td>\n",
       "      <td>28</td>\n",
       "      <td>1445</td>\n",
       "      <td>195004</td>\n",
       "      <td>28</td>\n",
       "      <td>1445</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10096222</td>\n",
       "      <td>OKLAHOMA</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35.12</td>\n",
       "      <td>-99.2</td>\n",
       "      <td>35.17</td>\n",
       "      <td>-99.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PUB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>195004</td>\n",
       "      <td>29</td>\n",
       "      <td>1530</td>\n",
       "      <td>195004</td>\n",
       "      <td>29</td>\n",
       "      <td>1530</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10120412</td>\n",
       "      <td>TEXAS</td>\n",
       "      <td>48.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.90</td>\n",
       "      <td>-98.6</td>\n",
       "      <td>31.73</td>\n",
       "      <td>-98.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PUB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 51 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   BEGIN_YEARMONTH  BEGIN_DAY  BEGIN_TIME  END_YEARMONTH  END_DAY  END_TIME  \\\n",
       "0           195004         28        1445         195004       28      1445   \n",
       "1           195004         29        1530         195004       29      1530   \n",
       "\n",
       "   EPISODE_ID  EVENT_ID     STATE  STATE_FIPS     ...      END_RANGE  \\\n",
       "0         NaN  10096222  OKLAHOMA        40.0     ...            0.0   \n",
       "1         NaN  10120412     TEXAS        48.0     ...            0.0   \n",
       "\n",
       "  END_AZIMUTH END_LOCATION BEGIN_LAT  BEGIN_LON END_LAT END_LON  \\\n",
       "0         NaN          NaN     35.12      -99.2   35.17   -99.2   \n",
       "1         NaN          NaN     31.90      -98.6   31.73   -98.6   \n",
       "\n",
       "  EPISODE_NARRATIVE EVENT_NARRATIVE DATA_SOURCE  \n",
       "0               NaN             NaN         PUB  \n",
       "1               NaN             NaN         PUB  \n",
       "\n",
       "[2 rows x 51 columns]"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "StormEventDetails_allyears = []\n",
    "\n",
    "# The first 6 elements and the last 2 have to be discarded:\n",
    "for element in elements[6:-2]:\n",
    "    \n",
    "    # Selecting only the storm events tables (one for each year)\n",
    "    if element.attrs['href'].startswith('StormEvents_details'):\n",
    "        \n",
    "        filename = element.attrs['href']\n",
    "        StormEventDetails_url = link + filename\n",
    "        \n",
    "        # Creating an iterator in order to load the file in chunks of 1000 elements:\n",
    "        iter_csv = pd.read_csv(StormEventDetails_url, compression='gzip', iterator=True,\n",
    "                               chunksize=1000)\n",
    "        \n",
    "        # Concatenating the different chunks into a single dataframe, \n",
    "        # selecting the tornado events only:\n",
    "        StormEventDetails_allyears.append(\n",
    "            pd.concat([chunk[chunk['EVENT_TYPE'].map(lambda x: x.lower())\n",
    "                             == 'tornado'] for chunk in iter_csvtttt]))\n",
    "\n",
    "# Concatenating all the dataframes from the different years:\n",
    "StormEventDetails = pd.concat(StormEventDetails_allyears)\n",
    "        \n",
    "print StormEventDetails.shape\n",
    "StormEventDetails.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(15991, 11) (1181011, 11)\n"
     ]
    },
    {
     "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>FAT_YEARMONTH</th>\n",
       "      <th>FAT_DAY</th>\n",
       "      <th>FAT_TIME</th>\n",
       "      <th>FATALITY_ID</th>\n",
       "      <th>EVENT_ID</th>\n",
       "      <th>FATALITY_TYPE</th>\n",
       "      <th>FATALITY_DATE</th>\n",
       "      <th>FATALITY_AGE</th>\n",
       "      <th>FATALITY_SEX</th>\n",
       "      <th>FATALITY_LOCATION</th>\n",
       "      <th>EVENT_YEARMONTH</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>195001</td>\n",
       "      <td>13</td>\n",
       "      <td>525</td>\n",
       "      <td>1005198</td>\n",
       "      <td>9981922</td>\n",
       "      <td>D</td>\n",
       "      <td>01/13/1950 05:25:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>195001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>195002</td>\n",
       "      <td>12</td>\n",
       "      <td>1200</td>\n",
       "      <td>1005199</td>\n",
       "      <td>10049525</td>\n",
       "      <td>D</td>\n",
       "      <td>02/12/1950 12:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>195002</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   FAT_YEARMONTH  FAT_DAY  FAT_TIME  FATALITY_ID  EVENT_ID FATALITY_TYPE  \\\n",
       "0         195001       13       525      1005198   9981922             D   \n",
       "1         195002       12      1200      1005199  10049525             D   \n",
       "\n",
       "         FATALITY_DATE  FATALITY_AGE FATALITY_SEX FATALITY_LOCATION  \\\n",
       "0  01/13/1950 05:25:00           NaN          NaN               NaN   \n",
       "1  02/12/1950 12:00:00           NaN          NaN               NaN   \n",
       "\n",
       "   EVENT_YEARMONTH  \n",
       "0           195001  \n",
       "1           195002  "
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Doing the same for the other two tables (locations and fatalities):\n",
    "StormEventFatalities_allyears = []\n",
    "StormEventLocations_allyears = []\n",
    "\n",
    "for element in elements[6:-2]:\n",
    "    if element.attrs['href'].startswith('StormEvents_fatalities'):\n",
    "        filename = element.attrs['href']\n",
    "        StormEventFatalities_url = link+filename\n",
    "        iter_csv = pd.read_csv(StormEventFatalities_url, compression='gzip', iterator=True,\n",
    "                               chunksize=1000)\n",
    "        StormEventFatalities_allyears.append(pd.concat(iter_csv))\n",
    "\n",
    "    elif element.attrs['href'].startswith('StormEvents_locations'):\n",
    "        filename = element.attrs['href']\n",
    "        StormEventLocation_url = link+filename\n",
    "        iter_csv = pd.read_csv(StormEventLocation_url, compression='gzip', iterator=True,\n",
    "                               chunksize=1000)\n",
    "        StormEventLocations_allyears.append(pd.concat(iter_csv))\n",
    "        \n",
    "StormEventFatalities = pd.concat(StormEventFatalities_allyears)\n",
    "StormEventLocations = pd.concat(StormEventLocations_allyears)\n",
    "\n",
    "print StormEventFatalities.shape, StormEventLocations.shape\n",
    "StormEventFatalities.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "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>YEARMONTH</th>\n",
       "      <th>EPISODE_ID</th>\n",
       "      <th>EVENT_ID</th>\n",
       "      <th>LOCATION_INDEX</th>\n",
       "      <th>RANGE</th>\n",
       "      <th>AZIMUTH</th>\n",
       "      <th>LOCATION</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "      <th>LAT2</th>\n",
       "      <th>LON2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>199603</td>\n",
       "      <td>2030059</td>\n",
       "      <td>5548852</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LANGLEY</td>\n",
       "      <td>34.32</td>\n",
       "      <td>-93.83</td>\n",
       "      <td>3419.0</td>\n",
       "      <td>9350.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>199603</td>\n",
       "      <td>2030060</td>\n",
       "      <td>5548853</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>S</td>\n",
       "      <td>YELLVILLE</td>\n",
       "      <td>36.20</td>\n",
       "      <td>-92.68</td>\n",
       "      <td>3612.0</td>\n",
       "      <td>9241.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  YEARMONTH EPISODE_ID EVENT_ID LOCATION_INDEX  RANGE AZIMUTH   LOCATION  \\\n",
       "0    199603    2030059  5548852              1    NaN     NaN    LANGLEY   \n",
       "1    199603    2030060  5548853              1    2.0       S  YELLVILLE   \n",
       "\n",
       "   LATITUDE  LONGITUDE    LAT2    LON2  \n",
       "0     34.32     -93.83  3419.0  9350.0  \n",
       "1     36.20     -92.68  3612.0  9241.0  "
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "StormEventLocations.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Creating engine connection to my local \"storms\" database, using sqlalchemy:\n",
    "engine_local = create_engine('postgresql://localhost:5432/storms')\n",
    "\n",
    "# Copying the first dataframe to local PostgreSQL:\n",
    "StormEventDetails.to_sql('tornadoes_1950_mid2017', engine_local, index=False,\n",
    "                         if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Copying the second dataframe\n",
    "StormEventFatalities.to_sql('fatalities_1950_mid2017', engine_local, index=False,\n",
    "                            if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Copying the third dataframe\n",
    "StormEventLocations.to_sql('locations_1950_mid2017', engine_local, index=False,\n",
    "                           if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the three tables now saved locally, we have a backup.<br>\n",
    "Following is part 3 of the capstone, where the tables will be read directly from my local PostgreSQL."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}