Working-with-structured-data-in-Python-using-Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Working with structured data in Python using Pandas\n",
    "\n",
    "\n",
    "## Table of Contents\n",
    "\n",
    "1. [Introduction](#introduction)<br>\n",
    "2. [Series and DataFrames](#series)<br>\n",
    "3. [Cleaning Data](#cleaning)<br>\n",
    "4. [Selecting Data](#selection)<br>\n",
    "5. [Merging Data](#merging)<br>\n",
    "6. [Grouping Data](#grouping)<br>\n",
    "7. [Visualising Data](#visualise)<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"introduction\"></a>\n",
    "## 1. Introduction\n",
    "\n",
    "A lot of data is **structured data**, which is data that is organized and formatted so it is easily readable, for example a table with variables as columns and records as rows, or key-value pairs in a noSQL database. As long as the data is formatted consistently and has multiple records with numbers, text and dates, you can probably read the data with [Pandas](https://pandas.pydata.org/pandas-docs/stable/index.html), an open-source Python package providing high-performance data manipulation and analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data\n",
    "\n",
    "The data that you will explore in this notebook is about the boroughs in London. Within Greater London there are [32 boroughs](https://en.wikipedia.org/wiki/London_boroughs). You can download the data from [data.gov.uk](https://data.gov.uk/dataset/248f5f04-23cf-4470-9216-0d0be9b877a8/london-borough-profiles-and-atlas) where this description is given:\n",
    "\n",
    "> The London Borough Profiles help paint a general picture of an area by presenting a range of headline indicator data to help show statistics covering demographic, economic, social and environmental datasets for each borough, alongside relevant comparator areas. \n",
    "\n",
    "**Let's start with loading the required Python packages and loading our data into the notebook.**\n",
    "\n",
    "* To run the code, select the below cell by clicking on it, and then click on the `Run` button at the top of the notebook (or use `Shift+Enter`), to run the cells in the notebook\n",
    "* The numbers in front of the cells tell you in which order you have run them, for instance `[1]`\n",
    "* When you see a `[*]` the cell is currently running and `[]` means you have not run the cell yet. Make sure run all of them!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Read data from a CSV file using the `read_csv` function. Load a file by running the next cell:**\n",
    "\n",
    "This file is read directly from a URL, but you can replace this with a local path when running this notebook on a local system. When you are using IBM Watson Studio you can also [upload](https://dataplatform.cloud.ibm.com/docs/content/wsj/manage-data/add-data-project.html?linkInPage=true) a file to your Cloud Object Storage, and then [import](https://dataplatform.cloud.ibm.com/docs/content/wsj/manage-data/add-data-project.html?linkInPage=true#os) it by clicking on the file in the menu on the right of the notebook.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('https://data.london.gov.uk/download/london-borough-profiles/c1693b82-68b1-44ee-beb2-3decf17dc1f8/london-borough-profiles.csv',encoding = 'unicode_escape', sep=',', thousands=',')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Only keep the data from the 32 boroughs by removng the last 5 rows from the DataFrame: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop([33,34,35,36,37])\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Let's take a first look at the data loaded into the notebook**\n",
    "\n",
    "* With `df.head()` or `df.tail()` you can view the first five or last five lines from the data  \n",
    "* Add a number between the brackets `()` to specify the number of lines you want to display., e.g. `df.head(2)`\n",
    "* Use `df.dtypes` to check the different variables and their datatype\n",
    "* `df.columns` gives a list of all column names\n",
    "* `len(df)` gives the number of rows\n",
    "* `df.shape` gives the number of rows and columns\n",
    "\n",
    "> **Tip**: to add more cells to run additional commands, activate a cell by clicking on it and then click on the '+' button at the top of the notebook. This will add a new cell. Click on the buttons with the upwards and downwards arrows to move the cells up and down to change their order\n",
    "\n",
    "<div class=\"alert alert-success\">\n",
    " <b>EXERCISE</b> <br/> \n",
    " Now let's have a look at the data that was loaded into the notebook. What are we actually looking at? \n",
    "    \n",
    "  Explore some of the following commands:\n",
    "  <ul>\n",
    "  <li><font face=\"Courier\">df.head()</font></li>\n",
    "  <li><font face=\"Courier\">df.tail()</font></li>\n",
    "  <li><font face=\"Courier\">df.columns</font></li>\n",
    "  <li><font face=\"Courier\">df.values</font></li>\n",
    "  <li><font face=\"Courier\">len(df)</font></li>\n",
    "  <li><font face=\"Courier\">list(df)</font></li>\n",
    "  </ul>\n",
    "</div>  \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# try the commands here (add as many cells as you need):\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"series\"></a>\n",
    "## 2. Series and DataFrames \n",
    "\n",
    "A `Series` is a one-dimensional labelled array that can contain of any type (integer, string, float, python objects, etc.)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = pd.Series([1, 3, 5, np.nan, 6, 8])\n",
    "s"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A `DataFrame` is a two-dimensional data structure, the data consists of rows and columns that you can create a in many ways, by loading a file or using a NumPy array and a date for the index.\n",
    "\n",
    "<div class=\"alert alert-info\" style=\"font-size:100%\">\n",
    "<a href=\"https://numpy.org\"> NumPy</a> is a Python library for working with multi-dimensional arrays and matrices with a large collection of mathematical functions to operate on these arrays.\n",
    "Have a look at this <a href=\"https://docs.scipy.org/doc/numpy-1.15.0/user/quickstart.html\"> NumPy tutorial</a> for an overview.\n",
    "</div>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create DataFrame `df1` with `dates` as the index, a 6 by 4 array of random `numbers` as values, and column names A, B, C and D (the index will be explained in the next section):  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dates = pd.date_range('20200101', periods=6)\n",
    "dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "numbers = np.random.randn(6, 4)\n",
    "numbers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame(numbers, index=dates, columns=['A', 'B', 'C', 'D'])\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or create a DataFrame by combining the above in one command:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.DataFrame({'A': 1.,\n",
    "                     'B': pd.Timestamp('20130102'),\n",
    "                     'C': pd.Series(1, index=list(range(4)), dtype='float32'),\n",
    "                     'D': np.array([3] * 4, dtype='int32'),\n",
    "                     'E': pd.Categorical([\"test\", \"train\", \"test\", \"train\"]),\n",
    "                     'F': 'foo'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use `type()` to check the data type of each variable. Below `print` is used to display the data type of all of them used so far:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('Data type of s is '+str(type(s)))\n",
    "print('Data type of s is '+str(type(dates)))\n",
    "print('Data type of s is '+str(type(numbers)))\n",
    "print('Data type of df is '+str(type(df1)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"cleaning\"></a>\n",
    "## 3. Cleaning Data\n",
    "\n",
    "When exploring data there are always transformations needed to get it in the format you need for your analysis, visualisations or models. Below are only a few examples of the endless possibilities. The best way to learn is to find a dataset and try to answer questions with the data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, let's make a copy of the Dataframe loaded from the URL:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs = df.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding an index\n",
    "\n",
    "Indexing and selecting data is key to data analysis and creating visualizations. For more information on indexing have a look at the [documentation](https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html).\n",
    "\n",
    "Set the area code (`Code`) as the index, which will change the table slightly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs = boroughs.set_index(['Code'])\n",
    "boroughs.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding and deleting columns\n",
    "\n",
    "Adding a column can be done by creating a new column `new`, which can be dropped using the `drop` function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs['new'] = 1\n",
    "boroughs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs = boroughs.drop(columns='new')\n",
    "boroughs.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As not all columns are needed, let's remove some. If you are interested in any of these, change the code and do not remove the columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs = boroughs.drop(columns=['GLA_Household_Estimate_2017',\n",
    "       'Proportion_of_population_aged_0-15,_2015',\n",
    "       'Proportion_of_population_of_working-age,_2015',\n",
    "       'Proportion_of_population_aged_65_and_over,_2015',\n",
    "       'Net_internal_migration_(2015)', 'Net_international_migration_(2015)',\n",
    "       'Net_natural_change_(2015)',\n",
    "       '%_of_largest_migrant_population_(2011)',\n",
    "       'Second_largest_migrant_population_by_country_of_birth_(2011)',\n",
    "       '%_of_second_largest_migrant_population_(2011)',\n",
    "       'Third_largest_migrant_population_by_country_of_birth_(2011)',\n",
    "       '%_of_third_largest_migrant_population_(2011)',\n",
    "       '%_of_population_from_BAME_groups_(2016)',\n",
    "       '%_people_aged_3+_whose_main_language_is_not_English_(2011_Census)',\n",
    "       'Overseas_nationals_entering_the_UK_(NINo),_(2015/16)',\n",
    "       'Largest_migrant_population_arrived_during_2015/16',\n",
    "       'Second_largest_migrant_population_arrived_during_2015/16',\n",
    "       'Third_largest_migrant_population_arrived_during_2015/16',\n",
    "       'Male_employment_rate_(2015)',\n",
    "       'Female_employment_rate_(2015)', 'Unemployment_rate_(2015)',\n",
    "       'Youth_Unemployment_(claimant)_rate_18-24_(Dec-15)',\n",
    "       'Proportion_of_16-18_year_olds_who_are_NEET_(%)_(2014)',\n",
    "       'Proportion_of_the_working-age_population_who_claim_out-of-work_benefits_(%)_(May-2016)',\n",
    "       '%_working-age_with_a_disability_(2015)',\n",
    "       'Proportion_of_working_age_people_with_no_qualifications_(%)_2015',\n",
    "       'Proportion_of_working_age_with_degree_or_equivalent_and_above_(%)_2015',\n",
    "       'Gross_Annual_Pay,_(2016)',\n",
    "       'Modelled_Household_median_income_estimates_2012/13',\n",
    "       '%_adults_that_volunteered_in_past_12_months_(2010/11_to_2012/13)',\n",
    "       'Number_of_jobs_by_workplace_(2014)',\n",
    "       '%_of_employment_that_is_in_public_sector_(2014)', 'Jobs_Density,_2015',\n",
    "       'Number_of_active_businesses,_2015',\n",
    "       'Two-year_business_survival_rates_(started_in_2013)',\n",
    "       'Crime_rates_per_thousand_population_2014/15',\n",
    "       'Fires_per_thousand_population_(2014)',\n",
    "       'Ambulance_incidents_per_hundred_population_(2014)',\n",
    "       'Average_Band_D_Council_Tax_charge_(£),_2015/16',\n",
    "       'New_Homes_(net)_2015/16_(provisional)',\n",
    "       'Homes_Owned_outright,_(2014)_%',\n",
    "       'Being_bought_with_mortgage_or_loan,_(2014)_%',\n",
    "       'Rented_from_Local_Authority_or_Housing_Association,_(2014)_%',\n",
    "       'Rented_from_Private_landlord,_(2014)_%',\n",
    "       'Total_carbon_emissions_(2014)',\n",
    "       'Household_Waste_Recycling_Rate,_2014/15',\n",
    "       'Number_of_cars,_(2011_Census)',\n",
    "       'Number_of_cars_per_household,_(2011_Census)',\n",
    "       '%_of_adults_who_cycle_at_least_once_per_month,_2014/15',\n",
    "       'Average_Public_Transport_Accessibility_score,_2014',\n",
    "       'Achievement_of_5_or_more_A*-_C_grades_at_GCSE_or_equivalent_including_English_and_Maths,_2013/14',\n",
    "       'Rates_of_Children_Looked_After_(2016)',\n",
    "       '%_of_pupils_whose_first_language_is_not_English_(2015)',\n",
    "       '%_children_living_in_out-of-work_households_(2015)',\n",
    "       'Male_life_expectancy,_(2012-14)', 'Female_life_expectancy,_(2012-14)',\n",
    "       'Teenage_conception_rate_(2014)',\n",
    "       'Life_satisfaction_score_2011-14_(out_of_10)',\n",
    "       'Worthwhileness_score_2011-14_(out_of_10)',\n",
    "       'Anxiety_score_2011-14_(out_of_10)',\n",
    "       'Childhood_Obesity_Prevalance_(%)_2015/16',\n",
    "       'People_aged_17+_with_diabetes_(%)',\n",
    "       'Mortality_rate_from_causes_considered_preventable_2012/14',\n",
    "       'Proportion_of_seats_won_by_Conservatives_in_2014_election',\n",
    "       'Proportion_of_seats_won_by_Labour_in_2014_election',\n",
    "       'Proportion_of_seats_won_by_Lib_Dems_in_2014_election'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"Renaming\"></a>\n",
    "\n",
    "You can change names of columns using `rename`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs.rename(columns={'Area_name':'Name',\n",
    "                'Inner/_Outer_London':'Inner/Outer',\n",
    "                'GLA_Population_Estimate_2017':'Population',\n",
    "                'Inland_Area_(Hectares)':'Area (ha)',\n",
    "                'Average_Age,_2017':'Average Age',\n",
    "                'Political_control_in_council':'Political control',\n",
    "                'Population_density_(per_hectare)_2017':'Population density (/ha)',\n",
    "                'New_migrant_(NINo)_rates,_(2015/16)':'New migrant rates',\n",
    "                'Happiness_score_2011-14_(out_of_10)':'Happiness score',\n",
    "                '%_of_resident_population_born_abroad_(2015)':'Population born abroad (%)',\n",
    "                'Employment_rate_(%)_(2015)':'Employment rate (%)',\n",
    "                'Turnout_at_2014_local_elections':'Turnout at local elections',\n",
    "                'Median_House_Price,_2015':'Median House Price',\n",
    "                \"Largest_migrant_population_by_country_of_birth_(2011)\":'Largest migrant population',\n",
    "                'Gross_Annual_Pay_-_Female_(2016)':'Gross Pay (Female)',\n",
    "                'Gross_Annual_Pay_-_Male_(2016)':'Gross Pay (Male)',\n",
    "                '%_of_area_that_is_Greenspace,_2005':'Greenspace (%)'},\n",
    "                 inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Further Data Cleaning\n",
    "\n",
    "**Things to check:**\n",
    "\n",
    "* Is the data tidy: each variable forms a column, each observation forms a row and  each type of observational unit forms a table.\n",
    "* Are all columns in the right data format?\n",
    "* Are there missing values?\n",
    "* Are there unrealistic outliers?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get a quick overview of the numeric data using the `.describe()` function. If any of the numeric columns are missing this is a probably because of a wrong data type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When looking at the `Turnout at local elections` columns you can see a `.`, this needs to be replaced to a missing value. Change them all with `replace`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs = boroughs.replace('.', float('NaN'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check if all datatypes are as you expect with `dtypes`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Expect for `Inner/Outer `, `Largest migration population` and `Political control` these all should be numeric (`float64` or `int64`). Change the data type to numeric with `to_numeric`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs['Population density (/ha)'] = pd.to_numeric(boroughs['Population density (/ha)'])\n",
    "boroughs['Population born abroad (%)'] = pd.to_numeric(boroughs['Population born abroad (%)'])\n",
    "boroughs['Gross Pay (Male)'] = pd.to_numeric(boroughs['Gross Pay (Male)'])\n",
    "boroughs['Gross Pay (Female)'] = pd.to_numeric(boroughs['Gross Pay (Female)'])\n",
    "boroughs['Median House Price'] = pd.to_numeric(boroughs['Median House Price'])\n",
    "boroughs['Greenspace (%)'] = pd.to_numeric(boroughs['Greenspace (%)'])\n",
    "boroughs['Turnout at local elections'] = pd.to_numeric(boroughs['Turnout at local elections'])\n",
    "\n",
    "boroughs['Area (ha)'] = boroughs['Area (ha)'].str.replace(',', '')\n",
    "boroughs['Area (ha)'] = pd.to_numeric(boroughs['Area (ha)'])\n",
    "\n",
    "boroughs.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"selection\"></a>\n",
    "## 4. Selecting Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "Access single or groups of rows and columns with labels using `.loc[]`. (This only works for the column that was set to the index):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs.loc['E09000001', 'Area (ha)']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs.loc['E09000001':'E09000004', ['Area (ha)', 'Average Age']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or select by position with `.iloc[]`. Select a single row, multiple rows (or columns) at particular positions in the index. This function is integer based (from 0 to length-1 of the axis):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs.iloc[:,1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs.iloc[:,0:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs.iloc[2:4,0:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All the above examples can be used to create a new DataFrame. Or create a new DataFrame from 2 columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs2 = boroughs[['Area (ha)', 'Average Age']]\n",
    "boroughs2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filtering\n",
    "\n",
    "Selecting rows based on a certain condition can be done with Boolean indexing. This uses the actual values of the data in the DataFrame as opposed to the row/column labels or index positions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs['Average Age'] > 39"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When you want to select the rows and see all the data add `boroughs[]` around your function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "boroughs[boroughs['Average Age'] > 39]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can combine different columns using `&`, `|` and `==` operators:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "boroughs[(boroughs['Average Age'] > 39) & (boroughs['Political control'] == 'Cons')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "boroughs[(boroughs['Political control'] == 'Lab') | (boroughs['Political control'] == 'Lib Dem')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-success\">\n",
    " <b>EXERCISE</b> <br/> \n",
    " With the above commands you can now start exploring the data some more. Answer the following questions by writing a little code (add as many cells as you need):\n",
    "  <ol>\n",
    "  <li>Which borough has the largest population density per hectare? </li>  \n",
    "  <li>What are the maximum and minimum number of new migrants? And for which boroughs?</li>   \n",
    "  <li> Which borough is happiest? </li>\n",
    "  \n",
    " </ol>  \n",
    "</div>  \n",
    "\n",
    "\n",
    "> *Tips*: \n",
    "- Find the maximum of a row with for instance `boroughs['Population'].max()` \n",
    "- Extract the value from a cell in a DataFrame with `.value[]`\n",
    "- Print a value with `print()` for instance: `print(boroughs['area'][0])` for the first row. If you calculate multiple values in one cell you will need this, else the answers will not be displayed.\n",
    "- To extract an entire row use `idxmax()` which returns column with maximum value, and `.loc[]` to return row of the index\n",
    "- To see the answer uncomment the line in the cell that contains `%load` (by deleting the `#`) and then run the cell, but try to find your own solution first in the cell above the solution!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Which borough has the largest population density per hectare?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your answer:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %load https://raw.githubusercontent.com/IBMDeveloperUK/python-pandas-workshop/master/answers/answer1.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What are the maximum and minimum number of new migrants? And for which boroughs?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your answer:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %load https://raw.githubusercontent.com/IBMDeveloperUK/python-pandas-workshop/master/answers/answer2.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Which borough is happiest?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your answer:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %load https://raw.githubusercontent.com/IBMDeveloperUK/python-pandas-workshop/master/answers/answer3.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"merging\"></a>\n",
    "## 5. Merging Data\n",
    "\n",
    "Pandas has several different options to combine or merge data. The [documentation](https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html) has lots of examples. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's create two new Dataframes to explore this: `cities` and `cities2`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {'city':       ['London','Manchester','Birmingham','Leeds','Glasgow'],\n",
    "        'population': [9787426,  2553379,     2440986,    1777934,1209143],\n",
    "        'area':       [1737.9,   630.3,       598.9,      487.8,  368.5 ]}\n",
    "cities = pd.DataFrame(data)\n",
    "\n",
    "data2 = {'city':       ['Liverpool','Southampton'],\n",
    "        'population': [864122,  855569],\n",
    "        'area':       [199.6,   192.0]}\n",
    "cities2 = pd.DataFrame(data2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use `append()` to combine these Dataframes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cities = cities.append(cities2)\n",
    "cities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {'city': ['London','Manchester','Birmingham','Leeds','Glasgow'],\n",
    "        'density': [5630,4051,4076,3645,3390]}\n",
    "cities3 = pd.DataFrame(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cities3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "An extra column can be added with `.merge()` with an outer join using the city names:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cities = pd.merge(cities, cities3, how='outer', sort=True,on='city')\n",
    "cities"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"grouping\"></a>\n",
    "## 6. Grouping Data\n",
    "\n",
    "Grouping data is a quick way to calculate values for classes in your DataFrame. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs.groupby(['Inner/Outer']).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When you have multiple categorial variables you can create a nested index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs.groupby(['Inner/Outer','Political control']).sum().head(8)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"visualise\"></a>\n",
    "## 7. Visualising Data\n",
    "\n",
    "Pandas uses [`Matplotlib`](https://matplotlib.org/users/index.html) as the default for visualisations. \n",
    "\n",
    "Import the package and also add the magic line starting with `%` to output the charts within the notebook:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs = boroughs.reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The default plot is a line chart that uses the index for the x-axis:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs['Employment rate (%)'].plot();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To create a plot that makes more sense for this data have a look at the [documentation](https://pandas.pydata.org/pandas-docs/stable/user_guide/visualization.html) for all options. \n",
    "\n",
    "For the above example, a histogram might work better. You can change the number of `bins` to get the desired output:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs['Employment rate (%)'].plot.hist(bins=10);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Change the size of the histogram with the `figsize` option:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs['Employment rate (%)'].plot.hist(bins=15,figsize=(10,5));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Within the plot command you can select the data directly. The below histogram shows the Employment rate for Outer London only:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs['Employment rate (%)'][boroughs['Inner/Outer']=='Outer London'].plot.hist(bins=15,figsize=(10,5));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To add the Employment rate for Inner London, repeat the plot command with a different selection of the data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs['Employment rate (%)'][boroughs['Inner/Outer']=='Outer London'].plot.hist(bins=15,figsize=(10,5));\n",
    "boroughs['Employment rate (%)'][boroughs['Inner/Outer']=='Inner London'].plot.hist(bins=15,figsize=(10,5));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above plot is difficult to read as the histograms have overlapped. You can fix this by changing the colours and making them transparant. \n",
    "    \n",
    "To add a legend each histogram needs to be assigned to an object `ax`. With `legend()` you can then add a legend. With `plt.xlabel()` you can also add a label for the x-axis (this works similar for the y-axis):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ax = boroughs['Employment rate (%)'][boroughs['Inner/Outer']=='Outer London'].plot.hist(\n",
    "    bins=15,figsize=(10,5),alpha=0.5,color='#1A4D3B');\n",
    "ax = boroughs['Employment rate (%)'][boroughs['Inner/Outer']=='Inner London'].plot.hist(\n",
    "    bins=15,figsize=(10,5),alpha=0.5,color='#4D1A39');\n",
    "ax.legend(['Outer London','Inner London'])\n",
    "plt.xlabel('Employment rate (%)');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are various options available to change every aspect of your chart. Below are some examples to get you started.\n",
    "        \n",
    "**Go ahead and create new charts and customise the options.** \n",
    "\n",
    "Especially the next one can be improved on to make it look better:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs['Population density (/ha)'].plot.hist(\n",
    "    bins=15, \n",
    "    title=\"Population Density (/ha)\",\n",
    "    legend=False,\n",
    "    fontsize=14,\n",
    "    grid=False,\n",
    "    linestyle='--',\n",
    "    edgecolor='black',\n",
    "    color='darkred',\n",
    "    linewidth=3);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Seaborn\n",
    "\n",
    "Seaborn is a Python data visualization library based on matplotlib. It is an easy to use visualisation package that works well with Pandas DataFrames. \n",
    "\n",
    "Below are a few examples using Seaborn. \n",
    "\n",
    "Refer to this [documentation](https://seaborn.pydata.org/index.html) for information on lots of plots you can create."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look at a distribution plot using `distplot`, which shows a distribution of the data. \n",
    "\n",
    "Use the `dropna()` function to remove rows and columns with Null/NaN values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.distplot(boroughs['Population density (/ha)'].dropna());"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<You can create categorical plots with `catplot`. There are categorical scatter plots, distribution plots and estimate plots. The `kind` parameter selects the function to use, for instance box, violin, swarm ,bar, stripplot and boxen.\n",
    "    \n",
    "The default representation in catplot() uses a scatter plot:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.catplot(x='Turnout at local elections', y='Political control', data=boroughs);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Also try `kind=\"swarm\"`, `kind=\"box\"` or `kind=\"violin\"`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.catplot(x='Median House Price', y='Name', kind='swarm', data=boroughs);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.catplot(x='Employment rate (%)', y='Largest migrant population', kind=\"box\", data=boroughs);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.catplot(x='Turnout at local elections', y='Political control', kind=\"violin\", data=boroughs);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-success\">\n",
    " <b>EXERCISE</b>\n",
    " <ol>\n",
    "  <li>Create two histograms that compare the Gross Annual pay for Male and Female Employees using `.plot.hist()`</li>\n",
    "  <li>Create a bar plot comparing the median house prices for different boroughs</li>\n",
    "  <li>Create a scatter plot comparing the Median House price and percentage of area that is greenspace </li>\n",
    " </ol> \n",
    "   </div> \n",
    "   \n",
    " <ul></ul> \n",
    " <ul></ul> \n",
    " <ul></ul> \n",
    " \n",
    " > *Tips*:\n",
    "-  To add two histograms to one plot you can repeat `.plot()` in the same cell \n",
    "-  Add a legend by assiging each histogram to an object `ax`, which is used to create a legend\n",
    "-  To customise the size of your maps, use the example of `[fig, ax]`, which customises the figsize for each map in other examples above \n",
    "\n",
    "**Create two histograms that compare the Gross Annual pay for Male and Female Employees using `.plot.hist()`**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your answer:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %load https://raw.githubusercontent.com/IBMDeveloperUK/python-pandas-workshop/master/answers/answer4.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Create a bar plot comparing the median house prices for different boroughs**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your answer:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %load https://raw.githubusercontent.com/IBMDeveloperUK/python-pandas-workshop/master/answers/answer5.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Create a scatter plot comparing the Median House price and percentage of greenspace area** "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your answer:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %load https://raw.githubusercontent.com/IBMDeveloperUK/python-pandas-workshop/master/answers/answer6.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that you have explored some real data with Python and Pandas keep learning by exploring more of this dataset or create a new notebook and start with your own data. The data used here is a clean dataset, which is definitely not always the case, so stay alert to always check all your data. \n",
    "\n",
    "<div class=\"alert alert-info\" style=\"font-size:100%\">\n",
    "<b>To learn more about Pandas start with this <a href=\"http://pandas.pydata.org/pandas-docs/stable/getting_started/10min.html\">10 minute introduction</a><br>\n",
    "</div>\n",
    "\n",
    "## Optional Excercises and further learning\n",
    "\n",
    "If you finish early:\n",
    "\n",
    "2. Try to create other plots. Have a look at the [Pandas plot examples](https://pandas.pydata.org/pandas-docs/stable/user_guide/visualization.html) or the [Seaborn gallery](https://seaborn.pydata.org/examples/index.html) for inspiration.  \n",
    "3. Or load one of your own datasets into a new notebook and play around with the data to practice what you have learned. You can use the free account you created today for your own projects as well! \n",
    "4. Have a look at these Pandas workshops and book: <br>\n",
    "4.1. [Pandas workshop by Alexander Hensdorf](https://github.com/alanderex/pydata-pandas-workshop) <br>\n",
    "4.2. [Pandas tutorial by Joris van den Bossche](https://github.com/jorisvandenbossche/pandas-tutorial) <br>\n",
    "4.3. [Python Data Science Handbook](https://jakevdp.github.io/PythonDataScienceHandbook/) <br>\n",
    "\n",
    "### Authors\n",
    "    \n",
    "Yamini Rao is a Developer Advocate for IBM. She compiles developer scenarios, workshops and training material based on IBM Cloud technologies. She works with various developer communities across the UK, collaborating with them to present/organise workshops and meetups. She has a background in computer science and has worked extensively as an Implementation Engineer for various IBM Analytical tools. \n",
    "\n",
    "Margriet Groenendijk is a Data & AI Developer Advocate for IBM. She develops and presents talks and workshops about data science and AI. She is active in the local developer communities through attending, presenting and organising meetups and conferences. She has a background in climate science where she explored large observational datasets of carbon uptake by forests during her PhD, and global scale weather and climate models as a postdoctoral fellow. \n",
    "\n",
    "\n",
    "Copyright © 2020 IBM. This notebook and its source code are released under the terms of the MIT License."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7",
   "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.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}