Rent Data/Rent_Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importing Dependencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dependencies\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating Dataframe from the csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>Region Name</th>\n",
       "      <th>Region Type</th>\n",
       "      <th>Data Type</th>\n",
       "      <th>Nov-10</th>\n",
       "      <th>Dec-10</th>\n",
       "      <th>Jan-11</th>\n",
       "      <th>Feb-11</th>\n",
       "      <th>Mar-11</th>\n",
       "      <th>Apr-11</th>\n",
       "      <th>May-11</th>\n",
       "      <th>...</th>\n",
       "      <th>Dec-17</th>\n",
       "      <th>Jan-18</th>\n",
       "      <th>Feb-18</th>\n",
       "      <th>Mar-18</th>\n",
       "      <th>Apr-18</th>\n",
       "      <th>May-18</th>\n",
       "      <th>Jun-18</th>\n",
       "      <th>Jul-18</th>\n",
       "      <th>Aug-18</th>\n",
       "      <th>Sep-18</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bayview</td>\n",
       "      <td>neighborhood</td>\n",
       "      <td>All Homes</td>\n",
       "      <td>3183</td>\n",
       "      <td>3179</td>\n",
       "      <td>3078</td>\n",
       "      <td>2917</td>\n",
       "      <td>2706</td>\n",
       "      <td>2571</td>\n",
       "      <td>2480</td>\n",
       "      <td>...</td>\n",
       "      <td>3713</td>\n",
       "      <td>3663</td>\n",
       "      <td>3624</td>\n",
       "      <td>3602</td>\n",
       "      <td>3579</td>\n",
       "      <td>3558</td>\n",
       "      <td>3535</td>\n",
       "      <td>3519</td>\n",
       "      <td>3508</td>\n",
       "      <td>3502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bernal Heights</td>\n",
       "      <td>neighborhood</td>\n",
       "      <td>All Homes</td>\n",
       "      <td>3155</td>\n",
       "      <td>3146</td>\n",
       "      <td>3110</td>\n",
       "      <td>3013</td>\n",
       "      <td>2882</td>\n",
       "      <td>2798</td>\n",
       "      <td>2763</td>\n",
       "      <td>...</td>\n",
       "      <td>4490</td>\n",
       "      <td>4470</td>\n",
       "      <td>4457</td>\n",
       "      <td>4448</td>\n",
       "      <td>4444</td>\n",
       "      <td>4440</td>\n",
       "      <td>4443</td>\n",
       "      <td>4451</td>\n",
       "      <td>4459</td>\n",
       "      <td>4465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Buena Vista</td>\n",
       "      <td>neighborhood</td>\n",
       "      <td>All Homes</td>\n",
       "      <td>3551</td>\n",
       "      <td>3604</td>\n",
       "      <td>3636</td>\n",
       "      <td>3601</td>\n",
       "      <td>3514</td>\n",
       "      <td>3413</td>\n",
       "      <td>3362</td>\n",
       "      <td>...</td>\n",
       "      <td>5207</td>\n",
       "      <td>5155</td>\n",
       "      <td>5139</td>\n",
       "      <td>5158</td>\n",
       "      <td>5187</td>\n",
       "      <td>5203</td>\n",
       "      <td>5201</td>\n",
       "      <td>5198</td>\n",
       "      <td>5203</td>\n",
       "      <td>5212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Corona Heights</td>\n",
       "      <td>neighborhood</td>\n",
       "      <td>All Homes</td>\n",
       "      <td>3504</td>\n",
       "      <td>3544</td>\n",
       "      <td>3551</td>\n",
       "      <td>3500</td>\n",
       "      <td>3406</td>\n",
       "      <td>3316</td>\n",
       "      <td>3261</td>\n",
       "      <td>...</td>\n",
       "      <td>5082</td>\n",
       "      <td>5070</td>\n",
       "      <td>5057</td>\n",
       "      <td>5060</td>\n",
       "      <td>5069</td>\n",
       "      <td>5082</td>\n",
       "      <td>5086</td>\n",
       "      <td>5087</td>\n",
       "      <td>5087</td>\n",
       "      <td>5083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Cow Hollow</td>\n",
       "      <td>neighborhood</td>\n",
       "      <td>All Homes</td>\n",
       "      <td>4243</td>\n",
       "      <td>4401</td>\n",
       "      <td>4438</td>\n",
       "      <td>4412</td>\n",
       "      <td>4324</td>\n",
       "      <td>4319</td>\n",
       "      <td>4370</td>\n",
       "      <td>...</td>\n",
       "      <td>6050</td>\n",
       "      <td>6061</td>\n",
       "      <td>6052</td>\n",
       "      <td>6034</td>\n",
       "      <td>6009</td>\n",
       "      <td>5979</td>\n",
       "      <td>5964</td>\n",
       "      <td>5972</td>\n",
       "      <td>6013</td>\n",
       "      <td>6065</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 98 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Region Name   Region Type  Data Type  Nov-10  Dec-10  Jan-11  Feb-11  \\\n",
       "0         Bayview  neighborhood  All Homes    3183    3179    3078    2917   \n",
       "1  Bernal Heights  neighborhood  All Homes    3155    3146    3110    3013   \n",
       "2     Buena Vista  neighborhood  All Homes    3551    3604    3636    3601   \n",
       "3  Corona Heights  neighborhood  All Homes    3504    3544    3551    3500   \n",
       "4      Cow Hollow  neighborhood  All Homes    4243    4401    4438    4412   \n",
       "\n",
       "   Mar-11  Apr-11  May-11   ...    Dec-17  Jan-18  Feb-18  Mar-18  Apr-18  \\\n",
       "0    2706    2571    2480   ...      3713    3663    3624    3602    3579   \n",
       "1    2882    2798    2763   ...      4490    4470    4457    4448    4444   \n",
       "2    3514    3413    3362   ...      5207    5155    5139    5158    5187   \n",
       "3    3406    3316    3261   ...      5082    5070    5057    5060    5069   \n",
       "4    4324    4319    4370   ...      6050    6061    6052    6034    6009   \n",
       "\n",
       "   May-18  Jun-18  Jul-18  Aug-18  Sep-18  \n",
       "0    3558    3535    3519    3508    3502  \n",
       "1    4440    4443    4451    4459    4465  \n",
       "2    5203    5201    5198    5203    5212  \n",
       "3    5082    5086    5087    5087    5083  \n",
       "4    5979    5964    5972    6013    6065  \n",
       "\n",
       "[5 rows x 98 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Create a dataframe from the csv file\n",
    "rent_df = pd.read_csv(\"rent_raw.csv\")\n",
    "rent_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cleaning Data and Creating new DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove the Region Type, Data Type columns\n",
    "rent_df = rent_df.drop([\"Region Type\", \"Data Type\"], axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Neighborhood</th>\n",
       "      <th>Nov-10</th>\n",
       "      <th>Dec-10</th>\n",
       "      <th>Jan-11</th>\n",
       "      <th>Feb-11</th>\n",
       "      <th>Mar-11</th>\n",
       "      <th>Apr-11</th>\n",
       "      <th>May-11</th>\n",
       "      <th>Jun-11</th>\n",
       "      <th>Jul-11</th>\n",
       "      <th>...</th>\n",
       "      <th>Dec-17</th>\n",
       "      <th>Jan-18</th>\n",
       "      <th>Feb-18</th>\n",
       "      <th>Mar-18</th>\n",
       "      <th>Apr-18</th>\n",
       "      <th>May-18</th>\n",
       "      <th>Jun-18</th>\n",
       "      <th>Jul-18</th>\n",
       "      <th>Aug-18</th>\n",
       "      <th>Sep-18</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bayview</td>\n",
       "      <td>3183</td>\n",
       "      <td>3179</td>\n",
       "      <td>3078</td>\n",
       "      <td>2917</td>\n",
       "      <td>2706</td>\n",
       "      <td>2571</td>\n",
       "      <td>2480</td>\n",
       "      <td>2450</td>\n",
       "      <td>2437</td>\n",
       "      <td>...</td>\n",
       "      <td>3713</td>\n",
       "      <td>3663</td>\n",
       "      <td>3624</td>\n",
       "      <td>3602</td>\n",
       "      <td>3579</td>\n",
       "      <td>3558</td>\n",
       "      <td>3535</td>\n",
       "      <td>3519</td>\n",
       "      <td>3508</td>\n",
       "      <td>3502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bernal Heights</td>\n",
       "      <td>3155</td>\n",
       "      <td>3146</td>\n",
       "      <td>3110</td>\n",
       "      <td>3013</td>\n",
       "      <td>2882</td>\n",
       "      <td>2798</td>\n",
       "      <td>2763</td>\n",
       "      <td>2767</td>\n",
       "      <td>2780</td>\n",
       "      <td>...</td>\n",
       "      <td>4490</td>\n",
       "      <td>4470</td>\n",
       "      <td>4457</td>\n",
       "      <td>4448</td>\n",
       "      <td>4444</td>\n",
       "      <td>4440</td>\n",
       "      <td>4443</td>\n",
       "      <td>4451</td>\n",
       "      <td>4459</td>\n",
       "      <td>4465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Buena Vista</td>\n",
       "      <td>3551</td>\n",
       "      <td>3604</td>\n",
       "      <td>3636</td>\n",
       "      <td>3601</td>\n",
       "      <td>3514</td>\n",
       "      <td>3413</td>\n",
       "      <td>3362</td>\n",
       "      <td>3371</td>\n",
       "      <td>3428</td>\n",
       "      <td>...</td>\n",
       "      <td>5207</td>\n",
       "      <td>5155</td>\n",
       "      <td>5139</td>\n",
       "      <td>5158</td>\n",
       "      <td>5187</td>\n",
       "      <td>5203</td>\n",
       "      <td>5201</td>\n",
       "      <td>5198</td>\n",
       "      <td>5203</td>\n",
       "      <td>5212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Corona Heights</td>\n",
       "      <td>3504</td>\n",
       "      <td>3544</td>\n",
       "      <td>3551</td>\n",
       "      <td>3500</td>\n",
       "      <td>3406</td>\n",
       "      <td>3316</td>\n",
       "      <td>3261</td>\n",
       "      <td>3265</td>\n",
       "      <td>3307</td>\n",
       "      <td>...</td>\n",
       "      <td>5082</td>\n",
       "      <td>5070</td>\n",
       "      <td>5057</td>\n",
       "      <td>5060</td>\n",
       "      <td>5069</td>\n",
       "      <td>5082</td>\n",
       "      <td>5086</td>\n",
       "      <td>5087</td>\n",
       "      <td>5087</td>\n",
       "      <td>5083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Cow Hollow</td>\n",
       "      <td>4243</td>\n",
       "      <td>4401</td>\n",
       "      <td>4438</td>\n",
       "      <td>4412</td>\n",
       "      <td>4324</td>\n",
       "      <td>4319</td>\n",
       "      <td>4370</td>\n",
       "      <td>4442</td>\n",
       "      <td>4495</td>\n",
       "      <td>...</td>\n",
       "      <td>6050</td>\n",
       "      <td>6061</td>\n",
       "      <td>6052</td>\n",
       "      <td>6034</td>\n",
       "      <td>6009</td>\n",
       "      <td>5979</td>\n",
       "      <td>5964</td>\n",
       "      <td>5972</td>\n",
       "      <td>6013</td>\n",
       "      <td>6065</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 96 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Neighborhood  Nov-10  Dec-10  Jan-11  Feb-11  Mar-11  Apr-11  May-11  \\\n",
       "0         Bayview    3183    3179    3078    2917    2706    2571    2480   \n",
       "1  Bernal Heights    3155    3146    3110    3013    2882    2798    2763   \n",
       "2     Buena Vista    3551    3604    3636    3601    3514    3413    3362   \n",
       "3  Corona Heights    3504    3544    3551    3500    3406    3316    3261   \n",
       "4      Cow Hollow    4243    4401    4438    4412    4324    4319    4370   \n",
       "\n",
       "   Jun-11  Jul-11   ...    Dec-17  Jan-18  Feb-18  Mar-18  Apr-18  May-18  \\\n",
       "0    2450    2437   ...      3713    3663    3624    3602    3579    3558   \n",
       "1    2767    2780   ...      4490    4470    4457    4448    4444    4440   \n",
       "2    3371    3428   ...      5207    5155    5139    5158    5187    5203   \n",
       "3    3265    3307   ...      5082    5070    5057    5060    5069    5082   \n",
       "4    4442    4495   ...      6050    6061    6052    6034    6009    5979   \n",
       "\n",
       "   Jun-18  Jul-18  Aug-18  Sep-18  \n",
       "0    3535    3519    3508    3502  \n",
       "1    4443    4451    4459    4465  \n",
       "2    5201    5198    5203    5212  \n",
       "3    5086    5087    5087    5083  \n",
       "4    5964    5972    6013    6065  \n",
       "\n",
       "[5 rows x 96 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rename the columns\n",
    "rent_df = rent_df.rename(columns = {\"Region Name\": \"Neighborhood\"})\n",
    "rent_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get a series of neighbourhoods\n",
    "neighborhood = rent_df[\"Neighborhood\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a function that returns the sum per row per year\n",
    "\n",
    "def totals(df,str): # where str is the last two digits of the year\n",
    "    year = df.filter(regex = str, axis = 1) # filter the columns based on the str\n",
    "    \n",
    "    return year.sum(axis = 1) # get the sum per row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['11', '12', '13', '14', '15', '16', '17']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a list of years (with data for 12 months)\n",
    "year_list = list(np.arange(11,18)) # creates a list of floats covering the year range of rent_df\n",
    "year_list_str = [str(item) for item in year_list] # converts numbers to string\n",
    "    \n",
    "year_list_str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create an array of yearly rates per neighbourhood using the totals function\n",
    "yearly_rate = [totals(rent_df,year) for year in year_list_str] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Neighborhood',\n",
       " 'Rent_2011',\n",
       " 'Rent_2012',\n",
       " 'Rent_2013',\n",
       " 'Rent_2014',\n",
       " 'Rent_2015',\n",
       " 'Rent_2016',\n",
       " 'Rent_2017']"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a list of keys by adding \"20\" to the last two digits of the year\n",
    "keys = year_list_str\n",
    "keys = [\"Rent_20\" + key for key in keys]\n",
    "\n",
    "# Insert Neighbourhood as a key\n",
    "keys.insert(0,\"Neighborhood\")\n",
    "keys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a list of values by adding the neighbourhood series to the yearly_rate array\n",
    "values = yearly_rate\n",
    "values.insert(0,neighborhood)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>Neighborhood</th>\n",
       "      <th>Rent_2011</th>\n",
       "      <th>Rent_2012</th>\n",
       "      <th>Rent_2013</th>\n",
       "      <th>Rent_2014</th>\n",
       "      <th>Rent_2015</th>\n",
       "      <th>Rent_2016</th>\n",
       "      <th>Rent_2017</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bayview</td>\n",
       "      <td>30723</td>\n",
       "      <td>28821</td>\n",
       "      <td>30433</td>\n",
       "      <td>35338</td>\n",
       "      <td>42870</td>\n",
       "      <td>45681</td>\n",
       "      <td>45747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bernal Heights</td>\n",
       "      <td>34471</td>\n",
       "      <td>35739</td>\n",
       "      <td>38924</td>\n",
       "      <td>43654</td>\n",
       "      <td>53977</td>\n",
       "      <td>54833</td>\n",
       "      <td>53741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Buena Vista</td>\n",
       "      <td>42407</td>\n",
       "      <td>45678</td>\n",
       "      <td>49364</td>\n",
       "      <td>53889</td>\n",
       "      <td>61646</td>\n",
       "      <td>65690</td>\n",
       "      <td>61917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Corona Heights</td>\n",
       "      <td>41051</td>\n",
       "      <td>44269</td>\n",
       "      <td>48263</td>\n",
       "      <td>52768</td>\n",
       "      <td>61781</td>\n",
       "      <td>64072</td>\n",
       "      <td>59849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Cow Hollow</td>\n",
       "      <td>52856</td>\n",
       "      <td>52816</td>\n",
       "      <td>56455</td>\n",
       "      <td>62256</td>\n",
       "      <td>75947</td>\n",
       "      <td>78557</td>\n",
       "      <td>71952</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Neighborhood  Rent_2011  Rent_2012  Rent_2013  Rent_2014  Rent_2015  \\\n",
       "0         Bayview      30723      28821      30433      35338      42870   \n",
       "1  Bernal Heights      34471      35739      38924      43654      53977   \n",
       "2     Buena Vista      42407      45678      49364      53889      61646   \n",
       "3  Corona Heights      41051      44269      48263      52768      61781   \n",
       "4      Cow Hollow      52856      52816      56455      62256      75947   \n",
       "\n",
       "   Rent_2016  Rent_2017  \n",
       "0      45681      45747  \n",
       "1      54833      53741  \n",
       "2      65690      61917  \n",
       "3      64072      59849  \n",
       "4      78557      71952  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a dataframe containing the yearly rates from 2011 to 2017 for the 62 neighbourhoods in SF \n",
    "year_rent_df = pd.DataFrame(dict(zip(keys, values)))\n",
    "year_rent_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>Neighborhood</th>\n",
       "      <th>Rent_2011</th>\n",
       "      <th>Rent_2012</th>\n",
       "      <th>Rent_2013</th>\n",
       "      <th>Rent_2014</th>\n",
       "      <th>Rent_2015</th>\n",
       "      <th>Rent_2016</th>\n",
       "      <th>Rent_2017</th>\n",
       "      <th>City</th>\n",
       "      <th>Avg Rent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bayview</td>\n",
       "      <td>30723</td>\n",
       "      <td>28821</td>\n",
       "      <td>30433</td>\n",
       "      <td>35338</td>\n",
       "      <td>42870</td>\n",
       "      <td>45681</td>\n",
       "      <td>45747</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>37087.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bernal Heights</td>\n",
       "      <td>34471</td>\n",
       "      <td>35739</td>\n",
       "      <td>38924</td>\n",
       "      <td>43654</td>\n",
       "      <td>53977</td>\n",
       "      <td>54833</td>\n",
       "      <td>53741</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>45048.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Buena Vista</td>\n",
       "      <td>42407</td>\n",
       "      <td>45678</td>\n",
       "      <td>49364</td>\n",
       "      <td>53889</td>\n",
       "      <td>61646</td>\n",
       "      <td>65690</td>\n",
       "      <td>61917</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>54370.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Corona Heights</td>\n",
       "      <td>41051</td>\n",
       "      <td>44269</td>\n",
       "      <td>48263</td>\n",
       "      <td>52768</td>\n",
       "      <td>61781</td>\n",
       "      <td>64072</td>\n",
       "      <td>59849</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>53150.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Cow Hollow</td>\n",
       "      <td>52856</td>\n",
       "      <td>52816</td>\n",
       "      <td>56455</td>\n",
       "      <td>62256</td>\n",
       "      <td>75947</td>\n",
       "      <td>78557</td>\n",
       "      <td>71952</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>64405.57</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Neighborhood  Rent_2011  Rent_2012  Rent_2013  Rent_2014  Rent_2015  \\\n",
       "0         Bayview      30723      28821      30433      35338      42870   \n",
       "1  Bernal Heights      34471      35739      38924      43654      53977   \n",
       "2     Buena Vista      42407      45678      49364      53889      61646   \n",
       "3  Corona Heights      41051      44269      48263      52768      61781   \n",
       "4      Cow Hollow      52856      52816      56455      62256      75947   \n",
       "\n",
       "   Rent_2016  Rent_2017           City  Avg Rent  \n",
       "0      45681      45747  San Francisco  37087.57  \n",
       "1      54833      53741  San Francisco  45048.43  \n",
       "2      65690      61917  San Francisco  54370.14  \n",
       "3      64072      59849  San Francisco  53150.43  \n",
       "4      78557      71952  San Francisco  64405.57  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Calculating Avg Rent Price Per Year \n",
    "#and adding column 'City' to specify the location of neighborhood (San Francisco)\n",
    "\n",
    "year_rent_df[\"City\"] = \"San Francisco\"\n",
    "\n",
    "for row in year_rent_df[\"Neighborhood\"]:\n",
    "    year_rent_df[\"Avg Rent\"] = round(year_rent_df.mean(axis = 1), 2)\n",
    "    \n",
    "year_rent_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding geolocation information for each SF neighbourhood"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Importing dependencies to make a request for Lat and Lng\n",
    "import requests\n",
    "import json\n",
    "from config import gkey"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create new columns for geolocation\n",
    "year_rent_df[\"Lat\"] = \"\"\n",
    "year_rent_df[\"Lng\"] = \"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a params \n",
    "params = {\"key\": gkey}\n",
    "\n",
    "# Loop through the year_rent_df and run a lat/long search for each neighborhood\n",
    "for index, row in year_rent_df.iterrows():\n",
    "    base_url = \"https://maps.googleapis.com/maps/api/geocode/json\"\n",
    "\n",
    "    neighborhood = row[\"Neighborhood\"]\n",
    "    city = row[\"City\"]\n",
    "\n",
    "    # update address key value\n",
    "    params['address'] = f\"{neighborhood},{city}\"\n",
    "\n",
    "    # make request\n",
    "    lat_lng = requests.get(base_url, params=params)\n",
    "    \n",
    "    \n",
    "    # convert to json\n",
    "    lat_lng = lat_lng.json()\n",
    "    #inserting coordinates to assigned columns\n",
    "    year_rent_df.loc[index, \"Lat\"] = lat_lng[\"results\"][0][\"geometry\"][\"location\"][\"lat\"]\n",
    "    year_rent_df.loc[index, \"Lng\"] = lat_lng[\"results\"][0][\"geometry\"][\"location\"][\"lng\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>Neighborhood</th>\n",
       "      <th>Rent_2011</th>\n",
       "      <th>Rent_2012</th>\n",
       "      <th>Rent_2013</th>\n",
       "      <th>Rent_2014</th>\n",
       "      <th>Rent_2015</th>\n",
       "      <th>Rent_2016</th>\n",
       "      <th>Rent_2017</th>\n",
       "      <th>City</th>\n",
       "      <th>Avg Rent</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Lng</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bayview</td>\n",
       "      <td>30723</td>\n",
       "      <td>28821</td>\n",
       "      <td>30433</td>\n",
       "      <td>35338</td>\n",
       "      <td>42870</td>\n",
       "      <td>45681</td>\n",
       "      <td>45747</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>37087.57</td>\n",
       "      <td>37.7304</td>\n",
       "      <td>-122.384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bernal Heights</td>\n",
       "      <td>34471</td>\n",
       "      <td>35739</td>\n",
       "      <td>38924</td>\n",
       "      <td>43654</td>\n",
       "      <td>53977</td>\n",
       "      <td>54833</td>\n",
       "      <td>53741</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>45048.43</td>\n",
       "      <td>37.7389</td>\n",
       "      <td>-122.415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Buena Vista</td>\n",
       "      <td>42407</td>\n",
       "      <td>45678</td>\n",
       "      <td>49364</td>\n",
       "      <td>53889</td>\n",
       "      <td>61646</td>\n",
       "      <td>65690</td>\n",
       "      <td>61917</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>54370.14</td>\n",
       "      <td>37.8065</td>\n",
       "      <td>-122.421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Corona Heights</td>\n",
       "      <td>41051</td>\n",
       "      <td>44269</td>\n",
       "      <td>48263</td>\n",
       "      <td>52768</td>\n",
       "      <td>61781</td>\n",
       "      <td>64072</td>\n",
       "      <td>59849</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>53150.43</td>\n",
       "      <td>37.7618</td>\n",
       "      <td>-122.443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Cow Hollow</td>\n",
       "      <td>52856</td>\n",
       "      <td>52816</td>\n",
       "      <td>56455</td>\n",
       "      <td>62256</td>\n",
       "      <td>75947</td>\n",
       "      <td>78557</td>\n",
       "      <td>71952</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>64405.57</td>\n",
       "      <td>37.798</td>\n",
       "      <td>-122.44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Neighborhood  Rent_2011  Rent_2012  Rent_2013  Rent_2014  Rent_2015  \\\n",
       "0         Bayview      30723      28821      30433      35338      42870   \n",
       "1  Bernal Heights      34471      35739      38924      43654      53977   \n",
       "2     Buena Vista      42407      45678      49364      53889      61646   \n",
       "3  Corona Heights      41051      44269      48263      52768      61781   \n",
       "4      Cow Hollow      52856      52816      56455      62256      75947   \n",
       "\n",
       "   Rent_2016  Rent_2017           City  Avg Rent      Lat      Lng  \n",
       "0      45681      45747  San Francisco  37087.57  37.7304 -122.384  \n",
       "1      54833      53741  San Francisco  45048.43  37.7389 -122.415  \n",
       "2      65690      61917  San Francisco  54370.14  37.8065 -122.421  \n",
       "3      64072      59849  San Francisco  53150.43  37.7618 -122.443  \n",
       "4      78557      71952  San Francisco  64405.57   37.798  -122.44  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "year_rent_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save the file as csv file in the Data folder\n",
    "year_rent_df.to_csv(\"../Data/yearly_rent.csv\", encoding = \"utf-8\", index = False)"
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}