Database Builder Weather.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Notebook for creating dataset that will be uploaded to aws rds (MySQL)\n",
    "### The weather dataset was created using the noaa_weather.py program.\n",
    "##### In the future the weather may be aggregated somehow, this method is good enough to get the neural network set up and create the process for feeding the weather data to the network."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(r'weather.csv', low_memory = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>USAF</th>\n",
       "      <th>WBAN</th>\n",
       "      <th>DATE</th>\n",
       "      <th>TIME</th>\n",
       "      <th>SOURCE</th>\n",
       "      <th>LAT</th>\n",
       "      <th>LONG</th>\n",
       "      <th>TYPE</th>\n",
       "      <th>ELEV</th>\n",
       "      <th>FWSID</th>\n",
       "      <th>...</th>\n",
       "      <th>VISIBILITY_QUALITY_VAR</th>\n",
       "      <th>AIR_TEMP</th>\n",
       "      <th>AIR_TEMP_QC</th>\n",
       "      <th>AIR_TEMP_DEW</th>\n",
       "      <th>AIR_TEMP_DEW_QC</th>\n",
       "      <th>ATM_PRESSURE</th>\n",
       "      <th>ATM_PRESSURE_QC</th>\n",
       "      <th>STATION</th>\n",
       "      <th>LAT_LONG</th>\n",
       "      <th>MGRS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>690150</td>\n",
       "      <td>93121</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>00:56:00</td>\n",
       "      <td>6</td>\n",
       "      <td>34.3</td>\n",
       "      <td>-116.166</td>\n",
       "      <td>FM-15</td>\n",
       "      <td>696.0</td>\n",
       "      <td>KNXP</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>7.2</td>\n",
       "      <td>5</td>\n",
       "      <td>-11.7</td>\n",
       "      <td>5</td>\n",
       "      <td>1023.1</td>\n",
       "      <td>5</td>\n",
       "      <td>690150-93121</td>\n",
       "      <td>34.3,-116.166</td>\n",
       "      <td>11SNT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>690150</td>\n",
       "      <td>93121</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>01:56:00</td>\n",
       "      <td>6</td>\n",
       "      <td>34.3</td>\n",
       "      <td>-116.166</td>\n",
       "      <td>FM-15</td>\n",
       "      <td>696.0</td>\n",
       "      <td>KNXP</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>3.9</td>\n",
       "      <td>5</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>5</td>\n",
       "      <td>1023.9</td>\n",
       "      <td>5</td>\n",
       "      <td>690150-93121</td>\n",
       "      <td>34.3,-116.166</td>\n",
       "      <td>11SNT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>690150</td>\n",
       "      <td>93121</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>02:56:00</td>\n",
       "      <td>6</td>\n",
       "      <td>34.3</td>\n",
       "      <td>-116.166</td>\n",
       "      <td>FM-15</td>\n",
       "      <td>696.0</td>\n",
       "      <td>KNXP</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>4.4</td>\n",
       "      <td>5</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>5</td>\n",
       "      <td>1024.0</td>\n",
       "      <td>5</td>\n",
       "      <td>690150-93121</td>\n",
       "      <td>34.3,-116.166</td>\n",
       "      <td>11SNT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>690150</td>\n",
       "      <td>93121</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>03:56:00</td>\n",
       "      <td>6</td>\n",
       "      <td>34.3</td>\n",
       "      <td>-116.166</td>\n",
       "      <td>FM-15</td>\n",
       "      <td>696.0</td>\n",
       "      <td>KNXP</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>1.7</td>\n",
       "      <td>5</td>\n",
       "      <td>-12.8</td>\n",
       "      <td>5</td>\n",
       "      <td>1024.4</td>\n",
       "      <td>5</td>\n",
       "      <td>690150-93121</td>\n",
       "      <td>34.3,-116.166</td>\n",
       "      <td>11SNT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>690150</td>\n",
       "      <td>93121</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>04:56:00</td>\n",
       "      <td>6</td>\n",
       "      <td>34.3</td>\n",
       "      <td>-116.166</td>\n",
       "      <td>FM-15</td>\n",
       "      <td>696.0</td>\n",
       "      <td>KNXP</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>1.7</td>\n",
       "      <td>5</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>5</td>\n",
       "      <td>1024.5</td>\n",
       "      <td>5</td>\n",
       "      <td>690150-93121</td>\n",
       "      <td>34.3,-116.166</td>\n",
       "      <td>11SNT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 33 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     USAF   WBAN        DATE      TIME SOURCE   LAT     LONG   TYPE   ELEV  \\\n",
       "0  690150  93121  2013-01-01  00:56:00      6  34.3 -116.166  FM-15  696.0   \n",
       "1  690150  93121  2013-01-01  01:56:00      6  34.3 -116.166  FM-15  696.0   \n",
       "2  690150  93121  2013-01-01  02:56:00      6  34.3 -116.166  FM-15  696.0   \n",
       "3  690150  93121  2013-01-01  03:56:00      6  34.3 -116.166  FM-15  696.0   \n",
       "4  690150  93121  2013-01-01  04:56:00      6  34.3 -116.166  FM-15  696.0   \n",
       "\n",
       "   FWSID  ... VISIBILITY_QUALITY_VAR  AIR_TEMP AIR_TEMP_QC AIR_TEMP_DEW  \\\n",
       "0  KNXP   ...                      5       7.2           5        -11.7   \n",
       "1  KNXP   ...                      5       3.9           5        -12.2   \n",
       "2  KNXP   ...                      5       4.4           5        -12.2   \n",
       "3  KNXP   ...                      5       1.7           5        -12.8   \n",
       "4  KNXP   ...                      5       1.7           5        -13.3   \n",
       "\n",
       "   AIR_TEMP_DEW_QC ATM_PRESSURE  ATM_PRESSURE_QC       STATION       LAT_LONG  \\\n",
       "0                5       1023.1                5  690150-93121  34.3,-116.166   \n",
       "1                5       1023.9                5  690150-93121  34.3,-116.166   \n",
       "2                5       1024.0                5  690150-93121  34.3,-116.166   \n",
       "3                5       1024.4                5  690150-93121  34.3,-116.166   \n",
       "4                5       1024.5                5  690150-93121  34.3,-116.166   \n",
       "\n",
       "    MGRS  \n",
       "0  11SNT  \n",
       "1  11SNT  \n",
       "2  11SNT  \n",
       "3  11SNT  \n",
       "4  11SNT  \n",
       "\n",
       "[5 rows x 33 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert to datetime for filtering\n",
    "df['DATE']= pd.to_datetime(df['DATE'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#use only 2016 for dev purposes\n",
    "df = df[df['DATE'].dt.year == 2016]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#remove these columns for now\n",
    "del df['WIND_QC']\n",
    "del df['WIND_SPEED_QC']\n",
    "del df['SKY_OBS_QC']\n",
    "del df['SKY_OBS']\n",
    "del df['SKY_OBS_CIEL']\n",
    "del df['LAT_LONG']\n",
    "del df['SKY_CAVOK']\n",
    "del df['VISIBILITY_DIST']\n",
    "del df['VISIBILITY_QC']\n",
    "del df['VISIBILITY_VAR']\n",
    "del df['VISIBILITY_QUALITY_VAR']\n",
    "del df['AIR_TEMP_QC']\n",
    "del df['ATM_PRESSURE_QC']\n",
    "del df['MPOQC']\n",
    "del df['AIR_TEMP_DEW_QC']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "import os\n",
    "USER = os.environ.get('DBUSERNAME')\n",
    "PASSWORD = os.environ.get('DBPASSWORD')\n",
    "ENDPOINT = os.environ.get('DBDEV')\n",
    "SCHEMA = 'dev'\n",
    "engine = create_engine(f\"mysql+pymysql://{USER}:{PASSWORD}@{ENDPOINT}/{SCHEMA}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# send to database\n",
    "table = 'weather'\n",
    "df.to_sql(table, con = engine, if_exists = 'replace')"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "c5b322d1f5beaecfb28b050f61b5bc2c682da0c88a01138942a1439f934c1c07"
  },
  "kernelspec": {
   "display_name": "Python 3.9.5 64-bit",
   "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.9.5"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}