geospatial_analysis/04-manipulating-geospatial-data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**[Geospatial Analysis Home Page](https://www.kaggle.com/learn/geospatial-analysis)**\n",
    "\n",
    "---\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "\n",
    "You are a Starbucks big data analyst ([that’s a real job!](https://www.forbes.com/sites/bernardmarr/2018/05/28/starbucks-using-big-data-analytics-and-artificial-intelligence-to-boost-performance/#130c7d765cdc)) looking to find the next store into a [Starbucks Reserve Roastery](https://www.businessinsider.com/starbucks-reserve-roastery-compared-regular-starbucks-2018-12#also-on-the-first-floor-was-the-main-coffee-bar-five-hourglass-like-units-hold-the-freshly-roasted-coffee-beans-that-are-used-in-each-order-the-selection-rotates-seasonally-5).  These roasteries are much larger than a typical Starbucks store and have several additional features, including various food and wine options, along with upscale lounge areas.  You'll investigate the demographics of various counties in the state of California, to determine potentially suitable locations.\n",
    "\n",
    "<center>\n",
    "<img src=\"https://i.imgur.com/BIyE6kR.png\" width=\"450\"><br/><br/>\n",
    "</center>\n",
    "\n",
    "Before you get started, run the code cell below to set everything up."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import math\n",
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "#from geopandas.tools import geocode            # What you'd normally run\n",
    "from learntools.geospatial.tools import geocode # Just for this exercise\n",
    "\n",
    "import folium \n",
    "from folium import Marker\n",
    "from folium.plugins import MarkerCluster\n",
    "\n",
    "from learntools.core import binder\n",
    "binder.bind(globals())\n",
    "from learntools.geospatial.ex4 import *"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You'll use the `embed_map()` function from the previous exercise to visualize your maps."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def embed_map(m, file_name):\n",
    "    from IPython.display import IFrame\n",
    "    m.save(file_name)\n",
    "    return IFrame(file_name, width='100%', height='500px')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercises\n",
    "\n",
    "### 1) Geocode the missing locations.\n",
    "\n",
    "Run the next code cell to create a DataFrame `starbucks` containing Starbucks locations in the state of California."
   ]
  },
  {
   "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>Store Number</th>\n",
       "      <th>Store Name</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>10429-100710</td>\n",
       "      <td>Palmdale &amp; Hwy 395</td>\n",
       "      <td>14136 US Hwy 395 Adelanto CA</td>\n",
       "      <td>Adelanto</td>\n",
       "      <td>-117.40</td>\n",
       "      <td>34.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>635-352</td>\n",
       "      <td>Kanan &amp; Thousand Oaks</td>\n",
       "      <td>5827 Kanan Road Agoura CA</td>\n",
       "      <td>Agoura</td>\n",
       "      <td>-118.76</td>\n",
       "      <td>34.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>74510-27669</td>\n",
       "      <td>Vons-Agoura Hills #2001</td>\n",
       "      <td>5671 Kanan Rd. Agoura Hills CA</td>\n",
       "      <td>Agoura Hills</td>\n",
       "      <td>-118.76</td>\n",
       "      <td>34.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>29839-255026</td>\n",
       "      <td>Target Anaheim T-0677</td>\n",
       "      <td>8148 E SANTA ANA CANYON ROAD AHAHEIM CA</td>\n",
       "      <td>AHAHEIM</td>\n",
       "      <td>-117.75</td>\n",
       "      <td>33.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>23463-230284</td>\n",
       "      <td>Safeway - Alameda 3281</td>\n",
       "      <td>2600 5th Street Alameda CA</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>-122.28</td>\n",
       "      <td>37.79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Store Number               Store Name  \\\n",
       "0  10429-100710       Palmdale & Hwy 395   \n",
       "1       635-352    Kanan & Thousand Oaks   \n",
       "2   74510-27669  Vons-Agoura Hills #2001   \n",
       "3  29839-255026    Target Anaheim T-0677   \n",
       "4  23463-230284   Safeway - Alameda 3281   \n",
       "\n",
       "                                   Address          City  Longitude  Latitude  \n",
       "0             14136 US Hwy 395 Adelanto CA      Adelanto    -117.40     34.51  \n",
       "1                5827 Kanan Road Agoura CA        Agoura    -118.76     34.16  \n",
       "2           5671 Kanan Rd. Agoura Hills CA  Agoura Hills    -118.76     34.15  \n",
       "3  8148 E SANTA ANA CANYON ROAD AHAHEIM CA       AHAHEIM    -117.75     33.87  \n",
       "4               2600 5th Street Alameda CA       Alameda    -122.28     37.79  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load and preview Starbucks locations in California\n",
    "starbucks = pd.read_csv(\"../input/geospatial-learn-course-data/starbucks_locations.csv\")\n",
    "starbucks.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Most of the stores have known (latitude, longitude) locations.  But, all of the locations in the city of Berkeley are missing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Store Number    0\n",
      "Store Name      0\n",
      "Address         0\n",
      "City            0\n",
      "Longitude       5\n",
      "Latitude        5\n",
      "dtype: int64\n"
     ]
    },
    {
     "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>Store Number</th>\n",
       "      <th>Store Name</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>153</td>\n",
       "      <td>5406-945</td>\n",
       "      <td>2224 Shattuck - Berkeley</td>\n",
       "      <td>2224 Shattuck Avenue Berkeley CA</td>\n",
       "      <td>Berkeley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>154</td>\n",
       "      <td>570-512</td>\n",
       "      <td>Solano Ave</td>\n",
       "      <td>1799 Solano Avenue Berkeley CA</td>\n",
       "      <td>Berkeley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>155</td>\n",
       "      <td>17877-164526</td>\n",
       "      <td>Safeway - Berkeley #691</td>\n",
       "      <td>1444 Shattuck Place Berkeley CA</td>\n",
       "      <td>Berkeley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>156</td>\n",
       "      <td>19864-202264</td>\n",
       "      <td>Telegraph &amp; Ashby</td>\n",
       "      <td>3001 Telegraph Avenue Berkeley CA</td>\n",
       "      <td>Berkeley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>157</td>\n",
       "      <td>9217-9253</td>\n",
       "      <td>2128 Oxford St.</td>\n",
       "      <td>2128 Oxford Street Berkeley CA</td>\n",
       "      <td>Berkeley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Store Number                Store Name  \\\n",
       "153      5406-945  2224 Shattuck - Berkeley   \n",
       "154       570-512                Solano Ave   \n",
       "155  17877-164526   Safeway - Berkeley #691   \n",
       "156  19864-202264         Telegraph & Ashby   \n",
       "157     9217-9253           2128 Oxford St.   \n",
       "\n",
       "                               Address      City  Longitude  Latitude  \n",
       "153   2224 Shattuck Avenue Berkeley CA  Berkeley        NaN       NaN  \n",
       "154     1799 Solano Avenue Berkeley CA  Berkeley        NaN       NaN  \n",
       "155    1444 Shattuck Place Berkeley CA  Berkeley        NaN       NaN  \n",
       "156  3001 Telegraph Avenue Berkeley CA  Berkeley        NaN       NaN  \n",
       "157     2128 Oxford Street Berkeley CA  Berkeley        NaN       NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How many rows in each column have missing values?\n",
    "print(starbucks.isnull().sum())\n",
    "\n",
    "# View rows with missing locations\n",
    "rows_with_missing = starbucks[starbucks[\"City\"]==\"Berkeley\"]\n",
    "rows_with_missing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use the code cell below to fill in these values with the OpenStreetMap Nominatim geocoder.\n",
    "\n",
    "Note that in the tutorial, we used `geocode()` (from `geopandas.tools`) to geocode values, and this is what you can use in your own projects outside of this micro-course.  \n",
    "\n",
    "In this exercise, you will use a slightly different function `geocode()` (from `learntools.geospatial.tools`).  This function was imported at the top of the notebook and works identically to the function from GeoPandas.\n",
    "\n",
    "So, in other words, as long as: \n",
    "- you don't change the import statements at the top of the notebook, and \n",
    "- you call the geocoding function as `geocode()` in the code cell below, \n",
    "\n",
    "your code will work as intended!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.2, \"interactionType\": 1, \"questionType\": 2, \"learnTutorialId\": 277, \"questionId\": \"1_Q1\", \"learnToolsVersion\": \"0.3.2\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct</span>"
      ],
      "text/plain": [
       "Correct"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Your code here\n",
    "def my_geocoder(row):\n",
    "    try:\n",
    "        point = geocode(row, provider='nominatim').geometry.iloc[0]\n",
    "        return pd.Series({'Latitude': point.y, 'Longitude': point.x})\n",
    "    except:\n",
    "        return None\n",
    "\n",
    "missing_locations = rows_with_missing.apply(lambda x: my_geocoder(x['Address']), axis=1)\n",
    "starbucks.update(missing_locations)\n",
    "\n",
    "# Check your answer\n",
    "q_1.check()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Line below will give you solution code\n",
    "#q_1.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2) View Berkeley locations.\n",
    "\n",
    "Let's take a look at the locations you just found.  Visualize the (latitude, longitude) locations in Berkeley in the OpenStreetMap style. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"500px\"\n",
       "            src=\"q_2.html\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x7f2f09d47278>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a base map\n",
    "m_2 = folium.Map(location=[37.88,-122.26], zoom_start=13)\n",
    "\n",
    "# Your code here: Add a marker for each Berkeley location\n",
    "berkeley_locations = starbucks[starbucks[\"City\"]==\"Berkeley\"]\n",
    "for idx, row in berkeley_locations.iterrows():\n",
    "    Marker([row['Latitude'], row['Longitude']], popup=row['Store Name']).add_to(m_2)\n",
    "\n",
    "# Uncomment to see a hint\n",
    "#q_2.a.hint()\n",
    "\n",
    "# Show the map\n",
    "embed_map(m_2, 'q_2.html')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.2, \"interactionType\": 1, \"questionType\": 2, \"learnTutorialId\": 277, \"questionId\": \"2.1_Q2P\", \"learnToolsVersion\": \"0.3.2\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Thank you for creating a map!</span>"
      ],
      "text/plain": [
       "Thank you for creating a map!"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Get credit for your work after you have created a map\n",
    "q_2.a.check()\n",
    "\n",
    "# Uncomment to see our solution (your code may look different!)\n",
    "#q_2.a.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Considering only the five locations in Berkeley, how many of the (latitude, longitude) locations seem potentially correct (are located in the correct city)?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 3, \"questionType\": 4, \"learnTutorialId\": 277, \"questionId\": \"2.2_Q2T\", \"learnToolsVersion\": \"0.3.2\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc99\">Solution:</span> All five locations appear to be correct!"
      ],
      "text/plain": [
       "Solution: All five locations appear to be correct!"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# View the solution (Run this code cell to receive credit!)\n",
    "q_2.b.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3) Consolidate your data.\n",
    "\n",
    "Run the code below to load a GeoDataFrame `CA_counties` containing the name, area (in square kilometers), and a unique id (in the \"GEOID\" column) for each county in the state of California.  The \"geometry\" column contains a polygon with county boundaries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>GEOID</th>\n",
       "      <th>name</th>\n",
       "      <th>area_sqkm</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>6091</td>\n",
       "      <td>Sierra County</td>\n",
       "      <td>2491.995494</td>\n",
       "      <td>POLYGON ((-120.6555981820125 39.69356523820564...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>6067</td>\n",
       "      <td>Sacramento County</td>\n",
       "      <td>2575.258262</td>\n",
       "      <td>POLYGON ((-121.1885841398944 38.71431311442083...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>6083</td>\n",
       "      <td>Santa Barbara County</td>\n",
       "      <td>9813.817958</td>\n",
       "      <td>(POLYGON ((-120.5819095703916 34.0985617276066...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>6009</td>\n",
       "      <td>Calaveras County</td>\n",
       "      <td>2685.626726</td>\n",
       "      <td>POLYGON ((-120.6309460064869 38.34110512435024...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>6111</td>\n",
       "      <td>Ventura County</td>\n",
       "      <td>5719.321379</td>\n",
       "      <td>(POLYGON ((-119.6363143545649 33.2730446943631...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   GEOID                  name    area_sqkm  \\\n",
       "0   6091         Sierra County  2491.995494   \n",
       "1   6067     Sacramento County  2575.258262   \n",
       "2   6083  Santa Barbara County  9813.817958   \n",
       "3   6009      Calaveras County  2685.626726   \n",
       "4   6111        Ventura County  5719.321379   \n",
       "\n",
       "                                            geometry  \n",
       "0  POLYGON ((-120.6555981820125 39.69356523820564...  \n",
       "1  POLYGON ((-121.1885841398944 38.71431311442083...  \n",
       "2  (POLYGON ((-120.5819095703916 34.0985617276066...  \n",
       "3  POLYGON ((-120.6309460064869 38.34110512435024...  \n",
       "4  (POLYGON ((-119.6363143545649 33.2730446943631...  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CA_counties = gpd.read_file(\"../input/geospatial-learn-course-data/CA_county_boundaries/CA_county_boundaries/CA_county_boundaries.shp\")\n",
    "CA_counties.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we create three DataFrames:\n",
    "- `CA_pop` contains an estimate of the population of each county.\n",
    "- `CA_high_earners` contains the number of households with an income of at least $150,000 per year.\n",
    "- `CA_median_age` contains the median age for each county."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "CA_pop = pd.read_csv(\"../input/geospatial-learn-course-data/CA_county_population.csv\", index_col=\"GEOID\")\n",
    "CA_high_earners = pd.read_csv(\"../input/geospatial-learn-course-data/CA_county_high_earners.csv\", index_col=\"GEOID\")\n",
    "CA_median_age = pd.read_csv(\"../input/geospatial-learn-course-data/CA_county_median_age.csv\", index_col=\"GEOID\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use the next code cell to join the `CA_counties` GeoDataFrame with `CA_pop`, `CA_high_earners`, and `CA_median_age`.\n",
    "\n",
    "Name the resultant GeoDataFrame `CA_stats`, and make sure it has 8 columns: \"GEOID\", \"name\", \"area_sqkm\", \"geometry\", \"population\", \"high_earners\", and \"median_age\".  Also, make sure the CRS is set to `{'init': 'epsg:4326'}`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.2, \"interactionType\": 1, \"questionType\": 2, \"learnTutorialId\": 277, \"questionId\": \"3_Q3\", \"learnToolsVersion\": \"0.3.2\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct</span>"
      ],
      "text/plain": [
       "Correct"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Your code here\n",
    "CA_stats = CA_counties.merge(CA_pop.join([CA_high_earners, CA_median_age]), on='GEOID')\n",
    "\n",
    "# Check your answer\n",
    "q_3.check()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lines below will give you a hint or solution code\n",
    "#q_3.hint()\n",
    "#q_3.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have all of the data in one place, it's much easier to calculate statistics that use a combination of columns.  Run the next code cell to create a \"density\" column with the population density."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "CA_stats[\"density\"] = CA_stats[\"population\"] / CA_stats[\"area_sqkm\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4) Which counties look promising?\n",
    "\n",
    "Collapsing all of the information into a single GeoDataFrame also makes it much easier to select counties that meet specific criteria.\n",
    "\n",
    "Use the next code cell to create a GeoDataFrame `sel_counties` that contains a subset of the rows (and all of the columns) from the `CA_stats` GeoDataFrame.  In particular, you should select counties where:\n",
    "- there are at least 100,000 households making \\$150,000 per year,\n",
    "- the median age is less than 38.5, and\n",
    "- the density of inhabitants is at least 285 (per square kilometer).\n",
    "\n",
    "Additionally, selected counties should satisfy at least one of the following criteria:\n",
    "- there are at least 500,000 households making \\$150,000 per year,\n",
    "- the median age is less than 35.5, or\n",
    "- the density of inhabitants is at least 1400 (per square kilometer)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.2, \"interactionType\": 1, \"questionType\": 2, \"learnTutorialId\": 277, \"questionId\": \"4_Q4\", \"learnToolsVersion\": \"0.3.2\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct</span>"
      ],
      "text/plain": [
       "Correct"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Your code here\n",
    "sel_counties = CA_stats[(CA_stats['high_earners']>=100000)\n",
    "                        &(CA_stats['median_age']<38.5)\n",
    "                        &(CA_stats['density']>=285)]\n",
    "sel_counties = sel_counties[(sel_counties['high_earners']>=500000)\n",
    "                            |(sel_counties['median_age']<35.5)\n",
    "                            |(sel_counties['density']>=1400)]\n",
    "\n",
    "# Check your answer\n",
    "q_4.check()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lines below will give you a hint or solution code\n",
    "#q_4.hint()\n",
    "#q_4.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5) How many stores did you identify?\n",
    "\n",
    "When looking for the next Starbucks Reserve Roastery location, you'd like to consider all of the stores within the counties that you selected.  So, how many stores are within the selected counties?\n",
    "\n",
    "To prepare to answer this question, run the next code cell to create a GeoDataFrame `starbucks_gdf` with all of the starbucks locations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "starbucks_gdf = gpd.GeoDataFrame(starbucks, geometry=gpd.points_from_xy(starbucks.Longitude, starbucks.Latitude))\n",
    "starbucks_gdf.crs = {'init': 'epsg:4326'}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, how many stores are in the counties you selected?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.2, \"interactionType\": 1, \"questionType\": 2, \"learnTutorialId\": 277, \"questionId\": \"5_Q5\", \"learnToolsVersion\": \"0.3.2\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct</span>"
      ],
      "text/plain": [
       "Correct"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Fill in your answer\n",
    "num_stores = len(gpd.sjoin(starbucks_gdf, sel_counties))\n",
    "\n",
    "# Check your answer\n",
    "q_5.check()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lines below will give you a hint or solution code\n",
    "#q_5.hint()\n",
    "#q_5.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6) Visualize the store locations.\n",
    "\n",
    "Create a map that shows the locations of the stores that you identified in the previous question."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"500px\"\n",
       "            src=\"q_6.html\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x7f2f0992afd0>"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a base map\n",
    "m_6 = folium.Map(location=[37,-120], zoom_start=6)\n",
    "\n",
    "# Your code here: show selected store locations\n",
    "sel_stores = gpd.sjoin(starbucks_gdf, sel_counties)\n",
    "mc = MarkerCluster()\n",
    "for idx, row in sel_stores.iterrows():\n",
    "    mc.add_child(folium.Marker([row['Latitude'], row['Longitude']]))\n",
    "m_6.add_child(mc)\n",
    "    #Marker([row['Latitude'], row['Longitude']], popup=row['Store Name']).add_to(m_6)\n",
    "\n",
    "# Uncomment to see a hint\n",
    "#q_6.hint()\n",
    "\n",
    "# Show the map\n",
    "embed_map(m_6, 'q_6.html')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.2, \"interactionType\": 1, \"questionType\": 2, \"learnTutorialId\": 277, \"questionId\": \"6_Q6\", \"learnToolsVersion\": \"0.3.2\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Thank you for creating a map!</span>"
      ],
      "text/plain": [
       "Thank you for creating a map!"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Get credit for your work after you have created a map\n",
    "q_6.check()\n",
    "\n",
    "# Uncomment to see our solution (your code may look different!)\n",
    "#q_6.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Keep going\n",
    "\n",
    "Learn about how **[proximity analysis](https://www.kaggle.com/alexisbcook/proximity-analysis)** can help you to understand the relationships between points on a map."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "**[Geospatial Analysis Home Page](https://www.kaggle.com/learn/geospatial-analysis)**\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "*Have questions or comments? Visit the [Learn Discussion forum](https://www.kaggle.com/learn-forum) to chat with other Learners.*"
   ]
  }
 ],
 "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.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}