Risk_Score_Calculation.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "DXSGvqLo1Y51"
      },
      "outputs": [],
      "source": [
        "from pyspark.sql import SparkSession\n",
        "from pyspark.ml.feature import PCA\n",
        "from pyspark.ml.linalg import Vectors\n",
        "\n",
        "# Initialize Spark session\n",
        "spark = SparkSession.builder.appName(\"PCAExample\").getOrCreate()\n"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import torch\n",
        "print(\"CUDA available:\", torch.cuda.is_available())\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0FrMtAEQvZ5_",
        "outputId": "e9f29ac9-e995-47c2-a5a6-8dffce12d928"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "CUDA available: True\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6uJanSfA3v12",
        "outputId": "b974693b-16a0-4252-fc31-507c47dc88fe"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Drive already mounted at /content/drive/; to attempt to forcibly remount, call drive.mount(\"/content/drive/\", force_remount=True).\n"
          ]
        }
      ],
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive/')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "SdN8W7Ns6Qfz",
        "outputId": "e8df4ec4-aff5-411f-a6d9-017daa265df4"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "/content/drive/MyDrive\n"
          ]
        }
      ],
      "source": [
        "%cd /content/drive/MyDrive"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql import SparkSession\n",
        "from pyspark.sql.functions import *\n",
        "from pyspark.sql import functions as F\n",
        "from pyspark.ml.feature import StringIndexer, VectorAssembler, StandardScaler\n",
        "from pyspark.ml import Pipeline\n",
        "from keras.models import Model\n",
        "from keras.layers import Input, Dense\n",
        "import numpy as np\n",
        "spark = SparkSession.builder \\\n",
        "    .appName(\"AutoencoderRiskScore\") \\\n",
        "    .getOrCreate()"
      ],
      "metadata": {
        "id": "h-bvX0JIbv01"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "\n",
        "\n",
        "file_path = 'city_scout_fbi_nibrs_2011_2021.csv'  # Update with the correct path\n",
        "data = spark.read.csv(\"/content/drive/MyDrive/\" + file_path, header=True, inferSchema=True)\n",
        "\n",
        "# df = pd.read_csv(\"/content/drive/MyDrive/\" + file_path)\n",
        "firstThousDF = data\n",
        "# firstThousDF.show(n = 10000)"
      ],
      "metadata": {
        "id": "wpGdEa6d5vE2"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "\n",
        "bendDF = firstThousDF\n",
        "print(bendDF.count())\n",
        "# Using population, age_num, sex_code, race, resident_status_code, offense_name feature as input to autoencoder"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "NMpF3yWsK699",
        "outputId": "a5c8297a-4108-44a4-f82f-92ff2b841356"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "6822798\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "median_value = bendDF.approxQuantile(\"population\", [0.5], 0.1)[0]\n",
        "\n",
        "median_value_age = bendDF.approxQuantile(\"age_num\", [0.5], 0.1)[0]\n",
        "\n",
        "\n",
        "res = bendDF.withColumn(\n",
        "    \"age_filled\",\n",
        "    when(col(\"age_num\").isNull(), median_value_age).otherwise(col(\"age_num\"))\n",
        ")\n",
        "\n",
        "res = res.withColumn(\n",
        "    \"population_filled\",\n",
        "    when(col(\"population\").isNull(), median_value).otherwise(col(\"population\"))\n",
        ")\n",
        "\n",
        "res = res.withColumn(\n",
        "    \"race_id_filled\",\n",
        "    when(col(\"race_id\").isNull(), \"Unknown\").otherwise(col(\"race_id\"))\n",
        ")\n",
        "\n",
        "res = res.withColumn(\n",
        "    \"offense_name_filled\",\n",
        "    when(col(\"offense_name\").isNull(), \"Unknown\").otherwise(col(\"offense_name\"))\n",
        ")\n",
        "\n",
        "res = res.withColumn(\n",
        "    \"resident_status_code_filled\",\n",
        "    when(col(\"resident_status_code\").isNull(), \"Unknown\").otherwise(col(\"resident_status_code\"))\n",
        ")\n",
        "\n",
        "res = res.withColumn(\n",
        "    \"sex_code_filled\",\n",
        "    when(col(\"sex_code\").isNull(), \"Unknown\").otherwise(col(\"sex_code\"))\n",
        ")\n",
        "\n",
        "# res = res.withColumn(\"log_population\", log(col(\"population_filled\") + 1))\n",
        "res.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6H-yikDlAa05",
        "outputId": "151a3adc-e8e4-44ec-f42b-2cf2e45289da"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---------------+---------+---------+----------------+--------------+----------+--------+----------+-----------+-------+--------+--------------------+------------+--------------------+----------+--------------------+---------------------+----------+-----------------+--------------------+--------------------+---------------------------+---------------+\n",
            "|offense_type_id|victim_id|agency_id|       city_name|primary_county|state_abbr|state_id|population|incident_id|age_num|sex_code|             race_id|ethnicity_id|resident_status_code|offense_id|        offense_name|offense_category_name|age_filled|population_filled|      race_id_filled| offense_name_filled|resident_status_code_filled|sex_code_filled|\n",
            "+---------------+---------+---------+----------------+--------------+----------+--------+----------+-----------+-------+--------+--------------------+------------+--------------------+----------+--------------------+---------------------+----------+-----------------+--------------------+--------------------+---------------------------+---------------+\n",
            "|             45| 62205773|     8714|Sterling Heights|        Macomb|        MI|      26|    132255|   57347297|     10|       M|               White|           2|                   R|  66872038|   All Other Larceny| Larceny/Theft Off...|      10.0|         132255.0|               White|   All Other Larceny|                          R|              M|\n",
            "|             49| 62206339|    14791|      N Lawrence|         Stark|        OH|      39|      8270|   57347791|     74|       F|               White|           2|                   R|  63734461|Burglary/Breaking...| Burglary/Breaking...|      74.0|           8270.0|               White|Burglary/Breaking...|                          R|              F|\n",
            "|             47| 62206339|    14791|      N Lawrence|         Stark|        OH|      39|      8270|   57347791|     74|       F|               White|           2|                   R|  63742381| Theft From Building| Larceny/Theft Off...|      74.0|           8270.0|               White| Theft From Building|                          R|              F|\n",
            "|             45| 62206390|    14791|      N Lawrence|         Stark|        OH|      39|      8270|   57347842|     82|       M|               White|           2|                   R|  63742390|   All Other Larceny| Larceny/Theft Off...|      82.0|           8270.0|               White|   All Other Larceny|                          R|              M|\n",
            "|             51| 62206791|     8714|Sterling Heights|        Macomb|        MI|      26|    132255|   57348207|     61|       M|               White|           2|                   R|  66874050|      Simple Assault|     Assault Offenses|      61.0|         132255.0|               White|      Simple Assault|                          R|              M|\n",
            "|             49| 62206891|     8714|Sterling Heights|        Macomb|        MI|      26|    132255|   57348303|     33|       F|               White|           3|                   R|  66870911|Burglary/Breaking...| Burglary/Breaking...|      33.0|         132255.0|               White|Burglary/Breaking...|                          R|              F|\n",
            "|             27| 62206901|     8714|Sterling Heights|        Macomb|        MI|      26|    132255|   57348313|     28|       M|Black or African ...|           3|                   R|  66874285|  Aggravated Assault|     Assault Offenses|      28.0|         132255.0|Black or African ...|  Aggravated Assault|                          R|              M|\n",
            "|             45| 62207338|    14791|      N Lawrence|         Stark|        OH|      39|      8270|   57348689|     44|       F|               White|           2|                   R|  63742612|   All Other Larceny| Larceny/Theft Off...|      44.0|           8270.0|               White|   All Other Larceny|                          R|              F|\n",
            "|             44| 62207419|    14791|      N Lawrence|         Stark|        OH|      39|      8270|   57348770|     31|       F|               White|           2|                   R|  63743180|        Intimidation|     Assault Offenses|      31.0|           8270.0|               White|        Intimidation|                          R|              F|\n",
            "|             45| 62207598|     6531|        Florence|         Boone|        KY|      21|     32357|   57348911|     45|       M|               White|           2|                   R|  64586669|   All Other Larceny| Larceny/Theft Off...|      45.0|          32357.0|               White|   All Other Larceny|                          R|              M|\n",
            "|             45| 62207661|     6531|        Florence|         Boone|        KY|      21|     32357|   57348962|     19|       F|               White|           2|                   R|  64585159|   All Other Larceny| Larceny/Theft Off...|      19.0|          32357.0|               White|   All Other Larceny|                          R|              F|\n",
            "|             51| 62207977|     8714|Sterling Heights|        Macomb|        MI|      26|    132255|   57349276|     14|       F|               White|           3|                   R|  66872083|      Simple Assault|     Assault Offenses|      14.0|         132255.0|               White|      Simple Assault|                          R|              F|\n",
            "|             11| 62208538|    14792|       Massillon|         Stark|        OH|      39|     28492|   57349763|     81|       M|               White|           2|                   R|  63749258|Counterfeiting/Fo...| Counterfeiting/Fo...|      81.0|          28492.0|               White|Counterfeiting/Fo...|                          R|              M|\n",
            "|             45| 62208538|    14792|       Massillon|         Stark|        OH|      39|     28492|   57349763|     81|       M|               White|           2|                   R|  63749257|   All Other Larceny| Larceny/Theft Off...|      81.0|          28492.0|               White|   All Other Larceny|                          R|              M|\n",
            "|             47| 62208992|     8714|Sterling Heights|        Macomb|        MI|      26|    132255|   57350181|     51|       M|               White|           3|                   R|  66882831| Theft From Building| Larceny/Theft Off...|      51.0|         132255.0|               White| Theft From Building|                          R|              M|\n",
            "|             47| 62209021|     8714|Sterling Heights|        Macomb|        MI|      26|    132255|   57350205|     55|       F|               White|           3|                   N|  66870954| Theft From Building| Larceny/Theft Off...|      55.0|         132255.0|               White| Theft From Building|                          N|              F|\n",
            "|             45| 62209685|     6531|        Florence|         Boone|        KY|      21|     32357|   57350769|     24|       F|               White|           2|                   R|  64580216|   All Other Larceny| Larceny/Theft Off...|      24.0|          32357.0|               White|   All Other Larceny|                          R|              F|\n",
            "|             51| 62210099|     8714|Sterling Heights|        Macomb|        MI|      26|    132255|   57351165|     77|       F|               White|           3|                   R|  66867546|      Simple Assault|     Assault Offenses|      77.0|         132255.0|               White|      Simple Assault|                          R|              F|\n",
            "|             14| 62210668|    14792|       Massillon|         Stark|        OH|      39|     28492|   57351640|     22|       F|               White|           2|                   R|  63752267|Theft From Motor ...| Larceny/Theft Off...|      22.0|          28492.0|               White|Theft From Motor ...|                          R|              F|\n",
            "|             51| 62210698|    14792|       Massillon|         Stark|        OH|      39|     28492|   57351668|     29|       F|               White|           2|                   U|  63753020|      Simple Assault|     Assault Offenses|      29.0|          28492.0|               White|      Simple Assault|                          U|              F|\n",
            "+---------------+---------+---------+----------------+--------------+----------+--------+----------+-----------+-------+--------+--------------------+------------+--------------------+----------+--------------------+---------------------+----------+-----------------+--------------------+--------------------+---------------------------+---------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "null_counts = res.select([sum(col(c).isNull().cast(\"int\")).alias(c) for c in res.columns])\n",
        "null_counts.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "lGq3YOdSqbSi",
        "outputId": "1474d280-d289-4056-b473-a05a41f482f4"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---------------+---------+---------+---------+--------------+----------+--------+----------+-----------+-------+--------+-------+------------+--------------------+----------+------------+---------------------+-----------------+--------------+-------------------+---------------------------+---------------+\n",
            "|offense_type_id|victim_id|agency_id|city_name|primary_county|state_abbr|state_id|population|incident_id|age_num|sex_code|race_id|ethnicity_id|resident_status_code|offense_id|offense_name|offense_category_name|population_filled|race_id_filled|offense_name_filled|resident_status_code_filled|sex_code_filled|\n",
            "+---------------+---------+---------+---------+--------------+----------+--------+----------+-----------+-------+--------+-------+------------+--------------------+----------+------------+---------------------+-----------------+--------------+-------------------+---------------------------+---------------+\n",
            "|              0|        0|        0|  1700668|         50094|         1|       1|        31|          1|      1|       1|      1|           1|                   1|         1|           1|                   18|                0|             0|                  0|                          0|              0|\n",
            "+---------------+---------+---------+---------+--------------+----------+--------+----------+-----------+-------+--------+-------+------------+--------------------+----------+------------+---------------------+-----------------+--------------+-------------------+---------------------------+---------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.ml.feature import StandardScaler\n",
        "\n",
        "currNumCols=[\"population_filled\", \"age_filled\"]\n",
        "otherCols = [\"sex_code_filled\", \"race_id_filled\", \"resident_status_code_filled\", \"offense_name_filled\"]\n",
        "\n",
        "\n",
        "raceIndexer = StringIndexer(inputCol=\"race_id_filled\", outputCol=\"race_id_index\")\n",
        "offenseIndexer = StringIndexer(inputCol=\"offense_name_filled\", outputCol=\"crime_type_index\")\n",
        "residentIndexer = StringIndexer(inputCol=\"resident_status_code_filled\", outputCol=\"resident_status_index\")\n",
        "sexCodeIndexer = StringIndexer(inputCol=\"sex_code_filled\", outputCol=\"sex_code_index\")\n",
        "\n",
        "\n",
        "\n",
        "assembler = VectorAssembler(inputCols=currNumCols + [\"sex_code_index\", \"race_id_index\", \"crime_type_index\", \"resident_status_index\"], outputCol=\"features_for_scaling\")\n",
        "\n",
        "\n",
        "\n",
        "scaler = StandardScaler(inputCol=\"features_for_scaling\", outputCol=\"scaledFeatures\", withStd=True, withMean=True)\n",
        "pipeline = Pipeline(stages=[raceIndexer, offenseIndexer, residentIndexer, sexCodeIndexer, assembler, scaler])\n",
        "scaled_df = pipeline.fit(res).transform(res)\n",
        "scaled_df.show()\n"
      ],
      "metadata": {
        "id": "0JjiLJTk3Mog",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "4d129ff2-50ec-41a6-d9bf-0a0b78a6ffce"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---------------+---------+---------+----------------+--------------+----------+--------+----------+-----------+-------+--------+--------------------+------------+--------------------+----------+--------------------+---------------------+----------+-----------------+--------------------+--------------------+---------------------------+---------------+-------------+----------------+---------------------+--------------+--------------------+--------------------+\n",
            "|offense_type_id|victim_id|agency_id|       city_name|primary_county|state_abbr|state_id|population|incident_id|age_num|sex_code|             race_id|ethnicity_id|resident_status_code|offense_id|        offense_name|offense_category_name|age_filled|population_filled|      race_id_filled| offense_name_filled|resident_status_code_filled|sex_code_filled|race_id_index|crime_type_index|resident_status_index|sex_code_index|features_for_scaling|      scaledFeatures|\n",
            "+---------------+---------+---------+----------------+--------------+----------+--------+----------+-----------+-------+--------+--------------------+------------+--------------------+----------+--------------------+---------------------+----------+-----------------+--------------------+--------------------+---------------------------+---------------+-------------+----------------+---------------------+--------------+--------------------+--------------------+\n",
            "|             45| 62205773|     8714|Sterling Heights|        Macomb|        MI|      26|    132255|   57347297|     10|       M|               White|           2|                   R|  66872038|   All Other Larceny| Larceny/Theft Off...|      10.0|         132255.0|               White|   All Other Larceny|                          R|              M|          0.0|             2.0|                  0.0|           1.0|[132255.0,10.0,1....|[-0.2489591256372...|\n",
            "|             49| 62206339|    14791|      N Lawrence|         Stark|        OH|      39|      8270|   57347791|     74|       F|               White|           2|                   R|  63734461|Burglary/Breaking...| Burglary/Breaking...|      74.0|           8270.0|               White|Burglary/Breaking...|                          R|              F|          0.0|             3.0|                  0.0|           0.0|[8270.0,74.0,0.0,...|[-0.6024573521764...|\n",
            "|             47| 62206339|    14791|      N Lawrence|         Stark|        OH|      39|      8270|   57347791|     74|       F|               White|           2|                   R|  63742381| Theft From Building| Larceny/Theft Off...|      74.0|           8270.0|               White| Theft From Building|                          R|              F|          0.0|             8.0|                  0.0|           0.0|[8270.0,74.0,0.0,...|[-0.6024573521764...|\n",
            "|             45| 62206390|    14791|      N Lawrence|         Stark|        OH|      39|      8270|   57347842|     82|       M|               White|           2|                   R|  63742390|   All Other Larceny| Larceny/Theft Off...|      82.0|           8270.0|               White|   All Other Larceny|                          R|              M|          0.0|             2.0|                  0.0|           1.0|[8270.0,82.0,1.0,...|[-0.6024573521764...|\n",
            "|             51| 62206791|     8714|Sterling Heights|        Macomb|        MI|      26|    132255|   57348207|     61|       M|               White|           2|                   R|  66874050|      Simple Assault|     Assault Offenses|      61.0|         132255.0|               White|      Simple Assault|                          R|              M|          0.0|             0.0|                  0.0|           1.0|[132255.0,61.0,1....|[-0.2489591256372...|\n",
            "|             49| 62206891|     8714|Sterling Heights|        Macomb|        MI|      26|    132255|   57348303|     33|       F|               White|           3|                   R|  66870911|Burglary/Breaking...| Burglary/Breaking...|      33.0|         132255.0|               White|Burglary/Breaking...|                          R|              F|          0.0|             3.0|                  0.0|           0.0|[132255.0,33.0,0....|[-0.2489591256372...|\n",
            "|             27| 62206901|     8714|Sterling Heights|        Macomb|        MI|      26|    132255|   57348313|     28|       M|Black or African ...|           3|                   R|  66874285|  Aggravated Assault|     Assault Offenses|      28.0|         132255.0|Black or African ...|  Aggravated Assault|                          R|              M|          1.0|             5.0|                  0.0|           1.0|[132255.0,28.0,1....|[-0.2489591256372...|\n",
            "|             45| 62207338|    14791|      N Lawrence|         Stark|        OH|      39|      8270|   57348689|     44|       F|               White|           2|                   R|  63742612|   All Other Larceny| Larceny/Theft Off...|      44.0|           8270.0|               White|   All Other Larceny|                          R|              F|          0.0|             2.0|                  0.0|           0.0|[8270.0,44.0,0.0,...|[-0.6024573521764...|\n",
            "|             44| 62207419|    14791|      N Lawrence|         Stark|        OH|      39|      8270|   57348770|     31|       F|               White|           2|                   R|  63743180|        Intimidation|     Assault Offenses|      31.0|           8270.0|               White|        Intimidation|                          R|              F|          0.0|             6.0|                  0.0|           0.0|[8270.0,31.0,0.0,...|[-0.6024573521764...|\n",
            "|             45| 62207598|     6531|        Florence|         Boone|        KY|      21|     32357|   57348911|     45|       M|               White|           2|                   R|  64586669|   All Other Larceny| Larceny/Theft Off...|      45.0|          32357.0|               White|   All Other Larceny|                          R|              M|          0.0|             2.0|                  0.0|           1.0|[32357.0,45.0,1.0...|[-0.5337820141706...|\n",
            "|             45| 62207661|     6531|        Florence|         Boone|        KY|      21|     32357|   57348962|     19|       F|               White|           2|                   R|  64585159|   All Other Larceny| Larceny/Theft Off...|      19.0|          32357.0|               White|   All Other Larceny|                          R|              F|          0.0|             2.0|                  0.0|           0.0|[32357.0,19.0,0.0...|[-0.5337820141706...|\n",
            "|             51| 62207977|     8714|Sterling Heights|        Macomb|        MI|      26|    132255|   57349276|     14|       F|               White|           3|                   R|  66872083|      Simple Assault|     Assault Offenses|      14.0|         132255.0|               White|      Simple Assault|                          R|              F|          0.0|             0.0|                  0.0|           0.0|(6,[0,1],[132255....|[-0.2489591256372...|\n",
            "|             11| 62208538|    14792|       Massillon|         Stark|        OH|      39|     28492|   57349763|     81|       M|               White|           2|                   R|  63749258|Counterfeiting/Fo...| Counterfeiting/Fo...|      81.0|          28492.0|               White|Counterfeiting/Fo...|                          R|              M|          0.0|            14.0|                  0.0|           1.0|[28492.0,81.0,1.0...|[-0.5448016588500...|\n",
            "|             45| 62208538|    14792|       Massillon|         Stark|        OH|      39|     28492|   57349763|     81|       M|               White|           2|                   R|  63749257|   All Other Larceny| Larceny/Theft Off...|      81.0|          28492.0|               White|   All Other Larceny|                          R|              M|          0.0|             2.0|                  0.0|           1.0|[28492.0,81.0,1.0...|[-0.5448016588500...|\n",
            "|             47| 62208992|     8714|Sterling Heights|        Macomb|        MI|      26|    132255|   57350181|     51|       M|               White|           3|                   R|  66882831| Theft From Building| Larceny/Theft Off...|      51.0|         132255.0|               White| Theft From Building|                          R|              M|          0.0|             8.0|                  0.0|           1.0|[132255.0,51.0,1....|[-0.2489591256372...|\n",
            "|             47| 62209021|     8714|Sterling Heights|        Macomb|        MI|      26|    132255|   57350205|     55|       F|               White|           3|                   N|  66870954| Theft From Building| Larceny/Theft Off...|      55.0|         132255.0|               White| Theft From Building|                          N|              F|          0.0|             8.0|                  1.0|           0.0|[132255.0,55.0,0....|[-0.2489591256372...|\n",
            "|             45| 62209685|     6531|        Florence|         Boone|        KY|      21|     32357|   57350769|     24|       F|               White|           2|                   R|  64580216|   All Other Larceny| Larceny/Theft Off...|      24.0|          32357.0|               White|   All Other Larceny|                          R|              F|          0.0|             2.0|                  0.0|           0.0|[32357.0,24.0,0.0...|[-0.5337820141706...|\n",
            "|             51| 62210099|     8714|Sterling Heights|        Macomb|        MI|      26|    132255|   57351165|     77|       F|               White|           3|                   R|  66867546|      Simple Assault|     Assault Offenses|      77.0|         132255.0|               White|      Simple Assault|                          R|              F|          0.0|             0.0|                  0.0|           0.0|(6,[0,1],[132255....|[-0.2489591256372...|\n",
            "|             14| 62210668|    14792|       Massillon|         Stark|        OH|      39|     28492|   57351640|     22|       F|               White|           2|                   R|  63752267|Theft From Motor ...| Larceny/Theft Off...|      22.0|          28492.0|               White|Theft From Motor ...|                          R|              F|          0.0|             4.0|                  0.0|           0.0|[28492.0,22.0,0.0...|[-0.5448016588500...|\n",
            "|             51| 62210698|    14792|       Massillon|         Stark|        OH|      39|     28492|   57351668|     29|       F|               White|           2|                   U|  63753020|      Simple Assault|     Assault Offenses|      29.0|          28492.0|               White|      Simple Assault|                          U|              F|          0.0|             0.0|                  2.0|           0.0|[28492.0,29.0,0.0...|[-0.5448016588500...|\n",
            "+---------------+---------+---------+----------------+--------------+----------+--------+----------+-----------+-------+--------+--------------------+------------+--------------------+----------+--------------------+---------------------+----------+-----------------+--------------------+--------------------+---------------------------+---------------+-------------+----------------+---------------------+--------------+--------------------+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "features_array = np.array(scaled_df.select(\"scaledFeatures\").rdd.map(lambda row: row[0].toArray()).collect())\n",
        "\n"
      ],
      "metadata": {
        "id": "U_sBv0bGtErL"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "input_dim = features_array.shape[1]\n",
        "encoding_dim = 6  # Adjust based on your dataset\n",
        "\n",
        "# Define the autoencoder architecture\n",
        "input_layer = Input(shape=(input_dim,))\n",
        "encoder = Dense(encoding_dim, activation='relu')(input_layer)\n",
        "decoder = Dense(input_dim, activation='sigmoid')(encoder)\n",
        "\n",
        "autoencoder = Model(inputs=input_layer, outputs=decoder)\n",
        "autoencoder.compile(optimizer='adam', loss='mean_squared_error')"
      ],
      "metadata": {
        "id": "XZWpf1EZgl3N"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "autoencoder.fit(features_array, features_array, epochs=5, batch_size=32, shuffle=True)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "qb-PM3PnhO9k",
        "outputId": "060dc947-046b-4518-9e2d-46e6f06501e4"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Epoch 1/5\n",
            "\u001b[1m213213/213213\u001b[0m \u001b[32m━━━━━━━━━━━━━━━━━━━━\u001b[0m\u001b[37m\u001b[0m \u001b[1m326s\u001b[0m 2ms/step - loss: 0.5135\n",
            "Epoch 2/5\n",
            "\u001b[1m213213/213213\u001b[0m \u001b[32m━━━━━━━━━━━━━━━━━━━━\u001b[0m\u001b[37m\u001b[0m \u001b[1m380s\u001b[0m 2ms/step - loss: 0.5139\n",
            "Epoch 3/5\n",
            "\u001b[1m213213/213213\u001b[0m \u001b[32m━━━━━━━━━━━━━━━━━━━━\u001b[0m\u001b[37m\u001b[0m \u001b[1m382s\u001b[0m 2ms/step - loss: 0.5138\n",
            "Epoch 4/5\n",
            "\u001b[1m213213/213213\u001b[0m \u001b[32m━━━━━━━━━━━━━━━━━━━━\u001b[0m\u001b[37m\u001b[0m \u001b[1m380s\u001b[0m 2ms/step - loss: 0.5133\n",
            "Epoch 5/5\n",
            "\u001b[1m213213/213213\u001b[0m \u001b[32m━━━━━━━━━━━━━━━━━━━━\u001b[0m\u001b[37m\u001b[0m \u001b[1m384s\u001b[0m 2ms/step - loss: 0.5136\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<keras.src.callbacks.history.History at 0x7aa046bff8e0>"
            ]
          },
          "metadata": {},
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "reconstructed_features = autoencoder.predict(features_array)\n",
        "print(reconstructed_features)\n",
        "# Calculate the reconstruction error (Mean Squared Error)\n",
        "reconstruction_error = np.mean(np.square(features_array - reconstructed_features), axis=1)\n",
        "\n",
        "print(reconstruction_error)\n",
        "print(np.mean(reconstruction_error))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "PWG3yqo4hO_z",
        "outputId": "249d5e0e-f406-4131-df50-567dfa042cc1"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\u001b[1m213213/213213\u001b[0m \u001b[32m━━━━━━━━━━━━━━━━━━━━\u001b[0m\u001b[37m\u001b[0m \u001b[1m267s\u001b[0m 1ms/step\n",
            "[[1.99430063e-03 5.02994794e-07 9.99992847e-01 9.96359518e-07\n",
            "  1.08034816e-03 2.75678392e-15]\n",
            " [1.19443583e-04 9.99955773e-01 3.85481478e-08 9.95519940e-07\n",
            "  3.42459907e-03 1.41599270e-13]\n",
            " [1.20082877e-04 9.99954820e-01 6.74541312e-08 9.98615974e-07\n",
            "  7.14824438e-01 3.09277696e-13]\n",
            " ...\n",
            " [4.44600475e-04 6.90870820e-06 1.72083318e-07 1.00029933e-06\n",
            "  9.99991536e-01 6.15903543e-16]\n",
            " [4.17151896e-04 1.07037595e-04 9.99995112e-01 9.98637006e-07\n",
            "  1.39991827e-02 1.88412840e-14]\n",
            " [8.51684454e-05 1.22941157e-03 1.00000000e+00 1.00000000e+00\n",
            "  1.00000000e+00 1.00000000e+00]]\n",
            "[ 0.63277002  0.5108052   0.49589767 ...  0.78927482  0.26158986\n",
            " 24.41835297]\n",
            "0.5136450201402303\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import pyspark.sql.functions as F\n",
        "from pyspark.sql.functions import monotonically_increasing_id\n",
        "\n",
        "\n",
        "risk_scores = reconstruction_error\n",
        "\n",
        "# Add the risk scores back to your original DataFrame\n",
        "risk_scores_df = spark.createDataFrame([(float(score),) for score in risk_scores], [\"risk_score\"])\n",
        "processed_data= scaled_df.withColumn(\"id\", monotonically_increasing_id())\n",
        "risk_scores_df = risk_scores_df.withColumn(\"id\", monotonically_increasing_id())\n",
        "\n",
        "# risk_scores_df.show()\n",
        "# final_df = processed_data.join(risk_scores_df)\n",
        "\n",
        "final_df = processed_data.join(risk_scores_df, \"id\")\n",
        "\n",
        "newFinalDF = final_df.drop(\"features_for_scaling\", \"scaledFeatures\")\n",
        "newFinalDF.show(truncate=False)# Show the final DataFrame with risk scores\n",
        "# final_df.select(\"age_num\", \"offense_name\", \"risk_score\").show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 436
        },
        "id": "uMo4JnmxhPCD",
        "outputId": "352f2bdc-2b72-4e30-84cd-7ccd9b80cc14"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "error",
          "ename": "KeyboardInterrupt",
          "evalue": "",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-24-a20915086513>\u001b[0m in \u001b[0;36m<cell line: 8>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      6\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      7\u001b[0m \u001b[0;31m# Add the risk scores back to your original DataFrame\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 8\u001b[0;31m \u001b[0mrisk_scores_df\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mspark\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcreateDataFrame\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfloat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mscore\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mscore\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mrisk_scores\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m\"risk_score\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      9\u001b[0m \u001b[0mprocessed_data\u001b[0m\u001b[0;34m=\u001b[0m \u001b[0mscaled_df\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwithColumn\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"id\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmonotonically_increasing_id\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     10\u001b[0m \u001b[0mrisk_scores_df\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mrisk_scores_df\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwithColumn\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"id\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmonotonically_increasing_id\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/pyspark/sql/session.py\u001b[0m in \u001b[0;36mcreateDataFrame\u001b[0;34m(self, data, schema, samplingRatio, verifySchema)\u001b[0m\n\u001b[1;32m   1441\u001b[0m                 \u001b[0mdata\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mschema\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msamplingRatio\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mverifySchema\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1442\u001b[0m             )\n\u001b[0;32m-> 1443\u001b[0;31m         return self._create_dataframe(\n\u001b[0m\u001b[1;32m   1444\u001b[0m             \u001b[0mdata\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mschema\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msamplingRatio\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mverifySchema\u001b[0m  \u001b[0;31m# type: ignore[arg-type]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1445\u001b[0m         )\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/pyspark/sql/session.py\u001b[0m in \u001b[0;36m_create_dataframe\u001b[0;34m(self, data, schema, samplingRatio, verifySchema)\u001b[0m\n\u001b[1;32m   1483\u001b[0m             \u001b[0mrdd\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstruct\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_createFromRDD\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmap\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mprepare\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mschema\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msamplingRatio\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1484\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1485\u001b[0;31m             \u001b[0mrdd\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstruct\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_createFromLocal\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmap\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mprepare\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdata\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mschema\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1486\u001b[0m         \u001b[0;32massert\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_jvm\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1487\u001b[0m         \u001b[0mjrdd\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_jvm\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mSerDeUtil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtoJavaArray\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrdd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_to_java_object_rdd\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/pyspark/sql/session.py\u001b[0m in \u001b[0;36m_createFromLocal\u001b[0;34m(self, data, schema)\u001b[0m\n\u001b[1;32m   1091\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1092\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mschema\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mNone\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mschema\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mlist\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtuple\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1093\u001b[0;31m             \u001b[0mstruct\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_inferSchemaFromList\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mnames\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mschema\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1094\u001b[0m             \u001b[0mconverter\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_create_converter\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstruct\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1095\u001b[0m             \u001b[0mtupled_data\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mIterable\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mTuple\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mmap\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconverter\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdata\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/pyspark/sql/session.py\u001b[0m in \u001b[0;36m_inferSchemaFromList\u001b[0;34m(self, data, names)\u001b[0m\n\u001b[1;32m    953\u001b[0m         \u001b[0minfer_array_from_first_element\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_jconf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mlegacyInferArrayTypeFromFirstElement\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    954\u001b[0m         \u001b[0mprefer_timestamp_ntz\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mis_timestamp_ntz_preferred\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 955\u001b[0;31m         schema = reduce(\n\u001b[0m\u001b[1;32m    956\u001b[0m             \u001b[0m_merge_type\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    957\u001b[0m             (\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/pyspark/sql/session.py\u001b[0m in \u001b[0;36m<genexpr>\u001b[0;34m(.0)\u001b[0m\n\u001b[1;32m    956\u001b[0m             \u001b[0m_merge_type\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    957\u001b[0m             (\n\u001b[0;32m--> 958\u001b[0;31m                 _infer_schema(\n\u001b[0m\u001b[1;32m    959\u001b[0m                     \u001b[0mrow\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    960\u001b[0m                     \u001b[0mnames\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/pyspark/sql/types.py\u001b[0m in \u001b[0;36m_infer_schema\u001b[0;34m(row, names, infer_dict_as_struct, infer_array_from_first_element, prefer_timestamp_ntz)\u001b[0m\n\u001b[1;32m   1707\u001b[0m                 \u001b[0mmessage_parameters\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m{\u001b[0m\u001b[0;34m\"field_name\"\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mk\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1708\u001b[0m             )\n\u001b[0;32m-> 1709\u001b[0;31m     \u001b[0;32mreturn\u001b[0m \u001b[0mStructType\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfields\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1710\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1711\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/pyspark/sql/types.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, fields)\u001b[0m\n\u001b[1;32m    791\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    792\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfields\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mfields\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 793\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mnames\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0mf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mname\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mf\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mfields\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    794\u001b[0m             assert all(\n\u001b[1;32m    795\u001b[0m                 \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mStructField\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mf\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mfields\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/pyspark/sql/types.py\u001b[0m in \u001b[0;36m<listcomp>\u001b[0;34m(.0)\u001b[0m\n\u001b[1;32m    791\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    792\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfields\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mfields\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 793\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mnames\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0mf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mname\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mf\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mfields\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    794\u001b[0m             assert all(\n\u001b[1;32m    795\u001b[0m                 \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mStructField\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mf\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mfields\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mKeyboardInterrupt\u001b[0m: "
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.ml import Pipeline\n",
        "newFinalDF = final_df.drop(\"features_for_scaling\", \"scaledFeatures\")\n",
        "newFinalDF.show(truncate=False)\n",
        "#choose age and crime type as features to get risk score using autoencoder\n",
        "# import pandas as pd\n",
        "# import numpy as np\n",
        "# from sklearn.preprocessing import OneHotEncoder, StandardScaler\n",
        "# from tensorflow.keras.models import Model\n",
        "# from tensorflow.keras.layers import Input, Dense\n",
        "# from sklearn.metrics import mean_squared_error\n",
        "# vector_assemblerAge = VectorAssembler(inputCols=[\"age_num\"], outputCol=\"age_vec\")\n",
        "# vector_assemblerPopulation = VectorAssembler(inputCols=[\"population_filled\"], outputCol=\"population_vec\")\n",
        "\n",
        "# scaler_age = StandardScaler(inputCol=\"age_vec\", outputCol=\"scaled_age_vector\", withMean=True, withStd=True)\n",
        "#scaler_pop = StandardScaler(inputCol=\"population_vec\", outputCol=\"scaled_pop_vector\", withMean=True, withStd=True)\n",
        "# vector_df = vector_assemblerAge.transform(res)\n",
        "#vector_df.show()"
      ],
      "metadata": {
        "id": "Pl7vwSrYYanU",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "3501f10d-5b7d-4212-89d3-09d866729146"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+---------------+---------+---------+----------------+--------------+----------+--------+----------+-----------+-------+--------+-------+------------+--------------------+----------+----------------------------------------+----------------------------------------+----------+-----------------+--------------+----------------------------------------+---------------------------+---------------+-------------+----------------+---------------------+--------------+-------------------+\n",
            "|id |offense_type_id|victim_id|agency_id|city_name       |primary_county|state_abbr|state_id|population|incident_id|age_num|sex_code|race_id|ethnicity_id|resident_status_code|offense_id|offense_name                            |offense_category_name                   |age_filled|population_filled|race_id_filled|offense_name_filled                     |resident_status_code_filled|sex_code_filled|race_id_index|crime_type_index|resident_status_index|sex_code_index|risk_score         |\n",
            "+---+---------------+---------+---------+----------------+--------------+----------+--------+----------+-----------+-------+--------+-------+------------+--------------------+----------+----------------------------------------+----------------------------------------+----------+-----------------+--------------+----------------------------------------+---------------------------+---------------+-------------+----------------+---------------------+--------------+-------------------+\n",
            "|0  |45             |62205773 |8714     |Sterling Heights|Macomb        |MI        |26      |132255    |57347297   |10     |M       |White  |2           |R                   |66872038  |All Other Larceny                       |Larceny/Theft Offenses                  |10.0      |132255.0         |White         |All Other Larceny                       |R                          |M              |0.0          |2.0             |0.0                  |1.0           |0.6327700219783486 |\n",
            "|19 |51             |62210698 |14792    |Massillon       |Stark         |OH        |39      |28492     |57351668   |29     |F       |White  |2           |U                   |63753020  |Simple Assault                          |Assault Offenses                        |29.0      |28492.0          |White         |Simple Assault                          |U                          |F              |0.0          |0.0             |2.0                  |0.0           |1.1949467465540216 |\n",
            "|22 |51             |62211216 |8714     |Sterling Heights|Macomb        |MI        |26      |132255    |57352172   |46     |F       |White  |3           |R                   |66888375  |Simple Assault                          |Assault Offenses                        |46.0      |132255.0         |White         |Simple Assault                          |R                          |F              |0.0          |0.0             |0.0                  |0.0           |0.39023496405131336|\n",
            "|26 |44             |62213395 |8714     |Sterling Heights|Macomb        |MI        |26      |132255    |57354965   |25     |F       |White  |3           |N                   |66892578  |Intimidation                            |Assault Offenses                        |25.0      |132255.0         |White         |Intimidation                            |N                          |F              |0.0          |6.0             |1.0                  |0.0           |0.3582040665576209 |\n",
            "|29 |51             |62214440 |8714     |Sterling Heights|Macomb        |MI        |26      |132255    |57355911   |60     |M       |White  |3           |N                   |66888456  |Simple Assault                          |Assault Offenses                        |60.0      |132255.0         |White         |Simple Assault                          |N                          |M              |0.0          |0.0             |1.0                  |1.0           |0.2223923666378641 |\n",
            "|54 |5              |62219444 |6536     |Paris           |Bourbon       |KY        |21      |9747      |57359557   |69     |M       |White  |2           |R                   |64600869  |Destruction/Damage/Vandalism of Property|Destruction/Damage/Vandalism of Property|69.0      |9747.0           |White         |Destruction/Damage/Vandalism of Property|R                          |M              |0.0          |1.0             |0.0                  |1.0           |0.32757416546418067|\n",
            "|65 |44             |62222250 |21680    |Beckley         |Raleigh       |WV        |55      |17137     |57362047   |52     |F       |White  |2           |N                   |65925860  |Intimidation                            |Assault Offenses                        |52.0      |17137.0          |White         |Intimidation                            |N                          |F              |0.0          |6.0             |1.0                  |0.0           |0.2906122240847251 |\n",
            "|112|5              |62235258 |21708    |Hinton          |Summers       |WV        |55      |2527      |57374593   |47     |F       |White  |2           |R                   |65943865  |Destruction/Damage/Vandalism of Property|Destruction/Damage/Vandalism of Property|47.0      |2527.0           |White         |Destruction/Damage/Vandalism of Property|R                          |F              |0.0          |1.0             |0.0                  |0.0           |0.39386981268780824|\n",
            "|113|40             |62235504 |6541     |Ashland         |Boyd          |KY        |21      |21232     |57374991   |40     |M       |White  |2           |R                   |64615334  |Robbery                                 |Robbery                                 |40.0      |21232.0          |White         |Robbery                                 |R                          |M              |0.0          |11.0            |0.0                  |1.0           |0.15680110936226352|\n",
            "|130|51             |62242706 |21741    |Kenova          |Wayne         |WV        |55      |3068      |57381385   |32     |M       |White  |2           |R                   |65960350  |Simple Assault                          |Assault Offenses                        |32.0      |3068.0           |White         |Simple Assault                          |R                          |M              |0.0          |0.0             |0.0                  |1.0           |0.31520737927359294|\n",
            "|155|2              |62253440 |21769    |Parkersburg     |Wood          |WV        |55      |30885     |57391094   |41     |M       |White  |2           |R                   |65974901  |False Pretenses/Swindle/Confidence Game |Fraud Offenses                          |41.0      |30885.0          |White         |False Pretenses/Swindle/Confidence Game |R                          |M              |0.0          |10.0            |0.0                  |1.0           |0.14442389553232984|\n",
            "|167|45             |62259904 |21769    |Parkersburg     |Wood          |WV        |55      |30885     |57396886   |55     |M       |White  |2           |U                   |65979550  |All Other Larceny                       |Larceny/Theft Offenses                  |55.0      |30885.0          |White         |All Other Larceny                       |U                          |M              |0.0          |2.0             |2.0                  |1.0           |0.8922260680283389 |\n",
            "|191|49             |62266806 |6569     |Louisville      |Bullitt       |KY        |21      |8103      |57404108   |42     |M       |White  |2           |R                   |64650877  |Burglary/Breaking & Entering            |Burglary/Breaking & Entering            |42.0      |8103.0           |White         |Burglary/Breaking & Entering            |R                          |M              |0.0          |3.0             |0.0                  |1.0           |0.17265065115042852|\n",
            "|196|21             |62269231 |8716     |Warren          |Macomb        |MI        |26      |135367    |57407289   |46     |M       |White  |2           |R                   |66977116  |Motor Vehicle Theft                     |Motor Vehicle Theft                     |46.0      |135367.0         |White         |Motor Vehicle Theft                     |R                          |M              |0.0          |7.0             |0.0                  |1.0           |0.10652962388510394|\n",
            "|198|45             |62271110 |6578     |Princeton       |Caldwell      |KY        |21      |6166      |57407981   |60     |M       |White  |2           |R                   |64655819  |All Other Larceny                       |Larceny/Theft Offenses                  |60.0      |6166.0           |White         |All Other Larceny                       |R                          |M              |0.0          |2.0             |0.0                  |1.0           |0.20704681399214708|\n",
            "|222|5              |62280125 |8717     |Clinton Township|Macomb        |MI        |26      |99662     |57418017   |51     |F       |White  |3           |R                   |67006168  |Destruction/Damage/Vandalism of Property|Destruction/Damage/Vandalism of Property|51.0      |99662.0          |White         |Destruction/Damage/Vandalism of Property|R                          |F              |0.0          |1.0             |0.0                  |0.0           |0.35025392532262334|\n",
            "|237|45             |62284381 |8717     |Clinton Township|Macomb        |MI        |26      |99662     |57421845   |78     |F       |White  |3           |R                   |67008556  |All Other Larceny                       |Larceny/Theft Offenses                  |78.0      |99662.0          |White         |All Other Larceny                       |R                          |F              |0.0          |2.0             |0.0                  |0.0           |0.5859260620776726 |\n",
            "|241|44             |62285403 |8717     |Clinton Township|Macomb        |MI        |26      |99662     |57422773   |18     |M       |White  |3           |R                   |66991748  |Intimidation                            |Assault Offenses                        |18.0      |99662.0          |White         |Intimidation                            |R                          |M              |0.0          |6.0             |0.0                  |1.0           |0.37215385672095075|\n",
            "|243|14             |62285449 |8717     |Clinton Township|Macomb        |MI        |26      |99662     |57422816   |43     |F       |White  |3           |R                   |67008579  |Theft From Motor Vehicle                |Larceny/Theft Offenses                  |43.0      |99662.0          |White         |Theft From Motor Vehicle                |R                          |F              |0.0          |4.0             |0.0                  |0.0           |0.27574750490167205|\n",
            "|270|11             |62292930 |8717     |Clinton Township|Macomb        |MI        |26      |99662     |57429535   |71     |M       |White  |2           |U                   |67016602  |Counterfeiting/Forgery                  |Counterfeiting/Forgery                  |71.0      |99662.0          |White         |Counterfeiting/Forgery                  |U                          |M              |0.0          |14.0            |2.0                  |1.0           |1.0509724076942162 |\n",
            "+---+---------------+---------+---------+----------------+--------------+----------+--------+----------+-----------+-------+--------+-------+------------+--------------------+----------+----------------------------------------+----------------------------------------+----------+-----------------+--------------+----------------------------------------+---------------------------+---------------+-------------+----------------+---------------------+--------------+-------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "print(newFinalDF.count())\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "QSNSd7P4Jk5q",
        "outputId": "27b878b4-aa8f-424d-ea57-be66373ab3fd"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2032348\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "newFinalDF.coalesce(1).write.csv(\"FinalCityDataRiskScores.csv\", header=True)\n",
        "# assembler = VectorAssembler(inputCols=[\"age_num\", \"log_population\"], outputCol=\"features_vec\")\n",
        "# vector_df = assembler.transform(res)\n",
        "# vector_df.show()"
      ],
      "metadata": {
        "id": "Ns-gmNq9KH8f"
      },
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "colab": {
      "provenance": [],
      "gpuType": "T4"
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    },
    "accelerator": "GPU"
  },
  "nbformat": 4,
  "nbformat_minor": 0
}