research-notebook.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/karnjj/sed-young-wa/blob/main/research-notebook.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "FXw-I6JLcX_3"
      },
      "outputs": [],
      "source": [
        "# prepare folder\n",
        "!mkdir pipeline_config\n",
        "!mkdir source\n",
        "!mkdir result"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "C_bBcws1b26_"
      },
      "outputs": [],
      "source": [
        "import os \n",
        "# careful try/except at `load traffy` and  `create weather` part \n",
        "CONFIG_DIR = './pipeline_config'\n",
        "DATA_DIR = './source'\n",
        "RESULT_DIR = './result'\n",
        "\n",
        "# training config\n",
        "# number of day for scraping weather \n",
        "DAY_OF_HIST_WEATHER = 40 \n",
        "\n",
        "# the year of holiday data.\n",
        "YEAR_IN_THAI = 2566\n",
        "\n",
        "# using data for training between [0, current_day - TIME_OFFSET]\n",
        "TIME_OFFSET = 4 "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aeNZF4r7Rqj4"
      },
      "source": [
        "# Initial Spark"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "0wZOIJ15RtTI"
      },
      "outputs": [],
      "source": [
        "# For Spark\n",
        "!apt-get install openjdk-8-jdk-headless -qq > /dev/null\n",
        "!wget -q https://dlcdn.apache.org/spark/spark-3.3.2/spark-3.3.2-bin-hadoop3.tgz\n",
        "!tar xf spark-3.3.2-bin-hadoop3.tgz\n",
        "!mv spark-3.3.2-bin-hadoop3 spark\n",
        "!pip install -q findspark\n",
        "\n",
        "import os\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-8-openjdk-amd64\"\n",
        "os.environ[\"SPARK_HOME\"] = \"/content/spark\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "AotJRlkvRvoM"
      },
      "outputs": [],
      "source": [
        "import findspark\n",
        "findspark.init()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ewxunlTbR-Rq",
        "outputId": "e4a2d616-20d6-4a67-801e-63abac69645b"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "            <div>\n",
              "                <p><b>SparkSession - in-memory</b></p>\n",
              "                \n",
              "        <div>\n",
              "            <p><b>SparkContext</b></p>\n",
              "\n",
              "            <p><a href=\"http://5f7876e52cc8:4050\">Spark UI</a></p>\n",
              "\n",
              "            <dl>\n",
              "              <dt>Version</dt>\n",
              "                <dd><code>v3.3.2</code></dd>\n",
              "              <dt>Master</dt>\n",
              "                <dd><code>local</code></dd>\n",
              "              <dt>AppName</dt>\n",
              "                <dd><code>Colab</code></dd>\n",
              "            </dl>\n",
              "        </div>\n",
              "        \n",
              "            </div>\n",
              "        "
            ],
            "text/plain": [
              "<pyspark.sql.session.SparkSession at 0x7f56942c79d0>"
            ]
          },
          "execution_count": 5,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder\\\n",
        "        .master(\"local\")\\\n",
        "        .appName(\"Colab\")\\\n",
        "        .config('spark.ui.port', '4050')\\\n",
        "        .getOrCreate()\n",
        "spark"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bTTxQ7gpRhdF"
      },
      "source": [
        "# [Resource] Load latest data and resources\n",
        "Load the data from traffy fondue api"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "00l56wa_Pcwa"
      },
      "source": [
        "## Load Traffy fondue api"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Y1E-q1qaIcXe",
        "outputId": "0eb0d824-49db-426d-d080-ec0e372151c3"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "--2023-05-18 15:42:33--  https://publicapi.traffy.in.th/dump-csv-chadchart/bangkok_traffy.csv\n",
            "Resolving publicapi.traffy.in.th (publicapi.traffy.in.th)... 35.201.64.130\n",
            "Connecting to publicapi.traffy.in.th (publicapi.traffy.in.th)|35.201.64.130|:443... connected.\n",
            "HTTP request sent, awaiting response... 200 OK\n",
            "Length: 361890441 (345M) [text/csv]\n",
            "Saving to: ‘./source/bangkok_traffy.csv’\n",
            "\n",
            "bangkok_traffy.csv  100%[===================>] 345.12M  21.9MB/s    in 16s     \n",
            "\n",
            "2023-05-18 15:42:50 (21.4 MB/s) - ‘./source/bangkok_traffy.csv’ saved [361890441/361890441]\n",
            "\n"
          ]
        }
      ],
      "source": [
        "!wget https://publicapi.traffy.in.th/dump-csv-chadchart/bangkok_traffy.csv -P $DATA_DIR"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XoZzSgqSPh3o"
      },
      "source": [
        "## Weather scraping\n",
        "output `df_weather` which contrain historical weather data."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "QPmvXJpjPlua"
      },
      "outputs": [],
      "source": [
        "import warnings\n",
        "warnings.simplefilter(action='ignore', category=FutureWarning)\n",
        "import requests\n",
        "import pandas as pd\n",
        "from tqdm import tqdm\n",
        "import pytz\n",
        "import time as os_time\n",
        "import random \n",
        "from bs4 import BeautifulSoup\n",
        "pd.options.mode.chained_assignment = None\n",
        "\n",
        "def get_api_key():\n",
        "  url = requests.get(\"https://www.wunderground.com/weather/th/bangkok/VTBD\")\n",
        "  soup = BeautifulSoup(url.content, 'html.parser')\n",
        "\n",
        "  soup = soup.find(\"script\", {\"id\":\"app-root-state\"}).text\n",
        "\n",
        "  start_idx = soup.find(\"apiKey=\")\n",
        "  end_idx = soup.find(\"&a\", start_idx)\n",
        "\n",
        "  api_key = soup[start_idx: end_idx].replace(\"apiKey=\", \"\")\n",
        "\n",
        "  return api_key\n",
        "\n",
        "def fill_missing(df):\n",
        "  if len(df) != 48:\n",
        "    periods = ['00:00', '00:30', '01:00', '01:30', '02:00', '02:30',\n",
        "                '03:00', '03:30', '04:00', '04:30', '05:00', '05:30',\n",
        "                '06:00', '06:30', '07:00', '07:30', '08:00', '08:30',\n",
        "                '09:00', '09:30', '10:00', '10:30', '11:00', '11:30',\n",
        "                '12:00', '12:30', '13:00', '13:30', '14:00', '14:30',\n",
        "                '15:00', '15:30', '16:00', '16:30', '17:00', '17:30',\n",
        "                '18:00', '18:30', '19:00', '19:30', '20:00', '20:30',\n",
        "                '21:00', '21:30', '22:00', '22:30', '23:00', '23:30']\n",
        "    for period in periods:\n",
        "        if period not in df['time'].values:\n",
        "            df = df.append({'time': period}, ignore_index=True)\n",
        "    df = df.sort_values(by=['time'])\n",
        "    df = df.fillna(method='ffill')\n",
        "    df = df.drop_duplicates(subset=['time'], keep='first')\n",
        "    df = df.reset_index(drop=True)\n",
        "  return df\n",
        "\n",
        "def scrap_weather_v2(n_day):\n",
        "\n",
        "  api_key = get_api_key()\n",
        "\n",
        "  all_df = pd.DataFrame()\n",
        "\n",
        "  today = datetime.now()\n",
        "  for i in tqdm(range(1, n_day + 1)):\n",
        "    df = pd.DataFrame(columns=[\"date\",\"time\",\"temp (F)\",\"feel_like (F)\",\"dew_point (F)\",\"humidity (%)\",\"wind\",\"wind_speed (mph)\",\"wind_gust (mph)\",\"pressure (in)\",\"precip (in)\",\"condition\"])\n",
        "    target_date = today - timedelta(days=i)\n",
        "    historical_data = requests.get(\"https://api.weather.com/v1/location/VTBD:9:TH/observations/historical.json\",\n",
        "                                  params={\n",
        "                                      \"apiKey\": api_key,\n",
        "                                      \"units\": \"e\",\n",
        "                                      \"startDate\": target_date.strftime(\"%Y%m%d\")\n",
        "                                  }\n",
        "                                  ).json()                \n",
        "    historical_data['observations']\n",
        "    for data in historical_data['observations']:\n",
        "      date_time = datetime.fromtimestamp(data['valid_time_gmt'], pytz.timezone(\"Asia/Bangkok\"))\n",
        "      date = date_time.strftime('%Y-%m-%d')\n",
        "      time = date_time.strftime('%H:%M')\n",
        "      new_row = {\n",
        "          \"date\": date,\n",
        "          \"time\": time,\n",
        "          \"temp (F)\": data[\"temp\"],\n",
        "          \"feel_like (F)\": data[\"feels_like\"],\n",
        "          \"dew_point (F)\": data[\"dewPt\"],\n",
        "          \"humidity (%)\": data[\"rh\"],\n",
        "          \"wind\": data[\"wdir_cardinal\"],\n",
        "          \"wind_speed (mph)\": data[\"wspd\"] or 0,\n",
        "          \"wind_gust (mph)\": data[\"gust\"] or 0,\n",
        "          \"pressure (in)\": data[\"pressure\"],\n",
        "          \"precip (in)\": data[\"precip_hrly\"] or 0,\n",
        "          \"condition\": data[\"wx_phrase\"],\n",
        "      }\n",
        "\n",
        "      df = df.append(new_row, ignore_index=True)\n",
        "\n",
        "    df = df.dropna()\n",
        "    df = fill_missing(df)\n",
        "\n",
        "    all_df = all_df.append(df, ignore_index=True)\n",
        "\n",
        "    os_time.sleep(random.randint(1, 3))\n",
        "\n",
        "  return all_df"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ijIor__9PlK4",
        "outputId": "ec40844c-9023-46c7-ba43-4b80be2da986"
      },
      "outputs": [
        {
          "name": "stderr",
          "output_type": "stream",
          "text": [
            "100%|██████████| 40/40 [02:28<00:00,  3.71s/it]\n"
          ]
        }
      ],
      "source": [
        "from tqdm import tqdm\n",
        "from datetime import  datetime, timedelta\n",
        "\n",
        "try:\n",
        "  df_weather  \n",
        "except :\n",
        "  df_weather = scrap_weather_v2(n_day=DAY_OF_HIST_WEATHER)\n",
        "\n",
        "# concat datetime\n",
        "def concat_datetime(data) :\n",
        "  date = data['date'] + \" \" + data['time']\n",
        "  return datetime.strptime(date, \"%Y-%m-%d %H:%M\")\n",
        "\n",
        "df_weather['datetime'] = df_weather.apply(concat_datetime, axis=1)\n",
        "df_weather.head()\n",
        "df_weather.to_csv(os.path.join(DATA_DIR, \"weather_data\"))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dWTFST8GQL8u"
      },
      "source": [
        "## Holidays in Thailand (current Year)\n",
        "output `holidays` which contrains a list of holiday in date format."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "xRne77jFQPcR",
        "outputId": "fa134594-919d-4d8c-e53c-fdc920725d6d"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "\u001b[?25l     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m0.0/293.8 kB\u001b[0m \u001b[31m?\u001b[0m eta \u001b[36m-:--:--\u001b[0m\r\u001b[2K     \u001b[91m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m\u001b[90m╺\u001b[0m \u001b[32m286.7/293.8 kB\u001b[0m \u001b[31m8.7 MB/s\u001b[0m eta \u001b[36m0:00:01\u001b[0m\r\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m293.8/293.8 kB\u001b[0m \u001b[31m6.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25h"
          ]
        }
      ],
      "source": [
        "import sys\n",
        "\n",
        "IN_COLAB = 'google.colab' in sys.modules\n",
        "if IN_COLAB:\n",
        "    %pip -q install dateparser"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "BlEfhOl-QU6O",
        "outputId": "612be0ed-3b51-4b9e-b678-de34067ccc72"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "{\n",
            "    \"days\": [\n",
            "        \"10/12/2023\",\n",
            "        \"02/01/2023\",\n",
            "        \"23/10/2023\",\n",
            "        \"13/04/2023\",\n",
            "        \"14/08/2023\",\n",
            "        \"02/08/2023\",\n",
            "        \"17/04/2023\",\n",
            "        \"01/05/2023\",\n",
            "        \"05/12/2023\",\n",
            "        \"31/12/2023\",\n",
            "        \"01/08/2023\",\n",
            "        \"29/10/2023\",\n",
            "        \"17/05/2023\",\n",
            "        \"14/04/2023\",\n",
            "        \"06/04/2023\",\n",
            "        \"05/06/2023\",\n",
            "        \"28/07/2023\",\n",
            "        \"15/04/2023\",\n",
            "        \"01/01/2023\",\n",
            "        \"13/10/2023\",\n",
            "        \"03/06/2023\",\n",
            "        \"12/08/2023\",\n",
            "        \"04/05/2023\",\n",
            "        \"11/12/2023\",\n",
            "        \"06/03/2023\",\n",
            "        \"05/05/2023\"\n",
            "    ]\n",
            "}\n"
          ]
        }
      ],
      "source": [
        "from dateutil.relativedelta import relativedelta\n",
        "import requests\n",
        "from bs4 import BeautifulSoup\n",
        "import dateparser\n",
        "from datetime import datetime\n",
        "import json \n",
        "\n",
        "# Get current year\n",
        "url = f\"https://calendar.kapook.com/{YEAR_IN_THAI}/holiday\" \n",
        "\n",
        "url = requests.get(url)\n",
        "soup = BeautifulSoup(url.content, 'html.parser')\n",
        "\n",
        "soup = soup.find('div', {\"id\": \"holiday_wrap\"}).find_all('span', {\"class\": \"date\"})\n",
        "holidays = set()\n",
        "for x in soup :\n",
        "  x = x.text\n",
        "  dt = dateparser.parse(x)\n",
        "  dt = datetime(dt.year - 543, dt.month, dt.day)\n",
        "  holidays.add(dt.strftime(\"%d/%m/%Y\"))\n",
        "\n",
        "thai_holidays = {\"days\": list(holidays)}\n",
        "\n",
        "with open(os.path.join(DATA_DIR,\"thai_holidays.json\"), \"w\") as outfile:\n",
        "  json_object = json.dumps(thai_holidays, indent = 4, ensure_ascii=False) \n",
        "  print(json_object)\n",
        "  outfile.write(json_object)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "umUsEj0jSWHv"
      },
      "source": [
        "# Data Preparation"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fWIX4sbAc-TZ"
      },
      "source": [
        "## Load necessary data"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "y8zqlL30dBQU",
        "outputId": "fc7ac6f7-095f-4598-e9b4-c255728f2db0"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-17958a22-5a02-49b6-84b9-0e78995364a7\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>ticket_id</th>\n",
              "      <th>type</th>\n",
              "      <th>organization</th>\n",
              "      <th>comment</th>\n",
              "      <th>photo</th>\n",
              "      <th>photo_after</th>\n",
              "      <th>coords</th>\n",
              "      <th>address</th>\n",
              "      <th>subdistrict</th>\n",
              "      <th>district</th>\n",
              "      <th>province</th>\n",
              "      <th>timestamp</th>\n",
              "      <th>state</th>\n",
              "      <th>star</th>\n",
              "      <th>count_reopen</th>\n",
              "      <th>last_activity</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2021-9LHDM6</td>\n",
              "      <td>{}</td>\n",
              "      <td>None</td>\n",
              "      <td>ไม่มีภาพ</td>\n",
              "      <td>https://storage.googleapis.com/traffy_public_b...</td>\n",
              "      <td>None</td>\n",
              "      <td>100.48661,13.79386</td>\n",
              "      <td>1867 จรัญสนิทวงศ์ แขวง บางพลัด เขตบางพลัด กรุง...</td>\n",
              "      <td>บางพลัด</td>\n",
              "      <td>บางพลัด</td>\n",
              "      <td>กรุงเทพมหานคร</td>\n",
              "      <td>2021-09-01 10:44:55.353209</td>\n",
              "      <td>กำลังดำเนินการ</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2022-02-22 04:59:58.622268</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2021-FYJTFP</td>\n",
              "      <td>{ความสะอาด}</td>\n",
              "      <td>เขตบางซื่อ</td>\n",
              "      <td>ขยะเยอะ</td>\n",
              "      <td>https://storage.googleapis.com/traffy_public_b...</td>\n",
              "      <td>None</td>\n",
              "      <td>100.53084,13.81865</td>\n",
              "      <td>12/14 ถนน กรุงเทพ- นนทบุรี แขวง บางซื่อ เขตบาง...</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>กรุงเทพมหานคร</td>\n",
              "      <td>2021-09-03 12:51:09.453003</td>\n",
              "      <td>เสร็จสิ้น</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2022-06-04 15:34:14.609206</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-17958a22-5a02-49b6-84b9-0e78995364a7')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-17958a22-5a02-49b6-84b9-0e78995364a7 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-17958a22-5a02-49b6-84b9-0e78995364a7');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "     ticket_id         type organization   comment  \\\n",
              "0  2021-9LHDM6           {}         None  ไม่มีภาพ   \n",
              "1  2021-FYJTFP  {ความสะอาด}   เขตบางซื่อ   ขยะเยอะ   \n",
              "\n",
              "                                               photo photo_after  \\\n",
              "0  https://storage.googleapis.com/traffy_public_b...        None   \n",
              "1  https://storage.googleapis.com/traffy_public_b...        None   \n",
              "\n",
              "               coords                                            address  \\\n",
              "0  100.48661,13.79386  1867 จรัญสนิทวงศ์ แขวง บางพลัด เขตบางพลัด กรุง...   \n",
              "1  100.53084,13.81865  12/14 ถนน กรุงเทพ- นนทบุรี แขวง บางซื่อ เขตบาง...   \n",
              "\n",
              "  subdistrict district       province                  timestamp  \\\n",
              "0     บางพลัด  บางพลัด  กรุงเทพมหานคร 2021-09-01 10:44:55.353209   \n",
              "1        None     None  กรุงเทพมหานคร 2021-09-03 12:51:09.453003   \n",
              "\n",
              "            state  star  count_reopen              last_activity  \n",
              "0  กำลังดำเนินการ   NaN           NaN 2022-02-22 04:59:58.622268  \n",
              "1       เสร็จสิ้น   NaN           NaN 2022-06-04 15:34:14.609206  "
            ]
          },
          "execution_count": 11,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# load traffy fondue\n",
        "from pyspark.sql import functions as F\n",
        "from pyspark.sql.types import *\n",
        "\n",
        "path = os.path.join(DATA_DIR, \"bangkok_traffy.csv\")\n",
        "\n",
        "spark_df = spark.read.csv(path, header=True, inferSchema=True, sep=',', escape=\"\\\"\", encoding='utf-8', multiLine=True)\n",
        "\n",
        "# For develop\n",
        "try :\n",
        "  df_raw\n",
        "except:\n",
        "  df_raw = spark_df.toPandas()\n",
        "\n",
        "df_raw.tail(5).to_csv(os.path.join(CONFIG_DIR, \"sample_query.csv\"))\n",
        "df_raw.head(2)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "eJioTtWZddHA"
      },
      "outputs": [],
      "source": [
        "# Load Thai holiday\n",
        "with open(os.path.join(DATA_DIR,\"thai_holidays.json\"), \"r\") as f:\n",
        "  holidays = json.load(f)['days']\n",
        "\n",
        "# Load weather data\n",
        "df_weather = pd.read_csv(os.path.join(DATA_DIR, \"weather_data\"), parse_dates=['datetime'])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "zpn--GH7lwVv",
        "outputId": "5928cd35-94dd-48f4-b9f2-8dfaa01ae782"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 1920 entries, 0 to 1919\n",
            "Data columns (total 14 columns):\n",
            " #   Column            Non-Null Count  Dtype         \n",
            "---  ------            --------------  -----         \n",
            " 0   Unnamed: 0        1920 non-null   int64         \n",
            " 1   date              1920 non-null   object        \n",
            " 2   time              1920 non-null   object        \n",
            " 3   temp (F)          1920 non-null   int64         \n",
            " 4   feel_like (F)     1920 non-null   int64         \n",
            " 5   dew_point (F)     1920 non-null   int64         \n",
            " 6   humidity (%)      1920 non-null   int64         \n",
            " 7   wind              1920 non-null   object        \n",
            " 8   wind_speed (mph)  1920 non-null   int64         \n",
            " 9   wind_gust (mph)   1920 non-null   int64         \n",
            " 10  pressure (in)     1920 non-null   float64       \n",
            " 11  precip (in)       1920 non-null   int64         \n",
            " 12  condition         1920 non-null   object        \n",
            " 13  datetime          1920 non-null   datetime64[ns]\n",
            "dtypes: datetime64[ns](1), float64(1), int64(8), object(4)\n",
            "memory usage: 210.1+ KB\n"
          ]
        }
      ],
      "source": [
        "df_weather.info()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "N1rGptQNPl5M"
      },
      "source": [
        "## Set mark day\n",
        "- Set current_day\n",
        "- Set end_date_train"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "pjVPz9_cQBue",
        "outputId": "0c5647b1-5fe5-4db1-c60e-851884fed720"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "Timestamp('2023-05-14 15:44:54.297808')"
            ]
          },
          "execution_count": 14,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "from datetime import datetime, timedelta\n",
        "import pandas as pd\n",
        "\n",
        "current_day = datetime.now()\n",
        "# current_day = datetime(2023,5,12)\n",
        "end_date_train = current_day - timedelta(days=TIME_OFFSET)\n",
        "df_raw['timestamp'] = pd.to_datetime(df_raw['timestamp'])\n",
        "df = df_raw[df_raw['timestamp'] <= end_date_train]\n",
        "max(df['timestamp'])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ooZI7ODijmHF",
        "outputId": "cbc38e4f-cb6f-43c6-f522-778caaf6d67a"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-d1016aad-b1dc-40bf-94e9-dccc595532eb\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>ticket_id</th>\n",
              "      <th>type</th>\n",
              "      <th>organization</th>\n",
              "      <th>comment</th>\n",
              "      <th>photo</th>\n",
              "      <th>photo_after</th>\n",
              "      <th>coords</th>\n",
              "      <th>address</th>\n",
              "      <th>subdistrict</th>\n",
              "      <th>district</th>\n",
              "      <th>province</th>\n",
              "      <th>timestamp</th>\n",
              "      <th>state</th>\n",
              "      <th>star</th>\n",
              "      <th>count_reopen</th>\n",
              "      <th>last_activity</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2021-9LHDM6</td>\n",
              "      <td>{}</td>\n",
              "      <td>None</td>\n",
              "      <td>ไม่มีภาพ</td>\n",
              "      <td>https://storage.googleapis.com/traffy_public_b...</td>\n",
              "      <td>None</td>\n",
              "      <td>100.48661,13.79386</td>\n",
              "      <td>1867 จรัญสนิทวงศ์ แขวง บางพลัด เขตบางพลัด กรุง...</td>\n",
              "      <td>บางพลัด</td>\n",
              "      <td>บางพลัด</td>\n",
              "      <td>กรุงเทพมหานคร</td>\n",
              "      <td>2021-09-01 10:44:55.353209</td>\n",
              "      <td>กำลังดำเนินการ</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2022-02-22 04:59:58.622268</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2021-FYJTFP</td>\n",
              "      <td>{ความสะอาด}</td>\n",
              "      <td>เขตบางซื่อ</td>\n",
              "      <td>ขยะเยอะ</td>\n",
              "      <td>https://storage.googleapis.com/traffy_public_b...</td>\n",
              "      <td>None</td>\n",
              "      <td>100.53084,13.81865</td>\n",
              "      <td>12/14 ถนน กรุงเทพ- นนทบุรี แขวง บางซื่อ เขตบาง...</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>กรุงเทพมหานคร</td>\n",
              "      <td>2021-09-03 12:51:09.453003</td>\n",
              "      <td>เสร็จสิ้น</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2022-06-04 15:34:14.609206</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-d1016aad-b1dc-40bf-94e9-dccc595532eb')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-d1016aad-b1dc-40bf-94e9-dccc595532eb button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-d1016aad-b1dc-40bf-94e9-dccc595532eb');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "     ticket_id         type organization   comment  \\\n",
              "0  2021-9LHDM6           {}         None  ไม่มีภาพ   \n",
              "1  2021-FYJTFP  {ความสะอาด}   เขตบางซื่อ   ขยะเยอะ   \n",
              "\n",
              "                                               photo photo_after  \\\n",
              "0  https://storage.googleapis.com/traffy_public_b...        None   \n",
              "1  https://storage.googleapis.com/traffy_public_b...        None   \n",
              "\n",
              "               coords                                            address  \\\n",
              "0  100.48661,13.79386  1867 จรัญสนิทวงศ์ แขวง บางพลัด เขตบางพลัด กรุง...   \n",
              "1  100.53084,13.81865  12/14 ถนน กรุงเทพ- นนทบุรี แขวง บางซื่อ เขตบาง...   \n",
              "\n",
              "  subdistrict district       province                  timestamp  \\\n",
              "0     บางพลัด  บางพลัด  กรุงเทพมหานคร 2021-09-01 10:44:55.353209   \n",
              "1        None     None  กรุงเทพมหานคร 2021-09-03 12:51:09.453003   \n",
              "\n",
              "            state  star  count_reopen              last_activity  \n",
              "0  กำลังดำเนินการ   NaN           NaN 2022-02-22 04:59:58.622268  \n",
              "1       เสร็จสิ้น   NaN           NaN 2022-06-04 15:34:14.609206  "
            ]
          },
          "execution_count": 15,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "df_raw.head(2)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "p3Rlcur5Nz0a"
      },
      "source": [
        "## Drop unused columns\n",
        "Select only necessary columns"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Q-wyDQdvOBYQ",
        "outputId": "0776e297-2333-4d33-e4a1-5b091d752d86"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "(202154, 5)\n"
          ]
        },
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-4989438e-0688-427b-9a83-5e54ed54c99e\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>ticket_id</th>\n",
              "      <th>type</th>\n",
              "      <th>district</th>\n",
              "      <th>timestamp</th>\n",
              "      <th>last_activity</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2021-FYJTFP</td>\n",
              "      <td>{ความสะอาด}</td>\n",
              "      <td>None</td>\n",
              "      <td>2021-09-03 12:51:09.453003</td>\n",
              "      <td>2022-06-04 15:34:14.609206</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2021-CGPMUN</td>\n",
              "      <td>{น้ำท่วม,ร้องเรียน}</td>\n",
              "      <td>ประเวศ</td>\n",
              "      <td>2021-09-19 14:56:08.924992</td>\n",
              "      <td>2022-06-21 08:21:09.532782</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>2021-7XATFA</td>\n",
              "      <td>{สะพาน}</td>\n",
              "      <td>สาทร</td>\n",
              "      <td>2021-09-26 05:03:52.594898</td>\n",
              "      <td>2022-06-06 01:17:12.272904</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>2021-9U2NJT</td>\n",
              "      <td>{น้ำท่วม}</td>\n",
              "      <td>None</td>\n",
              "      <td>2021-10-14 10:45:27.713884</td>\n",
              "      <td>2022-09-08 08:35:43.784519</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25</th>\n",
              "      <td>2021-DVEWYM</td>\n",
              "      <td>{น้ำท่วม,ถนน}</td>\n",
              "      <td>ลาดพร้าว</td>\n",
              "      <td>2021-12-09 12:29:08.408763</td>\n",
              "      <td>2022-08-12 07:18:44.884945</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-4989438e-0688-427b-9a83-5e54ed54c99e')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-4989438e-0688-427b-9a83-5e54ed54c99e button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-4989438e-0688-427b-9a83-5e54ed54c99e');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "      ticket_id                 type  district                  timestamp  \\\n",
              "1   2021-FYJTFP          {ความสะอาด}      None 2021-09-03 12:51:09.453003   \n",
              "4   2021-CGPMUN  {น้ำท่วม,ร้องเรียน}    ประเวศ 2021-09-19 14:56:08.924992   \n",
              "10  2021-7XATFA              {สะพาน}      สาทร 2021-09-26 05:03:52.594898   \n",
              "12  2021-9U2NJT            {น้ำท่วม}      None 2021-10-14 10:45:27.713884   \n",
              "25  2021-DVEWYM        {น้ำท่วม,ถนน}  ลาดพร้าว 2021-12-09 12:29:08.408763   \n",
              "\n",
              "                last_activity  \n",
              "1  2022-06-04 15:34:14.609206  \n",
              "4  2022-06-21 08:21:09.532782  \n",
              "10 2022-06-06 01:17:12.272904  \n",
              "12 2022-09-08 08:35:43.784519  \n",
              "25 2022-08-12 07:18:44.884945  "
            ]
          },
          "execution_count": 16,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "target_columns = [ \"ticket_id\", \"type\", \"district\", \"timestamp\", \"last_activity\"]\n",
        "df = df[~df['ticket_id'].isna()]\n",
        "df = df[df['state'] == \"เสร็จสิ้น\"]\n",
        "df = df[target_columns]\n",
        "\n",
        "print(df.shape)\n",
        "df.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Zx2bxykTVrzs"
      },
      "source": [
        "## Data cleaning\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bz_iw_1Ld2Ef"
      },
      "source": [
        "### Format date\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "-gYPmUwOXTWW"
      },
      "outputs": [],
      "source": [
        "df['timestamp'] = pd.to_datetime(df['timestamp'])\n",
        "df['last_activity'] = pd.to_datetime(df['last_activity'])"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vLievJIJQaip"
      },
      "source": [
        "### Deal with NULL value\n",
        "- Fill type with `{}`\n",
        "- Drop out the data which have missing of `district`. "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "vSvf_xN2kWzF",
        "outputId": "fc764e82-4c8c-4d62-ae1e-3caeeef9cfd4"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "ticket_id         0\n",
              "type             96\n",
              "district         47\n",
              "timestamp         0\n",
              "last_activity     0\n",
              "dtype: int64"
            ]
          },
          "execution_count": 18,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "df.isna().sum()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "PzqVh0qpU3Ns"
      },
      "outputs": [],
      "source": [
        "df['type'].fillna(\"{}\", inplace=True)\n",
        "df.dropna(subset=['district'], inplace=True)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SIwc1BZJNEjo"
      },
      "source": [
        "## Feature enginearing\n",
        "- Create a feature `time_of_day` which is the unit of a day in between 0-23.\n",
        "- Create type features which are the group of high frequency on the data."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "WTBbhxeDXCkt"
      },
      "outputs": [],
      "source": [
        "selected_feature_value = {\"types\":[], \"provinces\":[]}"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qMA4P3nzdC2z"
      },
      "source": [
        "### Hours finish\n",
        "- Create the `process_hour` from `last_activity` minus `timestamp`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "SPX-SN6JdEne",
        "outputId": "7ac9e3ff-c345-4fbc-a8d0-8bf9acf6e44a"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-65b8f020-458e-4c81-bdbe-4b89063fe8f8\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>ticket_id</th>\n",
              "      <th>type</th>\n",
              "      <th>district</th>\n",
              "      <th>timestamp</th>\n",
              "      <th>process_hour</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2021-CGPMUN</td>\n",
              "      <td>{น้ำท่วม,ร้องเรียน}</td>\n",
              "      <td>ประเวศ</td>\n",
              "      <td>2021-09-19 14:56:08.924992</td>\n",
              "      <td>6593</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>2021-7XATFA</td>\n",
              "      <td>{สะพาน}</td>\n",
              "      <td>สาทร</td>\n",
              "      <td>2021-09-26 05:03:52.594898</td>\n",
              "      <td>6068</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25</th>\n",
              "      <td>2021-DVEWYM</td>\n",
              "      <td>{น้ำท่วม,ถนน}</td>\n",
              "      <td>ลาดพร้าว</td>\n",
              "      <td>2021-12-09 12:29:08.408763</td>\n",
              "      <td>5898</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>64</th>\n",
              "      <td>2021-4D9Y98</td>\n",
              "      <td>{}</td>\n",
              "      <td>ลาดพร้าว</td>\n",
              "      <td>2021-12-13 05:53:36.861064</td>\n",
              "      <td>10950</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>102</th>\n",
              "      <td>2021-7U9RED</td>\n",
              "      <td>{}</td>\n",
              "      <td>ดุสิต</td>\n",
              "      <td>2021-12-17 08:46:02.610983</td>\n",
              "      <td>12381</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-65b8f020-458e-4c81-bdbe-4b89063fe8f8')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-65b8f020-458e-4c81-bdbe-4b89063fe8f8 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-65b8f020-458e-4c81-bdbe-4b89063fe8f8');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "       ticket_id                 type  district                  timestamp  \\\n",
              "4    2021-CGPMUN  {น้ำท่วม,ร้องเรียน}    ประเวศ 2021-09-19 14:56:08.924992   \n",
              "10   2021-7XATFA              {สะพาน}      สาทร 2021-09-26 05:03:52.594898   \n",
              "25   2021-DVEWYM        {น้ำท่วม,ถนน}  ลาดพร้าว 2021-12-09 12:29:08.408763   \n",
              "64   2021-4D9Y98                   {}  ลาดพร้าว 2021-12-13 05:53:36.861064   \n",
              "102  2021-7U9RED                   {}     ดุสิต 2021-12-17 08:46:02.610983   \n",
              "\n",
              "     process_hour  \n",
              "4            6593  \n",
              "10           6068  \n",
              "25           5898  \n",
              "64          10950  \n",
              "102         12381  "
            ]
          },
          "execution_count": 21,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "def get_process_hour(data) :\n",
        "  dt = data['last_activity'] - data['timestamp']\n",
        "  total_hour = dt.days * 24 + dt.seconds//3600\n",
        "  return total_hour\n",
        "df['process_hour'] = df.apply(get_process_hour, axis=1)\n",
        "df.drop([\"last_activity\"], axis=1, inplace = True)\n",
        "df.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "edhp4yq7p5aE"
      },
      "source": [
        "### Time of a day"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "-9bqleWsp992"
      },
      "outputs": [],
      "source": [
        "df['time_of_day'] = df['timestamp'].dt.hour"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8N_mFXzqPYPG"
      },
      "source": [
        "### Type\n",
        "- Filter with the frequency of each category."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "I2coFdz-SVro",
        "outputId": "329a2c6d-78a1-4f9f-9f61-53ddb07e7873"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "['ถนน', 'ทางเท้า', 'ความสะอาด', 'น้ำท่วม', 'ความปลอดภัย', 'กีดขวาง', 'แสงสว่าง']\n"
          ]
        },
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-6e02cad3-0da3-4f5a-a851-6101e50afb7d\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>ticket_id</th>\n",
              "      <th>type</th>\n",
              "      <th>district</th>\n",
              "      <th>timestamp</th>\n",
              "      <th>process_hour</th>\n",
              "      <th>time_of_day</th>\n",
              "      <th>ถนน</th>\n",
              "      <th>ทางเท้า</th>\n",
              "      <th>ความสะอาด</th>\n",
              "      <th>น้ำท่วม</th>\n",
              "      <th>ความปลอดภัย</th>\n",
              "      <th>กีดขวาง</th>\n",
              "      <th>แสงสว่าง</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2021-CGPMUN</td>\n",
              "      <td>{น้ำท่วม,ร้องเรียน}</td>\n",
              "      <td>ประเวศ</td>\n",
              "      <td>2021-09-19 14:56:08.924992</td>\n",
              "      <td>6593</td>\n",
              "      <td>14</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2021-7XATFA</td>\n",
              "      <td>{สะพาน}</td>\n",
              "      <td>สาทร</td>\n",
              "      <td>2021-09-26 05:03:52.594898</td>\n",
              "      <td>6068</td>\n",
              "      <td>5</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2021-DVEWYM</td>\n",
              "      <td>{น้ำท่วม,ถนน}</td>\n",
              "      <td>ลาดพร้าว</td>\n",
              "      <td>2021-12-09 12:29:08.408763</td>\n",
              "      <td>5898</td>\n",
              "      <td>12</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2021-4D9Y98</td>\n",
              "      <td>{}</td>\n",
              "      <td>ลาดพร้าว</td>\n",
              "      <td>2021-12-13 05:53:36.861064</td>\n",
              "      <td>10950</td>\n",
              "      <td>5</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2021-7U9RED</td>\n",
              "      <td>{}</td>\n",
              "      <td>ดุสิต</td>\n",
              "      <td>2021-12-17 08:46:02.610983</td>\n",
              "      <td>12381</td>\n",
              "      <td>8</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-6e02cad3-0da3-4f5a-a851-6101e50afb7d')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-6e02cad3-0da3-4f5a-a851-6101e50afb7d button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-6e02cad3-0da3-4f5a-a851-6101e50afb7d');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "     ticket_id                 type  district                  timestamp  \\\n",
              "0  2021-CGPMUN  {น้ำท่วม,ร้องเรียน}    ประเวศ 2021-09-19 14:56:08.924992   \n",
              "1  2021-7XATFA              {สะพาน}      สาทร 2021-09-26 05:03:52.594898   \n",
              "2  2021-DVEWYM        {น้ำท่วม,ถนน}  ลาดพร้าว 2021-12-09 12:29:08.408763   \n",
              "3  2021-4D9Y98                   {}  ลาดพร้าว 2021-12-13 05:53:36.861064   \n",
              "4  2021-7U9RED                   {}     ดุสิต 2021-12-17 08:46:02.610983   \n",
              "\n",
              "   process_hour  time_of_day  ถนน  ทางเท้า  ความสะอาด  น้ำท่วม  ความปลอดภัย  \\\n",
              "0          6593           14    0        0          0        1            0   \n",
              "1          6068            5    0        0          0        0            0   \n",
              "2          5898           12    1        0          0        1            0   \n",
              "3         10950            5    0        0          0        0            0   \n",
              "4         12381            8    0        0          0        0            0   \n",
              "\n",
              "   กีดขวาง  แสงสว่าง  \n",
              "0        0         0  \n",
              "1        0         0  \n",
              "2        0         0  \n",
              "3        0         0  \n",
              "4        0         0  "
            ]
          },
          "execution_count": 23,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "#@title Fillter high cardinality\n",
        "from mpl_toolkits.mplot3d.axes3d import defaultdict\n",
        "\n",
        "type_freq = defaultdict(int)\n",
        "get_by = \"rank\" #@param [\"ratio\", \"rank\"]\n",
        "k = 7 #@param \n",
        "ratio_value = 0.5 #@param \n",
        "\n",
        "\n",
        "def get_types(data):\n",
        "  data = data.strip()\n",
        "  data = data[1:-1] # trim \"{}\"\n",
        "  types = data.split(\",\")\n",
        "  return types\n",
        "\n",
        "# reset index\n",
        "df = df.reset_index(drop=True)\n",
        "\n",
        "# get set of type\n",
        "for i in range(len(df)) :\n",
        "  data = get_types(df['type'][i])\n",
        "  for d in data :\n",
        "      type_freq[d]+=1\n",
        "type_freq.pop(\"\")\n",
        "type_freq = sorted([(v/df.shape[0], k) for k,v in type_freq.items()], reverse=True)\n",
        "type_freq\n",
        "\n",
        "if get_by == \"ratio\" :\n",
        "  target_type = []\n",
        "  for x in type_freq :\n",
        "    if x[0] > ratio_value :\n",
        "      target_type.append(x[1])\n",
        "elif get_by == \"rank\" :\n",
        "  target_type = [x[1] for x in type_freq[:k]]\n",
        "\n",
        "# create type feature\n",
        "for t in target_type :\n",
        "  df[t] = df['type'].map(lambda x: 1 if t in get_types(x) else 0)\n",
        "\n",
        "# df.drop(columns=['type'], inplace=True)\n",
        "print(target_type)\n",
        "selected_feature_value['types'] = target_type\n",
        "df.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Oczm4ubfrxyW"
      },
      "source": [
        "### District\n",
        "- Filter with the frequency of each category."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4zXLDRt9V0yW",
        "outputId": "5212e721-acd6-49b9-e877-7025c80d2755"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-bfdb3fd4-4197-4477-a8ac-92af37d59b5d\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>ticket_id</th>\n",
              "      <th>type</th>\n",
              "      <th>district</th>\n",
              "      <th>timestamp</th>\n",
              "      <th>process_hour</th>\n",
              "      <th>time_of_day</th>\n",
              "      <th>ถนน</th>\n",
              "      <th>ทางเท้า</th>\n",
              "      <th>ความสะอาด</th>\n",
              "      <th>น้ำท่วม</th>\n",
              "      <th>ความปลอดภัย</th>\n",
              "      <th>กีดขวาง</th>\n",
              "      <th>แสงสว่าง</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2021-CGPMUN</td>\n",
              "      <td>{น้ำท่วม,ร้องเรียน}</td>\n",
              "      <td>ประเวศ</td>\n",
              "      <td>2021-09-19 14:56:08.924992</td>\n",
              "      <td>6593</td>\n",
              "      <td>14</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2021-7XATFA</td>\n",
              "      <td>{สะพาน}</td>\n",
              "      <td>สาทร</td>\n",
              "      <td>2021-09-26 05:03:52.594898</td>\n",
              "      <td>6068</td>\n",
              "      <td>5</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2021-DVEWYM</td>\n",
              "      <td>{น้ำท่วม,ถนน}</td>\n",
              "      <td>ลาดพร้าว</td>\n",
              "      <td>2021-12-09 12:29:08.408763</td>\n",
              "      <td>5898</td>\n",
              "      <td>12</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2021-4D9Y98</td>\n",
              "      <td>{}</td>\n",
              "      <td>ลาดพร้าว</td>\n",
              "      <td>2021-12-13 05:53:36.861064</td>\n",
              "      <td>10950</td>\n",
              "      <td>5</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2021-7U9RED</td>\n",
              "      <td>{}</td>\n",
              "      <td>ดุสิต</td>\n",
              "      <td>2021-12-17 08:46:02.610983</td>\n",
              "      <td>12381</td>\n",
              "      <td>8</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-bfdb3fd4-4197-4477-a8ac-92af37d59b5d')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-bfdb3fd4-4197-4477-a8ac-92af37d59b5d button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-bfdb3fd4-4197-4477-a8ac-92af37d59b5d');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "     ticket_id                 type  district                  timestamp  \\\n",
              "0  2021-CGPMUN  {น้ำท่วม,ร้องเรียน}    ประเวศ 2021-09-19 14:56:08.924992   \n",
              "1  2021-7XATFA              {สะพาน}      สาทร 2021-09-26 05:03:52.594898   \n",
              "2  2021-DVEWYM        {น้ำท่วม,ถนน}  ลาดพร้าว 2021-12-09 12:29:08.408763   \n",
              "3  2021-4D9Y98                   {}  ลาดพร้าว 2021-12-13 05:53:36.861064   \n",
              "4  2021-7U9RED                   {}     ดุสิต 2021-12-17 08:46:02.610983   \n",
              "\n",
              "   process_hour  time_of_day  ถนน  ทางเท้า  ความสะอาด  น้ำท่วม  ความปลอดภัย  \\\n",
              "0          6593           14    0        0          0        1            0   \n",
              "1          6068            5    0        0          0        0            0   \n",
              "2          5898           12    1        0          0        1            0   \n",
              "3         10950            5    0        0          0        0            0   \n",
              "4         12381            8    0        0          0        0            0   \n",
              "\n",
              "   กีดขวาง  แสงสว่าง  \n",
              "0        0         0  \n",
              "1        0         0  \n",
              "2        0         0  \n",
              "3        0         0  \n",
              "4        0         0  "
            ]
          },
          "execution_count": 24,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "df.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "cellView": "form",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "LnhCWrHpWP6W",
        "outputId": "8997e54f-e33e-4c0d-e3a1-a2414d6b5950"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "['จตุจักร', 'ประเวศ', 'บางแค', 'บางเขน', 'ลาดกระบัง', 'บางกะปิ', 'วัฒนา', 'คลองเตย', 'สวนหลวง', 'บางขุนเทียน', 'บึงกุ่ม', 'บางซื่อ', 'ราชเทวี', 'ปทุมวัน', 'ดินแดง', 'บางกอกน้อย', 'ลาดพร้าว', 'สาทร', 'บางรัก', 'วังทองหลาง', 'พญาไท', 'ธนบุรี', 'สายไหม', 'บางนา', 'คลองสามวา', 'other']\n"
          ]
        },
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-7354bccd-8bc2-4c55-b900-5ec894f6300e\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>ticket_id</th>\n",
              "      <th>type</th>\n",
              "      <th>district</th>\n",
              "      <th>timestamp</th>\n",
              "      <th>process_hour</th>\n",
              "      <th>time_of_day</th>\n",
              "      <th>ถนน</th>\n",
              "      <th>ทางเท้า</th>\n",
              "      <th>ความสะอาด</th>\n",
              "      <th>น้ำท่วม</th>\n",
              "      <th>...</th>\n",
              "      <th>ลาดพร้าว</th>\n",
              "      <th>สาทร</th>\n",
              "      <th>บางรัก</th>\n",
              "      <th>วังทองหลาง</th>\n",
              "      <th>พญาไท</th>\n",
              "      <th>ธนบุรี</th>\n",
              "      <th>สายไหม</th>\n",
              "      <th>บางนา</th>\n",
              "      <th>คลองสามวา</th>\n",
              "      <th>other</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2021-CGPMUN</td>\n",
              "      <td>{น้ำท่วม,ร้องเรียน}</td>\n",
              "      <td>ประเวศ</td>\n",
              "      <td>2021-09-19 14:56:08.924992</td>\n",
              "      <td>6593</td>\n",
              "      <td>14</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>...</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2021-7XATFA</td>\n",
              "      <td>{สะพาน}</td>\n",
              "      <td>สาทร</td>\n",
              "      <td>2021-09-26 05:03:52.594898</td>\n",
              "      <td>6068</td>\n",
              "      <td>5</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>...</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2021-DVEWYM</td>\n",
              "      <td>{น้ำท่วม,ถนน}</td>\n",
              "      <td>ลาดพร้าว</td>\n",
              "      <td>2021-12-09 12:29:08.408763</td>\n",
              "      <td>5898</td>\n",
              "      <td>12</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>...</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2021-4D9Y98</td>\n",
              "      <td>{}</td>\n",
              "      <td>ลาดพร้าว</td>\n",
              "      <td>2021-12-13 05:53:36.861064</td>\n",
              "      <td>10950</td>\n",
              "      <td>5</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>...</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2021-7U9RED</td>\n",
              "      <td>{}</td>\n",
              "      <td>other</td>\n",
              "      <td>2021-12-17 08:46:02.610983</td>\n",
              "      <td>12381</td>\n",
              "      <td>8</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>...</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5 rows × 39 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-7354bccd-8bc2-4c55-b900-5ec894f6300e')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-7354bccd-8bc2-4c55-b900-5ec894f6300e button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-7354bccd-8bc2-4c55-b900-5ec894f6300e');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "     ticket_id                 type  district                  timestamp  \\\n",
              "0  2021-CGPMUN  {น้ำท่วม,ร้องเรียน}    ประเวศ 2021-09-19 14:56:08.924992   \n",
              "1  2021-7XATFA              {สะพาน}      สาทร 2021-09-26 05:03:52.594898   \n",
              "2  2021-DVEWYM        {น้ำท่วม,ถนน}  ลาดพร้าว 2021-12-09 12:29:08.408763   \n",
              "3  2021-4D9Y98                   {}  ลาดพร้าว 2021-12-13 05:53:36.861064   \n",
              "4  2021-7U9RED                   {}     other 2021-12-17 08:46:02.610983   \n",
              "\n",
              "   process_hour  time_of_day  ถนน  ทางเท้า  ความสะอาด  น้ำท่วม  ...  ลาดพร้าว  \\\n",
              "0          6593           14    0        0          0        1  ...         0   \n",
              "1          6068            5    0        0          0        0  ...         0   \n",
              "2          5898           12    1        0          0        1  ...         1   \n",
              "3         10950            5    0        0          0        0  ...         1   \n",
              "4         12381            8    0        0          0        0  ...         0   \n",
              "\n",
              "   สาทร  บางรัก  วังทองหลาง  พญาไท  ธนบุรี  สายไหม  บางนา  คลองสามวา  other  \n",
              "0     0       0           0      0       0       0      0          0      0  \n",
              "1     1       0           0      0       0       0      0          0      0  \n",
              "2     0       0           0      0       0       0      0          0      0  \n",
              "3     0       0           0      0       0       0      0          0      0  \n",
              "4     0       0           0      0       0       0      0          0      1  \n",
              "\n",
              "[5 rows x 39 columns]"
            ]
          },
          "execution_count": 25,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "#@title Fillter high cardinality\n",
        "\n",
        "get_by = \"rank\" #@param [\"ratio\", \"rank\"]\n",
        "k = 25 #@param \n",
        "ratio_value = 0.5 #@param \n",
        "\n",
        "district_freq = df.groupby(\"district\")[\"district\"].count()/df.shape[0]\n",
        "district_freq = sorted([(x[1],x[0]) for x in district_freq.items()], reverse=True)\n",
        "district_freq\n",
        "\n",
        "if get_by == \"ratio\" :\n",
        "  target_provinces = []\n",
        "  for x in district_freq :\n",
        "    if x[0] > ratio_value :\n",
        "      target_provinces.append(x[1])\n",
        "elif get_by == \"rank\" :\n",
        "  target_provinces = [x[1] for x in district_freq[:k]]\n",
        "\n",
        "if \"other\" not in target_provinces :\n",
        "  target_provinces += [\"other\"]\n",
        "\n",
        "print(target_provinces)\n",
        "selected_feature_value['provinces'] = target_provinces\n",
        "# map with target_provices\n",
        "df['district'] = df['district'].map(lambda x: x if x in target_provinces else \"other\")\n",
        "\n",
        "\n",
        "# create\n",
        "for province in target_provinces :\n",
        "  df[province] = df['district'].apply(lambda x: 1 if x == province else 0)\n",
        "\n",
        "df.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KF0GoreeQsQH"
      },
      "source": [
        "## Create holiday holiday features"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "AQcyfeNcQzPc"
      },
      "outputs": [],
      "source": [
        "def is_holiday_offset(data, offset=0) :\n",
        "  day = data + timedelta(days=offset)\n",
        "  day_text = day.strftime(\"%d/%m/%Y\")\n",
        "  if (day_text in holidays) or (day.day_name() in ['Saturday', 'Sunday']) :\n",
        "    return 1\n",
        "  else: return 0\n",
        "\n",
        "look_ahead = 5 # days\n",
        "for l in range(look_ahead) :\n",
        "  df['is_holiday_'+str(l)] = df['timestamp'].map(lambda x: is_holiday_offset(x, l))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wFH5vOCzdBKW"
      },
      "source": [
        "## Create weather information"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "vP-U_pUyoI9U",
        "outputId": "57724b0c-3ac6-4f9a-9a37-609d1715b574"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-a8e3022f-ca60-4582-879f-183e33bf2de4\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Unnamed: 0</th>\n",
              "      <th>temp (F)</th>\n",
              "      <th>feel_like (F)</th>\n",
              "      <th>humidity (%)</th>\n",
              "      <th>datetime</th>\n",
              "      <th>condition_Fair</th>\n",
              "      <th>condition_Light Rain</th>\n",
              "      <th>condition_Light Rain / Windy</th>\n",
              "      <th>condition_Light Rain with Thunder</th>\n",
              "      <th>condition_Mostly Cloudy</th>\n",
              "      <th>condition_Mostly Cloudy / Windy</th>\n",
              "      <th>condition_Partly Cloudy</th>\n",
              "      <th>condition_Rain</th>\n",
              "      <th>condition_T-Storm / Windy</th>\n",
              "      <th>condition_Thunder</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>0</td>\n",
              "      <td>88</td>\n",
              "      <td>100</td>\n",
              "      <td>70</td>\n",
              "      <td>2023-05-17</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-a8e3022f-ca60-4582-879f-183e33bf2de4')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-a8e3022f-ca60-4582-879f-183e33bf2de4 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-a8e3022f-ca60-4582-879f-183e33bf2de4');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "   Unnamed: 0  temp (F)  feel_like (F)  humidity (%)   datetime  \\\n",
              "0           0        88            100            70 2023-05-17   \n",
              "\n",
              "   condition_Fair  condition_Light Rain  condition_Light Rain / Windy  \\\n",
              "0               1                     0                             0   \n",
              "\n",
              "   condition_Light Rain with Thunder  condition_Mostly Cloudy  \\\n",
              "0                                  0                        0   \n",
              "\n",
              "   condition_Mostly Cloudy / Windy  condition_Partly Cloudy  condition_Rain  \\\n",
              "0                                0                        0               0   \n",
              "\n",
              "   condition_T-Storm / Windy  condition_Thunder  \n",
              "0                          0                  0  "
            ]
          },
          "execution_count": 27,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "weather_conditions = [\"condition_\" + x for x in df_weather['condition'].unique()]\n",
        "df_weather_d = df_weather.drop(columns=['date', \"time\", \"wind\", \"wind_speed (mph)\", \"wind_gust (mph)\", \"pressure (in)\", \"precip (in)\", \"dew_point (F)\"])\n",
        "df_weather_d = pd.get_dummies(df_weather_d, columns=[\"condition\"])\n",
        "df_weather_d.head(1)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "8GfR_8rEnA0s"
      },
      "outputs": [],
      "source": [
        "# mark only degree of 3 ex [3,9,15,21]\n",
        "mark_number = [3,9,15,21]\n",
        "mark_data = df_weather_d['datetime'].map(lambda x: ((x.hour in mark_number) and (x.minute == 0)))\n",
        "df_weather_mark = df_weather_d[mark_data]\n",
        "df_weather_mark.sort_values(\"datetime\", inplace=True) \n",
        "df_weather_mark.reset_index(drop=True, inplace=True)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ytrn92DQfVJ_",
        "outputId": "1b028792-6702-47ea-e1f4-8e43b1044a16"
      },
      "outputs": [
        {
          "name": "stderr",
          "output_type": "stream",
          "text": [
            "100%|██████████| 202107/202107 [01:07<00:00, 2974.05it/s]\n"
          ]
        },
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "missing weather: 0.0\n"
          ]
        },
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-07e6a575-894a-46d1-8b9c-298e5fbdd1da\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>ticket_id</th>\n",
              "      <th>type</th>\n",
              "      <th>district</th>\n",
              "      <th>timestamp</th>\n",
              "      <th>process_hour</th>\n",
              "      <th>time_of_day</th>\n",
              "      <th>ถนน</th>\n",
              "      <th>ทางเท้า</th>\n",
              "      <th>ความสะอาด</th>\n",
              "      <th>น้ำท่วม</th>\n",
              "      <th>...</th>\n",
              "      <th>7_temp (F)</th>\n",
              "      <th>7_humidity (%)</th>\n",
              "      <th>8_temp (F)</th>\n",
              "      <th>8_humidity (%)</th>\n",
              "      <th>9_temp (F)</th>\n",
              "      <th>9_humidity (%)</th>\n",
              "      <th>10_temp (F)</th>\n",
              "      <th>10_humidity (%)</th>\n",
              "      <th>11_temp (F)</th>\n",
              "      <th>11_humidity (%)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2021-CGPMUN</td>\n",
              "      <td>{น้ำท่วม,ร้องเรียน}</td>\n",
              "      <td>ประเวศ</td>\n",
              "      <td>2021-09-19 14:56:08.924992</td>\n",
              "      <td>6593</td>\n",
              "      <td>14</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>...</td>\n",
              "      <td>84</td>\n",
              "      <td>79</td>\n",
              "      <td>88</td>\n",
              "      <td>75</td>\n",
              "      <td>97</td>\n",
              "      <td>50</td>\n",
              "      <td>86</td>\n",
              "      <td>74</td>\n",
              "      <td>86</td>\n",
              "      <td>74</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2021-7XATFA</td>\n",
              "      <td>{สะพาน}</td>\n",
              "      <td>สาทร</td>\n",
              "      <td>2021-09-26 05:03:52.594898</td>\n",
              "      <td>6068</td>\n",
              "      <td>5</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>...</td>\n",
              "      <td>84</td>\n",
              "      <td>79</td>\n",
              "      <td>88</td>\n",
              "      <td>75</td>\n",
              "      <td>97</td>\n",
              "      <td>50</td>\n",
              "      <td>86</td>\n",
              "      <td>74</td>\n",
              "      <td>86</td>\n",
              "      <td>74</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2021-DVEWYM</td>\n",
              "      <td>{น้ำท่วม,ถนน}</td>\n",
              "      <td>ลาดพร้าว</td>\n",
              "      <td>2021-12-09 12:29:08.408763</td>\n",
              "      <td>5898</td>\n",
              "      <td>12</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>...</td>\n",
              "      <td>84</td>\n",
              "      <td>79</td>\n",
              "      <td>88</td>\n",
              "      <td>75</td>\n",
              "      <td>97</td>\n",
              "      <td>50</td>\n",
              "      <td>86</td>\n",
              "      <td>74</td>\n",
              "      <td>86</td>\n",
              "      <td>74</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2021-4D9Y98</td>\n",
              "      <td>{}</td>\n",
              "      <td>ลาดพร้าว</td>\n",
              "      <td>2021-12-13 05:53:36.861064</td>\n",
              "      <td>10950</td>\n",
              "      <td>5</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>...</td>\n",
              "      <td>84</td>\n",
              "      <td>79</td>\n",
              "      <td>88</td>\n",
              "      <td>75</td>\n",
              "      <td>97</td>\n",
              "      <td>50</td>\n",
              "      <td>86</td>\n",
              "      <td>74</td>\n",
              "      <td>86</td>\n",
              "      <td>74</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2021-7U9RED</td>\n",
              "      <td>{}</td>\n",
              "      <td>other</td>\n",
              "      <td>2021-12-17 08:46:02.610983</td>\n",
              "      <td>12381</td>\n",
              "      <td>8</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>...</td>\n",
              "      <td>84</td>\n",
              "      <td>79</td>\n",
              "      <td>88</td>\n",
              "      <td>75</td>\n",
              "      <td>97</td>\n",
              "      <td>50</td>\n",
              "      <td>86</td>\n",
              "      <td>74</td>\n",
              "      <td>86</td>\n",
              "      <td>74</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5 rows × 68 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-07e6a575-894a-46d1-8b9c-298e5fbdd1da')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-07e6a575-894a-46d1-8b9c-298e5fbdd1da button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-07e6a575-894a-46d1-8b9c-298e5fbdd1da');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "     ticket_id                 type  district                  timestamp  \\\n",
              "0  2021-CGPMUN  {น้ำท่วม,ร้องเรียน}    ประเวศ 2021-09-19 14:56:08.924992   \n",
              "1  2021-7XATFA              {สะพาน}      สาทร 2021-09-26 05:03:52.594898   \n",
              "2  2021-DVEWYM        {น้ำท่วม,ถนน}  ลาดพร้าว 2021-12-09 12:29:08.408763   \n",
              "3  2021-4D9Y98                   {}  ลาดพร้าว 2021-12-13 05:53:36.861064   \n",
              "4  2021-7U9RED                   {}     other 2021-12-17 08:46:02.610983   \n",
              "\n",
              "   process_hour  time_of_day  ถนน  ทางเท้า  ความสะอาด  น้ำท่วม  ...  \\\n",
              "0          6593           14    0        0          0        1  ...   \n",
              "1          6068            5    0        0          0        0  ...   \n",
              "2          5898           12    1        0          0        1  ...   \n",
              "3         10950            5    0        0          0        0  ...   \n",
              "4         12381            8    0        0          0        0  ...   \n",
              "\n",
              "   7_temp (F)  7_humidity (%)  8_temp (F)  8_humidity (%)  9_temp (F)  \\\n",
              "0          84              79          88              75          97   \n",
              "1          84              79          88              75          97   \n",
              "2          84              79          88              75          97   \n",
              "3          84              79          88              75          97   \n",
              "4          84              79          88              75          97   \n",
              "\n",
              "   9_humidity (%)  10_temp (F)  10_humidity (%)  11_temp (F)  11_humidity (%)  \n",
              "0              50           86               74           86               74  \n",
              "1              50           86               74           86               74  \n",
              "2              50           86               74           86               74  \n",
              "3              50           86               74           86               74  \n",
              "4              50           86               74           86               74  \n",
              "\n",
              "[5 rows x 68 columns]"
            ]
          },
          "execution_count": 29,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "import numpy as np\n",
        "\n",
        "def nearest_ind(items, pivot):\n",
        "  # assume items are sorted\n",
        "  l, r = 0, len(items)-1\n",
        "  while l < r :\n",
        "    mid = (l+r)//2\n",
        "    d = (items[mid] - pivot).total_seconds()\n",
        "    if d > 0 :\n",
        "      r = mid \n",
        "    else :\n",
        "      l = mid+1\n",
        "  # print(items[l], pivot)\n",
        "  return l\n",
        "\n",
        "# parameters\n",
        "w_lookhead_k = 3*4\n",
        "weather_features = [\"temp (F)\", \"humidity (%)\"] \n",
        "\n",
        "dict_features = {}\n",
        "for i in range(w_lookhead_k) :\n",
        "  for w in weather_features :\n",
        "    dict_features[str(i)+ \"_\" + w] = []\n",
        "\n",
        "missing = 0\n",
        "all = 0\n",
        "\n",
        "for target_timestamp in tqdm(df['timestamp']) :\n",
        "  ind_target_time = nearest_ind(df_weather_mark['datetime'], target_timestamp) + 1\n",
        "  # ind_target_time = 0\n",
        "  for i in range(w_lookhead_k) :\n",
        "    for w in weather_features :\n",
        "      all += 1\n",
        "      if ind_target_time+i >= len(df_weather_mark) :\n",
        "        missing+=1\n",
        "        dict_features[str(i)+ \"_\" + w].append(dict_features[str(i-1)+ \"_\" + w][-1])\n",
        "      else :\n",
        "        dict_features[str(i)+ \"_\" + w].append(df_weather_mark[w][ind_target_time+i])\n",
        "for k,v in dict_features.items() :\n",
        "  df[k] = v\n",
        "print(\"missing weather:\",missing/all)\n",
        "df.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ct4CRt0HZYKl"
      },
      "outputs": [],
      "source": [
        "df_after_weather_keep = df.copy"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "62mV7rGmXl1r"
      },
      "source": [
        "## [Save] selected_feature_value"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "VdByRZngYLmh",
        "outputId": "078e48d4-4dee-4391-df82-5be460ffc465"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "{\n",
            "    \"types\": [\n",
            "        \"ถนน\",\n",
            "        \"ทางเท้า\",\n",
            "        \"ความสะอาด\",\n",
            "        \"น้ำท่วม\",\n",
            "        \"ความปลอดภัย\",\n",
            "        \"กีดขวาง\",\n",
            "        \"แสงสว่าง\"\n",
            "    ],\n",
            "    \"provinces\": [\n",
            "        \"จตุจักร\",\n",
            "        \"ประเวศ\",\n",
            "        \"บางแค\",\n",
            "        \"บางเขน\",\n",
            "        \"ลาดกระบัง\",\n",
            "        \"บางกะปิ\",\n",
            "        \"วัฒนา\",\n",
            "        \"คลองเตย\",\n",
            "        \"สวนหลวง\",\n",
            "        \"บางขุนเทียน\",\n",
            "        \"บึงกุ่ม\",\n",
            "        \"บางซื่อ\",\n",
            "        \"ราชเทวี\",\n",
            "        \"ปทุมวัน\",\n",
            "        \"ดินแดง\",\n",
            "        \"บางกอกน้อย\",\n",
            "        \"ลาดพร้าว\",\n",
            "        \"สาทร\",\n",
            "        \"บางรัก\",\n",
            "        \"วังทองหลาง\",\n",
            "        \"พญาไท\",\n",
            "        \"ธนบุรี\",\n",
            "        \"สายไหม\",\n",
            "        \"บางนา\",\n",
            "        \"คลองสามวา\",\n",
            "        \"other\"\n",
            "    ]\n",
            "}\n"
          ]
        }
      ],
      "source": [
        "import json \n",
        "with open(os.path.join(CONFIG_DIR,\"selected_feature_value.json\"), \"w\") as outfile:\n",
        "  json_object = json.dumps(selected_feature_value, indent = 4, ensure_ascii=False) \n",
        "  print(json_object)\n",
        "  outfile.write(json_object)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Wjl7q3invQ_4"
      },
      "source": [
        "# Defind period of time\n",
        "\n",
        "```\n",
        "df_test = [end_date_train - k_test,end_date_train]\n",
        "df_train = [end_date_train - k_test - k_train, end_date_train - k_test ]\n",
        "```"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "cellView": "form",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "XQWtj0B9VraF",
        "outputId": "e475946d-159a-4d4e-bfe6-176f55086e20"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "train on  2023-04-12 15:46:37.097891  to  2023-05-07 15:46:37.097891\n",
            "test on  2023-05-07 15:46:37.097891  to  2023-05-14 15:46:37.097891\n",
            "test set: 1358\n",
            " train set: 7508\n",
            "\n",
            "test set: 0.15316941123392736\n",
            " train set: 0.8468305887660726\n"
          ]
        }
      ],
      "source": [
        "#@title define start_train, end_train, end_test time\n",
        "k_test = 7 #@param {type:\"integer\"}\n",
        "k_train = 25 #@param {type:\"integer\"}\n",
        "\n",
        "from datetime import  datetime, timedelta\n",
        "\n",
        "# current_date = datetime.today()\n",
        "current_date = end_date_train\n",
        "start_test_date = end_date_train - timedelta(days=k_test)\n",
        "start_train_date = end_date_train- timedelta(days=k_test) - timedelta(days=k_train)\n",
        "\n",
        "print(\"train on \", start_train_date, \" to \",start_test_date)\n",
        "print(\"test on \", start_test_date, \" to \",current_date)\n",
        "\n",
        "# filter data\n",
        "df_test = df[df['timestamp'] > start_test_date]\n",
        "df_train = df[( df['timestamp'] > start_train_date ) & ( df['timestamp'] < start_test_date )]\n",
        "total_df = df_test.shape[0] + df_train.shape[0]\n",
        "print(f\"test set: {df_test.shape[0]}\\n train set: {df_train.shape[0]}\\n\")\n",
        "print(f\"test set: {df_test.shape[0]/total_df}\\n train set: {df_train.shape[0]/total_df}\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MaQQy__zgN6n"
      },
      "source": [
        "# Prepare Features"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "KP3NX8q7chDV",
        "outputId": "37aa5b92-b7dd-4f56-9f68-d4c2d14caefc"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-92164017-5051-4208-b0ef-20d5053998a6\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>ticket_id</th>\n",
              "      <th>type</th>\n",
              "      <th>district</th>\n",
              "      <th>timestamp</th>\n",
              "      <th>process_hour</th>\n",
              "      <th>time_of_day</th>\n",
              "      <th>ถนน</th>\n",
              "      <th>ทางเท้า</th>\n",
              "      <th>ความสะอาด</th>\n",
              "      <th>น้ำท่วม</th>\n",
              "      <th>...</th>\n",
              "      <th>7_temp (F)</th>\n",
              "      <th>7_humidity (%)</th>\n",
              "      <th>8_temp (F)</th>\n",
              "      <th>8_humidity (%)</th>\n",
              "      <th>9_temp (F)</th>\n",
              "      <th>9_humidity (%)</th>\n",
              "      <th>10_temp (F)</th>\n",
              "      <th>10_humidity (%)</th>\n",
              "      <th>11_temp (F)</th>\n",
              "      <th>11_humidity (%)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>193241</th>\n",
              "      <td>2023-3HGUVD</td>\n",
              "      <td>{ต้นไม้}</td>\n",
              "      <td>สวนหลวง</td>\n",
              "      <td>2023-04-12 15:47:59.694765</td>\n",
              "      <td>35</td>\n",
              "      <td>15</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>...</td>\n",
              "      <td>90</td>\n",
              "      <td>52</td>\n",
              "      <td>84</td>\n",
              "      <td>84</td>\n",
              "      <td>90</td>\n",
              "      <td>70</td>\n",
              "      <td>99</td>\n",
              "      <td>50</td>\n",
              "      <td>90</td>\n",
              "      <td>62</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>193242</th>\n",
              "      <td>2023-N7V32D</td>\n",
              "      <td>{ทางเท้า}</td>\n",
              "      <td>other</td>\n",
              "      <td>2023-04-12 16:02:16.907755</td>\n",
              "      <td>254</td>\n",
              "      <td>16</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>...</td>\n",
              "      <td>90</td>\n",
              "      <td>52</td>\n",
              "      <td>84</td>\n",
              "      <td>84</td>\n",
              "      <td>90</td>\n",
              "      <td>70</td>\n",
              "      <td>99</td>\n",
              "      <td>50</td>\n",
              "      <td>90</td>\n",
              "      <td>62</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>193243</th>\n",
              "      <td>2023-3TGFQV</td>\n",
              "      <td>{ทางเท้า}</td>\n",
              "      <td>other</td>\n",
              "      <td>2023-04-12 16:08:31.399695</td>\n",
              "      <td>640</td>\n",
              "      <td>16</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>...</td>\n",
              "      <td>90</td>\n",
              "      <td>52</td>\n",
              "      <td>84</td>\n",
              "      <td>84</td>\n",
              "      <td>90</td>\n",
              "      <td>70</td>\n",
              "      <td>99</td>\n",
              "      <td>50</td>\n",
              "      <td>90</td>\n",
              "      <td>62</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>193244</th>\n",
              "      <td>2023-6NLQ44</td>\n",
              "      <td>{เสียงรบกวน}</td>\n",
              "      <td>other</td>\n",
              "      <td>2023-04-12 16:41:26.914410</td>\n",
              "      <td>134</td>\n",
              "      <td>16</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>...</td>\n",
              "      <td>90</td>\n",
              "      <td>52</td>\n",
              "      <td>84</td>\n",
              "      <td>84</td>\n",
              "      <td>90</td>\n",
              "      <td>70</td>\n",
              "      <td>99</td>\n",
              "      <td>50</td>\n",
              "      <td>90</td>\n",
              "      <td>62</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>193245</th>\n",
              "      <td>2023-D9XWGN</td>\n",
              "      <td>{เสียงรบกวน}</td>\n",
              "      <td>other</td>\n",
              "      <td>2023-04-12 16:45:09.933172</td>\n",
              "      <td>134</td>\n",
              "      <td>16</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>...</td>\n",
              "      <td>90</td>\n",
              "      <td>52</td>\n",
              "      <td>84</td>\n",
              "      <td>84</td>\n",
              "      <td>90</td>\n",
              "      <td>70</td>\n",
              "      <td>99</td>\n",
              "      <td>50</td>\n",
              "      <td>90</td>\n",
              "      <td>62</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5 rows × 68 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-92164017-5051-4208-b0ef-20d5053998a6')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-92164017-5051-4208-b0ef-20d5053998a6 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-92164017-5051-4208-b0ef-20d5053998a6');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "          ticket_id          type district                  timestamp  \\\n",
              "193241  2023-3HGUVD      {ต้นไม้}  สวนหลวง 2023-04-12 15:47:59.694765   \n",
              "193242  2023-N7V32D     {ทางเท้า}    other 2023-04-12 16:02:16.907755   \n",
              "193243  2023-3TGFQV     {ทางเท้า}    other 2023-04-12 16:08:31.399695   \n",
              "193244  2023-6NLQ44  {เสียงรบกวน}    other 2023-04-12 16:41:26.914410   \n",
              "193245  2023-D9XWGN  {เสียงรบกวน}    other 2023-04-12 16:45:09.933172   \n",
              "\n",
              "        process_hour  time_of_day  ถนน  ทางเท้า  ความสะอาด  น้ำท่วม  ...  \\\n",
              "193241            35           15    0        0          0        0  ...   \n",
              "193242           254           16    0        1          0        0  ...   \n",
              "193243           640           16    0        1          0        0  ...   \n",
              "193244           134           16    0        0          0        0  ...   \n",
              "193245           134           16    0        0          0        0  ...   \n",
              "\n",
              "        7_temp (F)  7_humidity (%)  8_temp (F)  8_humidity (%)  9_temp (F)  \\\n",
              "193241          90              52          84              84          90   \n",
              "193242          90              52          84              84          90   \n",
              "193243          90              52          84              84          90   \n",
              "193244          90              52          84              84          90   \n",
              "193245          90              52          84              84          90   \n",
              "\n",
              "        9_humidity (%)  10_temp (F)  10_humidity (%)  11_temp (F)  \\\n",
              "193241              70           99               50           90   \n",
              "193242              70           99               50           90   \n",
              "193243              70           99               50           90   \n",
              "193244              70           99               50           90   \n",
              "193245              70           99               50           90   \n",
              "\n",
              "        11_humidity (%)  \n",
              "193241               62  \n",
              "193242               62  \n",
              "193243               62  \n",
              "193244               62  \n",
              "193245               62  \n",
              "\n",
              "[5 rows x 68 columns]"
            ]
          },
          "execution_count": 33,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "df_train.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "x4XS3XWtgRS0",
        "outputId": "9cbaa5ab-a64b-4504-f27c-df6e9c822c7f"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "((7508, 63), (1358, 63))"
            ]
          },
          "execution_count": 34,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "\n",
        "X_train = df_train.iloc[:,len(target_columns):]\n",
        "y_train = df_train[\"process_hour\"]\n",
        "\n",
        "X_test = df_test.iloc[:,len(target_columns):]\n",
        "y_test = df_test[\"process_hour\"]\n",
        "X_train.shape, X_test.shape"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "kOoHJaFzgxAn"
      },
      "outputs": [],
      "source": [
        "#@title [Save] standardScaler\n",
        "# normalize feature\n",
        "from sklearn.preprocessing import StandardScaler\n",
        "import pickle\n",
        "import json \n",
        "\n",
        "scaler = StandardScaler()\n",
        "X_train[:] = scaler.fit_transform(X_train)\n",
        "X_test[:] = scaler.transform(X_test)\n",
        "\n",
        "check_order=  {\"order\": list(X_train.columns)}\n",
        "\n",
        "with open(os.path.join(CONFIG_DIR,\"order_check.json\"), \"w\") as outfile:\n",
        "  json_object = json.dumps(check_order, indent = 4, ensure_ascii=False) \n",
        "  outfile.write(json_object)\n",
        "\n",
        "\n",
        "# dump standard \n",
        "file_name = os.path.join(CONFIG_DIR, \"scalar.pkl\")\n",
        "with open(file_name,'wb') as f:\n",
        "    pickle.dump(scaler, f)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NNbO_yya4JEG"
      },
      "source": [
        "# Feature selection"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AQj-Qq7cazPG"
      },
      "source": [
        "## Mutual_info_regression"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "background_save": true
        },
        "id": "H70ucY7v4K3O",
        "outputId": "bc26d672-cc6a-48a1-cc1b-e255ae2dbb1e"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "time_of_day 0.2639732257331706\n",
            "1_humidity (%) 0.13981410229023972\n",
            "3_humidity (%) 0.1396161631116093\n",
            "3_temp (F) 0.13646378786510338\n",
            "11_humidity (%) 0.13200994330881888\n",
            "7_humidity (%) 0.128650125862958\n",
            "0_temp (F) 0.12571329004639797\n",
            "5_humidity (%) 0.12226991716247682\n",
            "4_humidity (%) 0.1182127478342303\n",
            "1_temp (F) 0.117857108473578\n",
            "0_humidity (%) 0.11764556596513365\n",
            "11_temp (F) 0.11640364449355611\n",
            "9_humidity (%) 0.11512460629042076\n",
            "7_temp (F) 0.10529131169130701\n",
            "8_humidity (%) 0.10311683941751237\n",
            "5_temp (F) 0.10307379733907895\n",
            "6_temp (F) 0.09903207609510956\n",
            "6_humidity (%) 0.09817869126494316\n",
            "9_temp (F) 0.09538349678691938\n",
            "2_humidity (%) 0.09215308320961912\n",
            "4_temp (F) 0.09144458396498756\n",
            "8_temp (F) 0.0903965723398148\n",
            "2_temp (F) 0.07593246139186705\n",
            "10_temp (F) 0.07303199012119244\n",
            "10_humidity (%) 0.07107849839241509\n",
            "is_holiday_1 0.05586553358221469\n",
            "is_holiday_2 0.04289298202021263\n",
            "บางแค 0.041009556162168614\n",
            "is_holiday_3 0.036785583205851013\n",
            "is_holiday_0 0.036126308481559954\n",
            "is_holiday_4 0.03561362668766743\n",
            "บึงกุ่ม 0.019839501611228272\n",
            "แสงสว่าง 0.012913467574022786\n",
            "ถนน 0.010509422783345324\n",
            "other 0.010485744119051565\n",
            "ความสะอาด 0.010104801558257659\n",
            "จตุจักร 0.009991611749555096\n",
            "ปทุมวัน 0.007856404880539847\n",
            "คลองเตย 0.006980712275635348\n",
            "บางขุนเทียน 0.006941331326101752\n",
            "ความปลอดภัย 0.0064264156782931625\n",
            "น้ำท่วม 0.005779151076060307\n",
            "คลองสามวา 0.004698923562258095\n",
            "บางรัก 0.004447495426934678\n",
            "ธนบุรี 0.003953412656641397\n",
            "สายไหม 0.003287538117142663\n",
            "ลาดพร้าว 0.0032645739051866407\n",
            "กีดขวาง 0.0031366658236067213\n",
            "ดินแดง 0.003086930361428042\n",
            "บางกะปิ 0.0028149485985582423\n",
            "วังทองหลาง 0.002465807501723871\n",
            "บางซื่อ 0.002302543810451363\n",
            "ราชเทวี 0.0020890372637281107\n",
            "บางเขน 0.002065228545693243\n",
            "ประเวศ 0.0019339016570629752\n",
            "สาทร 0.0017559470882575479\n",
            "ทางเท้า 0.0014704025101788254\n",
            "บางนา 0.0009742985493710332\n",
            "ลาดกระบัง 0.0009092582406278193\n",
            "สวนหลวง 0.0006217915786330686\n",
            "วัฒนา 0.0\n",
            "พญาไท 0.0\n",
            "บางกอกน้อย 0.0\n"
          ]
        }
      ],
      "source": [
        "from sklearn.feature_selection import mutual_info_regression\n",
        "\n",
        "def make_mi_scores(X, y, discrete_features):\n",
        "  mi_scores = mutual_info_regression(X, y, discrete_features=discrete_features)\n",
        "  mi_scores = pd.Series(mi_scores, name=\"MI Scores\", index=X.columns)\n",
        "  mi_scores = mi_scores.sort_values(ascending=False)\n",
        "  return mi_scores \n",
        "\n",
        "\n",
        "X_train = X_train.astype(int)\n",
        "discrete_features = X_train.dtypes == int\n",
        "\n",
        "mi_scores = make_mi_scores(X_train, y_train, discrete_features)\n",
        "for col, v in mi_scores.items() :\n",
        "  print(col, v)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tCWpfzTDa465"
      },
      "source": [
        "## Top K mutual_info_reg"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "background_save": true
        },
        "id": "BK0sP_aya80u"
      },
      "outputs": [],
      "source": [
        "# from sklearn.feature_selection import SelectKBest, mutual_info_regression, f_regression\n",
        "# slk =  SelectKBest(mutual_info_regression, k=30)\n",
        "# X_new_train = slk.fit_transform(X_train, y_train)\n",
        "# X_new_test = slk.transform(X_test)\n",
        "# # X_new_valid = slk.transform(X_valid)\n",
        "# X_train.columns[slk.get_support()]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "h22L4OF10EC1"
      },
      "source": [
        "## Model select base"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "background_save": true
        },
        "id": "_0AebUAt0Hw5",
        "outputId": "c41a2b85-9f70-4013-b5a4-1e19eb87a2ad"
      },
      "outputs": [
        {
          "name": "stderr",
          "output_type": "stream",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/sklearn/base.py:432: UserWarning: X has feature names, but SelectFromModel was fitted without feature names\n",
            "  warnings.warn(\n",
            "/usr/local/lib/python3.10/dist-packages/sklearn/base.py:432: UserWarning: X has feature names, but SelectFromModel was fitted without feature names\n",
            "  warnings.warn(\n"
          ]
        },
        {
          "data": {
            "text/plain": [
              "((7508, 20), (7508, 63))"
            ]
          },
          "execution_count": 38,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "from sklearn.svm import LinearSVC\n",
        "from sklearn.feature_selection import SelectFromModel\n",
        "from sklearn.ensemble import (RandomForestRegressor, GradientBoostingRegressor, \n",
        "                              AdaBoostRegressor)\n",
        "\n",
        "selection_model = GradientBoostingRegressor(learning_rate=0.2, max_depth=7, min_samples_split=350,\n",
        "                          n_estimators=50, random_state=1).fit(X_train, y_train)\n",
        "model_feature = SelectFromModel(selection_model, prefit=True)\n",
        "X_new_train = model_feature.transform(X_train)\n",
        "X_new_test = model_feature.transform(X_test)\n",
        "X_new_train.shape, X_train.shape"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "background_save": true
        },
        "id": "fed2YNZR6N9i",
        "outputId": "9046572a-b28e-46a9-e30d-0c0c7ae18c7d"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "Index(['time_of_day', 'ความสะอาด', 'ความปลอดภัย', 'แสงสว่าง', 'จตุจักร',\n",
              "       'บางแค', 'ลาดกระบัง', 'บางกะปิ', 'คลองเตย', 'ปทุมวัน', 'ลาดพร้าว',\n",
              "       'บางรัก', 'บางนา', 'other', 'is_holiday_1', 'is_holiday_2',\n",
              "       '0_humidity (%)', '2_humidity (%)', '3_humidity (%)', '4_humidity (%)'],\n",
              "      dtype='object')"
            ]
          },
          "execution_count": 39,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "X_train.columns[model_feature.get_support()]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "x-sr1tZAywDe"
      },
      "source": [
        "# Model"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zvZcRxtX-oxX"
      },
      "source": [
        "## Model selection"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "kUZKX7ob8Zhp"
      },
      "outputs": [],
      "source": [
        "#We will save the model performance metrics in a DataFrame\n",
        "from sklearn.metrics import mean_squared_error, r2_score\n",
        "from sklearn.model_selection import KFold, cross_val_score\n",
        "from sklearn.linear_model import LinearRegression, Ridge, Lasso\n",
        "from sklearn.neighbors import KNeighborsRegressor\n",
        "from sklearn.tree import DecisionTreeRegressor\n",
        "from sklearn.ensemble import (RandomForestRegressor, GradientBoostingRegressor, \n",
        "                              AdaBoostRegressor)\n",
        "from xgboost import XGBRegressor\n",
        "import numpy as np\n",
        "\n",
        "Model = []\n",
        "RMSE = []\n",
        "R_sq = []\n",
        "cv = KFold(5, random_state = 1, shuffle=True)\n",
        "\n",
        "names = ['Linear Regression', 'Ridge Regression', 'Lasso Regression',\n",
        "         'K Neighbors Regressor', 'Decision Tree Regressor', \n",
        "         'Random Forest Regressor', 'Gradient Boosting Regressor',\n",
        "         'Adaboost Regressor', \"XGBRegressor\"]\n",
        "models = [LinearRegression(), Ridge(), Lasso(),\n",
        "          KNeighborsRegressor(), DecisionTreeRegressor(),\n",
        "          RandomForestRegressor(), GradientBoostingRegressor(), \n",
        "          AdaBoostRegressor(), XGBRegressor()]\n",
        "\n",
        "#Creating a Function to append the cross validation scores of the algorithms\n",
        "def input_scores(name, model, x, y):\n",
        "    Model.append(name)\n",
        "    RMSE.append(np.sqrt((-1) * cross_val_score(model, x, y, cv=cv, \n",
        "                                               scoring='neg_mean_squared_error').mean()))\n",
        "    R_sq.append(cross_val_score(model, x, y, cv=cv, scoring='r2').mean())\n",
        "\n",
        "#Running all algorithms\n",
        "for name, model in zip(names, models):\n",
        "    input_scores(name, model, X_new_train, y_train)\n",
        "\n",
        "evaluation = pd.DataFrame({'Model': Model,\n",
        "                           'RMSE': RMSE,\n",
        "                           'R Squared': R_sq})\n",
        "print(\"FOLLOWING ARE THE TRAINING SCORES: \")\n",
        "evaluation"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6PGMCnAd-w6f"
      },
      "source": [
        "## Model tunning"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "_880wt7c8bb9"
      },
      "outputs": [],
      "source": [
        "#tuning for number of trees\n",
        "from sklearn.model_selection import GridSearchCV\n",
        "from sklearn.model_selection import KFold, cross_val_score\n",
        "\n",
        "cv = KFold(5, random_state = 1, shuffle=True)\n",
        "param_grid = {'n_estimators': [50],\n",
        "              'max_depth': [7], #range(5,16,2), \n",
        "              'min_samples_split': range(100,1001,200), #range(100,1001,200), \n",
        "              'learning_rate':[0.2]}\n",
        "clf = GridSearchCV(GradientBoostingRegressor(random_state=1), \n",
        "                   param_grid = param_grid, scoring='r2', \n",
        "                   cv=cv).fit(X_train, y_train)\n",
        "\n",
        "best_model = clf.best_estimator_\n",
        "print(best_model) \n",
        "print(\"R Squared:\",clf.best_score_)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ZpVN2GAocNiL"
      },
      "outputs": [],
      "source": [
        "# Test dataset\n",
        "import numpy as np\n",
        "r2_test = cross_val_score(best_model, X_test, y_test, cv=cv, scoring='r2').mean()\n",
        "RMSE_test = np.sqrt((-1) * cross_val_score(best_model, X_test, y_test, cv=cv, scoring='neg_mean_squared_error').mean())\n",
        "print(\" == Test set ==\")\n",
        "print(\"R Seuared of test set: %.2f\" % r2_test)\n",
        "print(\"RMSE of test set: %.2f\" % RMSE_test)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MiHQRFffbs1Q"
      },
      "source": [
        "# [Save] Model"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "2yWLZhAubvMq"
      },
      "outputs": [],
      "source": [
        "import pickle\n",
        "filename = os.path.join(CONFIG_DIR,'model_satee.sav')\n",
        "pickle.dump(best_model, open(filename, 'wb'))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "axLYBoHojQQz"
      },
      "source": [
        "# [Save] Prepare the data for visualize"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ckSxqYZVm1N_"
      },
      "outputs": [],
      "source": [
        "# Get result\n",
        "X_v = df.iloc[:, 5:]\n",
        "df_visual = df.copy()\n",
        "df_visual['estimated_hour'] = best_model.predict(X_v)\n",
        "df_visual.drop(columns=['type', 'district', 'timestamp'], inplace=True)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "HUdLsqrGnLJZ"
      },
      "outputs": [],
      "source": [
        "out_df_visual = df_raw.merge(df_visual, how=\"left\", on=\"ticket_id\")\n",
        "out_df_visual.to_csv(os.path.join(RESULT_DIR, \"df_visual.csv\"))\n",
        "out_df_visual.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "V-v2AThvb6nG"
      },
      "outputs": [],
      "source": [
        "out_df_visual.columns"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "collapsed_sections": [
        "aeNZF4r7Rqj4",
        "8N_mFXzqPYPG",
        "Oczm4ubfrxyW",
        "KF0GoreeQsQH",
        "wFH5vOCzdBKW",
        "Wjl7q3invQ_4",
        "NNbO_yya4JEG"
      ],
      "provenance": [],
      "toc_visible": true,
      "include_colab_link": true
    },
    "gpuClass": "standard",
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}