acquire.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Acquisition\n",
    "\n",
    "In this project my goal is to create an interactive tool that can be used to find a user's top location recommendations for where to move/live in the continental United States, given a user's preferences. I was inspired to create this because I am currently relocating and wished there was an aggregated source to visualize my preferences for things that matter to me when choosing a state and city to move to and live and thrive in."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# First source: The Municipal Equality Index by HRC (2020)\n",
    "\n",
    "https://www.hrc.org/resources/municipalities"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I'll try to use web scraping on the database results from a blank query on their webpage."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "from requests import get\n",
    "import re\n",
    "from bs4 import BeautifulSoup\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://www.hrc.org/resources/municipalities'\n",
    "headers = {'User-Agent': 'Kwame'} # Some websites don't accept the pyhon-requests default user-agent\n",
    "response = get(url, headers=headers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<!DOCTYPE html>\n",
      "<html dir=\"ltr\" lang=\"en-US\" class=\"no-js no-touch\">\n",
      "<head>\n",
      "  <meta charset=\"utf-8\">\n",
      "  <meta http-equiv=\"x-ua-compatible\" content=\"ie=edge\">\n",
      "  <meta name=\"viewport\" content=\"width=device-width, initial-scale=1, shrink-to-fit=no\">\n",
      "\n",
      "  <link rel=\"home\" href=\"https://www.hrc.org/\">\n",
      "  <link rel=\"shortcut icon\" href=\"/favicon.ico\">\n",
      "  <link rel=\"icon\" sizes=\"16x16 32x32 64x64\" href=\"/favi\n"
     ]
    }
   ],
   "source": [
    "print(response.text[:400])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make a soup variable holding the response content\n",
    "soup = BeautifulSoup(response.content, 'html.parser')\n",
    "\n",
    "# Look at the website and identify parts of results\n",
    "title = soup.find('h2').text\n",
    "content = soup.find('p').text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\n            Cookies in use\\n          '"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "title"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Where does your city stand in the movement for equality?'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<h2 class=\"heading-24 mb-12\">\n",
       "             Cookies in use\n",
       "           </h2>,\n",
       " <h2 class=\"type-eyebrow text-theme-headline-default-current\">\n",
       "               Related Resources\n",
       "             </h2>,\n",
       " <h2 class=\"heading-48\">\n",
       "             Love conquers hate.\n",
       "           </h2>,\n",
       " <h2 class=\"font-bold leading-squeeze text-32\">\n",
       "             Wear your pride this year.\n",
       "           </h2>,\n",
       " <h2 class=\"heading-48\">\n",
       "                 Love conquers hate.\n",
       "               </h2>,\n",
       " <h2 class=\"font-bold leading-squeeze text-32\">\n",
       "                 Wear your pride this year.\n",
       "               </h2>,\n",
       " <h2 class=\"heading-20 mb-32\">Join millions of supporters by signing up for the HRC newsletter.</h2>,\n",
       " <h2 class=\"heading-60 mb-32\">You are leaving HRC.org</h2>]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "soup.find_all('h2')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<span class=\"align-middle\">Accept</span>,\n",
       " <span class=\"align-middle\">Shop</span>,\n",
       " <span class=\"align-middle\">Donate</span>,\n",
       " <span class=\"align-middle\">Shop</span>,\n",
       " <span class=\"align-middle\">Donate</span>,\n",
       " <span class=\"align-middle\">View All</span>,\n",
       " <span class=\"align-middle\">Donate Today</span>,\n",
       " <span class=\"align-middle\">Shop Now</span>,\n",
       " <span class=\"align-middle\">Donate Today</span>,\n",
       " <span class=\"align-middle\">Shop Now</span>,\n",
       " <span class=\"align-middle\">Sign Me Up</span>]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "soup.find_all(\"span\", class_=\"align-middle\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "soup.find_all('$0')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "soup.find_all('article', {'data-label': 'component-score-card-index'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Congressional Scorecard\n",
      "Congressional Scorecard\n",
      "Congressional Scorecard\n",
      "Congressional Scorecard\n"
     ]
    }
   ],
   "source": [
    "for item in soup:\n",
    "    print(soup.div.article['aria-label'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "soup.find_all('/html/body/div[1]/main/div/section/div/article[1]/div[1]/div[1]/h2/a/span[1]')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I think there is javascript in the mix, so I'm going to try scraping using Selenium."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.hrc.org/resources/municipalities/search?sort=score-desc\n"
     ]
    }
   ],
   "source": [
    "# import libraries\n",
    "import urllib.request\n",
    "from selenium import webdriver\n",
    "import time\n",
    "\n",
    "# specify the url\n",
    "urlpage = 'https://www.hrc.org/resources/municipalities/search?sort=score-desc' \n",
    "print(urlpage)\n",
    "# run firefox webdriver from executable path of your choice\n",
    "driver = webdriver.Firefox(executable_path = '/Users/a666/codeup-data-science/flask-project/geckodriver')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.hrc.org/resources/municipalities/search?sort=score-desc\n"
     ]
    }
   ],
   "source": [
    "# import libraries\n",
    "import urllib.request\n",
    "from selenium import webdriver\n",
    "from selenium.webdriver.firefox.options import Options\n",
    "import time\n",
    "\n",
    "# specify the url\n",
    "urlpage = 'https://www.hrc.org/resources/municipalities/search?sort=score-desc' \n",
    "print(urlpage)\n",
    "# run firefox webdriver from executable path of your choice\n",
    "# i'm using headless mode with geckodriver\n",
    "#options = Options()\n",
    "#options.headless = True\n",
    "driver = webdriver.Firefox(executable_path = '/Users/a666/codeup-data-science/flask-project/geckodriver')\n",
    "#options=options, "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get web page\n",
    "driver.get(urlpage)\n",
    "# execute script to scroll down the page\n",
    "driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight);var lenOfPage=document.body.scrollHeight;return lenOfPage;\")\n",
    "# sleep for 30s\n",
    "time.sleep(30)\n",
    "# driver.quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of results (cities): 20\n",
      "Number of results (states): 10\n"
     ]
    }
   ],
   "source": [
    "results_cities = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/div/h2/a/span\")\n",
    "results_states = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/p\")\n",
    "print('Number of results (cities):', len(results_cities))\n",
    "print('Number of results (states):', len(results_states))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create empty df and arrays to store data\n",
    "df = pd.DataFrame(columns = ['city', 'state'])\n",
    "city_list = []\n",
    "state_list = []\n",
    "# loop over results and store in the lists\n",
    "for result_city in results_cities:\n",
    "    if result_city.text != \"\":\n",
    "        city_list.append(result_city.text)\n",
    "for result_state in results_states:\n",
    "    state_list.append(result_state.text)\n",
    "# add lists to df\n",
    "df['city'] = pd.Series(city_list)\n",
    "df['state'] = pd.Series(state_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I'll make this a function so I can easily use it later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_locations(target_page_url):\n",
    "    # print the url\n",
    "    print(target_page_url)\n",
    "    # run firefox webdriver from executable path of your choice\n",
    "    # i'm using headless mode with geckodriver\n",
    "    #options = Options()\n",
    "    #options.headless = True\n",
    "    driver = webdriver.Firefox(executable_path = '/Users/a666/codeup-data-science/flask-project/geckodriver')\n",
    "    #options=options, \n",
    "    \n",
    "    # get web page\n",
    "    driver.get(target_page_url)\n",
    "    # execute script to scroll down the page\n",
    "    driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight);var lenOfPage=document.body.scrollHeight;return lenOfPage;\")\n",
    "    # sleep for 30s\n",
    "    time.sleep(30)\n",
    "    driver.quit()\n",
    "    \n",
    "    results_cities = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/div/h2/a/span\")\n",
    "    results_states = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/p\")\n",
    "    print('Number of results (cities):', len(results_cities))\n",
    "    print('Number of results (states):', len(results_states))\n",
    "\n",
    "    # create empty df and arrays to store data from this page\n",
    "    df = pd.DataFrame(columns = ['city', 'state'])\n",
    "    city_list = []\n",
    "    state_list = []\n",
    "    # loop over this page's results and store in the lists\n",
    "    for result_city in results_cities:\n",
    "        if result_city.text != \"\":\n",
    "            city_list.append(result_city.text)\n",
    "    for result_state in results_states:\n",
    "        state_list.append(result_state.text)\n",
    "    # add this page's city and states to df\n",
    "    df['city'] = pd.Series(city_list)\n",
    "    df['state'] = pd.Series(state_list)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>city</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albany</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alexandria</td>\n",
       "      <td>Virginia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Allentown</td>\n",
       "      <td>Pennsylvania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ann Arbor</td>\n",
       "      <td>Michigan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Arlington</td>\n",
       "      <td>Massachusetts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Arlington</td>\n",
       "      <td>Virginia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Atlanta</td>\n",
       "      <td>Georgia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Austin</td>\n",
       "      <td>Texas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Baltimore</td>\n",
       "      <td>Maryland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Bellevue</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         city          state\n",
       "0      Albany       New York\n",
       "1  Alexandria       Virginia\n",
       "2   Allentown   Pennsylvania\n",
       "3   Ann Arbor       Michigan\n",
       "4   Arlington  Massachusetts\n",
       "5   Arlington       Virginia\n",
       "6     Atlanta        Georgia\n",
       "7      Austin          Texas\n",
       "8   Baltimore       Maryland\n",
       "9    Bellevue     Washington"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that I have retrieved the first page successfully, it is time to go through the rest of the pages.\n",
    "\n",
    "https://www.hrc.org/resources/municipalities/search/p2?sort=score-desc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "#start_url = 'https://www.hrc.org/resources/municipalities/search/p'\n",
    "#page_nums = [2, 3, 4, 5, 6, 7, 8, 9, 10]\n",
    "#end_url = '?sort=score-desc'\n",
    "#locations = pd.DataFrame(['city', 'state'])\n",
    "\n",
    "#for page in page_nums:\n",
    "#    target_page_url = start_url + str(page) + end_url\n",
    "#    locations = pd.concat(locations, get_locations(target_page_url))\n",
    "    \n",
    "#locations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I got an error, which I think is caused by the need for Selenium to open a browser window for each page. I am going to try to get around this using headless mode with geckodriver."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I actually don't have the Selenium knowledge to solve this loop right now, so I am just going to iterate through the (10) pages by hand."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.hrc.org/resources/municipalities/search/p2?sort=score-desc\n",
      "https://www.hrc.org/resources/municipalities/search/p3?sort=score-desc\n",
      "https://www.hrc.org/resources/municipalities/search/p4?sort=score-desc\n",
      "https://www.hrc.org/resources/municipalities/search/p5?sort=score-desc\n",
      "https://www.hrc.org/resources/municipalities/search/p6?sort=score-desc\n",
      "https://www.hrc.org/resources/municipalities/search/p7?sort=score-desc\n",
      "https://www.hrc.org/resources/municipalities/search/p8?sort=score-desc\n",
      "https://www.hrc.org/resources/municipalities/search/p9?sort=score-desc\n",
      "https://www.hrc.org/resources/municipalities/search/p10?sort=score-desc\n"
     ]
    }
   ],
   "source": [
    "start_url = 'https://www.hrc.org/resources/municipalities/search/p'\n",
    "page_nums = [2, 3, 4, 5, 6, 7, 8, 9, 10]\n",
    "end_url = '?sort=score-desc'\n",
    "locations = pd.DataFrame(['city', 'state'])\n",
    "\n",
    "for page in page_nums:\n",
    "    target_page_url = start_url + str(page) + end_url\n",
    "    print(target_page_url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>city</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albany</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alexandria</td>\n",
       "      <td>Virginia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Allentown</td>\n",
       "      <td>Pennsylvania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ann Arbor</td>\n",
       "      <td>Michigan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Arlington</td>\n",
       "      <td>Massachusetts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Arlington</td>\n",
       "      <td>Virginia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Atlanta</td>\n",
       "      <td>Georgia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Austin</td>\n",
       "      <td>Texas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Baltimore</td>\n",
       "      <td>Maryland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Bellevue</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         city          state\n",
       "0      Albany       New York\n",
       "1  Alexandria       Virginia\n",
       "2   Allentown   Pennsylvania\n",
       "3   Ann Arbor       Michigan\n",
       "4   Arlington  Massachusetts\n",
       "5   Arlington       Virginia\n",
       "6     Atlanta        Georgia\n",
       "7      Austin          Texas\n",
       "8   Baltimore       Maryland\n",
       "9    Bellevue     Washington"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = df\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.hrc.org/resources/municipalities/search/p2?sort=score-desc\n"
     ]
    }
   ],
   "source": [
    "#df2 = get_locations('https://www.hrc.org/resources/municipalities/search/p2?sort=score-desc')\n",
    "\n",
    "# specify the url\n",
    "urlpage = 'https://www.hrc.org/resources/municipalities/search/p2?sort=score-desc'\n",
    "print(urlpage)\n",
    "# run firefox webdriver from executable path of your choice\n",
    "driver = webdriver.Firefox(executable_path = '/Users/a666/codeup-data-science/flask-project/geckodriver')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of results (cities): 20\n",
      "Number of results (states): 10\n"
     ]
    }
   ],
   "source": [
    "# get web page\n",
    "driver.get(urlpage)\n",
    "# execute script to scroll down the page\n",
    "driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight);var lenOfPage=document.body.scrollHeight;return lenOfPage;\")\n",
    "# sleep for 30s\n",
    "time.sleep(30)\n",
    "\n",
    "results_cities = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/div/h2/a/span\")\n",
    "results_states = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/p\")\n",
    "print('Number of results (cities):', len(results_cities))\n",
    "print('Number of results (states):', len(results_states))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>city</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Berkeley</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Birmingham</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bloomington</td>\n",
       "      <td>Indiana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Boston</td>\n",
       "      <td>Massachusetts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Boulder</td>\n",
       "      <td>Colorado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Brookings</td>\n",
       "      <td>South Dakota</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Cambridge</td>\n",
       "      <td>Massachusetts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Cathedral City</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Cedar Rapids</td>\n",
       "      <td>Iowa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Chicago</td>\n",
       "      <td>Illinois</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             city          state\n",
       "0        Berkeley     California\n",
       "1      Birmingham        Alabama\n",
       "2     Bloomington        Indiana\n",
       "3          Boston  Massachusetts\n",
       "4         Boulder       Colorado\n",
       "5       Brookings   South Dakota\n",
       "6       Cambridge  Massachusetts\n",
       "7  Cathedral City     California\n",
       "8    Cedar Rapids           Iowa\n",
       "9         Chicago       Illinois"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create empty df and arrays to store data\n",
    "df2 = pd.DataFrame(columns = ['city', 'state'])\n",
    "city_list = []\n",
    "state_list = []\n",
    "# loop over results and store in the lists\n",
    "for result_city in results_cities:\n",
    "    if result_city.text != \"\":\n",
    "        city_list.append(result_city.text)\n",
    "for result_state in results_states:\n",
    "    state_list.append(result_state.text)\n",
    "# add lists to df\n",
    "df2['city'] = pd.Series(city_list)\n",
    "df2['state'] = pd.Series(state_list)\n",
    "\n",
    "driver.quit()\n",
    "\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.hrc.org/resources/municipalities/search/p3?sort=score-desc\n"
     ]
    }
   ],
   "source": [
    "# specify the url\n",
    "urlpage = 'https://www.hrc.org/resources/municipalities/search/p3?sort=score-desc'\n",
    "print(urlpage)\n",
    "# run firefox webdriver from executable path of your choice\n",
    "driver = webdriver.Firefox(executable_path = '/Users/a666/codeup-data-science/flask-project/geckodriver')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of results (cities): 20\n",
      "Number of results (states): 10\n"
     ]
    }
   ],
   "source": [
    "# get web page\n",
    "driver.get(urlpage)\n",
    "# execute script to scroll down the page\n",
    "driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight);var lenOfPage=document.body.scrollHeight;return lenOfPage;\")\n",
    "# sleep for 30s\n",
    "time.sleep(30)\n",
    "\n",
    "results_cities = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/div/h2/a/span\")\n",
    "results_states = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/p\")\n",
    "print('Number of results (cities):', len(results_cities))\n",
    "print('Number of results (states):', len(results_states))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>city</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chula Vista</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Cincinatti</td>\n",
       "      <td>Ohio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Cleveland</td>\n",
       "      <td>Ohio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>College Park</td>\n",
       "      <td>Maryland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Columbia</td>\n",
       "      <td>Maryland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Columbia</td>\n",
       "      <td>Missouri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Columbus</td>\n",
       "      <td>Ohio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Dallas</td>\n",
       "      <td>Texas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Dayton</td>\n",
       "      <td>Ohio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Denver</td>\n",
       "      <td>Colorado</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           city       state\n",
       "0   Chula Vista  California\n",
       "1    Cincinatti        Ohio\n",
       "2     Cleveland        Ohio\n",
       "3  College Park    Maryland\n",
       "4      Columbia    Maryland\n",
       "5      Columbia    Missouri\n",
       "6      Columbus        Ohio\n",
       "7        Dallas       Texas\n",
       "8        Dayton        Ohio\n",
       "9        Denver    Colorado"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create empty df and arrays to store data\n",
    "df3 = pd.DataFrame(columns = ['city', 'state'])\n",
    "city_list = []\n",
    "state_list = []\n",
    "# loop over results and store in the lists\n",
    "for result_city in results_cities:\n",
    "    if result_city.text != \"\":\n",
    "        city_list.append(result_city.text)\n",
    "for result_state in results_states:\n",
    "    state_list.append(result_state.text)\n",
    "# add lists to df\n",
    "df3['city'] = pd.Series(city_list)\n",
    "df3['state'] = pd.Series(state_list)\n",
    "\n",
    "driver.quit()\n",
    "\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.hrc.org/resources/municipalities/search/p4?sort=score-desc\n"
     ]
    }
   ],
   "source": [
    "# specify the url\n",
    "urlpage = 'https://www.hrc.org/resources/municipalities/search/p4?sort=score-desc'\n",
    "print(urlpage)\n",
    "# run firefox webdriver from executable path of your choice\n",
    "driver = webdriver.Firefox(executable_path = '/Users/a666/codeup-data-science/flask-project/geckodriver')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of results (cities): 20\n",
      "Number of results (states): 10\n"
     ]
    }
   ],
   "source": [
    "# get web page\n",
    "driver.get(urlpage)\n",
    "# execute script to scroll down the page\n",
    "driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight);var lenOfPage=document.body.scrollHeight;return lenOfPage;\")\n",
    "# sleep for 30s\n",
    "time.sleep(30)\n",
    "\n",
    "results_cities = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/div/h2/a/span\")\n",
    "results_states = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/p\")\n",
    "print('Number of results (cities):', len(results_cities))\n",
    "print('Number of results (states):', len(results_states))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>city</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Detroit</td>\n",
       "      <td>Michigan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Dubuque</td>\n",
       "      <td>Iowa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>East Lansing</td>\n",
       "      <td>Michigan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Enterprise</td>\n",
       "      <td>Nevada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Eugene</td>\n",
       "      <td>Oregon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Ferndale</td>\n",
       "      <td>Michigan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Fort Lauderdale</td>\n",
       "      <td>Florida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Fort Worth</td>\n",
       "      <td>Texas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Frederick</td>\n",
       "      <td>Maryland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Hoboken</td>\n",
       "      <td>New Jersey</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              city       state\n",
       "0          Detroit    Michigan\n",
       "1          Dubuque        Iowa\n",
       "2     East Lansing    Michigan\n",
       "3       Enterprise      Nevada\n",
       "4           Eugene      Oregon\n",
       "5         Ferndale    Michigan\n",
       "6  Fort Lauderdale     Florida\n",
       "7       Fort Worth       Texas\n",
       "8        Frederick    Maryland\n",
       "9          Hoboken  New Jersey"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create empty df and arrays to store data\n",
    "df4 = pd.DataFrame(columns = ['city', 'state'])\n",
    "city_list = []\n",
    "state_list = []\n",
    "# loop over results and store in the lists\n",
    "for result_city in results_cities:\n",
    "    if result_city.text != \"\":\n",
    "        city_list.append(result_city.text)\n",
    "for result_state in results_states:\n",
    "    state_list.append(result_state.text)\n",
    "# add lists to df\n",
    "df4['city'] = pd.Series(city_list)\n",
    "df4['state'] = pd.Series(state_list)\n",
    "\n",
    "driver.quit()\n",
    "\n",
    "df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.hrc.org/resources/municipalities/search/p5?sort=score-desc\n"
     ]
    }
   ],
   "source": [
    "# specify the url\n",
    "urlpage = 'https://www.hrc.org/resources/municipalities/search/p5?sort=score-desc'\n",
    "print(urlpage)\n",
    "# run firefox webdriver from executable path of your choice\n",
    "driver = webdriver.Firefox(executable_path = '/Users/a666/codeup-data-science/flask-project/geckodriver')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of results (cities): 20\n",
      "Number of results (states): 10\n"
     ]
    }
   ],
   "source": [
    "# get web page\n",
    "driver.get(urlpage)\n",
    "# execute script to scroll down the page\n",
    "driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight);var lenOfPage=document.body.scrollHeight;return lenOfPage;\")\n",
    "# sleep for 30s\n",
    "time.sleep(30)\n",
    "\n",
    "results_cities = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/div/h2/a/span\")\n",
    "results_states = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/p\")\n",
    "print('Number of results (cities):', len(results_cities))\n",
    "print('Number of results (states):', len(results_states))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>city</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Huntington</td>\n",
       "      <td>West Virginia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Iowa City</td>\n",
       "      <td>Iowa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jersey City</td>\n",
       "      <td>New Jersey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>Nevada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Long Beach</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Louisville</td>\n",
       "      <td>Kentucky</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Madison</td>\n",
       "      <td>Wisconsin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Miami Beach</td>\n",
       "      <td>Florida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Milwaukee</td>\n",
       "      <td>Wisconsin</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          city          state\n",
       "0   Huntington  West Virginia\n",
       "1    Iowa City           Iowa\n",
       "2  Jersey City     New Jersey\n",
       "3    Las Vegas         Nevada\n",
       "4   Long Beach     California\n",
       "5  Los Angeles     California\n",
       "6   Louisville       Kentucky\n",
       "7      Madison      Wisconsin\n",
       "8  Miami Beach        Florida\n",
       "9    Milwaukee      Wisconsin"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create empty df and arrays to store data\n",
    "df5 = pd.DataFrame(columns = ['city', 'state'])\n",
    "city_list = []\n",
    "state_list = []\n",
    "# loop over results and store in the lists\n",
    "for result_city in results_cities:\n",
    "    if result_city.text != \"\":\n",
    "        city_list.append(result_city.text)\n",
    "for result_state in results_states:\n",
    "    state_list.append(result_state.text)\n",
    "# add lists to df\n",
    "df5['city'] = pd.Series(city_list)\n",
    "df5['state'] = pd.Series(state_list)\n",
    "\n",
    "driver.quit()\n",
    "\n",
    "df5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.hrc.org/resources/municipalities/search/p6?sort=score-desc\n"
     ]
    }
   ],
   "source": [
    "# specify the url\n",
    "urlpage = 'https://www.hrc.org/resources/municipalities/search/p6?sort=score-desc'\n",
    "print(urlpage)\n",
    "# run firefox webdriver from executable path of your choice\n",
    "driver = webdriver.Firefox(executable_path = '/Users/a666/codeup-data-science/flask-project/geckodriver')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of results (cities): 20\n",
      "Number of results (states): 10\n"
     ]
    }
   ],
   "source": [
    "# get web page\n",
    "driver.get(urlpage)\n",
    "# execute script to scroll down the page\n",
    "driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight);var lenOfPage=document.body.scrollHeight;return lenOfPage;\")\n",
    "# sleep for 30s\n",
    "time.sleep(30)\n",
    "\n",
    "results_cities = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/div/h2/a/span\")\n",
    "results_states = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/p\")\n",
    "print('Number of results (cities):', len(results_cities))\n",
    "print('Number of results (states):', len(results_states))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>city</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Minneapolis</td>\n",
       "      <td>Minnesota</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>New Orleans</td>\n",
       "      <td>Louisiana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>New Rochelle</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>New York</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Northampton</td>\n",
       "      <td>Massachusetts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Norwalk</td>\n",
       "      <td>Connecticut</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Oakland</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Oceanside</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Olympia</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Orlando</td>\n",
       "      <td>Florida</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           city          state\n",
       "0   Minneapolis      Minnesota\n",
       "1   New Orleans      Louisiana\n",
       "2  New Rochelle       New York\n",
       "3      New York       New York\n",
       "4   Northampton  Massachusetts\n",
       "5       Norwalk    Connecticut\n",
       "6       Oakland     California\n",
       "7     Oceanside     California\n",
       "8       Olympia     Washington\n",
       "9       Orlando        Florida"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create empty df and arrays to store data\n",
    "df6 = pd.DataFrame(columns = ['city', 'state'])\n",
    "city_list = []\n",
    "state_list = []\n",
    "# loop over results and store in the lists\n",
    "for result_city in results_cities:\n",
    "    if result_city.text != \"\":\n",
    "        city_list.append(result_city.text)\n",
    "for result_state in results_states:\n",
    "    state_list.append(result_state.text)\n",
    "# add lists to df\n",
    "df6['city'] = pd.Series(city_list)\n",
    "df6['state'] = pd.Series(state_list)\n",
    "\n",
    "driver.quit()\n",
    "\n",
    "df6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.hrc.org/resources/municipalities/search/p7?sort=score-desc\n"
     ]
    }
   ],
   "source": [
    "# specify the url\n",
    "urlpage = 'https://www.hrc.org/resources/municipalities/search/p7?sort=score-desc'\n",
    "print(urlpage)\n",
    "# run firefox webdriver from executable path of your choice\n",
    "driver = webdriver.Firefox(executable_path = '/Users/a666/codeup-data-science/flask-project/geckodriver')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of results (cities): 20\n",
      "Number of results (states): 10\n"
     ]
    }
   ],
   "source": [
    "# get web page\n",
    "driver.get(urlpage)\n",
    "# execute script to scroll down the page\n",
    "driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight);var lenOfPage=document.body.scrollHeight;return lenOfPage;\")\n",
    "# sleep for 30s\n",
    "time.sleep(30)\n",
    "\n",
    "results_cities = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/div/h2/a/span\")\n",
    "results_states = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/p\")\n",
    "print('Number of results (cities):', len(results_cities))\n",
    "print('Number of results (states):', len(results_states))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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>city</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Palm Springs</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Paradise</td>\n",
       "      <td>Nevada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>Pennsylvania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Phoenix</td>\n",
       "      <td>Arizona</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>Pennsylvania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Portland</td>\n",
       "      <td>Oregon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Princeton</td>\n",
       "      <td>New Jersey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Providence</td>\n",
       "      <td>Rhode Island</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Provincetown</td>\n",
       "      <td>Massachusetts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Rancho Mirage</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            city          state\n",
       "0   Palm Springs     California\n",
       "1       Paradise         Nevada\n",
       "2   Philadelphia   Pennsylvania\n",
       "3        Phoenix        Arizona\n",
       "4     Pittsburgh   Pennsylvania\n",
       "5       Portland         Oregon\n",
       "6      Princeton     New Jersey\n",
       "7     Providence   Rhode Island\n",
       "8   Provincetown  Massachusetts\n",
       "9  Rancho Mirage     California"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create empty df and arrays to store data\n",
    "df7 = pd.DataFrame(columns = ['city', 'state'])\n",
    "city_list = []\n",
    "state_list = []\n",
    "# loop over results and store in the lists\n",
    "for result_city in results_cities:\n",
    "    if result_city.text != \"\":\n",
    "        city_list.append(result_city.text)\n",
    "for result_state in results_states:\n",
    "    state_list.append(result_state.text)\n",
    "# add lists to df\n",
    "df7['city'] = pd.Series(city_list)\n",
    "df7['state'] = pd.Series(state_list)\n",
    "\n",
    "driver.quit()\n",
    "\n",
    "df7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.hrc.org/resources/municipalities/search/p8?sort=score-desc\n"
     ]
    }
   ],
   "source": [
    "# specify the url\n",
    "urlpage = 'https://www.hrc.org/resources/municipalities/search/p8?sort=score-desc'\n",
    "print(urlpage)\n",
    "# run firefox webdriver from executable path of your choice\n",
    "driver = webdriver.Firefox(executable_path = '/Users/a666/codeup-data-science/flask-project/geckodriver')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of results (cities): 20\n",
      "Number of results (states): 10\n"
     ]
    }
   ],
   "source": [
    "# get web page\n",
    "driver.get(urlpage)\n",
    "# execute script to scroll down the page\n",
    "driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight);var lenOfPage=document.body.scrollHeight;return lenOfPage;\")\n",
    "# sleep for 30s\n",
    "time.sleep(30)\n",
    "\n",
    "results_cities = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/div/h2/a/span\")\n",
    "results_states = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/p\")\n",
    "print('Number of results (cities):', len(results_cities))\n",
    "print('Number of results (states):', len(results_states))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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>city</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Reno</td>\n",
       "      <td>Nevada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Richmond</td>\n",
       "      <td>Virginia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Riverside</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rochester</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rockville</td>\n",
       "      <td>Maryland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Sacramento</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Saint Paul</td>\n",
       "      <td>Minnesota</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Salem</td>\n",
       "      <td>Massachusetts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>San Antonio</td>\n",
       "      <td>Texas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>San Diego</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          city          state\n",
       "0         Reno         Nevada\n",
       "1     Richmond       Virginia\n",
       "2    Riverside     California\n",
       "3    Rochester       New York\n",
       "4    Rockville       Maryland\n",
       "5   Sacramento     California\n",
       "6   Saint Paul      Minnesota\n",
       "7        Salem  Massachusetts\n",
       "8  San Antonio          Texas\n",
       "9    San Diego     California"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create empty df and arrays to store data\n",
    "df8 = pd.DataFrame(columns = ['city', 'state'])\n",
    "city_list = []\n",
    "state_list = []\n",
    "# loop over results and store in the lists\n",
    "for result_city in results_cities:\n",
    "    if result_city.text != \"\":\n",
    "        city_list.append(result_city.text)\n",
    "for result_state in results_states:\n",
    "    state_list.append(result_state.text)\n",
    "# add lists to df\n",
    "df8['city'] = pd.Series(city_list)\n",
    "df8['state'] = pd.Series(state_list)\n",
    "\n",
    "driver.quit()\n",
    "\n",
    "df8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.hrc.org/resources/municipalities/search/p9?sort=score-desc\n"
     ]
    }
   ],
   "source": [
    "# specify the url\n",
    "urlpage = 'https://www.hrc.org/resources/municipalities/search/p9?sort=score-desc'\n",
    "print(urlpage)\n",
    "# run firefox webdriver from executable path of your choice\n",
    "driver = webdriver.Firefox(executable_path = '/Users/a666/codeup-data-science/flask-project/geckodriver')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of results (cities): 20\n",
      "Number of results (states): 10\n"
     ]
    }
   ],
   "source": [
    "# get web page\n",
    "driver.get(urlpage)\n",
    "# execute script to scroll down the page\n",
    "driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight);var lenOfPage=document.body.scrollHeight;return lenOfPage;\")\n",
    "# sleep for 30s\n",
    "time.sleep(30)\n",
    "\n",
    "results_cities = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/div/h2/a/span\")\n",
    "results_states = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/p\")\n",
    "print('Number of results (cities):', len(results_cities))\n",
    "print('Number of results (states):', len(results_states))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "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>city</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Santa Monica</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Seattle</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>St. Louis</td>\n",
       "      <td>Missouri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>St. Petersburg</td>\n",
       "      <td>Florida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Stamford</td>\n",
       "      <td>Connecticut</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>State College</td>\n",
       "      <td>Pennsylvania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Tallahassee</td>\n",
       "      <td>Florida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Tampa</td>\n",
       "      <td>Florida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Tempe</td>\n",
       "      <td>Arizona</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             city         state\n",
       "0   San Francisco    California\n",
       "1    Santa Monica    California\n",
       "2         Seattle    Washington\n",
       "3       St. Louis      Missouri\n",
       "4  St. Petersburg       Florida\n",
       "5        Stamford   Connecticut\n",
       "6   State College  Pennsylvania\n",
       "7     Tallahassee       Florida\n",
       "8           Tampa       Florida\n",
       "9           Tempe       Arizona"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create empty df and arrays to store data\n",
    "df9 = pd.DataFrame(columns = ['city', 'state'])\n",
    "city_list = []\n",
    "state_list = []\n",
    "# loop over results and store in the lists\n",
    "for result_city in results_cities:\n",
    "    if result_city.text != \"\":\n",
    "        city_list.append(result_city.text)\n",
    "for result_state in results_states:\n",
    "    state_list.append(result_state.text)\n",
    "# add lists to df\n",
    "df9['city'] = pd.Series(city_list)\n",
    "df9['state'] = pd.Series(state_list)\n",
    "\n",
    "driver.quit()\n",
    "\n",
    "df9"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.hrc.org/resources/municipalities/search/p10?sort=score-desc\n"
     ]
    }
   ],
   "source": [
    "# specify the url\n",
    "urlpage = 'https://www.hrc.org/resources/municipalities/search/p10?sort=score-desc'\n",
    "print(urlpage)\n",
    "# run firefox webdriver from executable path of your choice\n",
    "driver = webdriver.Firefox(executable_path = '/Users/a666/codeup-data-science/flask-project/geckodriver')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of results (cities): 20\n",
      "Number of results (states): 10\n"
     ]
    }
   ],
   "source": [
    "# get web page\n",
    "driver.get(urlpage)\n",
    "# execute script to scroll down the page\n",
    "driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight);var lenOfPage=document.body.scrollHeight;return lenOfPage;\")\n",
    "# sleep for 30s\n",
    "time.sleep(30)\n",
    "\n",
    "results_cities = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/div/h2/a/span\")\n",
    "results_states = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/p\")\n",
    "print('Number of results (cities):', len(results_cities))\n",
    "print('Number of results (states):', len(results_states))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>city</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tucson</td>\n",
       "      <td>Arizona</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Virginia Beach</td>\n",
       "      <td>Virginia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>West Hollywood</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>West Palm Beach</td>\n",
       "      <td>Florida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Wilton Manors</td>\n",
       "      <td>Florida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Worcester</td>\n",
       "      <td>Massachusetts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Yonkers</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Akron</td>\n",
       "      <td>Ohio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Hartford</td>\n",
       "      <td>Connecticut</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Missoula</td>\n",
       "      <td>Montana</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              city          state\n",
       "0           Tucson        Arizona\n",
       "1   Virginia Beach       Virginia\n",
       "2   West Hollywood     California\n",
       "3  West Palm Beach        Florida\n",
       "4    Wilton Manors        Florida\n",
       "5        Worcester  Massachusetts\n",
       "6          Yonkers       New York\n",
       "7            Akron           Ohio\n",
       "8         Hartford    Connecticut\n",
       "9         Missoula        Montana"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create empty df and arrays to store data\n",
    "df10 = pd.DataFrame(columns = ['city', 'state'])\n",
    "city_list = []\n",
    "state_list = []\n",
    "# loop over results and store in the lists\n",
    "for result_city in results_cities:\n",
    "    if result_city.text != \"\":\n",
    "        city_list.append(result_city.text)\n",
    "for result_state in results_states:\n",
    "    state_list.append(result_state.text)\n",
    "# add lists to df\n",
    "df10['city'] = pd.Series(city_list)\n",
    "df10['state'] = pd.Series(state_list)\n",
    "\n",
    "driver.quit()\n",
    "\n",
    "df10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.hrc.org/resources/municipalities/search/p11?sort=score-desc\n"
     ]
    }
   ],
   "source": [
    "# specify the url\n",
    "urlpage = 'https://www.hrc.org/resources/municipalities/search/p11?sort=score-desc'\n",
    "print(urlpage)\n",
    "# run firefox webdriver from executable path of your choice\n",
    "driver = webdriver.Firefox(executable_path = '/Users/a666/codeup-data-science/flask-project/geckodriver')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of results (cities): 20\n",
      "Number of results (states): 10\n"
     ]
    }
   ],
   "source": [
    "# get web page\n",
    "driver.get(urlpage)\n",
    "# execute script to scroll down the page\n",
    "driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight);var lenOfPage=document.body.scrollHeight;return lenOfPage;\")\n",
    "# sleep for 30s\n",
    "time.sleep(30)\n",
    "\n",
    "results_cities = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/div/h2/a/span\")\n",
    "results_states = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/p\")\n",
    "print('Number of results (cities):', len(results_cities))\n",
    "print('Number of results (states):', len(results_states))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "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>city</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Oakland Park</td>\n",
       "      <td>Florida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>West Des Moines</td>\n",
       "      <td>Iowa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Burlington</td>\n",
       "      <td>Vermont</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ithaca</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Lambertville</td>\n",
       "      <td>New Jersey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Lawrence</td>\n",
       "      <td>Kansas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Toledo</td>\n",
       "      <td>Ohio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>San Jose</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Asbury Park</td>\n",
       "      <td>New Jersey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Covington</td>\n",
       "      <td>Kentucky</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              city       state\n",
       "0     Oakland Park     Florida\n",
       "1  West Des Moines        Iowa\n",
       "2       Burlington     Vermont\n",
       "3           Ithaca    New York\n",
       "4     Lambertville  New Jersey\n",
       "5         Lawrence      Kansas\n",
       "6           Toledo        Ohio\n",
       "7         San Jose  California\n",
       "8      Asbury Park  New Jersey\n",
       "9        Covington    Kentucky"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create empty df and arrays to store data\n",
    "df11 = pd.DataFrame(columns = ['city', 'state'])\n",
    "city_list = []\n",
    "state_list = []\n",
    "# loop over results and store in the lists\n",
    "for result_city in results_cities:\n",
    "    if result_city.text != \"\":\n",
    "        city_list.append(result_city.text)\n",
    "for result_state in results_states:\n",
    "    state_list.append(result_state.text)\n",
    "# add lists to df\n",
    "df11['city'] = pd.Series(city_list)\n",
    "df11['state'] = pd.Series(state_list)\n",
    "\n",
    "driver.quit()\n",
    "\n",
    "df11"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.hrc.org/resources/municipalities/search/p12?sort=score-desc\n"
     ]
    }
   ],
   "source": [
    "# specify the url\n",
    "urlpage = 'https://www.hrc.org/resources/municipalities/search/p12?sort=score-desc'\n",
    "print(urlpage)\n",
    "# run firefox webdriver from executable path of your choice\n",
    "driver = webdriver.Firefox(executable_path = '/Users/a666/codeup-data-science/flask-project/geckodriver')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of results (cities): 20\n",
      "Number of results (states): 10\n"
     ]
    }
   ],
   "source": [
    "# get web page\n",
    "driver.get(urlpage)\n",
    "# execute script to scroll down the page\n",
    "driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight);var lenOfPage=document.body.scrollHeight;return lenOfPage;\")\n",
    "# sleep for 30s\n",
    "time.sleep(30)\n",
    "\n",
    "results_cities = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/div/h2/a/span\")\n",
    "results_states = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/p\")\n",
    "print('Number of results (cities):', len(results_cities))\n",
    "print('Number of results (states):', len(results_states))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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>city</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Signal Hill</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tacoma</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>White Plains</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fort Collins</td>\n",
       "      <td>Colorado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Gainesville</td>\n",
       "      <td>Florida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Lexington</td>\n",
       "      <td>Kentucky</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Trenton</td>\n",
       "      <td>New Jersey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Guerneville</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Henderson</td>\n",
       "      <td>Nevada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Kansas City</td>\n",
       "      <td>Missouri</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           city       state\n",
       "0   Signal Hill  California\n",
       "1        Tacoma  Washington\n",
       "2  White Plains    New York\n",
       "3  Fort Collins    Colorado\n",
       "4   Gainesville     Florida\n",
       "5     Lexington    Kentucky\n",
       "6       Trenton  New Jersey\n",
       "7   Guerneville  California\n",
       "8     Henderson      Nevada\n",
       "9   Kansas City    Missouri"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create empty df and arrays to store data\n",
    "df12 = pd.DataFrame(columns = ['city', 'state'])\n",
    "city_list = []\n",
    "state_list = []\n",
    "# loop over results and store in the lists\n",
    "for result_city in results_cities:\n",
    "    if result_city.text != \"\":\n",
    "        city_list.append(result_city.text)\n",
    "for result_state in results_states:\n",
    "    state_list.append(result_state.text)\n",
    "# add lists to df\n",
    "df12['city'] = pd.Series(city_list)\n",
    "df12['state'] = pd.Series(state_list)\n",
    "\n",
    "driver.quit()\n",
    "\n",
    "df12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.hrc.org/resources/municipalities/search/p13?sort=score-desc\n"
     ]
    }
   ],
   "source": [
    "# specify the url\n",
    "urlpage = 'https://www.hrc.org/resources/municipalities/search/p13?sort=score-desc'\n",
    "print(urlpage)\n",
    "# run firefox webdriver from executable path of your choice\n",
    "driver = webdriver.Firefox(executable_path = '/Users/a666/codeup-data-science/flask-project/geckodriver')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of results (cities): 20\n",
      "Number of results (states): 10\n"
     ]
    }
   ],
   "source": [
    "# get web page\n",
    "driver.get(urlpage)\n",
    "# execute script to scroll down the page\n",
    "driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight);var lenOfPage=document.body.scrollHeight;return lenOfPage;\")\n",
    "# sleep for 30s\n",
    "time.sleep(30)\n",
    "\n",
    "results_cities = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/div/h2/a/span\")\n",
    "results_states = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/p\")\n",
    "print('Number of results (cities):', len(results_cities))\n",
    "print('Number of results (states):', len(results_states))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "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>city</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Palm Desert</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Portland</td>\n",
       "      <td>Maine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Richmond</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Vashon</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Gaithersburg</td>\n",
       "      <td>Maryland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Irvine</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Laguna Beach</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Overland Park</td>\n",
       "      <td>Kansas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Pasadena</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Anchorage</td>\n",
       "      <td>Alaska</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            city       state\n",
       "0    Palm Desert  California\n",
       "1       Portland       Maine\n",
       "2       Richmond  California\n",
       "3         Vashon  Washington\n",
       "4   Gaithersburg    Maryland\n",
       "5         Irvine  California\n",
       "6   Laguna Beach  California\n",
       "7  Overland Park      Kansas\n",
       "8       Pasadena  California\n",
       "9      Anchorage      Alaska"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create empty df and arrays to store data\n",
    "df13 = pd.DataFrame(columns = ['city', 'state'])\n",
    "city_list = []\n",
    "state_list = []\n",
    "# loop over results and store in the lists\n",
    "for result_city in results_cities:\n",
    "    if result_city.text != \"\":\n",
    "        city_list.append(result_city.text)\n",
    "for result_state in results_states:\n",
    "    state_list.append(result_state.text)\n",
    "# add lists to df\n",
    "df13['city'] = pd.Series(city_list)\n",
    "df13['state'] = pd.Series(state_list)\n",
    "\n",
    "driver.quit()\n",
    "\n",
    "df13"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.hrc.org/resources/municipalities/search/p14?sort=score-desc\n"
     ]
    }
   ],
   "source": [
    "# specify the url\n",
    "urlpage = 'https://www.hrc.org/resources/municipalities/search/p14?sort=score-desc'\n",
    "print(urlpage)\n",
    "# run firefox webdriver from executable path of your choice\n",
    "driver = webdriver.Firefox(executable_path = '/Users/a666/codeup-data-science/flask-project/geckodriver')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of results (cities): 20\n",
      "Number of results (states): 10\n"
     ]
    }
   ],
   "source": [
    "# get web page\n",
    "driver.get(urlpage)\n",
    "# execute script to scroll down the page\n",
    "driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight);var lenOfPage=document.body.scrollHeight;return lenOfPage;\")\n",
    "# sleep for 30s\n",
    "time.sleep(30)\n",
    "\n",
    "results_cities = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/div/h2/a/span\")\n",
    "results_states = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/p\")\n",
    "print('Number of results (cities):', len(results_cities))\n",
    "print('Number of results (states):', len(results_states))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "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>city</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Charleston</td>\n",
       "      <td>West Virginia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Grand Rapids</td>\n",
       "      <td>Michigan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Norman</td>\n",
       "      <td>Oklahoma</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Albuquerque</td>\n",
       "      <td>New Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Buffalo</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Coral Gables</td>\n",
       "      <td>Florida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Norfolk</td>\n",
       "      <td>Virginia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Reading</td>\n",
       "      <td>Pennsylvania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Duluth</td>\n",
       "      <td>Minnesota</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Durham</td>\n",
       "      <td>New Hampshire</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           city          state\n",
       "0    Charleston  West Virginia\n",
       "1  Grand Rapids       Michigan\n",
       "2        Norman       Oklahoma\n",
       "3   Albuquerque     New Mexico\n",
       "4       Buffalo       New York\n",
       "5  Coral Gables        Florida\n",
       "6       Norfolk       Virginia\n",
       "7       Reading   Pennsylvania\n",
       "8        Duluth      Minnesota\n",
       "9        Durham  New Hampshire"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create empty df and arrays to store data\n",
    "df14 = pd.DataFrame(columns = ['city', 'state'])\n",
    "city_list = []\n",
    "state_list = []\n",
    "# loop over results and store in the lists\n",
    "for result_city in results_cities:\n",
    "    if result_city.text != \"\":\n",
    "        city_list.append(result_city.text)\n",
    "for result_state in results_states:\n",
    "    state_list.append(result_state.text)\n",
    "# add lists to df\n",
    "df14['city'] = pd.Series(city_list)\n",
    "df14['state'] = pd.Series(state_list)\n",
    "\n",
    "driver.quit()\n",
    "\n",
    "df14"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.hrc.org/resources/municipalities/search/p15?sort=score-desc\n"
     ]
    }
   ],
   "source": [
    "# specify the url\n",
    "urlpage = 'https://www.hrc.org/resources/municipalities/search/p15?sort=score-desc'\n",
    "print(urlpage)\n",
    "# run firefox webdriver from executable path of your choice\n",
    "driver = webdriver.Firefox(executable_path = '/Users/a666/codeup-data-science/flask-project/geckodriver')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of results (cities): 20\n",
      "Number of results (states): 10\n"
     ]
    }
   ],
   "source": [
    "# get web page\n",
    "driver.get(urlpage)\n",
    "# execute script to scroll down the page\n",
    "driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight);var lenOfPage=document.body.scrollHeight;return lenOfPage;\")\n",
    "# sleep for 30s\n",
    "time.sleep(30)\n",
    "\n",
    "results_cities = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/div/h2/a/span\")\n",
    "results_states = driver.find_elements_by_xpath(\"/html/body/div/main/div/section/div/article/div/p\")\n",
    "print('Number of results (cities):', len(results_cities))\n",
    "print('Number of results (states):', len(results_states))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "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>city</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fremont</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Salem</td>\n",
       "      <td>Oregon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Terre Haute</td>\n",
       "      <td>Indiana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wilkes-Barre</td>\n",
       "      <td>Pennsylvania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Charlottesville</td>\n",
       "      <td>Virginia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Indianapolis</td>\n",
       "      <td>Indiana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>New Hope</td>\n",
       "      <td>Pennsylvania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Ocean Grove (Neptune)</td>\n",
       "      <td>New Jersey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Sunnyvale</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Ames</td>\n",
       "      <td>Iowa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    city         state\n",
       "0                Fremont    California\n",
       "1                  Salem        Oregon\n",
       "2            Terre Haute       Indiana\n",
       "3           Wilkes-Barre  Pennsylvania\n",
       "4        Charlottesville      Virginia\n",
       "5           Indianapolis       Indiana\n",
       "6               New Hope  Pennsylvania\n",
       "7  Ocean Grove (Neptune)    New Jersey\n",
       "8              Sunnyvale    California\n",
       "9                   Ames          Iowa"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create empty df and arrays to store data\n",
    "df15 = pd.DataFrame(columns = ['city', 'state'])\n",
    "city_list = []\n",
    "state_list = []\n",
    "# loop over results and store in the lists\n",
    "for result_city in results_cities:\n",
    "    if result_city.text != \"\":\n",
    "        city_list.append(result_city.text)\n",
    "for result_state in results_states:\n",
    "    state_list.append(result_state.text)\n",
    "# add lists to df\n",
    "df15['city'] = pd.Series(city_list)\n",
    "df15['state'] = pd.Series(state_list)\n",
    "\n",
    "driver.quit()\n",
    "\n",
    "df15"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I want to (for now) have a list of only cities with a MEI score of 90 or above. Therefore I will cut off anything after Wilkes-Barre, PA.m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "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>city</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fremont</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Salem</td>\n",
       "      <td>Oregon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Terre Haute</td>\n",
       "      <td>Indiana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wilkes-Barre</td>\n",
       "      <td>Pennsylvania</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           city         state\n",
       "0       Fremont    California\n",
       "1         Salem        Oregon\n",
       "2   Terre Haute       Indiana\n",
       "3  Wilkes-Barre  Pennsylvania"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df15 = df15[:4]\n",
    "df15"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now I will concat all the dfs, so that I now have a list of all of the cities listed as receiving a score of at least 90 on the MEI."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "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>city</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albany</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alexandria</td>\n",
       "      <td>Virginia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Allentown</td>\n",
       "      <td>Pennsylvania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ann Arbor</td>\n",
       "      <td>Michigan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Arlington</td>\n",
       "      <td>Massachusetts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Durham</td>\n",
       "      <td>New Hampshire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fremont</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Salem</td>\n",
       "      <td>Oregon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Terre Haute</td>\n",
       "      <td>Indiana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wilkes-Barre</td>\n",
       "      <td>Pennsylvania</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>144 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            city          state\n",
       "0         Albany       New York\n",
       "1     Alexandria       Virginia\n",
       "2      Allentown   Pennsylvania\n",
       "3      Ann Arbor       Michigan\n",
       "4      Arlington  Massachusetts\n",
       "..           ...            ...\n",
       "9         Durham  New Hampshire\n",
       "0        Fremont     California\n",
       "1          Salem         Oregon\n",
       "2    Terre Haute        Indiana\n",
       "3   Wilkes-Barre   Pennsylvania\n",
       "\n",
       "[144 rows x 2 columns]"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hrc_mei_90plus_cities = pd.concat([df1, df2, df3, df4, df5, df6, df7, df8, df9, df10, df11, df12, df13, df14, df15])\n",
    "hrc_mei_90plus_cities"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Success. Now I'll export it to csv."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "hrc_mei_90plus_cities.to_csv('hrc_mei_90plus_cities.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Second source: Cat-friendly and Dog-friendly locations\n",
    "\n",
    "https://www.thrillist.com/news/nation/most-cat-friendly-us-cities-right-now\n",
    "\n",
    "https://www.thrillist.com/news/nation/pet-friendly-us-cities-2020-list-wallethub"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cats = pd.DataFrame(columns = ['city', 'state'])\n",
    "dogs = pd.DataFrame(columns = ['city', 'state'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dog_cities = ['Tampa', 'Austin', 'Las Vegas', 'Orlando', 'Seattle',\n",
    "              'St. Louis', 'Atlanta', 'New Orleans', 'Birmingham', 'San Diego',\n",
    "              'Cincinnati', 'Scottsdale', 'Boise', 'Portland', 'Lexington-Fayette',\n",
    "              'Miami', 'Nashville', 'Houston', 'Corpus Christi', 'Oklahoma City']\n",
    "\n",
    "dog_states = ['Florida', 'Texas', 'Nevada', 'Florida', 'Washington', 'Missouri', 'Georgia',\n",
    "             'Louisiana', 'Alabama', 'California', 'Ohio', 'Arizona', 'Idaho', 'Oregon',\n",
    "             'Kentucky', 'Florida', 'Tennessee', 'Texas', 'Texas', 'Oklahoma']\n",
    "\n",
    "dogs['city'] = pd.Series(dog_cities)\n",
    "dogs['state'] = pd.Series(dog_states)\n",
    "\n",
    "dogs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# going to truncate at 20 locations for cats (for now at least) so that dogs and cats have equal locations\n",
    "\n",
    "cat_cities = ['Birmingham', 'Portland', 'Madison', 'Richmond', 'Minneapolis', 'St. Louis', 'Tampa',\n",
    "              'Orlando', 'Greensboro', 'Denver', 'Fort Wayne', 'Baton Rouge', 'Seattle', 'Omaha', 'Tulsa',\n",
    "              'St. Paul', 'Sacramento', 'St. Petersburg', 'Reno', 'Cincinnati']\n",
    "\n",
    "cat_states = ['Alabama', 'Oregon', 'Wisconsin', 'Virginia', 'Minnesota', 'Missouri', 'Florida', 'Florida',\n",
    "             'North Carolina', 'Colorado', 'Indiana', 'Louisiana', 'Washington', 'Nebraska',\n",
    "             'Oklahoma', 'Minnesota', 'California', 'Florida', 'Nevada', 'Ohio']\n",
    "\n",
    "cats['city'] = pd.Series(cat_cities)\n",
    "cats['state'] = pd.Series(cat_states)\n",
    "\n",
    "cats"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now I'll export the data I have so far and do some visualizations in an EDA notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dogs.to_csv('dogs.csv')\n",
    "cats.to_csv('cats.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Third source: Natural Environment Rankings\n",
    "\n",
    "http://localhost:8888/notebooks/acquire.ipynb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.usnews.com/news/best-states/rankings/natural-environment\n"
     ]
    }
   ],
   "source": [
    "# import libraries\n",
    "import urllib.request\n",
    "from selenium import webdriver\n",
    "import time\n",
    "\n",
    "# specify the url\n",
    "urlpage = 'https://www.usnews.com/news/best-states/rankings/natural-environment' \n",
    "print(urlpage)\n",
    "# run firefox webdriver from executable path of your choice\n",
    "driver = webdriver.Firefox(executable_path = '/Users/a666/codeup-data-science/flask-project/geckodriver')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of results (rank): 200\n"
     ]
    }
   ],
   "source": [
    "# get web page\n",
    "driver.get(urlpage)\n",
    "# execute script to scroll down the page\n",
    "driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight);var lenOfPage=document.body.scrollHeight;return lenOfPage;\")\n",
    "# sleep for 30s\n",
    "time.sleep(30)\n",
    "\n",
    "results_rank = driver.find_elements_by_xpath(\"/html/body/main/div/div/div/div/div/div/div/div/div/div/table/tbody/tr/td/span\")\n",
    "print('Number of results (rank):', len(results_rank))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\n",
      "Hawaii\n",
      "1\n",
      "13\n",
      "2\n",
      "New Hampshire\n",
      "10\n",
      "2\n",
      "3\n",
      "South Dakota\n",
      "11\n",
      "3\n",
      "4\n",
      "Massachusetts\n",
      "2\n",
      "17\n",
      "5\n",
      "New York\n",
      "7\n",
      "11\n",
      "6\n",
      "Nebraska\n",
      "6\n",
      "12\n",
      "7\n",
      "Rhode Island\n",
      "8\n",
      "7\n",
      "8\n",
      "North Dakota\n",
      "3\n",
      "19\n",
      "9\n",
      "Vermont\n",
      "28\n",
      "1\n",
      "10\n",
      "Minnesota\n",
      "14\n",
      "14\n",
      "11\n",
      "Maryland\n",
      "16\n",
      "16\n",
      "12\n",
      "Idaho\n",
      "27\n",
      "10\n",
      "13\n",
      "Wyoming\n",
      "36\n",
      "5\n",
      "14\n",
      "Maine\n",
      "31\n",
      "8\n",
      "15\n",
      "Washington\n",
      "17\n",
      "18\n",
      "16\n",
      "Kansas\n",
      "9\n",
      "23\n",
      "17\n",
      "Wisconsin\n",
      "15\n",
      "20\n",
      "18\n",
      "Florida\n",
      "5\n",
      "27\n",
      "19\n",
      "Virginia\n",
      "4\n",
      "32\n",
      "20\n",
      "Iowa\n",
      "18\n",
      "25\n",
      "21\n",
      "Missouri\n",
      "26\n",
      "24\n",
      "22\n",
      "Mississippi\n",
      "13\n",
      "28\n",
      "23\n",
      "Colorado\n",
      "41\n",
      "9\n",
      "24\n",
      "Montana\n",
      "39\n",
      "15\n",
      "25\n",
      "Georgia\n",
      "32\n",
      "26\n",
      "26\n",
      "South Carolina\n",
      "12\n",
      "33\n",
      "27\n",
      "North Carolina\n",
      "24\n",
      "31\n",
      "28\n",
      "Connecticut\n",
      "30\n",
      "30\n",
      "29\n",
      "Kentucky\n",
      "22\n",
      "35\n",
      "30\n",
      "Arkansas\n",
      "20\n",
      "36\n",
      "31\n",
      "New Mexico\n",
      "48\n",
      "4\n",
      "32\n",
      "Michigan\n",
      "35\n",
      "34\n",
      "33\n",
      "New Jersey\n",
      "25\n",
      "38\n",
      "34\n",
      "Oklahoma\n",
      "45\n",
      "22\n",
      "35\n",
      "California\n",
      "47\n",
      "6\n",
      "36\n",
      "West Virginia\n",
      "37\n",
      "37\n",
      "37\n",
      "Alabama\n",
      "33\n",
      "41\n",
      "38\n",
      "Pennsylvania\n",
      "40\n",
      "39\n",
      "39\n",
      "Tennessee\n",
      "23\n",
      "43\n",
      "40\n",
      "Texas\n",
      "42\n",
      "40\n",
      "41\n",
      "Arizona\n",
      "49\n",
      "21\n",
      "42\n",
      "Oregon\n",
      "21\n",
      "44\n",
      "43\n",
      "Illinois\n",
      "44\n",
      "42\n",
      "44\n",
      "Ohio\n",
      "34\n",
      "45\n",
      "45\n",
      "Delaware\n",
      "29\n",
      "47\n",
      "46\n",
      "Alaska\n",
      "50\n",
      "29\n",
      "47\n",
      "Utah\n",
      "43\n",
      "46\n",
      "48\n",
      "Indiana\n",
      "38\n",
      "48\n",
      "49\n",
      "Louisiana\n",
      "19\n",
      "50\n",
      "50\n",
      "Nevada\n",
      "46\n",
      "49\n"
     ]
    }
   ],
   "source": [
    "for result in results_rank:\n",
    "    print(result.text)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I'll use regex to grab the numbers (and states) that I need."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1 Hawaii 1 13 2 New Hampshire 10 2 3 South Dakota 11 3 4 Massachusetts 2 17 5 New York 7 11 6 Nebraska 6 12 7 Rhode Island 8 7 8 North Dakota 3 19 9 Vermont 28 1 10 Minnesota 14 14 11 Maryland 16 16 12 Idaho 27 10 13 Wyoming 36 5 14 Maine 31 8 15 Washington 17 18 16 Kansas 9 23 17 Wisconsin 15 20 18 Florida 5 27 19 Virginia 4 32 20 Iowa 18 25 21 Missouri 26 24 22 Mississippi 13 28 23 Colorado 41 9 24 Montana 39 15 25 Georgia 32 26 26 South Carolina 12 33 27 North Carolina 24 31 28 Connecticut 30 30 29 Kentucky 22 35 30 Arkansas 20 36 31 New Mexico 48 4 32 Michigan 35 34 33 New Jersey 25 38 34 Oklahoma 45 22 35 California 47 6 36 West Virginia 37 37 37 Alabama 33 41 38 Pennsylvania 40 39 39 Tennessee 23 43 40 Texas 42 40 41 Arizona 49 21 42 Oregon 21 44 43 Illinois 44 42 44 Ohio 34 45 45 Delaware 29 47 46 Alaska 50 29 47 Utah 43 46 48 Indiana 38 48 49 Louisiana 19 50 50 Nevada 46 49 '"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = \"\"\n",
    "for result in results_rank:\n",
    "    s += result.text\n",
    "    s += \" \"\n",
    "    \n",
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['1 Hawaii ', '2 New Hampshire ', '3 South Dakota ', '4 Massachusetts ', '5 New York ', '6 Nebraska ', '7 Rhode Island ', '8 North Dakota ', '9 Vermont ', '10 Minnesota ', '11 Maryland ', '12 Idaho ', '13 Wyoming ', '14 Maine ', '15 Washington ', '16 Kansas ', '17 Wisconsin ', '18 Florida ', '19 Virginia ', '20 Iowa ', '21 Missouri ', '22 Mississippi ', '23 Colorado ', '24 Montana ', '25 Georgia ', '26 South Carolina ', '27 North Carolina ', '28 Connecticut ', '29 Kentucky ', '30 Arkansas ', '31 New Mexico ', '32 Michigan ', '33 New Jersey ', '34 Oklahoma ', '35 California ', '36 West Virginia ', '37 Alabama ', '38 Pennsylvania ', '39 Tennessee ', '40 Texas ', '41 Arizona ', '42 Oregon ', '43 Illinois ', '44 Ohio ', '45 Delaware ', '46 Alaska ', '47 Utah ', '48 Indiana ', '49 Louisiana ', '50 Nevada ']\n"
     ]
    }
   ],
   "source": [
    "r1 = re.findall(r\"\\d+ [A-Z]\\w+ ?\\D*\", s)\n",
    "print(r1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1 Hawaii</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2 New Hampshire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3 South Dakota</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4 Massachusetts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5 New York</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  0\n",
       "0         1 Hawaii \n",
       "1  2 New Hampshire \n",
       "2   3 South Dakota \n",
       "3  4 Massachusetts \n",
       "4       5 New York "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfr1 = pd.DataFrame(r1)\n",
    "dfr1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "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>environmental_rank</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Hawaii</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>New Hampshire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>South Dakota</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Massachusetts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Nebraska</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Rhode Island</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>North Dakota</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Vermont</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>Minnesota</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>Maryland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>Idaho</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>Wyoming</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>Maine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>Kansas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17</td>\n",
       "      <td>Wisconsin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18</td>\n",
       "      <td>Florida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19</td>\n",
       "      <td>Virginia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>Iowa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>21</td>\n",
       "      <td>Missouri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>22</td>\n",
       "      <td>Mississippi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>23</td>\n",
       "      <td>Colorado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>24</td>\n",
       "      <td>Montana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>25</td>\n",
       "      <td>Georgia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>26</td>\n",
       "      <td>South Carolina</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>27</td>\n",
       "      <td>North Carolina</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>28</td>\n",
       "      <td>Connecticut</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>29</td>\n",
       "      <td>Kentucky</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>30</td>\n",
       "      <td>Arkansas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>31</td>\n",
       "      <td>New Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>32</td>\n",
       "      <td>Michigan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>33</td>\n",
       "      <td>New Jersey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>34</td>\n",
       "      <td>Oklahoma</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>35</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>36</td>\n",
       "      <td>West Virginia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>37</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>38</td>\n",
       "      <td>Pennsylvania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>39</td>\n",
       "      <td>Tennessee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>40</td>\n",
       "      <td>Texas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>41</td>\n",
       "      <td>Arizona</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>42</td>\n",
       "      <td>Oregon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>43</td>\n",
       "      <td>Illinois</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>44</td>\n",
       "      <td>Ohio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>45</td>\n",
       "      <td>Delaware</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>46</td>\n",
       "      <td>Alaska</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>47</td>\n",
       "      <td>Utah</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>48</td>\n",
       "      <td>Indiana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>49</td>\n",
       "      <td>Louisiana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>50</td>\n",
       "      <td>Nevada</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   environmental_rank            state\n",
       "0                   1          Hawaii \n",
       "1                   2   New Hampshire \n",
       "2                   3    South Dakota \n",
       "3                   4   Massachusetts \n",
       "4                   5        New York \n",
       "5                   6        Nebraska \n",
       "6                   7    Rhode Island \n",
       "7                   8    North Dakota \n",
       "8                   9         Vermont \n",
       "9                  10       Minnesota \n",
       "10                 11        Maryland \n",
       "11                 12           Idaho \n",
       "12                 13         Wyoming \n",
       "13                 14           Maine \n",
       "14                 15      Washington \n",
       "15                 16          Kansas \n",
       "16                 17       Wisconsin \n",
       "17                 18         Florida \n",
       "18                 19        Virginia \n",
       "19                 20            Iowa \n",
       "20                 21        Missouri \n",
       "21                 22     Mississippi \n",
       "22                 23        Colorado \n",
       "23                 24         Montana \n",
       "24                 25         Georgia \n",
       "25                 26  South Carolina \n",
       "26                 27  North Carolina \n",
       "27                 28     Connecticut \n",
       "28                 29        Kentucky \n",
       "29                 30        Arkansas \n",
       "30                 31      New Mexico \n",
       "31                 32        Michigan \n",
       "32                 33      New Jersey \n",
       "33                 34        Oklahoma \n",
       "34                 35      California \n",
       "35                 36   West Virginia \n",
       "36                 37         Alabama \n",
       "37                 38    Pennsylvania \n",
       "38                 39       Tennessee \n",
       "39                 40           Texas \n",
       "40                 41         Arizona \n",
       "41                 42          Oregon \n",
       "42                 43        Illinois \n",
       "43                 44            Ohio \n",
       "44                 45        Delaware \n",
       "45                 46          Alaska \n",
       "46                 47            Utah \n",
       "47                 48         Indiana \n",
       "48                 49       Louisiana \n",
       "49                 50          Nevada "
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "green_states = pd.DataFrame(dfr1[0].str.split(' ',1).tolist())\n",
    "green_states = green_states.rename(columns={0: 'environmental_rank', 1: 'state'})\n",
    "\n",
    "green_states"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.quit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now I'll export it to csv."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "green_states.to_csv('green_states.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Fourth source: Marijuana legalization laws by state\n",
    "\n",
    "http://pdaps.org/datasets/recreational-marijuana-laws"
   ]
  },
  {
   "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>Jurisdictions</th>\n",
       "      <th>Effective Date</th>\n",
       "      <th>Valid Through Date</th>\n",
       "      <th>rm-rmlaw_Yes</th>\n",
       "      <th>rm-rmlaw_No</th>\n",
       "      <th>rm-age_21</th>\n",
       "      <th>rm-regulatoryagency_Liquor Control Board</th>\n",
       "      <th>rm-regulatoryagency_Department of Revenue</th>\n",
       "      <th>rm-regulatoryagency_Marijuana Control Board</th>\n",
       "      <th>rm-regulatoryagency_Department of Consumer Affairs</th>\n",
       "      <th>...</th>\n",
       "      <th>rm-excisetaxrate_37%</th>\n",
       "      <th>rm-excisetaxrate_$35 per ounce of usable marijuana</th>\n",
       "      <th>rm-excisetaxrate_$50 per ounce of usable marijuana</th>\n",
       "      <th>rm-excisetaxrate_$10 per ounce of marijuana leaves</th>\n",
       "      <th>rm-excisetaxrate_$5 per immature marijuana plant</th>\n",
       "      <th>rm-salestax_Yes</th>\n",
       "      <th>rm-salestax_No</th>\n",
       "      <th>rm-salestaxrate_3.75%</th>\n",
       "      <th>rm-salestaxrate_10%</th>\n",
       "      <th>rm-salestaxrate_17%</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>2014-10-01</td>\n",
       "      <td>2017-02-01</td>\n",
       "      <td>.</td>\n",
       "      <td>1</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>...</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>2014-10-01</td>\n",
       "      <td>2015-02-23</td>\n",
       "      <td>.</td>\n",
       "      <td>1</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>...</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>2015-02-24</td>\n",
       "      <td>2015-05-04</td>\n",
       "      <td>1</td>\n",
       "      <td>.</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>...</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>1</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>1</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>2015-05-05</td>\n",
       "      <td>2016-02-20</td>\n",
       "      <td>1</td>\n",
       "      <td>.</td>\n",
       "      <td>1</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>1</td>\n",
       "      <td>.</td>\n",
       "      <td>...</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>1</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>1</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>2016-07-29</td>\n",
       "      <td>2016-10-03</td>\n",
       "      <td>1</td>\n",
       "      <td>.</td>\n",
       "      <td>1</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>1</td>\n",
       "      <td>.</td>\n",
       "      <td>...</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>1</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>1</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 90 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Jurisdictions Effective Date Valid Through Date rm-rmlaw_Yes rm-rmlaw_No  \\\n",
       "0       Alabama     2014-10-01         2017-02-01            .           1   \n",
       "1        Alaska     2014-10-01         2015-02-23            .           1   \n",
       "2        Alaska     2015-02-24         2015-05-04            1           .   \n",
       "3        Alaska     2015-05-05         2016-02-20            1           .   \n",
       "4        Alaska     2016-07-29         2016-10-03            1           .   \n",
       "\n",
       "  rm-age_21 rm-regulatoryagency_Liquor Control Board  \\\n",
       "0         .                                        .   \n",
       "1         .                                        .   \n",
       "2         1                                        1   \n",
       "3         1                                        .   \n",
       "4         1                                        .   \n",
       "\n",
       "  rm-regulatoryagency_Department of Revenue  \\\n",
       "0                                         .   \n",
       "1                                         .   \n",
       "2                                         .   \n",
       "3                                         .   \n",
       "4                                         .   \n",
       "\n",
       "  rm-regulatoryagency_Marijuana Control Board  \\\n",
       "0                                           .   \n",
       "1                                           .   \n",
       "2                                           .   \n",
       "3                                           1   \n",
       "4                                           1   \n",
       "\n",
       "  rm-regulatoryagency_Department of Consumer Affairs  ...  \\\n",
       "0                                                  .  ...   \n",
       "1                                                  .  ...   \n",
       "2                                                  .  ...   \n",
       "3                                                  .  ...   \n",
       "4                                                  .  ...   \n",
       "\n",
       "  rm-excisetaxrate_37% rm-excisetaxrate_$35 per ounce of usable marijuana  \\\n",
       "0                    .                                                  .   \n",
       "1                    .                                                  .   \n",
       "2                    .                                                  .   \n",
       "3                    .                                                  .   \n",
       "4                    .                                                  .   \n",
       "\n",
       "  rm-excisetaxrate_$50 per ounce of usable marijuana  \\\n",
       "0                                                  .   \n",
       "1                                                  .   \n",
       "2                                                  1   \n",
       "3                                                  1   \n",
       "4                                                  1   \n",
       "\n",
       "  rm-excisetaxrate_$10 per ounce of marijuana leaves  \\\n",
       "0                                                  .   \n",
       "1                                                  .   \n",
       "2                                                  .   \n",
       "3                                                  .   \n",
       "4                                                  .   \n",
       "\n",
       "  rm-excisetaxrate_$5 per immature marijuana plant rm-salestax_Yes  \\\n",
       "0                                                .               .   \n",
       "1                                                .               .   \n",
       "2                                                .               .   \n",
       "3                                                .               .   \n",
       "4                                                .               .   \n",
       "\n",
       "  rm-salestax_No rm-salestaxrate_3.75% rm-salestaxrate_10% rm-salestaxrate_17%  \n",
       "0              .                     .                   .                   .  \n",
       "1              .                     .                   .                   .  \n",
       "2              1                     .                   .                   .  \n",
       "3              1                     .                   .                   .  \n",
       "4              1                     .                   .                   .  \n",
       "\n",
       "[5 rows x 90 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load raw data\n",
    "marijuana_states = pd.read_excel('marijuana_states.xlsx')\n",
    "marijuana_states.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now I'll prepare the data (with the help of it's data dictionary) so it's clean enough to join with all the other data I've collected thus far."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# quick check for any nulls\n",
    "marijuana_states.isnull().any().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Colorado                16\n",
       "Washington              15\n",
       "Oregon                  11\n",
       "Alaska                   8\n",
       "District of Columbia     2\n",
       "Massachusetts            2\n",
       "California               2\n",
       "Nevada                   2\n",
       "Maine                    2\n",
       "Rhode Island             1\n",
       "Michigan                 1\n",
       "Missouri                 1\n",
       "Oklahoma                 1\n",
       "Georgia                  1\n",
       "South Dakota             1\n",
       "Montana                  1\n",
       "Illinois                 1\n",
       "Ohio                     1\n",
       "North Carolina           1\n",
       "Idaho                    1\n",
       "Tennessee                1\n",
       "Delaware                 1\n",
       "New Hampshire            1\n",
       "Nebraska                 1\n",
       "Texas                    1\n",
       "Maryland                 1\n",
       "Arkansas                 1\n",
       "New Mexico               1\n",
       "Hawaii                   1\n",
       "Arizona                  1\n",
       "Mississippi              1\n",
       "West Virginia            1\n",
       "New Jersey               1\n",
       "North Dakota             1\n",
       "Kansas                   1\n",
       "Wyoming                  1\n",
       "Wisconsin                1\n",
       "Minnesota                1\n",
       "Louisiana                1\n",
       "Iowa                     1\n",
       "Florida                  1\n",
       "South Carolina           1\n",
       "Kentucky                 1\n",
       "Connecticut              1\n",
       "Alabama                  1\n",
       "Vermont                  1\n",
       "Virginia                 1\n",
       "Indiana                  1\n",
       "Utah                     1\n",
       "New York                 1\n",
       "Pennsylvania             1\n",
       "Name: Jurisdictions, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# are the jurisdictions just states or also cities?\n",
    "marijuana_states.Jurisdictions.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "51"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# how many jurisdictions are there?\n",
    "marijuana_states.Jurisdictions.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Does the state have a law authorizing recreational marijuana? -- ```rm-rmlaw```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Alaska', 'California', 'Colorado', 'District of Columbia',\n",
       "       'Maine', 'Massachusetts', 'Nevada', 'Oregon', 'Washington'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "marijuana_states[marijuana_states['rm-rmlaw_Yes'] == 1].Jurisdictions.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0            Alabama\n",
       "1             Alaska\n",
       "2             Alaska\n",
       "3             Alaska\n",
       "4             Alaska\n",
       "           ...      \n",
       "97        Washington\n",
       "98        Washington\n",
       "99     West Virginia\n",
       "100        Wisconsin\n",
       "101          Wyoming\n",
       "Name: Jurisdictions, Length: 102, dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "marijuana_states.Jurisdictions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>state</th>\n",
       "      <th>rec_marij</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Colorado</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Connecticut</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Delaware</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Florida</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Georgia</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Hawaii</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Idaho</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Illinois</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Iowa</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Kansas</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Kentucky</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Louisiana</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Maine</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Maryland</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Michigan</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Minnesota</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Mississippi</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Missouri</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Montana</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Nebraska</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Nevada</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>New Hampshire</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>New Jersey</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>New Mexico</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>New York</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>North Dakota</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Ohio</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Oklahoma</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Oregon</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>South Carolina</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>South Dakota</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Tennessee</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Texas</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Utah</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Vermont</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Washington</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   state rec_marij\n",
       "0                Alabama         .\n",
       "1                 Alaska         .\n",
       "2                Arizona         .\n",
       "3               Arkansas         .\n",
       "4             California         .\n",
       "5               Colorado         1\n",
       "6            Connecticut         .\n",
       "7               Delaware         .\n",
       "8   District of Columbia         1\n",
       "9                Florida         .\n",
       "10               Georgia         .\n",
       "11                Hawaii         .\n",
       "12                 Idaho         .\n",
       "13              Illinois         .\n",
       "14               Indiana         .\n",
       "15                  Iowa         .\n",
       "16                Kansas         .\n",
       "17              Kentucky         .\n",
       "18             Louisiana         .\n",
       "19                 Maine         .\n",
       "20              Maryland         .\n",
       "21         Massachusetts         .\n",
       "22              Michigan         .\n",
       "23             Minnesota         .\n",
       "24           Mississippi         .\n",
       "25              Missouri         .\n",
       "26               Montana         .\n",
       "27              Nebraska         .\n",
       "28                Nevada         .\n",
       "29         New Hampshire         .\n",
       "30            New Jersey         .\n",
       "31            New Mexico         .\n",
       "32              New York         .\n",
       "33        North Carolina         .\n",
       "34          North Dakota         .\n",
       "35                  Ohio         .\n",
       "36              Oklahoma         .\n",
       "37                Oregon         .\n",
       "38          Pennsylvania         .\n",
       "39          Rhode Island         .\n",
       "40        South Carolina         .\n",
       "41          South Dakota         .\n",
       "42             Tennessee         .\n",
       "43                 Texas         .\n",
       "44                  Utah         .\n",
       "45               Vermont         .\n",
       "46              Virginia         .\n",
       "47            Washington         1\n",
       "48         West Virginia         .\n",
       "49             Wisconsin         .\n",
       "50               Wyoming         ."
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# First, sort\n",
    "drop_dups = marijuana_states.sort_values([\"Jurisdictions\", \"Valid Through Date\"])\n",
    "# Then drop duplicates\n",
    "drop_dups = drop_dups.drop_duplicates(subset=[\"Jurisdictions\"])\n",
    "\n",
    "# set up columns I ideally want to have\n",
    "temp = pd.DataFrame(columns=['state', 'rec_marij'])\n",
    "\n",
    "temp['rec_marij'] = drop_dups['rm-rmlaw_Yes']\n",
    "temp['state'] = drop_dups['Jurisdictions']\n",
    "temp = temp.reset_index()\n",
    "temp = temp.drop(columns=['index'])\n",
    "\n",
    "temp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Fifth source: "
   ]
  },
  {
   "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}