Data Wrangling(Python) - Colleges for Maria.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PROJECT LAYOUT\n",
    "\n",
    "#### Aim: To filter out colleges from all over the US according to Maria's preferences, which are that the colleges must:\n",
    "#### 1. be in an urban(metro) area\n",
    "#### 2. be in a city that ranks 75th percentile or higher on Kauffman's start-up rankings.\n",
    "#### 3. be below 50th percentile in overall crime.\n",
    "#### 4. offer a 2-year or 4-year degree in Information Technology/Science."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# A. Startup Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Because Maria wants colleges to be in Metro cities, a filtering on that basis would leave a smaller data set\n",
    "#### in the other two files, making the analysis convenient"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Import Libraries\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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>Location</th>\n",
       "      <th>Index Year</th>\n",
       "      <th>Breakdown</th>\n",
       "      <th>Characteristic</th>\n",
       "      <th>Rank</th>\n",
       "      <th>z_rate</th>\n",
       "      <th>z_oppshare</th>\n",
       "      <th>z_startup_den</th>\n",
       "      <th>norm_rate</th>\n",
       "      <th>norm_opp</th>\n",
       "      <th>...</th>\n",
       "      <th>rank_diff</th>\n",
       "      <th>index_last_y</th>\n",
       "      <th>index_diff</th>\n",
       "      <th>Location_name</th>\n",
       "      <th>Category</th>\n",
       "      <th>OppShare_New_Entrp_web</th>\n",
       "      <th>Rate_New_Entrp_web</th>\n",
       "      <th>location_category</th>\n",
       "      <th>index_diff_text</th>\n",
       "      <th>dataset</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1996.0</td>\n",
       "      <td>Education</td>\n",
       "      <td>Less than High School</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United States</td>\n",
       "      <td>National</td>\n",
       "      <td>NaN</td>\n",
       "      <td>393.46829</td>\n",
       "      <td>the</td>\n",
       "      <td>.</td>\n",
       "      <td>national_demog</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1998.0</td>\n",
       "      <td>Age</td>\n",
       "      <td>Ages 35-44</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United States</td>\n",
       "      <td>National</td>\n",
       "      <td>7.9575</td>\n",
       "      <td>305.42319</td>\n",
       "      <td>the</td>\n",
       "      <td>.</td>\n",
       "      <td>national_demog</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>Race</td>\n",
       "      <td>White</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United States</td>\n",
       "      <td>National</td>\n",
       "      <td>7.6008</td>\n",
       "      <td>293.58240</td>\n",
       "      <td>the</td>\n",
       "      <td>.</td>\n",
       "      <td>national_demog</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>Race</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United States</td>\n",
       "      <td>National</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>the</td>\n",
       "      <td>.</td>\n",
       "      <td>national_demog</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>Race</td>\n",
       "      <td>Latino</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United States</td>\n",
       "      <td>National</td>\n",
       "      <td>6.7777</td>\n",
       "      <td>515.29340</td>\n",
       "      <td>the</td>\n",
       "      <td>.</td>\n",
       "      <td>national_demog</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Location  Index Year  Breakdown         Characteristic  Rank  z_rate  \\\n",
       "0         0      1996.0  Education  Less than High School   NaN     NaN   \n",
       "1         0      1998.0        Age             Ages 35-44   NaN     NaN   \n",
       "2         0      2011.0       Race                  White   NaN     NaN   \n",
       "3         0      2011.0       Race                  Other   NaN     NaN   \n",
       "4         0      2011.0       Race                 Latino   NaN     NaN   \n",
       "\n",
       "   z_oppshare  z_startup_den  norm_rate  norm_opp       ...        rank_diff  \\\n",
       "0         NaN            NaN        NaN       NaN       ...              NaN   \n",
       "1         NaN            NaN        NaN       NaN       ...              NaN   \n",
       "2         NaN            NaN        NaN       NaN       ...              NaN   \n",
       "3         NaN            NaN        NaN       NaN       ...              NaN   \n",
       "4         NaN            NaN        NaN       NaN       ...              NaN   \n",
       "\n",
       "   index_last_y  index_diff  Location_name  Category  OppShare_New_Entrp_web  \\\n",
       "0           NaN         NaN  United States  National                     NaN   \n",
       "1           NaN         NaN  United States  National                  7.9575   \n",
       "2           NaN         NaN  United States  National                  7.6008   \n",
       "3           NaN         NaN  United States  National                     NaN   \n",
       "4           NaN         NaN  United States  National                  6.7777   \n",
       "\n",
       "   Rate_New_Entrp_web  location_category  index_diff_text         dataset  \n",
       "0           393.46829                the                .  national_demog  \n",
       "1           305.42319                the                .  national_demog  \n",
       "2           293.58240                the                .  national_demog  \n",
       "3                 NaN                the                .  national_demog  \n",
       "4           515.29340                the                .  national_demog  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "startup = pd.read_csv(\"Startup Activity.csv\")\n",
    "startup.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cleaning the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['United States', 'Alabama', 'Alaska', 'Arizona', 'Arkansas',\n",
       "       'California', 'Colorado', 'Connecticut', 'Delaware', 'Florida',\n",
       "       'Georgia', 'Hawaii', 'Idaho', 'Illinois', 'Indiana', 'Iowa',\n",
       "       'Kansas', 'Kentucky', 'Louisiana', 'Maine', 'Maryland',\n",
       "       'Massachusetts', 'Michigan', 'Minnesota', 'Mississippi',\n",
       "       'Missouri', 'Montana', 'Nebraska', 'Nevada', 'New Hampshire',\n",
       "       'New Jersey', 'New Mexico', 'New York', 'North Carolina',\n",
       "       'North Dakota', 'Ohio', 'Oklahoma', 'Oregon', 'Pennsylvania',\n",
       "       'Rhode Island', 'South Carolina', 'South Dakota', 'Tennessee',\n",
       "       'Texas', 'Utah', 'Vermont', 'Virginia', 'Washington',\n",
       "       'West Virginia', 'Wisconsin', 'Wyoming',\n",
       "       'Atlanta-Sandy Springs-Marietta', 'Austin-Round Rock-San Marcos',\n",
       "       'Baltimore-Towson', 'Boston-Cambridge-Quincy',\n",
       "       'Charlotte-Gastonia-Rock Hill', 'Chicago-Joliet-Naperville',\n",
       "       'Cincinnati-Middletown', 'Cleveland-Elyria-Mentor', 'Columbus',\n",
       "       'Dallas-Fort Worth-Arlington', 'Denver-Aurora-Broomfield',\n",
       "       'Detroit-Warren-Livonia', 'Houston-Sugar Land-Baytown',\n",
       "       'Indianapolis-Carmel', 'Jacksonville', 'Kansas City',\n",
       "       'Las Vegas-Paradise', 'Los Angeles-Long Beach-Santa Ana',\n",
       "       'Miami-Fort Lauderdale-Pompano Beach',\n",
       "       'Milwaukee-Waukesha-West Allis',\n",
       "       'Minneapolis-St. Paul-Bloomington',\n",
       "       'Nashville-Davidson-Murfreesboro-Franklin',\n",
       "       'New York-Northern New Jersey-Long Island',\n",
       "       'Orlando-Kissimmee-Sanford', 'Philadelphia-Camden-Wilmington',\n",
       "       'Phoenix-Mesa-Glendale', 'Pittsburgh',\n",
       "       'Portland-Vancouver-Hillsboro',\n",
       "       'Providence-New Bedford-Fall River',\n",
       "       'Riverside-San Bernardino-Ontario',\n",
       "       'Sacramento-Arden-Arcade-Roseville', 'St. Louis',\n",
       "       'San Antonio-New Braunfels', 'San Diego-Carlsbad-San Marcos',\n",
       "       'San Francisco-Oakland-Fremont', 'San Jose-Sunnyvale-Santa Clara',\n",
       "       'Seattle-Tacoma-Bellevue', 'Tampa-St. Petersburg-Clearwater',\n",
       "       'Virginia Beach-Norfolk-Newport News',\n",
       "       'Washington-Arlington-Alexandria'], dtype=object)"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Since the data is for year 2015 filtering for 2015\n",
    "# And Dropping irrelevant columns\n",
    "startup = startup[[\"Index Year\", \"Rank\", \"Location_name\"]][startup[\"Index Year\"] == 2015]\n",
    "\n",
    "#Dropping \"Index Year\" column, since it contains the single value '2015'.\n",
    "startup = startup.drop(\"Index Year\", axis=1)\n",
    "\n",
    "# Data file indicates presence of states in the city column as well.\n",
    "#Fetching values to remove that\n",
    "startup[\"Location_name\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Removing country and state names\n",
    "\n",
    "states = [\"United States\", \"Alabama\", \"Alaska\", \"Arkansas\", \"Arizona\", \"California\", \"Colorado\", \"Connecticut\", \"Delaware\", \"Florida\", \"Georgia\", \"Hawaii\", \"Idaho\", \"Illinois\", \"Indiana\", \"Iowa\", \"Kansas\", \"Kentucky\", \"Louisiana\", \"Maine\", \"Maryland\", \"Massachusetts\", \"Michigan\", \"Minnesota\", \"Mississippi\", \"Missouri\", \"Montana\", \"Nebraska\", \"Nevada\", \"New Hampshire\", \"New Jersey\", \"New Mexico\", \"New York\", \"North Carolina\", \"North Dakota\", \"Ohio\", \"Oklahoma\", \"Oregon\", \"Pennsylvania\", \"Rhode Island\", \"South Carolina\", \"South Dakota\", \"Tennessee\", \"Texas\", \"Utah\", \"Vermont\", \"Virginia\", \"Washington\", \"West Virginia\", \"Wisconsin\", \"Wyoming\"]\n",
    "\n",
    "for name in states:\n",
    "    startup = startup[startup[\"Location_name\"] != name]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting top 25 percentile cities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(40, 2)"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Sorting cities by their Startup Activity ranks\n",
    "\n",
    "startup = startup.sort_values(by=\"Rank\")\n",
    "startup.shape"
   ]
  },
  {
   "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>Rank</th>\n",
       "      <th>Location_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2413</th>\n",
       "      <td>1.0</td>\n",
       "      <td>Austin-Round Rock-San Marcos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3042</th>\n",
       "      <td>2.0</td>\n",
       "      <td>Miami-Fort Lauderdale-Pompano Beach</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3683</th>\n",
       "      <td>3.0</td>\n",
       "      <td>San Jose-Sunnyvale-Santa Clara</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2991</th>\n",
       "      <td>4.0</td>\n",
       "      <td>Los Angeles-Long Beach-Santa Ana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2730</th>\n",
       "      <td>5.0</td>\n",
       "      <td>Denver-Aurora-Broomfield</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2965</th>\n",
       "      <td>6.0</td>\n",
       "      <td>Las Vegas-Paradise</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2821</th>\n",
       "      <td>7.0</td>\n",
       "      <td>Houston-Sugar Land-Baytown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3632</th>\n",
       "      <td>8.0</td>\n",
       "      <td>San Francisco-Oakland-Fremont</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3574</th>\n",
       "      <td>9.0</td>\n",
       "      <td>San Antonio-New Braunfels</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3194</th>\n",
       "      <td>10.0</td>\n",
       "      <td>New York-Northern New Jersey-Long Island</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Rank                             Location_name\n",
       "2413   1.0              Austin-Round Rock-San Marcos\n",
       "3042   2.0       Miami-Fort Lauderdale-Pompano Beach\n",
       "3683   3.0            San Jose-Sunnyvale-Santa Clara\n",
       "2991   4.0          Los Angeles-Long Beach-Santa Ana\n",
       "2730   5.0                  Denver-Aurora-Broomfield\n",
       "2965   6.0                        Las Vegas-Paradise\n",
       "2821   7.0                Houston-Sugar Land-Baytown\n",
       "3632   8.0             San Francisco-Oakland-Fremont\n",
       "3574   9.0                 San Antonio-New Braunfels\n",
       "3194  10.0  New York-Northern New Jersey-Long Island"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Selecting top 25 percentile values.\n",
    "# Since the number of values is 40, top 25 percentile are the top 10 values\n",
    "startup = startup[:10]\n",
    "startup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fetching Metro area names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "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",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2413</th>\n",
       "      <td>Austin</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>San Marcos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3042</th>\n",
       "      <td>Miami</td>\n",
       "      <td>Fort Lauderdale</td>\n",
       "      <td>Pompano Beach</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3683</th>\n",
       "      <td>San Jose</td>\n",
       "      <td>Sunnyvale</td>\n",
       "      <td>Santa Clara</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2991</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>Long Beach</td>\n",
       "      <td>Santa Ana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2730</th>\n",
       "      <td>Denver</td>\n",
       "      <td>Aurora</td>\n",
       "      <td>Broomfield</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2965</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>Paradise</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2821</th>\n",
       "      <td>Houston</td>\n",
       "      <td>Sugar Land</td>\n",
       "      <td>Baytown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3632</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>Oakland</td>\n",
       "      <td>Fremont</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3574</th>\n",
       "      <td>San Antonio</td>\n",
       "      <td>New Braunfels</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3194</th>\n",
       "      <td>New York</td>\n",
       "      <td>Northern New Jersey</td>\n",
       "      <td>Long Island</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  0                    1              2\n",
       "2413         Austin           Round Rock     San Marcos\n",
       "3042          Miami      Fort Lauderdale  Pompano Beach\n",
       "3683       San Jose            Sunnyvale    Santa Clara\n",
       "2991    Los Angeles           Long Beach      Santa Ana\n",
       "2730         Denver               Aurora     Broomfield\n",
       "2965      Las Vegas             Paradise           None\n",
       "2821        Houston           Sugar Land        Baytown\n",
       "3632  San Francisco              Oakland        Fremont\n",
       "3574    San Antonio        New Braunfels           None\n",
       "3194       New York  Northern New Jersey    Long Island"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Since Maria prefers Metro areas, which can be seen as the first city names before the first '-' delimeter,\n",
    "## Extracting the Metro names only\n",
    "\n",
    "A = startup[\"Location_name\"].str.split(\"-\", expand=True)\n",
    "A"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>Startup_Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2413</th>\n",
       "      <td>Austin</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3042</th>\n",
       "      <td>Miami</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3683</th>\n",
       "      <td>San Jose</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2991</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2730</th>\n",
       "      <td>Denver</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2965</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2821</th>\n",
       "      <td>Houston</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3632</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3574</th>\n",
       "      <td>San Antonio</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3194</th>\n",
       "      <td>New York</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               city  Startup_Rank\n",
       "2413         Austin           1.0\n",
       "3042          Miami           2.0\n",
       "3683       San Jose           3.0\n",
       "2991    Los Angeles           4.0\n",
       "2730         Denver           5.0\n",
       "2965      Las Vegas           6.0\n",
       "2821        Houston           7.0\n",
       "3632  San Francisco           8.0\n",
       "3574    San Antonio           9.0\n",
       "3194       New York          10.0"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Concatenating the dataframe containing split names with startup dataframe\n",
    "startup = pd.concat([A, startup], axis=1)\n",
    "\n",
    "startup.columns = [\"city\", \"C\", \"D\", \"Rank\", \"Location_name\"]\n",
    "\n",
    "# Keeping only the Metro city name\n",
    "startup[\"city\"] = startup[\"city\"].astype(str)\n",
    "\n",
    "startup = startup[[\"city\", \"Rank\"]]\n",
    "\n",
    "startup.columns = [\"city\", \"Startup_Rank\"]\n",
    "startup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# B. CRIME DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "crime = pd.read_csv(\"crime_2015.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "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>murder</th>\n",
       "      <th>rape</th>\n",
       "      <th>robbery</th>\n",
       "      <th>aggravatedassault</th>\n",
       "      <th>burglary</th>\n",
       "      <th>theft</th>\n",
       "      <th>motorvehicletheft</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.3</td>\n",
       "      <td>56.0</td>\n",
       "      <td>78.4</td>\n",
       "      <td>272.8</td>\n",
       "      <td>852.0</td>\n",
       "      <td>2493.6</td>\n",
       "      <td>263.4</td>\n",
       "      <td>Abilene</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5.1</td>\n",
       "      <td>38.2</td>\n",
       "      <td>75.2</td>\n",
       "      <td>119.8</td>\n",
       "      <td>575.3</td>\n",
       "      <td>1853.0</td>\n",
       "      <td>124.1</td>\n",
       "      <td>Akron</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7.8</td>\n",
       "      <td>30.4</td>\n",
       "      <td>157.9</td>\n",
       "      <td>471.8</td>\n",
       "      <td>1099.6</td>\n",
       "      <td>2652.8</td>\n",
       "      <td>141.7</td>\n",
       "      <td>Albany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2.5</td>\n",
       "      <td>28.2</td>\n",
       "      <td>20.7</td>\n",
       "      <td>63.0</td>\n",
       "      <td>484.6</td>\n",
       "      <td>2476.1</td>\n",
       "      <td>247.7</td>\n",
       "      <td>Albany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6.1</td>\n",
       "      <td>63.8</td>\n",
       "      <td>206.7</td>\n",
       "      <td>516.0</td>\n",
       "      <td>883.4</td>\n",
       "      <td>3047.6</td>\n",
       "      <td>676.9</td>\n",
       "      <td>Albuquerque</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   murder  rape  robbery  aggravatedassault  burglary   theft  \\\n",
       "0     5.3  56.0     78.4              272.8     852.0  2493.6   \n",
       "1     5.1  38.2     75.2              119.8     575.3  1853.0   \n",
       "2     7.8  30.4    157.9              471.8    1099.6  2652.8   \n",
       "3     2.5  28.2     20.7               63.0     484.6  2476.1   \n",
       "4     6.1  63.8    206.7              516.0     883.4  3047.6   \n",
       "\n",
       "   motorvehicletheft         city  \n",
       "0              263.4      Abilene  \n",
       "1              124.1        Akron  \n",
       "2              141.7       Albany  \n",
       "3              247.7       Albany  \n",
       "4              676.9  Albuquerque  "
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Dropping irrelevant columns\n",
    "# violent crime and property crime are sums of individual crime categories\n",
    "\n",
    "crime = crime.drop([\"msa\", \"violentcrime\", \"propertycrime\", \"state\"], axis=1)\n",
    "\n",
    "# Converting all the columns to numeric forms\n",
    "\n",
    "cols = crime.columns.drop(\"city\")\n",
    "\n",
    "for col in cols:\n",
    "    crime[col] = pd.to_numeric(crime[col], errors=\"coerce\")\n",
    "    \n",
    "crime.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [],
   "source": [
    "crime = crime.fillna(crime.mean())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filtering crime dataframe for Metro cities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "Metro_cities = [\"Austin\", \"Miami\", \"San Jose\", \"Los Angeles\", \"Denver\", \"Las Vegas\", \"Houston\", \"San Francisco\", \"San Antonio\", \"New York\"]\n",
    "\n",
    "crime = crime[crime[\"city\"].isin(Metro_cities)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating a CRIME ranking metric"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "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>city_crime_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Austin</td>\n",
       "      <td>213.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>Denver</td>\n",
       "      <td>260.676859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>Houston</td>\n",
       "      <td>285.879640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>341.755000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>235.255000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>251.465000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>213</th>\n",
       "      <td>Miami</td>\n",
       "      <td>289.945000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>Miami</td>\n",
       "      <td>326.525000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>294</th>\n",
       "      <td>San Antonio</td>\n",
       "      <td>326.060000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>296</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>332.130000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>298</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>361.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300</th>\n",
       "      <td>San Jose</td>\n",
       "      <td>220.360000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              city  city_crime_score\n",
       "17          Austin        213.000000\n",
       "91          Denver        260.676859\n",
       "149        Houston        285.879640\n",
       "184      Las Vegas        341.755000\n",
       "196    Los Angeles        235.255000\n",
       "198    Los Angeles        251.465000\n",
       "213          Miami        289.945000\n",
       "215          Miami        326.525000\n",
       "294    San Antonio        326.060000\n",
       "296  San Francisco        332.130000\n",
       "298  San Francisco        361.125000\n",
       "300       San Jose        220.360000"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# For this, I assign following weightage to individual crime categories:\n",
    "\n",
    "#murder - 25% - serious\n",
    "#rape - 20% - serious(Maria is a woman)\n",
    "#robbery - 10%\n",
    "#assault - 15% - moderately serious\n",
    "#burglary - 10%\n",
    "#theft - 5%\n",
    "#mvtheft - 15% - moderately serious\n",
    "\n",
    "crime[\"city_crime_score\"] = 0.25*crime[\"murder\"] + 0.2*crime[\"rape\"] + 0.1*crime[\"robbery\"] + 0.15*crime[\"aggravatedassault\"] + 0.1*crime[\"burglary\"] + 0.05*crime[\"theft\"] + 0.15*crime[\"motorvehicletheft\"]\n",
    "\n",
    "## Filtering crime only for crime and crime metric\n",
    "\n",
    "crime = crime[[\"city\", \"city_crime_score\"]]\n",
    "\n",
    "crime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Removing Duplicates and finding mean for repeated values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "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>city_crime_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Austin</td>\n",
       "      <td>213.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Denver</td>\n",
       "      <td>260.676859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Houston</td>\n",
       "      <td>285.879640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>341.755000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>San Antonio</td>\n",
       "      <td>326.060000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>San Jose</td>\n",
       "      <td>220.360000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>243.330000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Miami</td>\n",
       "      <td>308.240000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>346.630000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             city  city_crime_score\n",
       "0          Austin        213.000000\n",
       "1          Denver        260.676859\n",
       "2         Houston        285.879640\n",
       "3       Las Vegas        341.755000\n",
       "8     San Antonio        326.060000\n",
       "11       San Jose        220.360000\n",
       "12    Los Angeles        243.330000\n",
       "13          Miami        308.240000\n",
       "14  San Francisco        346.630000"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Averaging the values for LA, SF and Miami values (Since they fall into two jurisdictions, they come twice)\n",
    "## Crime score to be averaged, duplicate entry to be dropped\n",
    "\n",
    "crime = crime.append({\"city\": \"LA\", \"city_crime_score\": 243.33}, ignore_index=True)\n",
    "crime = crime.append({\"city\": \"MIA\", \"city_crime_score\": 308.24}, ignore_index=True)\n",
    "crime = crime.append({\"city\": \"SF\", \"city_crime_score\": 346.63}, ignore_index=True)\n",
    "\n",
    "crime = crime[crime[\"city\"] != \"Los Angeles\"]\n",
    "crime = crime[crime[\"city\"] != \"Miami\"]\n",
    "crime = crime[crime[\"city\"] != \"San Francisco\"]\n",
    "\n",
    "crime[\"city\"] = crime[\"city\"].replace({\"LA\": \"Los Angeles\", \"MIA\": \"Miami\", \"SF\": \"San Francisco\"})\n",
    "\n",
    "crime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Assigning Ranks and finalizing Crime Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "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>Safety_Crime_Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Austin</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>San Jose</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Denver</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Houston</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Miami</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>San Antonio</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            city  Safety_Crime_Rank\n",
       "0         Austin                  1\n",
       "1       San Jose                  2\n",
       "2    Los Angeles                  3\n",
       "3         Denver                  4\n",
       "4        Houston                  5\n",
       "5          Miami                  6\n",
       "6    San Antonio                  7\n",
       "7      Las Vegas                  8\n",
       "8  San Francisco                  9"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Assigning ranks by sorting by values and then using indexes\n",
    "\n",
    "crime = crime.sort_values(by=\"city_crime_score\", ascending=True)\n",
    "\n",
    "crime = crime.reset_index()\n",
    "\n",
    "crime[\"Safety_Crime_Rank\"] = crime[\"city_crime_score\"].index + 1\n",
    "\n",
    "crime = crime.drop([\"index\", \"city_crime_score\"], axis=1)\n",
    "\n",
    "crime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# C. PREPARING COLLEGE DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\hp\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:2728: DtypeWarning: Columns (7,9,1427,1542,1561,1575,1725,1726,1727,1728,1729) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unitid</th>\n",
       "      <th>opeid</th>\n",
       "      <th>opeid6</th>\n",
       "      <th>instnm</th>\n",
       "      <th>city</th>\n",
       "      <th>stabbr</th>\n",
       "      <th>zip</th>\n",
       "      <th>accredagency</th>\n",
       "      <th>insturl</th>\n",
       "      <th>npcurl</th>\n",
       "      <th>...</th>\n",
       "      <th>nopell_rpy_3yr_rt_supp</th>\n",
       "      <th>female_rpy_3yr_rt_supp</th>\n",
       "      <th>male_rpy_3yr_rt_supp</th>\n",
       "      <th>firstgen_rpy_3yr_rt_supp</th>\n",
       "      <th>notfirstgen_rpy_3yr_rt_supp</th>\n",
       "      <th>c150_l4_pooled_supp</th>\n",
       "      <th>c150_4_pooled_supp</th>\n",
       "      <th>c200_l4_pooled_supp</th>\n",
       "      <th>c200_4_pooled_supp</th>\n",
       "      <th>location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100654</td>\n",
       "      <td>100200</td>\n",
       "      <td>1002</td>\n",
       "      <td>Alabama A &amp; M University</td>\n",
       "      <td>Normal</td>\n",
       "      <td>AL</td>\n",
       "      <td>35762</td>\n",
       "      <td>Southern Association of Colleges and Schools C...</td>\n",
       "      <td>www.aamu.edu/</td>\n",
       "      <td>galileo.aamu.edu/netpricecalculator/npcalc.htm</td>\n",
       "      <td>...</td>\n",
       "      <td>0.45251396648</td>\n",
       "      <td>0.484818805093</td>\n",
       "      <td>0.406</td>\n",
       "      <td>0.423580786026</td>\n",
       "      <td>0.442452830189</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.30871831265508</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.35168556430446</td>\n",
       "      <td>POINT(34.7834 -86.5685)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100663</td>\n",
       "      <td>105200</td>\n",
       "      <td>1052</td>\n",
       "      <td>University of Alabama at Birmingham</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>AL</td>\n",
       "      <td>35294-0110</td>\n",
       "      <td>Southern Association of Colleges and Schools C...</td>\n",
       "      <td>www.uab.edu</td>\n",
       "      <td>www.collegeportraits.org/AL/UAB/estimator/agree</td>\n",
       "      <td>...</td>\n",
       "      <td>0.795454545455</td>\n",
       "      <td>0.760521885522</td>\n",
       "      <td>0.755344418052</td>\n",
       "      <td>0.751552795031</td>\n",
       "      <td>0.765074770863</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.50854980789381</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.4730923834537</td>\n",
       "      <td>POINT(33.5022 -86.8092)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100690</td>\n",
       "      <td>2503400</td>\n",
       "      <td>25034</td>\n",
       "      <td>Amridge University</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>AL</td>\n",
       "      <td>36117-3553</td>\n",
       "      <td>Southern Association of Colleges and Schools C...</td>\n",
       "      <td>www.amridgeuniversity.edu</td>\n",
       "      <td>tcc.noellevitz.com/(S(miwoihs5stz5cpyifh4nczu0...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.775</td>\n",
       "      <td>0.610465116279</td>\n",
       "      <td>0.686567164179</td>\n",
       "      <td>0.632653061224</td>\n",
       "      <td>0.65</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PrivacySuppressed</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PrivacySuppressed</td>\n",
       "      <td>POINT(32.3626 -86.174)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100706</td>\n",
       "      <td>105500</td>\n",
       "      <td>1055</td>\n",
       "      <td>University of Alabama in Huntsville</td>\n",
       "      <td>Huntsville</td>\n",
       "      <td>AL</td>\n",
       "      <td>35899</td>\n",
       "      <td>Southern Association of Colleges and Schools C...</td>\n",
       "      <td>www.uah.edu</td>\n",
       "      <td>finaid.uah.edu/</td>\n",
       "      <td>...</td>\n",
       "      <td>0.85140562249</td>\n",
       "      <td>0.768378650554</td>\n",
       "      <td>0.798440979955</td>\n",
       "      <td>0.778156996587</td>\n",
       "      <td>0.791666666667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.47821134615384</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.49412939796716</td>\n",
       "      <td>POINT(34.7228 -86.6384)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100724</td>\n",
       "      <td>100500</td>\n",
       "      <td>1005</td>\n",
       "      <td>Alabama State University</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>AL</td>\n",
       "      <td>36104-0271</td>\n",
       "      <td>Southern Association of Colleges and Schools C...</td>\n",
       "      <td>www.alasu.edu/email/index.aspx</td>\n",
       "      <td>www.alasu.edu/cost-aid/forms/calculator/index....</td>\n",
       "      <td>...</td>\n",
       "      <td>0.380829015544</td>\n",
       "      <td>0.364423717522</td>\n",
       "      <td>0.286530612245</td>\n",
       "      <td>0.315737051793</td>\n",
       "      <td>0.334633385335</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.25748195545488</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.29365162671232</td>\n",
       "      <td>POINT(32.3643 -86.2957)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 1730 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   unitid    opeid  opeid6                               instnm        city  \\\n",
       "0  100654   100200    1002             Alabama A & M University      Normal   \n",
       "1  100663   105200    1052  University of Alabama at Birmingham  Birmingham   \n",
       "2  100690  2503400   25034                   Amridge University  Montgomery   \n",
       "3  100706   105500    1055  University of Alabama in Huntsville  Huntsville   \n",
       "4  100724   100500    1005             Alabama State University  Montgomery   \n",
       "\n",
       "  stabbr         zip                                       accredagency  \\\n",
       "0     AL       35762  Southern Association of Colleges and Schools C...   \n",
       "1     AL  35294-0110  Southern Association of Colleges and Schools C...   \n",
       "2     AL  36117-3553  Southern Association of Colleges and Schools C...   \n",
       "3     AL       35899  Southern Association of Colleges and Schools C...   \n",
       "4     AL  36104-0271  Southern Association of Colleges and Schools C...   \n",
       "\n",
       "                          insturl  \\\n",
       "0                   www.aamu.edu/   \n",
       "1                     www.uab.edu   \n",
       "2       www.amridgeuniversity.edu   \n",
       "3                     www.uah.edu   \n",
       "4  www.alasu.edu/email/index.aspx   \n",
       "\n",
       "                                              npcurl           ...             \\\n",
       "0     galileo.aamu.edu/netpricecalculator/npcalc.htm           ...              \n",
       "1    www.collegeportraits.org/AL/UAB/estimator/agree           ...              \n",
       "2  tcc.noellevitz.com/(S(miwoihs5stz5cpyifh4nczu0...           ...              \n",
       "3                                    finaid.uah.edu/           ...              \n",
       "4  www.alasu.edu/cost-aid/forms/calculator/index....           ...              \n",
       "\n",
       "   nopell_rpy_3yr_rt_supp  female_rpy_3yr_rt_supp  male_rpy_3yr_rt_supp  \\\n",
       "0           0.45251396648          0.484818805093                 0.406   \n",
       "1          0.795454545455          0.760521885522        0.755344418052   \n",
       "2                   0.775          0.610465116279        0.686567164179   \n",
       "3           0.85140562249          0.768378650554        0.798440979955   \n",
       "4          0.380829015544          0.364423717522        0.286530612245   \n",
       "\n",
       "   firstgen_rpy_3yr_rt_supp  notfirstgen_rpy_3yr_rt_supp  c150_l4_pooled_supp  \\\n",
       "0            0.423580786026               0.442452830189                  NaN   \n",
       "1            0.751552795031               0.765074770863                  NaN   \n",
       "2            0.632653061224                         0.65                  NaN   \n",
       "3            0.778156996587               0.791666666667                  NaN   \n",
       "4            0.315737051793               0.334633385335                  NaN   \n",
       "\n",
       "   c150_4_pooled_supp  c200_l4_pooled_supp  c200_4_pooled_supp  \\\n",
       "0    0.30871831265508                  NaN    0.35168556430446   \n",
       "1    0.50854980789381                  NaN     0.4730923834537   \n",
       "2   PrivacySuppressed                  NaN   PrivacySuppressed   \n",
       "3    0.47821134615384                  NaN    0.49412939796716   \n",
       "4    0.25748195545488                  NaN    0.29365162671232   \n",
       "\n",
       "                  location  \n",
       "0  POINT(34.7834 -86.5685)  \n",
       "1  POINT(33.5022 -86.8092)  \n",
       "2   POINT(32.3626 -86.174)  \n",
       "3  POINT(34.7228 -86.6384)  \n",
       "4  POINT(32.3643 -86.2957)  \n",
       "\n",
       "[5 rows x 1730 columns]"
      ]
     },
     "execution_count": 259,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "college = pd.read_csv(\"collegescorecard.csv\")\n",
    "\n",
    "college.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting Colleges that provide relevant degrees and Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(44, 4)"
      ]
     },
     "execution_count": 260,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Filtering df for college name, city name, and associate or graduate degrees\n",
    "## From the data dictionary, we can see which columns tell us whether the degrees are offered or not\n",
    "## These columns tell us in 0 or 1 terms if the degrees are offered or not\n",
    "\n",
    "college = college[[\"instnm\", \"city\", \"cip11assoc\", \"cip11cert4\"]]\n",
    "\n",
    "# Performing a union of the two columns\n",
    "college = college[(college[\"cip11assoc\"] == 1) | (college[\"cip11cert4\"] == 1)]\n",
    "\n",
    "# Filtering for Metro cities\n",
    "Metro_cities = [\"Austin\", \"Miami\", \"San Jose\", \"Los Angeles\", \"Denver\", \"Las Vegas\", \"Houston\", \"San Francisco\", \"San Antonio\", \"Houston\"]\n",
    "\n",
    "college = college.loc[college[\"city\"].isin(Metro_cities)]\n",
    "\n",
    "college.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# D. Merging Startup, Crime and College Dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "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>instnm</th>\n",
       "      <th>city</th>\n",
       "      <th>cip11assoc</th>\n",
       "      <th>cip11cert4</th>\n",
       "      <th>Safety_Crime_Rank</th>\n",
       "      <th>Startup_Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Heald College-San Francisco</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Los Angeles Southwest College</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Los Angeles Trade Technical College</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Los Angeles City College</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>The Art Institute of California-Argosy Univers...</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              instnm           city  \\\n",
       "0                        Heald College-San Francisco  San Francisco   \n",
       "1                      Los Angeles Southwest College    Los Angeles   \n",
       "2                Los Angeles Trade Technical College    Los Angeles   \n",
       "3                           Los Angeles City College    Los Angeles   \n",
       "4  The Art Institute of California-Argosy Univers...  San Francisco   \n",
       "\n",
       "   cip11assoc  cip11cert4  Safety_Crime_Rank  Startup_Rank  \n",
       "0         1.0         0.0                  9           8.0  \n",
       "1         1.0         0.0                  3           4.0  \n",
       "2         1.0         0.0                  3           4.0  \n",
       "3         1.0         0.0                  3           4.0  \n",
       "4         1.0         0.0                  9           8.0  "
      ]
     },
     "execution_count": 261,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Introducing crime ranks by merging the two dfs - college and crime.\n",
    "## Introducing Startup activity ranks by merging the two dfs - college and crime.\n",
    "\n",
    "college = college[college.columns].merge(crime, 'left')\n",
    "college = college[college.columns].merge(startup, 'left')\n",
    "\n",
    "college.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Combining Crime Rank and Startup Rank in a single rank metric for final rank calculation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 262,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>instnm</th>\n",
       "      <th>city</th>\n",
       "      <th>cip11assoc</th>\n",
       "      <th>cip11cert4</th>\n",
       "      <th>Safety_Crime_Rank</th>\n",
       "      <th>Startup_Rank</th>\n",
       "      <th>Rank_Score</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21</td>\n",
       "      <td>ITT Technical Institute-Austin</td>\n",
       "      <td>Austin</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>38</td>\n",
       "      <td>The Art Institute of Austin</td>\n",
       "      <td>Austin</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>15</td>\n",
       "      <td>Concordia University-Texas</td>\n",
       "      <td>Austin</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>41</td>\n",
       "      <td>South University-Austin</td>\n",
       "      <td>Austin</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12</td>\n",
       "      <td>Riverland Community College</td>\n",
       "      <td>Austin</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index                          instnm    city  cip11assoc  cip11cert4  \\\n",
       "0     21  ITT Technical Institute-Austin  Austin         1.0         0.0   \n",
       "1     38     The Art Institute of Austin  Austin         1.0         0.0   \n",
       "2     15      Concordia University-Texas  Austin         1.0         0.0   \n",
       "3     41         South University-Austin  Austin         1.0         0.0   \n",
       "4     12     Riverland Community College  Austin         2.0         1.0   \n",
       "\n",
       "   Safety_Crime_Rank  Startup_Rank  Rank_Score  Rank  \n",
       "0                  1           1.0         2.0     1  \n",
       "1                  1           1.0         2.0     2  \n",
       "2                  1           1.0         2.0     3  \n",
       "3                  1           1.0         2.0     4  \n",
       "4                  1           1.0         2.0     5  "
      ]
     },
     "execution_count": 262,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Now, we have to determine the rank of these colleges, for which we need an metric\n",
    "# Since low crime and high startup activity are equally important, we add up the two\n",
    "# And then sort the df by the new metric\n",
    "\n",
    "college[\"Rank_Score\"] = (college[\"Safety_Crime_Rank\"] + college[\"Startup_Rank\"])\n",
    "college = college.sort_values(by=\"Rank_Score\", ascending=True)\n",
    "\n",
    "college = college.reset_index()\n",
    "college[\"Rank\"] = college[\"Rank_Score\"].index + 1\n",
    "\n",
    "college.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 263,
   "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>instnm</th>\n",
       "      <th>city</th>\n",
       "      <th>Rank_Score</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ITT Technical Institute-Austin</td>\n",
       "      <td>Austin</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>The Art Institute of Austin</td>\n",
       "      <td>Austin</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Concordia University-Texas</td>\n",
       "      <td>Austin</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>South University-Austin</td>\n",
       "      <td>Austin</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Riverland Community College</td>\n",
       "      <td>Austin</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Carrington College California-San Jose</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Argosy University-Los Angeles</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Advanced Computing Institute</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Los Angeles ORT College-Los Angeles Campus</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>7.0</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Los Angeles City College</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>7.0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Los Angeles Trade Technical College</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>7.0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Los Angeles Southwest College</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>7.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Florida Career College-Miami</td>\n",
       "      <td>Miami</td>\n",
       "      <td>8.0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>AI Miami International University of Art and D...</td>\n",
       "      <td>Miami</td>\n",
       "      <td>8.0</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>ITT Technical Institute-Miami</td>\n",
       "      <td>Miami</td>\n",
       "      <td>8.0</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>College of Business and Technology-Flagler</td>\n",
       "      <td>Miami</td>\n",
       "      <td>8.0</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>College of Business and Technology-Kendall</td>\n",
       "      <td>Miami</td>\n",
       "      <td>8.0</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Florida International University</td>\n",
       "      <td>Miami</td>\n",
       "      <td>8.0</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Miami Dade College</td>\n",
       "      <td>Miami</td>\n",
       "      <td>8.0</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Westwood College-Denver North</td>\n",
       "      <td>Denver</td>\n",
       "      <td>9.0</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>College America-Denver</td>\n",
       "      <td>Denver</td>\n",
       "      <td>9.0</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>The Art Institute of Colorado</td>\n",
       "      <td>Denver</td>\n",
       "      <td>9.0</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Westwood College-Denver South</td>\n",
       "      <td>Denver</td>\n",
       "      <td>9.0</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>ITT Technical Institute-Houston West</td>\n",
       "      <td>Houston</td>\n",
       "      <td>12.0</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>ITT Technical Institute-Houston North</td>\n",
       "      <td>Houston</td>\n",
       "      <td>12.0</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Interactive Learning Systems-North Houston</td>\n",
       "      <td>Houston</td>\n",
       "      <td>12.0</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>The Art Institute of Houston</td>\n",
       "      <td>Houston</td>\n",
       "      <td>12.0</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Interactive Learning Systems-Houston</td>\n",
       "      <td>Houston</td>\n",
       "      <td>12.0</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>American InterContinental University-Houston</td>\n",
       "      <td>Houston</td>\n",
       "      <td>12.0</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Kaplan College-Las Vegas</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>14.0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Luna Community College</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>14.0</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>New Mexico Highlands University</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>14.0</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>International Academy of Design and Technology...</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>16.0</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>The Art Institute of San Antonio</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>16.0</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Brown Mackie College-San Antonio</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>16.0</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>ITT Technical Institute-San Antonio East</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>16.0</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Northwest Vista College</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>16.0</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>ITT Technical Institute-San Antonio</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>16.0</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Kaplan College-San Antonio-San Pedro</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>16.0</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>St Philip's College</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>16.0</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Hallmark College</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>16.0</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Kaplan College-San Antonio-Ingram</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>16.0</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>The Art Institute of California-Argosy Univers...</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>17.0</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Heald College-San Francisco</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>17.0</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               instnm           city  \\\n",
       "0                      ITT Technical Institute-Austin         Austin   \n",
       "1                         The Art Institute of Austin         Austin   \n",
       "2                          Concordia University-Texas         Austin   \n",
       "3                             South University-Austin         Austin   \n",
       "4                         Riverland Community College         Austin   \n",
       "5              Carrington College California-San Jose       San Jose   \n",
       "6                       Argosy University-Los Angeles    Los Angeles   \n",
       "7                        Advanced Computing Institute    Los Angeles   \n",
       "8          Los Angeles ORT College-Los Angeles Campus    Los Angeles   \n",
       "9                            Los Angeles City College    Los Angeles   \n",
       "10                Los Angeles Trade Technical College    Los Angeles   \n",
       "11                      Los Angeles Southwest College    Los Angeles   \n",
       "12                       Florida Career College-Miami          Miami   \n",
       "13  AI Miami International University of Art and D...          Miami   \n",
       "14                      ITT Technical Institute-Miami          Miami   \n",
       "15         College of Business and Technology-Flagler          Miami   \n",
       "16         College of Business and Technology-Kendall          Miami   \n",
       "17                   Florida International University          Miami   \n",
       "18                                 Miami Dade College          Miami   \n",
       "19                      Westwood College-Denver North         Denver   \n",
       "20                             College America-Denver         Denver   \n",
       "21                      The Art Institute of Colorado         Denver   \n",
       "22                      Westwood College-Denver South         Denver   \n",
       "23               ITT Technical Institute-Houston West        Houston   \n",
       "24              ITT Technical Institute-Houston North        Houston   \n",
       "25         Interactive Learning Systems-North Houston        Houston   \n",
       "26                       The Art Institute of Houston        Houston   \n",
       "27               Interactive Learning Systems-Houston        Houston   \n",
       "28       American InterContinental University-Houston        Houston   \n",
       "29                           Kaplan College-Las Vegas      Las Vegas   \n",
       "30                             Luna Community College      Las Vegas   \n",
       "31                    New Mexico Highlands University      Las Vegas   \n",
       "32  International Academy of Design and Technology...    San Antonio   \n",
       "33                   The Art Institute of San Antonio    San Antonio   \n",
       "34                   Brown Mackie College-San Antonio    San Antonio   \n",
       "35           ITT Technical Institute-San Antonio East    San Antonio   \n",
       "36                            Northwest Vista College    San Antonio   \n",
       "37                ITT Technical Institute-San Antonio    San Antonio   \n",
       "38               Kaplan College-San Antonio-San Pedro    San Antonio   \n",
       "39                                St Philip's College    San Antonio   \n",
       "40                                   Hallmark College    San Antonio   \n",
       "41                  Kaplan College-San Antonio-Ingram    San Antonio   \n",
       "42  The Art Institute of California-Argosy Univers...  San Francisco   \n",
       "43                        Heald College-San Francisco  San Francisco   \n",
       "\n",
       "    Rank_Score  Rank  \n",
       "0          2.0     1  \n",
       "1          2.0     2  \n",
       "2          2.0     3  \n",
       "3          2.0     4  \n",
       "4          2.0     5  \n",
       "5          5.0     6  \n",
       "6          7.0     7  \n",
       "7          7.0     8  \n",
       "8          7.0     9  \n",
       "9          7.0    10  \n",
       "10         7.0    11  \n",
       "11         7.0    12  \n",
       "12         8.0    13  \n",
       "13         8.0    14  \n",
       "14         8.0    15  \n",
       "15         8.0    16  \n",
       "16         8.0    17  \n",
       "17         8.0    18  \n",
       "18         8.0    19  \n",
       "19         9.0    20  \n",
       "20         9.0    21  \n",
       "21         9.0    22  \n",
       "22         9.0    23  \n",
       "23        12.0    24  \n",
       "24        12.0    25  \n",
       "25        12.0    26  \n",
       "26        12.0    27  \n",
       "27        12.0    28  \n",
       "28        12.0    29  \n",
       "29        14.0    30  \n",
       "30        14.0    31  \n",
       "31        14.0    32  \n",
       "32        16.0    33  \n",
       "33        16.0    34  \n",
       "34        16.0    35  \n",
       "35        16.0    36  \n",
       "36        16.0    37  \n",
       "37        16.0    38  \n",
       "38        16.0    39  \n",
       "39        16.0    40  \n",
       "40        16.0    41  \n",
       "41        16.0    42  \n",
       "42        17.0    43  \n",
       "43        17.0    44  "
      ]
     },
     "execution_count": 263,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "college = college[[\"instnm\", \"city\", \"Rank_Score\", \"Rank\"]]\n",
    "\n",
    "college"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating a temporary dataframe for city rank determination by duplicate removal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 264,
   "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>Rank_Score</th>\n",
       "      <th>Final_Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8.0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9.0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>12.0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>14.0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>16.0</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>17.0</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Rank_Score  Final_Rank\n",
       "0         2.0           1\n",
       "1         5.0           2\n",
       "2         7.0           3\n",
       "3         8.0           4\n",
       "4         9.0           5\n",
       "5        12.0           6\n",
       "6        14.0           7\n",
       "7        16.0           8\n",
       "8        17.0           9"
      ]
     },
     "execution_count": 264,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Assigning a rank by first removing duplicates\n",
    "# And then using df.index\n",
    "\n",
    "Temp = college[[\"Rank_Score\", \"Rank\"]]\n",
    "Temp = Temp.drop_duplicates(\"Rank_Score\")\n",
    "\n",
    "Temp = Temp.reset_index()\n",
    "\n",
    "Temp[\"Final_Rank\"] = Temp[\"Rank_Score\"].index + 1\n",
    "\n",
    "Temp = Temp.drop([\"index\", \"Rank\"], axis=1)\n",
    "Temp.columns = [\"Rank_Score\", \"Final_Rank\"]\n",
    "\n",
    "Temp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# E. Final Rank Calculation by merging Temp and college dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 265,
   "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>instnm</th>\n",
       "      <th>city</th>\n",
       "      <th>Final_Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ITT Technical Institute-Austin</td>\n",
       "      <td>Austin</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>The Art Institute of Austin</td>\n",
       "      <td>Austin</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Concordia University-Texas</td>\n",
       "      <td>Austin</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>South University-Austin</td>\n",
       "      <td>Austin</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Riverland Community College</td>\n",
       "      <td>Austin</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Carrington College California-San Jose</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Argosy University-Los Angeles</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Advanced Computing Institute</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Los Angeles ORT College-Los Angeles Campus</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Los Angeles City College</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Los Angeles Trade Technical College</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Los Angeles Southwest College</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Florida Career College-Miami</td>\n",
       "      <td>Miami</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>AI Miami International University of Art and D...</td>\n",
       "      <td>Miami</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>ITT Technical Institute-Miami</td>\n",
       "      <td>Miami</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>College of Business and Technology-Flagler</td>\n",
       "      <td>Miami</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>College of Business and Technology-Kendall</td>\n",
       "      <td>Miami</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Florida International University</td>\n",
       "      <td>Miami</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Miami Dade College</td>\n",
       "      <td>Miami</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Westwood College-Denver North</td>\n",
       "      <td>Denver</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>College America-Denver</td>\n",
       "      <td>Denver</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>The Art Institute of Colorado</td>\n",
       "      <td>Denver</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Westwood College-Denver South</td>\n",
       "      <td>Denver</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>ITT Technical Institute-Houston West</td>\n",
       "      <td>Houston</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>ITT Technical Institute-Houston North</td>\n",
       "      <td>Houston</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Interactive Learning Systems-North Houston</td>\n",
       "      <td>Houston</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>The Art Institute of Houston</td>\n",
       "      <td>Houston</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Interactive Learning Systems-Houston</td>\n",
       "      <td>Houston</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>American InterContinental University-Houston</td>\n",
       "      <td>Houston</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Kaplan College-Las Vegas</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Luna Community College</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>New Mexico Highlands University</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>International Academy of Design and Technology...</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>The Art Institute of San Antonio</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Brown Mackie College-San Antonio</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>ITT Technical Institute-San Antonio East</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Northwest Vista College</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>ITT Technical Institute-San Antonio</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Kaplan College-San Antonio-San Pedro</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>St Philip's College</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Hallmark College</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Kaplan College-San Antonio-Ingram</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>The Art Institute of California-Argosy Univers...</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Heald College-San Francisco</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               instnm           city  \\\n",
       "0                      ITT Technical Institute-Austin         Austin   \n",
       "1                         The Art Institute of Austin         Austin   \n",
       "2                          Concordia University-Texas         Austin   \n",
       "3                             South University-Austin         Austin   \n",
       "4                         Riverland Community College         Austin   \n",
       "5              Carrington College California-San Jose       San Jose   \n",
       "6                       Argosy University-Los Angeles    Los Angeles   \n",
       "7                        Advanced Computing Institute    Los Angeles   \n",
       "8          Los Angeles ORT College-Los Angeles Campus    Los Angeles   \n",
       "9                            Los Angeles City College    Los Angeles   \n",
       "10                Los Angeles Trade Technical College    Los Angeles   \n",
       "11                      Los Angeles Southwest College    Los Angeles   \n",
       "12                       Florida Career College-Miami          Miami   \n",
       "13  AI Miami International University of Art and D...          Miami   \n",
       "14                      ITT Technical Institute-Miami          Miami   \n",
       "15         College of Business and Technology-Flagler          Miami   \n",
       "16         College of Business and Technology-Kendall          Miami   \n",
       "17                   Florida International University          Miami   \n",
       "18                                 Miami Dade College          Miami   \n",
       "19                      Westwood College-Denver North         Denver   \n",
       "20                             College America-Denver         Denver   \n",
       "21                      The Art Institute of Colorado         Denver   \n",
       "22                      Westwood College-Denver South         Denver   \n",
       "23               ITT Technical Institute-Houston West        Houston   \n",
       "24              ITT Technical Institute-Houston North        Houston   \n",
       "25         Interactive Learning Systems-North Houston        Houston   \n",
       "26                       The Art Institute of Houston        Houston   \n",
       "27               Interactive Learning Systems-Houston        Houston   \n",
       "28       American InterContinental University-Houston        Houston   \n",
       "29                           Kaplan College-Las Vegas      Las Vegas   \n",
       "30                             Luna Community College      Las Vegas   \n",
       "31                    New Mexico Highlands University      Las Vegas   \n",
       "32  International Academy of Design and Technology...    San Antonio   \n",
       "33                   The Art Institute of San Antonio    San Antonio   \n",
       "34                   Brown Mackie College-San Antonio    San Antonio   \n",
       "35           ITT Technical Institute-San Antonio East    San Antonio   \n",
       "36                            Northwest Vista College    San Antonio   \n",
       "37                ITT Technical Institute-San Antonio    San Antonio   \n",
       "38               Kaplan College-San Antonio-San Pedro    San Antonio   \n",
       "39                                St Philip's College    San Antonio   \n",
       "40                                   Hallmark College    San Antonio   \n",
       "41                  Kaplan College-San Antonio-Ingram    San Antonio   \n",
       "42  The Art Institute of California-Argosy Univers...  San Francisco   \n",
       "43                        Heald College-San Francisco  San Francisco   \n",
       "\n",
       "    Final_Rank  \n",
       "0            1  \n",
       "1            1  \n",
       "2            1  \n",
       "3            1  \n",
       "4            1  \n",
       "5            2  \n",
       "6            3  \n",
       "7            3  \n",
       "8            3  \n",
       "9            3  \n",
       "10           3  \n",
       "11           3  \n",
       "12           4  \n",
       "13           4  \n",
       "14           4  \n",
       "15           4  \n",
       "16           4  \n",
       "17           4  \n",
       "18           4  \n",
       "19           5  \n",
       "20           5  \n",
       "21           5  \n",
       "22           5  \n",
       "23           6  \n",
       "24           6  \n",
       "25           6  \n",
       "26           6  \n",
       "27           6  \n",
       "28           6  \n",
       "29           7  \n",
       "30           7  \n",
       "31           7  \n",
       "32           8  \n",
       "33           8  \n",
       "34           8  \n",
       "35           8  \n",
       "36           8  \n",
       "37           8  \n",
       "38           8  \n",
       "39           8  \n",
       "40           8  \n",
       "41           8  \n",
       "42           9  \n",
       "43           9  "
      ]
     },
     "execution_count": 265,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Introducing final ranks to the college df by merging Temp df and college df\n",
    "\n",
    "college = college[college.columns].merge(Temp, 'left')\n",
    "\n",
    "college = college.drop([\"Rank\", \"Rank_Score\"], axis=1)\n",
    "\n",
    "college"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The above are the colleges from which Maria should choose!!!"
   ]
  },
  {
   "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}