Practical-06-Managing Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center>\n",
    "<h1>5SSG2059 Geocomputation</h1>\n",
    "<h2>Practical 6: Data Manipulation</h2>\n",
    "</center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Manipulating Data\n",
    "\n",
    "As we've discussed in lectures, manipulating data can be a major component of data analysis. This week will look at some further ways to manipulate data that might be useful for you when analysing the data for your final report. \n",
    "\n",
    "Specifically we will:\n",
    "1. recap some useful sorting and selecting methods\n",
    "2. see how we can combine two `DataFrames` together using common properties for further analysis\n",
    "3. look at how we can group data for analysis (e.g. grouping LSOAs for borough-level analysis)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup\n",
    "\n",
    "As usual we will be using pandas and doing some data analysis plotting, so we need to import the relevant packages (note the aliases used for reference below):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will start working with the initial LSOA data that we have been using previously:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_df = pd.read_csv(\n",
    "    'https://github.com/kingsgeocomp/geocomputation/blob/master/data/LSOA%20Data.csv.gz?raw=true',\n",
    "    compression='gzip', low_memory=False) # The 'low memory' option means pandas doesn't guess data types"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Later we'll look at how we can add more data to this `DataFrame` later, but first let's just check what columns of data we have:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Okay, now we have our data loaded and we've reminded ourselves of what the data set contains (maybe by consulting the [metadata](https://github.com/kingsgeocomp/geocomputation/raw/master/Data/LSOA_metadata.xlsx)) we can move on. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Quick Recap: Sorting and Selecting "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Task 1:\n",
    "\n",
    "1. Write some code to list the **LSOA11 Names** and **Population Densities** where the population density is more than **four standard deviations greater than the mean** population density of all London LSOAs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_df[???][['LSOA11NM','POPDEN']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Hopefully, your code returns 19 LSOAs and looks _something_ like this:\n",
    "\n",
    "| | **LSOA11NM** | **POPDEN** |\n",
    "|-|--------------|------------|\n",
    "| 1717 | Hackney 016C | 344.8 |\n",
    "| 1861 | Hammersmith and Fulham 015B | 378.7 |\n",
    "| 1910 | Hammersmith and Fulham 002C | 368.1 |\n",
    "| 2777 | Kensington and Chelsea 021C | 684.7 |\n",
    "| ... | ... | ... |\n",
    "| 4747 | Islington 011F | 502.5 |\n",
    "| 4753 | Islington 006F | 359.4 |\n",
    "| 4793 | Westminster 014F | 394.1 |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. Good, now what about if we want to find the top 10 LSOAs in terms of population density, and examine how many households there are in those LSOAs? Recall that we did this last week in one line:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. In a single line of code create a new `df` containing information about the name, population density and number of usual residents for the seven least populated LSOAs (in terms of usual residents). Then use another line of code to print the new `df`: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Taking a Random Sample of Data\n",
    "\n",
    "Of course, sometimes you don't want a particular range of data, you want a _random sample_ so that you can either \n",
    "a. get a better sense of the data, or \n",
    "b. perform some kind of test with a subsample before replicating on the full data set. \n",
    "\n",
    "Pandas has [got you covered](http://pandas.pydata.org/pandas-docs/version/0.18.1/indexing.html#selecting-random-samples) with a huge range of options, including sampling with replacement, sample weights, row numbers and a fraction of the data set. \n",
    "\n",
    "Let's look at some simple examples:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_df.sample(n=5)[ ['LSOA11NM','POPDEN','USUALRES'] ] # Sample of size 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_df.sample(n=5)[ ['LSOA11NM','POPDEN','USUALRES'] ] # This will not give you the same sample"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that even though the two lines of code above are identical we return a different (random) sample of rows. This is useful but what if we want to give our code to someone else to that they would get the same (random) sample of rows?  To do this we can specify the `random_state` argument:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_df.sample(n=5, random_state=42)[ ['LSOA11NM','POPDEN','HHOLDS'] ] "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Task 2\n",
    "\n",
    "1. What should you add here in order to get the _same_ random sample as above?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_df.sample(???)[ ['LSOA11NM','POPDEN','HHOLDS'] ] "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. While setting `random_state` ensure that we get a _different_ sample:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_df.sample(n=5, random_state=???)[ ['LSOA11NM','POPDEN','HHOLDS'] ] "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also specify the fraction of the `DataFrame` we want to sample, rather than an absolute number of observations (think about why this is useful for when we don't know what size `DataFrame` our code might be used with):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_df.sample(frac=0.002)[ ['LSOA11NM','POPDEN','USUALRES'] ] # Sample a fraction of the rows (here 0.2%)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, the code above has automatically been sampling rows of data, but we can also sample columns by specifying the `axis` of the `DataFrame` we want to sample: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_df.sample(n=5, axis=1).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combining Data\n",
    "\n",
    "Up until this point we have been working with a dataset of ~48 variables (columns) for the LSOAs. But what if we have additional data for LSOAs that we want to work with together with our original data, for example to look for correlations between variables. Here we will look at how to combine two datasets that have data for individual LSOAs:\n",
    "1. our original data\n",
    "2. data for air quality in each LSOA\n",
    "\n",
    "Combining these data would be useful, for example, to examine relationships between air quality and socio-economic and other variables. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When joining two data sets, each of the following functions does something _slightly_ different, though they can all produce the _same output_:\n",
    "- `pd.merge` enables us to [combine](http://pandas.pydata.org/pandas-docs/stable/merging.html#database-style-dataframe-joining-merging) two dataframes based on a column that is common between them\n",
    "- `pd.concatenate` [combines](http://pandas.pydata.org/pandas-docs/stable/merging.html#concatenating-objects) dataframes regardless of common attributes. \n",
    "- `df.join` is used to [combine](http://pandas.pydata.org/pandas-docs/stable/merging.html#joining-on-index) two dataframes when they share a common `index` (e.g. a `DateTimeIndex` in timeseries data)\n",
    "Notice that `merge` and `concatenate` are functions associated with the pandas library, while `join` is a method associated with a data frame. We'll then look at how to analyse variables in the combined `DataFrame` we produce using correlation later in the module. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Air Quality Data\n",
    "\n",
    "Metadata about the air quality data are included in the [metadata](https://github.com/kingsgeocomp/geocomputation/blob/master/data/LSOA_metadata.xlsx?raw=true) file. The data themselves are hosted online and can be read using:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "aq_df = pd.read_csv(\n",
    "    'https://github.com/kingsgeocomp/geocomputation/blob/master/data/LSOA_AirQuality.csv.gz?raw=true',\n",
    "    compression='gzip', low_memory=False) # The 'low memory' option means pandas doesn't guess data types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "aq_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Task 3:\n",
    "\n",
    "Familiarise yourself with the data you have just loaded in and compare it to the data we have worked with previously. To do this you might:\n",
    "\n",
    "1. Check the column names and data types of the air quality data file and compare to the metadata file (hint: use `info()` method for `DataFrame`s - you might need to google this)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. calculate descriptive statistics for the air quality data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. compare the shapes of the two `DataFrames`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From your exploration of the new data and comparison with the original LSOA data you might notice a few things:\n",
    "1. They have the same number of rows\n",
    "2. They have different numbers of columns\n",
    "3. They share one column name in common (`LSOA11CD`)\n",
    "\n",
    "Check you can see these observations for yourself. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4. create a single distribution plot for all PM25 measures _except_ the Standard Deviation\n",
    "\n",
    "To quickly find all of the PM25 measures you might want to read up on 'list comprehensions' ([here](https://medium.com/better-programming/list-comprehension-in-python-8895a785550b) and [here](https://medium.com/quick-code/advanced-python-made-easy-eece317334fa)) and then have a look at [this StackOverflow answer](https://stackoverflow.com/questions/44357731/filter-list-of-strings-starting-with-specific-keyword).\n",
    "\n",
    "_Note:_ this can be done in as few as three lines, though you might find it easier to _first_ find the columns as a list and _then_ iterate over these while creating a `distplot` for each. Don't forget to add the legend!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is called a 'list comprehension' and it's one of\n",
    "# the powerful techniques for processing/extracting data\n",
    "# in/from lists. You will need to think about how you \n",
    "# capture only the PM25 measures *and* exclude the sd measure...\n",
    "cols = [???]\n",
    "print(cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "f, ax = plt.subplots(1, figsize=(12, 5))\n",
    "for c in cols:\n",
    "    sns.distplot(???)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Your output should look like this (allowing for the fact that your colours may differ and that I forgot to change the x-axis label):\n",
    "    \n",
    "![Distribution Plot](https://github.com/kingsgeocomp/geocomputation/raw/master/img/PM25%20Measures.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge\n",
    "\n",
    "If we have columns in each dataframes that contain the same identifiers, then we can use this column to define how the two dfs should be joined together. For example, the data we are working with are for LSOAs (distinct geographical regions) - if any additional data we have is also for LSOAs, as long as we we have a common way of identifying the LSOAs in each dataframe we can `merge` them. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Hopefully from the task above that we have a common identifier in both the orginal data `my_df` and the new air qualiy data `aq_df`: `LSOA11CD`. The `LSOA11CD` is a unique identifier code for each LSOA. We can use this to match rows of data in `my_df` (each of which is for a particular LSOA) with the corresponding rows in `aq_df` (which are also for individual LSOAs)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the common identifier identified, we now need to decide what type of join we want to do. Recall from this week's lecture that there are four main types of 'join':\n",
    "1. left\n",
    "2. right\n",
    "3. outer\n",
    "4. inner\n",
    "\n",
    "We could use any of the above depending on our objectives. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we'll do a **left join**, where the left `df` will be the original data and the right `df` will be our new air quality data. If we had more rows in the _left_ data set then this would ensure that we didn't lose them when they didn't find a match in the _right_ data set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge_df = pd.merge(my_df, aq_df, how='left', on='LSOA11CD')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Okay, now let's check what the columns are in the new `DataFrame` we just created: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(merge_df.shape)\n",
    "print(merge_df.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And let's check from a sample of the data how the rows look: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge_df.sample(n=5, random_state=42)[ \n",
    "    ['LSOA11NM','POPDEN','HHOLDS', 'PM25mean', 'PM25min', 'PM25max', 'PM25sd'] ] "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Hopefully it looks good so far. Let's review what we did the with `pd.merge` function above:\n",
    "```python\n",
    "pd.merge(my_df, aq_df, how='left', on='LSOA11CD')\n",
    "```\n",
    "\n",
    "1. `my_df` is the left `df`\n",
    "2. `aq_df` is the right `df` \n",
    "3. `how` defines what type of join is to be done\n",
    "4. `on` is the column we want to use as the common identifier to 'join on' "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So above, each value in the `LSOA11CD` column in `aq_df` is matched with the same value in the `LSOA11CD` column in `my_df` and the rows those values are found in are combined. The figure below illustrates the process (and look back to the lecture slides and see this [nice tutoral](https://www.shanelynn.ie/merge-join-dataframes-python-pandas-index-1/)). \n",
    "\n",
    "![Illustration of the Pandas merge function](http://pandas.pydata.org/pandas-docs/stable/_images/merging_merge_on_key_left.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check you understand how something similar to the image above has been done for our LSOA data. Remember you can read [the documentation](http://pandas.pydata.org/pandas-docs/stable/merging.html#database-style-dataframe-joining-merging) for more detailed explanation. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Even though we used the pandas `merge` function here, we are doing what we called a _join_ in the lecture; the only difference between pandas `merge` and `join` is that the former uses a common column whereas the latter uses a common index. The 'merge' column can do all four of the joins we have considered (left, right, outer and inner).   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Checks\n",
    "\n",
    "A common indicator that something has not gone quite according to plan is the existence of NaNs (_Not a Number_) in the merged data set. To check if there were any missing values introduced into our new `DataFrame` we can do [a quick check](https://stackoverflow.com/a/29530601) and look at [which columns contain NaNs](https://stackoverflow.com/questions/36226083/how-to-find-which-columns-contain-any-nan-value-in-pandas-dataframe-python) or [which rows]():"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Any NaNs at all check\n",
    "print(\"Any NaNs?\")\n",
    "print(merge_df.isnull().values.any())\n",
    "\n",
    "# Column check\n",
    "print(\"\\nWhich columns contain NaNs?\")\n",
    "print(merge_df.columns[merge_df.isna().any()].tolist())\n",
    "\n",
    "# Row check\n",
    "print(\"\\nWhich rows contain NaNs?\")\n",
    "print(merge_df[merge_df.isnull().any(axis=1)])\n",
    "\n",
    "# How many NaNs\n",
    "print(\"\\nHow many NaNs in the dataframe?\")\n",
    "print(merge_df.isnull().sum().sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Hopefully you received a `False` response! If so, this is more evidence the join worked (if not you might want to check what you did above and ask for help). \n",
    "\n",
    "Let's save these data for later - they may be useful for your final report!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge_df.to_csv(\"Merged LSOA Data.csv.gz\", compression='gzip', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_Note:_ If you really want to check what the join has done, you might open the file you just saved in Excel to have a look."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Task 4\n",
    "\n",
    "Explore the air quality data to get an understanding of what they might show you in relation to other variables in the data set. For example:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Find the maximum values for each of three PM2.5 measures (min, mean, max):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. Find the population densities of the LSOAs with highest maximum values for _each_ of the three measures of PM2.5 (there are at least two ways to do this).  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. Create a boxplot to compare the distributions of the **mean values** of each of the four main types of pollutants (PM2.5, PM10, NOx, NO2)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. Create four scatter plots on a 2x2 grid in a single figure (use a loop) to visualise the relationship between area within 250m of a major road (in km$^2$) and the minimum values of each pollutant. As usual, [StackOverflow](https://stackoverflow.com/questions/45765735/arranging-multiple-for-loop-categorical-plots-with-seaborn) can help you here.\n",
    "\n",
    "_Note:_ if you find this task difficult you may want to skip this exercise in practical to ensure you can work through the rest of the practical while help is available."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# the columns in merge_df we want to plot\n",
    "target = ['PM25min', 'PM10min','NOxmin','NO2min']\n",
    "\n",
    "# a new series to hold Roads Area in sq km (not sq m) [this will look tidier on the axes]\n",
    "RoadsArea_km = ???\n",
    "\n",
    "# setup the figure with four subplots in 2x2 format\n",
    "# figsize tip from: https://stackoverflow.com/a/41666345\n",
    "fig, axes = plt.subplots(2, 2, sharex=True, figsize=(8,8))\n",
    "axes = axes.flatten()\n",
    "\n",
    "# loop over the axes and the columns -- \n",
    "# https://stackoverflow.com/questions/45765735\n",
    "for ax, col in zip(axes, merge_df[target].columns):\n",
    "    \n",
    "    ???\n",
    "\n",
    "plt.suptitle(\"Minimum Pollution Levels and Proximity to Roads\")\n",
    "fig.tight_layout(rect=[0, 0.03, 1, 0.95])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Your output should look like this:\n",
    "\n",
    "![Pollution and Proximity to Roads](https://github.com/kingsgeocomp/geocomputation/raw/master/img/Pollution%20and%20Roads%20Proximity.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Grouping Data\n",
    "\n",
    "Often in geographical data, we have data specified for different aerial units; counties, State parks, constituencies, etc. And frequently, these are units are hierarchical; counties are sub-units within states (e.g. Yorkshire is within England), postcode units within postcode areas (e.g. WC2R 2LS is within WC2R). This is particularly true for census data and so for the data we are working with; Lower Super Output Areas (LSOAs) are within London Boroughs. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we want to investigate differences or similarities at the borough-level, we are going to have to group the data for individual LSOAs into their respective boroughs. Then we can summarise the boroughs as a whole. \n",
    "\n",
    "As you can see from the [metadata](https://github.com/kingsgeocomp/geocomputation/blob/master/data/LSOA_metadata.xlsx?raw=true), the _LAD11CD_ and _LAD11NM_ columns contains Local Authority District (i.e. Borough) IDs and names. These columns are useful as they specify for every LSOA (which are on different rows) and ID and the name of the borough each LSOA lies within. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could check the contents of these columns using the `unique()` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(merge_df.LAD11NM.unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are two ways we could use these columns to analyse our data at the borough level:\n",
    "1. create new `Dataframes` for each individual borough\n",
    "2. tell pandas to group the data using the values in the borough column \n",
    "\n",
    "The first approach might be useful if we want to examine one or few particular boroughs in detail. We could create these new `DataFrames` using the selection methods we have seen previously (but also [others you could learn about](http://pandas.pydata.org/pandas-docs/stable/indexing.html)). However, if we want to work with data for all London boroughs, this method would not be particularly easy to work with. \n",
    "\n",
    "So for the second approach, the pandas library has another data structure known as `DataFrameGroupBy` which is useful in this situation (see [documentation here](http://pandas.pydata.org/pandas-docs/stable/groupby.html)). We'll examine this approach in more detail now. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_df.groupby('LAD11NM')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So calling 'groupby' returns a `DataFrameGroupBy` object. This behaves _like_ a regular dataframe but now it represents groupings of data so the output of some functions changes a great deal:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs = my_df.groupby('LAD11NM')\n",
    "boroughs.head(1) # Note the output now!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can retrieve an individual sub-group from within the grouped data frame like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs.get_group(\"City of London\")  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that to access the data for this group, we pass a value from the column we used to define the groups using `groupby()` previously. As we used _LAD11NM_ to specify our groups above, here we were able to type the name we wanted (_\"City of London\"_). But if we had used _LAD11CD_ to specify the groups, we would have had to pass _E09000001_."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs.get_group('Tower Hamlets').sample(4, random_state=42)[['LSOA11CD','LAD11NM','USUALRES','HHOLDRES']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But when we try to get the shape of the object we find it's slightly different from a normal `DataFrame` (you should get an error if you try this):\n",
    "```python\n",
    "boroughs.shape\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can’t use `shape` to find out how many elements in the boroughs `DataFrameGroupBy` object, but we can use our old favourite function `len()` (which works pretty much anywhere!). Compare the output for the next two lines of code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(boroughs) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(merge_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The length of `boroughs` is the number of groups in the `DataFrameGroupBy` object, whereas the length of `merge_df` is the number of rows in the `DataFrame` object. Check you understand the difference! We can tell from this that there are 33 boroughs (groups) and 4835 LSOAs (rows).\n",
    "\n",
    "The difference between `merge_df` and `boroughs` also results in different output for other methods. What method gives you a row count?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge_df[\"LAD11NM\"].???"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs[\"LAD11NM\"].???"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "See how the `count` method for the `DataFrameGroupBy` object gives the count of LSOAs within in each borough (group) whereas the count method for the `DataFrame` object simply gives the count of the total number of LSOAs (rows). \n",
    "\n",
    "Note, the following two lines of input code would do exactly the same as the last two but using dot notation\n",
    "```python\n",
    "boroughs.LAD11NM.count()\n",
    "merge_df.LAD11NM.count()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using the `DataFrameGroupBy` object also allows us to describe the data by group (rather than for all of the LSOAs as we did before). For example, to find the mean values for the columns by borough we can use the `aggregate()` method: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs[['USUALRES','HHOLDRES','MedianPrice']].aggregate(np.mean).sample(5, random_state=42)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note how the `aggregate()` method makes a call to the numpy function `mean`; this is why we needed to `import numpy as np` in the setup section at the start of the notebook. You can aggregate on a mix of `numpy` and other functions such as those included with the dataframe object itself:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs[['MedianPrice']].aggregate(['min', np.median, np.mean, max]).sample(5, random_state=42)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice also that the `aggregate` method returns a `DataFrame`. Check this by:\n",
    "1. printing the type of object of `bMeans` \n",
    "2. printing the `POPDEN` and `HHOLDS` columns of the new `DataFrame`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bMeans = boroughs[['USUALRES','HHOLDRES']].aggregate(np.mean)\n",
    "type(bMeans)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(bMeans[['USUALRES','HHOLDRES']].sample(5, random_state=42))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check you understand what has been produced here; `bMeans` contains the mean (average) of all columns in our original dataset but aggregated (grouped) by borough."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are other methods we can use on `DataFrameGroupBy`, for example `get_group()` gets the data (for LSOAs) for just one of the groups (boroughs):"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Asking questions about boroughs\n",
    "\n",
    "Let’s see how this all might be useful for answering a geographical question. Say we want to calculate what proportion of the population of the Borough of Harrow that identifies as 'White' ethnicity. We'll calculate this as a borough-level proportion, so it's the number of residents identifying as White divided by the number of usual residents. We have two ways of doing this that cover slightly different cases:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Case 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "harrow = merge_df.groupby('LAD11NM').get_group(\"Harrow\")\n",
    "print(\"The proportion of Harrow that is White ethnicity is: {0:.3f}\".format(harrow.???.sum() / harrow.???.sum()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Case 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bSums  = merge_df.groupby('LAD11NM').aggregate(np.sum)  #as above  \n",
    "\n",
    "sumW   = bSums.???.loc[\"Harrow\"]     #note: equivalent using dot notation is harrow_sumW = bSums.White.Harrow   \n",
    "sumRes = bSums.???.loc[\"Harrow\"]     #note: equivalent using dot notation is harrow_sumRes = bSums.USUALRES.Harrow\n",
    "\n",
    "print(\"The proportion of Harrow that is White ethnicity is: {0:.3f}\".format( sumW/sumRes ))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run the code above and check that you find that the proportion is 0.422. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Iterating over `DataFrameGroupBy`\n",
    "\n",
    "Finally, a short note to highlight that [iterating over groups](http://pandas.pydata.org/pandas-docs/stable/groupby.html#iterating-through-groups) in a `DataFRameGroupBy` object is much the same as looping over many other objects in python. For example, to iterate over all boroughs (groups) printing out the total population of each: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs = merge_df.groupby('LAD11NM')\n",
    "\n",
    "for key, ??? in boroughs:\n",
    "    popn = value.???.sum()\n",
    "    print(\"{0:8.0f} people in {1}\".format(???,???))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Have a think about what the code above does:\n",
    "1. iterating over the groups returns a tuple composed of `key` and `value`\n",
    "2.`value` allows us to get to the actual data in each group (borough)\n",
    "3. we can use `key` to get the label of each group (in this case the values of `LAD11NM` used to create the `GroupBy` object)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This next example is a bit obscure but _could_ be more useful in other contexts: we can `filter` grouped objects to remove cases when calculating aggregates. This might be most useful with our type of data if you had a special number (_e.g._ -1) to indicate that there was no value for a particular observation and didn't want those to be counted as part of your calculation of the `mean` or `sum` or `median` or some other value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def filter_func(x):\n",
    "    return x['MeanPrice'].mean() > my_df['MeanPrice'].mean() + my_df['MeanPrice'].std()\n",
    "\n",
    "print(\"Mean of whole df: {0:0.2f}\".format(my_df.MeanPrice.mean()))\n",
    "print(\"Mean of filtered df: {0:0.2f}\".format(boroughs.filter(filter_func)[['MeanPrice']].mean()[0]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And this next example standardises the Mean Price of an Airbnb property _by the mean price of properties in the borough_. So instead of applying the following to the data set as a whole:\n",
    "```python\n",
    "(my_df.MeanPrice - my_df.MeanPrice.mean())/my_df.MeanPrice.std()\n",
    "```\n",
    "We apply that to _each_ group separately."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def grp_standardise(x):\n",
    "    # x is a DataFrame of group values\n",
    "    x['Std'] = (x.MeanPrice-x.MeanPrice.mean())/x.MeanPrice.std()\n",
    "    return x\n",
    "\n",
    "my_df.groupby('LAD11NM').apply(grp_standardise).sample(10, random_state=42)[['LAD11NM','MeanPrice','Std']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Task 5\n",
    "\n",
    "Similar to the exercises above for pollutants, but this time for borough-level data:\n",
    "\n",
    "1. Find the total number of usual residents of the borough with highest **mean** value for each of the four pollutants.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Columns of interest\n",
    "target = ['PM25mean', 'PM10mean','NOxmean','NO2mean']\n",
    "\n",
    "# Maxima\n",
    "mtarget = merge_df[target].max()\n",
    "\n",
    "# Get the sum of usual residents\n",
    "bSums = boroughs.groupby('LAD11NM').apply(np.sum)['USUALRES']\n",
    "\n",
    "# Loop\n",
    "for t in target:\n",
    "    b = merge_df[merge_df[t]==mtarget[t]][['LAD11NM']].iloc[0,0]\n",
    "    \n",
    "    print(\"{0:8.0f} usual residents are in the borough {1} with the highest {2} max of {3:6.2f}.\".format(\n",
    "                bSums.loc[???], ???, t, mtarget[t]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Your results should be:\n",
    "```\n",
    "220338 usual residents are in the borough Camden with the highest PM25mean max of  19.52.\n",
    "219396 usual residents are in the borough Westminster with the highest PM10mean max of  31.35.\n",
    "219396 usual residents are in the borough Westminster with the highest NOxmean max of 168.37.\n",
    "219396 usual residents are in the borough Westminster with the highest NO2mean max of  70.71.\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There's quite a lot of stuff happening in the code above, perhaps some comments are in order?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. Create a barplot to compare the borough-level means of mean values of each of the four pollutants  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Re-Run the aggregate\n",
    "bMeans = boroughs.groupby(???).aggregate(???)\n",
    "\n",
    "#the columns in merge_df we want to plot\n",
    "target = ['PM25mean', 'PM10mean','NOxmean','NO2mean']\n",
    "\n",
    "#loop\n",
    "for i, col in enumerate(bMeans[target].columns):\n",
    "    fig, ax = plt.subplots()\n",
    "    g = sns.barplot(x=bMeans.index, y=???, data=bMeans);\n",
    "    ax.set(xlabel='Boroughs')\n",
    "    plt.xticks(???)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One of your results should look like this (although the x-labels have been chopped off):\n",
    "\n",
    "![NO2 Mean Barplot](https://github.com/kingsgeocomp/geocomputation/raw/master/img/Barplot%20Example.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pivot Tables\n",
    "\n",
    "For simplicity (because the data is already available and not too large) I've shamelessly taken key parts of [this tutorial](https://jakevdp.github.io/PythonDataScienceHandbook/03.09-pivot-tables.html) from the O'Reilly _Data Science Handbook_. I would suggest that you try performing your own pivot table analysis on the InsideAirbnb data that we started to work with as a 'supplementary' notebook a few weeks ago. I'd suggest looking at things like counts and median price by type of property and borough.\n",
    "\n",
    "The reason that we don't use the 'usual' LSOA data we were working with above is that Pivot Tables work _best_ with low-level count data and our already-aggregated data doesn't yield much that is particularly exciting in that context. We _can_ build pivot tables, but they just aren't really worth the time.\n",
    "\n",
    "So this is a dataset provided _by_ a library for demonstration purposes. Seaborn provides data on the survivors of the Titanic disaster, other libraries (_e.g._ PySAL) provided other demonstration data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "titanic = sns.load_dataset('titanic')\n",
    "print(titanic.shape)\n",
    "titanic.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So this is a _two-level_ grouping of the data (above we stuck to only one level) from which we derive the likelihood that someone of a given gender in a given class was likely to survive the disaster. If you're unclear what's going on try running this code in stages and exploring the data at each point: first, group by, then with survived, then with aggregate by mean (and for contrast type `count`!), and finally with `unstack`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "titanic.groupby(['sex', 'class'])['survived'].aggregate('mean').unstack()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And here's the same output, but using the `pivot_table` syntax, which is quite a bit easier to frame intellectually."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "titanic.pivot_table('survived', index='sex', columns='class')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is a new function `cut` which takes a pandas Series of continuous data and cuts it up into bins. THis gives us a better sense of how survival was impacted by age and class."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "age_range = pd.cut(titanic.age, [0, 18, 40, 65, 99])\n",
    "titanic.pivot_table('survived', index=['sex',age_range], columns='class')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### More Examples\n",
    "\n",
    "Some additional resources to do more experimentation with Group By and Pivot Tables can be found:\n",
    "- [Summarising, Aggregation and Grouping Data in Pandas](https://www.shanelynn.ie/summarising-aggregation-and-grouping-data-in-python-pandas/?amp)\n",
    "- [Pivot Table Explained](https://pbpython.com/pandas-pivot-table-explained.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Summary \n",
    "\n",
    "In this practical we have:\n",
    "1. had a recap of some sorting and selecting\n",
    "2. seen how to take a random sample of data\n",
    "3. introduced ourselves to the air quality data and combined it with our other data\n",
    "4. learned about the `DataFrameGroupBy` data structure. \n",
    "\n",
    "You now have the 'full' data set (combining the original data with the air quality data) that you can use for your final report. So start exploring!\n",
    "\n",
    "If you want to join your own LSOA data for analysis in your final report, please discuss with James before doing so. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Credits!\n",
    "\n",
    "#### Contributors:\n",
    "The following individuals have contributed to these teaching materials: Jon Reades (jonathan.reades@kcl.ac.uk), James Millington (james.millington@kcl.ac.uk)\n",
    "\n",
    "#### License\n",
    "These teaching materials are licensed under a mix of [The MIT License](https://opensource.org/licenses/mit-license.php) and the [Creative Commons Attribution-NonCommercial-ShareAlike 4.0 license](https://creativecommons.org/licenses/by-nc-sa/4.0/).\n",
    "\n",
    "#### Acknowledgements:\n",
    "Supported by the [Royal Geographical Society](https://www.rgs.org/HomePage.htm) (with the Institute of British Geographers) with a Ray Y Gildea Jr Award.\n",
    "\n",
    "#### Potential Dependencies:\n",
    "This notebook may depend on the following libraries: pandas, matplotlib, seaborn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "GSA2019",
   "language": "python",
   "name": "gsa2019"
  },
  "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}