pyspark_learning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Dd6t0uFzuR4X"
   },
   "source": [
    "## Download and install Spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "zToS5PqxBXpK"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "tt7ZS1_wGgjn"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "FEuEkV0BlzAD"
   },
   "source": [
    "## Setup environment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "sdOOq4twHN1K"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "            <div>\n",
       "                <p><b>SparkSession - in-memory</b></p>\n",
       "                \n",
       "        <div>\n",
       "            <p><b>SparkContext</b></p>\n",
       "\n",
       "            <p><a href=\"http://MSI:4041\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v3.2.1</code></dd>\n",
       "              <dt>Master</dt>\n",
       "                <dd><code>local[*]</code></dd>\n",
       "              <dt>AppName</dt>\n",
       "                <dd><code>pyspark-shell</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0x1bf7b5bedf0>"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# import os\n",
    "# os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-11-openjdk-amd64\"\n",
    "# os.environ[\"SPARK_HOME\"] = \"/content/spark/...\"\n",
    "\n",
    "import findspark\n",
    "findspark.init()\n",
    "from pyspark import SparkContext\n",
    "sc = SparkContext.getOrCreate()\n",
    "\n",
    "import pyspark\n",
    "from pyspark.sql import SparkSession\n",
    "spark = SparkSession.builder.getOrCreate() \n",
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "FDtw5Hy3N-pV"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>pre { white-space: pre !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.core.display import HTML\n",
    "display(HTML(\"<style>pre { white-space: pre !important; }</style>\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "v4P5mMONYyVd"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "MzpuCsvDPWM-"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "9-1cK0nPNS95"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-----------+-------------------+--------------------+----+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "|      ID|Case Number|               Date|               Block|IUCR|Primary Type|         Description|Location Description|Arrest|Domestic|Beat|District|Ward|Community Area|FBI Code|X Coordinate|Y Coordinate|Year|          Updated On|    Latitude|    Longitude|            Location|\n",
      "+--------+-----------+-------------------+--------------------+----+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "|10224738|   HY411648|2015-09-05 13:30:00|     043XX S WOOD ST|0486|     BATTERY|DOMESTIC BATTERY ...|           RESIDENCE| false|    true|0924|     009|  12|            61|     08B|     1165074|     1875917|2015|02/10/2018 03:50:...|41.815117282|-87.669999562|(41.815117282, -8...|\n",
      "|10224739|   HY411615|2015-09-04 11:30:00| 008XX N CENTRAL AVE|0870|       THEFT|      POCKET-PICKING|             CTA BUS| false|   false|1511|     015|  29|            25|      06|     1138875|     1904869|2015|02/10/2018 03:50:...|41.895080471|-87.765400451|(41.895080471, -8...|\n",
      "|11646166|   JC213529|2018-09-01 00:01:00|082XX S INGLESIDE...|0810|       THEFT|           OVER $500|           RESIDENCE| false|    true|0631|     006|   8|            44|      06|        null|        null|2018|04/06/2019 04:04:...|        null|         null|                null|\n",
      "|10224740|   HY411595|2015-09-05 12:45:00|   035XX W BARRY AVE|2023|   NARCOTICS|POSS: HEROIN(BRN/...|            SIDEWALK|  true|   false|1412|     014|  35|            21|      18|     1152037|     1920384|2015|02/10/2018 03:50:...|41.937405765|-87.716649687|(41.937405765, -8...|\n",
      "|10224741|   HY411610|2015-09-05 13:00:00| 0000X N LARAMIE AVE|0560|     ASSAULT|              SIMPLE|           APARTMENT| false|    true|1522|     015|  28|            25|     08A|     1141706|     1900086|2015|02/10/2018 03:50:...|41.881903443|-87.755121152|(41.881903443, -8...|\n",
      "+--------+-----------+-------------------+--------------------+----+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "from pyspark.sql.functions import to_timestamp,col,lit\n",
    "rc = spark.read.csv('C:\\\\Users\\\\shubh\\\\Downloads\\\\Crimes.csv',header=True).withColumn('Date',to_timestamp(col('Date'),'MM/dd/yyyy hh:mm:ss a')).filter(col('Date') <= lit('2018-11-11'))\n",
    "rc.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "FtDmwilgogPH"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- ID: string (nullable = true)\n",
      " |-- Case Number: string (nullable = true)\n",
      " |-- Date: timestamp (nullable = true)\n",
      " |-- Block: string (nullable = true)\n",
      " |-- IUCR: string (nullable = true)\n",
      " |-- Primary Type: string (nullable = true)\n",
      " |-- Description: string (nullable = true)\n",
      " |-- Location Description: string (nullable = true)\n",
      " |-- Arrest: string (nullable = true)\n",
      " |-- Domestic: string (nullable = true)\n",
      " |-- Beat: string (nullable = true)\n",
      " |-- District: string (nullable = true)\n",
      " |-- Ward: string (nullable = true)\n",
      " |-- Community Area: string (nullable = true)\n",
      " |-- FBI Code: string (nullable = true)\n",
      " |-- X Coordinate: string (nullable = true)\n",
      " |-- Y Coordinate: string (nullable = true)\n",
      " |-- Year: string (nullable = true)\n",
      " |-- Updated On: string (nullable = true)\n",
      " |-- Latitude: string (nullable = true)\n",
      " |-- Longitude: string (nullable = true)\n",
      " |-- Location: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Zxbq2qCyogGp"
   },
   "outputs": [],
   "source": [
    "from pyspark.sql.types import StructType, StructField, StringType, TimestampType, BooleanType, DoubleType, IntegerType"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ID',\n",
       " 'Case Number',\n",
       " 'Date',\n",
       " 'Block',\n",
       " 'IUCR',\n",
       " 'Primary Type',\n",
       " 'Description',\n",
       " 'Location Description',\n",
       " 'Arrest',\n",
       " 'Domestic',\n",
       " 'Beat',\n",
       " 'District',\n",
       " 'Ward',\n",
       " 'Community Area',\n",
       " 'FBI Code',\n",
       " 'X Coordinate',\n",
       " 'Y Coordinate',\n",
       " 'Year',\n",
       " 'Updated On',\n",
       " 'Latitude',\n",
       " 'Longitude',\n",
       " 'Location']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rc.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "StructType([\n",
    "    StructField('ID',StringType,True),\n",
    "    StructField('Case Number',StringType, True),\n",
    " StructField('Date',TimestampType, True),\n",
    " 'Block',\n",
    " 'IUCR',\n",
    " 'Primary Type',\n",
    " 'Description',\n",
    " 'Location Description',\n",
    " 'Arrest',\n",
    " 'Domestic',\n",
    " 'Beat',\n",
    " 'District',\n",
    " 'Ward',\n",
    " 'Community Area',\n",
    " 'FBI Code',\n",
    " 'X Coordinate',\n",
    " 'Y Coordinate',\n",
    " 'Year',\n",
    " 'Updated On',\n",
    " 'Latitude',\n",
    " 'Longitude',\n",
    " 'Location')\n",
    "])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "labels = [('ID',StringType()),\n",
    " ('Case Number',StringType()),\n",
    " ('Date',TimestampType()),\n",
    " ('Block',StringType()),\n",
    " ('IUCR',StringType()),\n",
    " ('Primary Type',StringType()),\n",
    " ('Description',StringType()),\n",
    " ('Location Description',StringType()),\n",
    " ('Arrest',StringType()),\n",
    " ('Domestic',BooleanType()),\n",
    " ('Beat',StringType()),\n",
    " ('District',StringType()),\n",
    " ('Ward',StringType()),\n",
    " ('Community Area',StringType()),\n",
    " ('FBI Code',StringType()),\n",
    " ('X Coordinate',StringType()),\n",
    " ('Y Coordinate',StringType()),\n",
    " ('Year',IntegerType()),\n",
    " ('Updated On',StringType()),\n",
    " ('Latitude',DoubleType()),\n",
    " ('Longitude',DoubleType()),\n",
    " ('Location',StringType())]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "schema = StructType([StructField (x[0],x[1],True) for x in labels])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "StructType(List(StructField(ID,StringType,true),StructField(Case Number,StringType,true),StructField(Date,TimestampType,true),StructField(Block,StringType,true),StructField(IUCR,StringType,true),StructField(Primary Type,StringType,true),StructField(Description,StringType,true),StructField(Location Description,StringType,true),StructField(Arrest,StringType,true),StructField(Domestic,BooleanType,true),StructField(Beat,StringType,true),StructField(District,StringType,true),StructField(Ward,StringType,true),StructField(Community Area,StringType,true),StructField(FBI Code,StringType,true),StructField(X Coordinate,StringType,true),StructField(Y Coordinate,StringType,true),StructField(Year,IntegerType,true),StructField(Updated On,StringType,true),StructField(Latitude,DoubleType,true),StructField(Longitude,DoubleType,true),StructField(Location,StringType,true)))"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "rc = spark.read.csv(\"C:\\\\Users\\\\shubh\\\\Downloads\\\\Crimes.csv\",schema=schema)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- ID: string (nullable = true)\n",
      " |-- Case Number: string (nullable = true)\n",
      " |-- Date: timestamp (nullable = true)\n",
      " |-- Block: string (nullable = true)\n",
      " |-- IUCR: string (nullable = true)\n",
      " |-- Primary Type: string (nullable = true)\n",
      " |-- Description: string (nullable = true)\n",
      " |-- Location Description: string (nullable = true)\n",
      " |-- Arrest: string (nullable = true)\n",
      " |-- Domestic: boolean (nullable = true)\n",
      " |-- Beat: string (nullable = true)\n",
      " |-- District: string (nullable = true)\n",
      " |-- Ward: string (nullable = true)\n",
      " |-- Community Area: string (nullable = true)\n",
      " |-- FBI Code: string (nullable = true)\n",
      " |-- X Coordinate: string (nullable = true)\n",
      " |-- Y Coordinate: string (nullable = true)\n",
      " |-- Year: integer (nullable = true)\n",
      " |-- Updated On: string (nullable = true)\n",
      " |-- Latitude: double (nullable = true)\n",
      " |-- Longitude: double (nullable = true)\n",
      " |-- Location: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-----------+----+--------------------+----+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "|      ID|Case Number|Date|               Block|IUCR|Primary Type|         Description|Location Description|Arrest|Domestic|Beat|District|Ward|Community Area|FBI Code|X Coordinate|Y Coordinate|Year|          Updated On|    Latitude|    Longitude|            Location|\n",
      "+--------+-----------+----+--------------------+----+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "|      ID|Case Number|null|               Block|IUCR|Primary Type|         Description|Location Description|Arrest|    null|Beat|District|Ward|Community Area|FBI Code|X Coordinate|Y Coordinate|null|          Updated On|        null|         null|            Location|\n",
      "|10224738|   HY411648|null|     043XX S WOOD ST|0486|     BATTERY|DOMESTIC BATTERY ...|           RESIDENCE| false|    true|0924|     009|  12|            61|     08B|     1165074|     1875917|2015|02/10/2018 03:50:...|41.815117282|-87.669999562|(41.815117282, -8...|\n",
      "|10224739|   HY411615|null| 008XX N CENTRAL AVE|0870|       THEFT|      POCKET-PICKING|             CTA BUS| false|   false|1511|     015|  29|            25|      06|     1138875|     1904869|2015|02/10/2018 03:50:...|41.895080471|-87.765400451|(41.895080471, -8...|\n",
      "|11646166|   JC213529|null|082XX S INGLESIDE...|0810|       THEFT|           OVER $500|           RESIDENCE| false|    true|0631|     006|   8|            44|      06|        null|        null|2018|04/06/2019 04:04:...|        null|         null|                null|\n",
      "|10224740|   HY411595|null|   035XX W BARRY AVE|2023|   NARCOTICS|POSS: HEROIN(BRN/...|            SIDEWALK|  true|   false|1412|     014|  35|            21|      18|     1152037|     1920384|2015|02/10/2018 03:50:...|41.937405765|-87.716649687|(41.937405765, -8...|\n",
      "+--------+-----------+----+--------------------+----+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#Ran [4] th line again"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+\n",
      "|IUCR|\n",
      "+----+\n",
      "|0486|\n",
      "|0870|\n",
      "|0810|\n",
      "|2023|\n",
      "|0560|\n",
      "+----+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.select('IUCR').show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+\n",
      "|IUCR|\n",
      "+----+\n",
      "|0486|\n",
      "|0870|\n",
      "|0810|\n",
      "|2023|\n",
      "|0560|\n",
      "+----+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.select(rc.IUCR).show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+\n",
      "|IUCR|\n",
      "+----+\n",
      "|0486|\n",
      "|0870|\n",
      "|0810|\n",
      "|2023|\n",
      "|0560|\n",
      "+----+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.select(col('IUCR')).show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+-------------------+------+\n",
      "|Case Number|               Date|Arrest|\n",
      "+-----------+-------------------+------+\n",
      "|   HY411648|2015-09-05 13:30:00| false|\n",
      "|   HY411615|2015-09-04 11:30:00| false|\n",
      "|   JC213529|2018-09-01 00:01:00| false|\n",
      "|   HY411595|2015-09-05 12:45:00|  true|\n",
      "+-----------+-------------------+------+\n",
      "only showing top 4 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.select('Case Number','Date','Arrest').show(4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import lit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-----------+-------------------+--------------------+----+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+---+\n",
      "|      ID|Case Number|               Date|               Block|IUCR|Primary Type|         Description|Location Description|Arrest|Domestic|Beat|District|Ward|Community Area|FBI Code|X Coordinate|Y Coordinate|Year|          Updated On|    Latitude|    Longitude|            Location|One|\n",
      "+--------+-----------+-------------------+--------------------+----+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+---+\n",
      "|10224738|   HY411648|2015-09-05 13:30:00|     043XX S WOOD ST|0486|     BATTERY|DOMESTIC BATTERY ...|           RESIDENCE| false|    true|0924|     009|  12|            61|     08B|     1165074|     1875917|2015|02/10/2018 03:50:...|41.815117282|-87.669999562|(41.815117282, -8...|  1|\n",
      "|10224739|   HY411615|2015-09-04 11:30:00| 008XX N CENTRAL AVE|0870|       THEFT|      POCKET-PICKING|             CTA BUS| false|   false|1511|     015|  29|            25|      06|     1138875|     1904869|2015|02/10/2018 03:50:...|41.895080471|-87.765400451|(41.895080471, -8...|  1|\n",
      "|11646166|   JC213529|2018-09-01 00:01:00|082XX S INGLESIDE...|0810|       THEFT|           OVER $500|           RESIDENCE| false|    true|0631|     006|   8|            44|      06|        null|        null|2018|04/06/2019 04:04:...|        null|         null|                null|  1|\n",
      "|10224740|   HY411595|2015-09-05 12:45:00|   035XX W BARRY AVE|2023|   NARCOTICS|POSS: HEROIN(BRN/...|            SIDEWALK|  true|   false|1412|     014|  35|            21|      18|     1152037|     1920384|2015|02/10/2018 03:50:...|41.937405765|-87.716649687|(41.937405765, -8...|  1|\n",
      "|10224741|   HY411610|2015-09-05 13:00:00| 0000X N LARAMIE AVE|0560|     ASSAULT|              SIMPLE|           APARTMENT| false|    true|1522|     015|  28|            25|     08A|     1141706|     1900086|2015|02/10/2018 03:50:...|41.881903443|-87.755121152|(41.881903443, -8...|  1|\n",
      "+--------+-----------+-------------------+--------------------+----+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+---+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.withColumn('One',lit(1)).show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "rc = rc.drop('IUCR')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-----------+-------------------+--------------------+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "|      ID|Case Number|               Date|               Block|Primary Type|         Description|Location Description|Arrest|Domestic|Beat|District|Ward|Community Area|FBI Code|X Coordinate|Y Coordinate|Year|          Updated On|    Latitude|    Longitude|            Location|\n",
      "+--------+-----------+-------------------+--------------------+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "|10224738|   HY411648|2015-09-05 13:30:00|     043XX S WOOD ST|     BATTERY|DOMESTIC BATTERY ...|           RESIDENCE| false|    true|0924|     009|  12|            61|     08B|     1165074|     1875917|2015|02/10/2018 03:50:...|41.815117282|-87.669999562|(41.815117282, -8...|\n",
      "|10224739|   HY411615|2015-09-04 11:30:00| 008XX N CENTRAL AVE|       THEFT|      POCKET-PICKING|             CTA BUS| false|   false|1511|     015|  29|            25|      06|     1138875|     1904869|2015|02/10/2018 03:50:...|41.895080471|-87.765400451|(41.895080471, -8...|\n",
      "|11646166|   JC213529|2018-09-01 00:01:00|082XX S INGLESIDE...|       THEFT|           OVER $500|           RESIDENCE| false|    true|0631|     006|   8|            44|      06|        null|        null|2018|04/06/2019 04:04:...|        null|         null|                null|\n",
      "|10224740|   HY411595|2015-09-05 12:45:00|   035XX W BARRY AVE|   NARCOTICS|POSS: HEROIN(BRN/...|            SIDEWALK|  true|   false|1412|     014|  35|            21|      18|     1152037|     1920384|2015|02/10/2018 03:50:...|41.937405765|-87.716649687|(41.937405765, -8...|\n",
      "|10224741|   HY411610|2015-09-05 13:00:00| 0000X N LARAMIE AVE|     ASSAULT|              SIMPLE|           APARTMENT| false|    true|1522|     015|  28|            25|     08A|     1141706|     1900086|2015|02/10/2018 03:50:...|41.881903443|-87.755121152|(41.881903443, -8...|\n",
      "+--------+-----------+-------------------+--------------------+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "one_day = spark.read.csv('C:\\\\Users\\\\shubh\\\\Downloads\\\\Crimes.csv',header=True).withColumn('Date',to_timestamp(col('Date'),'MM/dd/yyyy hh:mm:ss a')).filter(col('Date') == lit('2018-11-11'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-----------+-------------------+--------------------+----+---------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "|      ID|Case Number|               Date|               Block|IUCR|   Primary Type|         Description|Location Description|Arrest|Domestic|Beat|District|Ward|Community Area|FBI Code|X Coordinate|Y Coordinate|Year|          Updated On|    Latitude|    Longitude|            Location|\n",
      "+--------+-----------+-------------------+--------------------+----+---------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "|11504097|   JB511773|2018-11-11 00:00:00|003XX S ST LOUIS AVE|1320|CRIMINAL DAMAGE|          TO VEHICLE|              STREET| false|   false|1133|     011|  28|            27|      14|     1153064|     1898239|2018|11/18/2018 04:11:...|41.876617517|-87.713463403|(41.876617517, -8...|\n",
      "|11504126|   JB511829|2018-11-11 00:00:00|    012XX N NOBLE ST|1320|CRIMINAL DAMAGE|          TO VEHICLE|              STREET| false|   false|1433|     014|   2|            24|      14|     1166828|     1908386|2018|11/18/2018 04:11:...|41.904177725|-87.662635226|(41.904177725, -8...|\n",
      "|11504529|   JB512336|2018-11-11 00:00:00|     026XX W 21ST ST|1320|CRIMINAL DAMAGE|          TO VEHICLE|              STREET| false|   false|1023|     010|  24|            30|      14|     1159124|     1889973|2018|11/18/2018 04:11:...|41.853812529| -87.69143988|(41.853812529, -8...|\n",
      "|11504835|   JB512723|2018-11-11 00:00:00|011XX W GARFIELD ...|1305|CRIMINAL DAMAGE| CRIMINAL DEFACEMENT|           RESIDENCE| false|   false|0712|     007|  16|            68|      14|     1169461|     1868186|2018|11/18/2018 04:11:...|41.793808521|-87.654131491|(41.793808521, -8...|\n",
      "|11505581|   JB513620|2018-11-11 00:00:00|  049XX S KEELER AVE|0486|        BATTERY|DOMESTIC BATTERY ...|           RESIDENCE| false|    true|0815|     008|  14|            57|     08B|     1149195|     1871583|2018|11/18/2018 04:11:...|41.803545745|-87.728358627|(41.803545745, -8...|\n",
      "+--------+-----------+-------------------+--------------------+----+---------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "one_day.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "one_day.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#Ran [4] th line again"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-----------+-------------------+--------------------+----+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "|      ID|Case Number|               Date|               Block|IUCR|Primary Type|         Description|Location Description|Arrest|Domestic|Beat|District|Ward|Community Area|FBI Code|X Coordinate|Y Coordinate|Year|          Updated On|    Latitude|    Longitude|            Location|\n",
      "+--------+-----------+-------------------+--------------------+----+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "|10224738|   HY411648|2015-09-05 13:30:00|     043XX S WOOD ST|0486|     BATTERY|DOMESTIC BATTERY ...|           RESIDENCE| false|    true|0924|     009|  12|            61|     08B|     1165074|     1875917|2015|02/10/2018 03:50:...|41.815117282|-87.669999562|(41.815117282, -8...|\n",
      "|10224739|   HY411615|2015-09-04 11:30:00| 008XX N CENTRAL AVE|0870|       THEFT|      POCKET-PICKING|             CTA BUS| false|   false|1511|     015|  29|            25|      06|     1138875|     1904869|2015|02/10/2018 03:50:...|41.895080471|-87.765400451|(41.895080471, -8...|\n",
      "|11646166|   JC213529|2018-09-01 00:01:00|082XX S INGLESIDE...|0810|       THEFT|           OVER $500|           RESIDENCE| false|    true|0631|     006|   8|            44|      06|        null|        null|2018|04/06/2019 04:04:...|        null|         null|                null|\n",
      "|10224740|   HY411595|2015-09-05 12:45:00|   035XX W BARRY AVE|2023|   NARCOTICS|POSS: HEROIN(BRN/...|            SIDEWALK|  true|   false|1412|     014|  35|            21|      18|     1152037|     1920384|2015|02/10/2018 03:50:...|41.937405765|-87.716649687|(41.937405765, -8...|\n",
      "|10224741|   HY411610|2015-09-05 13:00:00| 0000X N LARAMIE AVE|0560|     ASSAULT|              SIMPLE|           APARTMENT| false|    true|1522|     015|  28|            25|     08A|     1141706|     1900086|2015|02/10/2018 03:50:...|41.881903443|-87.755121152|(41.881903443, -8...|\n",
      "+--------+-----------+-------------------+--------------------+----+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.union(one_day).show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-----------+-------------------+--------------------+----+--------------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "|      ID|Case Number|               Date|               Block|IUCR|        Primary Type|         Description|Location Description|Arrest|Domestic|Beat|District|Ward|Community Area|FBI Code|X Coordinate|Y Coordinate|Year|          Updated On|    Latitude|    Longitude|            Location|\n",
      "+--------+-----------+-------------------+--------------------+----+--------------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "|11504835|   JB512723|2018-11-11 00:00:00|011XX W GARFIELD ...|1305|     CRIMINAL DAMAGE| CRIMINAL DEFACEMENT|           RESIDENCE| false|   false|0712|     007|  16|            68|      14|     1169461|     1868186|2018|11/18/2018 04:11:...|41.793808521|-87.654131491|(41.793808521, -8...|\n",
      "|11513303|   JB523990|2018-11-11 00:00:00|  007XX S CICERO AVE|0281|CRIMINAL SEXUAL A...|      NON-AGGRAVATED|              STREET| false|   false|1131|     011|  24|            25|      02|     1144511|     1896107|2018|03/19/2020 03:44:...|41.870932257|-87.744921277|(41.870932257, -8...|\n",
      "|11505581|   JB513620|2018-11-11 00:00:00|  049XX S KEELER AVE|0486|             BATTERY|DOMESTIC BATTERY ...|           RESIDENCE| false|    true|0815|     008|  14|            57|     08B|     1149195|     1871583|2018|11/18/2018 04:11:...|41.803545745|-87.728358627|(41.803545745, -8...|\n",
      "|11504529|   JB512336|2018-11-11 00:00:00|     026XX W 21ST ST|1320|     CRIMINAL DAMAGE|          TO VEHICLE|              STREET| false|   false|1023|     010|  24|            30|      14|     1159124|     1889973|2018|11/18/2018 04:11:...|41.853812529| -87.69143988|(41.853812529, -8...|\n",
      "|11505877|   JB514053|2018-11-11 00:00:00| 040XX S PRAIRIE AVE|0820|               THEFT|      $500 AND UNDER|               OTHER| false|   false|0213|     002|   3|            38|      06|     1178707|     1878242|2018|11/18/2018 04:11:...|41.821197606|-87.619921139|(41.821197606, -8...|\n",
      "+--------+-----------+-------------------+--------------------+----+--------------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.union(one_day).orderBy('Date',ascending=False).show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+------+\n",
      "|       Primary Type| count|\n",
      "+-------------------+------+\n",
      "|              THEFT|708619|\n",
      "|            BATTERY|640815|\n",
      "|    CRIMINAL DAMAGE|401274|\n",
      "|          NARCOTICS|332675|\n",
      "|      OTHER OFFENSE|222873|\n",
      "|            ASSAULT|218260|\n",
      "|           BURGLARY|182161|\n",
      "|MOTOR VEHICLE THEFT|166737|\n",
      "| DECEPTIVE PRACTICE|138591|\n",
      "|            ROBBERY|129675|\n",
      "+-------------------+------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.groupby('Primary Type').count().orderBy('count',ascending=False).show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "#percentage of reported crime that resulted in an arrest"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#Ran [4] th line again"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+\n",
      "|Arrest|\n",
      "+------+\n",
      "| false|\n",
      "|  true|\n",
      "|  null|\n",
      "+------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.select('Arrest').distinct().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- ID: string (nullable = true)\n",
      " |-- Case Number: string (nullable = true)\n",
      " |-- Date: timestamp (nullable = true)\n",
      " |-- Block: string (nullable = true)\n",
      " |-- IUCR: string (nullable = true)\n",
      " |-- Primary Type: string (nullable = true)\n",
      " |-- Description: string (nullable = true)\n",
      " |-- Location Description: string (nullable = true)\n",
      " |-- Arrest: string (nullable = true)\n",
      " |-- Domestic: string (nullable = true)\n",
      " |-- Beat: string (nullable = true)\n",
      " |-- District: string (nullable = true)\n",
      " |-- Ward: string (nullable = true)\n",
      " |-- Community Area: string (nullable = true)\n",
      " |-- FBI Code: string (nullable = true)\n",
      " |-- X Coordinate: string (nullable = true)\n",
      " |-- Y Coordinate: string (nullable = true)\n",
      " |-- Year: string (nullable = true)\n",
      " |-- Updated On: string (nullable = true)\n",
      " |-- Latitude: string (nullable = true)\n",
      " |-- Longitude: string (nullable = true)\n",
      " |-- Location: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.2750435528654029"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rc.filter(col('Arrest')=='true').count()/rc.select('Arrest').count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "#top 3 locations of crime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+------+\n",
      "|Location Description| count|\n",
      "+--------------------+------+\n",
      "|              STREET|936333|\n",
      "|           RESIDENCE|598683|\n",
      "|           APARTMENT|322501|\n",
      "+--------------------+------+\n",
      "only showing top 3 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.groupby('Location Description').count().orderBy('count',ascending=False).show(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "#inbuilt functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Column',\n",
       " 'DataFrame',\n",
       " 'DataType',\n",
       " 'PandasUDFType',\n",
       " 'PythonEvalType',\n",
       " 'SparkContext',\n",
       " 'StringType',\n",
       " 'UserDefinedFunction',\n",
       " '__builtins__',\n",
       " '__cached__',\n",
       " '__doc__',\n",
       " '__file__',\n",
       " '__loader__',\n",
       " '__name__',\n",
       " '__package__',\n",
       " '__spec__',\n",
       " '_create_column_from_literal',\n",
       " '_create_lambda',\n",
       " '_create_udf',\n",
       " '_get_get_jvm_function',\n",
       " '_get_lambda_parameters',\n",
       " '_invoke_binary_math_function',\n",
       " '_invoke_function',\n",
       " '_invoke_function_over_column',\n",
       " '_invoke_higher_order_function',\n",
       " '_options_to_str',\n",
       " '_test',\n",
       " '_to_java_column',\n",
       " '_to_seq',\n",
       " '_unresolved_named_lambda_variable',\n",
       " 'abs',\n",
       " 'acos',\n",
       " 'acosh',\n",
       " 'add_months',\n",
       " 'aggregate',\n",
       " 'approxCountDistinct',\n",
       " 'approx_count_distinct',\n",
       " 'array',\n",
       " 'array_contains',\n",
       " 'array_distinct',\n",
       " 'array_except',\n",
       " 'array_intersect',\n",
       " 'array_join',\n",
       " 'array_max',\n",
       " 'array_min',\n",
       " 'array_position',\n",
       " 'array_remove',\n",
       " 'array_repeat',\n",
       " 'array_sort',\n",
       " 'array_union',\n",
       " 'arrays_overlap',\n",
       " 'arrays_zip',\n",
       " 'asc',\n",
       " 'asc_nulls_first',\n",
       " 'asc_nulls_last',\n",
       " 'ascii',\n",
       " 'asin',\n",
       " 'asinh',\n",
       " 'assert_true',\n",
       " 'atan',\n",
       " 'atan2',\n",
       " 'atanh',\n",
       " 'avg',\n",
       " 'base64',\n",
       " 'bin',\n",
       " 'bitwiseNOT',\n",
       " 'bitwise_not',\n",
       " 'broadcast',\n",
       " 'bround',\n",
       " 'bucket',\n",
       " 'cbrt',\n",
       " 'ceil',\n",
       " 'coalesce',\n",
       " 'col',\n",
       " 'collect_list',\n",
       " 'collect_set',\n",
       " 'column',\n",
       " 'concat',\n",
       " 'concat_ws',\n",
       " 'conv',\n",
       " 'corr',\n",
       " 'cos',\n",
       " 'cosh',\n",
       " 'count',\n",
       " 'countDistinct',\n",
       " 'count_distinct',\n",
       " 'covar_pop',\n",
       " 'covar_samp',\n",
       " 'crc32',\n",
       " 'create_map',\n",
       " 'cume_dist',\n",
       " 'current_date',\n",
       " 'current_timestamp',\n",
       " 'date_add',\n",
       " 'date_format',\n",
       " 'date_sub',\n",
       " 'date_trunc',\n",
       " 'datediff',\n",
       " 'dayofmonth',\n",
       " 'dayofweek',\n",
       " 'dayofyear',\n",
       " 'days',\n",
       " 'decode',\n",
       " 'degrees',\n",
       " 'dense_rank',\n",
       " 'desc',\n",
       " 'desc_nulls_first',\n",
       " 'desc_nulls_last',\n",
       " 'element_at',\n",
       " 'encode',\n",
       " 'exists',\n",
       " 'exp',\n",
       " 'explode',\n",
       " 'explode_outer',\n",
       " 'expm1',\n",
       " 'expr',\n",
       " 'factorial',\n",
       " 'filter',\n",
       " 'first',\n",
       " 'flatten',\n",
       " 'floor',\n",
       " 'forall',\n",
       " 'format_number',\n",
       " 'format_string',\n",
       " 'from_csv',\n",
       " 'from_json',\n",
       " 'from_unixtime',\n",
       " 'from_utc_timestamp',\n",
       " 'functools',\n",
       " 'get_json_object',\n",
       " 'greatest',\n",
       " 'grouping',\n",
       " 'grouping_id',\n",
       " 'hash',\n",
       " 'hex',\n",
       " 'hour',\n",
       " 'hours',\n",
       " 'hypot',\n",
       " 'initcap',\n",
       " 'input_file_name',\n",
       " 'instr',\n",
       " 'isnan',\n",
       " 'isnull',\n",
       " 'json_tuple',\n",
       " 'kurtosis',\n",
       " 'lag',\n",
       " 'last',\n",
       " 'last_day',\n",
       " 'lead',\n",
       " 'least',\n",
       " 'length',\n",
       " 'levenshtein',\n",
       " 'lit',\n",
       " 'locate',\n",
       " 'log',\n",
       " 'log10',\n",
       " 'log1p',\n",
       " 'log2',\n",
       " 'lower',\n",
       " 'lpad',\n",
       " 'ltrim',\n",
       " 'map_concat',\n",
       " 'map_entries',\n",
       " 'map_filter',\n",
       " 'map_from_arrays',\n",
       " 'map_from_entries',\n",
       " 'map_keys',\n",
       " 'map_values',\n",
       " 'map_zip_with',\n",
       " 'max',\n",
       " 'md5',\n",
       " 'mean',\n",
       " 'min',\n",
       " 'minute',\n",
       " 'monotonically_increasing_id',\n",
       " 'month',\n",
       " 'months',\n",
       " 'months_between',\n",
       " 'nanvl',\n",
       " 'next_day',\n",
       " 'nth_value',\n",
       " 'ntile',\n",
       " 'overlay',\n",
       " 'pandas_udf',\n",
       " 'percent_rank',\n",
       " 'percentile_approx',\n",
       " 'posexplode',\n",
       " 'posexplode_outer',\n",
       " 'pow',\n",
       " 'product',\n",
       " 'quarter',\n",
       " 'radians',\n",
       " 'raise_error',\n",
       " 'rand',\n",
       " 'randn',\n",
       " 'rank',\n",
       " 'regexp_extract',\n",
       " 'regexp_replace',\n",
       " 'repeat',\n",
       " 'reverse',\n",
       " 'rint',\n",
       " 'round',\n",
       " 'row_number',\n",
       " 'rpad',\n",
       " 'rtrim',\n",
       " 'schema_of_csv',\n",
       " 'schema_of_json',\n",
       " 'second',\n",
       " 'sentences',\n",
       " 'sequence',\n",
       " 'session_window',\n",
       " 'sha1',\n",
       " 'sha2',\n",
       " 'shiftLeft',\n",
       " 'shiftRight',\n",
       " 'shiftRightUnsigned',\n",
       " 'shiftleft',\n",
       " 'shiftright',\n",
       " 'shiftrightunsigned',\n",
       " 'shuffle',\n",
       " 'signum',\n",
       " 'sin',\n",
       " 'since',\n",
       " 'sinh',\n",
       " 'size',\n",
       " 'skewness',\n",
       " 'slice',\n",
       " 'sort_array',\n",
       " 'soundex',\n",
       " 'spark_partition_id',\n",
       " 'split',\n",
       " 'sqrt',\n",
       " 'stddev',\n",
       " 'stddev_pop',\n",
       " 'stddev_samp',\n",
       " 'struct',\n",
       " 'substring',\n",
       " 'substring_index',\n",
       " 'sum',\n",
       " 'sumDistinct',\n",
       " 'sum_distinct',\n",
       " 'sys',\n",
       " 'tan',\n",
       " 'tanh',\n",
       " 'timestamp_seconds',\n",
       " 'toDegrees',\n",
       " 'toRadians',\n",
       " 'to_csv',\n",
       " 'to_date',\n",
       " 'to_json',\n",
       " 'to_str',\n",
       " 'to_timestamp',\n",
       " 'to_utc_timestamp',\n",
       " 'transform',\n",
       " 'transform_keys',\n",
       " 'transform_values',\n",
       " 'translate',\n",
       " 'trim',\n",
       " 'trunc',\n",
       " 'udf',\n",
       " 'unbase64',\n",
       " 'unhex',\n",
       " 'unix_timestamp',\n",
       " 'upper',\n",
       " 'var_pop',\n",
       " 'var_samp',\n",
       " 'variance',\n",
       " 'warnings',\n",
       " 'weekofyear',\n",
       " 'when',\n",
       " 'window',\n",
       " 'xxhash64',\n",
       " 'year',\n",
       " 'years',\n",
       " 'zip_with']"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dir(functions)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import lower, upper, substring"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on function substring in module pyspark.sql.functions:\n",
      "\n",
      "substring(str, pos, len)\n",
      "    Substring starts at `pos` and is of length `len` when str is String type or\n",
      "    returns the slice of byte array that starts at `pos` in byte and is of length `len`\n",
      "    when str is Binary type.\n",
      "    \n",
      "    .. versionadded:: 1.5.0\n",
      "    \n",
      "    Notes\n",
      "    -----\n",
      "    The position is not zero based, but 1 based index.\n",
      "    \n",
      "    Examples\n",
      "    --------\n",
      "    >>> df = spark.createDataFrame([('abcd',)], ['s',])\n",
      "    >>> df.select(substring(df.s, 1, 2).alias('s')).collect()\n",
      "    [Row(s='ab')]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "help(substring)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- ID: string (nullable = true)\n",
      " |-- Case Number: string (nullable = true)\n",
      " |-- Date: timestamp (nullable = true)\n",
      " |-- Block: string (nullable = true)\n",
      " |-- IUCR: string (nullable = true)\n",
      " |-- Primary Type: string (nullable = true)\n",
      " |-- Description: string (nullable = true)\n",
      " |-- Location Description: string (nullable = true)\n",
      " |-- Arrest: string (nullable = true)\n",
      " |-- Domestic: string (nullable = true)\n",
      " |-- Beat: string (nullable = true)\n",
      " |-- District: string (nullable = true)\n",
      " |-- Ward: string (nullable = true)\n",
      " |-- Community Area: string (nullable = true)\n",
      " |-- FBI Code: string (nullable = true)\n",
      " |-- X Coordinate: string (nullable = true)\n",
      " |-- Y Coordinate: string (nullable = true)\n",
      " |-- Year: string (nullable = true)\n",
      " |-- Updated On: string (nullable = true)\n",
      " |-- Latitude: string (nullable = true)\n",
      " |-- Longitude: string (nullable = true)\n",
      " |-- Location: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+-----------------------------+\n",
      "|lower(Primary Type)|upper(primary Type)|substring(Primary Type, 1, 4)|\n",
      "+-------------------+-------------------+-----------------------------+\n",
      "|            battery|            BATTERY|                         BATT|\n",
      "|              theft|              THEFT|                         THEF|\n",
      "|              theft|              THEFT|                         THEF|\n",
      "|          narcotics|          NARCOTICS|                         NARC|\n",
      "|            assault|            ASSAULT|                         ASSA|\n",
      "+-------------------+-------------------+-----------------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.select(lower(col('Primary Type')),upper(col('primary Type')),substring(col('Primary Type'),1,4)).show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import min, max"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+\n",
      "|          min(Date)|          max(Date)|\n",
      "+-------------------+-------------------+\n",
      "|2001-01-01 00:00:00|2018-11-11 00:00:00|\n",
      "+-------------------+-------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.select(min(col('Date')),max(col('Date'))).show(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import date_add, date_sub"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on function date_add in module pyspark.sql.functions:\n",
      "\n",
      "date_add(start, days)\n",
      "    Returns the date that is `days` days after `start`\n",
      "    \n",
      "    .. versionadded:: 1.5.0\n",
      "    \n",
      "    Examples\n",
      "    --------\n",
      "    >>> df = spark.createDataFrame([('2015-04-08',)], ['dt'])\n",
      "    >>> df.select(date_add(df.dt, 1).alias('next_date')).collect()\n",
      "    [Row(next_date=datetime.date(2015, 4, 9))]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "help(date_add)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3 days before oldest date and 3 days after newest date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------------+----------------------+\n",
      "|date_sub(min(Date), 3)|date_add(max(Date), 3)|\n",
      "+----------------------+----------------------+\n",
      "|            2000-12-29|            2018-11-14|\n",
      "+----------------------+----------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.select(date_sub(min(col('Date')),3),date_add(max(col('Date')),3)).show(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "#date functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import to_date, to_timestamp, lit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+\n",
      "|                May|\n",
      "+-------------------+\n",
      "|2022-05-27 13:44:00|\n",
      "+-------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df = spark.createDataFrame([('2022-05-27 13:44:00',)],['May'])\n",
    "df.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------------------------+--------------------------------------+\n",
      "|to_date(May, yyyy-MM-dd HH:mm:ss)|to_timestamp(May, yyyy-MM-dd HH:mm:ss)|\n",
      "+---------------------------------+--------------------------------------+\n",
      "|                       2022-05-27|                   2022-05-27 13:44:00|\n",
      "+---------------------------------+--------------------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(to_date(col('May'),'yyyy-MM-dd HH:mm:ss'),to_timestamp(col('May'),'yyyy-MM-dd HH:mm:ss')).show(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = spark.createDataFrame([('27/May/2022 13:55:00',)],['May'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------------------------+---------------------------------------+\n",
      "|to_date(May, dd/MMM/yyyy HH:mm:ss)|to_timestamp(May, dd/MMM/yyyy HH:mm:ss)|\n",
      "+----------------------------------+---------------------------------------+\n",
      "|                        2022-05-27|                    2022-05-27 13:55:00|\n",
      "+----------------------------------+---------------------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(to_date(col('May'),'dd/MMM/yyyy HH:mm:ss'),to_timestamp(col('May'),'dd/MMM/yyyy HH:mm:ss')).show(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = spark.createDataFrame([('05/27/2022 01:58:00 PM',)],['May'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------------+\n",
      "|May                   |\n",
      "+----------------------+\n",
      "|05/27/2022 01:58:00 PM|\n",
      "+----------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show(1,truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------------------------+----------------------------------------+\n",
      "|to_date(May, MM/dd/yyyy hh:mm:ss a)|to_timestamp(May, MM/dd/yyyy hh:mm:ss a)|\n",
      "+-----------------------------------+----------------------------------------+\n",
      "|                         2022-05-27|                     2022-05-27 13:58:00|\n",
      "+-----------------------------------+----------------------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(to_date(col('May'),'MM/dd/yyyy hh:mm:ss a'),to_timestamp(col('May'),'MM/dd/yyyy hh:mm:ss a')).show(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-----------+----------------------+---------------------+----+------------+-----------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+----------------------+------------+-------------+-----------------------------+\n",
      "|ID      |Case Number|Date                  |Block                |IUCR|Primary Type|Description            |Location Description|Arrest|Domestic|Beat|District|Ward|Community Area|FBI Code|X Coordinate|Y Coordinate|Year|Updated On            |Latitude    |Longitude    |Location                     |\n",
      "+--------+-----------+----------------------+---------------------+----+------------+-----------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+----------------------+------------+-------------+-----------------------------+\n",
      "|10224738|HY411648   |09/05/2015 01:30:00 PM|043XX S WOOD ST      |0486|BATTERY     |DOMESTIC BATTERY SIMPLE|RESIDENCE           |false |true    |0924|009     |12  |61            |08B     |1165074     |1875917     |2015|02/10/2018 03:50:01 PM|41.815117282|-87.669999562|(41.815117282, -87.669999562)|\n",
      "|10224739|HY411615   |09/04/2015 11:30:00 AM|008XX N CENTRAL AVE  |0870|THEFT       |POCKET-PICKING         |CTA BUS             |false |false   |1511|015     |29  |25            |06      |1138875     |1904869     |2015|02/10/2018 03:50:01 PM|41.895080471|-87.765400451|(41.895080471, -87.765400451)|\n",
      "|11646166|JC213529   |09/01/2018 12:01:00 AM|082XX S INGLESIDE AVE|0810|THEFT       |OVER $500              |RESIDENCE           |false |true    |0631|006     |8   |44            |06      |null        |null        |2018|04/06/2019 04:04:43 PM|null        |null         |null                         |\n",
      "|10224740|HY411595   |09/05/2015 12:45:00 PM|035XX W BARRY AVE    |2023|NARCOTICS   |POSS: HEROIN(BRN/TAN)  |SIDEWALK            |true  |false   |1412|014     |35  |21            |18      |1152037     |1920384     |2015|02/10/2018 03:50:01 PM|41.937405765|-87.716649687|(41.937405765, -87.716649687)|\n",
      "|10224741|HY411610   |09/05/2015 01:00:00 PM|0000X N LARAMIE AVE  |0560|ASSAULT     |SIMPLE                 |APARTMENT           |false |true    |1522|015     |28  |25            |08A     |1141706     |1900086     |2015|02/10/2018 03:50:01 PM|41.881903443|-87.755121152|(41.881903443, -87.755121152)|\n",
      "+--------+-----------+----------------------+---------------------+----+------------+-----------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+----------------------+------------+-------------+-----------------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "nrc = spark.read.csv('C:\\\\Users\\\\shubh\\\\Downloads\\\\Crimes.csv',header=True)\n",
    "nrc.show(5, truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------+-------------+-------------------+-------+-----+-----+--------------------+------------+------------+------------+------------+------------+-----------+------------+--------------------+\n",
      "|    DISTRICT|DISTRICT NAME|            ADDRESS|   CITY|STATE|  ZIP|             WEBSITE|       PHONE|         FAX|         TTY|X COORDINATE|Y COORDINATE|   LATITUDE|   LONGITUDE|            LOCATION|\n",
      "+------------+-------------+-------------------+-------+-----+-----+--------------------+------------+------------+------------+------------+------------+-----------+------------+--------------------+\n",
      "|Headquarters| Headquarters|3510 S Michigan Ave|Chicago|   IL|60653|http://home.chica...|        null|        null|        null| 1177731.401| 1881697.404|41.83070169|-87.62339535|(41.8307016873, -...|\n",
      "|          18|   Near North| 1160 N Larrabee St|Chicago|   IL|60610|http://home.chica...|312-742-5870|312-742-5771|312-742-5773| 1172080.029| 1908086.527|41.90324165|-87.64335214|(41.9032416531, -...|\n",
      "|          19|    Town Hall|   850 W Addison St|Chicago|   IL|60613|http://home.chica...|312-744-8320|312-744-4481|312-744-8011| 1169730.744| 1924160.317|41.94740046|-87.65151202|(41.9474004564, -...|\n",
      "|          20|      Lincoln| 5400 N Lincoln Ave|Chicago|   IL|60625|http://home.chica...|312-742-8714|312-742-8803|312-742-8841| 1158399.146| 1935788.826|41.97954951|-87.69284451|(41.9795495131, -...|\n",
      "|          22|  Morgan Park|1900 W Monterey Ave|Chicago|   IL|60643|http://home.chica...|312-745-0710|312-745-0814|312-745-0569| 1165825.476| 1830851.333|41.69143478|-87.66852039|(41.6914347795, -...|\n",
      "+------------+-------------+-------------------+-------+-----+-----+--------------------+------------+------------+------------+------------+------------+-----------+------------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "ps = spark.read.csv('C:\\\\Users\\\\shubh\\\\Downloads\\\\Police_Stations.csv',header=True)\n",
    "ps.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[ID: string, Case Number: string, Date: timestamp, Block: string, IUCR: string, Primary Type: string, Description: string, Location Description: string, Arrest: string, Domestic: string, Beat: string, District: string, Ward: string, Community Area: string, FBI Code: string, X Coordinate: string, Y Coordinate: string, Year: string, Updated On: string, Latitude: string, Longitude: string, Location: string]"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rc.cache()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3433758"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rc.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+\n",
      "|            DISTRICT|\n",
      "+--------------------+\n",
      "|                   7|\n",
      "|                  15|\n",
      "|                  11|\n",
      "|                   3|\n",
      "|                   8|\n",
      "|                  22|\n",
      "|                  16|\n",
      "|                   5|\n",
      "|                  18|\n",
      "|                  17|\n",
      "|                   6|\n",
      "|                  19|\n",
      "|                  25|\n",
      "|        Headquarters|\n",
      "|                  24|\n",
      "|                   9|\n",
      "|                   1|\n",
      "|                  20|\n",
      "|                  10|\n",
      "|                   4|\n",
      "|                  12|\n",
      "|                  14|\n",
      "|                   2|\n",
      "|\",Chicago,IL,6060...|\n",
      "+--------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "ps.select(col('DISTRICT')).distinct().show(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+\n",
      "|District|\n",
      "+--------+\n",
      "|     009|\n",
      "|     012|\n",
      "|     024|\n",
      "|     031|\n",
      "|     015|\n",
      "|     006|\n",
      "|     019|\n",
      "|     020|\n",
      "|     011|\n",
      "|     025|\n",
      "|     005|\n",
      "|     003|\n",
      "|     016|\n",
      "|     018|\n",
      "|     008|\n",
      "|     022|\n",
      "|     001|\n",
      "|     014|\n",
      "|     010|\n",
      "|     004|\n",
      "|     017|\n",
      "|     007|\n",
      "|     002|\n",
      "|    null|\n",
      "|     021|\n",
      "+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.select(col('District')).distinct().show(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import lpad"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on function lpad in module pyspark.sql.functions:\n",
      "\n",
      "lpad(col, len, pad)\n",
      "    Left-pad the string column to width `len` with `pad`.\n",
      "    \n",
      "    .. versionadded:: 1.5.0\n",
      "    \n",
      "    Examples\n",
      "    --------\n",
      "    >>> df = spark.createDataFrame([('abcd',)], ['s',])\n",
      "    >>> df.select(lpad(df.s, 6, '#').alias('s')).collect()\n",
      "    [Row(s='##abcd')]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "help(lpad)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+\n",
      "|lpad(DISTRICT, 3, 0)|\n",
      "+--------------------+\n",
      "|                 Hea|\n",
      "|                 018|\n",
      "|                 019|\n",
      "|                 020|\n",
      "|                 022|\n",
      "|                 024|\n",
      "|                 025|\n",
      "|                 001|\n",
      "|                 002|\n",
      "|                 003|\n",
      "|                 004|\n",
      "|                 005|\n",
      "|                 006|\n",
      "|                 007|\n",
      "|                 008|\n",
      "|                 009|\n",
      "|                 010|\n",
      "|                 011|\n",
      "|                 012|\n",
      "|                 \",C|\n",
      "+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "ps.select(lpad(col('DISTRICT'),3,'0')).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "ps = ps.withColumn('Format_District', lpad(col('DISTRICT'),3,'0'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------+-------------+-------------------+-------+-----+-----+--------------------+------------+------------+------------+------------+------------+-----------+------------+--------------------+---------------+---------------+\n",
      "|    DISTRICT|DISTRICT NAME|            ADDRESS|   CITY|STATE|  ZIP|             WEBSITE|       PHONE|         FAX|         TTY|X COORDINATE|Y COORDINATE|   LATITUDE|   LONGITUDE|            LOCATION|Format District|Format_District|\n",
      "+------------+-------------+-------------------+-------+-----+-----+--------------------+------------+------------+------------+------------+------------+-----------+------------+--------------------+---------------+---------------+\n",
      "|Headquarters| Headquarters|3510 S Michigan Ave|Chicago|   IL|60653|http://home.chica...|        null|        null|        null| 1177731.401| 1881697.404|41.83070169|-87.62339535|(41.8307016873, -...|            Hea|            Hea|\n",
      "|          18|   Near North| 1160 N Larrabee St|Chicago|   IL|60610|http://home.chica...|312-742-5870|312-742-5771|312-742-5773| 1172080.029| 1908086.527|41.90324165|-87.64335214|(41.9032416531, -...|            018|            018|\n",
      "|          19|    Town Hall|   850 W Addison St|Chicago|   IL|60613|http://home.chica...|312-744-8320|312-744-4481|312-744-8011| 1169730.744| 1924160.317|41.94740046|-87.65151202|(41.9474004564, -...|            019|            019|\n",
      "|          20|      Lincoln| 5400 N Lincoln Ave|Chicago|   IL|60625|http://home.chica...|312-742-8714|312-742-8803|312-742-8841| 1158399.146| 1935788.826|41.97954951|-87.69284451|(41.9795495131, -...|            020|            020|\n",
      "|          22|  Morgan Park|1900 W Monterey Ave|Chicago|   IL|60643|http://home.chica...|312-745-0710|312-745-0814|312-745-0569| 1165825.476| 1830851.333|41.69143478|-87.66852039|(41.6914347795, -...|            022|            022|\n",
      "+------------+-------------+-------------------+-------+-----+-----+--------------------+------------+------------+------------+------------+------------+-----------+------------+--------------------+---------------+---------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "ps.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(Format_District='Hea'),\n",
       " Row(Format_District='018'),\n",
       " Row(Format_District='019'),\n",
       " Row(Format_District='020'),\n",
       " Row(Format_District='022'),\n",
       " Row(Format_District='024'),\n",
       " Row(Format_District='025'),\n",
       " Row(Format_District='001'),\n",
       " Row(Format_District='002'),\n",
       " Row(Format_District='003'),\n",
       " Row(Format_District='004'),\n",
       " Row(Format_District='005'),\n",
       " Row(Format_District='006'),\n",
       " Row(Format_District='007'),\n",
       " Row(Format_District='008'),\n",
       " Row(Format_District='009'),\n",
       " Row(Format_District='010'),\n",
       " Row(Format_District='011'),\n",
       " Row(Format_District='012'),\n",
       " Row(Format_District='\",C'),\n",
       " Row(Format_District='014'),\n",
       " Row(Format_District='015'),\n",
       " Row(Format_District='016'),\n",
       " Row(Format_District='017')]"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ps.select('Format_District').collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [],
   "source": [
    "ps = ps.drop('Format District')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-----------+-------------------+--------------------+----+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+--------+-------------+--------------------+-------+-----+-----+--------------------+------------+------------+------------+------------+------------+-----------+------------+--------------------+---------------+\n",
      "|      ID|Case Number|               Date|               Block|IUCR|Primary Type|         Description|Location Description|Arrest|Domestic|Beat|District|Ward|Community Area|FBI Code|X Coordinate|Y Coordinate|Year|          Updated On|    Latitude|    Longitude|            Location|DISTRICT|DISTRICT NAME|             ADDRESS|   CITY|STATE|  ZIP|             WEBSITE|       PHONE|         FAX|         TTY|X COORDINATE|Y COORDINATE|   LATITUDE|   LONGITUDE|            LOCATION|Format_District|\n",
      "+--------+-----------+-------------------+--------------------+----+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+--------+-------------+--------------------+-------+-----+-----+--------------------+------------+------------+------------+------------+------------+-----------+------------+--------------------+---------------+\n",
      "|10224738|   HY411648|2015-09-05 13:30:00|     043XX S WOOD ST|0486|     BATTERY|DOMESTIC BATTERY ...|           RESIDENCE| false|    true|0924|     009|  12|            61|     08B|     1165074|     1875917|2015|02/10/2018 03:50:...|41.815117282|-87.669999562|(41.815117282, -8...|       9|      Deering|   3120 S Halsted St|Chicago|   IL|60608|http://home.chica...|312-747-8227|312-747-5329|312-747-9172|  1171440.24| 1884085.224|41.83739443|-87.64640771|(41.8373944311, -...|            009|\n",
      "|10224739|   HY411615|2015-09-04 11:30:00| 008XX N CENTRAL AVE|0870|       THEFT|      POCKET-PICKING|             CTA BUS| false|   false|1511|     015|  29|            25|      06|     1138875|     1904869|2015|02/10/2018 03:50:...|41.895080471|-87.765400451|(41.895080471, -8...|      15|       Austin|   5701 W Madison St|Chicago|   IL|60644|http://home.chica...|312-743-1440|312-743-1366|312-743-1485| 1138148.815| 1899399.078|41.88008346|-87.76819989|(41.8800834614, -...|            015|\n",
      "|11646166|   JC213529|2018-09-01 00:01:00|082XX S INGLESIDE...|0810|       THEFT|           OVER $500|           RESIDENCE| false|    true|0631|     006|   8|            44|      06|        null|        null|2018|04/06/2019 04:04:...|        null|         null|                null|       6|      Gresham|   7808 S Halsted St|Chicago|   IL|60620|http://home.chica...|312-745-3617|312-745-3649|312-745-3639| 1172283.013| 1853022.646|41.75213684|-87.64422891|(41.7521368378, -...|            006|\n",
      "|10224740|   HY411595|2015-09-05 12:45:00|   035XX W BARRY AVE|2023|   NARCOTICS|POSS: HEROIN(BRN/...|            SIDEWALK|  true|   false|1412|     014|  35|            21|      18|     1152037|     1920384|2015|02/10/2018 03:50:...|41.937405765|-87.716649687|(41.937405765, -8...|      14|  Shakespeare|2150 N California...|Chicago|   IL|60647|http://home.chica...|312-744-8250|312-744-2422|312-744-8260| 1157304.426| 1914481.521|41.92110332|-87.69745182|(41.9211033246, -...|            014|\n",
      "|10224741|   HY411610|2015-09-05 13:00:00| 0000X N LARAMIE AVE|0560|     ASSAULT|              SIMPLE|           APARTMENT| false|    true|1522|     015|  28|            25|     08A|     1141706|     1900086|2015|02/10/2018 03:50:...|41.881903443|-87.755121152|(41.881903443, -8...|      15|       Austin|   5701 W Madison St|Chicago|   IL|60644|http://home.chica...|312-743-1440|312-743-1366|312-743-1485| 1138148.815| 1899399.078|41.88008346|-87.76819989|(41.8800834614, -...|            015|\n",
      "+--------+-----------+-------------------+--------------------+----+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+--------+-------------+--------------------+-------+-----+-----+--------------------+------------+------------+------------+------------+------------+-----------+------------+--------------------+---------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.join(ps, rc.District == ps.Format_District,'left_outer').show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['DISTRICT',\n",
       " 'DISTRICT NAME',\n",
       " 'ADDRESS',\n",
       " 'CITY',\n",
       " 'STATE',\n",
       " 'ZIP',\n",
       " 'WEBSITE',\n",
       " 'PHONE',\n",
       " 'FAX',\n",
       " 'TTY',\n",
       " 'X COORDINATE',\n",
       " 'Y COORDINATE',\n",
       " 'LATITUDE',\n",
       " 'LONGITUDE',\n",
       " 'LOCATION',\n",
       " 'Format_District']"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ps.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-----------+-------------------+--------------------+----+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+----+--------------------+--------+-------------+\n",
      "|      ID|Case Number|               Date|               Block|IUCR|Primary Type|         Description|Location Description|Arrest|Domestic|Beat|District|Ward|Community Area|FBI Code|Year|          Updated On|DISTRICT|DISTRICT NAME|\n",
      "+--------+-----------+-------------------+--------------------+----+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+----+--------------------+--------+-------------+\n",
      "|10224738|   HY411648|2015-09-05 13:30:00|     043XX S WOOD ST|0486|     BATTERY|DOMESTIC BATTERY ...|           RESIDENCE| false|    true|0924|     009|  12|            61|     08B|2015|02/10/2018 03:50:...|       9|      Deering|\n",
      "|10224739|   HY411615|2015-09-04 11:30:00| 008XX N CENTRAL AVE|0870|       THEFT|      POCKET-PICKING|             CTA BUS| false|   false|1511|     015|  29|            25|      06|2015|02/10/2018 03:50:...|      15|       Austin|\n",
      "|11646166|   JC213529|2018-09-01 00:01:00|082XX S INGLESIDE...|0810|       THEFT|           OVER $500|           RESIDENCE| false|    true|0631|     006|   8|            44|      06|2018|04/06/2019 04:04:...|       6|      Gresham|\n",
      "|10224740|   HY411595|2015-09-05 12:45:00|   035XX W BARRY AVE|2023|   NARCOTICS|POSS: HEROIN(BRN/...|            SIDEWALK|  true|   false|1412|     014|  35|            21|      18|2015|02/10/2018 03:50:...|      14|  Shakespeare|\n",
      "|10224741|   HY411610|2015-09-05 13:00:00| 0000X N LARAMIE AVE|0560|     ASSAULT|              SIMPLE|           APARTMENT| false|    true|1522|     015|  28|            25|     08A|2015|02/10/2018 03:50:...|      15|       Austin|\n",
      "+--------+-----------+-------------------+--------------------+----+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+----+--------------------+--------+-------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.join(ps, rc.District == ps.Format_District,'left_outer').drop(\n",
    " 'ADDRESS',\n",
    " 'CITY',\n",
    " 'STATE',\n",
    " 'ZIP',\n",
    " 'WEBSITE',\n",
    " 'PHONE',\n",
    " 'FAX',\n",
    " 'TTY',\n",
    " 'X COORDINATE',\n",
    " 'Y COORDINATE',\n",
    " 'LATITUDE',\n",
    " 'LONGITUDE',\n",
    " 'LOCATION',\n",
    " 'Format_District').show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "37"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rc.select(col('Primary Type')).distinct().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------------------------+\n",
      "|Primary Type                     |\n",
      "+---------------------------------+\n",
      "|null                             |\n",
      "|ARSON                            |\n",
      "|ASSAULT                          |\n",
      "|BATTERY                          |\n",
      "|BURGLARY                         |\n",
      "|CONCEALED CARRY LICENSE VIOLATION|\n",
      "|CRIM SEXUAL ASSAULT              |\n",
      "|CRIMINAL DAMAGE                  |\n",
      "|CRIMINAL SEXUAL ASSAULT          |\n",
      "|CRIMINAL TRESPASS                |\n",
      "|DECEPTIVE PRACTICE               |\n",
      "|DOMESTIC VIOLENCE                |\n",
      "|GAMBLING                         |\n",
      "|HOMICIDE                         |\n",
      "|HUMAN TRAFFICKING                |\n",
      "|INTERFERENCE WITH PUBLIC OFFICER |\n",
      "|INTIMIDATION                     |\n",
      "|KIDNAPPING                       |\n",
      "|LIQUOR LAW VIOLATION             |\n",
      "|MOTOR VEHICLE THEFT              |\n",
      "|NARCOTICS                        |\n",
      "|NON - CRIMINAL                   |\n",
      "|NON-CRIMINAL                     |\n",
      "|NON-CRIMINAL (SUBJECT SPECIFIED) |\n",
      "|OBSCENITY                        |\n",
      "|OFFENSE INVOLVING CHILDREN       |\n",
      "|OTHER NARCOTIC VIOLATION         |\n",
      "|OTHER OFFENSE                    |\n",
      "|PROSTITUTION                     |\n",
      "|PUBLIC INDECENCY                 |\n",
      "|PUBLIC PEACE VIOLATION           |\n",
      "|RITUALISM                        |\n",
      "|ROBBERY                          |\n",
      "|SEX OFFENSE                      |\n",
      "|STALKING                         |\n",
      "|THEFT                            |\n",
      "|WEAPONS VIOLATION                |\n",
      "+---------------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.select(col('Primary Type')).distinct().orderBy(col('Primary Type')).show(37, truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [],
   "source": [
    "nc = rc.filter((col('Primary Type') == 'NON - CRIMINAL') | (col('Primary Type') == 'NON-CRIMINAL') | (col('Primary Type') == 'NON-CRIMINAL (SUBJECT SPECIFIED)'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-----------+-------------------+--------------------+----+--------------+-----------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "|      ID|Case Number|               Date|               Block|IUCR|  Primary Type|      Description|Location Description|Arrest|Domestic|Beat|District|Ward|Community Area|FBI Code|X Coordinate|Y Coordinate|Year|          Updated On|    Latitude|    Longitude|            Location|\n",
      "+--------+-----------+-------------------+--------------------+----+--------------+-----------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "|10233370|   HY421530|2015-09-13 10:45:00|  010XX S MENARD AVE|5114|NON - CRIMINAL|FOID - REVOCATION|           RESIDENCE|  true|   false|1513|     015|  29|            25|      26|     1137869|     1895239|2015|02/10/2018 03:50:...|41.868672696|-87.769327726|(41.868672696, -8...|\n",
      "|10241515|   HY429346|2015-09-19 07:45:00|051XX N MILWAUKEE...|5114|NON - CRIMINAL|FOID - REVOCATION|POLICE FACILITY/V...|  true|   false|1623|     016|  45|            11|      26|     1138424|     1933673|2015|02/10/2018 03:50:...|41.974129858|-87.766357256|(41.974129858, -8...|\n",
      "|10277370|   HY465290|2015-10-16 20:00:00|    100XX W OHARE ST|5093|  NON-CRIMINAL|    LOST PASSPORT|            AIRCRAFT| false|   false|1651|     016|  41|            76|      26|     1100658|     1934241|2015|02/10/2018 03:50:...|41.976290414|-87.905227221|(41.976290414, -8...|\n",
      "|10280097|   HY468448|2015-10-19 18:50:00|   033XX W OGDEN AVE|5114|NON - CRIMINAL|FOID - REVOCATION|POLICE FACILITY/V...| false|   false|1024|     010|  24|            29|      26|     1154489|     1891024|2015|02/10/2018 03:50:...|41.856790413|-87.708424071|(41.856790413, -8...|\n",
      "|10290745|   HY478502|2015-10-26 16:00:00|004XX W DIVERSEY ...|5093|  NON-CRIMINAL|    LOST PASSPORT|            SIDEWALK| false|   false|1934|     019|  44|             6|      26|     1172533|     1918887|2015|02/10/2018 03:50:...|41.932868651|-87.641368196|(41.932868651, -8...|\n",
      "|10296800|   HY485235|2015-09-30 17:00:00|008XX N MAPLEWOOD...|5093|  NON-CRIMINAL|    LOST PASSPORT|            SIDEWALK| false|   false|1211|     012|   1|            24|      26|     1159246|     1905582|2015|02/10/2018 03:50:...|41.896642608|-87.690563055|(41.896642608, -8...|\n",
      "|10300617|   HY489191|2015-11-03 15:00:00|  031XX S HALSTED ST|5114|NON - CRIMINAL|FOID - REVOCATION|           RESIDENCE| false|   false|0913|     009|  11|            60|      26|     1171479|     1884120|2015|02/10/2018 03:50:...|41.837489008|-87.646264459|(41.837489008, -8...|\n",
      "|10325994|   HY516457|2015-11-26 23:30:00|  001XX W HUBBARD ST|5093|  NON-CRIMINAL|    LOST PASSPORT|          RESTAURANT| false|   false|1831|     018|  42|             8|      26|     1175301|     1903292|2015|02/10/2018 03:50:...|41.890013524|-87.631664996|(41.890013524, -8...|\n",
      "|10157940|   HY347539|2015-07-08 10:30:00| 016XX W CHICAGO AVE|5093|  NON-CRIMINAL|    LOST PASSPORT|              STREET| false|   false|1215|     012|   1|            24|      26|     1165158|     1905410|2015|02/10/2018 03:50:...|41.896047034|-87.668854221|(41.896047034, -8...|\n",
      "|10181019|   HY369010|2015-08-01 10:15:00|  079XX S KEDZIE AVE|5114|NON - CRIMINAL|FOID - REVOCATION|           RESIDENCE| false|   false|0835|     008|  18|            70|      26|     1156427|     1851731|2015|02/10/2018 03:50:...|41.748926157| -87.70236927|(41.748926157, -8...|\n",
      "+--------+-----------+-------------------+--------------------+----+--------------+-----------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "nc.show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------------------------------+-----+\n",
      "|Description                                |count|\n",
      "+-------------------------------------------+-----+\n",
      "|LOST PASSPORT                              |81   |\n",
      "|FOID - REVOCATION                          |50   |\n",
      "|NOTIFICATION OF CIVIL NO CONTACT ORDER     |6    |\n",
      "|NOTIFICATION OF STALKING - NO CONTACT ORDER|6    |\n",
      "|CONCEALED CARRY LICENSE REVOCATION         |4    |\n",
      "|FOUND PASSPORT                             |2    |\n",
      "|GUN OFFENDER NOTIFICATION-NO CONTACT       |1    |\n",
      "+-------------------------------------------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "nc.groupBy(col('Description')).count().orderBy('count',ascending=False).show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import dayofweek"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on function dayofweek in module pyspark.sql.functions:\n",
      "\n",
      "dayofweek(col)\n",
      "    Extract the day of the week of a given date as integer.\n",
      "    \n",
      "    .. versionadded:: 2.3.0\n",
      "    \n",
      "    Examples\n",
      "    --------\n",
      "    >>> df = spark.createDataFrame([('2015-04-08',)], ['dt'])\n",
      "    >>> df.select(dayofweek('dt').alias('day')).collect()\n",
      "    [Row(day=4)]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "help(dayofweek)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+---------------+\n",
      "|               Date|dayofweek(Date)|\n",
      "+-------------------+---------------+\n",
      "|2015-09-05 13:30:00|              7|\n",
      "|2015-09-04 11:30:00|              6|\n",
      "|2018-09-01 00:01:00|              7|\n",
      "|2015-09-05 12:45:00|              7|\n",
      "|2015-09-05 13:00:00|              7|\n",
      "+-------------------+---------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.select(col('Date'),dayofweek(col('Date'))).show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import date_format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on function date_format in module pyspark.sql.functions:\n",
      "\n",
      "date_format(date, format)\n",
      "    Converts a date/timestamp/string to a value of string in the format specified by the date\n",
      "    format given by the second argument.\n",
      "    \n",
      "    A pattern could be for instance `dd.MM.yyyy` and could return a string like '18.03.1993'. All\n",
      "    pattern letters of `datetime pattern`_. can be used.\n",
      "    \n",
      "    .. _datetime pattern: https://spark.apache.org/docs/latest/sql-ref-datetime-pattern.html\n",
      "    \n",
      "    .. versionadded:: 1.5.0\n",
      "    \n",
      "    Notes\n",
      "    -----\n",
      "    Whenever possible, use specialized functions like `year`.\n",
      "    \n",
      "    Examples\n",
      "    --------\n",
      "    >>> df = spark.createDataFrame([('2015-04-08',)], ['dt'])\n",
      "    >>> df.select(date_format('dt', 'MM/dd/yyy').alias('date')).collect()\n",
      "    [Row(date='04/08/2015')]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "help(date_format)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+---------------+--------------------+\n",
      "|               Date|dayofweek(Date)|date_format(Date, E)|\n",
      "+-------------------+---------------+--------------------+\n",
      "|2015-09-05 13:30:00|              7|                 Sat|\n",
      "|2015-09-04 11:30:00|              6|                 Fri|\n",
      "|2018-09-01 00:01:00|              7|                 Sat|\n",
      "|2015-09-05 12:45:00|              7|                 Sat|\n",
      "|2015-09-05 13:00:00|              7|                 Sat|\n",
      "+-------------------+---------------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.select(col('Date'),dayofweek(col('Date')),date_format(col('Date'),'E')).show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+------+\n",
      "|date_format(Date, E)| count|\n",
      "+--------------------+------+\n",
      "|                 Fri|514882|\n",
      "|                 Wed|494583|\n",
      "|                 Sat|491411|\n",
      "|                 Thu|491347|\n",
      "|                 Tue|491341|\n",
      "|                 Mon|486633|\n",
      "|                 Sun|463561|\n",
      "+--------------------+------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rc.groupBy(date_format(col('Date'),'E')).count().orderBy('count',ascending=False).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(date_format(Date, E)='Sun', count=463561),\n",
       " Row(date_format(Date, E)='Mon', count=486633),\n",
       " Row(date_format(Date, E)='Thu', count=491347),\n",
       " Row(date_format(Date, E)='Sat', count=491411),\n",
       " Row(date_format(Date, E)='Wed', count=494583),\n",
       " Row(date_format(Date, E)='Fri', count=514882),\n",
       " Row(date_format(Date, E)='Tue', count=491341)]"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rc.groupBy(date_format(col('Date'),'E')).count().collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [],
   "source": [
    "dow = [x[0] for x in rc.groupBy(date_format(col('Date'),'E')).count().collect()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Sun', 'Mon', 'Thu', 'Sat', 'Wed', 'Fri', 'Tue']"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [],
   "source": [
    "cnt = [x[1] for x in rc.groupBy(date_format(col('Date'),'E')).count().collect()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[463561, 486633, 491347, 491411, 494583, 514882, 491341]"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cnt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [],
   "source": [
    "cp = pd.DataFrame({'Day_of_week':dow,'Count':cnt})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "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>Day_of_week</th>\n",
       "      <th>Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Sun</td>\n",
       "      <td>463561</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mon</td>\n",
       "      <td>486633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Thu</td>\n",
       "      <td>491347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sat</td>\n",
       "      <td>491411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Wed</td>\n",
       "      <td>494583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Fri</td>\n",
       "      <td>514882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Tue</td>\n",
       "      <td>491341</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Day_of_week   Count\n",
       "0         Sun  463561\n",
       "1         Mon  486633\n",
       "2         Thu  491347\n",
       "3         Sat  491411\n",
       "4         Wed  494583\n",
       "5         Fri  514882\n",
       "6         Tue  491341"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Text(0.5, 1.0, 'No of reported crimes per day of week from 2001 to 2018')"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAZ8AAAEiCAYAAAAmtt8/AAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuNCwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8QVMy6AAAACXBIWXMAAAsTAAALEwEAmpwYAAAuUklEQVR4nO3debwU1Zn/8c9XMEhQEBCJgBH3jLhFcRn1l0UdNIlR4xbUKIlmiBlNzGiS0WRmMBpnjCZxJotrNKhZlJgQmUyMYYy4L6BBAVfiBoIKYhCJouDz++OclqLpe2/dC9197+X7fr36dbtOVZ16qm51P3WqTlcpIjAzM2uk9ZodgJmZrXucfMzMrOGcfMzMrOGcfMzMrOGcfMzMrOGcfMzMrOGcfOpIUm9J/yNpsaRfNTuetU3ScEkhqedaqOt4SX9cG3F1FZLOkfSzJiz325IWSnqx0cvOyx8v6dslpx0s6Q5JSyR9r96xWeOsc8lH0rOSXpLUp1D2eUlT6rC4o4DBwMCIOLoO9a+RZn351RIRP4+IUc2Oo7uTtDlwJrBDRLyv2fGUMBZYCPSNiDMbuWBJvSRdJem5nPz+LOljVdMcIOlxSX+TdJukLQrjJOk7kl7JrwslqTD+PEkzJC2XdE4bsazRZ1XS1yTNzOvxjKSvVY0fnuP/W16fAwvjNpM0SdK8fLA5vGreAZJuyAc0CyX9XFLftmJa55JP1hM4vQHL2QJ4MiKWl5l4bbQgymrkstrSmWJZWyT1aHYMLdgCeCUiXm52ICVtATwaLfwavs77Tk9gDvBhoB/wb8CEypevpE2A3+TyAcA04IbC/GOBw4FdgJ2BQ4AvFMbPBr4O/G8d16FCwIlAf+Bg4DRJowvjfwn8GRgIfBO4UdKgPO4d4A/AkS3U/e1c71bA1qQD7nPajCgi1qkX8CxwFrAI2DiXfR6YUphmH2AqsDj/3aeV+v4OmAL8FZgFHJrLvwW8BbwNvA6cXGPec4AbgZ8Br+U4+gFXAfOBF/I/tkee/rPA3cAPc2yPAwcU6hsCTMrrNhv4x1aWdVpVfA/n6Vpbfg/gu6Qj0aeBU4EAerawbTYnfTgXAK8AP6paj4tzrN/OZXcV5g3gn4CngCXAeaQd+94c/wTgPYXpDwGm5//DPcDOhXH/ktdlCfBEcZtVxTseuAyYnKe9HdiiMP4DedyiXM8xVfNeCvweWAocWKP+LXOdS3I9PwJ+Vhj/K+DF/L+9AxiRy/cAXipuZ9IXwfQW1qMfcG3e7s8B/0o60DwQeIP0ZfI6ML7GvLcDR+b3++X/w8fz8IHFZQInAY8BrwK3tHNbfTu/3wi4DfgBoBr/j7dJ++nrefnnsPpnpq39/ld5+iXADGA74GzgZVJyGdWO749HCttnLHBPYVyfvH0/kIfvAcYWxp8M3Fejzp8B57SyzIOp/Vltcb1LrMcPgB/m99sBy4CNCuPvBE6pmqdn3h+GV5XfDPxTYfhU4JY2YygbbHd5kZLPgaQvxcoH4N3kQzqCeRU4IW/sY/PwwBp1rZ//6d8A3gPsn3fw7Qs7/s9aieWcvEMdTvpy6A38Frg878ibAg8AX8jTfxZYDvxzXvanSV9UA/L424FLgA2AXUlfPge0sqzV4mtj+aeQEt7meTvdRgvJh5SoHiYlmD45pv2q1uNLeRv3pnbymQT0BUbkD8etpKOrfsCjwJg87W6kL5K98nLH5P9zL2B70hfMkDztcGDrFv4f4/P/70N53v+uxJTXYQ7wuRzzbqQkPKIw72Jg37x9N6hR/73A93PdH8rLKiafk0hfxr2A/2LVL/pHgY8VhicCZ7awHtcCN+W6hgNPkg9+gI8Ac1vZJ89l5ZfSN4C/AN8pjPvv/P5w0r7/d3l7/Cv5i7jktvo26Sj7AfLnsJX/ybcLw+ew+n7c1n7/JnBQjuVa4BnS0f36wD8Cz5T87hic66okl/8GLq2aZiYrk9NiYK/CuJHAkhr1tpp8WvouaW2926hLpFbOKXn4U8BjVdP8qLIfFMpaSj6HkA66+ufXn4CvtBlHmY3enV6sTD475p1jEKsmnxOAB6rmuRf4bI26/h/pSHW9QtkvKztSrR2mxg51R9XOvQzoXSg7Frgtv/8sMI/CEWL+8J5ASggrWPXo5T/JR7fVy6oVX4nl/4nC0RAwipaTz9/nD0OtcZ8Fnq9RVp189i0MPwj8S2H4e8B/5feXAudV1fcE6XTJNqTEdCCwfhv7xnjg+sLwhnmbbk5K9HdWTX85MK4w77Wt1P1+UsLtUyj7RUv7B7Bx3gb98vC/AD/P7wcAfwM2qzFfj/w/3KFQ9gVW7t8fofXkcwDwSH7/B9Jn4748fDtwRH5/M4XWPCkR/I10mqzMtrqa9EX9tRL/k+rkU/zMlNnvJxfGfZLUeqi05jfK23njNuJYH/g/4PJC2VXABVXT3U3+rshxfaAwbtu8rOoWXruTT1vr3UZd3yIdGPbKwydQ1SIDzq+ui5aTz5C8bd7Jr8kUzkq09FpXr/kQETOB35FOwRUNIZ2qKHoOGFqjmiHAnIh4p8S0LZlTeL8FaSefL+mvkv5K+tBuWpjmhcj/8cLyhuTXoohY0kosxWXV0tbyh1TVUb2dijYHnouWr3e1FQukU00Vb9QY3rAQ95mVmHPcm5NaO7OBr5A+vC9Lul7SkFaW+W5cEfE66ZTGkLyMvaqWcTzwvlrz1jAEeDUilhbK3t1+knpIukDSXyS9RjpIAtgk//0Z8ElJGwLHkL7c59dYziakVnjxf9OeffJeYDtJg0lH09cCm+frG3uSTgdC2h7/XdgWi0hH1EMpt60+QWq1XFYyrqLidi6z31fvNwsjYkVhGFbuS6uRtB5wHenU12mFUa+TWuZFfUkt2lrj+wKvV31+O6rMeq9G0mmkaz+fiIhlLcRZiXUJ5fyK1LreKM/3F9L+2qp1Nvlk40jN7uI/bB7pw1P0ftI1g2rzSB/M9UpM25LijjiHdNS6SURsnF99I2JEYZqhxR4zeXnz8muApI1aiaV6p68ebmv580lf6sX6WzIHeH8rF4TXxgewuKzzCzFvHBHvjYhfAkTELyJiP9L/NYDvtFLXu+uXv+gHkLbtHOD2qmVsGBFfLLlO84H+xV6WrLr9jgMOI7XQ+pFOl0H6QiciXiAlhk+RjlSva2E5C0mnpYr7cOl9MiL+Rmplng7MjIi3SNcuzgD+EhEL86RzSKdji9ujd0TcQ7ltdSWpZfX7qm1SKszC+zL7fYflz9pVpLMCR0bE24XRs0idCSrT9iFdl5xVa3x+P4uOqd632r3ekk4iHWwfEBFzC6NmAVtV1dWeWHchtQiX5gO2y4CPtzXTOp188lHxDcCXC8W/Jx35HSepp6RPAzuQWknV7iddXP66pPUlfYTUrL++g/HMB/4IfE9SX0nrSdpa0ocLk20KfDkv72jSOfffR8Qc0pfEf0raQNLOpAucP29lkS8BwyvJs8TyJ+RlD5PUn9VbjUUPkL5wL5DUJ8e0b3u3SUlXAqdI2it3b+0j6ROSNpK0vaT9JfUina9/g3S6oiUfl7SfpPeQOjncn7ft70j7xQl5268vaQ9Jf1cmwIh4jtQb6luS3iNpP9K+UrERKfG/ArwX+I8a1VxL6h21E+maT63lrCD9n87P678FKXG0p5vu7aQj/Nvz8JSqYUhfMGdLGgEgqV/eH6H8tjqNdHr0d5J6tyO+d3Vwv2+PS0mfsU9GxBtV4yYCO0o6UtIGwL+TTlk+nsdfC5whaWhubZ9JOo0IQN4uG5C+h3vm+FvqJVn9WW3Xeks6nrRP/UNEPF0cFxFPkjrrjMt1fYrUO+/Xhfk3IF2LBOiVhyumAp9X+l1jb1JHjIdbWI93rdPJJzuXdIEUgIh4hXQB7UzSF8HXgUMKR3wUpn0LOBT4GOmI8xLgxMLO1xEnkk6bPErq6HAjsFlh/P2kc8cLSedlj8oxQ7o+M5x0VDSRdI59civLqvzw9RVJD5VY/pWkXk0PAw+ROm3UlL8EP0m65vI8MJd0LWCti4hppBbsj3LMs0nXkCB9YC4gba8XScn7G61U9wtSi3gRsDvpdBH59MYoYDRp+75IakH1ql1NTceROkUsysu4tjDuWtJpkxdI2/6+GvNPJLVoJladvqv2JdJB0dPAXXmdrm5HnLeTkuEdLQwTERNJ6399Pk04k/Q5KL2t8umnsaSW0k1VX2jt0d79vpScuL9AOv34oqTX86uyTywg9To8n7Tf7UVa54rLgf8h9bCbSepSfXlh/JWkg6FjSR0g3iC1amup9Vltz3pXOnhMLaxH8ZTnaFKHiFdJn5ej8vpVvEE6PQep01ExEZ+U45hL2n+3YuXnr0VaO6cfrREkfRb4fD6FZGuZpPGki/H/2uxYWiLpL6TTXf/X7FjM1oRbPmZdhKQjSef+/9TsWMzWVLf7ZblZd6R0+6cdgBOqeleadUk+7WZmZg3n025mZtZwTj5mZtZwvuaTbbLJJjF8+PBmh2Fm1qU8+OCDCyNiUNtTrsrJJxs+fDjTpk1rdhhmZl2KpNZus9Uin3YzM7OGc/IxM7OGc/IxM7OG8zUfM7MS3n77bebOncubb77Z7FCaYoMNNmDYsGGsv/76a6U+Jx8zsxLmzp3LRhttxPDhw1n1qSbdX0TwyiuvMHfuXLbccsu1UqdPu5mZlfDmm28ycODAdS7xAEhi4MCBa7XV5+RjZlbSuph4Ktb2ujv5mJl1ES+++CKjR49m6623ZocdduDjH/84Tz755Fqrf8qUKdxzzz1rrb7W+JpPB33rW/U9Aho3zjd8NevM1vZ3QFuf+YjgU5/6FGPGjOH669PDkqdPn85LL73Edtttt1ZimDJlChtuuCH77LPPWqmvNW75mJl1Abfddhvrr78+p5xyyrtlu+66K/vttx9f+9rX2HHHHdlpp5244YYbgJRIDjnkkHenPe200xg/fjyQ7ugybtw4dtttN3baaScef/xxnn32WS677DIuvvhidt11V+688866ro9bPmZmXcDMmTPZfffdVyv/zW9+w/Tp03n44YdZuHAhe+yxBx/60IfarG+TTTbhoYce4pJLLuG73/0uP/nJTzjllFPYcMMN+epXv1qPVVhFXVs+kp6VNEPSdEnTctkASZMlPZX/9i9Mf7ak2ZKekHRQoXz3XM9sST9QvvIlqZekG3L5/ZKGF+YZk5fxlKQx9VxPM7Nmueuuuzj22GPp0aMHgwcP5sMf/jBTp05tc74jjjgCgN13351nn322zlGurhGn3T4aEbtGxMg8fBZwa0RsC9yah5G0AzAaGAEcDFwiqUee51JgLLBtfh2cy08GXo2IbYCLge/kugYA44C9gD2BccUkZ2bW1YwYMYIHH3xwtfKWHgjas2dP3nln5UNvq7tJ9+rVC4AePXqwfPnytRhpOc245nMYcE1+fw1weKH8+ohYFhHPALOBPSVtBvSNiHsjbeVrq+ap1HUjcEBuFR0ETI6IRRHxKjCZlQnLzKzL2X///Vm2bBlXXnnlu2VTp06lf//+3HDDDaxYsYIFCxZwxx13sOeee7LFFlvw6KOPsmzZMhYvXsytt97a5jI22mgjlixZUs/VeFe9k08Af5T0oKSxuWxwRMwHyH83zeVDgTmFeefmsqH5fXX5KvNExHJgMTCwlbpWIWmspGmSpi1YsKDDK2lmVm+SmDhxIpMnT2brrbdmxIgRnHPOORx33HHsvPPO7LLLLuy///5ceOGFvO9972PzzTfnmGOOYeedd+b444/ngx/8YJvL+OQnP8nEiRO7RYeDfSNinqRNgcmSHm9l2lr9FqOV8o7Os7Ig4grgCoCRI0e6b7OZldaMn0MMGTKECRMmrFZ+0UUXcdFFF61WfuGFF3LhhReuVl68xjNy5EimTJkCwHbbbccjjzyy1uJtTV2TT0TMy39fljSRdP3lJUmbRcT8fErt5Tz5XGDzwuzDgHm5fFiN8uI8cyX1BPoBi3L5R6rmmbL21qzr8++UzKyZ6pZ8JPUB1ouIJfn9KOBcYBIwBrgg/70pzzIJ+IWk7wNDSB0LHoiIFZKWSNobuB84EfhhYZ4xwL3AUcCfIiIk3QL8R6GTwSjg7HqtqzVeV0+ejr91Pnjp/urZ8hkMTMy9onsCv4iIP0iaCkyQdDLwPHA0QETMkjQBeBRYDpwaEStyXV8ExgO9gZvzC+Aq4DpJs0ktntG5rkWSzgMq/Q3PjYhFdVxXMzNrh7oln4h4GtilRvkrwAEtzHM+cH6N8mnAjjXK3yQnrxrjrgaubl/UZrYu6EjLbdSo/+WFF5bS1v01hwwZ2foEXVRLXbo7yrfXMTMr4bXXZrN06XLW8ndwl1B5ns8GG2yw1ur07XXMzEp46KFzgHPo23cbWjtuX7z4sUaF1FCVJ5muLU4+ZmYlvPXWq9x33+ltTufOEuX4tJuZmTWck4+ZmTWck4+ZmTWck4+ZmTWck4+ZmTWck4+ZmTWcu1qbmXUh3eW+em75mJlZwzn5mJlZwzn5mJlZwzn5mJlZwzn5mJlZwzn5mJlZwzn5mJlZwzn5mJlZwzn5mJlZwzn5mJlZwzn5mJlZwzn5mJlZwzn5mJlZwzn5mJlZwzn5mJlZwzn5mJlZwzn5mJlZwzn5mJlZwzn5mJlZwzn5mJlZwzn5mJlZw9U9+UjqIenPkn6XhwdImizpqfy3f2HasyXNlvSEpIMK5btLmpHH/UCScnkvSTfk8vslDS/MMyYv4ylJY+q9nmZmVl4jWj6nA48Vhs8Cbo2IbYFb8zCSdgBGAyOAg4FLJPXI81wKjAW2za+Dc/nJwKsRsQ1wMfCdXNcAYBywF7AnMK6Y5MzMrLnalXwk9Ze0czumHwZ8AvhJofgw4Jr8/hrg8EL59RGxLCKeAWYDe0raDOgbEfdGRADXVs1TqetG4IDcKjoImBwRiyLiVWAyKxOWmZk1WZvJR9IUSX1za+Jh4KeSvl+y/v8Cvg68UygbHBHzAfLfTXP5UGBOYbq5uWxofl9dvso8EbEcWAwMbKWu6nUbK2mapGkLFiwouUpmZramyrR8+kXEa8ARwE8jYnfgwLZmknQI8HJEPFgyFtUoi1bKOzrPyoKIKyJiZESMHDRoUMkwzcxsTZVJPj3zqa9jgN+1o+59gUMlPQtcD+wv6WfAS7k+8t+X8/Rzgc0L8w8D5uXyYTXKV5lHUk+gH7ColbrMzKwTKJN8zgVuAf4SEVMlbQU81dZMEXF2RAyLiOGkjgR/iojPAJOASu+zMcBN+f0kYHTuwbYlqWPBA/nU3BJJe+frOSdWzVOp66i8jMjxjsrXqPoDo3KZmZl1Aj3bmiAifgX8qjD8NHDkGizzAmCCpJOB54Gjc72zJE0AHgWWA6dGxIo8zxeB8UBv4Ob8ArgKuE7SbFKLZ3Sua5Gk84CpebpzI2LRGsRsZmZrUZvJR9J2pK7OgyNix9zb7dCI+HbZhUTEFGBKfv8KcEAL050PnF+jfBqwY43yN8nJq8a4q4Gry8ZoZmaNU+a025XA2cDbABHxCLmFYWZm1hFlks97I+KBqrLl9QjGzMzWDWWSz0JJW5O7Kks6Cphf16jMzKxba/OaD3AqcAXwAUkvAM8An6lrVGZm1q2V6e32NHCgpD7AehGxpP5hmZlZd1amt9vGpN/WDCf94BSAiPhyPQMzM7Puq8xpt98D9wEzWPUebWZmZh1SJvlsEBFn1D0SMzNbZ5Tp7XadpH+UtFl+ENyAfIdrMzOzDinT8nkLuAj4JivvDB3AVvUKyszMurcyyecMYJuIWFjvYMzMbN1Q5rTbLOBv9Q7EzMzWHWVaPiuA6ZJuA5ZVCt3V2szMOqpM8vltfpmZma0VZe5wcE0jAjEzs3VHi8lH0oSIOEbSDFb2cntXROxc18jMzKzbaq3lc3r+e0gjAjEzs3VHi8knIuZL6gFcFREHNjAmMzPr5lrtah0RK4C/SerXoHjMzGwdUKa325vADEmTgaWVQne1NjOzjiqTfP43v8zMzNaKMsnnRuDNfAqOfB2oV12jMjOzbq3M7XVuBXoXhnsD/1efcMzMbF1QJvlsEBGvVwby+/fWLyQzM+vuyiSfpZJ2qwxI2h14o34hmZlZd1fmms9XgF9JmpeHNwM+XbeIzMys2ytzb7epkj4AbA8IeDwi3q57ZGZm1m2VafmQk83MOsdiZmbriDLXfMzMzNYqJx8zM2u41h6psFtL4wAi4qG1H46Zma0LWmv5fC+/fgzcD1wBXJnf/6CtiiVtIOkBSQ9LmiXpW7l8gKTJkp7Kf/sX5jlb0mxJT0g6qFC+u6QZedwPJCmX95J0Qy6/X9Lwwjxj8jKekjSmXVvFzMzqqsXkExEfjYiPAs8Bu0XEyIjYHfggMLtE3cuA/SNiF2BX4GBJewNnAbdGxLakuyecBSBpB2A0MAI4GLgk38oH4FJgLLBtfh2cy08GXo2IbYCLge/kugYA44C9gD2BccUkZ2ZmzVXmms8HImJGZSAiZpKSSasiqdwZYf38CuAwoPJo7muAw/P7w4DrI2JZRDxDSnB7StoM6BsR90ZEANdWzVOp60bggNwqOgiYHBGLIuJVYDIrE5aZmTVZmeTzmKSfSPqIpA9LuhJ4rEzlknpImg68TEoG9wODI2I+pAfWAZvmyYcCcwqzz81lQ/P76vJV5omI5cBiYGArdVXHN1bSNEnTFixYUGaVzMxsLSiTfD4HzCI9VvsrwKO5rE0RsSIidgWGkVoxO7YyuWpV0Up5R+cpxndFPp04ctCgQa2EZmZma1OZOxy8Keky4PcR8URHFhIRf5U0hXTq6yVJm+XHdG9GahVBap1sXphtGDAvlw+rUV6cZ66knkA/YFEu/0jVPFM6EruZma19bbZ8JB0KTAf+kId3lTSpxHyDJG2c3/cGDgQeByYBld5nY4Cb8vtJwOjcg21LUseCB/KpuSWS9s7Xc06smqdS11HAn/J1oVuAUZL6544Go3KZmZl1AmVurzOO1GNsCkBETC92aW7FZsA1ucfaesCEiPidpHuBCZJOBp4Hjs71zpI0gXRabzlwauUBdsAXgfGkZwndnF8AVwHXSZpNavGMznUtknQeMDVPd25ELCoRs5mZNUCZ5LM8Ihbnn9aUFhGPkLplV5e/AhzQwjznA+fXKJ8GrHa9KCLeJCevGuOuBq5uV9BmZtYQZZLPTEnHAT0kbQt8GbinvmGZmVl3Vqa325dIP/xcBvyC1J359HoGZWZm3VuZls8nIuKbwDcrBZKOBn5Vt6jMzKxbK9PyObtkmZmZWSmt3dX6Y8DHgaGSijcS7UvqjWZmZtYhrZ12mwdMAw4FHiyULwH+uZ5BmZlZ99Zi8omIhyXNBEZFxDUtTWdmZtZerV7zyT/yHCjpPQ2Kx8zM1gFlers9B9ydb6mztFIYEd+vW1RmZtatlUk+8/JrPWCj+oZjZmbrgjJ3ta48/nqjNPjuA+LMzMw6pMxdrXeU9GdgJjBL0oOSRtQ/NDMz667K/Mj0CuCMiNgiIrYAzgSurG9YZmbWnZVJPn0i4rbKQERMAfrULSIzM+v2ynQ4eFrSvwHX5eHPAM/ULyQzM+vuyrR8TgIGAb8BJub3n6tnUGZm1r2V6e32KvBlSf2AdyJiSf3DMjOz7qxMb7c9JM0AHgZmSHpY0u71D83MzLqrMtd8rgL+KSLuBJC0H/BTYOd6BmZmZt1XmWs+SyqJByAi7iLd2drMzKxDyrR8HpB0OfBLIIBPA1Mk7QYQEQ/VMT4zM+uGyiSfXfPfcVXl+5CS0f5rMyAzM+v+yvR2+2gjAjEzs3VHmd5ugyVdJenmPLyDpJPrH5qZmXVXZTocjAduAYbk4SeBr9QpHjMzWweUST6bRMQE4B2AiFgOrKhrVGZm1q2VST5LJQ0kdS5A0t7A4rpGZWZm3VqZ3m5nAJOArSXdTbq321F1jcrMzLq1VpOPpB7Ah/Nre0DAExHxdgNiMzOzbqrV024RsQI4LCKWR8SsiJjpxGNmZmuqzGm3uyX9CLgBWFop9J0NzMyso8p0ONgHGAGcC3wvv77b1kySNpd0m6THJM2SdHouHyBpsqSn8t/+hXnOljRb0hOSDiqU7y5pRh73A0nK5b0k3ZDL75c0vDDPmLyMpySNKbk9zMysAep5h4PlwJkR8ZCkjYAHJU0GPgvcGhEXSDoLOAv4F0k7AKNJiW4I8H+Stsun/i4FxgL3Ab8HDgZuBk4GXo2IbSSNBr4DfFrSANLtgEaSeuk9KGlSfjaRmZk1WZmWT4dExPzKqbn8ALrHgKHAYcA1ebJrgMPz+8OA6yNiWUQ8A8wG9pS0GdA3Iu6NiACurZqnUteNwAG5VXQQMDkiFuWEM5mUsMzMrBOoW/IpyqfDPgjcDwyOiPmQEhSwaZ5sKDCnMNvcXDY0v68uX2We/OPXxcDAVuoyM7NOoMXkI+no/HfLNVmApA2BXwNfiYjXWpu0Rlm0Ut7ReYqxjZU0TdK0BQsWtBKamZmtTa21fM7Of3/d0colrZ/n/3lE/CYXv5RPpZH/vpzL5wKbF2YfBszL5cNqlK8yj6SeQD9gUSt1rSIiroiIkRExctCgQR1dTTMza6fWks8rkm4DtpQ0qfrVVsX52stVwGMR8f3CqElApffZGOCmQvno3INtS2Bb4IF8am6JpL1znSdWzVOp6yjgT/m60C3AKEn9c2+6UbnMzMw6gdZ6u30C2A24jtS9ur32BU4AZkiansu+AVwATMiPZXgeOBogImZJmgA8Suopd2ru6QbwRdLdtXuTerndnMuvAq6TNJvU4hmd61ok6Txgap7u3IhY1IF1MDOzOmgx+UTEW8B9kvaJiAW5u3RExOtlKo6Iu6h97QXggBbmOR84v0b5NGDHGuVvkpNXjXFXA1eXidXMzBqrTG+3wZL+DMwEHpX0oKTVEoGZmVlZZZLPFcAZEbFFRLwfODOXmZmZdUiZ5NMnIm6rDETEFKBP3SIyM7Nur8yNRZ+W9G+kjgcAnwGeqV9IZmbW3ZVp+ZxEeoDcb/JrE+Bz9QzKzMy6tzI3Fn0V+HIDYjEzs3VEQ+7tZmZmVuTkY2ZmDefkY2ZmDddm8pE0TNJESQskvSTp15KGtTWfmZlZS8q0fH5KuoHnZqRn4vxPLjMzM+uQMslnUET8NCKW59d4UtdrMzOzDimTfBZK+oykHvn1GeCVegdmZmbdV9kfmR4DvAjMJz0356R6BmVmZt1bmR+ZPg8c2oBYzMxsHdFi8pH0763MFxFxXh3iMTOzdUBrLZ+lNcr6ACcDAwEnHzMz65DWnmT67qOz81NMTyfdUPR6OvZYbTMzM6CNaz6SBgBnAMcD1wC75RuNmpmZdVhr13wuAo4gPbV0p4h4vWFRmZlZt9ZaV+szgSHAvwLzJL2WX0skvdaY8MzMrDtq7ZqPbzpqZmZ14QRjZmYN5+RjZmYN5+RjZmYN5+RjZmYN5+RjZmYN5+RjZmYN5+RjZmYN5+RjZmYN5+RjZmYNV7fkI+lqSS9LmlkoGyBpsqSn8t/+hXFnS5ot6QlJBxXKd5c0I4/7gSTl8l6Sbsjl90saXphnTF7GU5LG1GsdzcysY+rZ8hkPHFxVdhZwa0RsC9yah5G0AzAaGJHnuURSjzzPpcBYYNv8qtR5MvBqRGwDXAx8J9c1ABgH7AXsCYwrJjkzM2u+uiWfiLgDWFRVfBjp0Qzkv4cXyq+PiGUR8QwwG9hT0mZA34i4NyICuLZqnkpdNwIH5FbRQcDkiFiUH/8wmdWToJmZNVGjr/kMjoj5APnvprl8KDCnMN3cXDY0v68uX2WeiFgOLCY9YbWluszMrJPoLB0OVKMsWinv6DyrLlQaK2mapGkLFiwoFaiZma25Riefl/KpNPLfl3P5XGDzwnTDgHm5fFiN8lXmkdQT6Ec6zddSXauJiCsiYmREjBw0aNAarJaZmbVHo5PPJKDS+2wMcFOhfHTuwbYlqWPBA/nU3BJJe+frOSdWzVOp6yjgT/m60C3AKEn9c0eDUbnMzMw6iRYfJremJP0S+AiwiaS5pB5oFwATJJ0MPA8cDRARsyRNAB4FlgOnRsSKXNUXST3negM35xfAVcB1kmaTWjyjc12LJJ0HTM3TnRsR1R0fzMysieqWfCLi2BZGHdDC9OcD59conwbsWKP8TXLyqjHuauDq0sGamVlDdZYOB2Zmtg5x8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4Zz8jEzs4br1slH0sGSnpA0W9JZzY7HzMySbpt8JPUAfgx8DNgBOFbSDs2NyszMoBsnH2BPYHZEPB0RbwHXA4c1OSYzMwMUEc2OoS4kHQUcHBGfz8MnAHtFxGmFacYCY/Pg9sATdQxpE2BhHeuvN8ffXI6/ubpy/PWOfYuIGNTemXrWI5JOQjXKVsm0EXEFcEVDgpGmRcTIRiyrHhx/czn+5urK8XfW2Lvzabe5wOaF4WHAvCbFYmZmBd05+UwFtpW0paT3AKOBSU2OyczM6Man3SJiuaTTgFuAHsDVETGriSE15PReHTn+5nL8zdWV4++UsXfbDgdmZtZ5defTbmZm1kk5+ZiZWcM5+ZiZWcN12w4HZpJ6RcSytso6I0nPUPW7NICI2KoJ4XSIpP2AbSPip5IGARtGxDPNjss6ByefOpDUNyJekzSg1viIWNTomNpD0gxqfPFVRMTODQxnTdwL7FairDMq/ihwA+BooOb+1BlJGkdah+2BnwLrAz8D9m1mXO2V7xE5mMJ3ZUQ837yIyuvssTv51McvgEOAB0lf4sW7LQTQ2Y9eD8l/T81/r8t/jwf+1vhw2kfS+4ChQG9JH2Tl9u8LvLdpgbVDRLxSVfRfku4C/r0Z8XTAp4APAg8BRMQ8SRs1N6T2kfQlYBzwEvBOLg6g0x98dYXYnXzqICIOkSTgw53pSKOsiHgOQNK+EVE8Uj1L0t3Auc2JrLSDgM+S7mrx/UL5EuAbzQiovSQVW2frkVoRXenL+62ICEkBIKlPswPqgNOB7WscCHQFnT52J586yR+8icDuzY5lDfSRtF9E3AUgaR+g03+JRMQ1wDWSjoyIXzc7ng76XuH9cuBZ4JjmhNIhEyRdDmws6R+Bk4ArmxxTe80BFjc7iA7q9LH7R6Z1JOnHwPiImNrsWDpC0u7A1UA/UpN9MXBSRDzU1MDaQdIngBGk6yYARERnb7l1C5L+ARhFOu15S0RMbnJI7SLpKtI1q/8F3u2kEhHfb3GmTqIrxO6WT319FDhF0rPAUtKHMLrKBfuIeBDYRVJf0oFKpz6SqibpMtI1no8CPwGOAh5oalAlSeoFHAkMZ9ULxl0mceZk06USTpXn8+s9+dWVdPrY3fKpA0nvj4jnJW1Ra3zlmkpnJ2kw8B/AkIj4WH4S7N9HxFVNDq0USY9ExM6FvxsCv4mIUc2OrS2S/kBqaT4IrKiUR8T3WpypE5G0hJU9Jt9D6u22NCL6Ni+qjskdJSIiXm92LN2JWz718Vtgt4h4TtKvI+LIZgfUQeNJ3WS/mYefBG4AukTyAd7If/8maQiwCNiyifG0x7CIOLjZQXRURKzSOULS4aSnC3cZknYk9fQckIcXAic2+QbFpUi6jdq/E9u/CeHU5ORTH8Wu1Z29W3VrNomICZLOhnfvFL6irZk6kd9J2hi4kNSCgHT6rSu4R9JOETGj2YG0h6SeEbG8ujwifivprGbEtAauAM6IiNsAJH2E1GlinybGVNZXC+83IJ3CXe3/0kxOPvURLbzvapZKGkheB0l708l70ABI2gOYExHn5eENgRnA48DFzYytLZJmkn6X0RP4nKSnSReMu8r1wgeA3SQdUSirdBXvap+FPpXEAxARU7pKl/F8vbbobkm3NyWYFjj51Mcukl4jfWH0zu9h5RdIpz7vLekrwN3A14GbgK3y73sGkX5p39ldDhwIIOlDwAXAl4BdSUezRzUtsrYNJcXZ1X2Slcmm0lX80KZF0zFPS/o3Vv7I+jNAl7g9UNXdVSrJ/31NCqcmdziw1Uj6LunUwgdIrYUXgCnADRGxsImhlSLp4YjYJb//MbAgIs7Jw9MjYtcmhtcqSQ9FRFe4/U9NkuaSftirqlEBnaurb1sk9Qe+BexHWp87gHMi4tWmBlZC1b0BK8n/3Mpv9joDt3xsNRHxVYD8+PGRpES0P/BNSX+NiB2aGV8JPQrXHg4AxhbGdfZ9flNJZ7Q0sgt8efcANmT15NPl5CTz5WbH0R6FU85b5uExpOs9zwKPNjG01XT2D6I1V2/S/dD65dc80rWTzu6XwO25d9IbwJ0Akrah81+z6upf3vO70m+RapE0qbXxEdGZTx9Wn3L+TzrpKWefdrPVSLqCdFeAJcD9wH3AfV3hdENF7hyxGfDHiFiay7Yj3da/096hoRucdvtzRHyw2XGsCUkLSLen+SVp/1/lQCAiOtWF+6KudMrZLR+r5f1AL+Ap0vWeucBfmxlQe0XEfTXKnmxGLO3UVVs8FQc0O4C14H3APwDHAseRblHzy67w+x660Clnt3yspnxX7hGk6z37ADuSfqR5b0SMa2Zs3ZmkAZ39eU/rknybo2OBi0gX7H/Y5JBaJembwMeBhaSDyN3yTY63Aa6pukt9Uzn5WKskDSM9AGwf0nN+BkbExk0NyqzOctL5BCnxDAcmAVdHxAvNjKuMrnLK2cnHViPpy6Rksy/wNuk3P/fmvzMi4p1WZjfr0iRdQ2rp3wxcHxEzmxxSt+TkY6uR9H3gHuDuiJjf7HjMGknSO6S70MOqd2XoEj8S7yqcfMzMrOHWa3YAZma27nHyMTOzhnPyMWsHSSskTZc0S9LDks6QVNfPkaSL8vIuqvNyhue7apvVXaf60ZFZF/BG5VfikjYFfkG69VA9f/v0BWBQRCyr4zLMGsotH7MOioiXSb8gP03JcEl3Snoov/YBkHSdpMMq80n6uaRV7g+W579I0kxJMyR9OpdPAvoA91fKCvPMkLRxnvcVSScWlnegpB65zqmSHpH0hcK8XyuUf6t63SRtJenP+UaVZmudWz5mayAins6n3TYFXgb+ISLelLQt6d5gI0lPT/1n4CZJ/Ui/oRpTVdURpJs/7gJsAkyVdEdEHCrp9RbuyXU36bdYzwFPA/8PuBbYG/gicDKwOCL2yD+avFvSH4Ft82tPUvfhSfkmlM8DSNoeuB74XERMX8NNZFaTk4/Zmqvcj2194EeSdgVWANtBuhGlpB/n03RHAL+u8ajp/Uj3D1sBvJSfOrkH6Zf1LbkT+BAp+VwKjJU0FFgUEa9LGgXsLKlyJ+N+pKQzKr/+nMs3zOXPkx4YeBNwZBe5l5l1UU4+ZmtA0lakRPMy6brPS6TWy3rAm4VJrwOOB0YDJ9WqqgOLvwM4lXQPr28CnyLdMv/OQp1fiohbqmI+CPjPiLi8qnw46ZETc0gtKicfqxtf8zHrIEmDgMuAH0X6tXY/0vNs3gFOID2bp2I88BWAFloUdwCfztdpBpFaNA+0tvyImEM6RbdtRDwN3AV8lZXJ5xbgi5LWz/FuJ6lPLj9J0oa5fGhulQG8BRwOnCjpuJKbwqzd3PIxa5/ekqaTTrEtJ7VoKk8XvQT4taSjgdtYeYsWIuIlSY8Bv22h3onA3wMPk27p8vWIeLFEPPezMsndSXp4WOVRyT8h3RTzoXyX8gXA4RHxR0l/B9ybinkd+AypBUdELJV0CDBZ0tKIuKlEHGbt4tvrmDWApPeSngK7W0R09qepmtWdT7uZ1ZmkA4HHgR868ZglbvmYmVnDueVjZmYN5+RjZmYN5+RjZmYN5+RjZmYN5+RjZmYN5+RjZmYN9/8BqKdMi6/WHp0AAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "cp.sort_values('Count', ascending=False).plot(kind='bar',x='Day_of_week', y='Count', color='olive');\n",
    "plt.xlabel('Day of week')\n",
    "plt.ylabel('No of reported crimes')\n",
    "plt.title('No of reported crimes per day of week from 2001 to 2018')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## RDD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [],
   "source": [
    "psrdd = sc.textFile('C:\\\\Users\\\\shubh\\\\Downloads\\\\Police_Stations.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'DISTRICT,DISTRICT NAME,ADDRESS,CITY,STATE,ZIP,WEBSITE,PHONE,FAX,TTY,X COORDINATE,Y COORDINATE,LATITUDE,LONGITUDE,LOCATION'"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "psrdd.first()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [],
   "source": [
    "ps_header = psrdd.first()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [],
   "source": [
    "ps_rest = psrdd.filter(lambda line: line!=ps_header)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Headquarters,Headquarters,3510 S Michigan Ave,Chicago,IL,60653,http://home.chicagopolice.org,,,,1177731.401,1881697.404,41.83070169,-87.62339535,\"(41.8307016873, -87.6233953459)\"'"
      ]
     },
     "execution_count": 178,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ps_rest.first()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['Headquarters',\n",
       "  'Headquarters',\n",
       "  '3510 S Michigan Ave',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60653',\n",
       "  'http://home.chicagopolice.org',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '1177731.401',\n",
       "  '1881697.404',\n",
       "  '41.83070169',\n",
       "  '-87.62339535',\n",
       "  '\"(41.8307016873',\n",
       "  ' -87.6233953459)\"'],\n",
       " ['18',\n",
       "  'Near North',\n",
       "  '1160 N Larrabee St',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60610',\n",
       "  'http://home.chicagopolice.org/community/districts/18th-district-near-north/',\n",
       "  '312-742-5870',\n",
       "  '312-742-5771',\n",
       "  '312-742-5773',\n",
       "  '1172080.029',\n",
       "  '1908086.527',\n",
       "  '41.90324165',\n",
       "  '-87.64335214',\n",
       "  '\"(41.9032416531',\n",
       "  ' -87.6433521393)\"'],\n",
       " ['19',\n",
       "  'Town Hall',\n",
       "  '850 W Addison St',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60613',\n",
       "  'http://home.chicagopolice.org/community/districts/19th-district-town-hall/',\n",
       "  '312-744-8320',\n",
       "  '312-744-4481',\n",
       "  '312-744-8011',\n",
       "  '1169730.744',\n",
       "  '1924160.317',\n",
       "  '41.94740046',\n",
       "  '-87.65151202',\n",
       "  '\"(41.9474004564',\n",
       "  ' -87.651512018)\"'],\n",
       " ['20',\n",
       "  'Lincoln',\n",
       "  '5400 N Lincoln Ave',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60625',\n",
       "  'http://home.chicagopolice.org/community/districts/20th-district-lincoln/',\n",
       "  '312-742-8714',\n",
       "  '312-742-8803',\n",
       "  '312-742-8841',\n",
       "  '1158399.146',\n",
       "  '1935788.826',\n",
       "  '41.97954951',\n",
       "  '-87.69284451',\n",
       "  '\"(41.9795495131',\n",
       "  ' -87.6928445094)\"'],\n",
       " ['22',\n",
       "  'Morgan Park',\n",
       "  '1900 W Monterey Ave',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60643',\n",
       "  'http://home.chicagopolice.org/community/districts/22nd-district-morgan-park/',\n",
       "  '312-745-0710',\n",
       "  '312-745-0814',\n",
       "  '312-745-0569',\n",
       "  '1165825.476',\n",
       "  '1830851.333',\n",
       "  '41.69143478',\n",
       "  '-87.66852039',\n",
       "  '\"(41.6914347795',\n",
       "  ' -87.6685203937)\"'],\n",
       " ['24',\n",
       "  'Rogers Park',\n",
       "  '6464 N Clark St',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60626',\n",
       "  'http://home.chicagopolice.org/community/districts/24th-district-rogers-park/',\n",
       "  '312-744-5907',\n",
       "  '312-744-6928',\n",
       "  '312-744-7603',\n",
       "  '1164193.588',\n",
       "  '1943199.401',\n",
       "  '41.99976348',\n",
       "  '-87.67132429',\n",
       "  '\"(41.9997634842',\n",
       "  ' -87.6713242922)\"'],\n",
       " ['25',\n",
       "  'Grand Central',\n",
       "  '5555 W Grand Ave',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60639',\n",
       "  'http://home.chicagopolice.org/community/districts/25th-district-grand-central/',\n",
       "  '312-746-8605',\n",
       "  '312-746-4353',\n",
       "  '312-746-8383',\n",
       "  '1138770.871',\n",
       "  '1913442.439',\n",
       "  '41.91860889',\n",
       "  '-87.76557448',\n",
       "  '\"(41.9186088912',\n",
       "  ' -87.765574479)\"'],\n",
       " ['1',\n",
       "  'Central',\n",
       "  '1718 S State St',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60616',\n",
       "  'http://home.chicagopolice.org/community/districts/1st-district-central/',\n",
       "  '312-745-4290',\n",
       "  '312-745-3694',\n",
       "  '312-745-3693',\n",
       "  '1176569.052',\n",
       "  '1891771.704',\n",
       "  '41.85837259',\n",
       "  '-87.62735617',\n",
       "  '\"(41.8583725929',\n",
       "  ' -87.627356171)\"'],\n",
       " ['2',\n",
       "  'Wentworth',\n",
       "  '5101 S Wentworth Ave',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60609',\n",
       "  'http://home.chicagopolice.org/community/districts/2nd-district-wentworth/',\n",
       "  '312-747-8366',\n",
       "  '312-747-5396',\n",
       "  '312-747-6656',\n",
       "  '1175864.837',\n",
       "  '1871153.753',\n",
       "  '41.80181109',\n",
       "  '-87.63056018',\n",
       "  '\"(41.8018110912',\n",
       "  ' -87.6305601801)\"'],\n",
       " ['3',\n",
       "  'Grand Crossing',\n",
       "  '7040 S Cottage Grove Ave',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60637',\n",
       "  'http://home.chicagopolice.org/community/districts/3rd-district-grand-crossing/',\n",
       "  '312-747-8201',\n",
       "  '312-747-5479',\n",
       "  '312-747-9168',\n",
       "  '1182739.183',\n",
       "  '1858317.732',\n",
       "  '41.76643089',\n",
       "  '-87.60574786',\n",
       "  '\"(41.7664308925',\n",
       "  ' -87.6057478606)\"'],\n",
       " ['4',\n",
       "  'South Chicago',\n",
       "  '2255 E 103rd St',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60617',\n",
       "  'http://home.chicagopolice.org/community/districts/4th-district-south-chicago/',\n",
       "  '312-747-7581',\n",
       "  '312-747-5276',\n",
       "  '312-747-9169',\n",
       "  '1193131.299',\n",
       "  '1837090.265',\n",
       "  '41.70793329',\n",
       "  '-87.56834912',\n",
       "  '\"(41.7079332906',\n",
       "  ' -87.5683491228)\"'],\n",
       " ['5',\n",
       "  'Calumet',\n",
       "  '727 E 111th St',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60628',\n",
       "  'http://home.chicagopolice.org/community/districts/5th-district-calumet/',\n",
       "  '312-747-8210',\n",
       "  '312-747-5935',\n",
       "  '312-747-9170',\n",
       "  '1183305.427',\n",
       "  '1831462.313',\n",
       "  '41.69272336',\n",
       "  '-87.60450587',\n",
       "  '\"(41.6927233639',\n",
       "  ' -87.6045058667)\"'],\n",
       " ['6',\n",
       "  'Gresham',\n",
       "  '7808 S Halsted St',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60620',\n",
       "  'http://home.chicagopolice.org/community/districts/6th-district-gresham/',\n",
       "  '312-745-3617',\n",
       "  '312-745-3649',\n",
       "  '312-745-3639',\n",
       "  '1172283.013',\n",
       "  '1853022.646',\n",
       "  '41.75213684',\n",
       "  '-87.64422891',\n",
       "  '\"(41.7521368378',\n",
       "  ' -87.6442289066)\"'],\n",
       " ['7',\n",
       "  'Englewood',\n",
       "  '1438 W 63rd St',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60636',\n",
       "  'http://home.chicagopolice.org/community/districts/7th-district-englewood/',\n",
       "  '312-747-8223',\n",
       "  '312-747-6558',\n",
       "  '312-747-6652',\n",
       "  '1167659.235',\n",
       "  '1863005.522',\n",
       "  '41.77963154',\n",
       "  '-87.66088702',\n",
       "  '\"(41.7796315359',\n",
       "  ' -87.6608870173)\"'],\n",
       " ['8',\n",
       "  'Chicago Lawn',\n",
       "  '3420 W 63rd St',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60629',\n",
       "  'http://home.chicagopolice.org/community/districts/8th-district-chicago-lawn/',\n",
       "  '312-747-8730',\n",
       "  '312-747-8545',\n",
       "  '312-747-8116',\n",
       "  '1154575.242',\n",
       "  '1862672.049',\n",
       "  '41.77898719',\n",
       "  '-87.70886382',\n",
       "  '\"(41.778987189',\n",
       "  ' -87.7088638153)\"'],\n",
       " ['9',\n",
       "  'Deering',\n",
       "  '3120 S Halsted St',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60608',\n",
       "  'http://home.chicagopolice.org/community/districts/9th-district-deering/',\n",
       "  '312-747-8227',\n",
       "  '312-747-5329',\n",
       "  '312-747-9172',\n",
       "  '1171440.24',\n",
       "  '1884085.224',\n",
       "  '41.83739443',\n",
       "  '-87.64640771',\n",
       "  '\"(41.8373944311',\n",
       "  ' -87.6464077068)\"'],\n",
       " ['10',\n",
       "  'Ogden',\n",
       "  '3315 W Ogden Ave',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60623',\n",
       "  'http://home.chicagopolice.org/community/districts/10th-district-ogden/',\n",
       "  '312-747-7511',\n",
       "  '312-747-7429',\n",
       "  '312-747-7471',\n",
       "  '1154500.753',\n",
       "  '1890985.501',\n",
       "  '41.85668453',\n",
       "  '-87.70838196',\n",
       "  '\"(41.8566845327',\n",
       "  ' -87.708381958)\"'],\n",
       " ['11',\n",
       "  'Harrison',\n",
       "  '3151 W Harrison St',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60612',\n",
       "  'http://home.chicagopolice.org/community/districts/11th-district-harrison/',\n",
       "  '312-746-8386',\n",
       "  '312-746-4281',\n",
       "  '312-746-5151',\n",
       "  '1155244.069',\n",
       "  '1897148.755',\n",
       "  '41.87358229',\n",
       "  '-87.70548813',\n",
       "  '\"(41.8735822883',\n",
       "  ' -87.705488126)\"'],\n",
       " ['12', 'Near West', '\"1412 S Blue Island Ave'],\n",
       " ['\"',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60608',\n",
       "  'http://home.chicagopolice.org/community/districts/12th-district-near-west/',\n",
       "  '312-746-8396',\n",
       "  '312-746-4248',\n",
       "  '312-746-9868',\n",
       "  '1168487.845',\n",
       "  '1893384.455',\n",
       "  '41.86297662',\n",
       "  '-87.65697251',\n",
       "  '\"(41.8629766244',\n",
       "  ' -87.6569725149)\"'],\n",
       " ['14',\n",
       "  'Shakespeare',\n",
       "  '2150 N California Ave',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60647',\n",
       "  'http://home.chicagopolice.org/community/districts/14th-district-shakespeare/',\n",
       "  '312-744-8250',\n",
       "  '312-744-2422',\n",
       "  '312-744-8260',\n",
       "  '1157304.426',\n",
       "  '1914481.521',\n",
       "  '41.92110332',\n",
       "  '-87.69745182',\n",
       "  '\"(41.9211033246',\n",
       "  ' -87.6974518223)\"'],\n",
       " ['15',\n",
       "  'Austin',\n",
       "  '5701 W Madison St',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60644',\n",
       "  'http://home.chicagopolice.org/community/districts/15th-district-austin/',\n",
       "  '312-743-1440',\n",
       "  '312-743-1366',\n",
       "  '312-743-1485',\n",
       "  '1138148.815',\n",
       "  '1899399.078',\n",
       "  '41.88008346',\n",
       "  '-87.76819989',\n",
       "  '\"(41.8800834614',\n",
       "  ' -87.768199889)\"'],\n",
       " ['16',\n",
       "  'Jefferson Park',\n",
       "  '5151 N Milwaukee Ave',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60630',\n",
       "  'http://home.chicagopolice.org/community/districts/16th-district-jefferson-park/',\n",
       "  '312-742-4480',\n",
       "  '312-742-4421',\n",
       "  '312-742-4423',\n",
       "  '1138480.758',\n",
       "  '1933660.473',\n",
       "  '41.97409445',\n",
       "  '-87.76614884',\n",
       "  '\"(41.9740944511',\n",
       "  ' -87.7661488432)\"'],\n",
       " ['17',\n",
       "  'Albany Park',\n",
       "  '4650 N Pulaski Rd',\n",
       "  'Chicago',\n",
       "  'IL',\n",
       "  '60630',\n",
       "  'http://home.chicagopolice.org/community/districts/17th-district-albany-park/',\n",
       "  '312-742-4410',\n",
       "  '312-742-5411',\n",
       "  '312-742-5451',\n",
       "  '1148843.91',\n",
       "  '1930801.058',\n",
       "  '41.96605342',\n",
       "  '-87.72811456',\n",
       "  '\"(41.9660534171',\n",
       "  ' -87.728114561)\"']]"
      ]
     },
     "execution_count": 179,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ps_rest.map(lambda line: line.split(',')).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24"
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ps_rest.map(lambda line: line.split(',')).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('7', 'Englewood', '1438 W 63rd St', '60636')]"
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(ps_rest.filter(lambda line: line.split(',')[0]=='7').\n",
    "map(lambda line:(line.split(',')[0],\n",
    "    line.split(',')[1],\n",
    "    line.split(',')[2],\n",
    "    line.split(',')[5]\n",
    "   )).collect())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Ogden', '3315 W Ogden Ave', '60623'),\n",
       " ('Harrison', '3151 W Harrison St', '60612')]"
      ]
     },
     "execution_count": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(ps_rest.filter(lambda line:line.split(',')[0] in ['10','11']).\n",
    "map(lambda line:(line.split(',')[1],\n",
    "           line.split(',')[2],\n",
    "           line.split(',')[5]\n",
    "           )\n",
    ")).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "name": "Download_data.ipynb",
   "provenance": [],
   "version": "0.3.2"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}