Practical-10-Pandas Merge and Join.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center><h1>7SSG2059 Geocomputation 2016/17</h1></center>\n",
    "\n",
    "<h1><center>Practical 9: Merging and Joining Dataframes in Panadas</h1></center>\n",
    "\n",
    "<p><center><i>James Millington, 17 November 2016</i></center>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overview\n",
    "\n",
    "Up until this point we have been working with individual datasets. But what if we have data in two datasets that we want to work with together, for example to look for correlations between variables. This will be useful for example to examine relationshis between weather and air quality variables or between socio-economic classifications and amenity values in neighbourhoods. \n",
    "\n",
    "In this practical we will look at how to `merge` and `join` Pandas dataframes. There's also a `concatenate` function. Each of these functions are slightly different:\n",
    "- `merge` enables us to [combine](http://pandas.pydata.org/pandas-docs/stable/merging.html) two dataframes based on a column that is common between them\n",
    "- `join` is used to [combine](http://pandas.pydata.org/pandas-docs/stable/merging.html#database-style-dataframe-joining-merging) two dataframes when they share a common index (e.g. a DateTimeIndex)\n",
    "- `concatenate` [combines](http://pandas.pydata.org/pandas-docs/stable/merging.html#concatenating-objects) dataframes regardless of common attributes. \n",
    "\n",
    "Here we will look at how to use `merge` to combine the NS-Sec data with additional data for the LSOAs into a single dataframe, and we'll see how `join` is useful to combine weather and air quality data for the same measurement times into a single dataframe. We'll then look at how to analyse these combined dataframes using correlation and regression next week.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge\n",
    "\n",
    "If we have a column in each of two DataFrames that contains the same identifier for the remaining data, we can use the common identifier column to define how the two DataFrames are joined together. For example, the NS-SeC data 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 both DataFrames we can merge the Data Frames. \n",
    "\n",
    "### NS-Sec and Amenity Values\n",
    "\n",
    "The additional data you can use in conjunction with the NS-SeC data are found in `LSOA_ValuesData_London.csv` on KEATS. There are a variety of additional factors that you are free to explore, and you can read about them in the `AdditionalDataOverview.pdf` document also on KEATS. Smith (2010) used similar data in their study which will also likely help you to think about possible analyses you might make for your final report (e.g. between house prices and socio-economic indicators of LSOAs). \n",
    "\n",
    "These data are for housing and other amenity values for LSOAs in London. Consequently, we'll also use only NS-SeC data for London from now on - LSOA NS-SeC data for London only can be found in `Data_NSSHRP_UNIT_URESPOP_London.csv` on KEATS.\n",
    "\n",
    "The code below loads the two data files for London LSOAs into memory as pandas DataFrames, tidies up their column names and drops rows with missing data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[Errno 2] File b'Data_NSSHRP_UNIT_URESPOP_London.csv' does not exist: b'Data_NSSHRP_UNIT_URESPOP_London.csv'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-1-fc38e4971268>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0;31m#read NS-SeC data\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0mnsCN\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m\"CDU_ID\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"GEO_CODE\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"GEO_LABEL\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"F2084\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"F2085\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"F2094\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"F2102\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"F2107\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"F2114\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"F2119\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"F2127\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"F2133\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"F2136\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 5\u001b[0;31m \u001b[0mnsDF\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_csv\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'Data_NSSHRP_UNIT_URESPOP_London.csv'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mheader\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mskiprows\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0musecols\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mnsCN\u001b[0m\u001b[0;34m)\u001b[0m   \u001b[0;31m#read csv with headers, skipping notes row and no data column 15\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      6\u001b[0m \u001b[0mnsDF\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumns\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m\"CDU_ID\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"GEO_CODE\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"GEO_LABEL\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"Total\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"Group1\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"Group2\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"Group3\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"Group4\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"Group5\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"Group6\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"Group7\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"Group8\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"NC\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      7\u001b[0m \u001b[0mnsDF\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mnsDF\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdropna\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0maxis\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;36m0\u001b[0m\u001b[0;34m)\u001b[0m  \u001b[0;31m#drop rows with missing data\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/envs/gsa2019/lib/python3.7/site-packages/pandas/io/parsers.py\u001b[0m in \u001b[0;36mparser_f\u001b[0;34m(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, dialect, error_bad_lines, warn_bad_lines, delim_whitespace, low_memory, memory_map, float_precision)\u001b[0m\n\u001b[1;32m    683\u001b[0m         )\n\u001b[1;32m    684\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 685\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0m_read\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkwds\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    686\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    687\u001b[0m     \u001b[0mparser_f\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__name__\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/envs/gsa2019/lib/python3.7/site-packages/pandas/io/parsers.py\u001b[0m in \u001b[0;36m_read\u001b[0;34m(filepath_or_buffer, kwds)\u001b[0m\n\u001b[1;32m    455\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    456\u001b[0m     \u001b[0;31m# Create the parser.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 457\u001b[0;31m     \u001b[0mparser\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mTextFileReader\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfp_or_buf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwds\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    458\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    459\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0mchunksize\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0miterator\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/envs/gsa2019/lib/python3.7/site-packages/pandas/io/parsers.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, f, engine, **kwds)\u001b[0m\n\u001b[1;32m    893\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0moptions\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"has_index_names\"\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mkwds\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"has_index_names\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    894\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 895\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_make_engine\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    896\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    897\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mclose\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/envs/gsa2019/lib/python3.7/site-packages/pandas/io/parsers.py\u001b[0m in \u001b[0;36m_make_engine\u001b[0;34m(self, engine)\u001b[0m\n\u001b[1;32m   1133\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_make_engine\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"c\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1134\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mengine\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m\"c\"\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1135\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_engine\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mCParserWrapper\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0moptions\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1136\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1137\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mengine\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m\"python\"\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/envs/gsa2019/lib/python3.7/site-packages/pandas/io/parsers.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, src, **kwds)\u001b[0m\n\u001b[1;32m   1915\u001b[0m         \u001b[0mkwds\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"usecols\"\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0musecols\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1916\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1917\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_reader\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mparsers\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mTextReader\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msrc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwds\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1918\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0munnamed_cols\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_reader\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0munnamed_cols\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1919\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.TextReader.__cinit__\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.TextReader._setup_parser_source\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mFileNotFoundError\u001b[0m: [Errno 2] File b'Data_NSSHRP_UNIT_URESPOP_London.csv' does not exist: b'Data_NSSHRP_UNIT_URESPOP_London.csv'"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "#read NS-SeC data\n",
    "nsCN = [\"CDU_ID\",\"GEO_CODE\",\"GEO_LABEL\",\"F2084\",\"F2085\",\"F2094\",\"F2102\",\"F2107\",\"F2114\",\"F2119\",\"F2127\",\"F2133\",\"F2136\"]  \n",
    "nsDF = pd.read_csv('Data_NSSHRP_UNIT_URESPOP_London.csv', header=0, skiprows=[1], usecols=nsCN)   #read csv with headers, skipping notes row and no data column 15\n",
    "nsDF.columns = [\"CDU_ID\",\"GEO_CODE\",\"GEO_LABEL\",\"Total\",\"Group1\",\"Group2\",\"Group3\",\"Group4\",\"Group5\",\"Group6\",\"Group7\",\"Group8\",\"NC\"]  \n",
    "nsDF = nsDF.dropna(axis = 0)  #drop rows with missing data\n",
    "\n",
    "#read Additional Values Data\n",
    "valCN = [\"lsoa11cd\",\"median_price\",\"avg_distance_to_station\",\"positive_area\",\"moderate_area\",\"negative_area\"]\n",
    "valDF = pd.read_csv('LSOA_ValuesData_London.csv', header=0, usecols = valCN)  \n",
    "valDF = valDF.dropna(axis = 0)  #drop rows with missing data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we check these data we have just read into memory, we can see that the column in `valDF` named `lsoa11cd` uses the same labels for LSOAs as the `GEO_CODE` column in `nsDF`. Very handy!  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "#check what the common columns are\n",
    "nsDF.head()\n",
    "valDF.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By renaming `lsoa11cd` to `GEO_CODE` we can use it with the Pandas `merge` function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "valDF.columns = [\"GEO_CODE\",\"MedPrice\",\"MeanStationDist\", \"PosArea\", \"ModArea\", \"NegArea\"]  #rename to 'GEO_CODE'!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `merge` of `nsDF` and `valDF` is then done ‘on’ the `GEO_CODE` column found in each DataFrame. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "#merge the two data frames \n",
    "nsvalDF = pd.merge(nsDF, valDF, on = 'GEO_CODE')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each value in the `GEO_CODE` column in `nsDF` is matched with the same value in the `GEO_CODE` column in `valDF` and the rows those values are found in are combined. The figure below illustrates the process (combining `left` and `right` on `key`). Read more about merge [here](http://pandas.pydata.org/pandas-docs/stable/merging.html). \n",
    "\n",
    "![Illustration of the Pandas merge function](http://pandas.pydata.org/pandas-docs/stable/_images/merging_merge_on_key.png)\n",
    "\n",
    "Check you understand how something similar has been done for our LSOA data, combining `nsDF` and `valDF` on `GEO_CODE`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "#check output\n",
    "nsvalDF.head()\n",
    "nsvalDF.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's save these data for later - they may be useful for your final report!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "nsvalDF.to_csv(\"LondonLSOAData.csv\")\n",
    "nsvalDF.to_pickle(\"LondonLSOAData.pkl\") "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Join\n",
    "\n",
    "The `merge` functions uses a common column (Series) in two dataframes to combine them. If the _index_ of two dataframes is common we could also use `merge` to combine on the index. However, we would need to pass more arguments to the `merge` function, and another function called `join` has been designed specifically to combine on dataframe indexes. \n",
    "\n",
    "Here, we'll join some air quality time-series data to our weather time-series data using a common `DateTimeIndex`. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, so we can join it later, we'll load (and check) the weather data from our previous data manipulation (week 7):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "metDF = pd.read_pickle(\"CleanedHeathrowData2016.pkl\")\n",
    "print metDF.info()\n",
    "print metDF.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Air Quality Data\n",
    "\n",
    "The additional data we'll use with the Heathrow Weather data are air quality data have been downloaded from the Air Quality England [website](http://www.airqualityengland.co.uk/) (AQE 2016) for the [Hounslow Hatton Cross site](http://www.airqualityengland.co.uk/site/latest?site_id=HS7) (site HS7). This site was chosen as it is near Heathrow Airport. \n",
    "\n",
    "Air pollution is an important aspect of the ongoing argument about the construction of the third runway at Heathrow (e.g. GLA 2012). In particular, although Nitrogen Dioxide (NO2) concentrations around Heathrow, are lower than in the centre of London, they are still often above recommended levels (e.g. Heathrow 2012). By looking at relationships between weather and air quality we may begin to better understand the drivers of pollution."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However, as the air quaity data have also been automatically collected, we'll need to do some cleaning and manipulation of those data before we can join them with the weather data. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cleaning Air Quality Data\n",
    "\n",
    "When reading the data (in the next code block) we will read only the first 10 columns of the data to a DataFrame named `aqDF`, accounting for the need to skip lines. Also note there is a footer in the data so we use the `skipfooter` argument, but this also means we need to add the `engine` argument (read more about this in the `read_csv` [documentation](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html) online)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "aqDF = pd.read_csv('AirQuality_HattonCross_2016.csv', header=0, skiprows=3, usecols=range(0,10), skipfooter = 1, engine = 'python')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we'll rename the columns to something more intuitive:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "aqCN = ['Date', 'Time', 'PM10', 'PM10_su', 'NO', 'NO_su', 'NO2', 'NO2_su', 'NOx', 'NOx_su']\n",
    "aqDF.columns = aqCN"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's check the head and tail of the data to see what we've got:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "print aqDF.head()\n",
    "print aqDF.tail() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notcie that some columns (the ones we named `_su`) are labels not actual data. We can quickly remove those using the python built-in function `del` (this is an alternative to using a loop to retain only `float64` data types, plus `Date` and `Time`, for example). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "del aqDF['PM10_su'] \n",
    "del aqDF['NO_su']   \n",
    "del aqDF['NO2_su'] \n",
    "del aqDF['NOx_su'] \n",
    "\n",
    "print aqDF.tail() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setting a Common DateTimeIndex\n",
    "\n",
    "As the air quality data are a time series like the weather data, we'll create and use a matching `DateTimeIndex`. Later we'll be able to use this to join the air quality data to the weather data. \n",
    "\n",
    "However, before we can set `DateTimeIndex` we need to change how midnight is represented in the air quality data to match the MetOffice data; in the air quality data midnight is 24:00 but in the MetOffice data it is 00:00 (you can see by comparing the tails of `aqDF` and `metDF` we printed above). As discussed [here](http://stackoverflow.com/a/30536227), ideally midnight should be represented by 00:00 and should be the _first_ record in a given date (not the last).\n",
    "\n",
    "To change the 24:00 to 00:00 we can use pandas `replace` method on the Time `column` of `aqDF`: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "aqDF.Time.replace(to_replace = '24:00:00', value = '00:00:00', inplace = True) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can set start to the index like we have done before for the weather data (see week 7), but in a moment we'll note there is a problem with when midnight appears in the air quality data. So first we'll create a new temporary column named `DT` so we can keep track of different formats:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "aqDF[\"DT\"] = pd.to_datetime(aqDF.Date + aqDF.Time, format='%d/%m/%Y%H:%M:%S')  #create a new series containing a datetime object\n",
    "aqDF.index = aqDF[\"DT\"]                                                        #set the index\n",
    "\n",
    "aqDF.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The problem here is that by changing 24:00 to 00:00 in the air quality data, dates are off by one day for the 00:00 timestamp - each day should _start_ at midnight, but now in the air quality each day _finishes_ with midnight. We can see this by comparing the first value for an individual date in each of our dataframes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "print aqDF['2016-10-09'].index[0]\n",
    "print metDF['2016-10-09'].index[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Days in `metDF` start at `00:00:00` (as we want) but days in `aqDF` start at `01:00:00`!\n",
    "\n",
    "To resolve this we need to add one day to the values for 00:00:00 in `aqDF`. We can do this using the [functionality](https://docs.python.org/2/library/datetime.html#timedelta-objects) from the `datetime` library. We'll update the values in `DT` just so we can check what we're doing is right."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "import datetime as dt\n",
    "\n",
    "oneday = dt.timedelta(days=1)                           #create a timedelta object of days = 1\n",
    "aqDF.loc[aqDF['Time'] == '00:00:00','DT'] += oneday     #update the DT cell for rows with Time == 00:00 by adding oneday\n",
    "\n",
    "aqDF.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can see from the last line of the tail of `aqDF` that whereas the original `Date` column _ended_ a given date at `00:00:00`, the `DT` column now _starts_ a date with `00:00:00`. So now we can use `DT` to set the `DateTimeIndex` again, this time correctly matching the weather data index.  \n",
    "\n",
    "Furthermore, because we have set the `DateTimeIndex` to equal the `DT` column, the index has automatically updated to match!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We don't need the DT column anymore so let's drop that, along with any duplicate records that might exist:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "del aqDF['DT'] \n",
    "aqDF = aqDF.drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally we need to re-index `aqDF` so that all hours are represented (even those that are missing - see week 7):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "aqfirstDate = aqDF.index[0]\n",
    "aqlastDate = aqDF.index[len(aqDF.Date) - 1]\n",
    "aqDF = aqDF.reindex(index=pd.date_range(start = aqfirstDate, end = aqlastDate, freq = '1H'), fill_value = None)   #http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reindex.html\n",
    "\n",
    "print aqDF.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's quickly check that aqDF and metDF have consistent `DateTimeIndex`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "print \"Head of aqDF:\\n\", aqDF.head()\n",
    "print \"\\nHead of metDF:\\n\", metDF.head()\n",
    "print \"\\nTail of aqDF:\\n\", aqDF.tail() \n",
    "print \"\\nTail of metDF:\\n\", metDF.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looks good. Now we can move on to see how we can combine these various dataframes. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now we're ready to join!\n",
    "\n",
    "The [syntax](http://pandas.pydata.org/pandas-docs/stable/merging.html#database-style-dataframe-joining-merging) for the `join` function is quite straight-forward but can depend on the names of existing columns. For our current air quality and weather data frames we would `join` as follows: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "aqmetDF = aqDF.join(metDF, lsuffix='_l', rsuffix='_r')\n",
    "aqmetDF.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above creates a new dataframe (`aqmetDF`) from `aqDF` and `metDF`. We need to specify `lsuffix` and `rsuffix` as we have `Date` and `Time` columns in both of our dataframes - the suffixes are be added to the original columns in the new dataframe created so that we don't have duplicate columns names."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we didn't have duplicate column names wouldn't need suffixes, so let's keep only `float64` series for the weather data, dropping `WindGust` and `LocID` (as they are not as interesting as the other variables):  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "metDF = metDF.select_dtypes(include=['float64'])\n",
    "del metDF['WindGust'] \n",
    "del metDF['LocID']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And we'll drop `Date` and `Time` from `aqDF`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "del aqDF['Date'] \n",
    "del aqDF['Time']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have no duplicate column names in our dataframes (check using `.info()` if you like), our useage of `join` is much simpler:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "aqmetDF = aqDF.join(metDF)\n",
    "aqmetDF.info()\n",
    "aqmetDF.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's save this new combined dataframe to disk as it could also be useful for your final report. Before we do so, let's check if there's any values we want to drop from or change in the data using a pairplot to visualise:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "import seaborn as sb\n",
    "%matplotlib inline \n",
    "\n",
    "sb.pairplot(aqmetDF.dropna(axis = 0))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Oh yes, we need to drop those 0 _Pressure_ values that don't make sense:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "aqmetDF.loc[aqmetDF.Pressure == 0] = None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What's going on PM10? Are the very high values erroneous do you think? I'll leave that for you to investigate... Let's save the data to disk for safe-keeping and future use:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "aqmetDF.to_pickle(\"HeathrowAQWeather2016.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary\n",
    "\n",
    "So we have now created two dataframes of combined data and saved these to disk:\n",
    "- `LondonLSOAData.pkl`\n",
    "- `HeathrowAQWeather2016.pkl`\n",
    "\n",
    "You can load these data into a pandas dataframe easily using `read_pickle` and use them in your final reports."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercises\n",
    "\n",
    "Below are some exercises to help you think about the data you have created - feel free to work on all the exercises but you should be beginning to think about which data set you will focus on for your final report. We'll investigate in week 10 some more, but these exercises might get you started."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### NS-SeC and Values\n",
    "House Price data are often heavily skewed with a long tail (many smaller values and very, very few large values).  \n",
    "\n",
    "**Task 1.** Check the distribution of House Price data by creating plots using the seaborn distplot function. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When working with House Price data we often used a transformed version of the data that is the logarithm of the original data. One reason is that by reducing the skew of the data we overcome some of the problems of heteroscedasticity but also linear regression models are will better fit more normally distributed data. \n",
    "\n",
    "**Task 2.** Create a new column in the nsvalDF DataFrame that contains the natural logarithm of the House Price _[Hint: use the numpy `log` function]_ Once you have created your new series (column) check the new distribution using a plot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now you might compare the relationship between both the transformed and un-transformed house price Series and the NS-SeC Group Series to think about how each is related and which might be better for correlation and regression analyses. \n",
    "\n",
    "**Task 3.** Use a loop to create seaborn jointplots between tranformed and un-transformed house proce data with each of the SN-SeC Group populations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Weather and Air Quality\n",
    "\n",
    "It's always useful to thoroughly check your data to understand it sufficiently to interpret results properly. \n",
    "\n",
    "**Task 1.** Use a loop to plot time series for for all weather and air quality variables. When are the missing data? Are there any strange values?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Task 2.** Run some other exploratory data analyses: jointplots and correlations for different combinations of variables. Next week we'll look at regression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### References\n",
    "- AQE (2016) _Air Quality England_ [Online] Available at: http://www.airqualityengland.co.uk/ \n",
    "- GLA (2012) Air and noise pollution around a growing Heathrow Airport [Online] Available from: http://www.london.gov.uk/mayor-assembly/london-assembly/publications/tackling-air-and-noise-pollution-around-heathrow \n",
    "- Heathrow (2012) Heathrow Air Quality [Online] Available from: http://www.heathrow.com/file_source/Company/Static/PDF/Communityandenvironment/air-quality-strategy_LHR.pdf\n",
    "- Smith, D. (2010) _Valuing housing and green spaces: Understanding local amenities, the built environment and house prices in London._ London: Greater London Authority. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "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
}