Apache_PySpark_by_Example.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Apache PySpark by Example.ipynb",
      "provenance": [],
      "collapsed_sections": [
        "OmNZ64Vpgs3i",
        "P5DoC5WIgxur",
        "Jdw4bxE59vm2",
        "uNvBLTzl_WsC"
      ],
      "toc_visible": true,
      "authorship_tag": "ABX9TyNsvzLU730QDtanGys6gkX1",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/vsvale/Apache-PySpark-by-Example/blob/main/Apache_PySpark_by_Example.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wlFbrWnhgHL2"
      },
      "source": [
        "# Setup Environment"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "sAkWOC8F-evy"
      },
      "source": [
        "%%capture\n",
        "!pip install pyspark"
      ],
      "execution_count": 1,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "StKmLtSqI2sL"
      },
      "source": [
        "import pyspark\n",
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.getOrCreate()"
      ],
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hkRlfc85gOj4"
      },
      "source": [
        "# Download Dataset"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CVPcoLpBLCqA"
      },
      "source": [
        "Este conjunto de dados reflete incidentes de crime relatados (com exceção de assassinatos em que existem dados para cada vítima) que ocorreram na cidade de Chicago de 2001 até o presente, menos os sete dias mais recentes. Os dados são extraídos do sistema CLEAR (Citizen Law Enforcement Analysis and Reporting) do Departamento de Polícia de Chicago. Para proteger a privacidade das vítimas de crimes, os endereços são mostrados apenas no nível do bloco e os locais específicos não são identificados."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0vLY2micLD-9",
        "outputId": "0236bf75-1e65-4d7a-f2b5-dc0bd3f41764"
      },
      "source": [
        "!wget https://data.cityofchicago.org/api/views/ijzp-q8t2/rows.csv?accessType=DOWNLOAD"
      ],
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "--2021-04-11 18:51:56--  https://data.cityofchicago.org/api/views/ijzp-q8t2/rows.csv?accessType=DOWNLOAD\n",
            "Resolving data.cityofchicago.org (data.cityofchicago.org)... 52.206.140.205, 52.206.68.26, 52.206.140.199\n",
            "Connecting to data.cityofchicago.org (data.cityofchicago.org)|52.206.140.205|:443... connected.\n",
            "HTTP request sent, awaiting response... 200 OK\n",
            "Length: unspecified [text/csv]\n",
            "Saving to: ‘rows.csv?accessType=DOWNLOAD’\n",
            "\n",
            "rows.csv?accessType     [        <=>         ]   1.60G  3.21MB/s    in 8m 19s  \n",
            "\n",
            "2021-04-11 19:00:16 (3.29 MB/s) - ‘rows.csv?accessType=DOWNLOAD’ saved [1723679791]\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "uXDVCVG3Q5JW"
      },
      "source": [
        "!mv /content/rows.csv?accessType=DOWNLOAD /content/reported_crimes.csv"
      ],
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bUeSPat_gX-g"
      },
      "source": [
        "# Create Dataframe"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Nml3V9jZRTn-"
      },
      "source": [
        "#criar o dataframe spark a partir do dataset\n",
        "from pyspark.sql.functions import to_timestamp,col,lit\n",
        "rc = spark.read.csv('./reported_crimes.csv',header=True).withColumn('Date',to_timestamp(col('Date'),'MM/dd/yyyy hh:mm:ss a'))"
      ],
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "E9DVJ9HWTZTE",
        "outputId": "3cfe3f7c-7309-4059-9707-1f2f4c7512f3"
      },
      "source": [
        "rc.show(5)"
      ],
      "execution_count": 6,
      "outputs": [
        {
          "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"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kedtJH-GgevN"
      },
      "source": [
        "# Limit example"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "m_hY-2T-TfH9",
        "outputId": "6fd0472d-9ecc-412b-8bf0-f1360a4d3698"
      },
      "source": [
        "#selecionar apenas algumas linhas\n",
        "small_set = rc.limit(100)\n",
        "small_set.count()"
      ],
      "execution_count": 103,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "100"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 103
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "RfSFip9xru3m",
        "outputId": "f7810b95-eab2-4a3d-cab9-9891f7368333"
      },
      "source": [
        "small_set.show()"
      ],
      "execution_count": 104,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------+-----------+-------------------+--------------------+----+------------------+--------------------+--------------------+------+--------+----+----+--------------+--------+----+--------------------+--------------+\n",
            "|      ID|Case Number|               Date|               Block|IUCR|      Primary Type|         Description|Location Description|Arrest|Domestic|Beat|Ward|Community Area|FBI Code|Year|          Updated On| DISTRICT NAME|\n",
            "+--------+-----------+-------------------+--------------------+----+------------------+--------------------+--------------------+------+--------+----+----+--------------+--------+----+--------------------+--------------+\n",
            "|10224738|   HY411648|2015-09-05 13:30:00|     043XX S WOOD ST|0486|           BATTERY|DOMESTIC BATTERY ...|           RESIDENCE| false|    true|0924|  12|            61|     08B|2015|02/10/2018 03:50:...|       Deering|\n",
            "|10224739|   HY411615|2015-09-04 11:30:00| 008XX N CENTRAL AVE|0870|             THEFT|      POCKET-PICKING|             CTA BUS| false|   false|1511|  29|            25|      06|2015|02/10/2018 03:50:...|        Austin|\n",
            "|11646166|   JC213529|2018-09-01 00:01:00|082XX S INGLESIDE...|0810|             THEFT|           OVER $500|           RESIDENCE| false|    true|0631|   8|            44|      06|2018|04/06/2019 04:04:...|       Gresham|\n",
            "|10224740|   HY411595|2015-09-05 12:45:00|   035XX W BARRY AVE|2023|         NARCOTICS|POSS: HEROIN(BRN/...|            SIDEWALK|  true|   false|1412|  35|            21|      18|2015|02/10/2018 03:50:...|   Shakespeare|\n",
            "|10224741|   HY411610|2015-09-05 13:00:00| 0000X N LARAMIE AVE|0560|           ASSAULT|              SIMPLE|           APARTMENT| false|    true|1522|  28|            25|     08A|2015|02/10/2018 03:50:...|        Austin|\n",
            "|10224742|   HY411435|2015-09-05 10:55:00| 082XX S LOOMIS BLVD|0610|          BURGLARY|      FORCIBLE ENTRY|           RESIDENCE| false|   false|0614|  21|            71|      05|2015|02/10/2018 03:50:...|       Gresham|\n",
            "|10224743|   HY411629|2015-09-04 18:00:00|021XX W CHURCHILL ST|0620|          BURGLARY|      UNLAWFUL ENTRY|    RESIDENCE-GARAGE| false|   false|1434|  32|            24|      05|2015|02/10/2018 03:50:...|   Shakespeare|\n",
            "|10224744|   HY411605|2015-09-05 13:00:00|   025XX W CERMAK RD|0860|             THEFT|        RETAIL THEFT|  GROCERY FOOD STORE|  true|   false|1034|  25|            31|      06|2015|09/17/2015 11:37:...|         Ogden|\n",
            "|10224745|   HY411654|2015-09-05 11:30:00|031XX W WASHINGTO...|0320|           ROBBERY|STRONGARM - NO WE...|              STREET| false|    true|1222|  27|            27|      03|2015|02/10/2018 03:50:...|     Near West|\n",
            "|11645836|   JC212333|2016-05-01 00:25:00| 055XX S ROCKWELL ST|1153|DECEPTIVE PRACTICE|FINANCIAL IDENTIT...|                null| false|   false|0824|  15|            63|      11|2016|04/06/2019 04:04:...|  Chicago Lawn|\n",
            "|10224746|   HY411662|2015-09-05 14:00:00|  071XX S PULASKI RD|0820|             THEFT|      $500 AND UNDER|PARKING LOT/GARAG...| false|   false|0833|  13|            65|      06|2015|02/10/2018 03:50:...|  Chicago Lawn|\n",
            "|10224749|   HY411626|2015-09-05 11:00:00|052XX N MILWAUKEE...|0460|           BATTERY|              SIMPLE|  SMALL RETAIL STORE| false|   false|1623|  45|            11|     08B|2015|02/10/2018 03:50:...|Jefferson Park|\n",
            "|10224750|   HY411632|2015-09-05 03:00:00|    0000X W 103RD ST|2820|     OTHER OFFENSE|    TELEPHONE THREAT|           APARTMENT| false|    true|0512|  34|            49|      26|2015|02/10/2018 03:50:...|       Calumet|\n",
            "|10224751|   HY411566|2015-09-05 12:50:00|     013XX E 47TH ST|0486|           BATTERY|DOMESTIC BATTERY ...|              STREET| false|    true|0222|   4|            39|     08B|2015|02/10/2018 03:50:...|     Wentworth|\n",
            "|10224752|   HY411601|2015-09-03 13:00:00| 020XX W SCHILLER ST|0810|             THEFT|           OVER $500|              STREET| false|   false|1424|   1|            24|      06|2015|02/10/2018 03:50:...|   Shakespeare|\n",
            "|10224753|   HY411489|2015-09-05 11:45:00|  080XX S JUSTINE ST|0497|           BATTERY|AGGRAVATED DOMEST...|           APARTMENT| false|   false|0612|  21|            71|     04B|2015|02/10/2018 03:50:...|       Gresham|\n",
            "|10224754|   HY411656|2015-09-05 13:30:00|007XX N LEAMINGTO...|1320|   CRIMINAL DAMAGE|          TO VEHICLE|              STREET| false|   false|1531|  28|            25|      14|2015|02/10/2018 03:50:...|        Austin|\n",
            "|10224756|   HY410094|2015-07-08 00:00:00|103XX S TORRENCE AVE|0620|          BURGLARY|      UNLAWFUL ENTRY|               OTHER| false|   false|0434|  10|            51|      05|2015|02/10/2018 03:50:...| South Chicago|\n",
            "|10224757|   HY411388|2015-09-05 09:55:00|  088XX S PAULINA ST|0610|          BURGLARY|      FORCIBLE ENTRY|           RESIDENCE|  true|   false|2221|  21|            71|      05|2015|02/10/2018 03:50:...|   Morgan Park|\n",
            "|10224758|   HY411568|2015-09-05 12:35:00|    059XX W GRACE ST|0486|           BATTERY|DOMESTIC BATTERY ...|              STREET| false|    true|1633|  38|            15|     08B|2015|02/10/2018 03:50:...|Jefferson Park|\n",
            "+--------+-----------+-------------------+--------------------+----+------------------+--------------------+--------------------+------+--------+----+----+--------------+--------+----+--------------------+--------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DSSZYXhNyEHj"
      },
      "source": [
        "del small_set"
      ],
      "execution_count": 105,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tM0TjVFsglaJ"
      },
      "source": [
        "# Schema"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "x_OlaokZhrrj",
        "outputId": "db214769-72cb-4441-da58-4e6b786b5307"
      },
      "source": [
        "rc.dtypes"
      ],
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[('ID', 'string'),\n",
              " ('Case Number', 'string'),\n",
              " ('Date', 'timestamp'),\n",
              " ('Block', 'string'),\n",
              " ('IUCR', 'string'),\n",
              " ('Primary Type', 'string'),\n",
              " ('Description', 'string'),\n",
              " ('Location Description', 'string'),\n",
              " ('Arrest', 'string'),\n",
              " ('Domestic', 'string'),\n",
              " ('Beat', 'string'),\n",
              " ('District', 'string'),\n",
              " ('Ward', 'string'),\n",
              " ('Community Area', 'string'),\n",
              " ('FBI Code', 'string'),\n",
              " ('X Coordinate', 'string'),\n",
              " ('Y Coordinate', 'string'),\n",
              " ('Year', 'string'),\n",
              " ('Updated On', 'string'),\n",
              " ('Latitude', 'string'),\n",
              " ('Longitude', 'string'),\n",
              " ('Location', 'string')]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ho7FCphHx4Bh",
        "outputId": "24c824fe-cadb-4263-d82a-c50ed34efcfa"
      },
      "source": [
        "#Spark define schema com base em algumas linhas\n",
        "rc.printSchema()"
      ],
      "execution_count": 10,
      "outputs": [
        {
          "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"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "K5k4cknmYZdP",
        "outputId": "c625c11b-a3bd-460e-b419-441f0a1c3f18"
      },
      "source": [
        "rc.columns"
      ],
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "execute_result",
          "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']"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OmNZ64Vpgs3i"
      },
      "source": [
        "# Define Schema"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "poxHnEYaytDN",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "26aff797-2d50-474f-cc5d-e3311bec9a55"
      },
      "source": [
        "#definir manualmente garante a qualidade do Schema\n",
        "from pyspark.sql.types import StructType, StructField, StringType, BooleanType, DoubleType, IntegerType, TimestampType\n",
        "schema = StructType([\n",
        "            #Campo('nomedocampo',TipodoCampo,Aceita_NUll),\n",
        "            StructField('ID', StringType(),True),\n",
        "            StructField('Case Number',StringType(),True),\n",
        "            StructField('Date',TimestampType(),True),\n",
        "            StructField('Block',StringType(),True),\n",
        "            StructField('IUCR',StringType(),True),\n",
        "            StructField('Primary Type',StringType(),True),\n",
        "            StructField('Description',StringType(),True),\n",
        "            StructField('Location Description',StringType(),True),\n",
        "            StructField('Arrest',StringType(),True),\n",
        "            StructField('Domestic',BooleanType(),True),\n",
        "            StructField('Beat',StringType(),True),\n",
        "            StructField('District',StringType(),True),\n",
        "            StructField('Ward',StringType(),True),\n",
        "            StructField('Community Area',StringType(),True),\n",
        "            StructField('FBI Code',StringType(),True),\n",
        "            StructField('X Coordinate',StringType(),True),\n",
        "            StructField('Y Coordinate',StringType(),True),\n",
        "            StructField('Year',IntegerType(),True),\n",
        "            StructField('Updated On',StringType(),True),\n",
        "            StructField('Latitude',DoubleType(),True),\n",
        "            StructField('Longitude',DoubleType(),True),\n",
        "            StructField('Location',StringType(),True)\n",
        "])\n",
        "schema"
      ],
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "execute_result",
          "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)))"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9srG3AsGbRsn"
      },
      "source": [
        "rc = spark.read.csv('reported_crimes.csv', schema=schema)"
      ],
      "execution_count": 13,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "iarSCx9wfGec",
        "outputId": "88877058-631b-48ce-c861-fc7e43824072"
      },
      "source": [
        "rc.printSchema()"
      ],
      "execution_count": 14,
      "outputs": [
        {
          "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"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fGwHVO7ifP7R",
        "outputId": "cbea7315-38d8-4df4-bb0a-0cc445f2a8e8"
      },
      "source": [
        "rc.show(5)"
      ],
      "execution_count": 15,
      "outputs": [
        {
          "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"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AfwWCyLFg5im"
      },
      "source": [
        "del rc"
      ],
      "execution_count": 16,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "P5DoC5WIgxur"
      },
      "source": [
        "# Select Columns"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gkjimsPQg-vV"
      },
      "source": [
        "rc = spark.read.csv('./reported_crimes.csv',header=True).withColumn('Date',to_timestamp(col('Date'),'MM/dd/yyyy hh:mm:ss a'))"
      ],
      "execution_count": 17,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TqdcbytLhGef",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "f646d51c-2671-4d03-b1a2-f3e56c550915"
      },
      "source": [
        "rc.select('IUCR').show(5)"
      ],
      "execution_count": 18,
      "outputs": [
        {
          "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"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ZQ-YKPTA88u4",
        "outputId": "230aeae1-9e59-4901-ef81-f4279155a6eb"
      },
      "source": [
        "rc.select(rc.IUCR).show(5)"
      ],
      "execution_count": 19,
      "outputs": [
        {
          "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"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Maju2x8o9NY1",
        "outputId": "2a329ea7-eb70-4e93-9ea2-fa31f1798849"
      },
      "source": [
        "rc.select(col('IUCR')).show(5)"
      ],
      "execution_count": 20,
      "outputs": [
        {
          "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"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "VgTB7Vi-9Y7Z",
        "outputId": "085759e2-fd6d-4f72-fa91-481853070065"
      },
      "source": [
        "rc.select('Case Number','Date','Arrest').show(5)"
      ],
      "execution_count": 21,
      "outputs": [
        {
          "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",
            "|   HY411610|2015-09-05 13:00:00| false|\n",
            "+-----------+-------------------+------+\n",
            "only showing top 5 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Jdw4bxE59vm2"
      },
      "source": [
        "# Literal"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0_8pBNHU9oYQ"
      },
      "source": [
        "from pyspark.sql.functions import lit\n",
        "rc = rc.withColumn('One',lit(1))"
      ],
      "execution_count": 22,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "B18pknY4_N1g",
        "outputId": "d415b2e3-8b10-4b72-e17e-31505747cc6f"
      },
      "source": [
        "rc.select('Case Number','Date','Arrest','One').show(5)"
      ],
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----------+-------------------+------+---+\n",
            "|Case Number|               Date|Arrest|One|\n",
            "+-----------+-------------------+------+---+\n",
            "|   HY411648|2015-09-05 13:30:00| false|  1|\n",
            "|   HY411615|2015-09-04 11:30:00| false|  1|\n",
            "|   JC213529|2018-09-01 00:01:00| false|  1|\n",
            "|   HY411595|2015-09-05 12:45:00|  true|  1|\n",
            "|   HY411610|2015-09-05 13:00:00| false|  1|\n",
            "+-----------+-------------------+------+---+\n",
            "only showing top 5 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uNvBLTzl_WsC"
      },
      "source": [
        "# Drop Column"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fVSghL1__ehN",
        "outputId": "7a2e46e3-557f-42e2-dccb-acdeb34a45d2"
      },
      "source": [
        "rc = rc.drop('One')\n",
        "rc.show(5)"
      ],
      "execution_count": 24,
      "outputs": [
        {
          "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"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QOmjtHiCAlz7"
      },
      "source": [
        "# Add Row"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "EWuwl8DYF6TK"
      },
      "source": [
        "from datetime import datetime"
      ],
      "execution_count": 25,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "It3wCQVPAnBG",
        "outputId": "95e22f48-83a3-4abf-e63f-8af9d4f4c3c0"
      },
      "source": [
        "#lista com novos dados\n",
        "new_row = [['20000055','HY189684',datetime.now(),'032XX W HURON ST','0560','ROBBERY','ARMED: HANDGUN','RESIDENCE',True,True,'2523','002','4','40','08B','1154760','1928502',2021,'04/04/2021 19:51:52',41.893720636,-87.622361533,'(41.893720636,-87.622361533)']]\n",
        "#lista para spark dataframe\n",
        "new_df = spark.createDataFrame(new_row,schema=rc.schema)\n",
        "new_df.show(1)"
      ],
      "execution_count": 26,
      "outputs": [
        {
          "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",
            "|20000055|   HY189684|2021-04-11 19:00:...|032XX W HURON ST|0560|     ROBBERY|ARMED: HANDGUN|           RESIDENCE|  true|    true|2523|     002|   4|            40|     08B|     1154760|     1928502|2021|04/04/2021 19:51:52|41.893720636|-87.622361533|(41.893720636,-87...|\n",
            "+--------+-----------+--------------------+----------------+----+------------+--------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+-------------------+------------+-------------+--------------------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "diYN6_jlHmzy"
      },
      "source": [
        "#unir linha nova ao restante dos dados\n",
        "rc = rc.union(new_df)"
      ],
      "execution_count": 27,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "HNcN3HEMJCYt",
        "outputId": "dbefc333-250c-4f86-ea05-95f3c386201d"
      },
      "source": [
        "#filtrar apenas a nova linha pelo id\n",
        "rc.filter(col('ID') == lit('20000055')).show()"
      ],
      "execution_count": 28,
      "outputs": [
        {
          "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",
            "|20000055|   HY189684|2021-04-11 19:00:...|032XX W HURON ST|0560|     ROBBERY|ARMED: HANDGUN|           RESIDENCE|  true|    true|2523|     002|   4|            40|     08B|     1154760|     1928502|2021|04/04/2021 19:51:52|41.893720636|-87.622361533|(41.893720636,-87...|\n",
            "+--------+-----------+--------------------+----------------+----+------------+--------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+-------------------+------------+-------------+--------------------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "YLy8QpL1gTfE",
        "outputId": "6e1d6ef9-b4cc-453c-fcfe-812cab0ffefe"
      },
      "source": [
        "#Ordernar de forma decrescente para ver a nova linha e o restante dos dados\n",
        "rc.orderBy('ID',ascending=False).show(5)"
      ],
      "execution_count": 29,
      "outputs": [
        {
          "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",
            "|9999999|   HY189683|2015-03-18 17:08:00| 043XX N CENTRAL AVE|0560|     ASSAULT|              SIMPLE|          RESTAURANT|  true|   false|1624|     016|  38|            15|     08A|     1138222|     1928502|2015|02/10/2018 03:50:...|41.959943835|-87.767225719|(41.959943835, -8...|\n",
            "|9999998|   HY188874|2015-03-18 04:00:00| 036XX W BELMONT AVE|0460|     BATTERY|              SIMPLE|           RESIDENCE| false|   false|2523|     025|  35|            21|     08B|     1151659|     1921038|2015|02/10/2018 03:50:...|41.939207846|-87.718021657|(41.939207846, -8...|\n",
            "|9999997|   HY189887|2015-03-18 19:55:00|    032XX W HURON ST|0486|     BATTERY|DOMESTIC BATTERY ...|RESIDENCE PORCH/H...| false|    true|1121|     011|  27|            23|     08B|     1154760|     1904484|2015|02/10/2018 03:50:...|41.893720636|-87.707068862|(41.893720636, -8...|\n",
            "|9999996|   HY188806|2015-03-17 23:15:00|     011XX E 46TH ST|031A|     ROBBERY|      ARMED: HANDGUN|           APARTMENT| false|   false|0222|     002|   4|            39|      03|     1184597|     1874738|2015|02/10/2018 03:50:...|41.811446152|-87.598423885|(41.811446152, -8...|\n",
            "|9999992|   HY189647|2015-03-18 16:55:00|057XX S MICHIGAN AVE|041A|     BATTERY| AGGRAVATED: HANDGUN|            SIDEWALK| false|   false|0232|     002|  20|            40|     04B|     1178134|     1867092|2015|02/10/2018 03:50:...|41.790614044|-87.622361533|(41.790614044, -8...|\n",
            "+-------+-----------+-------------------+--------------------+----+------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+\n",
            "only showing top 5 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5SX484sVAnbY"
      },
      "source": [
        "# Top 10 crimes por tipo"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "K1K6KP9zAn5w",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "bcf372fd-1405-4501-d35c-26fa0e3655eb"
      },
      "source": [
        "rc.groupBy('Primary Type').count().orderBy('count',ascending=False).show(10)"
      ],
      "execution_count": 30,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-------------------+-------+\n",
            "|       Primary Type|  count|\n",
            "+-------------------+-------+\n",
            "|              THEFT|1538553|\n",
            "|            BATTERY|1337949|\n",
            "|    CRIMINAL DAMAGE| 831752|\n",
            "|          NARCOTICS| 737218|\n",
            "|            ASSAULT| 464133|\n",
            "|      OTHER OFFENSE| 453448|\n",
            "|           BURGLARY| 409215|\n",
            "|MOTOR VEHICLE THEFT| 336770|\n",
            "| DECEPTIVE PRACTICE| 310298|\n",
            "|            ROBBERY| 274557|\n",
            "+-------------------+-------+\n",
            "only showing top 10 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zml2xZbnAogs"
      },
      "source": [
        "# Percentual de crimes reportados que resultaram em prisão"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "As1I8DuHz21-",
        "outputId": "c45c9cf9-c14f-40c3-965b-761923894917"
      },
      "source": [
        "rc.select('Arrest').distinct().show()"
      ],
      "execution_count": 31,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+------+\n",
            "|Arrest|\n",
            "+------+\n",
            "| false|\n",
            "|  true|\n",
            "+------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2862kDVbApGq",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "960fa77d-9276-42f5-e0c0-261ebae433d9"
      },
      "source": [
        "((rc.filter(col('Arrest') == 'true').count())/rc.count())*100"
      ],
      "execution_count": 32,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "27.065234531992786"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 32
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mJsUMG95rKai"
      },
      "source": [
        "# Top 3 lugares para sofrer um crime"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "_kL3gPEYrE3m",
        "outputId": "3bbe7aea-5c2b-4a47-9fb8-39f2cb6e549c"
      },
      "source": [
        "rc.groupBy('Location Description').count().orderBy('count',ascending=False).show(3)"
      ],
      "execution_count": 33,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------------+-------+\n",
            "|Location Description|  count|\n",
            "+--------------------+-------+\n",
            "|              STREET|1896337|\n",
            "|           RESIDENCE|1241265|\n",
            "|           APARTMENT| 784397|\n",
            "+--------------------+-------+\n",
            "only showing top 3 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nYr3_9Gsr4_X"
      },
      "source": [
        "# Top 3 quarteirões dos narcoticos"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "jAbf_d8Xsvda",
        "outputId": "fb64af00-6c3c-4419-891c-d3e2c903744d"
      },
      "source": [
        "rc.filter(col('Primary Type')==lit('NARCOTICS')).groupBy('Block').count().orderBy('count',ascending=False).show(3)"
      ],
      "execution_count": 34,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------------+-----+\n",
            "|               Block|count|\n",
            "+--------------------+-----+\n",
            "| 033XX W FILLMORE ST| 3081|\n",
            "|005XX E BROWNING AVE| 1706|\n",
            "|038XX W ROOSEVELT RD| 1613|\n",
            "+--------------------+-----+\n",
            "only showing top 3 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zfs0SBHZsfYb"
      },
      "source": [
        "# O dia para ficar em casa"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "WO3GFNl0uBNw",
        "outputId": "e201a0c2-b72a-4673-eea5-2624742b0f94"
      },
      "source": [
        "rc.groupBy('Date').count().orderBy('count',ascending=False).show(1)"
      ],
      "execution_count": 35,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-------------------+-----+\n",
            "|               Date|count|\n",
            "+-------------------+-----+\n",
            "|2008-01-01 00:01:00|  303|\n",
            "+-------------------+-----+\n",
            "only showing top 1 row\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xjenMszP1YRm"
      },
      "source": [
        "# Some functions"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "sK-GLL3e1ZMB"
      },
      "source": [
        "from pyspark.sql.functions import lower,upper,substring,concat"
      ],
      "execution_count": 36,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1gGS2GF2Ae1a",
        "outputId": "9e9fb8f3-584c-4c04-bf5e-f8f456fd11aa"
      },
      "source": [
        "rc.select(lower(col('Primary Type'))).show(5)"
      ],
      "execution_count": 37,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-------------------+\n",
            "|lower(Primary Type)|\n",
            "+-------------------+\n",
            "|            battery|\n",
            "|              theft|\n",
            "|              theft|\n",
            "|          narcotics|\n",
            "|            assault|\n",
            "+-------------------+\n",
            "only showing top 5 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "B_gM9MalAduJ",
        "outputId": "6a919780-d18b-4a32-bc95-720f171dea32"
      },
      "source": [
        "rc.select(upper(col('Arrest'))).show(5)"
      ],
      "execution_count": 38,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-------------+\n",
            "|upper(Arrest)|\n",
            "+-------------+\n",
            "|        FALSE|\n",
            "|        FALSE|\n",
            "|        FALSE|\n",
            "|         TRUE|\n",
            "|        FALSE|\n",
            "+-------------+\n",
            "only showing top 5 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5CJDJdPO1tQG",
        "outputId": "3f3fec3b-c2dd-49ac-cf2b-0c04e09485cc"
      },
      "source": [
        "rc.select(substring(col('Description'),1,4)).show(5)"
      ],
      "execution_count": 39,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+----------------------------+\n",
            "|substring(Description, 1, 4)|\n",
            "+----------------------------+\n",
            "|                        DOME|\n",
            "|                        POCK|\n",
            "|                        OVER|\n",
            "|                        POSS|\n",
            "|                        SIMP|\n",
            "+----------------------------+\n",
            "only showing top 5 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-ANiCe11BHyL",
        "outputId": "acfa6be7-891d-4e95-ccc9-bd920e0a5cc4"
      },
      "source": [
        "rc.select(lower(col('Primary Type')),upper(col('Arrest')),substring(col('Description'),1,4)).show(5)"
      ],
      "execution_count": 40,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-------------------+-------------+----------------------------+\n",
            "|lower(Primary Type)|upper(Arrest)|substring(Description, 1, 4)|\n",
            "+-------------------+-------------+----------------------------+\n",
            "|            battery|        FALSE|                        DOME|\n",
            "|              theft|        FALSE|                        POCK|\n",
            "|              theft|        FALSE|                        OVER|\n",
            "|          narcotics|         TRUE|                        POSS|\n",
            "|            assault|        FALSE|                        SIMP|\n",
            "+-------------------+-------------+----------------------------+\n",
            "only showing top 5 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "a6dO8GjJCLNN",
        "outputId": "3e3eab01-cda4-4918-c434-30c81392b50b"
      },
      "source": [
        "rc.select(concat(lower(substring(col('Primary Type'),1,3)),lower(substring(col('Location Description'),3,6))).alias(\"random_word\")).show(5)"
      ],
      "execution_count": 41,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----------+\n",
            "|random_word|\n",
            "+-----------+\n",
            "|  batsidenc|\n",
            "|   thea bus|\n",
            "|  thesidenc|\n",
            "|  nardewalk|\n",
            "|  assartmen|\n",
            "+-----------+\n",
            "only showing top 5 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "41jevt9R6gWJ"
      },
      "source": [
        "from pyspark.sql.functions import min,max"
      ],
      "execution_count": 42,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "H3uYcEzG6bXp",
        "outputId": "0be5c9a0-958f-4bbf-d4a7-3c41c95dd2fe"
      },
      "source": [
        "rc.select(min(col('Date')),max(col('Date'))).show()"
      ],
      "execution_count": 43,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-------------------+--------------------+\n",
            "|          min(Date)|           max(Date)|\n",
            "+-------------------+--------------------+\n",
            "|2001-01-01 00:00:00|2021-04-11 19:00:...|\n",
            "+-------------------+--------------------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Qw6XzDfT7wwK"
      },
      "source": [
        "from pyspark.sql.functions import date_add,date_sub"
      ],
      "execution_count": 44,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "kyASpkJD7bb9",
        "outputId": "32f76975-2de3-4452-c6f8-a60fab144f93"
      },
      "source": [
        "rc.select(date_sub(min(col('Date')),3).alias(\"Min_date-3\"),date_add(max(col('Date')),3).alias(\"Max_date+3\")).show()"
      ],
      "execution_count": 45,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+----------+----------+\n",
            "|Min_date-3|Max_date+3|\n",
            "+----------+----------+\n",
            "|2000-12-29|2021-04-14|\n",
            "+----------+----------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "q_EW9f-XGwv3"
      },
      "source": [
        "# Date"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cNh8WBXD8ygX"
      },
      "source": [
        "from pyspark.sql.functions import to_date, to_timestamp, date_format"
      ],
      "execution_count": 46,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "YweCx2RQHI3b",
        "outputId": "a258e174-b3cb-40eb-8083-2ac9ccb3f25a"
      },
      "source": [
        "#Parse\n",
        "rc.select(col('Date'), \\\n",
        "          to_date(col('Date'),'yyyy-MM-dd HH:mm:ss'), \\\n",
        "          to_timestamp(col('Date'),'yyyy-MM-dd HH:mm:ss'), \\\n",
        "          ).show()"
      ],
      "execution_count": 47,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-------------------+----------------------------------+---------------------------------------+\n",
            "|               Date|to_date(Date, yyyy-MM-dd HH:mm:ss)|to_timestamp(Date, yyyy-MM-dd HH:mm:ss)|\n",
            "+-------------------+----------------------------------+---------------------------------------+\n",
            "|2015-09-05 13:30:00|                        2015-09-05|                    2015-09-05 13:30:00|\n",
            "|2015-09-04 11:30:00|                        2015-09-04|                    2015-09-04 11:30:00|\n",
            "|2018-09-01 00:01:00|                        2018-09-01|                    2018-09-01 00:01:00|\n",
            "|2015-09-05 12:45:00|                        2015-09-05|                    2015-09-05 12:45:00|\n",
            "|2015-09-05 13:00:00|                        2015-09-05|                    2015-09-05 13:00:00|\n",
            "|2015-09-05 10:55:00|                        2015-09-05|                    2015-09-05 10:55:00|\n",
            "|2015-09-04 18:00:00|                        2015-09-04|                    2015-09-04 18:00:00|\n",
            "|2015-09-05 13:00:00|                        2015-09-05|                    2015-09-05 13:00:00|\n",
            "|2015-09-05 11:30:00|                        2015-09-05|                    2015-09-05 11:30:00|\n",
            "|2016-05-01 00:25:00|                        2016-05-01|                    2016-05-01 00:25:00|\n",
            "|2015-09-05 14:00:00|                        2015-09-05|                    2015-09-05 14:00:00|\n",
            "|2015-09-05 11:00:00|                        2015-09-05|                    2015-09-05 11:00:00|\n",
            "|2015-09-05 03:00:00|                        2015-09-05|                    2015-09-05 03:00:00|\n",
            "|2015-09-05 12:50:00|                        2015-09-05|                    2015-09-05 12:50:00|\n",
            "|2015-09-03 13:00:00|                        2015-09-03|                    2015-09-03 13:00:00|\n",
            "|2015-09-05 11:45:00|                        2015-09-05|                    2015-09-05 11:45:00|\n",
            "|2015-09-05 13:30:00|                        2015-09-05|                    2015-09-05 13:30:00|\n",
            "|2015-07-08 00:00:00|                        2015-07-08|                    2015-07-08 00:00:00|\n",
            "|2015-09-05 09:55:00|                        2015-09-05|                    2015-09-05 09:55:00|\n",
            "|2015-09-05 12:35:00|                        2015-09-05|                    2015-09-05 12:35:00|\n",
            "+-------------------+----------------------------------+---------------------------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6QXKgbiEIzBu",
        "outputId": "a4b0ce0d-2902-442d-d433-3783b38a866b"
      },
      "source": [
        "#Format\n",
        "rc.select(col('Date'), \\\n",
        "          date_format(col('Date'),'G').alias('Era'), \\\n",
        "          date_format(col('Date'),'y').alias('Year'), \\\n",
        "          date_format(col('Date'),'M').alias('Month in year'), \\\n",
        "          date_format(col('Date'),'D').alias('Day in year'), \\\n",
        "          date_format(col('Date'),'d').alias('Day in month'), \\\n",
        "          date_format(col('Date'),'E').alias('Day name in week'), \\\n",
        "          date_format(col('Date'),'a').alias('AM/PM'), \\\n",
        "          date_format(col('Date'),'H').alias('Hour (0-23)'), \\\n",
        "          date_format(col('Date'),'k').alias('Hour (1-24)'), \\\n",
        "          date_format(col('Date'),'K').alias('Hour AM/PM (0-11)'), \\\n",
        "          date_format(col('Date'),'h').alias('Hour AM/PM (1-12)'), \\\n",
        "          date_format(col('Date'),'m').alias('Minute'), \\\n",
        "          date_format(col('Date'),'s').alias('Second'), \\\n",
        "          date_format(col('Date'),'S').alias('milisecond'), \\\n",
        "          date_format(col('Date'),'z').alias('Timezone'), \\\n",
        "          ).show()"
      ],
      "execution_count": 48,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-------------------+---+----+-------------+-----------+------------+----------------+-----+-----------+-----------+-----------------+-----------------+------+------+----------+--------+\n",
            "|               Date|Era|Year|Month in year|Day in year|Day in month|Day name in week|AM/PM|Hour (0-23)|Hour (1-24)|Hour AM/PM (0-11)|Hour AM/PM (1-12)|Minute|Second|milisecond|Timezone|\n",
            "+-------------------+---+----+-------------+-----------+------------+----------------+-----+-----------+-----------+-----------------+-----------------+------+------+----------+--------+\n",
            "|2015-09-05 13:30:00| AD|2015|            9|        248|           5|             Sat|   PM|         13|         13|                1|                1|    30|     0|         0|     UTC|\n",
            "|2015-09-04 11:30:00| AD|2015|            9|        247|           4|             Fri|   AM|         11|         11|               11|               11|    30|     0|         0|     UTC|\n",
            "|2018-09-01 00:01:00| AD|2018|            9|        244|           1|             Sat|   AM|          0|         24|                0|               12|     1|     0|         0|     UTC|\n",
            "|2015-09-05 12:45:00| AD|2015|            9|        248|           5|             Sat|   PM|         12|         12|                0|               12|    45|     0|         0|     UTC|\n",
            "|2015-09-05 13:00:00| AD|2015|            9|        248|           5|             Sat|   PM|         13|         13|                1|                1|     0|     0|         0|     UTC|\n",
            "|2015-09-05 10:55:00| AD|2015|            9|        248|           5|             Sat|   AM|         10|         10|               10|               10|    55|     0|         0|     UTC|\n",
            "|2015-09-04 18:00:00| AD|2015|            9|        247|           4|             Fri|   PM|         18|         18|                6|                6|     0|     0|         0|     UTC|\n",
            "|2015-09-05 13:00:00| AD|2015|            9|        248|           5|             Sat|   PM|         13|         13|                1|                1|     0|     0|         0|     UTC|\n",
            "|2015-09-05 11:30:00| AD|2015|            9|        248|           5|             Sat|   AM|         11|         11|               11|               11|    30|     0|         0|     UTC|\n",
            "|2016-05-01 00:25:00| AD|2016|            5|        122|           1|             Sun|   AM|          0|         24|                0|               12|    25|     0|         0|     UTC|\n",
            "|2015-09-05 14:00:00| AD|2015|            9|        248|           5|             Sat|   PM|         14|         14|                2|                2|     0|     0|         0|     UTC|\n",
            "|2015-09-05 11:00:00| AD|2015|            9|        248|           5|             Sat|   AM|         11|         11|               11|               11|     0|     0|         0|     UTC|\n",
            "|2015-09-05 03:00:00| AD|2015|            9|        248|           5|             Sat|   AM|          3|          3|                3|                3|     0|     0|         0|     UTC|\n",
            "|2015-09-05 12:50:00| AD|2015|            9|        248|           5|             Sat|   PM|         12|         12|                0|               12|    50|     0|         0|     UTC|\n",
            "|2015-09-03 13:00:00| AD|2015|            9|        246|           3|             Thu|   PM|         13|         13|                1|                1|     0|     0|         0|     UTC|\n",
            "|2015-09-05 11:45:00| AD|2015|            9|        248|           5|             Sat|   AM|         11|         11|               11|               11|    45|     0|         0|     UTC|\n",
            "|2015-09-05 13:30:00| AD|2015|            9|        248|           5|             Sat|   PM|         13|         13|                1|                1|    30|     0|         0|     UTC|\n",
            "|2015-07-08 00:00:00| AD|2015|            7|        189|           8|             Wed|   AM|          0|         24|                0|               12|     0|     0|         0|     UTC|\n",
            "|2015-09-05 09:55:00| AD|2015|            9|        248|           5|             Sat|   AM|          9|          9|                9|                9|    55|     0|         0|     UTC|\n",
            "|2015-09-05 12:35:00| AD|2015|            9|        248|           5|             Sat|   PM|         12|         12|                0|               12|    35|     0|         0|     UTC|\n",
            "+-------------------+---+----+-------------+-----------+------------+----------------+-----+-----------+-----------+-----------------+-----------------+------+------+----------+--------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "DbqQK-C9ONuB",
        "outputId": "25ed39c3-cd7a-404b-b031-0ed535c2d348"
      },
      "source": [
        "rc.select(col('Date'),date_format(col('Date'),'d/MMM/yy hh:mm a')).show(truncate=False)"
      ],
      "execution_count": 49,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-------------------+-----------------------------------+\n",
            "|Date               |date_format(Date, d/MMM/yy hh:mm a)|\n",
            "+-------------------+-----------------------------------+\n",
            "|2015-09-05 13:30:00|5/Sep/15 01:30 PM                  |\n",
            "|2015-09-04 11:30:00|4/Sep/15 11:30 AM                  |\n",
            "|2018-09-01 00:01:00|1/Sep/18 12:01 AM                  |\n",
            "|2015-09-05 12:45:00|5/Sep/15 12:45 PM                  |\n",
            "|2015-09-05 13:00:00|5/Sep/15 01:00 PM                  |\n",
            "|2015-09-05 10:55:00|5/Sep/15 10:55 AM                  |\n",
            "|2015-09-04 18:00:00|4/Sep/15 06:00 PM                  |\n",
            "|2015-09-05 13:00:00|5/Sep/15 01:00 PM                  |\n",
            "|2015-09-05 11:30:00|5/Sep/15 11:30 AM                  |\n",
            "|2016-05-01 00:25:00|1/May/16 12:25 AM                  |\n",
            "|2015-09-05 14:00:00|5/Sep/15 02:00 PM                  |\n",
            "|2015-09-05 11:00:00|5/Sep/15 11:00 AM                  |\n",
            "|2015-09-05 03:00:00|5/Sep/15 03:00 AM                  |\n",
            "|2015-09-05 12:50:00|5/Sep/15 12:50 PM                  |\n",
            "|2015-09-03 13:00:00|3/Sep/15 01:00 PM                  |\n",
            "|2015-09-05 11:45:00|5/Sep/15 11:45 AM                  |\n",
            "|2015-09-05 13:30:00|5/Sep/15 01:30 PM                  |\n",
            "|2015-07-08 00:00:00|8/Jul/15 12:00 AM                  |\n",
            "|2015-09-05 09:55:00|5/Sep/15 09:55 AM                  |\n",
            "|2015-09-05 12:35:00|5/Sep/15 12:35 PM                  |\n",
            "+-------------------+-----------------------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XYMr5W2V2T2r"
      },
      "source": [
        "# Joins"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8MI2nzVy5zcT",
        "outputId": "0099706f-e9a0-417a-da43-35de8eb9e3cb"
      },
      "source": [
        "!wget https://data.cityofchicago.org/api/views/z8bn-74gv/rows.csv?accessType=DOWNLOAD"
      ],
      "execution_count": 50,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "--2021-04-11 19:05:06--  https://data.cityofchicago.org/api/views/z8bn-74gv/rows.csv?accessType=DOWNLOAD\n",
            "Resolving data.cityofchicago.org (data.cityofchicago.org)... 52.206.140.199, 52.206.68.26, 52.206.140.205\n",
            "Connecting to data.cityofchicago.org (data.cityofchicago.org)|52.206.140.199|:443... connected.\n",
            "HTTP request sent, awaiting response... 200 OK\n",
            "Length: unspecified [text/csv]\n",
            "Saving to: ‘rows.csv?accessType=DOWNLOAD’\n",
            "\n",
            "rows.csv?accessType     [ <=>                ]   5.57K  --.-KB/s    in 0s      \n",
            "\n",
            "2021-04-11 19:05:06 (367 MB/s) - ‘rows.csv?accessType=DOWNLOAD’ saved [5699]\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hBqVpYLd6PsG"
      },
      "source": [
        "!mv /content/rows.csv?accessType=DOWNLOAD /content/police_stations.csv"
      ],
      "execution_count": 51,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "x8KW3d126bGa"
      },
      "source": [
        "ps = spark.read.csv('/content/police_stations.csv',header=True)"
      ],
      "execution_count": 52,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "zBBrvcaK_bkG",
        "outputId": "bc92a842-9394-4950-de17-68514b7ae226"
      },
      "source": [
        "ps.show()"
      ],
      "execution_count": 53,
      "outputs": [
        {
          "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",
            "|                  24|      Rogers Park|     6464 N Clark St|Chicago|   IL|60626|http://home.chica...|312-744-5907|312-744-6928|312-744-7603| 1164193.588| 1943199.401|41.99976348|-87.67132429|(41.9997634842, -...|\n",
            "|                  25|    Grand Central|    5555 W Grand Ave|Chicago|   IL|60639|http://home.chica...|312-746-8605|312-746-4353|312-746-8383| 1138770.871| 1913442.439|41.91860889|-87.76557448|(41.9186088912, -...|\n",
            "|                   1|          Central|     1718 S State St|Chicago|   IL|60616|http://home.chica...|312-745-4290|312-745-3694|312-745-3693| 1176569.052| 1891771.704|41.85837259|-87.62735617|(41.8583725929, -...|\n",
            "|                   2|        Wentworth|5101 S Wentworth Ave|Chicago|   IL|60609|http://home.chica...|312-747-8366|312-747-5396|312-747-6656| 1175864.837| 1871153.753|41.80181109|-87.63056018|(41.8018110912, -...|\n",
            "|                   3|   Grand Crossing|7040 S Cottage Gr...|Chicago|   IL|60637|http://home.chica...|312-747-8201|312-747-5479|312-747-9168| 1182739.183| 1858317.732|41.76643089|-87.60574786|(41.7664308925, -...|\n",
            "|                   4|    South Chicago|     2255 E 103rd St|Chicago|   IL|60617|http://home.chica...|312-747-7581|312-747-5276|312-747-9169| 1193131.299| 1837090.265|41.70793329|-87.56834912|(41.7079332906, -...|\n",
            "|                   5|          Calumet|      727 E 111th St|Chicago|   IL|60628|http://home.chica...|312-747-8210|312-747-5935|312-747-9170| 1183305.427| 1831462.313|41.69272336|-87.60450587|(41.6927233639, -...|\n",
            "|                   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, -...|\n",
            "|                   7|        Englewood|      1438 W 63rd St|Chicago|   IL|60636|http://home.chica...|312-747-8223|312-747-6558|312-747-6652| 1167659.235| 1863005.522|41.77963154|-87.66088702|(41.7796315359, -...|\n",
            "|                   8|     Chicago Lawn|      3420 W 63rd St|Chicago|   IL|60629|http://home.chica...|312-747-8730|312-747-8545|312-747-8116| 1154575.242| 1862672.049|41.77898719|-87.70886382|(41.778987189, -8...|\n",
            "|                   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, -...|\n",
            "|                  10|            Ogden|    3315 W Ogden Ave|Chicago|   IL|60623|http://home.chica...|312-747-7511|312-747-7429|312-747-7471| 1154500.753| 1890985.501|41.85668453|-87.70838196|(41.8566845327, -...|\n",
            "|                  11|         Harrison|  3151 W Harrison St|Chicago|   IL|60612|http://home.chica...|312-746-8386|312-746-4281|312-746-5151| 1155244.069| 1897148.755|41.87358229|-87.70548813|(41.8735822883, -...|\n",
            "|                  12|        Near West|1412 S Blue Islan...|   null| null| null|                null|        null|        null|        null|        null|        null|       null|        null|                null|\n",
            "|\",Chicago,IL,6060...| -87.6569725149)\"|                null|   null| null| null|                null|        null|        null|        null|        null|        null|       null|        null|                null|\n",
            "+--------------------+-----------------+--------------------+-------+-----+-----+--------------------+------------+------------+------------+------------+------------+-----------+------------+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "T3wcoEb-wJMc",
        "outputId": "07e4192c-fb78-41c9-c375-e86ceae14ad8"
      },
      "source": [
        "ps.cache()\n",
        "ps.count()"
      ],
      "execution_count": 54,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "24"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 54
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0nRx2umjUQ26",
        "outputId": "f94f4038-955e-405b-b180-6d9f654003ee"
      },
      "source": [
        "ps.select(col('DISTRICT NAME'),col('DISTRICT')).distinct().show()"
      ],
      "execution_count": 55,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----------------+--------------------+\n",
            "|    DISTRICT NAME|            DISTRICT|\n",
            "+-----------------+--------------------+\n",
            "|    South Chicago|                   4|\n",
            "|        Near West|                  12|\n",
            "| -87.6569725149)\"|\",Chicago,IL,6060...|\n",
            "|         Harrison|                  11|\n",
            "|      Shakespeare|                  14|\n",
            "|          Gresham|                   6|\n",
            "|    Grand Central|                  25|\n",
            "|        Town Hall|                  19|\n",
            "|     Headquarters|        Headquarters|\n",
            "|      Rogers Park|                  24|\n",
            "|        Wentworth|                   2|\n",
            "|          Deering|                   9|\n",
            "|          Calumet|                   5|\n",
            "|       Near North|                  18|\n",
            "|     Chicago Lawn|                   8|\n",
            "|        Englewood|                   7|\n",
            "|   Grand Crossing|                   3|\n",
            "|      Morgan Park|                  22|\n",
            "|   Jefferson Park|                  16|\n",
            "|            Ogden|                  10|\n",
            "+-----------------+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5zreCoRyv_I2",
        "outputId": "24166d0f-0385-487f-8d24-4fa7009263d5"
      },
      "source": [
        "rc.cache()\n",
        "rc.count()"
      ],
      "execution_count": 56,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "7305272"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 56
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "sLQsx7YJxI-C",
        "outputId": "b4065d26-854b-451f-c648-0667b29c399e"
      },
      "source": [
        "rc.select(col('District')).distinct().show()"
      ],
      "execution_count": 57,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------+\n",
            "|District|\n",
            "+--------+\n",
            "|     009|\n",
            "|     012|\n",
            "|     024|\n",
            "|    null|\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",
            "+--------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "J0fVeseHxbrs"
      },
      "source": [
        "from pyspark.sql.functions import lpad"
      ],
      "execution_count": 58,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YSRwQrBmyv0C"
      },
      "source": [
        "rc = rc.withColumn('JOIN_DISTRICT',lpad(col('District'),3,'0'))"
      ],
      "execution_count": 59,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "du6k5et5z5Me",
        "outputId": "012229e2-5462-4575-c5b4-7a47656a0ff9"
      },
      "source": [
        "rc.show()"
      ],
      "execution_count": 60,
      "outputs": [
        {
          "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|JOIN_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...|          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...|          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|          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...|          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...|          015|\n",
            "|10224742|   HY411435|2015-09-05 10:55:00| 082XX S LOOMIS BLVD|0610|          BURGLARY|      FORCIBLE ENTRY|           RESIDENCE| false|   false|0614|     006|  21|            71|      05|     1168430|     1850165|2015|02/10/2018 03:50:...|41.744378879|-87.658430635|(41.744378879, -8...|          006|\n",
            "|10224743|   HY411629|2015-09-04 18:00:00|021XX W CHURCHILL ST|0620|          BURGLARY|      UNLAWFUL ENTRY|    RESIDENCE-GARAGE| false|   false|1434|     014|  32|            24|      05|     1161628|     1912157|2015|02/10/2018 03:50:...|41.914635603|-87.681630909|(41.914635603, -8...|          014|\n",
            "|10224744|   HY411605|2015-09-05 13:00:00|   025XX W CERMAK RD|0860|             THEFT|        RETAIL THEFT|  GROCERY FOOD STORE|  true|   false|1034|     010|  25|            31|      06|     1159734|     1889313|2015|09/17/2015 11:37:...|41.851988885|-87.689219118|(41.851988885, -8...|          010|\n",
            "|10224745|   HY411654|2015-09-05 11:30:00|031XX W WASHINGTO...|0320|           ROBBERY|STRONGARM - NO WE...|              STREET| false|    true|1222|     012|  27|            27|      03|     1155536|     1900515|2015|02/10/2018 03:50:...| 41.88281374|-87.704325717|(41.88281374, -87...|          012|\n",
            "|11645836|   JC212333|2016-05-01 00:25:00| 055XX S ROCKWELL ST|1153|DECEPTIVE PRACTICE|FINANCIAL IDENTIT...|                null| false|   false|0824|     008|  15|            63|      11|        null|        null|2016|04/06/2019 04:04:...|        null|         null|                null|          008|\n",
            "|10224746|   HY411662|2015-09-05 14:00:00|  071XX S PULASKI RD|0820|             THEFT|      $500 AND UNDER|PARKING LOT/GARAG...| false|   false|0833|     008|  13|            65|      06|     1150938|     1857056|2015|02/10/2018 03:50:...|41.763647552|-87.722344693|(41.763647552, -8...|          008|\n",
            "|10224749|   HY411626|2015-09-05 11:00:00|052XX N MILWAUKEE...|0460|           BATTERY|              SIMPLE|  SMALL RETAIL STORE| false|   false|1623|     016|  45|            11|     08B|     1137969|     1934340|2015|02/10/2018 03:50:...|41.975968415|-87.768014257|(41.975968415, -8...|          016|\n",
            "|10224750|   HY411632|2015-09-05 03:00:00|    0000X W 103RD ST|2820|     OTHER OFFENSE|    TELEPHONE THREAT|           APARTMENT| false|    true|0512|     005|  34|            49|      26|     1177871|     1836676|2015|02/10/2018 03:50:...|41.707154919|-87.624244993|(41.707154919, -8...|          005|\n",
            "|10224751|   HY411566|2015-09-05 12:50:00|     013XX E 47TH ST|0486|           BATTERY|DOMESTIC BATTERY ...|              STREET| false|    true|0222|     002|   4|            39|     08B|     1185907|     1874105|2015|02/10/2018 03:50:...|41.809678314|-87.593638934|(41.809678314, -8...|          002|\n",
            "|10224752|   HY411601|2015-09-03 13:00:00| 020XX W SCHILLER ST|0810|             THEFT|           OVER $500|              STREET| false|   false|1424|     014|   1|            24|      06|     1162574|     1909428|2015|02/10/2018 03:50:...|41.907127255|-87.678232016|(41.907127255, -8...|          014|\n",
            "|10224753|   HY411489|2015-09-05 11:45:00|  080XX S JUSTINE ST|0497|           BATTERY|AGGRAVATED DOMEST...|           APARTMENT| false|   false|0612|     006|  21|            71|     04B|     1167400|     1851512|2015|02/10/2018 03:50:...|41.748097343|-87.662166183|(41.748097343, -8...|          006|\n",
            "|10224754|   HY411656|2015-09-05 13:30:00|007XX N LEAMINGTO...|1320|   CRIMINAL DAMAGE|          TO VEHICLE|              STREET| false|   false|1531|     015|  28|            25|      14|     1141889|     1904448|2015|02/10/2018 03:50:...|41.893869916|-87.754341096|(41.893869916, -8...|          015|\n",
            "|10224756|   HY410094|2015-07-08 00:00:00|103XX S TORRENCE AVE|0620|          BURGLARY|      UNLAWFUL ENTRY|               OTHER| false|   false|0434|     004|  10|            51|      05|     1195508|     1836950|2015|02/10/2018 03:50:...|41.707490122|-87.559650325|(41.707490122, -8...|          004|\n",
            "|10224757|   HY411388|2015-09-05 09:55:00|  088XX S PAULINA ST|0610|          BURGLARY|      FORCIBLE ENTRY|           RESIDENCE|  true|   false|2221|     022|  21|            71|      05|     1166554|     1846067|2015|02/10/2018 03:50:...|41.733173536|-87.665421067|(41.733173536, -8...|          022|\n",
            "|10224758|   HY411568|2015-09-05 12:35:00|    059XX W GRACE ST|0486|           BATTERY|DOMESTIC BATTERY ...|              STREET| false|    true|1633|     016|  38|            15|     08B|     1136014|     1924656|2015|02/10/2018 03:50:...|41.949429769|-87.775435529|(41.949429769, -8...|          016|\n",
            "+--------+-----------+-------------------+--------------------+----+------------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+-------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Cws1OIKPzHfc",
        "outputId": "775340c8-49d8-461c-e337-9f9945f90c8a"
      },
      "source": [
        "ps = ps.withColumn('JOIN_DISTRICT',lpad(col('DISTRICT'),3,'0'))\n",
        "ps.show()"
      ],
      "execution_count": 61,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------------+-----------------+--------------------+-------+-----+-----+--------------------+------------+------------+------------+------------+------------+-----------+------------+--------------------+-------------+\n",
            "|            DISTRICT|    DISTRICT NAME|             ADDRESS|   CITY|STATE|  ZIP|             WEBSITE|       PHONE|         FAX|         TTY|X COORDINATE|Y COORDINATE|   LATITUDE|   LONGITUDE|            LOCATION|JOIN_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|\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|\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|\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|\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|\n",
            "|                  24|      Rogers Park|     6464 N Clark St|Chicago|   IL|60626|http://home.chica...|312-744-5907|312-744-6928|312-744-7603| 1164193.588| 1943199.401|41.99976348|-87.67132429|(41.9997634842, -...|          024|\n",
            "|                  25|    Grand Central|    5555 W Grand Ave|Chicago|   IL|60639|http://home.chica...|312-746-8605|312-746-4353|312-746-8383| 1138770.871| 1913442.439|41.91860889|-87.76557448|(41.9186088912, -...|          025|\n",
            "|                   1|          Central|     1718 S State St|Chicago|   IL|60616|http://home.chica...|312-745-4290|312-745-3694|312-745-3693| 1176569.052| 1891771.704|41.85837259|-87.62735617|(41.8583725929, -...|          001|\n",
            "|                   2|        Wentworth|5101 S Wentworth Ave|Chicago|   IL|60609|http://home.chica...|312-747-8366|312-747-5396|312-747-6656| 1175864.837| 1871153.753|41.80181109|-87.63056018|(41.8018110912, -...|          002|\n",
            "|                   3|   Grand Crossing|7040 S Cottage Gr...|Chicago|   IL|60637|http://home.chica...|312-747-8201|312-747-5479|312-747-9168| 1182739.183| 1858317.732|41.76643089|-87.60574786|(41.7664308925, -...|          003|\n",
            "|                   4|    South Chicago|     2255 E 103rd St|Chicago|   IL|60617|http://home.chica...|312-747-7581|312-747-5276|312-747-9169| 1193131.299| 1837090.265|41.70793329|-87.56834912|(41.7079332906, -...|          004|\n",
            "|                   5|          Calumet|      727 E 111th St|Chicago|   IL|60628|http://home.chica...|312-747-8210|312-747-5935|312-747-9170| 1183305.427| 1831462.313|41.69272336|-87.60450587|(41.6927233639, -...|          005|\n",
            "|                   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",
            "|                   7|        Englewood|      1438 W 63rd St|Chicago|   IL|60636|http://home.chica...|312-747-8223|312-747-6558|312-747-6652| 1167659.235| 1863005.522|41.77963154|-87.66088702|(41.7796315359, -...|          007|\n",
            "|                   8|     Chicago Lawn|      3420 W 63rd St|Chicago|   IL|60629|http://home.chica...|312-747-8730|312-747-8545|312-747-8116| 1154575.242| 1862672.049|41.77898719|-87.70886382|(41.778987189, -8...|          008|\n",
            "|                   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",
            "|                  10|            Ogden|    3315 W Ogden Ave|Chicago|   IL|60623|http://home.chica...|312-747-7511|312-747-7429|312-747-7471| 1154500.753| 1890985.501|41.85668453|-87.70838196|(41.8566845327, -...|          010|\n",
            "|                  11|         Harrison|  3151 W Harrison St|Chicago|   IL|60612|http://home.chica...|312-746-8386|312-746-4281|312-746-5151| 1155244.069| 1897148.755|41.87358229|-87.70548813|(41.8735822883, -...|          011|\n",
            "|                  12|        Near West|1412 S Blue Islan...|   null| null| null|                null|        null|        null|        null|        null|        null|       null|        null|                null|          012|\n",
            "|\",Chicago,IL,6060...| -87.6569725149)\"|                null|   null| null| null|                null|        null|        null|        null|        null|        null|       null|        null|                null|          \",C|\n",
            "+--------------------+-----------------+--------------------+-------+-----+-----+--------------------+------------+------------+------------+------------+------------+-----------+------------+--------------------+-------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DWPxgJuzvpXM"
      },
      "source": [
        "rc = rc.join(ps,rc.JOIN_DISTRICT==ps.JOIN_DISTRICT,'left')"
      ],
      "execution_count": 62,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "C-3mmgt-wlAm",
        "outputId": "f4d62770-d540-4ba4-dfc1-d2a2ef167eb0"
      },
      "source": [
        "rc.show()"
      ],
      "execution_count": 63,
      "outputs": [
        {
          "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|JOIN_DISTRICT|DISTRICT| DISTRICT NAME|             ADDRESS|   CITY|STATE|  ZIP|             WEBSITE|       PHONE|         FAX|         TTY|X COORDINATE|Y COORDINATE|   LATITUDE|   LONGITUDE|            LOCATION|JOIN_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...|          009|       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...|          015|      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|          006|       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...|          014|      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...|          015|      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",
            "|10224742|   HY411435|2015-09-05 10:55:00| 082XX S LOOMIS BLVD|0610|          BURGLARY|      FORCIBLE ENTRY|           RESIDENCE| false|   false|0614|     006|  21|            71|      05|     1168430|     1850165|2015|02/10/2018 03:50:...|41.744378879|-87.658430635|(41.744378879, -8...|          006|       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",
            "|10224743|   HY411629|2015-09-04 18:00:00|021XX W CHURCHILL ST|0620|          BURGLARY|      UNLAWFUL ENTRY|    RESIDENCE-GARAGE| false|   false|1434|     014|  32|            24|      05|     1161628|     1912157|2015|02/10/2018 03:50:...|41.914635603|-87.681630909|(41.914635603, -8...|          014|      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",
            "|10224744|   HY411605|2015-09-05 13:00:00|   025XX W CERMAK RD|0860|             THEFT|        RETAIL THEFT|  GROCERY FOOD STORE|  true|   false|1034|     010|  25|            31|      06|     1159734|     1889313|2015|09/17/2015 11:37:...|41.851988885|-87.689219118|(41.851988885, -8...|          010|      10|         Ogden|    3315 W Ogden Ave|Chicago|   IL|60623|http://home.chica...|312-747-7511|312-747-7429|312-747-7471| 1154500.753| 1890985.501|41.85668453|-87.70838196|(41.8566845327, -...|          010|\n",
            "|10224745|   HY411654|2015-09-05 11:30:00|031XX W WASHINGTO...|0320|           ROBBERY|STRONGARM - NO WE...|              STREET| false|    true|1222|     012|  27|            27|      03|     1155536|     1900515|2015|02/10/2018 03:50:...| 41.88281374|-87.704325717|(41.88281374, -87...|          012|      12|     Near West|1412 S Blue Islan...|   null| null| null|                null|        null|        null|        null|        null|        null|       null|        null|                null|          012|\n",
            "|11645836|   JC212333|2016-05-01 00:25:00| 055XX S ROCKWELL ST|1153|DECEPTIVE PRACTICE|FINANCIAL IDENTIT...|                null| false|   false|0824|     008|  15|            63|      11|        null|        null|2016|04/06/2019 04:04:...|        null|         null|                null|          008|       8|  Chicago Lawn|      3420 W 63rd St|Chicago|   IL|60629|http://home.chica...|312-747-8730|312-747-8545|312-747-8116| 1154575.242| 1862672.049|41.77898719|-87.70886382|(41.778987189, -8...|          008|\n",
            "|10224746|   HY411662|2015-09-05 14:00:00|  071XX S PULASKI RD|0820|             THEFT|      $500 AND UNDER|PARKING LOT/GARAG...| false|   false|0833|     008|  13|            65|      06|     1150938|     1857056|2015|02/10/2018 03:50:...|41.763647552|-87.722344693|(41.763647552, -8...|          008|       8|  Chicago Lawn|      3420 W 63rd St|Chicago|   IL|60629|http://home.chica...|312-747-8730|312-747-8545|312-747-8116| 1154575.242| 1862672.049|41.77898719|-87.70886382|(41.778987189, -8...|          008|\n",
            "|10224749|   HY411626|2015-09-05 11:00:00|052XX N MILWAUKEE...|0460|           BATTERY|              SIMPLE|  SMALL RETAIL STORE| false|   false|1623|     016|  45|            11|     08B|     1137969|     1934340|2015|02/10/2018 03:50:...|41.975968415|-87.768014257|(41.975968415, -8...|          016|      16|Jefferson Park|5151 N Milwaukee Ave|Chicago|   IL|60630|http://home.chica...|312-742-4480|312-742-4421|312-742-4423| 1138480.758| 1933660.473|41.97409445|-87.76614884|(41.9740944511, -...|          016|\n",
            "|10224750|   HY411632|2015-09-05 03:00:00|    0000X W 103RD ST|2820|     OTHER OFFENSE|    TELEPHONE THREAT|           APARTMENT| false|    true|0512|     005|  34|            49|      26|     1177871|     1836676|2015|02/10/2018 03:50:...|41.707154919|-87.624244993|(41.707154919, -8...|          005|       5|       Calumet|      727 E 111th St|Chicago|   IL|60628|http://home.chica...|312-747-8210|312-747-5935|312-747-9170| 1183305.427| 1831462.313|41.69272336|-87.60450587|(41.6927233639, -...|          005|\n",
            "|10224751|   HY411566|2015-09-05 12:50:00|     013XX E 47TH ST|0486|           BATTERY|DOMESTIC BATTERY ...|              STREET| false|    true|0222|     002|   4|            39|     08B|     1185907|     1874105|2015|02/10/2018 03:50:...|41.809678314|-87.593638934|(41.809678314, -8...|          002|       2|     Wentworth|5101 S Wentworth Ave|Chicago|   IL|60609|http://home.chica...|312-747-8366|312-747-5396|312-747-6656| 1175864.837| 1871153.753|41.80181109|-87.63056018|(41.8018110912, -...|          002|\n",
            "|10224752|   HY411601|2015-09-03 13:00:00| 020XX W SCHILLER ST|0810|             THEFT|           OVER $500|              STREET| false|   false|1424|     014|   1|            24|      06|     1162574|     1909428|2015|02/10/2018 03:50:...|41.907127255|-87.678232016|(41.907127255, -8...|          014|      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",
            "|10224753|   HY411489|2015-09-05 11:45:00|  080XX S JUSTINE ST|0497|           BATTERY|AGGRAVATED DOMEST...|           APARTMENT| false|   false|0612|     006|  21|            71|     04B|     1167400|     1851512|2015|02/10/2018 03:50:...|41.748097343|-87.662166183|(41.748097343, -8...|          006|       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",
            "|10224754|   HY411656|2015-09-05 13:30:00|007XX N LEAMINGTO...|1320|   CRIMINAL DAMAGE|          TO VEHICLE|              STREET| false|   false|1531|     015|  28|            25|      14|     1141889|     1904448|2015|02/10/2018 03:50:...|41.893869916|-87.754341096|(41.893869916, -8...|          015|      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",
            "|10224756|   HY410094|2015-07-08 00:00:00|103XX S TORRENCE AVE|0620|          BURGLARY|      UNLAWFUL ENTRY|               OTHER| false|   false|0434|     004|  10|            51|      05|     1195508|     1836950|2015|02/10/2018 03:50:...|41.707490122|-87.559650325|(41.707490122, -8...|          004|       4| South Chicago|     2255 E 103rd St|Chicago|   IL|60617|http://home.chica...|312-747-7581|312-747-5276|312-747-9169| 1193131.299| 1837090.265|41.70793329|-87.56834912|(41.7079332906, -...|          004|\n",
            "|10224757|   HY411388|2015-09-05 09:55:00|  088XX S PAULINA ST|0610|          BURGLARY|      FORCIBLE ENTRY|           RESIDENCE|  true|   false|2221|     022|  21|            71|      05|     1166554|     1846067|2015|02/10/2018 03:50:...|41.733173536|-87.665421067|(41.733173536, -8...|          022|      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|\n",
            "|10224758|   HY411568|2015-09-05 12:35:00|    059XX W GRACE ST|0486|           BATTERY|DOMESTIC BATTERY ...|              STREET| false|    true|1633|     016|  38|            15|     08B|     1136014|     1924656|2015|02/10/2018 03:50:...|41.949429769|-87.775435529|(41.949429769, -8...|          016|      16|Jefferson Park|5151 N Milwaukee Ave|Chicago|   IL|60630|http://home.chica...|312-742-4480|312-742-4421|312-742-4423| 1138480.758| 1933660.473|41.97409445|-87.76614884|(41.9740944511, -...|          016|\n",
            "+--------+-----------+-------------------+--------------------+----+------------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+------------+-------------+--------------------+-------------+--------+--------------+--------------------+-------+-----+-----+--------------------+------------+------------+------------+------------+------------+-----------+------------+--------------------+-------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "55mO5cHI013R",
        "outputId": "af3034e8-09f2-4819-cfe7-0ffc5a31a36b"
      },
      "source": [
        "ps.columns"
      ],
      "execution_count": 64,
      "outputs": [
        {
          "output_type": "execute_result",
          "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",
              " 'JOIN_DISTRICT']"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 64
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Q56VbqTb0iQE"
      },
      "source": [
        "rc = rc.drop('JOIN_DISTRICT','DISTRICT','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')"
      ],
      "execution_count": 65,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "aGG0yDuk1Gpy",
        "outputId": "f01e53a2-a573-468e-b897-ed447f74fb45"
      },
      "source": [
        "rc.show()"
      ],
      "execution_count": 66,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------+-----------+-------------------+--------------------+----+------------------+--------------------+--------------------+------+--------+----+----+--------------+--------+----+--------------------+--------------+\n",
            "|      ID|Case Number|               Date|               Block|IUCR|      Primary Type|         Description|Location Description|Arrest|Domestic|Beat|Ward|Community Area|FBI Code|Year|          Updated On| DISTRICT NAME|\n",
            "+--------+-----------+-------------------+--------------------+----+------------------+--------------------+--------------------+------+--------+----+----+--------------+--------+----+--------------------+--------------+\n",
            "|10224738|   HY411648|2015-09-05 13:30:00|     043XX S WOOD ST|0486|           BATTERY|DOMESTIC BATTERY ...|           RESIDENCE| false|    true|0924|  12|            61|     08B|2015|02/10/2018 03:50:...|       Deering|\n",
            "|10224739|   HY411615|2015-09-04 11:30:00| 008XX N CENTRAL AVE|0870|             THEFT|      POCKET-PICKING|             CTA BUS| false|   false|1511|  29|            25|      06|2015|02/10/2018 03:50:...|        Austin|\n",
            "|11646166|   JC213529|2018-09-01 00:01:00|082XX S INGLESIDE...|0810|             THEFT|           OVER $500|           RESIDENCE| false|    true|0631|   8|            44|      06|2018|04/06/2019 04:04:...|       Gresham|\n",
            "|10224740|   HY411595|2015-09-05 12:45:00|   035XX W BARRY AVE|2023|         NARCOTICS|POSS: HEROIN(BRN/...|            SIDEWALK|  true|   false|1412|  35|            21|      18|2015|02/10/2018 03:50:...|   Shakespeare|\n",
            "|10224741|   HY411610|2015-09-05 13:00:00| 0000X N LARAMIE AVE|0560|           ASSAULT|              SIMPLE|           APARTMENT| false|    true|1522|  28|            25|     08A|2015|02/10/2018 03:50:...|        Austin|\n",
            "|10224742|   HY411435|2015-09-05 10:55:00| 082XX S LOOMIS BLVD|0610|          BURGLARY|      FORCIBLE ENTRY|           RESIDENCE| false|   false|0614|  21|            71|      05|2015|02/10/2018 03:50:...|       Gresham|\n",
            "|10224743|   HY411629|2015-09-04 18:00:00|021XX W CHURCHILL ST|0620|          BURGLARY|      UNLAWFUL ENTRY|    RESIDENCE-GARAGE| false|   false|1434|  32|            24|      05|2015|02/10/2018 03:50:...|   Shakespeare|\n",
            "|10224744|   HY411605|2015-09-05 13:00:00|   025XX W CERMAK RD|0860|             THEFT|        RETAIL THEFT|  GROCERY FOOD STORE|  true|   false|1034|  25|            31|      06|2015|09/17/2015 11:37:...|         Ogden|\n",
            "|10224745|   HY411654|2015-09-05 11:30:00|031XX W WASHINGTO...|0320|           ROBBERY|STRONGARM - NO WE...|              STREET| false|    true|1222|  27|            27|      03|2015|02/10/2018 03:50:...|     Near West|\n",
            "|11645836|   JC212333|2016-05-01 00:25:00| 055XX S ROCKWELL ST|1153|DECEPTIVE PRACTICE|FINANCIAL IDENTIT...|                null| false|   false|0824|  15|            63|      11|2016|04/06/2019 04:04:...|  Chicago Lawn|\n",
            "|10224746|   HY411662|2015-09-05 14:00:00|  071XX S PULASKI RD|0820|             THEFT|      $500 AND UNDER|PARKING LOT/GARAG...| false|   false|0833|  13|            65|      06|2015|02/10/2018 03:50:...|  Chicago Lawn|\n",
            "|10224749|   HY411626|2015-09-05 11:00:00|052XX N MILWAUKEE...|0460|           BATTERY|              SIMPLE|  SMALL RETAIL STORE| false|   false|1623|  45|            11|     08B|2015|02/10/2018 03:50:...|Jefferson Park|\n",
            "|10224750|   HY411632|2015-09-05 03:00:00|    0000X W 103RD ST|2820|     OTHER OFFENSE|    TELEPHONE THREAT|           APARTMENT| false|    true|0512|  34|            49|      26|2015|02/10/2018 03:50:...|       Calumet|\n",
            "|10224751|   HY411566|2015-09-05 12:50:00|     013XX E 47TH ST|0486|           BATTERY|DOMESTIC BATTERY ...|              STREET| false|    true|0222|   4|            39|     08B|2015|02/10/2018 03:50:...|     Wentworth|\n",
            "|10224752|   HY411601|2015-09-03 13:00:00| 020XX W SCHILLER ST|0810|             THEFT|           OVER $500|              STREET| false|   false|1424|   1|            24|      06|2015|02/10/2018 03:50:...|   Shakespeare|\n",
            "|10224753|   HY411489|2015-09-05 11:45:00|  080XX S JUSTINE ST|0497|           BATTERY|AGGRAVATED DOMEST...|           APARTMENT| false|   false|0612|  21|            71|     04B|2015|02/10/2018 03:50:...|       Gresham|\n",
            "|10224754|   HY411656|2015-09-05 13:30:00|007XX N LEAMINGTO...|1320|   CRIMINAL DAMAGE|          TO VEHICLE|              STREET| false|   false|1531|  28|            25|      14|2015|02/10/2018 03:50:...|        Austin|\n",
            "|10224756|   HY410094|2015-07-08 00:00:00|103XX S TORRENCE AVE|0620|          BURGLARY|      UNLAWFUL ENTRY|               OTHER| false|   false|0434|  10|            51|      05|2015|02/10/2018 03:50:...| South Chicago|\n",
            "|10224757|   HY411388|2015-09-05 09:55:00|  088XX S PAULINA ST|0610|          BURGLARY|      FORCIBLE ENTRY|           RESIDENCE|  true|   false|2221|  21|            71|      05|2015|02/10/2018 03:50:...|   Morgan Park|\n",
            "|10224758|   HY411568|2015-09-05 12:35:00|    059XX W GRACE ST|0486|           BATTERY|DOMESTIC BATTERY ...|              STREET| false|    true|1633|  38|            15|     08B|2015|02/10/2018 03:50:...|Jefferson Park|\n",
            "+--------+-----------+-------------------+--------------------+----+------------------+--------------------+--------------------+------+--------+----+----+--------------+--------+----+--------------------+--------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qXD7JwdMJdFQ"
      },
      "source": [
        "# Find the most frequently reported noncriminal activity"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1acPa2CER7CM",
        "outputId": "3375d8cd-8a79-4d3e-d975-f3b40a46874c"
      },
      "source": [
        "rc.select(col('Primary Type')).distinct().count()"
      ],
      "execution_count": 67,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "36"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 67
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "MpTvXC7HJcfZ",
        "outputId": "ced64b2b-ab11-4d2c-e37e-b3b197ad9c9e"
      },
      "source": [
        "rc.select(col('Primary Type')).distinct().orderBy(col('Primary Type')).show(rc.select(col('Primary Type')).distinct().count(),truncate=False)"
      ],
      "execution_count": 68,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+---------------------------------+\n",
            "|Primary Type                     |\n",
            "+---------------------------------+\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"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ScWKUzL6QX7W",
        "outputId": "3b5bb89d-83fc-4813-9e47-4083f65afa03"
      },
      "source": [
        "rc.filter((col('Primary Type') == 'NON-CRIMINAL (SUBJECT SPECIFIED)') | (col('Primary Type') == 'NON-CRIMINAL') | (col('Primary Type') == 'NON - CRIMINAL')).show()"
      ],
      "execution_count": 69,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------+-----------+-------------------+--------------------+----+--------------------+--------------------+--------------------+------+--------+----+----+--------------+--------+----+--------------------+--------------+\n",
            "|      ID|Case Number|               Date|               Block|IUCR|        Primary Type|         Description|Location Description|Arrest|Domestic|Beat|Ward|Community Area|FBI Code|Year|          Updated On| DISTRICT NAME|\n",
            "+--------+-----------+-------------------+--------------------+----+--------------------+--------------------+--------------------+------+--------+----+----+--------------+--------+----+--------------------+--------------+\n",
            "|10233370|   HY421530|2015-09-13 10:45:00|  010XX S MENARD AVE|5114|      NON - CRIMINAL|   FOID - REVOCATION|           RESIDENCE|  true|   false|1513|  29|            25|      26|2015|02/10/2018 03:50:...|        Austin|\n",
            "|10241515|   HY429346|2015-09-19 07:45:00|051XX N MILWAUKEE...|5114|      NON - CRIMINAL|   FOID - REVOCATION|POLICE FACILITY/V...|  true|   false|1623|  45|            11|      26|2015|02/10/2018 03:50:...|Jefferson Park|\n",
            "|10277370|   HY465290|2015-10-16 20:00:00|    100XX W OHARE ST|5093|        NON-CRIMINAL|       LOST PASSPORT|            AIRCRAFT| false|   false|1651|  41|            76|      26|2015|02/10/2018 03:50:...|Jefferson Park|\n",
            "|10280097|   HY468448|2015-10-19 18:50:00|   033XX W OGDEN AVE|5114|      NON - CRIMINAL|   FOID - REVOCATION|POLICE FACILITY/V...| false|   false|1024|  24|            29|      26|2015|02/10/2018 03:50:...|         Ogden|\n",
            "|10290745|   HY478502|2015-10-26 16:00:00|004XX W DIVERSEY ...|5093|        NON-CRIMINAL|       LOST PASSPORT|            SIDEWALK| false|   false|1934|  44|             6|      26|2015|02/10/2018 03:50:...|     Town Hall|\n",
            "|10296800|   HY485235|2015-09-30 17:00:00|008XX N MAPLEWOOD...|5093|        NON-CRIMINAL|       LOST PASSPORT|            SIDEWALK| false|   false|1211|   1|            24|      26|2015|02/10/2018 03:50:...|     Near West|\n",
            "|10300617|   HY489191|2015-11-03 15:00:00|  031XX S HALSTED ST|5114|      NON - CRIMINAL|   FOID - REVOCATION|           RESIDENCE| false|   false|0913|  11|            60|      26|2015|02/10/2018 03:50:...|       Deering|\n",
            "|10325994|   HY516457|2015-11-26 23:30:00|  001XX W HUBBARD ST|5093|        NON-CRIMINAL|       LOST PASSPORT|          RESTAURANT| false|   false|1831|  42|             8|      26|2015|02/10/2018 03:50:...|    Near North|\n",
            "|10157940|   HY347539|2015-07-08 10:30:00| 016XX W CHICAGO AVE|5093|        NON-CRIMINAL|       LOST PASSPORT|              STREET| false|   false|1215|   1|            24|      26|2015|02/10/2018 03:50:...|     Near West|\n",
            "|10181019|   HY369010|2015-08-01 10:15:00|  079XX S KEDZIE AVE|5114|      NON - CRIMINAL|   FOID - REVOCATION|           RESIDENCE| false|   false|0835|  18|            70|      26|2015|02/10/2018 03:50:...|  Chicago Lawn|\n",
            "|11249289|   JB176638|2016-12-23 12:00:00| 0000X N CENTRAL AVE|5093|        NON-CRIMINAL|       LOST PASSPORT|           RESIDENCE| false|   false|1513|  29|            25|      26|2016|03/08/2018 03:55:...|        Austin|\n",
            "|10202049|   HY389275|2015-08-19 18:38:00|025XX W MARQUETTE RD|5114|      NON - CRIMINAL|   FOID - REVOCATION|           APARTMENT| false|   false|0832|  15|            66|      26|2015|02/10/2018 03:50:...|  Chicago Lawn|\n",
            "|10813751|   JA113073|2003-03-17 20:00:00| 029XX N MELVINA AVE|5093|        NON-CRIMINAL|       LOST PASSPORT|           RESIDENCE| false|   false|2511|  29|            19|      26|2003|01/14/2017 03:49:...| Grand Central|\n",
            "|10212100|   HY398571|2015-08-26 17:45:00|    017XX S STATE ST|0585|        NON-CRIMINAL|NOTIFICATION OF S...|POLICE FACILITY/V...|  true|   false|0131|   3|            33|      26|2015|02/10/2018 03:50:...|       Central|\n",
            "|10213039|   HY398172|2015-08-26 11:45:00| 0000X W TERMINAL ST|5093|        NON-CRIMINAL|       LOST PASSPORT|AIRPORT TERMINAL ...| false|   false|1653|  41|            76|      26|2015|02/10/2018 03:50:...|Jefferson Park|\n",
            "|10221247|   HY406742|2015-09-01 22:00:00| 031XX W HARRISON ST|5114|      NON - CRIMINAL|   FOID - REVOCATION|POLICE FACILITY/V...| false|   false|1134|  24|            27|      26|2015|02/10/2018 03:50:...|      Harrison|\n",
            "|10336794|   HY527983|2015-10-26 14:00:00|    006XX N STATE ST|5093|        NON-CRIMINAL|       LOST PASSPORT|               OTHER| false|   false|1832|  42|             8|      26|2015|02/10/2018 03:50:...|    Near North|\n",
            "|10344075|   HY535016|2015-12-12 20:50:00|     025XX W 80TH PL|5114|      NON - CRIMINAL|   FOID - REVOCATION|VEHICLE NON-COMME...| false|   false|0835|  18|            70|      26|2015|02/10/2018 03:50:...|  Chicago Lawn|\n",
            "|10351482|   HY541941|2015-12-13 08:30:00|    100XX W OHARE ST|5093|        NON-CRIMINAL|       LOST PASSPORT|AIRPORT TERMINAL ...| false|   false|1651|  41|            76|      26|2015|02/10/2018 03:50:...|Jefferson Park|\n",
            "|10371674|   HZ107512|2016-01-07 17:36:00|051XX S WENTWORTH...|5073|NON-CRIMINAL (SUB...|NOTIFICATION OF C...|JAIL / LOCK-UP FA...| false|    true|0225|   3|            37|      26|2016|02/10/2018 03:50:...|     Wentworth|\n",
            "+--------+-----------+-------------------+--------------------+----+--------------------+--------------------+--------------------+------+--------+----+----+--------------+--------+----+--------------------+--------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "19HjpU-4VPoW",
        "outputId": "6f67cf58-5422-404d-b840-48259f9f20e8"
      },
      "source": [
        "rc.filter((col('Primary Type') == 'NON-CRIMINAL (SUBJECT SPECIFIED)') | (col('Primary Type') == 'NON-CRIMINAL') | (col('Primary Type') == 'NON - CRIMINAL'))\\\n",
        ".groupby('Description')\\\n",
        ".count()\\\n",
        ".orderBy('count',ascending=False)\\\n",
        ".show(1)"
      ],
      "execution_count": 70,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-------------+-----+\n",
            "|  Description|count|\n",
            "+-------------+-----+\n",
            "|LOST PASSPORT|  111|\n",
            "+-------------+-----+\n",
            "only showing top 1 row\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "K1Nc7IFeKSdh"
      },
      "source": [
        "# Find the day of the week with most reported crime"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6Re7ltyTKRk1",
        "outputId": "3da8a3cd-c657-4971-a277-a15d1a71940f"
      },
      "source": [
        "rc.groupby(date_format(col('Date'),'E').alias('Day_week')).count().orderBy('count',ascending=False).show(1)"
      ],
      "execution_count": 71,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------+-------+\n",
            "|Day_week|  count|\n",
            "+--------+-------+\n",
            "|     Fri|1099421|\n",
            "+--------+-------+\n",
            "only showing top 1 row\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KEjdlWcWlJW-"
      },
      "source": [
        "from pyspark.sql.functions import dayofweek"
      ],
      "execution_count": 72,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "FFlstY3fgHaz",
        "outputId": "73dd679e-350c-46b7-a86f-96a19b067f69"
      },
      "source": [
        "rc.groupby(dayofweek(col('Date')).alias('Day_week')).count().orderBy('count',ascending=False).show(1)"
      ],
      "execution_count": 73,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------+-------+\n",
            "|Day_week|  count|\n",
            "+--------+-------+\n",
            "|       6|1099421|\n",
            "+--------+-------+\n",
            "only showing top 1 row\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "K-EIJnKBnB0A",
        "outputId": "d1d85219-e06b-45ad-c86a-65c63e636f00"
      },
      "source": [
        "rc.groupby(date_format(col('Date'),'E').alias('Day_week'),dayofweek(col('Date')).alias('order_w')).count().orderBy('order_w',ascending=True).show()"
      ],
      "execution_count": 74,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------+-------+-------+\n",
            "|Day_week|order_w|  count|\n",
            "+--------+-------+-------+\n",
            "|     Sun|      1| 990646|\n",
            "|     Mon|      2|1031776|\n",
            "|     Tue|      3|1044921|\n",
            "|     Wed|      4|1051722|\n",
            "|     Thu|      5|1041194|\n",
            "|     Fri|      6|1099421|\n",
            "|     Sat|      7|1045592|\n",
            "+--------+-------+-------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "MPyexMsenl4x",
        "outputId": "7878c4dc-0f88-44a9-ac7a-4aec0cdeaeb7"
      },
      "source": [
        "week = [x[0] for x in rc.groupby(date_format(col('Date'),'E').alias('Day_week'),dayofweek(col('Date')).alias('order_w')).count().orderBy('order_w',ascending=True).collect()]\n",
        "week"
      ],
      "execution_count": 75,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat']"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 75
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "RPv4ESQkn7lh",
        "outputId": "8298d7a0-13f7-45d9-9750-bfe808b6d874"
      },
      "source": [
        "wcount = [x[2] for x in rc.groupby(date_format(col('Date'),'E').alias('Day_week'),dayofweek(col('Date')).alias('order_w')).count().orderBy('order_w',ascending=True).collect()]\n",
        "wcount"
      ],
      "execution_count": 76,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[990646, 1031776, 1044921, 1051722, 1041194, 1099421, 1045592]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 76
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pfJVorjFoB0d"
      },
      "source": [
        "import plotly\n",
        "import plotly.express as px\n",
        "import pandas as pd"
      ],
      "execution_count": 77,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 266
        },
        "id": "_33V2_BCrHfv",
        "outputId": "fead7c67-8416-40e5-e0a4-ac47ef2056c2"
      },
      "source": [
        "df = pd.DataFrame({'Day_of_week':week,'Count':wcount})\n",
        "df"
      ],
      "execution_count": 78,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>990646</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Mon</td>\n",
              "      <td>1031776</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Tue</td>\n",
              "      <td>1044921</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Wed</td>\n",
              "      <td>1051722</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Thu</td>\n",
              "      <td>1041194</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>Fri</td>\n",
              "      <td>1099421</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>Sat</td>\n",
              "      <td>1045592</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  Day_of_week    Count\n",
              "0         Sun   990646\n",
              "1         Mon  1031776\n",
              "2         Tue  1044921\n",
              "3         Wed  1051722\n",
              "4         Thu  1041194\n",
              "5         Fri  1099421\n",
              "6         Sat  1045592"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 78
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 542
        },
        "id": "KRyVXcciqmu1",
        "outputId": "a9a38fca-bfa8-4262-cec8-c57929c4363a"
      },
      "source": [
        "fig = px.line(\n",
        "    data_frame=df,\n",
        "    x=\"Day_of_week\",\n",
        "    y=\"Count\",\n",
        "    hover_name=\"Day_of_week\"\n",
        ")\n",
        "fig.show()"
      ],
      "execution_count": 79,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/html": [
              "<html>\n",
              "<head><meta charset=\"utf-8\" /></head>\n",
              "<body>\n",
              "    <div>\n",
              "            <script src=\"https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.5/MathJax.js?config=TeX-AMS-MML_SVG\"></script><script type=\"text/javascript\">if (window.MathJax) {MathJax.Hub.Config({SVG: {font: \"STIX-Web\"}});}</script>\n",
              "                <script type=\"text/javascript\">window.PlotlyConfig = {MathJaxConfig: 'local'};</script>\n",
              "        <script src=\"https://cdn.plot.ly/plotly-latest.min.js\"></script>    \n",
              "            <div id=\"76ecff1a-cdf4-459d-bbab-7b51f1246bae\" class=\"plotly-graph-div\" style=\"height:525px; width:100%;\"></div>\n",
              "            <script type=\"text/javascript\">\n",
              "                \n",
              "                    window.PLOTLYENV=window.PLOTLYENV || {};\n",
              "                    \n",
              "                if (document.getElementById(\"76ecff1a-cdf4-459d-bbab-7b51f1246bae\")) {\n",
              "                    Plotly.newPlot(\n",
              "                        '76ecff1a-cdf4-459d-bbab-7b51f1246bae',\n",
              "                        [{\"hoverlabel\": {\"namelength\": 0}, \"hovertemplate\": \"<b>%{hovertext}</b><br><br>Day_of_week=%{x}<br>Count=%{y}\", \"hovertext\": [\"Sun\", \"Mon\", \"Tue\", \"Wed\", \"Thu\", \"Fri\", \"Sat\"], \"legendgroup\": \"\", \"line\": {\"color\": \"#636efa\", \"dash\": \"solid\"}, \"mode\": \"lines\", \"name\": \"\", \"showlegend\": false, \"type\": \"scatter\", \"x\": [\"Sun\", \"Mon\", \"Tue\", \"Wed\", \"Thu\", \"Fri\", \"Sat\"], \"xaxis\": \"x\", \"y\": [990646, 1031776, 1044921, 1051722, 1041194, 1099421, 1045592], \"yaxis\": \"y\"}],\n",
              "                        {\"legend\": {\"tracegroupgap\": 0}, \"margin\": {\"t\": 60}, \"template\": {\"data\": {\"bar\": [{\"error_x\": {\"color\": \"#2a3f5f\"}, \"error_y\": {\"color\": \"#2a3f5f\"}, \"marker\": {\"line\": {\"color\": \"#E5ECF6\", \"width\": 0.5}}, \"type\": \"bar\"}], \"barpolar\": [{\"marker\": {\"line\": {\"color\": \"#E5ECF6\", \"width\": 0.5}}, \"type\": \"barpolar\"}], \"carpet\": [{\"aaxis\": {\"endlinecolor\": \"#2a3f5f\", \"gridcolor\": \"white\", \"linecolor\": \"white\", \"minorgridcolor\": \"white\", \"startlinecolor\": \"#2a3f5f\"}, \"baxis\": {\"endlinecolor\": \"#2a3f5f\", \"gridcolor\": \"white\", \"linecolor\": \"white\", \"minorgridcolor\": \"white\", \"startlinecolor\": \"#2a3f5f\"}, \"type\": \"carpet\"}], \"choropleth\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"type\": \"choropleth\"}], \"contour\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"colorscale\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"type\": \"contour\"}], \"contourcarpet\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"type\": \"contourcarpet\"}], \"heatmap\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"colorscale\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"type\": \"heatmap\"}], \"heatmapgl\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"colorscale\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"type\": \"heatmapgl\"}], \"histogram\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"histogram\"}], \"histogram2d\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"colorscale\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"type\": \"histogram2d\"}], \"histogram2dcontour\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"colorscale\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"type\": \"histogram2dcontour\"}], \"mesh3d\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"type\": \"mesh3d\"}], \"parcoords\": [{\"line\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"parcoords\"}], \"pie\": [{\"automargin\": true, \"type\": \"pie\"}], \"scatter\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scatter\"}], \"scatter3d\": [{\"line\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scatter3d\"}], \"scattercarpet\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scattercarpet\"}], \"scattergeo\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scattergeo\"}], \"scattergl\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scattergl\"}], \"scattermapbox\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scattermapbox\"}], \"scatterpolar\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scatterpolar\"}], \"scatterpolargl\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scatterpolargl\"}], \"scatterternary\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scatterternary\"}], \"surface\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"colorscale\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"type\": \"surface\"}], \"table\": [{\"cells\": {\"fill\": {\"color\": \"#EBF0F8\"}, \"line\": {\"color\": \"white\"}}, \"header\": {\"fill\": {\"color\": \"#C8D4E3\"}, \"line\": {\"color\": \"white\"}}, \"type\": \"table\"}]}, \"layout\": {\"annotationdefaults\": {\"arrowcolor\": \"#2a3f5f\", \"arrowhead\": 0, \"arrowwidth\": 1}, \"coloraxis\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"colorscale\": {\"diverging\": [[0, \"#8e0152\"], [0.1, \"#c51b7d\"], [0.2, \"#de77ae\"], [0.3, \"#f1b6da\"], [0.4, \"#fde0ef\"], [0.5, \"#f7f7f7\"], [0.6, \"#e6f5d0\"], [0.7, \"#b8e186\"], [0.8, \"#7fbc41\"], [0.9, \"#4d9221\"], [1, \"#276419\"]], \"sequential\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"sequentialminus\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]]}, \"colorway\": [\"#636efa\", \"#EF553B\", \"#00cc96\", \"#ab63fa\", \"#FFA15A\", \"#19d3f3\", \"#FF6692\", \"#B6E880\", \"#FF97FF\", \"#FECB52\"], \"font\": {\"color\": \"#2a3f5f\"}, \"geo\": {\"bgcolor\": \"white\", \"lakecolor\": \"white\", \"landcolor\": \"#E5ECF6\", \"showlakes\": true, \"showland\": true, \"subunitcolor\": \"white\"}, \"hoverlabel\": {\"align\": \"left\"}, \"hovermode\": \"closest\", \"mapbox\": {\"style\": \"light\"}, \"paper_bgcolor\": \"white\", \"plot_bgcolor\": \"#E5ECF6\", \"polar\": {\"angularaxis\": {\"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\"}, \"bgcolor\": \"#E5ECF6\", \"radialaxis\": {\"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\"}}, \"scene\": {\"xaxis\": {\"backgroundcolor\": \"#E5ECF6\", \"gridcolor\": \"white\", \"gridwidth\": 2, \"linecolor\": \"white\", \"showbackground\": true, \"ticks\": \"\", \"zerolinecolor\": \"white\"}, \"yaxis\": {\"backgroundcolor\": \"#E5ECF6\", \"gridcolor\": \"white\", \"gridwidth\": 2, \"linecolor\": \"white\", \"showbackground\": true, \"ticks\": \"\", \"zerolinecolor\": \"white\"}, \"zaxis\": {\"backgroundcolor\": \"#E5ECF6\", \"gridcolor\": \"white\", \"gridwidth\": 2, \"linecolor\": \"white\", \"showbackground\": true, \"ticks\": \"\", \"zerolinecolor\": \"white\"}}, \"shapedefaults\": {\"line\": {\"color\": \"#2a3f5f\"}}, \"ternary\": {\"aaxis\": {\"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\"}, \"baxis\": {\"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\"}, \"bgcolor\": \"#E5ECF6\", \"caxis\": {\"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\"}}, \"title\": {\"x\": 0.05}, \"xaxis\": {\"automargin\": true, \"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\", \"title\": {\"standoff\": 15}, \"zerolinecolor\": \"white\", \"zerolinewidth\": 2}, \"yaxis\": {\"automargin\": true, \"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\", \"title\": {\"standoff\": 15}, \"zerolinecolor\": \"white\", \"zerolinewidth\": 2}}}, \"xaxis\": {\"anchor\": \"y\", \"domain\": [0.0, 1.0], \"title\": {\"text\": \"Day_of_week\"}}, \"yaxis\": {\"anchor\": \"x\", \"domain\": [0.0, 1.0], \"title\": {\"text\": \"Count\"}}},\n",
              "                        {\"responsive\": true}\n",
              "                    ).then(function(){\n",
              "                            \n",
              "var gd = document.getElementById('76ecff1a-cdf4-459d-bbab-7b51f1246bae');\n",
              "var x = new MutationObserver(function (mutations, observer) {{\n",
              "        var display = window.getComputedStyle(gd).display;\n",
              "        if (!display || display === 'none') {{\n",
              "            console.log([gd, 'removed!']);\n",
              "            Plotly.purge(gd);\n",
              "            observer.disconnect();\n",
              "        }}\n",
              "}});\n",
              "\n",
              "// Listen for the removal of the full notebook cells\n",
              "var notebookContainer = gd.closest('#notebook-container');\n",
              "if (notebookContainer) {{\n",
              "    x.observe(notebookContainer, {childList: true});\n",
              "}}\n",
              "\n",
              "// Listen for the clearing of the current output cell\n",
              "var outputEl = gd.closest('.output');\n",
              "if (outputEl) {{\n",
              "    x.observe(outputEl, {childList: true});\n",
              "}}\n",
              "\n",
              "                        })\n",
              "                };\n",
              "                \n",
              "            </script>\n",
              "        </div>\n",
              "</body>\n",
              "</html>"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7JDfv5povh5c"
      },
      "source": [
        "df.sort_values('Count',inplace=True)"
      ],
      "execution_count": 80,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 542
        },
        "id": "eqghOjhPvIHB",
        "outputId": "3b141e40-e07c-42f6-ec0f-108de3cf1700"
      },
      "source": [
        "fig = px.line(\n",
        "    data_frame=df,\n",
        "    x=\"Day_of_week\",\n",
        "    y=\"Count\",\n",
        "    hover_name=\"Day_of_week\",\n",
        "\n",
        ")\n",
        "fig.show()"
      ],
      "execution_count": 81,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/html": [
              "<html>\n",
              "<head><meta charset=\"utf-8\" /></head>\n",
              "<body>\n",
              "    <div>\n",
              "            <script src=\"https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.5/MathJax.js?config=TeX-AMS-MML_SVG\"></script><script type=\"text/javascript\">if (window.MathJax) {MathJax.Hub.Config({SVG: {font: \"STIX-Web\"}});}</script>\n",
              "                <script type=\"text/javascript\">window.PlotlyConfig = {MathJaxConfig: 'local'};</script>\n",
              "        <script src=\"https://cdn.plot.ly/plotly-latest.min.js\"></script>    \n",
              "            <div id=\"769dc079-57e5-4b35-b8bb-cb65f68f8fa0\" class=\"plotly-graph-div\" style=\"height:525px; width:100%;\"></div>\n",
              "            <script type=\"text/javascript\">\n",
              "                \n",
              "                    window.PLOTLYENV=window.PLOTLYENV || {};\n",
              "                    \n",
              "                if (document.getElementById(\"769dc079-57e5-4b35-b8bb-cb65f68f8fa0\")) {\n",
              "                    Plotly.newPlot(\n",
              "                        '769dc079-57e5-4b35-b8bb-cb65f68f8fa0',\n",
              "                        [{\"hoverlabel\": {\"namelength\": 0}, \"hovertemplate\": \"<b>%{hovertext}</b><br><br>Day_of_week=%{x}<br>Count=%{y}\", \"hovertext\": [\"Sun\", \"Mon\", \"Thu\", \"Tue\", \"Sat\", \"Wed\", \"Fri\"], \"legendgroup\": \"\", \"line\": {\"color\": \"#636efa\", \"dash\": \"solid\"}, \"mode\": \"lines\", \"name\": \"\", \"showlegend\": false, \"type\": \"scatter\", \"x\": [\"Sun\", \"Mon\", \"Thu\", \"Tue\", \"Sat\", \"Wed\", \"Fri\"], \"xaxis\": \"x\", \"y\": [990646, 1031776, 1041194, 1044921, 1045592, 1051722, 1099421], \"yaxis\": \"y\"}],\n",
              "                        {\"legend\": {\"tracegroupgap\": 0}, \"margin\": {\"t\": 60}, \"template\": {\"data\": {\"bar\": [{\"error_x\": {\"color\": \"#2a3f5f\"}, \"error_y\": {\"color\": \"#2a3f5f\"}, \"marker\": {\"line\": {\"color\": \"#E5ECF6\", \"width\": 0.5}}, \"type\": \"bar\"}], \"barpolar\": [{\"marker\": {\"line\": {\"color\": \"#E5ECF6\", \"width\": 0.5}}, \"type\": \"barpolar\"}], \"carpet\": [{\"aaxis\": {\"endlinecolor\": \"#2a3f5f\", \"gridcolor\": \"white\", \"linecolor\": \"white\", \"minorgridcolor\": \"white\", \"startlinecolor\": \"#2a3f5f\"}, \"baxis\": {\"endlinecolor\": \"#2a3f5f\", \"gridcolor\": \"white\", \"linecolor\": \"white\", \"minorgridcolor\": \"white\", \"startlinecolor\": \"#2a3f5f\"}, \"type\": \"carpet\"}], \"choropleth\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"type\": \"choropleth\"}], \"contour\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"colorscale\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"type\": \"contour\"}], \"contourcarpet\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"type\": \"contourcarpet\"}], \"heatmap\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"colorscale\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"type\": \"heatmap\"}], \"heatmapgl\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"colorscale\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"type\": \"heatmapgl\"}], \"histogram\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"histogram\"}], \"histogram2d\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"colorscale\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"type\": \"histogram2d\"}], \"histogram2dcontour\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"colorscale\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"type\": \"histogram2dcontour\"}], \"mesh3d\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"type\": \"mesh3d\"}], \"parcoords\": [{\"line\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"parcoords\"}], \"pie\": [{\"automargin\": true, \"type\": \"pie\"}], \"scatter\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scatter\"}], \"scatter3d\": [{\"line\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scatter3d\"}], \"scattercarpet\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scattercarpet\"}], \"scattergeo\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scattergeo\"}], \"scattergl\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scattergl\"}], \"scattermapbox\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scattermapbox\"}], \"scatterpolar\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scatterpolar\"}], \"scatterpolargl\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scatterpolargl\"}], \"scatterternary\": [{\"marker\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"type\": \"scatterternary\"}], \"surface\": [{\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}, \"colorscale\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"type\": \"surface\"}], \"table\": [{\"cells\": {\"fill\": {\"color\": \"#EBF0F8\"}, \"line\": {\"color\": \"white\"}}, \"header\": {\"fill\": {\"color\": \"#C8D4E3\"}, \"line\": {\"color\": \"white\"}}, \"type\": \"table\"}]}, \"layout\": {\"annotationdefaults\": {\"arrowcolor\": \"#2a3f5f\", \"arrowhead\": 0, \"arrowwidth\": 1}, \"coloraxis\": {\"colorbar\": {\"outlinewidth\": 0, \"ticks\": \"\"}}, \"colorscale\": {\"diverging\": [[0, \"#8e0152\"], [0.1, \"#c51b7d\"], [0.2, \"#de77ae\"], [0.3, \"#f1b6da\"], [0.4, \"#fde0ef\"], [0.5, \"#f7f7f7\"], [0.6, \"#e6f5d0\"], [0.7, \"#b8e186\"], [0.8, \"#7fbc41\"], [0.9, \"#4d9221\"], [1, \"#276419\"]], \"sequential\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]], \"sequentialminus\": [[0.0, \"#0d0887\"], [0.1111111111111111, \"#46039f\"], [0.2222222222222222, \"#7201a8\"], [0.3333333333333333, \"#9c179e\"], [0.4444444444444444, \"#bd3786\"], [0.5555555555555556, \"#d8576b\"], [0.6666666666666666, \"#ed7953\"], [0.7777777777777778, \"#fb9f3a\"], [0.8888888888888888, \"#fdca26\"], [1.0, \"#f0f921\"]]}, \"colorway\": [\"#636efa\", \"#EF553B\", \"#00cc96\", \"#ab63fa\", \"#FFA15A\", \"#19d3f3\", \"#FF6692\", \"#B6E880\", \"#FF97FF\", \"#FECB52\"], \"font\": {\"color\": \"#2a3f5f\"}, \"geo\": {\"bgcolor\": \"white\", \"lakecolor\": \"white\", \"landcolor\": \"#E5ECF6\", \"showlakes\": true, \"showland\": true, \"subunitcolor\": \"white\"}, \"hoverlabel\": {\"align\": \"left\"}, \"hovermode\": \"closest\", \"mapbox\": {\"style\": \"light\"}, \"paper_bgcolor\": \"white\", \"plot_bgcolor\": \"#E5ECF6\", \"polar\": {\"angularaxis\": {\"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\"}, \"bgcolor\": \"#E5ECF6\", \"radialaxis\": {\"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\"}}, \"scene\": {\"xaxis\": {\"backgroundcolor\": \"#E5ECF6\", \"gridcolor\": \"white\", \"gridwidth\": 2, \"linecolor\": \"white\", \"showbackground\": true, \"ticks\": \"\", \"zerolinecolor\": \"white\"}, \"yaxis\": {\"backgroundcolor\": \"#E5ECF6\", \"gridcolor\": \"white\", \"gridwidth\": 2, \"linecolor\": \"white\", \"showbackground\": true, \"ticks\": \"\", \"zerolinecolor\": \"white\"}, \"zaxis\": {\"backgroundcolor\": \"#E5ECF6\", \"gridcolor\": \"white\", \"gridwidth\": 2, \"linecolor\": \"white\", \"showbackground\": true, \"ticks\": \"\", \"zerolinecolor\": \"white\"}}, \"shapedefaults\": {\"line\": {\"color\": \"#2a3f5f\"}}, \"ternary\": {\"aaxis\": {\"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\"}, \"baxis\": {\"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\"}, \"bgcolor\": \"#E5ECF6\", \"caxis\": {\"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\"}}, \"title\": {\"x\": 0.05}, \"xaxis\": {\"automargin\": true, \"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\", \"title\": {\"standoff\": 15}, \"zerolinecolor\": \"white\", \"zerolinewidth\": 2}, \"yaxis\": {\"automargin\": true, \"gridcolor\": \"white\", \"linecolor\": \"white\", \"ticks\": \"\", \"title\": {\"standoff\": 15}, \"zerolinecolor\": \"white\", \"zerolinewidth\": 2}}}, \"xaxis\": {\"anchor\": \"y\", \"domain\": [0.0, 1.0], \"title\": {\"text\": \"Day_of_week\"}}, \"yaxis\": {\"anchor\": \"x\", \"domain\": [0.0, 1.0], \"title\": {\"text\": \"Count\"}}},\n",
              "                        {\"responsive\": true}\n",
              "                    ).then(function(){\n",
              "                            \n",
              "var gd = document.getElementById('769dc079-57e5-4b35-b8bb-cb65f68f8fa0');\n",
              "var x = new MutationObserver(function (mutations, observer) {{\n",
              "        var display = window.getComputedStyle(gd).display;\n",
              "        if (!display || display === 'none') {{\n",
              "            console.log([gd, 'removed!']);\n",
              "            Plotly.purge(gd);\n",
              "            observer.disconnect();\n",
              "        }}\n",
              "}});\n",
              "\n",
              "// Listen for the removal of the full notebook cells\n",
              "var notebookContainer = gd.closest('#notebook-container');\n",
              "if (notebookContainer) {{\n",
              "    x.observe(notebookContainer, {childList: true});\n",
              "}}\n",
              "\n",
              "// Listen for the clearing of the current output cell\n",
              "var outputEl = gd.closest('.output');\n",
              "if (outputEl) {{\n",
              "    x.observe(outputEl, {childList: true});\n",
              "}}\n",
              "\n",
              "                        })\n",
              "                };\n",
              "                \n",
              "            </script>\n",
              "        </div>\n",
              "</body>\n",
              "</html>"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JtN1a67mx0aj"
      },
      "source": [
        "# RDD"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "w6PnkCP1feo3"
      },
      "source": [
        "from pyspark import SparkContext"
      ],
      "execution_count": 82,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UfiROijHx2PW"
      },
      "source": [
        "sparkc = SparkContext.getOrCreate()"
      ],
      "execution_count": 84,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IvHW19sZkLxu"
      },
      "source": [
        "psrdd = sparkc.textFile('/content/police_stations.csv')"
      ],
      "execution_count": 89,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        },
        "id": "fWBMT4Makk_8",
        "outputId": "d45fc1f7-02e2-4d86-af01-2cc9957d525a"
      },
      "source": [
        "psrdd.first()"
      ],
      "execution_count": 88,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            },
            "text/plain": [
              "'DISTRICT,DISTRICT NAME,ADDRESS,CITY,STATE,ZIP,WEBSITE,PHONE,FAX,TTY,X COORDINATE,Y COORDINATE,LATITUDE,LONGITUDE,LOCATION'"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 88
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fpfEjXbzktsB"
      },
      "source": [
        "psheader = psrdd.first()"
      ],
      "execution_count": 90,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VBeGZrzEk3PT"
      },
      "source": [
        "psdata = psrdd.filter(lambda line: line!=psheader)"
      ],
      "execution_count": 93,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        },
        "id": "DMrl8BlplHpN",
        "outputId": "68a451cc-d6d4-4227-f5c4-6ba6a82db4bc"
      },
      "source": [
        "psdata.first()"
      ],
      "execution_count": 94,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            },
            "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)\"'"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 94
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1TqhbEPVlLu7",
        "outputId": "d8908a07-0dbc-4986-d395-96095092a3e6"
      },
      "source": [
        "psdata.count()"
      ],
      "execution_count": 95,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "24"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 95
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "OJALw5yum5vY",
        "outputId": "a4cece7e-a922-4e0b-eb25-1f5fe5266543"
      },
      "source": [
        "psdata.map(lambda line: line.split(',')).collect()"
      ],
      "execution_count": 96,
      "outputs": [
        {
          "output_type": "execute_result",
          "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)\"']]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 96
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "f1BSPLDhnHYd",
        "outputId": "a12e0709-37ef-407c-ab0d-960a6c196bca"
      },
      "source": [
        "psdata.map(lambda line: line.split(',')).count()"
      ],
      "execution_count": 97,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "24"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 97
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3QrlZX4yqYcx"
      },
      "source": [
        "# Select District, district name, address e Zip para a estacao policial 7"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "H8QHckkUnX2R",
        "outputId": "23c6cf14-3269-43d6-f1e0-e47e6fdf1bbc"
      },
      "source": [
        "psdata.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()"
      ],
      "execution_count": 100,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[('7', 'Englewood', '1438 W 63rd St', '60636')]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 100
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mkTVwzhirPBp"
      },
      "source": [
        "# Select District, district name, address e Zip para as estacoes policiais 10 e 11"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "nHBGHqnFrDPT",
        "outputId": "80aa61f0-3ad9-4578-89f5-620a9978b208"
      },
      "source": [
        "psdata.filter(lambda line: line.split(',')[0] in ['10','11'])\\\n",
        ".map(lambda line: (line.split(',')[0]\\\n",
        "                   ,line.split(',')[1]\\\n",
        "                   ,line.split(',')[2]\\\n",
        "                   ,line.split(',')[5]))\\\n",
        "                   .collect()"
      ],
      "execution_count": 101,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[('10', 'Ogden', '3315 W Ogden Ave', '60623'),\n",
              " ('11', 'Harrison', '3151 W Harrison St', '60612')]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 101
        }
      ]
    }
  ]
}