.ipynb_checkpoints/ETL_Project-checkpoint.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## LOGS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Import SQL Alchemy\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "# Import and establish Base for which classes will be constructed \n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "Base = declarative_base()\n",
    "\n",
    "# Import modules to declare columns and column data types\n",
    "from sqlalchemy import Column, Integer, String, Float"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_path= 'Resources/OGSR-Library-logs-2013-02-13.csv'\n",
    "logs_df=pd.read_csv(data_path)"
   ]
  },
  {
   "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>index</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "      <th>licence</th>\n",
       "      <th>county</th>\n",
       "      <th>township</th>\n",
       "      <th>lot</th>\n",
       "      <th>conc</th>\n",
       "      <th>total depth date</th>\n",
       "      <th>plugged date</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>F005933_MOSA000_ELEC</td>\n",
       "      <td>ELEC</td>\n",
       "      <td>F005933</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>42.758067</td>\n",
       "      <td>-81.791921</td>\n",
       "      <td>http://www.ogsrlibrary.com/logs/view/F005933_M...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>F005959_MOSA000_ELEC</td>\n",
       "      <td>ELEC</td>\n",
       "      <td>F005959</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>42.758951</td>\n",
       "      <td>-81.793031</td>\n",
       "      <td>http://www.ogsrlibrary.com/logs/view/F005959_M...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>F005959_MOSA000_GRL0</td>\n",
       "      <td>GRL0</td>\n",
       "      <td>F005959</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>42.758951</td>\n",
       "      <td>-81.793031</td>\n",
       "      <td>http://www.ogsrlibrary.com/logs/view/F005959_M...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>F013469_MOSA000_GRN1</td>\n",
       "      <td>GRN1</td>\n",
       "      <td>F013469</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>42.756511</td>\n",
       "      <td>-81.787682</td>\n",
       "      <td>http://www.ogsrlibrary.com/logs/view/F013469_M...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>F013469_MOSA000_GRN2</td>\n",
       "      <td>GRN2</td>\n",
       "      <td>F013469</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>42.756511</td>\n",
       "      <td>-81.787682</td>\n",
       "      <td>http://www.ogsrlibrary.com/logs/view/F013469_M...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index                  name  type  licence county township  lot conc  \\\n",
       "0      0  F005933_MOSA000_ELEC  ELEC  F005933    NaN      NaN  NaN  NaN   \n",
       "1      1  F005959_MOSA000_ELEC  ELEC  F005959    NaN      NaN  NaN  NaN   \n",
       "2      2  F005959_MOSA000_GRL0  GRL0  F005959    NaN      NaN  NaN  NaN   \n",
       "3      3  F013469_MOSA000_GRN1  GRN1  F013469    NaN      NaN  NaN  NaN   \n",
       "4      4  F013469_MOSA000_GRN2  GRN2  F013469    NaN      NaN  NaN  NaN   \n",
       "\n",
       "  total depth date plugged date   latitude  longitude  \\\n",
       "0              NaN          NaN  42.758067 -81.791921   \n",
       "1              NaN          NaN  42.758951 -81.793031   \n",
       "2              NaN          NaN  42.758951 -81.793031   \n",
       "3              NaN          NaN  42.756511 -81.787682   \n",
       "4              NaN          NaN  42.756511 -81.787682   \n",
       "\n",
       "                                                 url  \n",
       "0  http://www.ogsrlibrary.com/logs/view/F005933_M...  \n",
       "1  http://www.ogsrlibrary.com/logs/view/F005959_M...  \n",
       "2  http://www.ogsrlibrary.com/logs/view/F005959_M...  \n",
       "3  http://www.ogsrlibrary.com/logs/view/F013469_M...  \n",
       "4  http://www.ogsrlibrary.com/logs/view/F013469_M...  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "logs_df.reset_index(drop=False, inplace = True)\n",
    "logs_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>log_type_id</th>\n",
       "      <th>log_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>ELEC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>GRL0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>GRN1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>GRN2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>GRN3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   log_type_id log_type\n",
       "0            0     ELEC\n",
       "1            1     GRL0\n",
       "2            2     GRN1\n",
       "3            3     GRN2\n",
       "4            4     GRN3"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "logs_df['type']=logs_df['type'].str.upper()\n",
    "log_type = logs_df['type'].unique()\n",
    "cat_log_type=pd.DataFrame(log_type)\n",
    "cat_log_type.reset_index(drop=False, inplace = True)\n",
    "cat_log_type.columns=['log_type_id','log_type']\n",
    "# cat_log_type.set_index('log_type_id', inplace = True)\n",
    "cat_log_type.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_log_type.to_csv(r'Resources/cat_log_type.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>log_id</th>\n",
       "      <th>log_name</th>\n",
       "      <th>log_type_id</th>\n",
       "      <th>license</th>\n",
       "      <th>total_depth_date</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>utm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>F005933_MOSA000_ELEC</td>\n",
       "      <td>0</td>\n",
       "      <td>F005933</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-81.791921</td>\n",
       "      <td>42.758067</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>F005959_MOSA000_ELEC</td>\n",
       "      <td>0</td>\n",
       "      <td>F005959</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-81.793031</td>\n",
       "      <td>42.758951</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>F005959_MOSA000_GRL0</td>\n",
       "      <td>1</td>\n",
       "      <td>F005959</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-81.793031</td>\n",
       "      <td>42.758951</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>F013469_MOSA000_GRN1</td>\n",
       "      <td>2</td>\n",
       "      <td>F013469</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-81.787682</td>\n",
       "      <td>42.756511</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>F013469_MOSA000_GRN2</td>\n",
       "      <td>3</td>\n",
       "      <td>F013469</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-81.787682</td>\n",
       "      <td>42.756511</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>F013469_MOSA000_GRN3</td>\n",
       "      <td>4</td>\n",
       "      <td>F013469</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-81.787682</td>\n",
       "      <td>42.756511</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>F013469_MOSA000_GRN4</td>\n",
       "      <td>5</td>\n",
       "      <td>F013469</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-81.787682</td>\n",
       "      <td>42.756511</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>F013799_ENNISKI_GRNL</td>\n",
       "      <td>6</td>\n",
       "      <td>F013799</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>F005959_MOSA000_REST</td>\n",
       "      <td>7</td>\n",
       "      <td>F005959</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-81.793031</td>\n",
       "      <td>42.758951</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>F013469_MOSA000_RTRS</td>\n",
       "      <td>8</td>\n",
       "      <td>F013469</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-81.787682</td>\n",
       "      <td>42.756511</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>T005035A_LAKE067_CMPL</td>\n",
       "      <td>9</td>\n",
       "      <td>T005035A</td>\n",
       "      <td>6/29/1979 0:00</td>\n",
       "      <td>-79.888679</td>\n",
       "      <td>42.613953</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>T005035A_LAKE067_CNFD</td>\n",
       "      <td>10</td>\n",
       "      <td>T005035A</td>\n",
       "      <td>6/29/1979 0:00</td>\n",
       "      <td>-79.888679</td>\n",
       "      <td>42.613953</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12</td>\n",
       "      <td>T005035A_LAKE067_DLL0</td>\n",
       "      <td>11</td>\n",
       "      <td>T005035A</td>\n",
       "      <td>6/29/1979 0:00</td>\n",
       "      <td>-79.888679</td>\n",
       "      <td>42.613953</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13</td>\n",
       "      <td>T006311_JOCELYN_0CNL</td>\n",
       "      <td>12</td>\n",
       "      <td>T006311</td>\n",
       "      <td>8/21/1983 0:00</td>\n",
       "      <td>-83.927432</td>\n",
       "      <td>46.096238</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>T006311_JOCELYN_CSCN</td>\n",
       "      <td>13</td>\n",
       "      <td>T006311</td>\n",
       "      <td>8/21/1983 0:00</td>\n",
       "      <td>-83.927432</td>\n",
       "      <td>46.096238</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    log_id               log_name  log_type_id   license total_depth_date  \\\n",
       "0        0   F005933_MOSA000_ELEC            0   F005933              NaN   \n",
       "1        1   F005959_MOSA000_ELEC            0   F005959              NaN   \n",
       "2        2   F005959_MOSA000_GRL0            1   F005959              NaN   \n",
       "3        3   F013469_MOSA000_GRN1            2   F013469              NaN   \n",
       "4        4   F013469_MOSA000_GRN2            3   F013469              NaN   \n",
       "5        5   F013469_MOSA000_GRN3            4   F013469              NaN   \n",
       "6        6   F013469_MOSA000_GRN4            5   F013469              NaN   \n",
       "7        7   F013799_ENNISKI_GRNL            6   F013799              NaN   \n",
       "8        8   F005959_MOSA000_REST            7   F005959              NaN   \n",
       "9        9   F013469_MOSA000_RTRS            8   F013469              NaN   \n",
       "10      10  T005035A_LAKE067_CMPL            9  T005035A   6/29/1979 0:00   \n",
       "11      11  T005035A_LAKE067_CNFD           10  T005035A   6/29/1979 0:00   \n",
       "12      12  T005035A_LAKE067_DLL0           11  T005035A   6/29/1979 0:00   \n",
       "13      13   T006311_JOCELYN_0CNL           12   T006311   8/21/1983 0:00   \n",
       "14      14   T006311_JOCELYN_CSCN           13   T006311   8/21/1983 0:00   \n",
       "\n",
       "            x          y  utm  \n",
       "0  -81.791921  42.758067   17  \n",
       "1  -81.793031  42.758951   17  \n",
       "2  -81.793031  42.758951   17  \n",
       "3  -81.787682  42.756511   17  \n",
       "4  -81.787682  42.756511   17  \n",
       "5  -81.787682  42.756511   17  \n",
       "6  -81.787682  42.756511   17  \n",
       "7         NaN        NaN   17  \n",
       "8  -81.793031  42.758951   17  \n",
       "9  -81.787682  42.756511   17  \n",
       "10 -79.888679  42.613953   17  \n",
       "11 -79.888679  42.613953   17  \n",
       "12 -79.888679  42.613953   17  \n",
       "13 -83.927432  46.096238   17  \n",
       "14 -83.927432  46.096238   17  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "log_hdr = logs_df.loc[:, [\"index\", \"name\", \"type\",\"licence\", \"total depth date\", \"latitude\", \"longitude\"]]\n",
    "log_hdr = pd.merge(left=logs_df, right=cat_log_type, left_on='type', right_on='log_type', how='left')\n",
    "log_hdr[\"utm\"] = 17\n",
    "logs = pd.DataFrame(log_hdr[[\"index\", \"name\", \"log_type_id\",\"licence\", \"total depth date\", \"longitude\", \"latitude\", \"utm\"]])\n",
    "logs.rename(columns = {'index':'log_id', 'name':'log_name',\n",
    "                                 'licence':'license', 'total depth date':'total_depth_date',\n",
    "                                 'longitude':'x', 'latitude':'y'\n",
    "                                }, inplace = True)\n",
    "logs.head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_log_type.set_index('log_type_id', inplace = True)\n",
    "logs.set_index('log_id', inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a connection to a SQLite database\n",
    "connection_string = \"postgres:pg@localhost:5432/ETL_PROJECT\"\n",
    "engine = create_engine(f'postgresql://{connection_string}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['cat_well_type',\n",
       " 'cat_well_mode',\n",
       " 'cat_operator',\n",
       " 'cat_log_type',\n",
       " 'well_hdr',\n",
       " 'log_hdr']"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Confirm tables\n",
    "engine.table_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_log_type.to_sql(name='cat_log_type', con=engine, if_exists='append', index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "logs.to_sql(name='log_hdr', con=engine, if_exists='append', index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}