Unit 4 Challenge - Tier 3.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Tia3MP1SJpgj"
   },
   "source": [
    "# Springboard Data Science Career Track Unit 4 Challenge - Tier 3 Complete\n",
    "\n",
    "## Objectives\n",
    "Hey! Great job getting through those challenging DataCamp courses. You're learning a lot in a short span of time. \n",
    "\n",
    "In this notebook, you're going to apply the skills you've been learning, bridging the gap between the controlled environment of DataCamp and the *slightly* messier work that data scientists do with actual datasets!\n",
    "\n",
    "Here’s the mystery we’re going to solve: ***which boroughs of London have seen the greatest increase in housing prices, on average, over the last two decades?***\n",
    "\n",
    "\n",
    "A borough is just a fancy word for district. You may be familiar with the five boroughs of New York… well, there are 32 boroughs within Greater London [(here's some info for the curious)](https://en.wikipedia.org/wiki/London_boroughs). Some of them are more desirable areas to live in, and the data will reflect that with a greater rise in housing prices.\n",
    "\n",
    "***This is the Tier 3 notebook, which means it's not filled in at all: we'll just give you the skeleton of a project, the brief and the data. It's up to you to play around with it and see what you can find out! Good luck! If you struggle, feel free to look at easier tiers for help; but try to dip in and out of them, as the more independent work you do, the better it is for your learning!***\n",
    "\n",
    "This challenge will make use of only what you learned in the following DataCamp courses: \n",
    "- Prework courses (Introduction to Python for Data Science, Intermediate Python for Data Science)\n",
    "- Data Types for Data Science\n",
    "- Python Data Science Toolbox (Part One) \n",
    "- pandas Foundations\n",
    "- Manipulating DataFrames with pandas\n",
    "- Merging DataFrames with pandas\n",
    "\n",
    "Of the tools, techniques and concepts in the above DataCamp courses, this challenge should require the application of the following: \n",
    "- **pandas**\n",
    "    - **data ingestion and inspection** (pandas Foundations, Module One) \n",
    "    - **exploratory data analysis** (pandas Foundations, Module Two)\n",
    "    - **tidying and cleaning** (Manipulating DataFrames with pandas, Module Three) \n",
    "    - **transforming DataFrames** (Manipulating DataFrames with pandas, Module One)\n",
    "    - **subsetting DataFrames with lists** (Manipulating DataFrames with pandas, Module One) \n",
    "    - **filtering DataFrames** (Manipulating DataFrames with pandas, Module One) \n",
    "    - **grouping data** (Manipulating DataFrames with pandas, Module Four) \n",
    "    - **melting data** (Manipulating DataFrames with pandas, Module Three) \n",
    "    - **advanced indexing** (Manipulating DataFrames with pandas, Module Four) \n",
    "- **matplotlib** (Intermediate Python for Data Science, Module One)\n",
    "- **fundamental data types** (Data Types for Data Science, Module One) \n",
    "- **dictionaries** (Intermediate Python for Data Science, Module Two)\n",
    "- **handling dates and times** (Data Types for Data Science, Module Four)\n",
    "- **function definition** (Python Data Science Toolbox - Part One, Module One)\n",
    "- **default arguments, variable length, and scope** (Python Data Science Toolbox - Part One, Module Two) \n",
    "- **lambda functions and error handling** (Python Data Science Toolbox - Part One, Module Four) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Ipgd2nV8Jpgl"
   },
   "source": [
    "## The Data Science Pipeline\n",
    "\n",
    "This is Tier Three, so we'll get you started. But after that, it's all in your hands! When you feel done with your investigations, look back over what you've accomplished, and prepare a quick presentation of your findings for the next mentor meeting. \n",
    "\n",
    "Data Science is magical. In this case study, you'll get to apply some complex machine learning algorithms. But as  [David Spiegelhalter](https://www.youtube.com/watch?v=oUs1uvsz0Ok) reminds us, there is no substitute for simply **taking a really, really good look at the data.** Sometimes, this is all we need to answer our question.\n",
    "\n",
    "Data Science projects generally adhere to the four stages of Data Science Pipeline:\n",
    "1. Sourcing and loading \n",
    "2. Cleaning, transforming, and visualizing \n",
    "3. Modeling \n",
    "4. Evaluating and concluding \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "zswDqbefJpgm"
   },
   "source": [
    "### 1. Sourcing and Loading \n",
    "\n",
    "Any Data Science project kicks off by importing  ***pandas***. The documentation of this wonderful library can be found [here](https://pandas.pydata.org/). As you've seen, pandas is conveniently connected to the [Numpy](http://www.numpy.org/) and [Matplotlib](https://matplotlib.org/) libraries. \n",
    "\n",
    "***Hint:*** This part of the data science pipeline will test those skills you acquired in the pandas Foundations course, Module One. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "aEau5nEvJpgm"
   },
   "source": [
    "#### 1.1. Importing Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "7Bt_Q_oPJpgn"
   },
   "outputs": [],
   "source": [
    "# Let's import the pandas, numpy libraries as pd, and np respectively. \n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# Load the pyplot collection of functions from matplotlib, as plt \n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "koUrawxsJpgq"
   },
   "source": [
    "#### 1.2.  Loading the data\n",
    "Your data comes from the [London Datastore](https://data.london.gov.uk/): a free, open-source data-sharing portal for London-oriented datasets. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "AiLiD4v3Jpgr"
   },
   "outputs": [],
   "source": [
    "# First, make a variable called url_LondonHousePrices, and assign it the following link, enclosed in quotation-marks as a string:\n",
    "# https://data.london.gov.uk/download/uk-house-price-index/70ac0766-8902-4eb5-aab5-01951aaed773/UK%20House%20price%20index.xls\n",
    "\n",
    "url_LondonHousePrices = \"https://data.london.gov.uk/download/uk-house-price-index/70ac0766-8902-4eb5-aab5-01951aaed773/UK%20House%20price%20index.xls\"\n",
    "\n",
    "# The dataset we're interested in contains the Average prices of the houses, and is actually on a particular sheet of the Excel file. \n",
    "# As a result, we need to specify the sheet name in the read_excel() method.\n",
    "# Put this data into a variable called properties.  \n",
    "properties = pd.read_excel(url_LondonHousePrices, sheet_name='Average price', index_col= None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "POukEJXgJpgu"
   },
   "source": [
    "### 2. Cleaning, transforming, and visualizing\n",
    "This second stage is arguably the most important part of any Data Science project. The first thing to do is take a proper look at the data. Cleaning forms the majority of this stage, and can be done both before or after Transformation.\n",
    "\n",
    "The end goal of data cleaning is to have tidy data. When data is tidy: \n",
    "\n",
    "1. Each variable has a column.\n",
    "2. Each observation forms a row.\n",
    "\n",
    "Keep the end goal in mind as you move through this process, every step will take you closer. \n",
    "\n",
    "\n",
    "\n",
    "***Hint:*** This part of the data science pipeline should test those skills you acquired in: \n",
    "- Intermediate Python for data science, all modules.\n",
    "- pandas Foundations, all modules. \n",
    "- Manipulating DataFrames with pandas, all modules.\n",
    "- Data Types for Data Science, Module Four.\n",
    "- Python Data Science Toolbox - Part One, all modules"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Te0Q548tnzZa"
   },
   "source": [
    "**2.1. Exploring your data** \n",
    "\n",
    "Think about your pandas functions for checking out a dataframe. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Rxirxw_qoAJa"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(306, 49)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "properties.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 306 entries, 0 to 305\n",
      "Data columns (total 49 columns):\n",
      " #   Column                Non-Null Count  Dtype         \n",
      "---  ------                --------------  -----         \n",
      " 0   Unnamed: 0            305 non-null    datetime64[ns]\n",
      " 1   City of London        306 non-null    object        \n",
      " 2   Barking & Dagenham    306 non-null    object        \n",
      " 3   Barnet                306 non-null    object        \n",
      " 4   Bexley                306 non-null    object        \n",
      " 5   Brent                 306 non-null    object        \n",
      " 6   Bromley               306 non-null    object        \n",
      " 7   Camden                306 non-null    object        \n",
      " 8   Croydon               306 non-null    object        \n",
      " 9   Ealing                306 non-null    object        \n",
      " 10  Enfield               306 non-null    object        \n",
      " 11  Greenwich             306 non-null    object        \n",
      " 12  Hackney               306 non-null    object        \n",
      " 13  Hammersmith & Fulham  306 non-null    object        \n",
      " 14  Haringey              306 non-null    object        \n",
      " 15  Harrow                306 non-null    object        \n",
      " 16  Havering              306 non-null    object        \n",
      " 17  Hillingdon            306 non-null    object        \n",
      " 18  Hounslow              306 non-null    object        \n",
      " 19  Islington             306 non-null    object        \n",
      " 20  Kensington & Chelsea  306 non-null    object        \n",
      " 21  Kingston upon Thames  306 non-null    object        \n",
      " 22  Lambeth               306 non-null    object        \n",
      " 23  Lewisham              306 non-null    object        \n",
      " 24  Merton                306 non-null    object        \n",
      " 25  Newham                306 non-null    object        \n",
      " 26  Redbridge             306 non-null    object        \n",
      " 27  Richmond upon Thames  306 non-null    object        \n",
      " 28  Southwark             306 non-null    object        \n",
      " 29  Sutton                306 non-null    object        \n",
      " 30  Tower Hamlets         306 non-null    object        \n",
      " 31  Waltham Forest        306 non-null    object        \n",
      " 32  Wandsworth            306 non-null    object        \n",
      " 33  Westminster           306 non-null    object        \n",
      " 34  Unnamed: 34           0 non-null      float64       \n",
      " 35  Inner London          306 non-null    object        \n",
      " 36  Outer London          306 non-null    object        \n",
      " 37  Unnamed: 37           0 non-null      float64       \n",
      " 38  NORTH EAST            306 non-null    object        \n",
      " 39  NORTH WEST            306 non-null    object        \n",
      " 40  YORKS & THE HUMBER    306 non-null    object        \n",
      " 41  EAST MIDLANDS         306 non-null    object        \n",
      " 42  WEST MIDLANDS         306 non-null    object        \n",
      " 43  EAST OF ENGLAND       306 non-null    object        \n",
      " 44  LONDON                306 non-null    object        \n",
      " 45  SOUTH EAST            306 non-null    object        \n",
      " 46  SOUTH WEST            306 non-null    object        \n",
      " 47  Unnamed: 47           0 non-null      float64       \n",
      " 48  England               306 non-null    object        \n",
      "dtypes: datetime64[ns](1), float64(3), object(45)\n",
      "memory usage: 117.3+ KB\n"
     ]
    }
   ],
   "source": [
    "properties.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Unnamed: 0</th>\n",
       "      <th>City of London</th>\n",
       "      <th>Barking &amp; Dagenham</th>\n",
       "      <th>Barnet</th>\n",
       "      <th>Bexley</th>\n",
       "      <th>Brent</th>\n",
       "      <th>Bromley</th>\n",
       "      <th>Camden</th>\n",
       "      <th>Croydon</th>\n",
       "      <th>Ealing</th>\n",
       "      <th>...</th>\n",
       "      <th>NORTH WEST</th>\n",
       "      <th>YORKS &amp; THE HUMBER</th>\n",
       "      <th>EAST MIDLANDS</th>\n",
       "      <th>WEST MIDLANDS</th>\n",
       "      <th>EAST OF ENGLAND</th>\n",
       "      <th>LONDON</th>\n",
       "      <th>SOUTH EAST</th>\n",
       "      <th>SOUTH WEST</th>\n",
       "      <th>Unnamed: 47</th>\n",
       "      <th>England</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaT</td>\n",
       "      <td>E09000001</td>\n",
       "      <td>E09000002</td>\n",
       "      <td>E09000003</td>\n",
       "      <td>E09000004</td>\n",
       "      <td>E09000005</td>\n",
       "      <td>E09000006</td>\n",
       "      <td>E09000007</td>\n",
       "      <td>E09000008</td>\n",
       "      <td>E09000009</td>\n",
       "      <td>...</td>\n",
       "      <td>E12000002</td>\n",
       "      <td>E12000003</td>\n",
       "      <td>E12000004</td>\n",
       "      <td>E12000005</td>\n",
       "      <td>E12000006</td>\n",
       "      <td>E12000007</td>\n",
       "      <td>E12000008</td>\n",
       "      <td>E12000009</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E92000001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1995-01-01</td>\n",
       "      <td>91449</td>\n",
       "      <td>50460.2</td>\n",
       "      <td>93284.5</td>\n",
       "      <td>64958.1</td>\n",
       "      <td>71306.6</td>\n",
       "      <td>81671.5</td>\n",
       "      <td>120933</td>\n",
       "      <td>69158.2</td>\n",
       "      <td>79885.9</td>\n",
       "      <td>...</td>\n",
       "      <td>43958.5</td>\n",
       "      <td>44803.4</td>\n",
       "      <td>45544.5</td>\n",
       "      <td>48527.5</td>\n",
       "      <td>56701.6</td>\n",
       "      <td>74435.8</td>\n",
       "      <td>64018.9</td>\n",
       "      <td>54705.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>53202.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1995-02-01</td>\n",
       "      <td>82202.8</td>\n",
       "      <td>51085.8</td>\n",
       "      <td>93190.2</td>\n",
       "      <td>64787.9</td>\n",
       "      <td>72022.3</td>\n",
       "      <td>81657.6</td>\n",
       "      <td>119509</td>\n",
       "      <td>68951.1</td>\n",
       "      <td>80897.1</td>\n",
       "      <td>...</td>\n",
       "      <td>43925.4</td>\n",
       "      <td>44528.8</td>\n",
       "      <td>46051.6</td>\n",
       "      <td>49341.3</td>\n",
       "      <td>56593.6</td>\n",
       "      <td>72777.9</td>\n",
       "      <td>63715</td>\n",
       "      <td>54356.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>53096.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1995-03-01</td>\n",
       "      <td>79120.7</td>\n",
       "      <td>51269</td>\n",
       "      <td>92247.5</td>\n",
       "      <td>64367.5</td>\n",
       "      <td>72015.8</td>\n",
       "      <td>81449.3</td>\n",
       "      <td>120282</td>\n",
       "      <td>68712.4</td>\n",
       "      <td>81379.9</td>\n",
       "      <td>...</td>\n",
       "      <td>44434.9</td>\n",
       "      <td>45200.5</td>\n",
       "      <td>45383.8</td>\n",
       "      <td>49442.2</td>\n",
       "      <td>56171.2</td>\n",
       "      <td>73896.8</td>\n",
       "      <td>64113.6</td>\n",
       "      <td>53583.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>53201.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1995-04-01</td>\n",
       "      <td>77101.2</td>\n",
       "      <td>53133.5</td>\n",
       "      <td>90762.9</td>\n",
       "      <td>64277.7</td>\n",
       "      <td>72965.6</td>\n",
       "      <td>81124.4</td>\n",
       "      <td>120098</td>\n",
       "      <td>68610</td>\n",
       "      <td>82188.9</td>\n",
       "      <td>...</td>\n",
       "      <td>44267.8</td>\n",
       "      <td>45614.3</td>\n",
       "      <td>46124.2</td>\n",
       "      <td>49455.9</td>\n",
       "      <td>56567.9</td>\n",
       "      <td>74455.3</td>\n",
       "      <td>64623.2</td>\n",
       "      <td>54786</td>\n",
       "      <td>NaN</td>\n",
       "      <td>53590.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 49 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Unnamed: 0 City of London Barking & Dagenham     Barnet     Bexley  \\\n",
       "0        NaT      E09000001          E09000002  E09000003  E09000004   \n",
       "1 1995-01-01          91449            50460.2    93284.5    64958.1   \n",
       "2 1995-02-01        82202.8            51085.8    93190.2    64787.9   \n",
       "3 1995-03-01        79120.7              51269    92247.5    64367.5   \n",
       "4 1995-04-01        77101.2            53133.5    90762.9    64277.7   \n",
       "\n",
       "       Brent    Bromley     Camden    Croydon     Ealing  ... NORTH WEST  \\\n",
       "0  E09000005  E09000006  E09000007  E09000008  E09000009  ...  E12000002   \n",
       "1    71306.6    81671.5     120933    69158.2    79885.9  ...    43958.5   \n",
       "2    72022.3    81657.6     119509    68951.1    80897.1  ...    43925.4   \n",
       "3    72015.8    81449.3     120282    68712.4    81379.9  ...    44434.9   \n",
       "4    72965.6    81124.4     120098      68610    82188.9  ...    44267.8   \n",
       "\n",
       "  YORKS & THE HUMBER EAST MIDLANDS WEST MIDLANDS EAST OF ENGLAND     LONDON  \\\n",
       "0          E12000003     E12000004     E12000005       E12000006  E12000007   \n",
       "1            44803.4       45544.5       48527.5         56701.6    74435.8   \n",
       "2            44528.8       46051.6       49341.3         56593.6    72777.9   \n",
       "3            45200.5       45383.8       49442.2         56171.2    73896.8   \n",
       "4            45614.3       46124.2       49455.9         56567.9    74455.3   \n",
       "\n",
       "  SOUTH EAST SOUTH WEST Unnamed: 47    England  \n",
       "0  E12000008  E12000009         NaN  E92000001  \n",
       "1    64018.9    54705.2         NaN    53202.8  \n",
       "2      63715    54356.1         NaN    53096.2  \n",
       "3    64113.6    53583.1         NaN    53201.3  \n",
       "4    64623.2      54786         NaN    53590.9  \n",
       "\n",
       "[5 rows x 49 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "properties.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "tE9Sqt9-oAta"
   },
   "source": [
    "**2.2. Cleaning the data**\n",
    "\n",
    "You might find you need to transpose your dataframe, check out what its row indexes are, and reset the index. You  also might find you need to assign the values of the first row to your column headings  . (Hint: recall the .columns feature of DataFrames, as well as the iloc[] method).\n",
    "\n",
    "Don't be afraid to use StackOverflow for help  with this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "properties_T = properties.T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "cdAu1A3YoH_r"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Unnamed: 0', 'City of London', 'Barking & Dagenham', 'Barnet',\n",
       "       'Bexley', 'Brent', 'Bromley', 'Camden', 'Croydon', 'Ealing', 'Enfield',\n",
       "       'Greenwich', 'Hackney', 'Hammersmith & Fulham', 'Haringey', 'Harrow',\n",
       "       'Havering', 'Hillingdon', 'Hounslow', 'Islington',\n",
       "       'Kensington & Chelsea', 'Kingston upon Thames', 'Lambeth', 'Lewisham',\n",
       "       'Merton', 'Newham', 'Redbridge', 'Richmond upon Thames', 'Southwark',\n",
       "       'Sutton', 'Tower Hamlets', 'Waltham Forest', 'Wandsworth',\n",
       "       'Westminster', 'Unnamed: 34', 'Inner London', 'Outer London',\n",
       "       'Unnamed: 37', 'NORTH EAST', 'NORTH WEST', 'YORKS & THE HUMBER',\n",
       "       'EAST MIDLANDS', 'WEST MIDLANDS', 'EAST OF ENGLAND', 'LONDON',\n",
       "       'SOUTH EAST', 'SOUTH WEST', 'Unnamed: 47', 'England'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "properties_T.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "properties_T = properties_T.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>...</th>\n",
       "      <th>296</th>\n",
       "      <th>297</th>\n",
       "      <th>298</th>\n",
       "      <th>299</th>\n",
       "      <th>300</th>\n",
       "      <th>301</th>\n",
       "      <th>302</th>\n",
       "      <th>303</th>\n",
       "      <th>304</th>\n",
       "      <th>305</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Unnamed: 0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>1995-01-01 00:00:00</td>\n",
       "      <td>1995-02-01 00:00:00</td>\n",
       "      <td>1995-03-01 00:00:00</td>\n",
       "      <td>1995-04-01 00:00:00</td>\n",
       "      <td>1995-05-01 00:00:00</td>\n",
       "      <td>1995-06-01 00:00:00</td>\n",
       "      <td>1995-07-01 00:00:00</td>\n",
       "      <td>1995-08-01 00:00:00</td>\n",
       "      <td>...</td>\n",
       "      <td>2019-08-01 00:00:00</td>\n",
       "      <td>2019-09-01 00:00:00</td>\n",
       "      <td>2019-10-01 00:00:00</td>\n",
       "      <td>2019-11-01 00:00:00</td>\n",
       "      <td>2019-12-01 00:00:00</td>\n",
       "      <td>2020-01-01 00:00:00</td>\n",
       "      <td>2020-02-01 00:00:00</td>\n",
       "      <td>2020-03-01 00:00:00</td>\n",
       "      <td>2020-04-01 00:00:00</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 307 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        index    0                    1                    2  \\\n",
       "0  Unnamed: 0  NaT  1995-01-01 00:00:00  1995-02-01 00:00:00   \n",
       "\n",
       "                     3                    4                    5  \\\n",
       "0  1995-03-01 00:00:00  1995-04-01 00:00:00  1995-05-01 00:00:00   \n",
       "\n",
       "                     6                    7                    8  ...  \\\n",
       "0  1995-06-01 00:00:00  1995-07-01 00:00:00  1995-08-01 00:00:00  ...   \n",
       "\n",
       "                   296                  297                  298  \\\n",
       "0  2019-08-01 00:00:00  2019-09-01 00:00:00  2019-10-01 00:00:00   \n",
       "\n",
       "                   299                  300                  301  \\\n",
       "0  2019-11-01 00:00:00  2019-12-01 00:00:00  2020-01-01 00:00:00   \n",
       "\n",
       "                   302                  303                  304  \\\n",
       "0  2020-02-01 00:00:00  2020-03-01 00:00:00  2020-04-01 00:00:00   \n",
       "\n",
       "                   305  \n",
       "0  2020-05-01 00:00:00  \n",
       "\n",
       "[1 rows x 307 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    " properties_T.iloc[[0]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "properties_T.columns = properties_T.iloc[0]"
   ]
  },
  {
   "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>Unnamed: 0</th>\n",
       "      <th>NaN</th>\n",
       "      <th>1995-01-01 00:00:00</th>\n",
       "      <th>1995-02-01 00:00:00</th>\n",
       "      <th>1995-03-01 00:00:00</th>\n",
       "      <th>1995-04-01 00:00:00</th>\n",
       "      <th>1995-05-01 00:00:00</th>\n",
       "      <th>1995-06-01 00:00:00</th>\n",
       "      <th>1995-07-01 00:00:00</th>\n",
       "      <th>1995-08-01 00:00:00</th>\n",
       "      <th>...</th>\n",
       "      <th>2019-08-01 00:00:00</th>\n",
       "      <th>2019-09-01 00:00:00</th>\n",
       "      <th>2019-10-01 00:00:00</th>\n",
       "      <th>2019-11-01 00:00:00</th>\n",
       "      <th>2019-12-01 00:00:00</th>\n",
       "      <th>2020-01-01 00:00:00</th>\n",
       "      <th>2020-02-01 00:00:00</th>\n",
       "      <th>2020-03-01 00:00:00</th>\n",
       "      <th>2020-04-01 00:00:00</th>\n",
       "      <th>2020-05-01 00:00:00</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Unnamed: 0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>1995-01-01 00:00:00</td>\n",
       "      <td>1995-02-01 00:00:00</td>\n",
       "      <td>1995-03-01 00:00:00</td>\n",
       "      <td>1995-04-01 00:00:00</td>\n",
       "      <td>1995-05-01 00:00:00</td>\n",
       "      <td>1995-06-01 00:00:00</td>\n",
       "      <td>1995-07-01 00:00:00</td>\n",
       "      <td>1995-08-01 00:00:00</td>\n",
       "      <td>...</td>\n",
       "      <td>2019-08-01 00:00:00</td>\n",
       "      <td>2019-09-01 00:00:00</td>\n",
       "      <td>2019-10-01 00:00:00</td>\n",
       "      <td>2019-11-01 00:00:00</td>\n",
       "      <td>2019-12-01 00:00:00</td>\n",
       "      <td>2020-01-01 00:00:00</td>\n",
       "      <td>2020-02-01 00:00:00</td>\n",
       "      <td>2020-03-01 00:00:00</td>\n",
       "      <td>2020-04-01 00:00:00</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City of London</td>\n",
       "      <td>E09000001</td>\n",
       "      <td>91449</td>\n",
       "      <td>82202.8</td>\n",
       "      <td>79120.7</td>\n",
       "      <td>77101.2</td>\n",
       "      <td>84409.1</td>\n",
       "      <td>94900.5</td>\n",
       "      <td>110128</td>\n",
       "      <td>112329</td>\n",
       "      <td>...</td>\n",
       "      <td>813770</td>\n",
       "      <td>810455</td>\n",
       "      <td>826227</td>\n",
       "      <td>776894</td>\n",
       "      <td>737275</td>\n",
       "      <td>747611</td>\n",
       "      <td>777640</td>\n",
       "      <td>844989</td>\n",
       "      <td>867841</td>\n",
       "      <td>899831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Barking &amp; Dagenham</td>\n",
       "      <td>E09000002</td>\n",
       "      <td>50460.2</td>\n",
       "      <td>51085.8</td>\n",
       "      <td>51269</td>\n",
       "      <td>53133.5</td>\n",
       "      <td>53042.2</td>\n",
       "      <td>53700.3</td>\n",
       "      <td>52113.1</td>\n",
       "      <td>52232.2</td>\n",
       "      <td>...</td>\n",
       "      <td>299421</td>\n",
       "      <td>304778</td>\n",
       "      <td>304579</td>\n",
       "      <td>306390</td>\n",
       "      <td>301283</td>\n",
       "      <td>303653</td>\n",
       "      <td>304265</td>\n",
       "      <td>304099</td>\n",
       "      <td>283139</td>\n",
       "      <td>295744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Barnet</td>\n",
       "      <td>E09000003</td>\n",
       "      <td>93284.5</td>\n",
       "      <td>93190.2</td>\n",
       "      <td>92247.5</td>\n",
       "      <td>90762.9</td>\n",
       "      <td>90258</td>\n",
       "      <td>90107.2</td>\n",
       "      <td>91441.2</td>\n",
       "      <td>92361.3</td>\n",
       "      <td>...</td>\n",
       "      <td>528577</td>\n",
       "      <td>526670</td>\n",
       "      <td>525678</td>\n",
       "      <td>522639</td>\n",
       "      <td>519306</td>\n",
       "      <td>518542</td>\n",
       "      <td>519121</td>\n",
       "      <td>527747</td>\n",
       "      <td>526553</td>\n",
       "      <td>532503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bexley</td>\n",
       "      <td>E09000004</td>\n",
       "      <td>64958.1</td>\n",
       "      <td>64787.9</td>\n",
       "      <td>64367.5</td>\n",
       "      <td>64277.7</td>\n",
       "      <td>63997.1</td>\n",
       "      <td>64252.3</td>\n",
       "      <td>63722.7</td>\n",
       "      <td>64432.6</td>\n",
       "      <td>...</td>\n",
       "      <td>337523</td>\n",
       "      <td>333340</td>\n",
       "      <td>332920</td>\n",
       "      <td>333657</td>\n",
       "      <td>336302</td>\n",
       "      <td>334765</td>\n",
       "      <td>337760</td>\n",
       "      <td>339215</td>\n",
       "      <td>346840</td>\n",
       "      <td>339408</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 307 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "0          Unnamed: 0        NaN  1995-01-01 00:00:00  1995-02-01 00:00:00  \\\n",
       "0          Unnamed: 0        NaT  1995-01-01 00:00:00  1995-02-01 00:00:00   \n",
       "1      City of London  E09000001                91449              82202.8   \n",
       "2  Barking & Dagenham  E09000002              50460.2              51085.8   \n",
       "3              Barnet  E09000003              93284.5              93190.2   \n",
       "4              Bexley  E09000004              64958.1              64787.9   \n",
       "\n",
       "0  1995-03-01 00:00:00  1995-04-01 00:00:00  1995-05-01 00:00:00  \\\n",
       "0  1995-03-01 00:00:00  1995-04-01 00:00:00  1995-05-01 00:00:00   \n",
       "1              79120.7              77101.2              84409.1   \n",
       "2                51269              53133.5              53042.2   \n",
       "3              92247.5              90762.9                90258   \n",
       "4              64367.5              64277.7              63997.1   \n",
       "\n",
       "0  1995-06-01 00:00:00  1995-07-01 00:00:00  1995-08-01 00:00:00  ...  \\\n",
       "0  1995-06-01 00:00:00  1995-07-01 00:00:00  1995-08-01 00:00:00  ...   \n",
       "1              94900.5               110128               112329  ...   \n",
       "2              53700.3              52113.1              52232.2  ...   \n",
       "3              90107.2              91441.2              92361.3  ...   \n",
       "4              64252.3              63722.7              64432.6  ...   \n",
       "\n",
       "0  2019-08-01 00:00:00  2019-09-01 00:00:00  2019-10-01 00:00:00  \\\n",
       "0  2019-08-01 00:00:00  2019-09-01 00:00:00  2019-10-01 00:00:00   \n",
       "1               813770               810455               826227   \n",
       "2               299421               304778               304579   \n",
       "3               528577               526670               525678   \n",
       "4               337523               333340               332920   \n",
       "\n",
       "0  2019-11-01 00:00:00  2019-12-01 00:00:00  2020-01-01 00:00:00  \\\n",
       "0  2019-11-01 00:00:00  2019-12-01 00:00:00  2020-01-01 00:00:00   \n",
       "1               776894               737275               747611   \n",
       "2               306390               301283               303653   \n",
       "3               522639               519306               518542   \n",
       "4               333657               336302               334765   \n",
       "\n",
       "0  2020-02-01 00:00:00  2020-03-01 00:00:00  2020-04-01 00:00:00  \\\n",
       "0  2020-02-01 00:00:00  2020-03-01 00:00:00  2020-04-01 00:00:00   \n",
       "1               777640               844989               867841   \n",
       "2               304265               304099               283139   \n",
       "3               519121               527747               526553   \n",
       "4               337760               339215               346840   \n",
       "\n",
       "0  2020-05-01 00:00:00  \n",
       "0  2020-05-01 00:00:00  \n",
       "1               899831  \n",
       "2               295744  \n",
       "3               532503  \n",
       "4               339408  \n",
       "\n",
       "[5 rows x 307 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "properties_T.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "o1uLbJAsoIjK"
   },
   "source": [
    "**2.3. Cleaning the data (part 2)**\n",
    "\n",
    "You might we have to **rename** a couple columns. How do you do this? The clue's pretty bold..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "GKkmn1AnoVZS"
   },
   "outputs": [],
   "source": [
    "properties_T = properties_T.drop(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "properties_T = properties_T.rename(columns={'Unnamed: 0':'London Borough',pd.NaT:'ID'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>London Borough</th>\n",
       "      <th>ID</th>\n",
       "      <th>1995-01-01 00:00:00</th>\n",
       "      <th>1995-02-01 00:00:00</th>\n",
       "      <th>1995-03-01 00:00:00</th>\n",
       "      <th>1995-04-01 00:00:00</th>\n",
       "      <th>1995-05-01 00:00:00</th>\n",
       "      <th>1995-06-01 00:00:00</th>\n",
       "      <th>1995-07-01 00:00:00</th>\n",
       "      <th>1995-08-01 00:00:00</th>\n",
       "      <th>...</th>\n",
       "      <th>2019-08-01 00:00:00</th>\n",
       "      <th>2019-09-01 00:00:00</th>\n",
       "      <th>2019-10-01 00:00:00</th>\n",
       "      <th>2019-11-01 00:00:00</th>\n",
       "      <th>2019-12-01 00:00:00</th>\n",
       "      <th>2020-01-01 00:00:00</th>\n",
       "      <th>2020-02-01 00:00:00</th>\n",
       "      <th>2020-03-01 00:00:00</th>\n",
       "      <th>2020-04-01 00:00:00</th>\n",
       "      <th>2020-05-01 00:00:00</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City of London</td>\n",
       "      <td>E09000001</td>\n",
       "      <td>91449</td>\n",
       "      <td>82202.8</td>\n",
       "      <td>79120.7</td>\n",
       "      <td>77101.2</td>\n",
       "      <td>84409.1</td>\n",
       "      <td>94900.5</td>\n",
       "      <td>110128</td>\n",
       "      <td>112329</td>\n",
       "      <td>...</td>\n",
       "      <td>813770</td>\n",
       "      <td>810455</td>\n",
       "      <td>826227</td>\n",
       "      <td>776894</td>\n",
       "      <td>737275</td>\n",
       "      <td>747611</td>\n",
       "      <td>777640</td>\n",
       "      <td>844989</td>\n",
       "      <td>867841</td>\n",
       "      <td>899831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Barking &amp; Dagenham</td>\n",
       "      <td>E09000002</td>\n",
       "      <td>50460.2</td>\n",
       "      <td>51085.8</td>\n",
       "      <td>51269</td>\n",
       "      <td>53133.5</td>\n",
       "      <td>53042.2</td>\n",
       "      <td>53700.3</td>\n",
       "      <td>52113.1</td>\n",
       "      <td>52232.2</td>\n",
       "      <td>...</td>\n",
       "      <td>299421</td>\n",
       "      <td>304778</td>\n",
       "      <td>304579</td>\n",
       "      <td>306390</td>\n",
       "      <td>301283</td>\n",
       "      <td>303653</td>\n",
       "      <td>304265</td>\n",
       "      <td>304099</td>\n",
       "      <td>283139</td>\n",
       "      <td>295744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Barnet</td>\n",
       "      <td>E09000003</td>\n",
       "      <td>93284.5</td>\n",
       "      <td>93190.2</td>\n",
       "      <td>92247.5</td>\n",
       "      <td>90762.9</td>\n",
       "      <td>90258</td>\n",
       "      <td>90107.2</td>\n",
       "      <td>91441.2</td>\n",
       "      <td>92361.3</td>\n",
       "      <td>...</td>\n",
       "      <td>528577</td>\n",
       "      <td>526670</td>\n",
       "      <td>525678</td>\n",
       "      <td>522639</td>\n",
       "      <td>519306</td>\n",
       "      <td>518542</td>\n",
       "      <td>519121</td>\n",
       "      <td>527747</td>\n",
       "      <td>526553</td>\n",
       "      <td>532503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bexley</td>\n",
       "      <td>E09000004</td>\n",
       "      <td>64958.1</td>\n",
       "      <td>64787.9</td>\n",
       "      <td>64367.5</td>\n",
       "      <td>64277.7</td>\n",
       "      <td>63997.1</td>\n",
       "      <td>64252.3</td>\n",
       "      <td>63722.7</td>\n",
       "      <td>64432.6</td>\n",
       "      <td>...</td>\n",
       "      <td>337523</td>\n",
       "      <td>333340</td>\n",
       "      <td>332920</td>\n",
       "      <td>333657</td>\n",
       "      <td>336302</td>\n",
       "      <td>334765</td>\n",
       "      <td>337760</td>\n",
       "      <td>339215</td>\n",
       "      <td>346840</td>\n",
       "      <td>339408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Brent</td>\n",
       "      <td>E09000005</td>\n",
       "      <td>71306.6</td>\n",
       "      <td>72022.3</td>\n",
       "      <td>72015.8</td>\n",
       "      <td>72965.6</td>\n",
       "      <td>73704</td>\n",
       "      <td>74310.5</td>\n",
       "      <td>74127</td>\n",
       "      <td>73547</td>\n",
       "      <td>...</td>\n",
       "      <td>488784</td>\n",
       "      <td>501533</td>\n",
       "      <td>494770</td>\n",
       "      <td>432188</td>\n",
       "      <td>427126</td>\n",
       "      <td>423161</td>\n",
       "      <td>467145</td>\n",
       "      <td>461398</td>\n",
       "      <td>494455</td>\n",
       "      <td>466574</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 307 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "0      London Borough         ID 1995-01-01 00:00:00 1995-02-01 00:00:00  \\\n",
       "1      City of London  E09000001               91449             82202.8   \n",
       "2  Barking & Dagenham  E09000002             50460.2             51085.8   \n",
       "3              Barnet  E09000003             93284.5             93190.2   \n",
       "4              Bexley  E09000004             64958.1             64787.9   \n",
       "5               Brent  E09000005             71306.6             72022.3   \n",
       "\n",
       "0 1995-03-01 00:00:00 1995-04-01 00:00:00 1995-05-01 00:00:00  \\\n",
       "1             79120.7             77101.2             84409.1   \n",
       "2               51269             53133.5             53042.2   \n",
       "3             92247.5             90762.9               90258   \n",
       "4             64367.5             64277.7             63997.1   \n",
       "5             72015.8             72965.6               73704   \n",
       "\n",
       "0 1995-06-01 00:00:00 1995-07-01 00:00:00 1995-08-01 00:00:00  ...  \\\n",
       "1             94900.5              110128              112329  ...   \n",
       "2             53700.3             52113.1             52232.2  ...   \n",
       "3             90107.2             91441.2             92361.3  ...   \n",
       "4             64252.3             63722.7             64432.6  ...   \n",
       "5             74310.5               74127               73547  ...   \n",
       "\n",
       "0 2019-08-01 00:00:00 2019-09-01 00:00:00 2019-10-01 00:00:00  \\\n",
       "1              813770              810455              826227   \n",
       "2              299421              304778              304579   \n",
       "3              528577              526670              525678   \n",
       "4              337523              333340              332920   \n",
       "5              488784              501533              494770   \n",
       "\n",
       "0 2019-11-01 00:00:00 2019-12-01 00:00:00 2020-01-01 00:00:00  \\\n",
       "1              776894              737275              747611   \n",
       "2              306390              301283              303653   \n",
       "3              522639              519306              518542   \n",
       "4              333657              336302              334765   \n",
       "5              432188              427126              423161   \n",
       "\n",
       "0 2020-02-01 00:00:00 2020-03-01 00:00:00 2020-04-01 00:00:00  \\\n",
       "1              777640              844989              867841   \n",
       "2              304265              304099              283139   \n",
       "3              519121              527747              526553   \n",
       "4              337760              339215              346840   \n",
       "5              467145              461398              494455   \n",
       "\n",
       "0 2020-05-01 00:00:00  \n",
       "1              899831  \n",
       "2              295744  \n",
       "3              532503  \n",
       "4              339408  \n",
       "5              466574  \n",
       "\n",
       "[5 rows x 307 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "properties_T.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "jy8BzXHmoWEw"
   },
   "source": [
    "**2.4.Transforming the data**\n",
    "\n",
    "Remember what Wes McKinney said about tidy data? \n",
    "\n",
    "You might need to **melt** your DataFrame here. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "S2wM0qLuo2Zt"
   },
   "outputs": [],
   "source": [
    "clean_properties = pd.melt(properties_T,id_vars=['London Borough','ID'])"
   ]
  },
  {
   "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>London Borough</th>\n",
       "      <th>ID</th>\n",
       "      <th>0</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>City of London</td>\n",
       "      <td>E09000001</td>\n",
       "      <td>1995-01-01</td>\n",
       "      <td>91449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Barking &amp; Dagenham</td>\n",
       "      <td>E09000002</td>\n",
       "      <td>1995-01-01</td>\n",
       "      <td>50460.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Barnet</td>\n",
       "      <td>E09000003</td>\n",
       "      <td>1995-01-01</td>\n",
       "      <td>93284.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bexley</td>\n",
       "      <td>E09000004</td>\n",
       "      <td>1995-01-01</td>\n",
       "      <td>64958.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Brent</td>\n",
       "      <td>E09000005</td>\n",
       "      <td>1995-01-01</td>\n",
       "      <td>71306.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       London Borough         ID          0    value\n",
       "0      City of London  E09000001 1995-01-01    91449\n",
       "1  Barking & Dagenham  E09000002 1995-01-01  50460.2\n",
       "2              Barnet  E09000003 1995-01-01  93284.5\n",
       "3              Bexley  E09000004 1995-01-01  64958.1\n",
       "4               Brent  E09000005 1995-01-01  71306.6"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_properties.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "7kIsgAo7o3mf"
   },
   "source": [
    "Remember to make sure your column data types are all correct. Average prices, for example, should be floating point numbers... "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "ZcR4IHbcpOaq"
   },
   "outputs": [],
   "source": [
    "clean_properties = clean_properties.rename(columns = {0:'Month','value':'Avg.Price'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>London Borough</th>\n",
       "      <th>ID</th>\n",
       "      <th>Month</th>\n",
       "      <th>Avg.Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>City of London</td>\n",
       "      <td>E09000001</td>\n",
       "      <td>1995-01-01</td>\n",
       "      <td>91449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Barking &amp; Dagenham</td>\n",
       "      <td>E09000002</td>\n",
       "      <td>1995-01-01</td>\n",
       "      <td>50460.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Barnet</td>\n",
       "      <td>E09000003</td>\n",
       "      <td>1995-01-01</td>\n",
       "      <td>93284.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bexley</td>\n",
       "      <td>E09000004</td>\n",
       "      <td>1995-01-01</td>\n",
       "      <td>64958.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Brent</td>\n",
       "      <td>E09000005</td>\n",
       "      <td>1995-01-01</td>\n",
       "      <td>71306.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       London Borough         ID      Month Avg.Price\n",
       "0      City of London  E09000001 1995-01-01     91449\n",
       "1  Barking & Dagenham  E09000002 1995-01-01   50460.2\n",
       "2              Barnet  E09000003 1995-01-01   93284.5\n",
       "3              Bexley  E09000004 1995-01-01   64958.1\n",
       "4               Brent  E09000005 1995-01-01   71306.6"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_properties.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "London Borough            object\n",
       "ID                        object\n",
       "Month             datetime64[ns]\n",
       "Avg.Price                 object\n",
       "dtype: object"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_properties.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_properties['Avg.Price'] = pd.to_numeric(clean_properties['Avg.Price'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "knLUXHLypOtw"
   },
   "source": [
    "**2.5. Cleaning the data (part 3)**\n",
    "\n",
    "Do we have an equal number of observations in the ID, Average Price, Month, and London Borough columns? Remember that there are only 32 London Boroughs. How many entries do you have in that column? \n",
    "\n",
    "Check out the contents of the London Borough column, and if you find null values, get rid of them however you see fit. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "BnvTW5a3p0fC"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "London Borough    14640\n",
       "ID                13725\n",
       "Month             14640\n",
       "Avg.Price         13725\n",
       "dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_properties.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>London Borough</th>\n",
       "      <th>ID</th>\n",
       "      <th>Month</th>\n",
       "      <th>Avg.Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Unnamed: 34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1995-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Unnamed: 37</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1995-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Unnamed: 47</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1995-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>Unnamed: 34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1995-02-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>Unnamed: 37</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1995-02-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14580</th>\n",
       "      <td>Unnamed: 37</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14590</th>\n",
       "      <td>Unnamed: 47</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14625</th>\n",
       "      <td>Unnamed: 34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14628</th>\n",
       "      <td>Unnamed: 37</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14638</th>\n",
       "      <td>Unnamed: 47</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>915 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      London Borough   ID      Month  Avg.Price\n",
       "33       Unnamed: 34  NaN 1995-01-01        NaN\n",
       "36       Unnamed: 37  NaN 1995-01-01        NaN\n",
       "46       Unnamed: 47  NaN 1995-01-01        NaN\n",
       "81       Unnamed: 34  NaN 1995-02-01        NaN\n",
       "84       Unnamed: 37  NaN 1995-02-01        NaN\n",
       "...              ...  ...        ...        ...\n",
       "14580    Unnamed: 37  NaN 2020-04-01        NaN\n",
       "14590    Unnamed: 47  NaN 2020-04-01        NaN\n",
       "14625    Unnamed: 34  NaN 2020-05-01        NaN\n",
       "14628    Unnamed: 37  NaN 2020-05-01        NaN\n",
       "14638    Unnamed: 47  NaN 2020-05-01        NaN\n",
       "\n",
       "[915 rows x 4 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_properties[clean_properties['ID'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_properties = clean_properties[clean_properties['ID'].notna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "London Borough    13725\n",
       "ID                13725\n",
       "Month             13725\n",
       "Avg.Price         13725\n",
       "dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_properties.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "45"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(clean_properties['London Borough'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "removables = ['Inner London', 'Outer London', \n",
    "               'NORTH EAST', 'NORTH WEST', 'YORKS & THE HUMBER', \n",
    "               'EAST MIDLANDS', 'WEST MIDLANDS',\n",
    "              'EAST OF ENGLAND', 'LONDON', 'SOUTH EAST', \n",
    "              'SOUTH WEST', 'England']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_properties = clean_properties[~clean_properties['London Borough'].isin(removables)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "PGEx6mJsp6dG"
   },
   "source": [
    "**2.6. Visualizing the data**\n",
    "\n",
    "To visualize the data, why not subset on a particular London Borough? Maybe do a line plot of Month against Average Price?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "nAg5pT9cqHAR"
   },
   "outputs": [],
   "source": [
    "## I choose Barnet.\n",
    "Barnet_prices = clean_properties[clean_properties['London Borough']=='Barnet']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Text(0, 0.5, 'Price')"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAZgAAAEGCAYAAABYV4NmAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMSwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/d3fzzAAAACXBIWXMAAAsTAAALEwEAmpwYAAA2kElEQVR4nO3dd1hUV/rA8e9LFwSkiQgqqFiwoRJL1FSjJjGryerGVHfjrtmsu0m2x/w2bc1mY/pm0xM3MU1juiUae0lswa4oiB1BqVJE2nB+f8xFURERGYbyfp5nnpk5954z770MvJx7zz1XjDEopZRSdc3F2QEopZRqmjTBKKWUcghNMEoppRxCE4xSSimH0ASjlFLKIdycHUBDERwcbCIjI50dhlJKNSqbNm3KNMaEVLVME4wlMjKS+Ph4Z4ehlFKNiogcutAyPUSmlFLKITTBKKWUcghNMEoppRxCz8FUo7S0lJSUFIqKipwdSqPm5eVFREQE7u7uzg5FKVWPNMFUIyUlBV9fXyIjIxERZ4fTKBljyMrKIiUlhaioKGeHo5SqR3qIrBpFRUUEBQVpcrkMIkJQUJD2ApVqhjTBXIQml8un+1Cp5kkTjFJKNTD5RaXM2ngYW3njvp2KJphG4uuvv0ZE2LNnz2W3tXLlSvz9/enbty/du3fnqaeeqnK9+Ph4Hnzwwcv+PKXUpfl0w2GmfrWDhTvTnB3KZdEE00jMmjWLoUOHMnv27Dppb9iwYWzZsoX4+Hg+/vhjNm3adNbysrIy4uLiePXVV+vk85RSNbciMR2A11fs48XFiWSfLDlreVGpjdxTpc4I7ZJogmkECgoK+PHHH5kxYwazZ89m4cKF/OIXvzi9fOXKldxyyy0AzJgxgy5dunDNNdfwm9/8ht///vfVtu3j40P//v3Zt28fTz75JJMnT2bEiBHce++9rFy5ktGjR5+O4Ve/+hW9evWid+/efPnllwAsXryYwYMH069fP8aPH09BQYGD9oJSzUN+USnxB3MIb9WC3Wl5/Hd5Mt9sOXrWOn/8bCvDpi9nV2ruefWLSm00lDsV6zDlGnpq3i4SUvPqtM2Ytn48cUuPi673zTffMGrUKLp06UJgYCBBQUGsX7+ekydP4uPjw2effcbtt99Oamoq06ZNY/Pmzfj6+nLdddfRp0+fatvOyspi/fr1PPbYYyQkJLBp0yZ++OEHWrRowcqVK0+vN23aNPz9/dmxYwcAOTk5ZGZm8vTTT7N06VJ8fHyYPn06L730Eo8//vhl7RelmipbuWFVUjpXd2mNq0vVg19+TM6krNzw3LjeZOQX86c5W9mdduZvz5HsQhbtOgbAvTM28tn9g+ncuiUAe4/nc9sba/nbqK7cMzjS4dtzMdqDaQRmzZrFhAkTAJgwYQKff/45o0aNYt68eZSVlbFgwQLGjBnDxo0bufrqqwkMDMTd3Z3x48dfsM01a9bQt29fRowYwSOPPEKPHvZE97Of/YwWLVqct/7SpUuZMmXK6fcBAQGsX7+ehIQEhgwZQmxsLDNnzuTQoQvOe6dUs/flphTu+yCej9df+Pdk0c5j+LdwZ0BUIGP7hjM0OoSESgnmkw2HcRHhk0kDERHumbGBUyU2ists/O6TzeQXlzFv2/nnbopKbWclqvqgPZgaqklPwxGysrJYvnw5O3fuRESw2WyICO+//z6vv/46gYGBXHHFFfj6+l5St3jYsGHMnz//vHIfH58q1zfGnDfc2BjDDTfcwKxZsy5to5RqhowxfLj+IACvLE1ibGw4WSeLCfD2IMDHA7AngSUJxxnduy3urvb//2PC/PjfDwcoKSvHw82FJQnHGNo5mCs7B/OfCbHc9d4Gvrd6NHvTC4jrEED8oWyyT5YQaLWbVVDM5I82selQDlOu7cRD13fBw83x/QvtwTRwX3zxBffeey+HDh3i4MGDHDlyhKioKNzc3Ni8eTPvvvsut99+OwADBgxg1apV5OTkUFZWdvo8SV0YMWIEr7322un3OTk5DBo0iB9//JHk5GQACgsLSUpKqrPPVKop2ZaSy86jedw1sD15RWXc+d56Rry8msHPLuOLTSkArErK4GSJjdF9wk7X6x7mS4mtnOT0AnJPlbIv4yRxHQIAGNwxiIiAFny5OYVPNxwmMsibx0bHUG5gxR77QIHvdqQx7LkV7EjJ5bpurXl9xT6ue3Elh7MKL3ubUk+cqna5JpgGbtasWdx6661nlf385z9n9uzZjB49moULF54+ER8eHs6jjz7KwIEDGT58ODExMfj7+wMwd+7cyzo38o9//IOcnBx69uxJnz59WLFiBSEhIXzwwQfccccd9O7dm0GDBtXJMGqlmqKNB7IA+MuIrvz7tl7sSs2jZ7g/nVu35JWlSRhjWLb7OP4t3BncMeh0vR5t/QDYeuQE246cAKBve3uCcXERbusXwZq9mWw8mM2EAe3pHeFPqJ8nS3cfB+DdNftp4+fFggeHMmNiHO//8gqyCkp4aUniZW/Tu2v2V7+CMUYfxtC/f39zroSEhPPKGrr8/HxjjDGlpaVm9OjR5quvvnJyRHaNcV8qVZf+9vk203/a4tPvtx85YU4Wl5rP44+YDn+fbzYdyjZXP7fc/HrmT2fVK7OVmxteWmn6T1tsHv1qu4l8ZL7JPVVyenlWQbF5ZkGC+ee8XafLH/1qu+n+2EKTXVBsOk5dYJ5btPusNp/5LsFEPTLf7D2eV2Ws2QXFZubaA6awuKzK5YXFZSb1RKHp8fgiA8SbC/xd1R5ME/Pkk08SGxtLz549iYqKYuzYsc4OSSkF7MsooGNIy9Pve0X44+3hxsgeoXi4ufDemv0czCpkQGTgWfVcXYTX7+zHyWIbn2w4TBs/L/y8zsxMHujjwdSbuvPY6JjT5cNjQikssfHaimRs5YaBUUFntXn/VZ1o4e7Kf5YlVxnrx+sP8fi3u7j51TV8tTnlvBkFHpq9hcH/Xk5BcVm126wJpol54YUX2Lp1K3v27OHVV1/VecCUaiD2ZRTQqVKCqeDr5c7oXmF8t8N+ov6KqMDz1okO9WXO/YOJbdeKuwa2v+hnDe4YhLeHKx+uO4iri9DfOmdTIdDHg4lXRjJ/eypJx/PPq7/nmL3MAH+as43nFp059H2yuIyViRl0CvHhzovEognmIkwDuWCpMdN9qJq7rIJicgpLT1+vcq6/jeqGj4cr3h6up8+5nKtXhD/fTBnC76+Lvujnebm78o+bY/B0c2VgVCA+nucPGP7NsI54ubnywdqD5y1LSMtjVI82LP/z1dw9qD1vr97Pt1vtF3v+kJxJia2caWN78sytvaqNQ4cpV8PLy4usrCydsv8yGOt+MF5eXs4ORSmn2ZdxEoBOIVVfBtDG34sXf9GH43nFp4cnX647B7ZnTGzbCy4P8PFgRI9QFmxP48lbepwetlxYUsbBrJOMjQ1HRHh8dA+SjhXw9y+3U1xazqJdx/D1dOOKyPN7WufSBFONiIgIUlJSyMjIcHYojVrFHS2Vaq4SrcNQVR0iqzCqZ9gFl9VWVT2XysbGhvPt1lRWJqYzokcbwH54zBj78GgADzcXXr+rH7e/s46/fbkdgF8NiaxRItQEUw13d3e9C6NS6rIUlpTx9qp9dArxIbzV+bNkONPQ6GBCfD15a9U+hncPxcVFTg+F7h525lBdiK8nS/94NbuP5dHC3ZWo4Kp7YufSBKOUUg6w82gupbZyvt2aSkrOKT6bPAiXC8w/5izuri78fVQ3/vL5Nj7fdAT/Fu48891uuoS2JCLg7GTo4iL0aOt/Se1rglFKKQd4cPYWDmUVYis3/PLKSAZ2DLp4JSf4eb9wZq49yMy1h/Bwc6FDkA9z7h9cJ+eddRSZUkrVscyCYvZnnCTIx4MrIgN45MZuzg7pgkSE0b3DSEjLY1vKCW7p3ZZW3h510rZDE4yIHBSRHSKyVUTirbJAEVkiInut54BK608VkWQRSRSRkZXK+1vtJIvIq2KlVhHxFJHPrPINIhJZqc5E6zP2ishER26nUkpVFn8wB4A37+7HnPsH4+Xu6uSIqjc8JhQAY+C6bq3rrN366MFca4yJNcbEWe8fAZYZY6KBZdZ7RCQGmAD0AEYBb4hIxU/lTWAyEG09Rlnlk4AcY0xn4GVgutVWIPAEMBAYADxROZEppZQjxR/MxtPNhZ7h/o3iEodOIS2JCvYhuKXnBa/DqQ1nHCIbA8y0Xs8ExlYqn22MKTbGHACSgQEiEgb4GWPWGfsVex+eU6eirS+A663ezUhgiTEm2xiTAyzhTFJSSimH2ZdRwJLdx+nTrhWebg2751LZv27tyfPje9fpQARHn+Q3wGIRMcDbxph3gFBjTBqAMSZNRCr6Y+HA+kp1U6yyUuv1ueUVdY5YbZWJSC4QVLm8ijpKKeUQp0ps3PbGWsrLDY/dHOPscC7JlZ2C67xNRyeYIcaYVCuJLBGR6uZyryptmmrKa1vnzAeKTMZ+6I327S8+v49SSlVnZWI6uadK+XjSQIZG1/0f7MbGoYfIjDGp1nM68DX28yHHrcNeWM/p1uopQLtK1SOAVKs8oorys+qIiBvgD2RX09a58b1jjIkzxsSFhITUfkOVUgqYvyONIB8PBnW8+DQqzYHDEoyI+IiIb8VrYASwE5gLVIzqmgh8a72eC0ywRoZFYT+Zv9E6nJYvIoOs8yv3nlOnoq1xwHLrPM33wAgRCbBO7o+wypRSyiFOldhYvjudkT3b4FZH84k1do48RBYKfG2NoHADPjXGLBKRn4A5IjIJOAyMBzDG7BKROUACUAZMMcbYrLYeAD4AWgALrQfADOAjEUnG3nOZYLWVLSLTgJ+s9f5pjMl24LYqpZq5lYnpnCq1cXOvup9TrLESnUrdLi4uzsTHxzs7DKVUIzXl082s35fFhkevb1Y9GBHZVOkylLM0n72glFIOoofHqqZ7QimlLtM3W49yqtTGz/pc+P4rzZEmGKWUugzl5Yb31uynR1s/BlZxu+PmTBOMUkpdhnX7s9iXcZJfD4tqFNPC1CdNMEopdRkW7zqGl7sLNzrgjpSNnd4PRimlaqC4zMZbK/dzqtTGfUMiSc0t4rOfDrM6KZMhnYIb/IzJzqAJRimlauA3H25idVIGri7C4l3HiAj0ZnVSBgBTru3s5OgaJk0wSil1EdknS1idlMGUazvRtY0fD87awv7Mk3Rr40vuqVKGx9TdPVSaEk0wSil1ERsPZAFwbdfW9GnXijB/L9Jyi3hlQixdQ3315P4FaIJRSqmLWL8/Gy93F3pHtMLd1YW/j+rG2n2ZdGtTdzfnaoo0wSil1EVsOJBNv/YBeLjZB96O7RvO2L56i6mL0WHKSilVjdzCUvYcy2NgVJCzQ2l0NMEopVQ1Nh7MxhgYqPd4uWSaYJRSqhob9mfh4eZCbLtWzg6l0dEEo5RS58g9VUp6XhFgP/8S266VXkhZC3qSXymlKvlmy1H+NGcr5QbuGtieXam5/F4vpKwVTTBKKWU5UVjCU/N20SuiFWF+Xnyy4TBdQ325e1AHZ4fWKGmCUUopy/9+PEjuqVKeva0X0a1bsmxPOkM7B+PjqX8qa0P3mlJKWb7bkcbAqCC6h9kvoBzZo42TI2rc9CS/UkoByen5JKcXcGMvTSp1RROMUkoBi3YeA2BEjCaYuqIJRimlgFVJGfQK96eNv5ezQ2kyNMEopZq9k8VlbDl8gqHRwc4OpUnRBKOUavY2HsimrNwwpJMmmLqkCUYp1ez9kJyJh5sLcZEBzg6lSdEEo5Rq1owxLN19nIFRgTodTB3TBKOUatZ2peZxKKuQm3uFOTuUJkcTjFKqWZu/PQ1XF9GLKh1AE4xSqtmylRvmbj3KkM7BBPh4ODucJkcTjFKq2VqVlE5qbhETrmjn7FCaJE0wSqlm6+P1hwlu6ckNMaHODqVJ0gSjlGqWvt16lOV70pk4uAPurvqn0BF0ryqlmp3CkjL+7+udxHUI4IFrOjk7nCZLE4xSqtlZtjudguIy/jKyK27ae3EYh+9ZEXEVkS0iMt96HygiS0Rkr/UcUGndqSKSLCKJIjKyUnl/EdlhLXtVRMQq9xSRz6zyDSISWanOROsz9orIREdvp1Kq8ViwPY0QX0+uiAx0dihNWn2k7oeA3ZXePwIsM8ZEA8us94hIDDAB6AGMAt4QkYrLat8EJgPR1mOUVT4JyDHGdAZeBqZbbQUCTwADgQHAE5UTmVKq+corKmVFYjo39WyDq4s4O5wmzaEJRkQigJuB9yoVjwFmWq9nAmMrlc82xhQbYw4AycAAEQkD/Iwx64wxBvjwnDoVbX0BXG/1bkYCS4wx2caYHGAJZ5KSUqoZ+2T9YYrLyhkfp0OTHc3RPZhXgL8B5ZXKQo0xaQDWc2urPBw4Umm9FKss3Hp9bvlZdYwxZUAuEFRNW0qpZqyo1MaMHw4wLDqYnuH+zg6nyXNYghGR0UC6MWZTTatUUWaqKa9tncoxThaReBGJz8jIqGGYSqnGav72NDILivnt1TpyrD44sgczBPiZiBwEZgPXicjHwHHrsBfWc7q1fgpQuc8aAaRa5RFVlJ9VR0TcAH8gu5q2zmKMeccYE2eMiQsJCan9liqlGoWP1h+iU4gPV3YKcnYozYLDEowxZqoxJsIYE4n95P1yY8zdwFygYlTXROBb6/VcYII1MiwK+8n8jdZhtHwRGWSdX7n3nDoVbY2zPsMA3wMjRCTAOrk/wipTSjVD5eWGj9YfYtuRE9w9qAPWQFTlYG5O+MxngTkiMgk4DIwHMMbsEpE5QAJQBkwxxtisOg8AHwAtgIXWA2AG8JGIJGPvuUyw2soWkWnAT9Z6/zTGZDt6w5RSDdP/fjzA0wt2MyAqUE/u1yOx/8Ov4uLiTHx8vLPDUErVsYLiMoZNX07PcH8+vG+A9l7qmIhsMsbEVbXMGT0YpZSqF2+t2sfrK5LJLyrjzyO6anKpZ5pglFJN0pKE4zy7cA/DooO5Y0B7Ytu1cnZIzY4mGKVUk1NQXMajX++gR1s/3r03Di9314tXUnVOE4xSqsl5a+U+MvKLeeee/ppcnEinEVVKNSnGGGb/dJgRMaH0ba9TEDqTJhilVJOSeDyfzIIShnfXu1Q6myYYpVST8mNyFgBXdtar9Z1NE4xSqlEwxlBefvHr9tYmZ9IhyJuIAO96iEpVR0/yK6UaPFu54bY317I/o4Cru4Twu2s6E9PW77z1Nh3KZvXeDO4Y0N4JUapzaYJRSjVoFSfttx05wfDurVmVlMHKxAy+e3AYx/KKOJJdSHJGAYM7BvGnOVtp26oFf7qhi7PDVmiCUUo1UMVlNh75cgeLdx3jZImN/h0CePfeOFJyTjHi5dUMf2kVJbYzt5p6c+U+An08mDHxClp5ezgxclVBE4xSqkFanZTJ11uOMja2LX3ateJnfdoiIrQL9OaJW2L4eMMh7r+qE13b+OLt4cqbK/dx58D2dG7d0tmhK4smGKVUg7R41zF8vdx4fnwf3F3PHo80YUB7JpxznuVft/aqz/BUDegoMqVUg1NmK2fp7uNc3631eclFNR7ag1FKOV1BcRkLtqdSXFbOPYM68OryZHIKSxnZo42zQ1OXQROMUsqpjDH89qNN/JCcCcDCHcdYtz+Lcf0jGKEJplHTvqdSqtayCop5/NudHM8rqlX9olIbX20+yg/JmUy9sRvDooNZtz+LX14ZyXM/742ri96/pTHTHoxSjYAxhqfmJbAvo4D7hkYR0tKTnuH+zg6Lt1fv58N1hziQefK8u0WuSsrgSHYhdw/qUGXd8nLD+LfWseNoLh2DffjVkCjuGNieTYdyuKZLiN4crAnQBKNUA2eMYcYPB/hg7UE8XF1YszcTVxdh8R+volOI84bk5hWV8umGw4T5e7Fmbyb/nJ/AYzfH4OIilNrKeeTL7RzLK2JgVCDRob7n1V++J50dR3N5eHg0EwdH4uHmgoebC9d2be2ErVGOoIfIlGrgHpq9lacX7Ob6bq1ZO/U63v/lFXi5ufDS4qTT6xSV2njsm52s2ZtRb3F9uuEwBcVlvHNPHL8aEsn7Px7kg7UHAViwPY203CIEeGXp3vPqZhUU8+ryvbT192LKtZ0J8NELI5uiGiUYEekiIstEZKf1vreI/MOxoSmldh7NZe62VH49NIp37o0juKUn13ZrzaRhHVmwI421yZmUlxv+MGsLH60/xAMfb2Z/RoHD4yopK+f9Hw8wpHMQvSL8eXx0DH3bt2LWxsOs35/FP+cnEN26JQ9c04kFO9LYnZZ3uu6JwhJGvrKGhNQ8/jqqqw5DbsJq+pN9F5gKlAIYY7YDExwVlFLK7tONh/F0c+EP10WfdcL7gas70THYh79+sZ3XVySzJOE4U67thKuL8MLiRIfH9e3WoxzPK2byVZ0AEBFuj2vH3vQC7npvAwHe7rx9T38mD+uEr5cbLy8509t6a9V+sk4WM+e3g7m1b4TDY1XOU9ME422M2XhOWVldB6OUOmPPsTy+3nyUW/q0xd/b/axlLTxceen2WLJPlvDikiQGRgXylxFdubl3GCsTMygqtTksLmMM767ZT7c2vlwVHXy6/ObeYfh6utGtjS9fPnAlHUNa4u/tzq+HdmRxwnF2pOSybl8WH6w9wM/6tKWf3m2yyatpgskUkU6AARCRcUCaw6JSqpkrLCnjvvd/wq+FG38eUfXMwLHtWvHV767k5l5h/Pu2XogIN3QPpbDExvr9WQ6Ja8P+LP78+TaSjhcw+aqOZ4308vVyZ/GfruLLB648a7LJ+4ZG0srbnSmfbuau99YT3qoFfx/VzSHxqYalpqPIpgDvAN1E5ChwALjbYVEp1cyt2JNBam4RH943gDD/Fhdcr3uYH6/f1e/0+8GdgvD2cGVJwnGuqePRWLtSc5n4/kZKbYYuoS25pU/b89apKlZfL3fuv6oT0xft4ebeYUz/eW9aeuoA1uagRj9lY8x+YLiI+AAuxph8x4alVPO2aNcxgnw8GNI5+OIrV+Ll7spV0SEs3X2cp8f2rLNrSTILipn84SYCvD2Y+/uhBLf0uKS277+qo31AQLi/Xt/SjNR0FNkzItLKGHPSGJMvIgEi8rSjg1OqOSoqtbF893FG9Ait1ZXsw2NCOZ5XzI6juXUW0x8/20pmQTHv3BNHiK/nJScJFxehd0QrTS7NTE3PwdxojDlR8cYYkwPc5JCIlGrm5m5L5WSJjZt6hdWq/nXdWuMisDTheJ3Es+3ICdbszeQvI7rSK8L5sweoxqOmCcZVRDwr3ohIC8CzmvWVUrVQZivn9RXJ9Gjrx9BLPDxWIdDHg7jIQD5af4gfrQkkL8f7Px6gpacbEwa0u+y2VPNS0wTzMbBMRCaJyH3AEmCm48JSqnn6Zmsqh7IKeej66Ms6nPTMrb0IbunJfR/8xKGsk7VqwxjDi4sT+WZrKrdf0Q5fL/eLV1KqkholGGPMc8C/gO5AD2CaVaaUqiNltnJeW76XmDA/bogJvay2OrduyUeTBuLu6sI/vtlZqzZWJWXw3+XJjO8fwd9Gdb2seFTzVOOxgsaYhcBCB8aiVLP20fpDHMwq5O17+tfJyfA21jxf0xft4UDmSaKCfWpc90RhCc8u3EP7QG/+dWsvPNx0Ohd16apNMCLygzFmqIjkY11kWbEIMMYYP4dGp1QzcCjrJLN/OsK7q/dzXbfW3ND98novld3cK4zpi/awYk86UUOjql1344Fs3lm9n1be7izYnsapUhtv3d1fk4uqtWoTjDFmqPV8/lzbSqnLlltYyp3vbuBYXhFXRAbwyoRYXOrwJlvtg7zpFOLDisR07qsmwRhjeOa73exKzUVEGNOnLZOGRdGtjf4PqWrvov+aiIhLxSzKl0JEvERko4hsE5FdIvKUVR4oIktEZK/1HFCpzlQRSRaRRBEZWam8v4jssJa9KtbxAxHxFJHPrPINIhJZqc5E6zP2isjES41fKUcrKrXx+1mbOZ5XxBe/HczsyYPxc8CJ9Ou6tWbD/mxOFl94+sCfDuaw9cgJHh8dQ+K0UTw/vo8mF3XZLppgjDHlwDYRaX+JbRcD1xlj+gCxwCgRGQQ8AiwzxkQDy6z3iEgM9hmaewCjgDdExNVq601gMhBtPUZZ5ZOAHGNMZ+BlYLrVViDwBDAQGAA8UTmRKeVstnLD/dZ96J+5tRd9HTjx45DOwZTYytl65MQFY/n3wt0E+Xgwrn87vRhS1ZmaHlwNA3ZZ94SZW/GoroKxq7gxhbv1MMAYzgxxngmMtV6PAWYbY4qNMQeAZGCAiIQBfsaYdcYYA3x4Tp2Ktr4Arrd6NyOBJcaYbOui0CWcSUpKOc3GA9nsPJrL49/uZFVSBk+P7ckvrnDs9SV929mT15bDOVUu/3j9IbYcPsFjo2No4eFa5TpK1UZNR5E9VZvGrR7IJqAz8LoxZoOIhBpj0gCMMWkiUjEjXziwvlL1FKus1Hp9bnlFnSNWW2UikgsEVS6voo5STrF8z3Hu+yD+9PtfXhnJXQOrvl99XfL3dqdz65ZsPnyiyuWzfzpC3/atGBN7/uSVSl2Oi40i8wJ+iz1B7ABmGGNqfB8YY4wNiBWRVsDXItKzuo+rqolqymtb58wHikzGfuiN9u0v9QigUjVXVGrjqXkJdAzx4aHro+naxrdez3H0a9+KJQnHMcacdQgss6CY3Wl5/HVkVz00purcxQ6RzQTisCeXG4EXa/Mh1jxmK7EfpjpuHfbCek63VksBKh8riABSrfKIKsrPqiMiboA/kF1NW+fG9Y4xJs4YExcSElKbTVOqRl74PpFDWYVMG9OTMbHh9X4CvV/7AHIKS0lOP/t2yhVTydR2WhqlqnOxBBNjjLnbGPM2MA4YVtOGRSTE6rlUzF02HNgDzAUqRnVNBL61Xs8FJlgjw6Kwn8zfaB1OyxeRQdb5lXvPqVPR1jhguXWe5ntghDXrcwAwwipTqt79dDCb9344wD2DOlzy9Pt15dpurXF3FWauO3hW+Zq9mfi3cKdnuE5iqerexc7BlFa8sM5xXErbYcBM6zyMCzDHGDNfRNYBc0RkEnAYGG+1v0tE5gAJ2G/HPMU6xAbwAPAB0AL7bAIVMwrMAD4SkWTsPZcJVlvZIjIN+Mla75/GmOxLCV6pumCM4V8LdtPGz4upNznvLo6hfl6M6x/BnPgUYtsFcHOvMLYeOcHXW44yrl9ErW4LoNTFiP0f/gssFLEBFTPlCfY/8IU0wSv54+LiTHx8/MVXVKqGck6W8PziRD7dcJjnxvXmF3HOnY34SHYht725loz8YjqF+HAst4g2/l58M2WITmSpak1ENhlj4qpadrEr+XXMolIXce6Jc4DiMhv3zfyJ7Sm53DmwPT/vF3GB2vWnXaA366dez4o96fxxzlZ6hvvz8u2xmlyUw+iNsZWyZBUUM2vjYToE+TB/eyp/GdGV6NDzZ0k6nlfEf5btZfnudAyGnJOljI+LIMzfi86tWxLeypsXFiey5fAJ3rirX61vHOYIri7C8JhQfvq/4Xi6uejIMeVQmmCUwt4LeeSrHSypdBdIVxfhjbv6n36fVVDMt1tTeXv1PnIKS7mheyjeHq7Yyg2fbDh8VnveHq5MG9OjQSWXyrzc9eCEcjxNMKrZKiq1cSirkO92pPH9rmPsOZbPg9d1pm+HANYkZfLB2gMcziqkfZA3AA98spmNB7KJCvZh5n0Dzhpq/McbuuDt4cqmQzmcKrVxZadgQnz1pq+qedMEo5ql4jIbY1//kT3H8gEY1DGQ+6/uyIPXR+Pm6kJMmB8fbzjEX77YxkeTBrA7LZ+NB7KZemM3Jl/V8bxDS+0C7UloRI829b4tSjVUmmBUs/TfZcnsOZbPozd1Y1DHIHpHtDpreaifF8+P681Ds7cy/q11FJXa8PVy465BHfS8hVI1pAlGNTs7j+by5qp9jOsfweSrOl1wvTGx4bi6CP/+bg9ursLz43rT0lN/ZZSqKf1tUc1Kebnh719uJ9DHg8dujrno+qN7t2V0b50EUqna0Huhqmbl+13H2JWaxz9u7o6/t17/oZQjaYJRzUZxmY3/LNtLx2Af7ZUoVQ/0EJlq8pLTC/hg7QF2HM1jz7F8Xr+zn869pVQ90ASjmqTycsOLSxL5cO0hTpXacHd1IailBy/f3oebezfMix+Vamo0wagmo9RWztSvdrAyMZ38ojKKy8q5ISaUjiE+TBoaRWtfL2eHqFSzoglGNQnGGP76+Ta+2ZrKmNi2hPp50a2NL7f2DdfrVpRyEk0wqkn4ftcxvtmaysPDo3l4eBdnh6OUQkeRqUboh72ZjP7vGlJPnAKgpKycJ+cmEBPmx++v7ezk6JRSFTTBqEalvNwwbX4CO4/m8fz3iQAsSTjOsbwi/jqyK26u+pVWqqHQQ2Sq0SgqtfHy0iQSj+fTJ8Kfr7ccpWOwD6uSMghv1YKruoQ4O0SlVCX6755qNKYv2sPbq/YzNrYtn/5mEDf1asOLS5KIP5TDnQPb67UtSjUw2oNRjYKt3DBvWyo39WrDKxP6AvD6nf1ISMujqLSc3hH+To5QKXUuTTCqUdhwIIvMghJu7nVmihcRoUdbTSxKNVSaYFSDtzopg+mL9uDl7sK13fQ8i1KNhSYY1SCVlxteXb6Xlp5uPPd9IsE+Hjx5Sw+8PfQrq1Rjob+tqkH67/JkXlm6F4Dglp7M/cNQglvqPe6Vakw0wagGZ1VSBq8sS+LWvuEMiw6mS6ivJhelGiFNMKpByT5ZwsOzt9A11Jdnbu1FCw9XZ4eklKolTTCqQZm3LZWcwlI+mjRQk4tSjZxeaKkalG+3HqVbG196huvwY6UaO00wqsE4kl3I5sMnuKWP3s5YqaZAE4xqMGb8cAA3F2Fs33Bnh6KUqgOaYFSDkJ5XxKyNh7mtXzjhrVo4OxylVB3QBKMahI83HKbEVs4UvZ+LUk2GJhjldLZyw+fxR7i6SwgdgnycHY5Sqo5oglFOt2JPOmm5RUy4op2zQ1FK1SGHJRgRaSciK0Rkt4jsEpGHrPJAEVkiInut54BKdaaKSLKIJIrIyErl/UVkh7XsVRERq9xTRD6zyjeISGSlOhOtz9grIhMdtZ3q8uScLOGJubtoF9iC67uHOjscpVQdcmQPpgz4szGmOzAImCIiMcAjwDJjTDSwzHqPtWwC0AMYBbwhIhVX2r0JTAairccoq3wSkGOM6Qy8DEy32goEngAGAgOAJyonMuV8KxLTmfHDAe56bwMZ+cW8fmc/3PV2x0o1KQ67kt8YkwakWa/zRWQ3EA6MAa6xVpsJrAT+bpXPNsYUAwdEJBkYICIHAT9jzDoAEfkQGAsstOo8abX1BfCa1bsZCSwxxmRbdZZgT0qzHLW9qmaMMby5ah/PLUoEwM/Ljbfv6U/viFbODUwpVefqZaoY69BVX2ADEGolH4wxaSLS2lotHFhfqVqKVVZqvT63vKLOEautMhHJBYIql1dRRznRvxbs5r0fDjAmti1/HdkVvxbu+Hm5OzsspZQDODzBiEhL4EvgYWNMnnX6pMpVqygz1ZTXtk7l2CZjP/RG+/btLxSXqiMr9qTz3g8HuGdQB576WQ9cXC74XVBKNQEOPegtIu7Yk8snxpivrOLjIhJmLQ8D0q3yFKDyMKIIINUqj6ii/Kw6IuIG+APZ1bR1FmPMO8aYOGNMXEiI3inRkU6V2Hhq3i46hvjw2OgYTS5KNQOOHEUmwAxgtzHmpUqL5gIVo7omAt9WKp9gjQyLwn4yf6N1OC1fRAZZbd57Tp2KtsYBy40xBvgeGCEiAdbJ/RFWmXICYwz/9/UODmUX8vSYnni46cl8pZoDRx4iGwLcA+wQka1W2aPAs8AcEZkEHAbGAxhjdonIHCAB+wi0KcYYm1XvAeADoAX2k/sLrfIZwEfWgIBs7KPQMMZki8g04CdrvX9WnPBX9e/TjYf5astRHh4ezZWdg50djlKqnoj9H34VFxdn4uPjnR1Gk7PzaC63vbGWwZ2CeP+XV+ihMaWaGBHZZIyJq2qZHqtQDlNcZuNPc7bSytudl2+P1eSiVDOjd7RUDmErN/zl8+0kHS/g/V9dQaCPh7NDUkrVM00wqk4ZYzAG/v7lduZtS2Xqjd24tmvri1dUSjU5mmBUrRhjqLimqcxWzvOLE9l+JJe96QVknyym3MDDw6O5/+pOTo5UKeUsmmDUJdmwP4sXlySx1bq1caifJzuO5rJmbyax7VoxLDqYNv5edAz2YVz/iIs3qJRqsjTBqBp7/8cDPDUvgfBWLbipVxvmbU/FVm5o6enGozd1Y/JV2ltRSp2hCUbVyNp9mfxrwW6Gdw/l1Tti8fZw46VygwhUM/2PUqoZ0wSjLuqlJUn8d/leOgR68+Iv+uDtYf/a6LBjpVR19DoYVa1vtx7l1WV7uTU2nLl/GIp/C535WClVM9qDURe0bl8Wf/1iO1dEBjB9XG+9IZhS6pLoXwxVpeT0fCZ/GE+HQG/euSdOk4tS6pLpXw11nhOFJUyaGY+nuwsf3DeAAL0KXylVC3qITJ2l1FbOlE83k3aiiFmTBxLeqoWzQ1JKNVKaYNRZnp6fwI/JWTw/rjf9OwQ6OxylVCOmh8jUaYt2HmPmukP8emgU4+PaXbyCUkpVQxOMAiD3VClPzdtF9zA/Hrmxm7PDUUo1AZpgFFsO5zDqldWk5xfz9NieuOmIMaVUHdC/JM3cmr0Z3DNjI+6uLnzx28H07xDg7JCUUk2EnuRvxt5YmcxzixLp3LolH08aSBt/L2eHpJRqQjTBNFObD+fwwveJ3NSrDS/9IhYvd1dnh6SUamL0EFkzdKrExl/mbCPMvwXP/ry3JhellENoD6YZmr5oD/szT/Lprwfi56WTVyqlHEN7MM3Mop1pfLD2IL8aEsmVnYOdHY5SqgnTBNOMHMw8yV8/306fCH+91kUp5XCaYJqJpOP53P/RJlxchNfv6oenm553UUo5lp6DaSLKyw0ZBcV4ubni4ebC5sM5rNuXxe60PPKLy/jpYDa+nm68dmc/IgK8nR2uUqoZ0ATTiJXayvk8PoXP4o+wJy2P4rJyRMDNRSi1GVxdhOjWLXF3deEP13Zm4pWRBLX0dHbYSqlmQhNMI7QqKYP/LE3ieF4xR0+cIibMj4lXRtIuoAUnCkspLLUxICqQuA4B+OooMaWUk2iCaURKysp5YXEi76zeT2SQN93a+PL4LTGMiAlFRJwdnlJKnUUTTCORkJrH1K+2sy0ll7sGtuex0TF6gaRSqkHTBNNAbdifxddbjpJ0PP/0oTA/LzfevKsfN/YKc3Z4Sil1UZpgGqD/LtvLi0uS8PV0o1eEPwM7BhIT5se4/hG08vZwdnhKKVUjmmAamG+3HuXFJUmMjW2r84QppRo1TTANhDGG73Yc4x/f7KRf+1a8ML6P3vhLKdWoOewvmIj8T0TSRWRnpbJAEVkiInut54BKy6aKSLKIJIrIyErl/UVkh7XsVbGGS4mIp4h8ZpVvEJHISnUmWp+xV0QmOmob64oxhhcWJzLl081EBHjznwl9NbkopRo9R/4V+wAYdU7ZI8AyY0w0sMx6j4jEABOAHladN0Sk4tjQm8BkINp6VLQ5CcgxxnQGXgamW20FAk8AA4EBwBOVE1lDcyDzJL98/ydeX7GPCVe0Y/4fhtIuUK+0V0o1fg47RGaMWV25V2EZA1xjvZ4JrAT+bpXPNsYUAwdEJBkYICIHAT9jzDoAEfkQGAsstOo8abX1BfCa1bsZCSwxxmRbdZZgT0qz6noba6vMVs63W1PZdDiHedtScRHh0Zu68euhHXFx0etZlFJNQ32fgwk1xqQBGGPSRKS1VR4OrK+0XopVVmq9Pre8os4Rq60yEckFgiqXV1Gn3hWV2sjIL6ZtqxYIMH9HGq8sSWJ/5klaebsTE+bHi7/oo/ODKaWanIZykr+qf9tNNeW1rXP2h4pMxn74jfbt2188yktgjOGd1ft5cXESJbZyfD3d8HR3IbOghC6hLXnr7v6M7KFX4Culmq76TjDHRSTM6r2EAelWeQrQrtJ6EUCqVR5RRXnlOiki4gb4A9lW+TXn1FlZVTDGmHeAdwB69O5r9mcUEBXsU+Uf/YqeSERAixolhemLEnlr1T5GxIRyddcQdqflUVRazrDoYEb3bourHgpTSjVx9Z1g5gITgWet528rlX8qIi8BbbGfzN9ojLGJSL6IDAI2APcC/z2nrXXAOGC5McaIyPfAM5VO7I8Apl4ssOSMAq57cRUdgryZck1nbu0XjrurC1uPnODTDYdYuOMY+cVltA/05q8juxLg7cGJUyV8Hp9CmL8XJwpL8fVyIzq0JT8kZ7E6KYM7BrTnmVt7ai9FKdUsiTFVHj26/IZFZmHvSQQDx7GP7PoGmAO0Bw4D4yudjP8/4D6gDHjYGLPQKo/DPiKtBfaT+3+wEokX8BHQF3vPZYIxZr9V5z7gUSuUfxlj3r9YvF169jFPzpjHnPgjbD1yAg83F0JaenL0xClaeroxqmcberb149ONh0k6XnC6Xhs/L3JPleLj6UZBcSlFpeV0CW3JsOgQHrmxG+463Fgp1YSJyCZjTFyVyxyVYBqbuLg4Ex8fjzGGZbvT2Xgwm/S8Irq08WXi4Eh8PO2dvZKyctbuy8TL3X5jrx5t/QBwc3HhRGEJhSU2HWaslGo2NMHUQEWCUUopVXPVJRg9fqOUUsohNMEopZRyCE0wSimlHEITjFJKKYfQBKOUUsohNMEopZRyCE0wSimlHEITjFJKKYfQCy0t1nT/e2tR1R/IbaB1goHMS6xT289qyHV0P9jpfjijNvuiIW+TM/dDB2NMSJVrGmP0YU+y79RXvXqsE98Et0n3g+6Hy6pT233RkLepoe4HPUR2xrx6rFdfdWqrIW+T7ofa16mthrxNuh9qX6e2avxZeoisCROReHOBOYKaE90PdrofztB9Yefo/aA9mKbtHWcH0EDofrDT/XCG7gs7h+4H7cEopZRyCO3BKKWUcghNMEoppRxCE0wjIyL/E5F0EdlZqayPiKwTkR0iMk9E/KxyDxF53yrfJiLXVKpzu4hsF5FdIvJc/W9J7YlIOxFZISK7rfgfssoDRWSJiOy1ngMq1ZkqIskikigiI6toc27lfdoY1OV+aMzfB7j0fSEiQdb6BSLy2gXabPLfier2Q518J2ozdlofznsAVwH9gJ2Vyn4CrrZe3wdMs15PAd63XrcGNmH/pyIIOAyEWMtmAtc7e9suYR+EAf2s175AEhADPAc8YpU/Aky3XscA2wBPIArYB7hWau824NPK+7QxPOpqPzT270Mt94UPMBT4LfBaFe01l+9Elfuhrr4T2oNpZIwxq4Hsc4q7Aqut10uAn1uvY4BlVr104AQQB3QEkowxGdZ6SyvVafCMMWnGmM3W63xgNxAOjMH+i4D1PNZ6PQaYbYwpNsYcAJKBAQAi0hL4E/B0vW1AHanD/dCovw9w6fvCGHPSGPMDUHRuW83pO1HNfqiT74QmmKZhJ/Az6/V4oJ31ehswRkTcRCQK6G8tSwa6iUikiLhh/7K1oxESkUigL7ABCDXGpIH9Fw17rw3sv2BHKlVLscoApgEvAoX1Ea+jXOZ+aDLfB6jxvqhOc/pOXEidfCc0wTQN9wFTRGQT9m5xiVX+P+x/ROKBV4C1QJkxJgd4APgMWAMcBMrqN+TLZ/2n+SXwsDEmr7pVqygzIhILdDbGfO2I+OrL5e6HpvJ9gEvaFxeqH0vz+k5Uqa6+E26XWkE1PMaYPcAIABHpAtxslZcBf6xYT0TWYk3oaYyZhzXlg4hMBmz1G/XlERF37L9AnxhjvrKKj4tImDEmTUTCgHSrPIWz//uKAFKBwUB/ETmI/XehtYisNMZcUx/bUBfqaD80+u8DXPK+uJDm9p24oLr4TmgPpgkQkdbWswvwD+At6723iPhYr2/A3ntJOKdOAPA74D0nhF4rIiLADGC3MealSovmAhOt1xOBbyuVTxART+tQYTSw0RjzpjGmrTEmEvuJzqRG9oekTvaD1Vaj/T5ArfZFlZrhd6K6ti7/O+HsUQ/6uORRIrOANKAU+3+kk4CHsI8WSQKe5cwMDZFAIvYTfUuxT6tduZ0E6zHB2dt1iftgKGCA7cBW63ET9pEvy7D30pYBgZXq/B/2UVOJwI1VtBlJ4xsxVGf7oTF/Hy5jXxzEPmCmwPpdimmm34kq90NdfCd0qhillFIOoYfIlFJKOYQmGKWUUg6hCUYppZRDaIJRSinlEJpglFJKOYQmGKXqiYgYEfmo0ns3EckQkfm1bK+ViPyu0vtratuWUo6gCUap+nMS6CkiLaz3NwBHL6O9VtgvgFOqQdIEo1T9Wog1lQ9wB/aL2YDT9+z4xroHx3oR6W2VPyn2+wCtFJH9IvKgVeVZoJOIbBWR562yliLyhYjsEZFPrCu7lXIKTTBK1a/Z2Kdr8QJ6Y5/ptsJTwBZjTG/gUeDDSsu6ASOxT6//hDXf1CPAPmNMrDHmr9Z6fYGHsd+qoSMwxIHbolS1NMEoVY+MMduxT0FyB/DdOYuHAh9Z6y0HgkTE31q2wNjv45KJfaLC0At8xEZjTIoxphz7NCGRdboBSl0CnU1Zqfo3F3gBuAb7HFEVqpxO33ourlRm48K/uzVdTymH0x6MUvXvf8A/jTE7zilfDdwF9hFhQKap/l4e+djv/6NUg6T/3ShVz4wxKcB/qlj0JPC+iGzHfjfFiVWsU7mdLBH5UUR2Yh88sKCuY1XqcuhsykoppRxCD5EppZRyCE0wSimlHEITjFJKKYfQBKOUUsohNMEopZRyCE0wSimlHEITjFJKKYf4f6uAhhjZ82shAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "plot = Barnet_prices.plot(kind='line',x='Month',y='Avg.Price')\n",
    "plot.set_ylabel('Price')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "aWTPqSJeqHnC"
   },
   "source": [
    "To limit the number of data points you have, you might want to extract the year from every month value your *Month* column. \n",
    "\n",
    "To this end, you *could* apply a ***lambda function***. Your logic could work as follows:\n",
    "1. look through the `Month` column\n",
    "2. extract the year from each individual value in that column \n",
    "3. store that corresponding year as separate column. \n",
    "\n",
    "Whether you go ahead with this is up to you. Just so long as you answer our initial brief: which boroughs of London have seen the greatest house price increase, on average, over the past two decades? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "e0DF92cyqnu8"
   },
   "outputs": [],
   "source": [
    "clean_properties['Year'] = clean_properties['Month'].apply(lambda x: x.year)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "2knuTxAEqoJ4"
   },
   "source": [
    "**3. Modeling**\n",
    "\n",
    "Consider creating a function that will calculate a ratio of house prices, comparing the price of a house in 2018 to the price in 1998.\n",
    "\n",
    "Consider calling this function create_price_ratio.\n",
    "\n",
    "You'd want this function to:\n",
    "1. Take a filter of dfg, specifically where this filter constrains the London_Borough, as an argument. For example, one admissible argument should be: dfg[dfg['London_Borough']=='Camden'].\n",
    "2. Get the Average Price for that Borough, for the years 1998 and 2018.\n",
    "4. Calculate the ratio of the Average Price for 1998 divided by the Average Price for 2018.\n",
    "5. Return that ratio.\n",
    "\n",
    "Once you've written this function, you ultimately want to use it to iterate through all the unique London_Boroughs and work out the ratio capturing the difference of house prices between 1998 and 2018.\n",
    "\n",
    "Bear in mind: you don't have to write a function like this if you don't want to. If you can solve the brief otherwise, then great! \n",
    "\n",
    "***Hint***: This section should test the skills you acquired in:\n",
    "- Python Data Science Toolbox - Part One, all modules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "cKTyr437UgDa"
   },
   "outputs": [],
   "source": [
    "df = clean_properties.groupby(by=['London Borough','Year']).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>London Borough</th>\n",
       "      <th>Year</th>\n",
       "      <th>Avg.Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Barking &amp; Dagenham</td>\n",
       "      <td>1995</td>\n",
       "      <td>51817.969390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Barking &amp; Dagenham</td>\n",
       "      <td>1996</td>\n",
       "      <td>51718.192690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Barking &amp; Dagenham</td>\n",
       "      <td>1997</td>\n",
       "      <td>55974.262309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Barking &amp; Dagenham</td>\n",
       "      <td>1998</td>\n",
       "      <td>60285.821083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Barking &amp; Dagenham</td>\n",
       "      <td>1999</td>\n",
       "      <td>65320.934441</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       London Borough  Year     Avg.Price\n",
       "0  Barking & Dagenham  1995  51817.969390\n",
       "1  Barking & Dagenham  1996  51718.192690\n",
       "2  Barking & Dagenham  1997  55974.262309\n",
       "3  Barking & Dagenham  1998  60285.821083\n",
       "4  Barking & Dagenham  1999  65320.934441"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.reset_index(inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_price_ratio(d):\n",
    "    avg_1998 = float(d['Avg.Price'][d['Year']==1998])\n",
    "    avg_2018 = float(d['Avg.Price'][d['Year']==2018])\n",
    "    ratio = [avg_1998/avg_2018]\n",
    "    return ratio\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[0.20261973503252542]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_price_ratio(df[df['London Borough']=='Camden'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Barking & Dagenham', 'Barnet', 'Bexley', 'Brent', 'Bromley',\n",
       "       'Camden', 'City of London', 'Croydon', 'Ealing', 'Enfield',\n",
       "       'Greenwich', 'Hackney', 'Hammersmith & Fulham', 'Haringey',\n",
       "       'Harrow', 'Havering', 'Hillingdon', 'Hounslow', 'Islington',\n",
       "       'Kensington & Chelsea', 'Kingston upon Thames', 'Lambeth',\n",
       "       'Lewisham', 'Merton', 'Newham', 'Redbridge',\n",
       "       'Richmond upon Thames', 'Southwark', 'Sutton', 'Tower Hamlets',\n",
       "       'Waltham Forest', 'Wandsworth', 'Westminster'], dtype=object)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['London Borough'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "final ={}\n",
    "for b in df['London Borough'].unique():\n",
    "    bor = df[df['London Borough'] == b]\n",
    "    final[b] = create_price_ratio(bor)\n",
    "ratios = pd.DataFrame(final)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "ratios = ratios.T\n",
    "ratios.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>London Borough</th>\n",
       "      <th>Price Ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Barking &amp; Dagenham</td>\n",
       "      <td>0.204223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Barnet</td>\n",
       "      <td>0.229453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bexley</td>\n",
       "      <td>0.235351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Brent</td>\n",
       "      <td>0.204309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bromley</td>\n",
       "      <td>0.244213</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       London Borough  Price Ratio\n",
       "0  Barking & Dagenham     0.204223\n",
       "1              Barnet     0.229453\n",
       "2              Bexley     0.235351\n",
       "3               Brent     0.204309\n",
       "4             Bromley     0.244213"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratios.rename(columns={'index':'London Borough',0:'Price Ratio'},inplace=True)\n",
    "ratios.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          London Borough  Price Ratio\n",
      "11               Hackney     0.161335\n",
      "30        Waltham Forest     0.171387\n",
      "27             Southwark     0.181275\n",
      "22              Lewisham     0.183512\n",
      "32           Westminster     0.186791\n",
      "24                Newham     0.188488\n",
      "6         City of London     0.188622\n",
      "13              Haringey     0.194756\n",
      "19  Kensington & Chelsea     0.196755\n",
      "21               Lambeth     0.201704\n"
     ]
    }
   ],
   "source": [
    "top_10 = ratios.sort_values(by='Price Ratio',ascending=True).head(10)\n",
    "print(top_10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "NzYUI7FxJpgv"
   },
   "source": [
    "### 4. Conclusion\n",
    "What can you conclude? Type out your conclusion below. \n",
    "\n",
    "Look back at your notebook. Think about how you might summarize what you have done, and prepare a quick presentation on it to your mentor at your next meeting. \n",
    "\n",
    "We hope you enjoyed this practical project. It should have consolidated your data hygiene and pandas skills by looking at a real-world problem involving just the kind of dataset you might encounter as a budding data scientist. Congratulations, and looking forward to seeing you at the next step in the course! "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Based on the results, we can say that the London Boroughs that had the largest \n",
    "## price increase in housing were Hackney, Waltham Forest, Southwark, Lewisham, and \n",
    "## Westminster. "
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "Springboard Data Science Career Track Unit 4 Challenge - Tier 3 Complete .ipynb",
   "provenance": []
  },
  "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}