notebooks/bias-in-crime-data-1-v0.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Detecting bias in crime data \n",
    "\n",
    "## part 1: Getting to know the data\n",
    "\n",
    "Margriet Groenendijk -  28 September 2020 \\\n",
    "**Data Science Lunch & Learn on [Crowdcast](https://www.crowdcast.io/e/data-science-lunch-and)**\n",
    "\n",
    "This is part 1 of a series about detecting bias in data. Various methods will be explored including different fairness metrics and comparing bias changes both over time and space. \n",
    "\n",
    "In this first notebook the data will be explored to understand what variables are available and which ones to use for a further analysis. There will be lots of data processing and data visualisations to get a better feel for what we can do in coming sessions to detect bias. \n",
    "\n",
    "\n",
    "* Run this Jupyter notebook in **Watson Studio**\n",
    "    * sign up for a free [IBM Cloud account](http://ibm.biz/crowdcast_289) \n",
    "    * Follow [these instructions](https://github.com/IBMDeveloperUK/data-science-lunch-and-learn/blob/master/watson-studio-instructions.md) \n",
    "* Or in any other environment\n",
    "\n",
    "\n",
    "## Contents\n",
    "\n",
    "0. [Installing and loading packages]()\n",
    "1. [Loading the data]() \\\n",
    "    1.1 [Using the API]() \\\n",
    "    1.2 [Downloading all data as csv files]()\n",
    "2. [Street level crimes]()    \n",
    "3. [Stop and searches]()\n",
    "4. [Outcomes]()\n",
    "5. [Join datasets]()\n",
    "6. [Maps]()\n",
    "7. [Summary]()\n",
    "8. [Open questions and ideas]()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 0. Installing and loading packages\n",
    "\n",
    "### Run the notebook in Watson Studio\n",
    "\n",
    "TODO: \n",
    "* add data to Cloud Object Storage\n",
    "* use SQL Query to access the data from notebook\n",
    "\n",
    "\n",
    "### Run the notebook locally\n",
    "\n",
    "Open a terminal and go to the folder with the notebook. Then create and activate a new environment:\n",
    "\n",
    "`conda env create --name crime-uk python=3.6` \\\n",
    "`conda activate crime-uk` \n",
    "\n",
    "Install the packages needed:\n",
    "\n",
    "`conda install jupyter requests dask pandas geopandas ` \\\n",
    "`conda install matplotlib seaborn ` \n",
    "\n",
    "Add the new environment to a kernel:\n",
    "\n",
    "`python -m ipykernel install --user --name crime-uk --display-name \"Python3.6 (crime-uk)\"` \n",
    "\n",
    "Save environment to yaml file for reproducibility:\n",
    "\n",
    "`conda env export --name crime-uk > crime-uk.yml`\n",
    "\n",
    "Start the notebook environment:\n",
    "\n",
    "`jupyter notebook`\n",
    "\n",
    "Uncomment the lines below for any packages that are still missing in your environment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "# !conda install --yes --prefix {sys.prefix} requests\n",
    "# !conda install --yes --prefix {sys.prefix} dask\n",
    "# !conda install --yes --prefix {sys.prefix} geopandas\n",
    "# !conda install --yes --prefix {sys.prefix} matplotlib\n",
    "# !conda install --yes --prefix {sys.prefix} seaborn\n",
    "# !conda install --yes --prefix {sys.prefix} -c conda-forge descartes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import glob\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import dask.dataframe as dd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Loading the data\n",
    "\n",
    "### 1.1 Using the API\n",
    "\n",
    "[A list of all available data sets](https://data.police.uk/docs/method/crimes-street-dates/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_list = requests.get('https://data.police.uk/api/crimes-street-dates')  \n",
    "print(data_list.status_code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_list_json = data_list.json() \n",
    "data_list_df = pd.json_normalize(data_list_json)\n",
    "data_list_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# months with data\n",
    "data_months = data_list_df['date'].unique()\n",
    "print(np.sort(data_months))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# force IDs\n",
    "force_IDs = data_list_df['stop-and-search'][0]\n",
    "print(force_IDs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# list of forces\n",
    "forces_list_df = pd.json_normalize(requests.get('https://data.police.uk/api/forces').json())\n",
    "forces_list_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2 Downloading all data as csv files\n",
    "\n",
    "The rest of the API does not seem to work, so downloaded all [latest data](https://data.police.uk/data/archive/latest.zip) from [here](https://data.police.uk/about/) (21GB!).\n",
    " \n",
    "`force_IDs = ['avon-and-somerset', 'bedfordshire', 'btp', 'cambridgeshire', 'cheshire', 'city-of-london', 'cleveland', 'cumbria', 'derbyshire', 'dorset', 'durham', 'dyfed-powys', 'essex', 'gloucestershire', 'gwent', 'hampshire', 'hertfordshire', 'humberside', 'kent', 'lancashire', 'leicestershire', 'merseyside', 'metropolitan', 'norfolk', 'northamptonshire', 'northumbria', 'north-wales', 'north-yorkshire', 'south-wales', 'south-yorkshire', 'staffordshire', 'suffolk', 'surrey', 'thames-valley', 'warwickshire', 'west-mercia', 'west-midlands', 'west-yorkshire']`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!wget -N https://data.police.uk/data/archive/latest.zip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO: when new file available (once a month): unzip and move to data/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a large dataset that is organized in folders and files by month and force. With [dask](https://docs.dask.org/en/latest/) you can explore all data without having to load it all into memory. \n",
    "\n",
    "There are three different data files. Load data for only one force into three Dask dataframes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#force = 'city-of-london'\n",
    "force = 'metropolitan'\n",
    "#force = 'hampshire'\n",
    "\n",
    "# get lists of all files for one force\n",
    "street_filenames = glob.glob(\"/Users/work/Data/UK-crime-data/*/*-\"+force+\"-street.csv\")\n",
    "outcomes_filenames = glob.glob(\"/Users/work/Data/UK-crime-data/*/*-\"+force+\"-outcomes.csv\")\n",
    "stop_and_search_filenames = glob.glob(\"/Users/work/Data/UK-crime-data/*/*-\"+force+\"-stop-and-search.csv\")\n",
    "\n",
    "# Dask dataframes are not loaded in memory and can load multiple files\n",
    "street_dd = dd.read_csv(\"/Users/work/Data/UK-crime-data/*/*-\"+force+\"-street.csv\",dtype={'Crime ID': 'object',\n",
    "       'Last outcome category': 'object'})\n",
    "outcomes_dd = dd.read_csv(\"/Users/work/Data/UK-crime-data/*/*-\"+force+\"-outcomes.csv\")\n",
    "stop_and_search_dd = dd.read_csv(\"/Users/work/Data/UK-crime-data/*/*-\"+force+\"-stop-and-search.csv\", \n",
    "                                 dtype={'Object of search': 'object'})\n",
    "\n",
    "print(\"Number of files:\")\n",
    "print(len(street_filenames))\n",
    "print(len(outcomes_filenames))\n",
    "print(len(stop_and_search_filenames))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Street level crimes\n",
    "\n",
    "[Data description](https://data.police.uk/docs/method/crime-street/)\n",
    "\n",
    "* [pandas DataFrame API](https://pandas.pydata.org/pandas-docs/stable/reference/frame.html)\n",
    "* [Reshaping and pivot tables](https://pandas.pydata.org/pandas-docs/stable/user_guide/reshaping.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load data into pandas dataframes\n",
    "street_df = street_dd.compute()\n",
    "print(len(street_df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop duplicates - check if these really are duplicates!\n",
    "street_df2 = street_df.drop_duplicates()\n",
    "print(len(street_df2))\n",
    "street_df.head().transpose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop columns with same value for all rows\n",
    "print(street_df['Reported by'].unique())\n",
    "print(street_df['Falls within'].unique())\n",
    "print(street_df['Context'].unique())\n",
    "\n",
    "street_df = street_df.drop(columns=['Reported by', 'Falls within', 'Context'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert Month to datetime\n",
    "street_df['Month'] = pd.to_datetime(street_df['Month'], format='%Y-%m')\n",
    "street_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(street_df['Crime type'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(street_df['Last outcome category'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# highest number of crimes by type\n",
    "bytype = street_df.groupby(['Last outcome category','Crime type']).count()\n",
    "bytype = bytype.drop(columns=['Month','LSOA code','Longitude','Latitude','LSOA name','Location'])\n",
    "bytype = bytype.rename(index=str, columns={\"Crime ID\": \"Number of crimes\"})\n",
    "bytype.sort_values(by=['Number of crimes'], ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# highest number of crimes by location\n",
    "bystreet = street_df.groupby(['Location','Crime type']).count()\n",
    "bystreet = bystreet.drop(columns=['Month', 'Last outcome category','LSOA code'])\n",
    "bystreet = bystreet.rename(index=str, columns={\"Crime ID\": \"Number of crimes\"})\n",
    "bystreet.sort_values(by=['Number of crimes'], ascending=False).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# highest number of crimes by area\n",
    "byarea = street_df.groupby(['LSOA name','Crime type']).count()\n",
    "byarea = byarea.drop(columns=['Month', 'Location','Longitude','Latitude','Last outcome category','LSOA code'])\n",
    "byarea = byarea.rename(index=str, columns={\"Crime ID\": \"Number of crimes\"})\n",
    "byarea.sort_values(by=['Number of crimes'], ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = plt.figure();\n",
    "street_df['Crime type'].groupby(street_df['Crime type']).count().plot.barh(figsize=(14,8));\n",
    "plt.ylabel(None);\n",
    "plt.title(force);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = plt.figure();\n",
    "street_df['Crime type'].groupby(street_df['Month']).count().plot(figsize=(14,6));\n",
    "plt.title(force);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# group by crime type\n",
    "street_type = street_df.groupby(['Month','Crime type'])['Location'].count().unstack(fill_value=0)\n",
    "street_type.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = plt.figure();\n",
    "street_type.plot(figsize=(14,6));\n",
    "plt.title(force);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = plt.figure();\n",
    "street_type.plot(figsize=(14,6));\n",
    "plt.ylabel('arrests / month', fontsize=16);\n",
    "plt.xlabel(None);\n",
    "plt.title(force);\n",
    "plt.legend(bbox_to_anchor=(1.02, 1.0));"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# group by outcome\n",
    "street_outcome = street_df.groupby(['Month','Last outcome category'])['Location'].count().unstack(fill_value=0)\n",
    "street_outcome.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = plt.figure();\n",
    "street_outcome.plot(figsize=(14,6));\n",
    "plt.ylabel('outcomes / month', fontsize=16);\n",
    "plt.xlabel(None);\n",
    "plt.title(force);\n",
    "plt.legend(bbox_to_anchor=(1.02, 1.0));"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = plt.figure();\n",
    "street_outcome.plot(figsize=(14,6),logy=True);\n",
    "plt.ylabel('outcomes / month', fontsize=16);\n",
    "plt.xlabel(None);\n",
    "plt.title(force);\n",
    "plt.legend(bbox_to_anchor=(1.02, 1.0));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> **Question: is data updated when there are updates later in time?** For instance when going from `Awaiting court outcome' to `Offender given...`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = plt.figure();\n",
    "ax=street_type.hist(figsize=(14,14));\n",
    "ax[0][0].set_xlim((0, 200))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3. Stop and searches"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stop_and_search_df = stop_and_search_dd.compute()\n",
    "print(len(stop_and_search_df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop duplicates - check if these really are duplicates!\n",
    "stop_and_search_df = stop_and_search_df.drop_duplicates()\n",
    "print(len(stop_and_search_df))\n",
    "stop_and_search_df.head().transpose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop columns with same value for all rows\n",
    "print(stop_and_search_df['Policing operation'].unique())\n",
    "print(stop_and_search_df['Part of a policing operation'].unique())\n",
    "\n",
    "stop_and_search_df = stop_and_search_df.drop(columns=['Policing operation', 'Part of a policing operation'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert Month to datetime\n",
    "stop_and_search_df['Date'] = pd.to_datetime(stop_and_search_df['Date'], format='%Y-%m-%dT%H:%M:%S')\n",
    "stop_and_search_df['Year'] = stop_and_search_df['Date'].dt.year\n",
    "stop_and_search_df['Month'] = pd.to_datetime(stop_and_search_df['Date']).dt.to_period('M')\n",
    "stop_and_search_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stop_and_search_grouped1 = stop_and_search_df.groupby(['Gender','Age range'])['Date'].count().unstack(fill_value=0)\n",
    "stop_and_search_grouped1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stop_and_search_grouped2 = stop_and_search_df.groupby(['Gender','Object of search'])['Date'].count().unstack(fill_value=0)\n",
    "stop_and_search_grouped2.head().transpose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stop_and_search_grouped3 = stop_and_search_df.groupby(['Gender','Self-defined ethnicity'])['Date'].count().unstack(fill_value=0)\n",
    "stop_and_search_grouped3.head().transpose()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Outcomes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "outcomes_df = outcomes_dd.compute()\n",
    "print(len(outcomes_df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop duplicates - check if these really are duplicates!\n",
    "#outcomes_df = outcomes_df.drop_duplicates()\n",
    "#print(len(outcomes_df))\n",
    "#outcomes_df.head().transpose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop columns with same value for all rows\n",
    "print(outcomes_df['Reported by'].unique())\n",
    "print(outcomes_df['Falls within'].unique())\n",
    "\n",
    "outcomes_df = outcomes_df.drop(columns=['Reported by', 'Falls within'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert Month to datetime\n",
    "outcomes_df['Month'] = pd.to_datetime(outcomes_df['Month'], format='%Y-%m')\n",
    "outcomes_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "outcomes_df['Outcome type'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Join datasets\n",
    "\n",
    "First explore if it is possible to combine the datasets.\n",
    "\n",
    "Are there duplicates in the data?\n",
    "\n",
    "[`duplicated`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.duplicated.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#duplicates = outcomes_df.drop_duplicates()\n",
    "#duplicates.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#print('all data: '+ str(len(outcomes_df)))\n",
    "#print('no duplicates: '+ str(len(duplicates)))\n",
    "#print('nr of unique IDs: '+ str(len(outcomes_df_duplicates['Crime ID'].unique())))\n",
    "#outcomes_df_duplicates.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#outcomes_df = outcomes_df.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1, 10, 100, 1000, 1005\n",
    "\n",
    "i = -2\n",
    "\n",
    "id = outcomes_df['Crime ID'].iloc[i]\n",
    "lat = outcomes_df['Latitude'].iloc[i]\n",
    "lon = outcomes_df['Longitude'].iloc[i]\n",
    "mon = outcomes_df['Month'].iloc[i]\n",
    "print(id)\n",
    "print(lat)\n",
    "print(lon)\n",
    "print(mon)\n",
    "\n",
    "outcomes_df[outcomes_df['Crime ID'] ==id]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "street_df[street_df['Crime ID'] ==id]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stop_and_search_df[(stop_and_search_df['Latitude']==lat) & (stop_and_search_df['Longitude']==lon)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stop_and_search_df[(stop_and_search_df['Latitude']==lat) & (stop_and_search_df['Longitude']==lon) \n",
    "                          & (stop_and_search_df['Month']==mon)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### join street_df and outcomes_df\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "crimes_df = pd.merge(street_df, outcomes_df, how='outer', sort=True,on='Crime ID')\n",
    "crimes_df.head().transpose()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Maps\n",
    "\n",
    "kml files for all regions from [here](https://data.police.uk/data/boundaries/).\n",
    "\n",
    "### Lond boroughs\n",
    "Explore with this geo-json file for London:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load data from a url\n",
    "london_boroughs = gpd.read_file(\"https://skgrange.github.io/www/data/london_boroughs.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "london_boroughs.plot();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "london_boroughs.plot(column='code');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "[fig, ax] = plt.subplots(1, figsize=(10, 6))\n",
    "london_boroughs.plot(ax=ax);\n",
    "ax.axis('off');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "london_boroughs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from shapely.geometry import Point, LineString, Polygon\n",
    "\n",
    "# convert to geopandas dataframe\n",
    "street_df['coordinates'] = list(zip(street_df.Longitude, street_df.Latitude))\n",
    "street_df['coordinates'] = street_df['coordinates'].apply(Point)\n",
    "street_gdf = gpd.GeoDataFrame(street_df, geometry='coordinates')\n",
    "street_gdf = street_gdf.drop(columns=['Latitude', 'Longitude'])\n",
    "street_gdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#street_gdf.plot();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "street_lsoa = street_df.groupby(['LSOA code','Crime type'])['Location'].count().unstack(fill_value=0).reset_index()\n",
    "street_lsoa.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "london_boroughs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(street_lsoa['LSOA code'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "london_boroughs['code'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "london_boroughs = pd.merge(london_boroughs, street_lsoa, how='outer', sort=True, left_on='code', right_on='LSOA code')\n",
    "london_boroughs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "london_boroughs.plot();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Spatial join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load data from a url\n",
    "london_boroughs = gpd.read_file(\"https://skgrange.github.io/www/data/london_boroughs.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "burglaries = street_gdf[street_gdf['Crime type']=='Burglary']\n",
    "burglaries.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "burglaries.crs = 'epsg:4326'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "points1 = gpd.sjoin(boroughs,burglaries) \n",
    "points1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#points2 = pd.pivot_table(points1,index='code',columns='Crime type',aggfunc={'Crime type':'count'})\n",
    "#points2.columns = points2.columns.droplevel()\n",
    "#points2 = points2.reset_index()\n",
    "points2 = points1.groupby(points1['code'])['Crime type'].count()\n",
    "points2\n",
    "#fig = plt.figure();\n",
    "#street_df['Crime type'].groupby(street_df['Month']).count().plot(figsize=(14,6));\n",
    "#plt.title(force);\n",
    "\n",
    "# group by outcome\n",
    "#street_outcome = street_df.groupby(['Month','Last outcome category'])['Location'].count().unstack(fill_value=0)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "london_boroughs = london_boroughs.merge(points2, on='code')\n",
    "#london_boroughs = london_boroughs.rename(columns={'Crime type': 'Burglary'}, inplace=True)\n",
    "london_boroughs\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Summary\n",
    "\n",
    "\n",
    "### Street level crimes\n",
    "\n",
    "* Crime ID, Month\n",
    "* Longitude, Latitude (these are an approximation of where the actual crimes occurred, not the exact locations)\n",
    "* Location, LSOA code, LSOA name\n",
    "* Crime type\n",
    "* Last outcome category\n",
    "\n",
    "\n",
    "### Outcome - case history\n",
    "\n",
    "* Crime ID, Month\n",
    "* Longitude, Latitude (these are an approximation of where the actual crimes occurred, not the exact locations)\n",
    "* Location, LSOA code, LSOA name\n",
    "* Outcome type\n",
    "\n",
    "\n",
    "## 8. Open questions and ideas \n",
    "\n",
    "### What is in each dataset? Can they be combined? And how, on which level, variables etc?\n",
    "\n",
    "* All three datasets contain lat-lon\n",
    "\n",
    "### What other data is needed?\n",
    "\n",
    "* Check [this list](https://data.police.uk/data/statistical-data/)\n",
    "\n",
    "\n",
    "### What does fairness mean?\n",
    "\n",
    "* Different definitions\n",
    "* Disentangle the judicial process\n",
    "\n",
    "### What can cause bias?\n",
    "\n",
    "Available data:\n",
    "* Age\n",
    "* Gender\n",
    "* Race\n",
    "* Location\n",
    "    * different regulations?\n",
    "    * number of schools, shops, pubs etc. - can get this easily from OSM data\n",
    "\n",
    "### Reconstruct process \n",
    "\n",
    "* Use dates + IDs in Street level crimes and Outcome \n",
    "* For example, how long does it take to go from arrested to charged?\n",
    "\n",
    "### Where is the crime rate highest?\n",
    "\n",
    "* What external data can this be related to? Census data?\n",
    "* What geospatial data is available to create maps? Probably LSOA shapefiles and lat-lon in \n",
    "\n",
    "### Where, when and why have crime rates changed most?\n",
    "\n",
    "\n",
    "### Are major events easily spotted?\n",
    "\n",
    "* Number of crimes much lower during start of lockdown\n",
    "* Changes in laws and regulations\n",
    "* Change in size of police force\n",
    "* Are there weekly and seasonal cycles?\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python3.6 (geopandas)",
   "language": "python",
   "name": "geopandas"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}