homework_answer_key.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "handled-greensboro",
   "metadata": {},
   "source": [
    "<h1>SI 564 Final Project: Trails in U.S. National Parks</h1>\n",
    "<p>Haley Johnson</p>\n",
    "\n",
    "<p>Solution key to homework assignment. I used starter code from <a href = \"https://gist.github.com/mlhess/be7841fda8baba5645f6aad807b50083\">here</a> to connect to SQL.</p>\n",
    "<p>Many of these problems can be solved in multiple ways. This is just one set of sample solutions.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "atmospheric-africa",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pymysql\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "smooth-locator",
   "metadata": {},
   "outputs": [],
   "source": [
    "con = pymysql.connect(host='34.134.16.183', port=14192,user='haleyej-ro',password=p,database='nat_parks',cursorclass=pymysql.cursors.DictCursor)  \n",
    "cur = con.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "backed-committee",
   "metadata": {},
   "source": [
    "<h4>Question 1</h4>\n",
    "<p>What national park has the longest trail length, on average?</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "increasing-journalist",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Kings Canyon National Park, average length: 38162.46 yards\n"
     ]
    }
   ],
   "source": [
    "cur.execute('''SELECT p.park, round(avg(t.length), 2) as average_length\n",
    "               FROM trails t\n",
    "               JOIN parks p \n",
    "               ON t.park_id = p.id\n",
    "               GROUP BY p.park\n",
    "               ORDER BY avg(t.length) DESC\n",
    "               LIMIT 1;''')\n",
    "for row in cur:\n",
    "    print(f\"{row['park']} National Park, average length: {row['average_length']} yards\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cooperative-youth",
   "metadata": {},
   "source": [
    "<h4>Question 2</h4>\n",
    "<p>What is the average elevation gain of trails that are in national parks where ‘Mountain,’ ‘Mount’ or any variation of 'Mountain' is in the park's name? </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "saved-placement",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Great Smoky Mountains National Park\n",
      "Average elevation gain: 848.22 yards\n",
      "\n",
      "Guadalupe Mountains National Park\n",
      "Average elevation gain: 735.5 yards\n",
      "\n",
      "Mount Rainier National Park\n",
      "Average elevation gain: 724.99 yards\n",
      "\n",
      "Rocky Mountain National Park\n",
      "Average elevation gain: 590.13 yards\n",
      "\n"
     ]
    }
   ],
   "source": [
    "cur.execute('''SELECT p.park, round(avg(t.elevation_gain), 2) as average_elevation_gain\n",
    "               FROM trails t\n",
    "               JOIN parks p \n",
    "               ON t.park_id = p.id\n",
    "               WHERE p.park LIKE '%Mountain%' or p.park LIKE '%Mount%'\n",
    "               GROUP BY p.park\n",
    "               ORDER BY avg(t.elevation_gain) DESC;''')\n",
    "for row in cur:\n",
    "    print(f\"{row['park']} National Park\\nAverage elevation gain: {row['average_elevation_gain']} yards\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "tough-network",
   "metadata": {},
   "source": [
    "<h4>Question 3</h4>\n",
    "<p>There are three types of trails in the database. How many of each type of trail is there in Yosemite National Park?</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "geological-language",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 135 out and back trails\n",
      "There are 65 loop trails\n",
      "There are 42 point to point trails\n"
     ]
    }
   ],
   "source": [
    "cur.execute('''SELECT r.route_type, count(1) as num_trails\n",
    "               FROM trails t\n",
    "               JOIN route_types r\n",
    "               ON t.route_type_id = r.id\n",
    "               JOIN parks p \n",
    "               ON t.park_id = p.id\n",
    "               WHERE p.park = \"Yosemite\"\n",
    "               GROUP BY r.route_type;''')\n",
    "\n",
    "for row in cur:\n",
    "    print(f\"There are {row['num_trails']} {row['route_type']} trails\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "valued-mechanics",
   "metadata": {},
   "source": [
    "<h4>Question 4</h4>\n",
    "<p>Alaska has the most national parks out of any state. What are the 5 most popular trails in national parks located in Alaska? </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "marked-wallpaper",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Trail: Harding Ice Field Trail\n",
      "Park: Kenai Fjords\n",
      "Popularity Rating: 24.8931\n",
      "\n",
      "Trail: Mount Healy Overlook Trail\n",
      "Park: Denali\n",
      "Popularity Rating: 18.0311\n",
      "\n",
      "Trail: Exit Glacier Trail\n",
      "Park: Kenai Fjords\n",
      "Popularity Rating: 17.7821\n",
      "\n",
      "Trail: Horseshoe Lake Trail\n",
      "Park: Denali\n",
      "Popularity Rating: 16.2674\n",
      "\n",
      "Trail: Triple Lakes Trail\n",
      "Park: Denali\n",
      "Popularity Rating: 12.5935\n",
      "\n"
     ]
    }
   ],
   "source": [
    "cur.execute('''SELECT p.park, t.name, t.popularity\n",
    "               FROM trails t\n",
    "               JOIN parks p \n",
    "               ON t.park_id = p.id\n",
    "               JOIN states s\n",
    "               ON t.state_id = s.id\n",
    "               WHERE s.state_name = 'Alaska'\n",
    "               ORDER BY t.popularity DESC\n",
    "               LIMIT 5;''')\n",
    "for row in cur:\n",
    "    print(f\"Trail: {row['name']}\\nPark: {row['park']}\\nPopularity Rating: {row['popularity']}\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "beautiful-establishment",
   "metadata": {},
   "source": [
    "<h4>Question 5</h4>\n",
    "<p>How many trails are in Yellowstone National Park? </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "smooth-publication",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 228 trails in Yellowstone National Park\n"
     ]
    }
   ],
   "source": [
    "cur.execute('''SELECT count(1) AS number_of_trails\n",
    "               FROM trails t\n",
    "               JOIN parks p \n",
    "               ON t.park_id = p.id\n",
    "               WHERE p.park = 'Yellowstone';''')\n",
    "\n",
    "\n",
    "for row in cur:\n",
    "    print(f\"There are {row['number_of_trails']} trails in Yellowstone National Park\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "surrounded-grace",
   "metadata": {},
   "source": [
    "<h4>Question 6</h4>\n",
    "<p>Return a list of 5 random trails that are in the contiguous U.S. and have less than 500 feet in elevation gain. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "dated-final",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Trail: Black Creek Canal Trail\n",
      "Park: Biscayne National Park\n",
      "Elevation Gain In Yards: 0.9144\n",
      "\n",
      "Trail: Smith Spring Trail\n",
      "Park: Guadalupe Mountains National Park\n",
      "Elevation Gain In Yards: 117.9576\n",
      "\n",
      "Trail: Red Rock Point via Red Point Trail\n",
      "Park: Yellowstone National Park\n",
      "Elevation Gain In Yards: 79.8576\n",
      "\n",
      "Trail: Lehman Caves Visitor Center and Mountain View Nature Trail\n",
      "Park: Great Basin National Park\n",
      "Elevation Gain In Yards: 12.8016\n",
      "\n",
      "Trail: Old Carriage Trail [CLOSED]\n",
      "Park: Cuyahoga Valley National Park\n",
      "Elevation Gain In Yards: 105.7656\n",
      "\n"
     ]
    }
   ],
   "source": [
    "cur.execute('''SELECT p.park, t.name, t.elevation_gain\n",
    "               FROM trails t\n",
    "               JOIN parks p \n",
    "               ON t.park_id = p.id\n",
    "               JOIN states s \n",
    "               ON t.state_id = s.id\n",
    "               WHERE s.state_name NOT IN ('Alaska', 'Hawaii')\n",
    "                     AND t.elevation_gain < (500/3)\n",
    "               ORDER BY RAND()\n",
    "               LIMIT 5;''')\n",
    "\n",
    "\n",
    "for row in cur:\n",
    "    print(f\"Trail: {row['name']}\\nPark: {row['park']} National Park\\nElevation Gain In Yards: {row['elevation_gain']}\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "meaning-companion",
   "metadata": {},
   "source": [
    "<h4>Question 7</h4>\n",
    "<p>Which national park has the most trails with the maximum difficulty rating?  </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "adverse-princeton",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Yosemite National Park has 25 parks with the maximum difficulty rating\n"
     ]
    }
   ],
   "source": [
    "cur.execute('''SELECT p.park, count(1) AS num_parks\n",
    "               FROM trails t \n",
    "               JOIN parks p \n",
    "               ON t.park_id = p.id\n",
    "               WHERE t.difficulty_rating = \n",
    "                (SELECT max(difficulty_rating) FROM trails t)\n",
    "               GROUP BY p.park\n",
    "               ORDER BY 2 DESC\n",
    "               LIMIT 1;''')\n",
    "\n",
    "\n",
    "for row in cur:\n",
    "    print(f\"{row['park']} National Park has {row['num_parks']} parks with the maximum difficulty rating\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "future-strike",
   "metadata": {},
   "source": [
    "<h4>Question 8</h4>\n",
    "<p>What trails have high average visitor ratings (4.5 or higher) but have the lowerst visitor usage rating</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "alleged-shadow",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Waldron Trail\n",
      "Park: Grand Canyon National Park\n",
      "Average Rating: 4.5\n",
      "\n",
      "Coastal Trail: Demartin Section\n",
      "Park: Redwood National Park\n",
      "Average Rating: 4.5\n",
      "\n",
      "Mount Thorofare Ridge Loop\n",
      "Park: Denali National Park\n",
      "Average Rating: 4.5\n",
      "\n",
      "Shuckstack Tower via Appalachian Trail\n",
      "Park: Great Smoky Mountains National Park\n",
      "Average Rating: 4.5\n",
      "\n",
      "Cascade Lake Trail to Observation Peak\n",
      "Park: Yellowstone National Park\n",
      "Average Rating: 4.5\n",
      "\n",
      "California Riding & Hiking Trail\n",
      "Park: Joshua Tree National Park\n",
      "Average Rating: 4.5\n",
      "\n",
      "Redwood Canyon to Redwood Creek\n",
      "Park: Kings Canyon National Park\n",
      "Average Rating: 5.0\n",
      "\n",
      "North Grove Loop Trail\n",
      "Park: Kings Canyon National Park\n",
      "Average Rating: 4.5\n",
      "\n",
      "Beach Trail 2\n",
      "Park: Olympic National Park\n",
      "Average Rating: 5.0\n",
      "\n",
      "Goler Wash and Mengal Pass Route\n",
      "Park: Death Valley National Park\n",
      "Average Rating: 5.0\n",
      "\n"
     ]
    }
   ],
   "source": [
    "cur.execute('''SELECT t.name, p.park, t.avg_rating, t.visitor_usage\n",
    "                FROM trails t \n",
    "                JOIN parks p\n",
    "                ON t.park_id = p.id\n",
    "                WHERE t.avg_rating >= 4.5 \n",
    "                      AND t.visitor_usage = \n",
    "                      (SELECT min(visitor_usage) FROM trails)\n",
    "                ORDER BY rand()\n",
    "                LIMIT 10;''')\n",
    "\n",
    "for row in cur:\n",
    "    print(f'''{row['name']}\\nPark: {row['park']} National Park\\nAverage Rating: {row['avg_rating']}\\n''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "logical-adams",
   "metadata": {},
   "source": [
    "<h4>Question 9</h4>\n",
    "<p>Give me a list of trails and the parks they're located in that have high difficulty ratings (greater than or equal to 5), but are frequently used by visitors.</p>\n",
    "\n",
    "<p><b>Note:</b> there is some ambiguity in how answers can define \"frequently used.\" This sample solution considers frequent usage to mean that the trail has the highest visitor usage rating possible, but any interpretation that's logical and well-explained should receive full credit.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "technical-summit",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Merced Lake High Sierra Camp Trail in Yosemite National Park\n",
      "Visitor Usage Rating (1-4): 4.0\n",
      "Difficulty Rating: 5\n",
      "\n",
      "Alum Cave Trail to Mount LeConte in Great Smoky Mountains National Park\n",
      "Visitor Usage Rating (1-4): 4.0\n",
      "Difficulty Rating: 5\n",
      "\n",
      "Chimney Tops Trail in Great Smoky Mountains National Park\n",
      "Visitor Usage Rating (1-4): 4.0\n",
      "Difficulty Rating: 5\n",
      "\n",
      "Angels Landing Trail in Zion National Park\n",
      "Visitor Usage Rating (1-4): 4.0\n",
      "Difficulty Rating: 5\n",
      "\n",
      "Old Rag Mountain Loop in Shenandoah National Park\n",
      "Visitor Usage Rating (1-4): 4.0\n",
      "Difficulty Rating: 5\n",
      "\n",
      "Lena Lake Trail to Upper Lena Lake Trail in Olympic National Park\n",
      "Visitor Usage Rating (1-4): 4.0\n",
      "Difficulty Rating: 5\n",
      "\n"
     ]
    }
   ],
   "source": [
    "cur.execute('''SELECT t.name, p.park, t.difficulty_rating, t.visitor_usage\n",
    "               FROM trails t \n",
    "               JOIN parks p \n",
    "               ON t.park_id = p.id\n",
    "               WHERE t.difficulty_rating >= 5\n",
    "               AND t.visitor_usage =  \n",
    "                   (SELECT max(visitor_usage) FROM trails)''')\n",
    "\n",
    "for row in cur:\n",
    "    print(f\"{row['name']} in {row['park']} National Park\\nVisitor Usage Rating (1-4): {row['visitor_usage']}\\nDifficulty Rating: {row['difficulty_rating']}\\n\")\n",
    "    \n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "split-thunder",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "entitled-automation",
   "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
}