predict_time.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\T-Gamer\\AppData\\Local\\Temp\\ipykernel_18532\\3303994412.py:1: DeprecationWarning: \n",
      "Pyarrow will become a required dependency of pandas in the next major release of pandas (pandas 3.0),\n",
      "(to allow more performant data types, such as the Arrow string type, and better interoperability with other libraries)\n",
      "but was not found to be installed on your system.\n",
      "If this would cause problems for you,\n",
      "please provide us feedback at https://github.com/pandas-dev/pandas/issues/54466\n",
      "        \n",
      "  import pandas as pd\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy import text\n",
    "import os\n",
    "import re\n",
    "import json\n",
    "import requests\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_files = [\n",
    "    \"final/2024-05-16/2024-05-16_10.json\",\n",
    "    \"final/2024-05-16/2024-05-16_11.json\",\n",
    "]\n",
    "\n",
    "test_files = [\n",
    "    \"final/2024-05-16/teste-2024-05-16_12.json\",\n",
    "]\n",
    "\n",
    "answer_files = [\n",
    "    \"validation/2024-05-16/resposta-2024-05-15_08.json\"\n",
    "]\n",
    "train1 = pd.read_json(train_files[0], encoding='latin-1')\n",
    "train2 = pd.read_json(train_files[1], encoding='latin-1')\n",
    "\n",
    "train = pd.concat([train1, train2])\n",
    "test = pd.read_json(test_files[0], encoding='latin-1')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "train['latitude'] = train['latitude'].str.replace(',', '.').astype(float)\n",
    "train['longitude'] = train['longitude'].str.replace(',', '.').astype(float)\n",
    "train['linha'] = train['linha'].astype(str)\n",
    "\n",
    "valid_linhas = [\n",
    "    '483', '864', '639', '3', '309', '774', '629', '371', '397', '100', '838', \n",
    "    '315', '624', '388', '918', '665', '328', '497', '878', '355', '138', '606', \n",
    "    '457', '550', '803', '917', '638', '2336', '399', '298', '867', '553', '565', \n",
    "    '422', '756', '186012003', '292', '554', '634', '232', '415', '2803', '324', \n",
    "    '852', '557', '759', '343', '779', '905', '108'\n",
    "]\n",
    "\n",
    "df_train = train[train['linha'].isin(valid_linhas)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create dataframe with last 2 points of each line and bus order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_last_two = df_train.groupby(['ordem', 'linha']).tail(2).reset_index(drop=True)\n",
    "\n",
    "counts = df_last_two.groupby(['ordem', 'linha']).size()\n",
    "to_duplicate = counts[counts == 1].index\n",
    "\n",
    "duplicated_rows = df_last_two.set_index(['ordem', 'linha']).loc[to_duplicate].reset_index()\n",
    "df_last_two = pd.concat([df_last_two, duplicated_rows]).sort_values(['ordem', 'linha'])\n",
    "\n",
    "\n",
    "df_last_two = df_last_two[['ordem','linha','latitude','longitude','datahoraservidor']]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Join data to predict with last two points dataframe\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>linha</th>\n",
       "      <th>ordem</th>\n",
       "      <th>latitude_test</th>\n",
       "      <th>longitude_test</th>\n",
       "      <th>latitude_last_two</th>\n",
       "      <th>longitude_last_two</th>\n",
       "      <th>datahoraservidor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>835090919</td>\n",
       "      <td>867</td>\n",
       "      <td>D86049</td>\n",
       "      <td>-22.98619</td>\n",
       "      <td>-43.59132</td>\n",
       "      <td>-23.03359</td>\n",
       "      <td>-43.56296</td>\n",
       "      <td>1715867969000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>835090919</td>\n",
       "      <td>867</td>\n",
       "      <td>D86049</td>\n",
       "      <td>-22.98619</td>\n",
       "      <td>-43.59132</td>\n",
       "      <td>-23.03119</td>\n",
       "      <td>-43.56398</td>\n",
       "      <td>1715868000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1621540253</td>\n",
       "      <td>108</td>\n",
       "      <td>A41351</td>\n",
       "      <td>-22.94945</td>\n",
       "      <td>-43.18871</td>\n",
       "      <td>-22.89900</td>\n",
       "      <td>-43.21208</td>\n",
       "      <td>1715867961000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1621540253</td>\n",
       "      <td>108</td>\n",
       "      <td>A41351</td>\n",
       "      <td>-22.94945</td>\n",
       "      <td>-43.18871</td>\n",
       "      <td>-22.89899</td>\n",
       "      <td>-43.21209</td>\n",
       "      <td>1715867992000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4239337617</td>\n",
       "      <td>638</td>\n",
       "      <td>B44616</td>\n",
       "      <td>-22.85432</td>\n",
       "      <td>-43.37753</td>\n",
       "      <td>-22.86795</td>\n",
       "      <td>-43.35186</td>\n",
       "      <td>1715867964000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>323623</th>\n",
       "      <td>999967629119048</td>\n",
       "      <td>371</td>\n",
       "      <td>C51576</td>\n",
       "      <td>-22.87720</td>\n",
       "      <td>-43.34015</td>\n",
       "      <td>-22.88422</td>\n",
       "      <td>-43.29759</td>\n",
       "      <td>1715867978000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>323624</th>\n",
       "      <td>999977830770220</td>\n",
       "      <td>371</td>\n",
       "      <td>C51584</td>\n",
       "      <td>-22.91067</td>\n",
       "      <td>-43.20764</td>\n",
       "      <td>-22.90287</td>\n",
       "      <td>-43.24120</td>\n",
       "      <td>1715867978000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>323625</th>\n",
       "      <td>999977830770220</td>\n",
       "      <td>371</td>\n",
       "      <td>C51584</td>\n",
       "      <td>-22.91067</td>\n",
       "      <td>-43.20764</td>\n",
       "      <td>-22.90561</td>\n",
       "      <td>-43.23996</td>\n",
       "      <td>1715868009000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>323626</th>\n",
       "      <td>999985479468595</td>\n",
       "      <td>232</td>\n",
       "      <td>B25530</td>\n",
       "      <td>-22.90870</td>\n",
       "      <td>-43.17025</td>\n",
       "      <td>-22.90441</td>\n",
       "      <td>-43.18331</td>\n",
       "      <td>1715867964000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>323627</th>\n",
       "      <td>999985479468595</td>\n",
       "      <td>232</td>\n",
       "      <td>B25530</td>\n",
       "      <td>-22.90870</td>\n",
       "      <td>-43.17025</td>\n",
       "      <td>-22.90442</td>\n",
       "      <td>-43.18330</td>\n",
       "      <td>1715867985000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>323628 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     id linha   ordem  latitude_test  longitude_test  \\\n",
       "0             835090919   867  D86049      -22.98619       -43.59132   \n",
       "1             835090919   867  D86049      -22.98619       -43.59132   \n",
       "2            1621540253   108  A41351      -22.94945       -43.18871   \n",
       "3            1621540253   108  A41351      -22.94945       -43.18871   \n",
       "4            4239337617   638  B44616      -22.85432       -43.37753   \n",
       "...                 ...   ...     ...            ...             ...   \n",
       "323623  999967629119048   371  C51576      -22.87720       -43.34015   \n",
       "323624  999977830770220   371  C51584      -22.91067       -43.20764   \n",
       "323625  999977830770220   371  C51584      -22.91067       -43.20764   \n",
       "323626  999985479468595   232  B25530      -22.90870       -43.17025   \n",
       "323627  999985479468595   232  B25530      -22.90870       -43.17025   \n",
       "\n",
       "        latitude_last_two  longitude_last_two  datahoraservidor  \n",
       "0               -23.03359           -43.56296     1715867969000  \n",
       "1               -23.03119           -43.56398     1715868000000  \n",
       "2               -22.89900           -43.21208     1715867961000  \n",
       "3               -22.89899           -43.21209     1715867992000  \n",
       "4               -22.86795           -43.35186     1715867964000  \n",
       "...                   ...                 ...               ...  \n",
       "323623          -22.88422           -43.29759     1715867978000  \n",
       "323624          -22.90287           -43.24120     1715867978000  \n",
       "323625          -22.90561           -43.23996     1715868009000  \n",
       "323626          -22.90441           -43.18331     1715867964000  \n",
       "323627          -22.90442           -43.18330     1715867985000  \n",
       "\n",
       "[323628 rows x 8 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test['linha'] = test['linha'].astype(str)\n",
    "test['latitude'] = test['latitude'].str.replace(',', '.').astype(float)\n",
    "test['longitude'] = test['longitude'].str.replace(',', '.').astype(float)\n",
    "\n",
    "df_test = test[test['linha'].isin(valid_linhas)]\n",
    "\n",
    "# Join the two dataframes\n",
    "join_df = pd.merge(df_test, df_last_two, on=['ordem','linha'], how='inner', suffixes=('_test', '_last_two'))\n",
    "join_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Model - Predict the next position of the bus based on past positions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "database_url = os.getenv(\"DATABASE_URL\")\n",
    "engine = create_engine(database_url, client_encoding='latin-1')  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def execute_query(connection, linha, lat1, lon1, lat2, lon2, last_date, prediction_lat, prediction_lon):\n",
    "    query = \"\"\"\n",
    "    WITH initial_similar_points AS (\n",
    "        SELECT time_ranking,\n",
    "               ordem,\n",
    "               linha,\n",
    "               x,\n",
    "               y,\n",
    "               datahoraservidor\n",
    "        FROM vw_buses_order\n",
    "        WHERE linha = :linha\n",
    "        AND x = width_bucket(:lon1, -43.726090, -42.951470, 1587)\n",
    "        AND y = width_bucket(:lat1, -23.170790, -22.546410, 1389)\n",
    "        AND (\n",
    "                (datahoraservidor >= TO_TIMESTAMP(:last_date) - interval '7 day' - interval '2 hour'  \n",
    "                AND datahoraservidor < TO_TIMESTAMP(:last_date) - interval '7 day' + interval '2 hour') \n",
    "                OR \n",
    "                (datahoraservidor >= TO_TIMESTAMP(:last_date) - interval '14 day' - interval '2 hour'  \n",
    "                AND datahoraservidor < TO_TIMESTAMP(:last_date) - interval '14 day' + interval '2 hour')\n",
    "                OR \n",
    "                (datahoraservidor >= TO_TIMESTAMP(:last_date) - interval '21 day' - interval '2 hour'  \n",
    "                AND datahoraservidor < TO_TIMESTAMP(:last_date) - interval '21 day' + interval '2 hour')\n",
    "            )\n",
    "        AND time_ranking > 1\n",
    "        LIMIT 10\n",
    "    ), anterior_points AS (\n",
    "        SELECT DISTINCT ON (time_ranking, ordem, linha) \n",
    "            time_ranking,\n",
    "            ordem,\n",
    "            linha,\n",
    "            x,\n",
    "            y,\n",
    "            datahoraservidor\n",
    "        FROM vw_buses_order\n",
    "        WHERE (ordem, linha, time_ranking) IN (\n",
    "            SELECT ordem, linha, time_ranking - 1\n",
    "            FROM initial_similar_points\n",
    "            )\n",
    "    ), direction_points AS (\n",
    "         SELECT \n",
    "            sp.ordem,\n",
    "            sp.datahoraservidor\n",
    "        FROM initial_similar_points sp\n",
    "        INNER JOIN anterior_points ap\n",
    "            ON sp.ordem = ap.ordem\n",
    "            AND sp.linha = ap.linha\n",
    "            AND sp.time_ranking = ap.time_ranking + 1\n",
    "        WHERE ((ap.x - sp.x) * (:lon2 - :lon1) + (ap.y - sp.y) * (:lat2 - :lat1)) >= 0\n",
    "    ), first_future_points AS (\n",
    "        SELECT \n",
    "            vo.ordem,\n",
    "            vo.datahoraservidor - dp.datahoraservidor AS time_diff\n",
    "        FROM (\n",
    "                SELECT \n",
    "                          ordem,\n",
    "                          linha,\n",
    "                          x,\n",
    "                          y,\n",
    "                          datahoraservidor\n",
    "                FROM vw_buses_order\n",
    "                WHERE linha = :linha\n",
    "                AND ordem IN (SELECT DISTINCT ordem FROM direction_points)\n",
    "             ) vo\n",
    "        INNER JOIN direction_points dp\n",
    "            ON vo.ordem = dp.ordem\n",
    "            AND vo.datahoraservidor > dp.datahoraservidor\n",
    "            AND vo.datahoraservidor < dp.datahoraservidor + interval '1 hour' + interval '20 minutes'\n",
    "        WHERE vo.x < width_bucket(:prediction_lon, -43.726090, -42.951470, 1587) + 2\n",
    "        AND vo.x > width_bucket(:prediction_lon, -43.726090, -42.951470, 1587) - 2\n",
    "        AND vo.y < width_bucket(:prediction_lat, -23.170790, -22.546410, 1389) + 2\n",
    "        AND vo.y > width_bucket(:prediction_lat, -23.170790, -22.546410, 1389) - 2\n",
    "    ), selected_future_points AS (\n",
    "        SELECT time_diff\n",
    "        FROM first_future_points\n",
    "    )\n",
    "    SELECT \n",
    "        percentile_cont(0.5) WITHIN GROUP (ORDER BY time_diff) AS median_time_diff\n",
    "    FROM selected_future_points;\n",
    "    \"\"\"\n",
    "    \n",
    "    params = {\n",
    "        'linha': linha,\n",
    "        'lat1': lat1,\n",
    "        'lon1': lon1,\n",
    "        'lat2': lat2,\n",
    "        'lon2': lon2,\n",
    "        'last_date': str(last_date),\n",
    "        'prediction_lat': prediction_lat,\n",
    "        'prediction_lon': prediction_lon\n",
    "    }\n",
    "    \n",
    "    \n",
    "    result = connection.execute(text(query), params)\n",
    "    row = result.fetchone()\n",
    "        \n",
    "    return row[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>linha</th>\n",
       "      <th>ordem</th>\n",
       "      <th>datahora</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>datahoraservidor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>949472760</td>\n",
       "      <td>422</td>\n",
       "      <td>C72081</td>\n",
       "      <td>1715856027000</td>\n",
       "      <td>-22.91142</td>\n",
       "      <td>-43.27241</td>\n",
       "      <td>1715853574000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>949472760</td>\n",
       "      <td>422</td>\n",
       "      <td>C72081</td>\n",
       "      <td>1715856027000</td>\n",
       "      <td>-22.91142</td>\n",
       "      <td>-43.27241</td>\n",
       "      <td>1715853574000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>15524859950</td>\n",
       "      <td>774</td>\n",
       "      <td>B27135</td>\n",
       "      <td>1715856165000</td>\n",
       "      <td>-22.83227</td>\n",
       "      <td>-43.32844</td>\n",
       "      <td>1715853591000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15524859950</td>\n",
       "      <td>774</td>\n",
       "      <td>B27135</td>\n",
       "      <td>1715856165000</td>\n",
       "      <td>-22.83119</td>\n",
       "      <td>-43.32838</td>\n",
       "      <td>1715853621000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17439983861</td>\n",
       "      <td>422</td>\n",
       "      <td>A72041</td>\n",
       "      <td>1715856960000</td>\n",
       "      <td>-22.92363</td>\n",
       "      <td>-43.23374</td>\n",
       "      <td>1715853574000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325017</th>\n",
       "      <td>999986210826016</td>\n",
       "      <td>803</td>\n",
       "      <td>D13242</td>\n",
       "      <td>1715855644000</td>\n",
       "      <td>-22.88547</td>\n",
       "      <td>-43.40034</td>\n",
       "      <td>1715853612000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325018</th>\n",
       "      <td>999987599402413</td>\n",
       "      <td>100</td>\n",
       "      <td>A71545</td>\n",
       "      <td>1715856871000</td>\n",
       "      <td>-22.90454</td>\n",
       "      <td>-43.19200</td>\n",
       "      <td>1715853600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325019</th>\n",
       "      <td>999987599402413</td>\n",
       "      <td>100</td>\n",
       "      <td>A71545</td>\n",
       "      <td>1715856871000</td>\n",
       "      <td>-22.90453</td>\n",
       "      <td>-43.19199</td>\n",
       "      <td>1715853600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325020</th>\n",
       "      <td>999992677630342</td>\n",
       "      <td>606</td>\n",
       "      <td>B25583</td>\n",
       "      <td>1715856945000</td>\n",
       "      <td>-22.90232</td>\n",
       "      <td>-43.29951</td>\n",
       "      <td>1715851014000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325021</th>\n",
       "      <td>999992677630342</td>\n",
       "      <td>606</td>\n",
       "      <td>B25583</td>\n",
       "      <td>1715856945000</td>\n",
       "      <td>-22.90230</td>\n",
       "      <td>-43.29952</td>\n",
       "      <td>1715852790000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>325022 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     id linha   ordem       datahora  latitude  longitude  \\\n",
       "0             949472760   422  C72081  1715856027000 -22.91142  -43.27241   \n",
       "1             949472760   422  C72081  1715856027000 -22.91142  -43.27241   \n",
       "2           15524859950   774  B27135  1715856165000 -22.83227  -43.32844   \n",
       "3           15524859950   774  B27135  1715856165000 -22.83119  -43.32838   \n",
       "4           17439983861   422  A72041  1715856960000 -22.92363  -43.23374   \n",
       "...                 ...   ...     ...            ...       ...        ...   \n",
       "325017  999986210826016   803  D13242  1715855644000 -22.88547  -43.40034   \n",
       "325018  999987599402413   100  A71545  1715856871000 -22.90454  -43.19200   \n",
       "325019  999987599402413   100  A71545  1715856871000 -22.90453  -43.19199   \n",
       "325020  999992677630342   606  B25583  1715856945000 -22.90232  -43.29951   \n",
       "325021  999992677630342   606  B25583  1715856945000 -22.90230  -43.29952   \n",
       "\n",
       "        datahoraservidor  \n",
       "0          1715853574000  \n",
       "1          1715853574000  \n",
       "2          1715853591000  \n",
       "3          1715853621000  \n",
       "4          1715853574000  \n",
       "...                  ...  \n",
       "325017     1715853612000  \n",
       "325018     1715853600000  \n",
       "325019     1715853600000  \n",
       "325020     1715851014000  \n",
       "325021     1715852790000  \n",
       "\n",
       "[325022 rows x 7 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "join_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'latitude'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "File \u001b[1;32mc:\\Users\\T-Gamer\\anaconda3\\envs\\ACELab\\Lib\\site-packages\\pandas\\core\\indexes\\base.py:3802\u001b[0m, in \u001b[0;36mIndex.get_loc\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   3801\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m-> 3802\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_engine\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mget_loc\u001b[49m\u001b[43m(\u001b[49m\u001b[43mcasted_key\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   3803\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m err:\n",
      "File \u001b[1;32mindex.pyx:153\u001b[0m, in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "File \u001b[1;32mindex.pyx:182\u001b[0m, in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "File \u001b[1;32mpandas\\\\_libs\\\\hashtable_class_helper.pxi:7081\u001b[0m, in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "File \u001b[1;32mpandas\\\\_libs\\\\hashtable_class_helper.pxi:7089\u001b[0m, in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: 'latitude'",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[8], line 10\u001b[0m\n\u001b[0;32m      5\u001b[0m         row1 \u001b[38;5;241m=\u001b[39m join_df\u001b[38;5;241m.\u001b[39miloc[i \u001b[38;5;241m+\u001b[39m \u001b[38;5;241m1\u001b[39m]\n\u001b[0;32m      6\u001b[0m         row2 \u001b[38;5;241m=\u001b[39m join_df\u001b[38;5;241m.\u001b[39miloc[i]\n\u001b[0;32m      7\u001b[0m         median_time_diff \u001b[38;5;241m=\u001b[39m execute_query(\n\u001b[0;32m      8\u001b[0m             connection,\n\u001b[0;32m      9\u001b[0m             row1[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mlinha\u001b[39m\u001b[38;5;124m'\u001b[39m], \n\u001b[1;32m---> 10\u001b[0m             \u001b[43mrow1\u001b[49m\u001b[43m[\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mlatitude\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m]\u001b[49m, \n\u001b[0;32m     11\u001b[0m             row1[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mlongitude\u001b[39m\u001b[38;5;124m'\u001b[39m], \n\u001b[0;32m     12\u001b[0m             row2[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mlatitude\u001b[39m\u001b[38;5;124m'\u001b[39m], \n\u001b[0;32m     13\u001b[0m             row2[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mlongitude\u001b[39m\u001b[38;5;124m'\u001b[39m], \n\u001b[0;32m     14\u001b[0m             row1[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mdatahoraservidor\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m/\u001b[39m\u001b[38;5;241m1000\u001b[39m, \u001b[38;5;66;03m# Convert to seconds - Last Date\u001b[39;00m\n\u001b[0;32m     15\u001b[0m             row1[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mlatitude_test\u001b[39m\u001b[38;5;124m'\u001b[39m],\n\u001b[0;32m     16\u001b[0m             row1[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mlongitude_test\u001b[39m\u001b[38;5;124m'\u001b[39m]\n\u001b[0;32m     17\u001b[0m         )\n\u001b[0;32m     19\u001b[0m         median_time_diff_list\u001b[38;5;241m.\u001b[39mextend([median_time_diff, median_time_diff])\n\u001b[0;32m     21\u001b[0m join_df[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mmedian_time_diff\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m=\u001b[39m median_time_diff_list\n",
      "File \u001b[1;32mc:\\Users\\T-Gamer\\anaconda3\\envs\\ACELab\\Lib\\site-packages\\pandas\\core\\series.py:1111\u001b[0m, in \u001b[0;36mSeries.__getitem__\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   1108\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_values[key]\n\u001b[0;32m   1110\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m key_is_scalar:\n\u001b[1;32m-> 1111\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_get_value\u001b[49m\u001b[43m(\u001b[49m\u001b[43mkey\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   1113\u001b[0m \u001b[38;5;66;03m# Convert generator to list before going through hashable part\u001b[39;00m\n\u001b[0;32m   1114\u001b[0m \u001b[38;5;66;03m# (We will iterate through the generator there to check for slices)\u001b[39;00m\n\u001b[0;32m   1115\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m is_iterator(key):\n",
      "File \u001b[1;32mc:\\Users\\T-Gamer\\anaconda3\\envs\\ACELab\\Lib\\site-packages\\pandas\\core\\series.py:1227\u001b[0m, in \u001b[0;36mSeries._get_value\u001b[1;34m(self, label, takeable)\u001b[0m\n\u001b[0;32m   1224\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_values[label]\n\u001b[0;32m   1226\u001b[0m \u001b[38;5;66;03m# Similar to Index.get_value, but we do not fall back to positional\u001b[39;00m\n\u001b[1;32m-> 1227\u001b[0m loc \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mindex\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mget_loc\u001b[49m\u001b[43m(\u001b[49m\u001b[43mlabel\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   1229\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m is_integer(loc):\n\u001b[0;32m   1230\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_values[loc]\n",
      "File \u001b[1;32mc:\\Users\\T-Gamer\\anaconda3\\envs\\ACELab\\Lib\\site-packages\\pandas\\core\\indexes\\base.py:3809\u001b[0m, in \u001b[0;36mIndex.get_loc\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   3804\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(casted_key, \u001b[38;5;28mslice\u001b[39m) \u001b[38;5;129;01mor\u001b[39;00m (\n\u001b[0;32m   3805\u001b[0m         \u001b[38;5;28misinstance\u001b[39m(casted_key, abc\u001b[38;5;241m.\u001b[39mIterable)\n\u001b[0;32m   3806\u001b[0m         \u001b[38;5;129;01mand\u001b[39;00m \u001b[38;5;28many\u001b[39m(\u001b[38;5;28misinstance\u001b[39m(x, \u001b[38;5;28mslice\u001b[39m) \u001b[38;5;28;01mfor\u001b[39;00m x \u001b[38;5;129;01min\u001b[39;00m casted_key)\n\u001b[0;32m   3807\u001b[0m     ):\n\u001b[0;32m   3808\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m InvalidIndexError(key)\n\u001b[1;32m-> 3809\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m(key) \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01merr\u001b[39;00m\n\u001b[0;32m   3810\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mTypeError\u001b[39;00m:\n\u001b[0;32m   3811\u001b[0m     \u001b[38;5;66;03m# If we have a listlike key, _check_indexing_error will raise\u001b[39;00m\n\u001b[0;32m   3812\u001b[0m     \u001b[38;5;66;03m#  InvalidIndexError. Otherwise we fall through and re-raise\u001b[39;00m\n\u001b[0;32m   3813\u001b[0m     \u001b[38;5;66;03m#  the TypeError.\u001b[39;00m\n\u001b[0;32m   3814\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_check_indexing_error(key)\n",
      "\u001b[1;31mKeyError\u001b[0m: 'latitude'"
     ]
    }
   ],
   "source": [
    "median_time_diff_list = []\n",
    "\n",
    "with engine.connect() as connection:\n",
    "    for i in range(0, len(join_df)- 1, 2):\n",
    "        row1 = join_df.iloc[i + 1]\n",
    "        row2 = join_df.iloc[i]\n",
    "        median_time_diff = execute_query(\n",
    "            connection,\n",
    "            row1['linha'], \n",
    "            row1['latitude_last_two'], \n",
    "            row1['longitude_last_two'], \n",
    "            row2['latitude_last_two'], \n",
    "            row2['longitude_last_two'], \n",
    "            row1['datahoraservidor']/1000, # Convert to seconds - Last Date\n",
    "            row1['latitude_test'],\n",
    "            row1['longitude_test']\n",
    "        )\n",
    "\n",
    "        median_time_diff_list.extend([median_time_diff, median_time_diff])\n",
    "\n",
    "join_df['median_time_diff'] = median_time_diff_list\n",
    "\n",
    "df_prediction = join_df[['id','datahoraservidor', 'median_time_diff']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Count nulls (%)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                  0.000000\n",
       "datahoraservidor    0.000000\n",
       "median_time_diff    6.350797\n",
       "dtype: float64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df_prediction.isnull().sum()/len(df_prediction)) * 100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sum interval to see the real time of the bus"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\T-Gamer\\AppData\\Local\\Temp\\ipykernel_18296\\3438387645.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_prediction['real_time'] =  df_prediction['datahoraservidor'] + df_prediction['median_time_diff'].dt.total_seconds() * 1000\n"
     ]
    }
   ],
   "source": [
    "# Sum datahoraservidor and median_time_diff\n",
    "df_prediction['real_time'] =  df_prediction['datahoraservidor'] + df_prediction['median_time_diff'].dt.total_seconds() * 1000\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fill Na values with starting prediction hour + 30 minutes\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Keep only odd indices\n",
    "df_prediction = df_prediction.iloc[1::2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_prediction['real_time'] = df_prediction['real_time'].fillna(df_prediction['datahoraservidor'].max() + 30 * 60 * 1000)\n",
    "df_prediction['real_time'] = df_prediction['real_time'].astype('Int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_prediction = df_prediction[['id','real_time']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_prediction.drop_duplicates(inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Criar json com resposta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'24-05-16_08'"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_files[0][25:36]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "JSON salvo em 24-05-16_08_answer.json\n"
     ]
    }
   ],
   "source": [
    "df_prediction['id'] = df_prediction['id'].astype('Int64')\n",
    "previsoes = df_prediction.values.tolist()\n",
    "\n",
    "match = re.search(r'teste-(\\d{4}-\\d{2}-\\d{2})_(\\d{2})', test_files[0])\n",
    "date_part = match.group(1)\n",
    "hour_part = match.group(2)\n",
    "datahora = f\"{date_part} {hour_part}:00:00\"\n",
    "datahora = \"2024-05-16 04:00:00\"\n",
    "\n",
    "output = {\n",
    "    \"aluno\": \"Pedro Siqueira das Neves\",\n",
    "    \"datahora\": datahora,\n",
    "    \"previsoes\": [[str(item) if isinstance(item, pd.Int64Dtype) else item for item in row] for row in previsoes],\n",
    "    \"senha\": os.getenv(\"SENHA\", \"professorlegal\")\n",
    "}\n",
    "\n",
    "output_json = json.dumps(output, indent=4)\n",
    "\n",
    "output_filename = test_files[0][25:36] + \"_answer.json\"\n",
    "with open(output_filename, \"w\") as json_file:\n",
    "    json_file.write(output_json)\n",
    "\n",
    "print(f\"JSON salvo em {output_filename}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fazer a requisição POST"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fazer o POST usando a biblioteca requests\n",
    "url = 'https://barra.cos.ufrj.br:443/rest/rpc/avalia'\n",
    "headers = {\n",
    "    'accept': 'application/json',\n",
    "    'Content-Type': 'application/json'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Falha no POST: 400\n",
      "{\"code\":\"P0001\",\"details\":\"Exemplo: teste-2024-05-20_13.json ==> 2024-05-20 13:00:00\",\"hint\":\"O testes válidos estão nos arquivos teste-data_hora.json\",\"message\":\"Teste não encontrado: 2024-05-16 04:00:00\"}\n"
     ]
    }
   ],
   "source": [
    "response = requests.post(url, headers=headers, data=output_json)\n",
    "\n",
    "# Verificar a resposta\n",
    "if response.status_code == 200:\n",
    "    print(\"POST bem-sucedido!\")\n",
    "    print(\"Resposta do servidor:\")\n",
    "    print(response.json())\n",
    "else:\n",
    "    print(f\"Falha no POST: {response.status_code}\")\n",
    "    print(response.text)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Compare the distance between predicted and real values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [],
   "source": [
    "answer = pd.read_json(answer_files[0], encoding='latin-1')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = pd.merge(df_prediction, answer, on='id', how='inner', suffixes=('_pred', '_true'))\n",
    "\n",
    "df = pd.DataFrame()\n",
    "\n",
    "df['error'] = final_df['datahora'] - final_df['real_time']\n",
    "df['error'] = df['error']/1000\n",
    "\n",
    "# Calcular o erro médio absoluto (MAE)\n",
    "mae = df['error'].abs().mean()\n",
    "\n",
    "# Calcular a mediana do erro\n",
    "median = df['error'].median()\n",
    "\n",
    "# Calcular o erro quadrático médio (MSE)\n",
    "mse = (df['error'] ** 2).mean()\n",
    "rmse = np.sqrt(mse)\n",
    "# Calcular o desvio padrão (STD) do erro\n",
    "std = df['error'].std()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average error in seconds: 541.46 s\n",
      "Standard deviation of the error in seconds: 845.77 s\n",
      "Median error in seconds: 56.00 s\n",
      "Mean squared error in seconds: 850.35 s\n"
     ]
    }
   ],
   "source": [
    "mean_error = mae\n",
    "std_deviation = std\n",
    "median_error = median\n",
    "rmse_error = rmse\n",
    "\n",
    "\n",
    "print(f'Average error in seconds: {mean_error:.2f} s')\n",
    "print(f'Standard deviation of the error in seconds: {std_deviation:.2f} s')\n",
    "print(f'Median error in seconds: {median_error:.2f} s')\n",
    "print(f'Mean squared error in seconds: {rmse_error:.2f} s')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "ACELab",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}