notebooks/search-force.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "042d54f3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/home/gerry/uk_crime_data/notebooks\r\n"
     ]
    }
   ],
   "source": [
    "!pwd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "04341591",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyspark\n",
    "from pyspark.sql import SparkSession\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "bae71e44",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING: An illegal reflective access operation has occurred\n",
      "WARNING: Illegal reflective access by org.apache.spark.unsafe.Platform (file:/home/gerry/bin/spark-3.0.3-bin-hadoop3.2/jars/spark-unsafe_2.12-3.0.3.jar) to constructor java.nio.DirectByteBuffer(long,int)\n",
      "WARNING: Please consider reporting this to the maintainers of org.apache.spark.unsafe.Platform\n",
      "WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations\n",
      "WARNING: All illegal access operations will be denied in a future release\n",
      "22/03/25 01:12:37 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable\n",
      "Using Spark's default log4j profile: org/apache/spark/log4j-defaults.properties\n",
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n"
     ]
    }
   ],
   "source": [
    "FILE = 'metropolitan_2020-12.csv'\n",
    "\n",
    "spark = SparkSession.builder \\\n",
    "    .master(\"local[*]\") \\\n",
    "    .appName('test') \\\n",
    "    .getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "bcfe2170",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df = spark.read \\\n",
    "    .option(\"header\", \"true\") \\\n",
    "    .csv(FILE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b32bb4aa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+--------------------+---------------+----------------------+------+--------------------+----------------------------------+--------------------+-----------------------------------+--------------------+--------------------+--------------------+-------------------------+--------------------+--------------------+--------------------+----------------+---------+---------+--------------------+\n",
      "|age_range|             outcome|involved_person|self_defined_ethnicity|gender|         legislation|outcome_linked_to_object_of_search|            datetime|removal_of_more_than_outer_clothing|      outcome_object|            location|           operation|officer_defined_ethnicity|                type|      operation_name|    object_of_search|        latitude|longitude|street_id|         street_name|\n",
      "+---------+--------------------+---------------+----------------------+------+--------------------+----------------------------------+--------------------+-----------------------------------+--------------------+--------------------+--------------------+-------------------------+--------------------+--------------------+--------------------+----------------+---------+---------+--------------------+\n",
      "|    18-24|A no further acti...|           True|  Asian/Asian Briti...|  Male|Misuse of Drugs A...|                              null|2020-12-01T19:50:...|                               null|{'id': 'bu-no-fur...|                null|               False|                    Asian|       Person search|                null|    Controlled drugs|            null|     null|     null|                null|\n",
      "|     null|A no further acti...|           True|  Other ethnic grou...|  Male|Misuse of Drugs A...|                              null|2020-12-04T03:08:...|                               null|{'id': 'bu-no-fur...|{'latitude': '51....|               False|                    Black|       Person search|                null|    Controlled drugs|       51.514168|-0.151458|   956836|On or near Oxford...|\n",
      "|     null|A no further acti...|           True|  Black/African/Car...|  Male|Misuse of Drugs A...|                              null|2020-12-04T03:20:...|                               null|{'id': 'bu-no-fur...|\"{'latitude': '51...| 'longitude': '-0...|                    False|               Black|Person and Vehicl...|                null|Controlled drugs|51.512938|-0.150441|              956855|\n",
      "|  over 34|Community resolution|           True|  Other ethnic grou...|  Male|Police and Crimin...|                              null|2020-12-04T01:00:...|                               null|{'id': 'bu-commun...|{'latitude': '51....|               False|                    Other|       Person search|                null|Evidence of offen...|       51.516524|-0.172117|   957057|On or near Norfol...|\n",
      "|    25-34|A no further acti...|           True|  Black/African/Car...|  Male|Misuse of Drugs A...|                              null|2020-12-03T22:00:...|                               null|{'id': 'bu-no-fur...|{'latitude': '51....|               False|                    Black|Person and Vehicl...|                null|    Controlled drugs|       51.519227|-0.167858|   960466|On or near Cabbel...|\n",
      "|    18-24|A no further acti...|           True|  Asian/Asian Briti...|  Male|Misuse of Drugs A...|                              null|2020-12-04T03:07:...|                               null|{'id': 'bu-no-fur...|\"{'latitude': '51...| 'longitude': '-0...|                    False|               Other|Person and Vehicl...|                null|Controlled drugs|51.512938|-0.150441|              956855|\n",
      "|    10-17|A no further acti...|           True|  White - English/W...|  Male|Misuse of Drugs A...|                              null|2020-12-03T21:20:...|                               null|{'id': 'bu-no-fur...|{'latitude': '51....|               False|                    White|       Person search|                null|    Controlled drugs|       51.514915|-0.169241|   957091|On or near Southw...|\n",
      "|  over 34|Community resolution|           True|  White - English/W...|  Male|Misuse of Drugs A...|                              null|2020-12-03T21:55:...|                               null|{'id': 'bu-commun...|{'latitude': '51....|               False|                    White|       Person search|                null|    Controlled drugs|       51.514246|-0.131436|   956576|On or near Nightclub|\n",
      "|     null|Summons / charged...|           True|  Black/African/Car...|  Male|Misuse of Drugs A...|                              null|2020-12-03T22:45:...|                               null|{'id': 'bu-summon...|{'latitude': '51....|               False|                    Black|Person and Vehicl...|                null|    Controlled drugs|       51.532825|-0.202809|   960065|On or near Kilbur...|\n",
      "|    25-34|A no further acti...|           True|  Asian/Asian Briti...|  Male|Police and Crimin...|                              null|2020-12-04T15:46:...|                               null|{'id': 'bu-no-fur...|                null|               False|                    Asian|Person and Vehicl...|                null|   Offensive weapons|            null|     null|     null|                null|\n",
      "|  over 34|A no further acti...|           True|  Other ethnic grou...|  Male|Misuse of Drugs A...|                              null|2020-12-04T13:15:...|                               null|{'id': 'bu-no-fur...|{'latitude': '51....|               False|                    Other|Person and Vehicl...|                null|    Controlled drugs|       51.520067|-0.170404|   960332|On or near Pedest...|\n",
      "|  over 34|A no further acti...|           True|  Other ethnic grou...|  Male|Misuse of Drugs A...|                              null|2020-12-04T12:13:...|                               null|{'id': 'bu-no-fur...|{'latitude': '51....|               False|                    Other|       Person search|                null|    Controlled drugs|       51.519670|-0.167408|   960465|On or near Chapel...|\n",
      "|     null|A no further acti...|          False|                  null|  null|Misuse of Drugs A...|                              null|2020-12-03T22:45:...|                               null|{'id': 'bu-no-fur...|{'latitude': '51....|               False|                     null|      Vehicle search|                null|    Controlled drugs|       51.532825|-0.202809|   960065|On or near Kilbur...|\n",
      "|    10-17|A no further acti...|           True|  Asian/Asian Briti...|  Male|Police and Crimin...|                              null|2020-12-04T15:47:...|                               null|{'id': 'bu-no-fur...|{'latitude': '51....|               False|                    Asian|       Person search|                null|   Offensive weapons|       51.532209|-0.174560|   960195|On or near Circus...|\n",
      "|  over 34|A no further acti...|           True|  Black/African/Car...|  Male|Misuse of Drugs A...|                              null|2020-12-04T15:32:...|                               null|{'id': 'bu-no-fur...|{'latitude': '51....|               False|                    Black|       Person search|                null|    Controlled drugs|       51.493904|-0.142655|   953396|On or near Confer...|\n",
      "|    18-24|A no further acti...|           True|  Other ethnic grou...|  Male|Police and Crimin...|                              null|2020-12-04T16:00:...|                               null|{'id': 'bu-no-fur...|{'latitude': '51....|               False|                    Other|       Person search|                null|        Stolen goods|       51.515915|-0.131209|   956514|On or near Falcon...|\n",
      "|  over 34|A no further acti...|           True|  Mixed/Multiple et...|  Male|Misuse of Drugs A...|                              null|2020-12-04T16:25:...|                               null|{'id': 'bu-no-fur...|{'latitude': '51....|               False|                    White|       Person search|                null|    Controlled drugs|       51.516769|-0.130583|   956610|On or near Totten...|\n",
      "|    18-24|A no further acti...|           True|  Other ethnic grou...|  Male|Police and Crimin...|                              null|2020-12-04T16:35:...|                               null|{'id': 'bu-no-fur...|{'latitude': '51....|               False|                    Other|       Person search|                null|        Stolen goods|       51.515915|-0.131209|   956514|On or near Falcon...|\n",
      "|    10-17|A no further acti...|           True|  Other ethnic grou...|  Male|Police and Crimin...|                              null|2020-12-04T16:35:...|                               null|{'id': 'bu-no-fur...|{'latitude': '51....|               False|                    Other|       Person search|                null|Evidence of offen...|       51.515915|-0.131209|   956514|On or near Falcon...|\n",
      "|    10-17|A no further acti...|           True|  White - English/W...|  Male|Misuse of Drugs A...|                              null|2020-12-03T21:20:...|                               null|{'id': 'bu-no-fur...|{'latitude': '51....|               False|                    White|       Person search|                null|    Controlled drugs|       51.501986|-0.159659|   953166|On or near Knight...|\n",
      "+---------+--------------------+---------------+----------------------+------+--------------------+----------------------------------+--------------------+-----------------------------------+--------------------+--------------------+--------------------+-------------------------+--------------------+--------------------+--------------------+----------------+---------+---------+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "02a7dec3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- age_range: string (nullable = true)\n",
      " |-- outcome: string (nullable = true)\n",
      " |-- involved_person: string (nullable = true)\n",
      " |-- self_defined_ethnicity: string (nullable = true)\n",
      " |-- gender: string (nullable = true)\n",
      " |-- legislation: string (nullable = true)\n",
      " |-- outcome_linked_to_object_of_search: string (nullable = true)\n",
      " |-- datetime: string (nullable = true)\n",
      " |-- removal_of_more_than_outer_clothing: string (nullable = true)\n",
      " |-- outcome_object: string (nullable = true)\n",
      " |-- location: string (nullable = true)\n",
      " |-- operation: string (nullable = true)\n",
      " |-- officer_defined_ethnicity: string (nullable = true)\n",
      " |-- type: string (nullable = true)\n",
      " |-- operation_name: string (nullable = true)\n",
      " |-- object_of_search: string (nullable = true)\n",
      " |-- latitude: string (nullable = true)\n",
      " |-- longitude: string (nullable = true)\n",
      " |-- street_id: string (nullable = true)\n",
      " |-- street_name: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "43c593b3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "StructType(List(StructField(age_range,StringType,true),StructField(outcome,StringType,true),StructField(involved_person,StringType,true),StructField(self_defined_ethnicity,StringType,true),StructField(gender,StringType,true),StructField(legislation,StringType,true),StructField(outcome_linked_to_object_of_search,StringType,true),StructField(datetime,StringType,true),StructField(removal_of_more_than_outer_clothing,StringType,true),StructField(outcome_object,StringType,true),StructField(location,StringType,true),StructField(operation,StringType,true),StructField(officer_defined_ethnicity,StringType,true),StructField(type,StringType,true),StructField(operation_name,StringType,true),StructField(object_of_search,StringType,true),StructField(latitude,StringType,true),StructField(longitude,StringType,true),StructField(street_id,StringType,true),StructField(street_name,StringType,true)))"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "00c55b24",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "9dd9f556",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sf_pd = pd.read_csv(FILE, nrows=1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a149ef59",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>age_range</th>\n",
       "      <th>outcome</th>\n",
       "      <th>involved_person</th>\n",
       "      <th>self_defined_ethnicity</th>\n",
       "      <th>gender</th>\n",
       "      <th>legislation</th>\n",
       "      <th>outcome_linked_to_object_of_search</th>\n",
       "      <th>datetime</th>\n",
       "      <th>removal_of_more_than_outer_clothing</th>\n",
       "      <th>outcome_object</th>\n",
       "      <th>location</th>\n",
       "      <th>operation</th>\n",
       "      <th>officer_defined_ethnicity</th>\n",
       "      <th>type</th>\n",
       "      <th>operation_name</th>\n",
       "      <th>object_of_search</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>street_id</th>\n",
       "      <th>street_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18-24</td>\n",
       "      <td>A no further action disposal</td>\n",
       "      <td>True</td>\n",
       "      <td>Asian/Asian British - Pakistani</td>\n",
       "      <td>Male</td>\n",
       "      <td>Misuse of Drugs Act 1971 (section 23)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-12-01T19:50:00+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'id': 'bu-no-further-action', 'name': 'A no f...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>Asian</td>\n",
       "      <td>Person search</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Controlled drugs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>A no further action disposal</td>\n",
       "      <td>True</td>\n",
       "      <td>Other ethnic group - Not stated</td>\n",
       "      <td>Male</td>\n",
       "      <td>Misuse of Drugs Act 1971 (section 23)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-12-04T03:08:00+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'id': 'bu-no-further-action', 'name': 'A no f...</td>\n",
       "      <td>{'latitude': '51.514168', 'street': {'id': 956...</td>\n",
       "      <td>False</td>\n",
       "      <td>Black</td>\n",
       "      <td>Person search</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Controlled drugs</td>\n",
       "      <td>51.514168</td>\n",
       "      <td>-0.151458</td>\n",
       "      <td>956836.0</td>\n",
       "      <td>On or near Oxford Street</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>A no further action disposal</td>\n",
       "      <td>True</td>\n",
       "      <td>Black/African/Caribbean/Black British - Any ot...</td>\n",
       "      <td>Male</td>\n",
       "      <td>Misuse of Drugs Act 1971 (section 23)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-12-04T03:20:00+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'id': 'bu-no-further-action', 'name': 'A no f...</td>\n",
       "      <td>{'latitude': '51.512938', 'street': {'id': 956...</td>\n",
       "      <td>False</td>\n",
       "      <td>Black</td>\n",
       "      <td>Person and Vehicle search</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Controlled drugs</td>\n",
       "      <td>51.512938</td>\n",
       "      <td>-0.150441</td>\n",
       "      <td>956855.0</td>\n",
       "      <td>On or near Duke'S Yard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>over 34</td>\n",
       "      <td>Community resolution</td>\n",
       "      <td>True</td>\n",
       "      <td>Other ethnic group - Not stated</td>\n",
       "      <td>Male</td>\n",
       "      <td>Police and Criminal Evidence Act 1984 (section 1)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-12-04T01:00:00+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'id': 'bu-community-resolution', 'name': 'Com...</td>\n",
       "      <td>{'latitude': '51.516524', 'street': {'id': 957...</td>\n",
       "      <td>False</td>\n",
       "      <td>Other</td>\n",
       "      <td>Person search</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Evidence of offences under the Act</td>\n",
       "      <td>51.516524</td>\n",
       "      <td>-0.172117</td>\n",
       "      <td>957057.0</td>\n",
       "      <td>On or near Norfolk Place</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>25-34</td>\n",
       "      <td>A no further action disposal</td>\n",
       "      <td>True</td>\n",
       "      <td>Black/African/Caribbean/Black British - Any ot...</td>\n",
       "      <td>Male</td>\n",
       "      <td>Misuse of Drugs Act 1971 (section 23)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-12-03T22:00:00+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'id': 'bu-no-further-action', 'name': 'A no f...</td>\n",
       "      <td>{'latitude': '51.519227', 'street': {'id': 960...</td>\n",
       "      <td>False</td>\n",
       "      <td>Black</td>\n",
       "      <td>Person and Vehicle search</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Controlled drugs</td>\n",
       "      <td>51.519227</td>\n",
       "      <td>-0.167858</td>\n",
       "      <td>960466.0</td>\n",
       "      <td>On or near Cabbell Street</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>25-34</td>\n",
       "      <td>A no further action disposal</td>\n",
       "      <td>True</td>\n",
       "      <td>Asian/Asian British - Any other Asian background</td>\n",
       "      <td>Male</td>\n",
       "      <td>Misuse of Drugs Act 1971 (section 23)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-12-04T00:15:00+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'id': 'bu-no-further-action', 'name': 'A no f...</td>\n",
       "      <td>{'latitude': '51.498152', 'street': {'id': 953...</td>\n",
       "      <td>False</td>\n",
       "      <td>Asian</td>\n",
       "      <td>Person and Vehicle search</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Controlled drugs</td>\n",
       "      <td>51.498152</td>\n",
       "      <td>-0.165735</td>\n",
       "      <td>953147.0</td>\n",
       "      <td>On or near Shopping Area</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>25-34</td>\n",
       "      <td>A no further action disposal</td>\n",
       "      <td>True</td>\n",
       "      <td>Asian/Asian British - Any other Asian background</td>\n",
       "      <td>Male</td>\n",
       "      <td>Misuse of Drugs Act 1971 (section 23)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-12-04T00:15:00+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'id': 'bu-no-further-action', 'name': 'A no f...</td>\n",
       "      <td>{'latitude': '51.495560', 'street': {'id': 952...</td>\n",
       "      <td>False</td>\n",
       "      <td>Asian</td>\n",
       "      <td>Person search</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Controlled drugs</td>\n",
       "      <td>51.495560</td>\n",
       "      <td>-0.181758</td>\n",
       "      <td>952968.0</td>\n",
       "      <td>On or near Atherstone Mews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>18-24</td>\n",
       "      <td>A no further action disposal</td>\n",
       "      <td>True</td>\n",
       "      <td>White - Any other White background</td>\n",
       "      <td>Male</td>\n",
       "      <td>Misuse of Drugs Act 1971 (section 23)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-12-04T00:39:00+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'id': 'bu-no-further-action', 'name': 'A no f...</td>\n",
       "      <td>{'latitude': '51.495560', 'street': {'id': 952...</td>\n",
       "      <td>False</td>\n",
       "      <td>White</td>\n",
       "      <td>Person and Vehicle search</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Controlled drugs</td>\n",
       "      <td>51.495560</td>\n",
       "      <td>-0.181758</td>\n",
       "      <td>952968.0</td>\n",
       "      <td>On or near Atherstone Mews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>25-34</td>\n",
       "      <td>A no further action disposal</td>\n",
       "      <td>True</td>\n",
       "      <td>White - Any other White background</td>\n",
       "      <td>Male</td>\n",
       "      <td>Misuse of Drugs Act 1971 (section 23)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-12-04T00:39:00+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'id': 'bu-no-further-action', 'name': 'A no f...</td>\n",
       "      <td>{'latitude': '51.495560', 'street': {'id': 952...</td>\n",
       "      <td>False</td>\n",
       "      <td>White</td>\n",
       "      <td>Person and Vehicle search</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Controlled drugs</td>\n",
       "      <td>51.495560</td>\n",
       "      <td>-0.181758</td>\n",
       "      <td>952968.0</td>\n",
       "      <td>On or near Atherstone Mews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>25-34</td>\n",
       "      <td>A no further action disposal</td>\n",
       "      <td>True</td>\n",
       "      <td>White - Any other White background</td>\n",
       "      <td>Male</td>\n",
       "      <td>Misuse of Drugs Act 1971 (section 23)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-12-04T00:40:00+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'id': 'bu-no-further-action', 'name': 'A no f...</td>\n",
       "      <td>{'latitude': '51.495560', 'street': {'id': 952...</td>\n",
       "      <td>False</td>\n",
       "      <td>White</td>\n",
       "      <td>Person search</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Controlled drugs</td>\n",
       "      <td>51.495560</td>\n",
       "      <td>-0.181758</td>\n",
       "      <td>952968.0</td>\n",
       "      <td>On or near Atherstone Mews</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   age_range                       outcome  involved_person  \\\n",
       "0      18-24  A no further action disposal             True   \n",
       "1        NaN  A no further action disposal             True   \n",
       "2        NaN  A no further action disposal             True   \n",
       "3    over 34          Community resolution             True   \n",
       "4      25-34  A no further action disposal             True   \n",
       "..       ...                           ...              ...   \n",
       "95     25-34  A no further action disposal             True   \n",
       "96     25-34  A no further action disposal             True   \n",
       "97     18-24  A no further action disposal             True   \n",
       "98     25-34  A no further action disposal             True   \n",
       "99     25-34  A no further action disposal             True   \n",
       "\n",
       "                               self_defined_ethnicity gender  \\\n",
       "0                     Asian/Asian British - Pakistani   Male   \n",
       "1                     Other ethnic group - Not stated   Male   \n",
       "2   Black/African/Caribbean/Black British - Any ot...   Male   \n",
       "3                     Other ethnic group - Not stated   Male   \n",
       "4   Black/African/Caribbean/Black British - Any ot...   Male   \n",
       "..                                                ...    ...   \n",
       "95   Asian/Asian British - Any other Asian background   Male   \n",
       "96   Asian/Asian British - Any other Asian background   Male   \n",
       "97                 White - Any other White background   Male   \n",
       "98                 White - Any other White background   Male   \n",
       "99                 White - Any other White background   Male   \n",
       "\n",
       "                                          legislation  \\\n",
       "0               Misuse of Drugs Act 1971 (section 23)   \n",
       "1               Misuse of Drugs Act 1971 (section 23)   \n",
       "2               Misuse of Drugs Act 1971 (section 23)   \n",
       "3   Police and Criminal Evidence Act 1984 (section 1)   \n",
       "4               Misuse of Drugs Act 1971 (section 23)   \n",
       "..                                                ...   \n",
       "95              Misuse of Drugs Act 1971 (section 23)   \n",
       "96              Misuse of Drugs Act 1971 (section 23)   \n",
       "97              Misuse of Drugs Act 1971 (section 23)   \n",
       "98              Misuse of Drugs Act 1971 (section 23)   \n",
       "99              Misuse of Drugs Act 1971 (section 23)   \n",
       "\n",
       "    outcome_linked_to_object_of_search                   datetime  \\\n",
       "0                                  NaN  2020-12-01T19:50:00+00:00   \n",
       "1                                  NaN  2020-12-04T03:08:00+00:00   \n",
       "2                                  NaN  2020-12-04T03:20:00+00:00   \n",
       "3                                  NaN  2020-12-04T01:00:00+00:00   \n",
       "4                                  NaN  2020-12-03T22:00:00+00:00   \n",
       "..                                 ...                        ...   \n",
       "95                                 NaN  2020-12-04T00:15:00+00:00   \n",
       "96                                 NaN  2020-12-04T00:15:00+00:00   \n",
       "97                                 NaN  2020-12-04T00:39:00+00:00   \n",
       "98                                 NaN  2020-12-04T00:39:00+00:00   \n",
       "99                                 NaN  2020-12-04T00:40:00+00:00   \n",
       "\n",
       "    removal_of_more_than_outer_clothing  \\\n",
       "0                                   NaN   \n",
       "1                                   NaN   \n",
       "2                                   NaN   \n",
       "3                                   NaN   \n",
       "4                                   NaN   \n",
       "..                                  ...   \n",
       "95                                  NaN   \n",
       "96                                  NaN   \n",
       "97                                  NaN   \n",
       "98                                  NaN   \n",
       "99                                  NaN   \n",
       "\n",
       "                                       outcome_object  \\\n",
       "0   {'id': 'bu-no-further-action', 'name': 'A no f...   \n",
       "1   {'id': 'bu-no-further-action', 'name': 'A no f...   \n",
       "2   {'id': 'bu-no-further-action', 'name': 'A no f...   \n",
       "3   {'id': 'bu-community-resolution', 'name': 'Com...   \n",
       "4   {'id': 'bu-no-further-action', 'name': 'A no f...   \n",
       "..                                                ...   \n",
       "95  {'id': 'bu-no-further-action', 'name': 'A no f...   \n",
       "96  {'id': 'bu-no-further-action', 'name': 'A no f...   \n",
       "97  {'id': 'bu-no-further-action', 'name': 'A no f...   \n",
       "98  {'id': 'bu-no-further-action', 'name': 'A no f...   \n",
       "99  {'id': 'bu-no-further-action', 'name': 'A no f...   \n",
       "\n",
       "                                             location  operation  \\\n",
       "0                                                 NaN      False   \n",
       "1   {'latitude': '51.514168', 'street': {'id': 956...      False   \n",
       "2   {'latitude': '51.512938', 'street': {'id': 956...      False   \n",
       "3   {'latitude': '51.516524', 'street': {'id': 957...      False   \n",
       "4   {'latitude': '51.519227', 'street': {'id': 960...      False   \n",
       "..                                                ...        ...   \n",
       "95  {'latitude': '51.498152', 'street': {'id': 953...      False   \n",
       "96  {'latitude': '51.495560', 'street': {'id': 952...      False   \n",
       "97  {'latitude': '51.495560', 'street': {'id': 952...      False   \n",
       "98  {'latitude': '51.495560', 'street': {'id': 952...      False   \n",
       "99  {'latitude': '51.495560', 'street': {'id': 952...      False   \n",
       "\n",
       "   officer_defined_ethnicity                       type  operation_name  \\\n",
       "0                      Asian              Person search             NaN   \n",
       "1                      Black              Person search             NaN   \n",
       "2                      Black  Person and Vehicle search             NaN   \n",
       "3                      Other              Person search             NaN   \n",
       "4                      Black  Person and Vehicle search             NaN   \n",
       "..                       ...                        ...             ...   \n",
       "95                     Asian  Person and Vehicle search             NaN   \n",
       "96                     Asian              Person search             NaN   \n",
       "97                     White  Person and Vehicle search             NaN   \n",
       "98                     White  Person and Vehicle search             NaN   \n",
       "99                     White              Person search             NaN   \n",
       "\n",
       "                      object_of_search   latitude  longitude  street_id  \\\n",
       "0                     Controlled drugs        NaN        NaN        NaN   \n",
       "1                     Controlled drugs  51.514168  -0.151458   956836.0   \n",
       "2                     Controlled drugs  51.512938  -0.150441   956855.0   \n",
       "3   Evidence of offences under the Act  51.516524  -0.172117   957057.0   \n",
       "4                     Controlled drugs  51.519227  -0.167858   960466.0   \n",
       "..                                 ...        ...        ...        ...   \n",
       "95                    Controlled drugs  51.498152  -0.165735   953147.0   \n",
       "96                    Controlled drugs  51.495560  -0.181758   952968.0   \n",
       "97                    Controlled drugs  51.495560  -0.181758   952968.0   \n",
       "98                    Controlled drugs  51.495560  -0.181758   952968.0   \n",
       "99                    Controlled drugs  51.495560  -0.181758   952968.0   \n",
       "\n",
       "                   street_name  \n",
       "0                          NaN  \n",
       "1     On or near Oxford Street  \n",
       "2       On or near Duke'S Yard  \n",
       "3     On or near Norfolk Place  \n",
       "4    On or near Cabbell Street  \n",
       "..                         ...  \n",
       "95    On or near Shopping Area  \n",
       "96  On or near Atherstone Mews  \n",
       "97  On or near Atherstone Mews  \n",
       "98  On or near Atherstone Mews  \n",
       "99  On or near Atherstone Mews  \n",
       "\n",
       "[100 rows x 20 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sf_pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "1dcba70a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>age_range</th>\n",
       "      <th>outcome</th>\n",
       "      <th>involved_person</th>\n",
       "      <th>self_defined_ethnicity</th>\n",
       "      <th>gender</th>\n",
       "      <th>legislation</th>\n",
       "      <th>outcome_linked_to_object_of_search</th>\n",
       "      <th>datetime</th>\n",
       "      <th>removal_of_more_than_outer_clothing</th>\n",
       "      <th>outcome_object</th>\n",
       "      <th>location</th>\n",
       "      <th>operation</th>\n",
       "      <th>officer_defined_ethnicity</th>\n",
       "      <th>type</th>\n",
       "      <th>operation_name</th>\n",
       "      <th>object_of_search</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>street_id</th>\n",
       "      <th>street_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>280</th>\n",
       "      <td>10-17</td>\n",
       "      <td>A no further action disposal</td>\n",
       "      <td>True</td>\n",
       "      <td>Mixed/Multiple ethnic groups - White and Black...</td>\n",
       "      <td>Male</td>\n",
       "      <td>Police and Criminal Evidence Act 1984 (section 1)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-12-18T21:20:00+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'id': 'bu-no-further-action', 'name': 'A no f...</td>\n",
       "      <td>{'latitude': '51.481567', 'street': {'id': 950...</td>\n",
       "      <td>False</td>\n",
       "      <td>Black</td>\n",
       "      <td>Person search</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Offensive weapons</td>\n",
       "      <td>51.481567</td>\n",
       "      <td>-0.178253</td>\n",
       "      <td>950082.0</td>\n",
       "      <td>On or near Blantyre Street</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    age_range                       outcome  involved_person  \\\n",
       "280     10-17  A no further action disposal             True   \n",
       "\n",
       "                                self_defined_ethnicity gender  \\\n",
       "280  Mixed/Multiple ethnic groups - White and Black...   Male   \n",
       "\n",
       "                                           legislation  \\\n",
       "280  Police and Criminal Evidence Act 1984 (section 1)   \n",
       "\n",
       "     outcome_linked_to_object_of_search                   datetime  \\\n",
       "280                                 NaN  2020-12-18T21:20:00+00:00   \n",
       "\n",
       "     removal_of_more_than_outer_clothing  \\\n",
       "280                                  NaN   \n",
       "\n",
       "                                        outcome_object  \\\n",
       "280  {'id': 'bu-no-further-action', 'name': 'A no f...   \n",
       "\n",
       "                                              location  operation  \\\n",
       "280  {'latitude': '51.481567', 'street': {'id': 950...      False   \n",
       "\n",
       "    officer_defined_ethnicity           type  operation_name  \\\n",
       "280                     Black  Person search             NaN   \n",
       "\n",
       "      object_of_search   latitude  longitude  street_id  \\\n",
       "280  Offensive weapons  51.481567  -0.178253   950082.0   \n",
       "\n",
       "                    street_name  \n",
       "280  On or near Blantyre Street  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_df_sf_pd = df_sf_pd.sample()\n",
    "sample_df_sf_pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "7ac0561c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "age_range                               object\n",
       "outcome                                 object\n",
       "involved_person                           bool\n",
       "self_defined_ethnicity                  object\n",
       "gender                                  object\n",
       "legislation                             object\n",
       "outcome_linked_to_object_of_search     float64\n",
       "datetime                                object\n",
       "removal_of_more_than_outer_clothing    float64\n",
       "outcome_object                          object\n",
       "location                                object\n",
       "operation                                 bool\n",
       "officer_defined_ethnicity               object\n",
       "type                                    object\n",
       "operation_name                         float64\n",
       "object_of_search                        object\n",
       "latitude                               float64\n",
       "longitude                              float64\n",
       "street_id                              float64\n",
       "street_name                             object\n",
       "dtype: object"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sf_pd.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "ba61e1d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "5356b110",
   "metadata": {},
   "outputs": [],
   "source": [
    "schema = types.StructType([\n",
    "    types.StructField('age_range', types.StringType(), True),\n",
    "    types.StructField('outcome', types.StringType(), True),\n",
    "    types.StructField('involved_person', types.BooleanType(), True),\n",
    "    types.StructField('self_defined_ethnicity', types.StringType(), True),\n",
    "    types.StructField('gender', types.StringType(), True),\n",
    "    types.StructField('legislation', types.StringType(), True),\n",
    "    types.StructField('outcome_linked_to_object_of_search', types.StringType(), True),\n",
    "    types.StructField('datetime', types.TimestampType(), True),\n",
    "    types.StructField('removal_of_more_than_outer_clothing', types.StringType(), True),\n",
    "    types.StructField('outcome_object', types.StringType(), True),\n",
    "    types.StructField('location', types.StringType(), True),\n",
    "    types.StructField('operation', types.StringType(), True),\n",
    "    types.StructField('officer_defined_ethnicity', types.StringType(), True),\n",
    "    types.StructField('type', types.StringType(), True),\n",
    "    types.StructField('operation_name', types.StringType(), True),\n",
    "    types.StructField('object_of_search', types.StringType(), True),\n",
    "    types.StructField('latitude', types.DoubleType(), True),\n",
    "    types.StructField('longitude', types.DoubleType(), True),\n",
    "    types.StructField('street_id', types.IntegerType(), True),\n",
    "    types.StructField('street_name', types.StringType(), True),\n",
    "])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "d644ddd8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = spark.read \\\n",
    "    .option(\"header\", \"true\") \\\n",
    "    .schema(schema) \\\n",
    "    .csv(FILE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "e2bb6bcc",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.repartition(24)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "950b6f06",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df = df.write.parquet('new_data/')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "bd61c5bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = spark.read.parquet('new_data/')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "41f18444",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- age_range: string (nullable = true)\n",
      " |-- outcome: string (nullable = true)\n",
      " |-- involved_person: boolean (nullable = true)\n",
      " |-- self_defined_ethnicity: string (nullable = true)\n",
      " |-- gender: string (nullable = true)\n",
      " |-- legislation: string (nullable = true)\n",
      " |-- outcome_linked_to_object_of_search: string (nullable = true)\n",
      " |-- datetime: timestamp (nullable = true)\n",
      " |-- removal_of_more_than_outer_clothing: string (nullable = true)\n",
      " |-- outcome_object: string (nullable = true)\n",
      " |-- location: string (nullable = true)\n",
      " |-- operation: string (nullable = true)\n",
      " |-- officer_defined_ethnicity: string (nullable = true)\n",
      " |-- type: string (nullable = true)\n",
      " |-- operation_name: string (nullable = true)\n",
      " |-- object_of_search: string (nullable = true)\n",
      " |-- latitude: double (nullable = true)\n",
      " |-- longitude: double (nullable = true)\n",
      " |-- street_id: integer (nullable = true)\n",
      " |-- street_name: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "1d1c70aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import functions as F"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "b3c806c9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+----------------------------+---------------+------------------------------------------------------------------------------------+------+-------------------------------------------------------+----------------------------------+-------------------+-----------------------------------+----------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+---------+-------------------------+-------------------------+--------------+-----------------------------------+---------+---------+---------+------------------------------+\n",
      "|age_range|outcome                     |involved_person|self_defined_ethnicity                                                              |gender|legislation                                            |outcome_linked_to_object_of_search|datetime           |removal_of_more_than_outer_clothing|outcome_object                                                        |location                                                                                                               |operation|officer_defined_ethnicity|type                     |operation_name|object_of_search                   |latitude |longitude|street_id|street_name                   |\n",
      "+---------+----------------------------+---------------+------------------------------------------------------------------------------------+------+-------------------------------------------------------+----------------------------------+-------------------+-----------------------------------+----------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+---------+-------------------------+-------------------------+--------------+-----------------------------------+---------+---------+---------+------------------------------+\n",
      "|null     |A no further action disposal|true           |Asian/Asian British - Bangladeshi                                                   |Male  |Police and Criminal Evidence Act 1984 (section 1)      |null                              |2020-12-01 17:00:00|null                               |{'id': 'bu-no-further-action', 'name': 'A no further action disposal'}|{'latitude': '51.526088', 'street': {'id': 961305, 'name': 'On or near Voss Street'}, 'longitude': '-0.065300'}        |False    |Asian                    |Person search            |null          |Evidence of offences under the Act |51.526088|-0.0653  |961305   |On or near Voss Street        |\n",
      "|null     |A no further action disposal|true           |Other ethnic group - Not stated                                                     |Male  |Misuse of Drugs Act 1971 (section 23)                  |null                              |2020-12-04 23:30:00|null                               |{'id': 'bu-no-further-action', 'name': 'A no further action disposal'}|{'latitude': '51.513034', 'street': {'id': 956961, 'name': 'On or near Queensway'}, 'longitude': '-0.187663'}          |False    |Black                    |Person and Vehicle search|null          |Controlled drugs                   |51.513034|-0.187663|956961   |On or near Queensway          |\n",
      "|over 34  |A no further action disposal|true           |Other ethnic group - Not stated                                                     |Male  |Misuse of Drugs Act 1971 (section 23)                  |null                              |2020-12-16 17:35:00|null                               |{'id': 'bu-no-further-action', 'name': 'A no further action disposal'}|{'latitude': '51.566686', 'street': {'id': 971460, 'name': 'On or near Norlington Road'}, 'longitude': '-0.001835'}    |False    |Black                    |Person search            |null          |Controlled drugs                   |51.566686|-0.001835|971460   |On or near Norlington Road    |\n",
      "|over 34  |A no further action disposal|true           |Black/African/Caribbean/Black British - Any other Black/African/Caribbean background|Male  |Misuse of Drugs Act 1971 (section 23)                  |null                              |2020-12-15 12:45:00|null                               |{'id': 'bu-no-further-action', 'name': 'A no further action disposal'}|{'latitude': '51.594459', 'street': {'id': 977573, 'name': 'On or near George Lane'}, 'longitude': '0.024218'}         |False    |Black                    |Person and Vehicle search|null          |Controlled drugs                   |51.594459|0.024218 |977573   |On or near George Lane        |\n",
      "|10-17    |A no further action disposal|true           |Other ethnic group - Not stated                                                     |Male  |Misuse of Drugs Act 1971 (section 23)                  |null                              |2020-12-16 18:45:00|null                               |{'id': 'bu-no-further-action', 'name': 'A no further action disposal'}|{'latitude': '51.495736', 'street': {'id': 953332, 'name': 'On or near Shopping Area'}, 'longitude': '-0.144755'}      |False    |Other                    |Person search            |null          |Controlled drugs                   |51.495736|-0.144755|953332   |On or near Shopping Area      |\n",
      "|18-24    |A no further action disposal|true           |Black/African/Caribbean/Black British - Any other Black/African/Caribbean background|Male  |Criminal Justice and Public Order Act 1994 (section 60)|null                              |2020-12-11 23:34:00|null                               |{'id': 'bu-no-further-action', 'name': 'A no further action disposal'}|{'latitude': '51.521698', 'street': {'id': 962213, 'name': 'On or near Prince Regent Lane'}, 'longitude': '0.031541'}  |False    |Black                    |Person search            |null          |Anything to threaten or harm anyone|51.521698|0.031541 |962213   |On or near Prince Regent Lane |\n",
      "|25-34    |A no further action disposal|true           |Asian/Asian British - Bangladeshi                                                   |Male  |Misuse of Drugs Act 1971 (section 23)                  |null                              |2020-12-31 17:27:00|null                               |{'id': 'bu-no-further-action', 'name': 'A no further action disposal'}|{'latitude': '51.531056', 'street': {'id': 964166, 'name': 'On or near B118'}, 'longitude': '-0.047658'}               |False    |Asian                    |Person and Vehicle search|null          |Controlled drugs                   |51.531056|-0.047658|964166   |On or near B118               |\n",
      "|18-24    |A no further action disposal|true           |Asian/Asian British - Bangladeshi                                                   |Male  |Misuse of Drugs Act 1971 (section 23)                  |null                              |2020-12-09 17:00:00|null                               |{'id': 'bu-no-further-action', 'name': 'A no further action disposal'}|{'latitude': '51.538115', 'street': {'id': 963279, 'name': 'On or near High Street North'}, 'longitude': '0.051422'}   |False    |Asian                    |Person and Vehicle search|null          |Controlled drugs                   |51.538115|0.051422 |963279   |On or near High Street North  |\n",
      "|18-24    |Penalty Notice for Disorder |true           |White - English/Welsh/Scottish/Northern Irish/British                               |Male  |Misuse of Drugs Act 1971 (section 23)                  |null                              |2020-12-09 15:15:00|null                               |{'id': 'bu-penalty-notice', 'name': 'Penalty Notice for Disorder'}    |{'latitude': '51.528238', 'street': {'id': 960514, 'name': 'On or near Parking Area'}, 'longitude': '-0.133674'}       |False    |White                    |Person search            |null          |Controlled drugs                   |51.528238|-0.133674|960514   |On or near Parking Area       |\n",
      "|18-24    |A no further action disposal|true           |White - Any other White background                                                  |Male  |Police and Criminal Evidence Act 1984 (section 1)      |null                              |2020-12-11 15:40:00|null                               |{'id': 'bu-no-further-action', 'name': 'A no further action disposal'}|{'latitude': '51.617267', 'street': {'id': 983481, 'name': 'On or near Old Church Road'}, 'longitude': '-0.017612'}    |False    |White                    |Person search            |null          |Evidence of offences under the Act |51.617267|-0.017612|983481   |On or near Old Church Road    |\n",
      "|18-24    |A no further action disposal|true           |White - Any other White background                                                  |Male  |Police and Criminal Evidence Act 1984 (section 1)      |null                              |2020-12-19 00:01:00|null                               |{'id': 'bu-no-further-action', 'name': 'A no further action disposal'}|{'latitude': '51.501207', 'street': {'id': 953429, 'name': 'On or near Great George Street'}, 'longitude': '-0.128569'}|False    |White                    |Person search            |null          |Articles for use in criminal damage|51.501207|-0.128569|953429   |On or near Great George Street|\n",
      "|10-17    |A no further action disposal|true           |Asian/Asian British - Chinese                                                       |Male  |Police and Criminal Evidence Act 1984 (section 1)      |null                              |2020-12-12 16:48:00|null                               |{'id': 'bu-no-further-action', 'name': 'A no further action disposal'}|{'latitude': '51.505396', 'street': {'id': 956106, 'name': 'On or near Supermarket'}, 'longitude': '-0.088901'}        |False    |Asian                    |Person search            |null          |Stolen goods                       |51.505396|-0.088901|956106   |On or near Supermarket        |\n",
      "|18-24    |A no further action disposal|true           |Asian/Asian British - Pakistani                                                     |Male  |Police and Criminal Evidence Act 1984 (section 1)      |null                              |2020-12-06 23:18:00|null                               |{'id': 'bu-no-further-action', 'name': 'A no further action disposal'}|{'latitude': '51.573905', 'street': {'id': 975765, 'name': 'On or near A406'}, 'longitude': '0.046337'}                |False    |Asian                    |Person search            |null          |Evidence of offences under the Act |51.573905|0.046337 |975765   |On or near A406               |\n",
      "|18-24    |A no further action disposal|true           |White - Any other White background                                                  |Female|Police and Criminal Evidence Act 1984 (section 1)      |null                              |2020-12-24 17:45:00|null                               |{'id': 'bu-no-further-action', 'name': 'A no further action disposal'}|null                                                                                                                   |False    |White                    |Person search            |null          |Stolen goods                       |null     |null     |null     |null                          |\n",
      "|over 34  |A no further action disposal|true           |White - Any other White background                                                  |Male  |Police and Criminal Evidence Act 1984 (section 1)      |null                              |2020-12-11 15:10:00|null                               |{'id': 'bu-no-further-action', 'name': 'A no further action disposal'}|{'latitude': '51.536432', 'street': {'id': 963167, 'name': 'On or near Barking Road'}, 'longitude': '0.068376'}        |False    |White                    |Person search            |null          |Stolen goods                       |51.536432|0.068376 |963167   |On or near Barking Road       |\n",
      "|25-34    |A no further action disposal|true           |White - English/Welsh/Scottish/Northern Irish/British                               |Male  |Police and Criminal Evidence Act 1984 (section 1)      |null                              |2020-12-05 13:18:00|null                               |{'id': 'bu-no-further-action', 'name': 'A no further action disposal'}|{'latitude': '51.546380', 'street': {'id': 969057, 'name': 'On or near Mcgrath Road'}, 'longitude': '0.013458'}        |False    |White                    |Person search            |null          |Evidence of offences under the Act |51.54638 |0.013458 |969057   |On or near Mcgrath Road       |\n",
      "|25-34    |A no further action disposal|true           |Other ethnic group - Not stated                                                     |Male  |Misuse of Drugs Act 1971 (section 23)                  |null                              |2020-12-03 19:14:00|null                               |{'id': 'bu-no-further-action', 'name': 'A no further action disposal'}|{'latitude': '51.540169', 'street': {'id': 965087, 'name': 'On or near Jamestown Road'}, 'longitude': '-0.145977'}     |False    |Black                    |Person search            |null          |Controlled drugs                   |51.540169|-0.145977|965087   |On or near Jamestown Road     |\n",
      "|25-34    |Arrest                      |true           |Asian/Asian British - Bangladeshi                                                   |Male  |Misuse of Drugs Act 1971 (section 23)                  |null                              |2020-12-15 00:55:00|null                               |{'id': 'bu-arrest', 'name': 'Arrest'}                                 |{'latitude': '51.545870', 'street': {'id': 969150, 'name': 'On or near Manbey Park Road'}, 'longitude': '0.005993'}    |False    |Asian                    |Person and Vehicle search|null          |Controlled drugs                   |51.54587 |0.005993 |969150   |On or near Manbey Park Road   |\n",
      "|18-24    |A no further action disposal|true           |White - English/Welsh/Scottish/Northern Irish/British                               |Male  |Misuse of Drugs Act 1971 (section 23)                  |null                              |2020-12-31 00:29:00|null                               |{'id': 'bu-no-further-action', 'name': 'A no further action disposal'}|{'latitude': '51.518492', 'street': {'id': 957466, 'name': 'On or near St Marks Road'}, 'longitude': '-0.216432'}      |False    |White                    |Person search            |null          |Controlled drugs                   |51.518492|-0.216432|957466   |On or near St Marks Road      |\n",
      "|null     |A no further action disposal|true           |Other ethnic group - Not stated                                                     |Male  |Police and Criminal Evidence Act 1984 (section 1)      |null                              |2020-12-13 22:55:00|null                               |{'id': 'bu-no-further-action', 'name': 'A no further action disposal'}|{'latitude': '51.469183', 'street': {'id': 945349, 'name': 'On or near Church Gate'}, 'longitude': '-0.210898'}        |False    |White                    |Person search            |null          |Evidence of offences under the Act |51.469183|-0.210898|945349   |On or near Church Gate        |\n",
      "+---------+----------------------------+---------------+------------------------------------------------------------------------------------+------+-------------------------------------------------------+----------------------------------+-------------------+-----------------------------------+----------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+---------+-------------------------+-------------------------+--------------+-----------------------------------+---------+---------+---------+------------------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "5ed84586",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 10:===================================>                  (133 + 4) / 200]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+-----+\n",
      "|age_range|count|\n",
      "+---------+-----+\n",
      "|    18-24| 6866|\n",
      "|    25-34| 4578|\n",
      "|  over 34| 3376|\n",
      "|    10-17| 3074|\n",
      "|     null| 2472|\n",
      "| under 10|    6|\n",
      "+---------+-----+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r\n",
      "[Stage 10:=============================================>        (169 + 4) / 200]\r\n",
      "\r\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df.groupBy('age_range').count().orderBy('count', ascending = False).show() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "b977fd31",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r\n",
      "[Stage 12:=======================>                               (86 + 4) / 200]\r\n",
      "\r\n",
      "[Stage 12:===================================>                  (132 + 4) / 200]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+-----+\n",
      "|gender|count|\n",
      "+------+-----+\n",
      "|  Male|18528|\n",
      "|Female| 1553|\n",
      "|  null|  270|\n",
      "| Other|   21|\n",
      "+------+-----+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r\n",
      "[Stage 12:==============================================>       (172 + 5) / 200]\r\n",
      "\r\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df.groupBy('gender').count().orderBy('count', ascending = False).show() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "dfe5440c",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r\n",
      "[Stage 16:===============================>                      (115 + 4) / 200]\r\n",
      "\r\n",
      "[Stage 16:==============================================>       (173 + 4) / 200]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------------------------------------+-----+\n",
      "|street_name                                   |count|\n",
      "+----------------------------------------------+-----+\n",
      "|null                                          |2166 |\n",
      "|On or near Parking Area                       |512  |\n",
      "|On or near Supermarket                        |488  |\n",
      "|On or near Pedestrian Subway                  |486  |\n",
      "|On or near Shopping Area                      |371  |\n",
      "|On or near Sports/Recreation Area             |260  |\n",
      "|On or near Petrol Station                     |246  |\n",
      "|On or near Nightclub                          |217  |\n",
      "|On or near Police Station                     |192  |\n",
      "|On or near Theatre/Concert Hall               |163  |\n",
      "|On or near Further/Higher Educational Building|135  |\n",
      "|On or near Station Road                       |129  |\n",
      "|On or near High Street                        |113  |\n",
      "|On or near Park/Open Space                    |105  |\n",
      "|On or near High Road                          |57   |\n",
      "|On or near Longbridge Road                    |52   |\n",
      "|On or near London Road                        |50   |\n",
      "|On or near Brixton Hill                       |47   |\n",
      "|On or near Romford Road                       |43   |\n",
      "|On or near Garratt Lane                       |42   |\n",
      "+----------------------------------------------+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df.groupBy('street_name').count().orderBy('count', ascending = False).show(truncate=False) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "f727e273",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------------+-----+\n",
      "|officer_defined_ethnicity|count|\n",
      "+-------------------------+-----+\n",
      "|White                    |8195 |\n",
      "|Black                    |6621 |\n",
      "|Asian                    |3729 |\n",
      "|Other                    |969  |\n",
      "|null                     |443  |\n",
      "|False                    |415  |\n",
      "+-------------------------+-----+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r\n",
      "[Stage 18:=============================================>        (170 + 4) / 200]\r\n",
      "\r\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df.groupBy('officer_defined_ethnicity').count().orderBy('count', ascending = False).show(truncate=False) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "89c0b66f",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r\n",
      "[Stage 20:============================>                         (104 + 4) / 200]\r\n",
      "\r\n",
      "[Stage 20:========================================>             (151 + 4) / 200]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------------------------------------------------------------------------+-----+\n",
      "|self_defined_ethnicity                                                              |count|\n",
      "+------------------------------------------------------------------------------------+-----+\n",
      "|Other ethnic group - Not stated                                                     |4829 |\n",
      "|White - English/Welsh/Scottish/Northern Irish/British                               |4194 |\n",
      "|White - Any other White background                                                  |2386 |\n",
      "|Black/African/Caribbean/Black British - Any other Black/African/Caribbean background|2170 |\n",
      "|Asian/Asian British - Any other Asian background                                    |1523 |\n",
      "|Black/African/Caribbean/Black British - African                                     |1487 |\n",
      "|Black/African/Caribbean/Black British - Caribbean                                   |814  |\n",
      "|Asian/Asian British - Bangladeshi                                                   |611  |\n",
      "|Other ethnic group - Any other ethnic group                                         |511  |\n",
      "|Asian/Asian British - Pakistani                                                     |379  |\n",
      "|Asian/Asian British - Indian                                                        |353  |\n",
      "|Mixed/Multiple ethnic groups - Any other Mixed/Multiple ethnic background           |344  |\n",
      "|null                                                                                |272  |\n",
      "|White - Irish                                                                       |192  |\n",
      "|Mixed/Multiple ethnic groups - White and Black Caribbean                            |169  |\n",
      "|Mixed/Multiple ethnic groups - White and Black African                              |66   |\n",
      "|Mixed/Multiple ethnic groups - White and Asian                                      |43   |\n",
      "|Asian/Asian British - Chinese                                                       |29   |\n",
      "+------------------------------------------------------------------------------------+-----+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df.groupBy('self_defined_ethnicity').count().orderBy('count', ascending = False).show(truncate=False) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "132b9235",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------------------------------------------+-----+\n",
      "|legislation                                            |count|\n",
      "+-------------------------------------------------------+-----+\n",
      "|Misuse of Drugs Act 1971 (section 23)                  |13855|\n",
      "|Police and Criminal Evidence Act 1984 (section 1)      |6285 |\n",
      "|Criminal Justice and Public Order Act 1994 (section 60)|133  |\n",
      "|Firearms Act 1968 (section 47)                         |99   |\n",
      "+-------------------------------------------------------+-----+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r\n",
      "[Stage 22:===============================================>      (177 + 4) / 200]\r\n",
      "\r\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df.groupBy('legislation').count().orderBy('count', ascending = False).show(truncate=False) "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}