nat_parks_db.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "experienced-connection",
   "metadata": {},
   "source": [
    "<h1>SI 564 Final Project: Trails in U.S. National Parks</h1>\n",
    "<p>Haley Johnson</p>\n",
    "\n",
    "<p>Code to create normalized database tables from All Trails dataset</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "charitable-intersection",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pymysql\n",
    "from sqlalchemy import create_engine \n",
    "from IPython.display import Image"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "after-recruitment",
   "metadata": {},
   "source": [
    "<h2>Database Diagram</h2>\n",
    "<p><a href = \"https://www.kaggle.com/datasets/planejane/national-park-trails\">National Parks data</a></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "rapid-senegal",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<img src=\"nat_parks_erd.png\" width=\"800\" height=\"800\"/>"
      ],
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Image(url=\"nat_parks_erd.png\", width=800, height=800)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "toxic-johnson",
   "metadata": {},
   "source": [
    "<h2>Connect to SQL</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "alone-transparency",
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(f'mysql+pymysql://haleyej-rw:{password}@34.134.16.183:14192/nat_parks')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dominant-despite",
   "metadata": {},
   "source": [
    "<h2>Split Data Into Tables</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "sophisticated-smoke",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"trails_data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "mechanical-stability",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['area_name'] = df['area_name'].apply(lambda s: np.where(s == 'Congaree National Park Wilderness', 'Congaree', s))\n",
    "\n",
    "not_nps = ['Fort Pickens National Park', 'Clayton Co International Park, Jonesboro GA', \n",
    "           'Fort Hunt National Park', 'Wolf Trap National Park for the Performing Arts']\n",
    "\n",
    "df['valid'] = df['area_name'].apply(lambda s: s not in not_nps)\n",
    "\n",
    "df = df[df['valid'] == True]\n",
    "df = df.drop(columns = ['valid', 'trail_id', 'city_name', 'country_name', '_geoloc', 'activities', \n",
    "                        'num_reviews', 'features'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "handy-ozone",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['area_name'] = df['area_name'].str.replace(\" National Park\", \"\")\n",
    "df['area_name'] = df['area_name'].str.replace(\" National Park and Preserve\", \"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "ultimate-foundation",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['state_name'] = df['state_name'].astype(str)\n",
    "df['state_name'] = df['state_name'].apply(lambda s: np.where(s == 'Maui', 'Hawaii', s))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "portuguese-intervention",
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_table(df, col):\n",
    "    '''\n",
    "    Turns column in the dataframe\n",
    "    into a new dataframe that just\n",
    "    contains the unique values of \n",
    "    that column\n",
    "    \n",
    "    Function is used to split\n",
    "    dataframe into smaller tables\n",
    "    for normalization\n",
    "    \n",
    "    Returns a new dataframe\n",
    "    '''\n",
    "    temp = df[col].unique()\n",
    "    df = pd.DataFrame(temp, columns = [col])\n",
    "    df = df.reset_index()\n",
    "    df = df.rename(columns = {'index': 'id'})\n",
    "    df['id'] = df['id'].apply(lambda s: s + 1)\n",
    "    return df\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "behavioral-samba",
   "metadata": {},
   "outputs": [],
   "source": [
    "parks_df = create_table(df, 'area_name')\n",
    "states_df = create_table(df, 'state_name')\n",
    "routes_df = create_table(df, 'route_type')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "available-fitness",
   "metadata": {},
   "source": [
    "<h3>Trails Table</h3>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "removable-mystery",
   "metadata": {},
   "outputs": [],
   "source": [
    "metric = df[df['units'] == 'm']\n",
    "imperial = df[df['units'] == 'i']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "third-tsunami",
   "metadata": {},
   "outputs": [],
   "source": [
    "def meters_to_yards(s):\n",
    "    '''\n",
    "    Takes in column of dataframe \n",
    "    \n",
    "    Convers meters to yards\n",
    "    '''\n",
    "    return s * 1.09361\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "different-gibraltar",
   "metadata": {},
   "outputs": [],
   "source": [
    "metric['elevation_gain'] = metric['elevation_gain'].apply(meters_to_yards)\n",
    "metric['length'] = metric['length'].apply(meters_to_yards)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "continuing-honolulu",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat([imperial, metric])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "lasting-flash",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(columns = ['units'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dramatic-salem",
   "metadata": {},
   "source": [
    "<h2>Normalize</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "amber-harvard",
   "metadata": {},
   "outputs": [],
   "source": [
    "def normalize(df1, df2, target, fk):\n",
    "    '''\n",
    "    Removes data that have been \n",
    "    normlized out from the main \n",
    "    dataframe\n",
    "    \n",
    "    Connects main table to supporting\n",
    "    tables with fk column\n",
    "    \n",
    "    Takes in four arguments:\n",
    "    the two dataframes that are being marged, \n",
    "    the column used to merge them\n",
    "    the foreign key connecting the tables\n",
    "    \n",
    "    Return a dataframe normalized \n",
    "    with respect to df2\n",
    "    '''\n",
    "    df1 = df1.merge(df2, on = target)\n",
    "    df1 = df1.rename(columns = {'id': fk})\n",
    "    df1 = df1.drop(columns = target)\n",
    "    return df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "distinct-sociology",
   "metadata": {},
   "outputs": [],
   "source": [
    "targets = [(states_df, 'state_name', 'state_id'), (routes_df, 'route_type', 'route_type_id'),\n",
    "           (parks_df, 'area_name', 'park_id')]\n",
    "           \n",
    "for target in targets:\n",
    "    df = normalize(df, target[0], target[1], target[2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "matched-firewall",
   "metadata": {},
   "outputs": [],
   "source": [
    "parks_df = parks_df.rename(columns = {'area_name': 'park'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "apparent-crack",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.reset_index()\n",
    "df = df.rename(columns = {'index': 'id'})\n",
    "df['id'] = df['id'].apply(lambda s: s + 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "hairy-jimmy",
   "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>popularity</th>\n",
       "      <th>length</th>\n",
       "      <th>elevation_gain</th>\n",
       "      <th>difficulty_rating</th>\n",
       "      <th>visitor_usage</th>\n",
       "      <th>avg_rating</th>\n",
       "      <th>state_id</th>\n",
       "      <th>route_type_id</th>\n",
       "      <th>park_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Harding Ice Field Trail</td>\n",
       "      <td>24.8931</td>\n",
       "      <td>15610.598</td>\n",
       "      <td>1161.8976</td>\n",
       "      <td>5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Exit Glacier Trail</td>\n",
       "      <td>17.7821</td>\n",
       "      <td>2896.812</td>\n",
       "      <td>81.9912</td>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Mount Healy Overlook Trail</td>\n",
       "      <td>18.0311</td>\n",
       "      <td>6920.162</td>\n",
       "      <td>507.7968</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Triple Lakes Trail</td>\n",
       "      <td>12.5935</td>\n",
       "      <td>29772.790</td>\n",
       "      <td>1124.7120</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Bison Gulch</td>\n",
       "      <td>8.8331</td>\n",
       "      <td>11265.380</td>\n",
       "      <td>1178.9664</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                        name  popularity     length  elevation_gain  \\\n",
       "0   1     Harding Ice Field Trail     24.8931  15610.598       1161.8976   \n",
       "1   2          Exit Glacier Trail     17.7821   2896.812         81.9912   \n",
       "2   3  Mount Healy Overlook Trail     18.0311   6920.162        507.7968   \n",
       "3   4          Triple Lakes Trail     12.5935  29772.790       1124.7120   \n",
       "4   5                 Bison Gulch      8.8331  11265.380       1178.9664   \n",
       "\n",
       "   difficulty_rating  visitor_usage  avg_rating  state_id  route_type_id  \\\n",
       "0                  5            3.0         5.0         1              1   \n",
       "1                  1            3.0         4.5         1              1   \n",
       "2                  3            1.0         4.5         1              1   \n",
       "3                  5            1.0         4.5         1              1   \n",
       "4                  5            1.0         4.5         1              1   \n",
       "\n",
       "   park_id  \n",
       "0        1  \n",
       "1        1  \n",
       "2        2  \n",
       "3        2  \n",
       "4        2  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "entertaining-cholesterol",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3306, 11)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "lesser-thomson",
   "metadata": {},
   "source": [
    "<h2>Write To SQL</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "rubber-scout",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_sql(\"trails\", con = engine, index = False)\n",
    "states_df.to_sql(\"states\", con = engine, index = False)\n",
    "parks_df.to_sql(\"parks\", con = engine, index = False)\n",
    "routes_df.to_sql(\"route_types\", con = engine, index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "changing-fisher",
   "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}