predict_place.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "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": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_files = [\n",
    "    \"final/2024-05-20/2024-05-20_07.json\",\n",
    "    \"final/2024-05-20/2024-05-20_08.json\",\n",
    "\n",
    "]\n",
    "\n",
    "test_files = [\n",
    "    \"final/2024-05-20/teste-2024-05-20_09.json\",\n",
    "]\n",
    "\n",
    "answer_files = [\n",
    "    \"validation/2024-05-13/resposta-2024-05-13_13.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": 60,
   "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": 61,
   "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']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Join data to predict with last two points dataframe\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "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>2384594492</td>\n",
       "      <td>2336</td>\n",
       "      <td>D87713</td>\n",
       "      <td>1716203184000</td>\n",
       "      <td>-22.88934</td>\n",
       "      <td>-43.55981</td>\n",
       "      <td>1716202765000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2384594492</td>\n",
       "      <td>2336</td>\n",
       "      <td>D87713</td>\n",
       "      <td>1716203184000</td>\n",
       "      <td>-22.88927</td>\n",
       "      <td>-43.55983</td>\n",
       "      <td>1716202796000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10300767826</td>\n",
       "      <td>553</td>\n",
       "      <td>C47891</td>\n",
       "      <td>1716203604000</td>\n",
       "      <td>-22.98277</td>\n",
       "      <td>-43.22356</td>\n",
       "      <td>1716202779000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10300767826</td>\n",
       "      <td>553</td>\n",
       "      <td>C47891</td>\n",
       "      <td>1716203604000</td>\n",
       "      <td>-22.98098</td>\n",
       "      <td>-43.22396</td>\n",
       "      <td>1716202780000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14139244023</td>\n",
       "      <td>864</td>\n",
       "      <td>D86161</td>\n",
       "      <td>1716204857000</td>\n",
       "      <td>-22.90167</td>\n",
       "      <td>-43.55602</td>\n",
       "      <td>1716202789000</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>323037</th>\n",
       "      <td>999963343402356</td>\n",
       "      <td>415</td>\n",
       "      <td>A48048</td>\n",
       "      <td>1716203185000</td>\n",
       "      <td>-22.93200</td>\n",
       "      <td>-43.23992</td>\n",
       "      <td>1716202800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>323038</th>\n",
       "      <td>999976895779326</td>\n",
       "      <td>606</td>\n",
       "      <td>B25583</td>\n",
       "      <td>1716203097000</td>\n",
       "      <td>-22.90662</td>\n",
       "      <td>-43.27637</td>\n",
       "      <td>1716202602000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>323039</th>\n",
       "      <td>999976895779326</td>\n",
       "      <td>606</td>\n",
       "      <td>B25583</td>\n",
       "      <td>1716203097000</td>\n",
       "      <td>-22.90757</td>\n",
       "      <td>-43.27717</td>\n",
       "      <td>1716202643000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>323040</th>\n",
       "      <td>999999304279971</td>\n",
       "      <td>324</td>\n",
       "      <td>B28568</td>\n",
       "      <td>1716204112000</td>\n",
       "      <td>-22.85508</td>\n",
       "      <td>-43.24744</td>\n",
       "      <td>1716202788000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>323041</th>\n",
       "      <td>999999304279971</td>\n",
       "      <td>324</td>\n",
       "      <td>B28568</td>\n",
       "      <td>1716204112000</td>\n",
       "      <td>-22.85361</td>\n",
       "      <td>-43.24738</td>\n",
       "      <td>1716202819000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>323042 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     id linha   ordem       datahora  latitude  longitude  \\\n",
       "0            2384594492  2336  D87713  1716203184000 -22.88934  -43.55981   \n",
       "1            2384594492  2336  D87713  1716203184000 -22.88927  -43.55983   \n",
       "2           10300767826   553  C47891  1716203604000 -22.98277  -43.22356   \n",
       "3           10300767826   553  C47891  1716203604000 -22.98098  -43.22396   \n",
       "4           14139244023   864  D86161  1716204857000 -22.90167  -43.55602   \n",
       "...                 ...   ...     ...            ...       ...        ...   \n",
       "323037  999963343402356   415  A48048  1716203185000 -22.93200  -43.23992   \n",
       "323038  999976895779326   606  B25583  1716203097000 -22.90662  -43.27637   \n",
       "323039  999976895779326   606  B25583  1716203097000 -22.90757  -43.27717   \n",
       "323040  999999304279971   324  B28568  1716204112000 -22.85508  -43.24744   \n",
       "323041  999999304279971   324  B28568  1716204112000 -22.85361  -43.24738   \n",
       "\n",
       "        datahoraservidor  \n",
       "0          1716202765000  \n",
       "1          1716202796000  \n",
       "2          1716202779000  \n",
       "3          1716202780000  \n",
       "4          1716202789000  \n",
       "...                  ...  \n",
       "323037     1716202800000  \n",
       "323038     1716202602000  \n",
       "323039     1716202643000  \n",
       "323040     1716202788000  \n",
       "323041     1716202819000  \n",
       "\n",
       "[323042 rows x 7 columns]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test['linha'] = test['linha'].astype(str)\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')\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": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "database_url = os.getenv(\"DATABASE_URL\")\n",
    "engine = create_engine(database_url, client_encoding='latin-1')  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "def execute_query(connection, linha, lat1, lon1, lat2, lon2, last_date, prediction_date):\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 DISTINCT ON (vo.ordem, vo.datahoraservidor)\n",
    "            vo.x,\n",
    "            vo.y,\n",
    "            vo.ordem,\n",
    "            vo.datahoraservidor              \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.datahoraservidor > dp.datahoraservidor + (TO_TIMESTAMP(:prediction_date) - TO_TIMESTAMP(:last_date) - interval '2 minutes')\n",
    "        AND vo.datahoraservidor < dp.datahoraservidor + (TO_TIMESTAMP(:prediction_date) - TO_TIMESTAMP(:last_date) + interval '2 minutes')\n",
    "    ), selected_future_points AS (\n",
    "        SELECT x,y\n",
    "        FROM first_future_points\n",
    "    )\n",
    "    SELECT \n",
    "        ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x)) AS median_x,\n",
    "        ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY y)) AS median_y\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_date': str(prediction_date)\n",
    "    }\n",
    "    \n",
    "    \n",
    "    result = connection.execute(text(query), params)\n",
    "    row = result.fetchone()\n",
    "        \n",
    "    return row[0], row[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "median_x_list = []\n",
    "median_y_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_x, median_y = execute_query(\n",
    "            connection,\n",
    "            row1['linha'], \n",
    "            row1['latitude'], \n",
    "            row1['longitude'], \n",
    "            row2['latitude'], \n",
    "            row2['longitude'], \n",
    "            row1['datahoraservidor']/1000, # Convert to seconds - Last Date\n",
    "            row1['datahora']/1000 # Convert to seconds - Prediction Date\n",
    "        )\n",
    "\n",
    "        median_x_list.extend([median_x, median_x])\n",
    "        median_y_list.extend([median_y, median_y])\n",
    "\n",
    "join_df['median_x'] = median_x_list\n",
    "join_df['median_y'] = median_y_list\n",
    "\n",
    "df_prediction = join_df[['id','latitude', 'longitude', 'median_y','median_x']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Count nulls (%)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id           0.000000\n",
       "latitude     0.000000\n",
       "longitude    0.000000\n",
       "median_y     8.110941\n",
       "median_x     8.110941\n",
       "dtype: float64"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df_prediction.isnull().sum()/len(df_prediction)) * 100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Convert the prediction to latitude and longitude"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\T-Gamer\\AppData\\Local\\Temp\\ipykernel_19888\\1338526071.py:31: 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['median_x'] = df_prediction['median_x'].apply(inverse_width_bucket_x)\n",
      "C:\\Users\\T-Gamer\\AppData\\Local\\Temp\\ipykernel_19888\\1338526071.py:32: 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['median_y'] = df_prediction['median_y'].apply(inverse_width_bucket_y)\n"
     ]
    }
   ],
   "source": [
    "def inverse_width_bucket_x(bucket_index):\n",
    "    min_value = -43.726090\n",
    "    max_value = -42.951470\n",
    "    num_buckets = 1587\n",
    "\n",
    "    if bucket_index < 1:\n",
    "        return min_value\n",
    "    elif bucket_index > num_buckets:\n",
    "        return max_value\n",
    "    \n",
    "    bucket_size = (max_value - min_value) / num_buckets\n",
    "    value = min_value + (bucket_index - 1) * bucket_size + bucket_size / 2\n",
    "    \n",
    "    return value\n",
    "    \n",
    "def inverse_width_bucket_y(bucket_index):\n",
    "    min_value = -23.170790\n",
    "    max_value = -22.546410\n",
    "    num_buckets = 1389\n",
    "\n",
    "    if bucket_index < 1:\n",
    "        return min_value\n",
    "    elif bucket_index > num_buckets:\n",
    "        return max_value\n",
    "    \n",
    "    bucket_size = (max_value - min_value) / num_buckets\n",
    "    value = min_value + (bucket_index - 1) * bucket_size + bucket_size / 2\n",
    "    \n",
    "    return value\n",
    "\n",
    "df_prediction['median_x'] = df_prediction['median_x'].apply(inverse_width_bucket_x)\n",
    "df_prediction['median_y'] = df_prediction['median_y'].apply(inverse_width_bucket_y)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fill Na values with latitude and longitude columns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Keep only newest rows for null values\n",
    "even_indices = df_prediction.index[df_prediction.index % 2 == 0]\n",
    "rows_with_nulls = df_prediction.loc[even_indices].isnull().any(axis=1)\n",
    "df_prediction = df_prediction.drop(even_indices[rows_with_nulls])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_prediction['median_x'] = df_prediction['median_x'].fillna(df_prediction['longitude'])\n",
    "df_prediction['median_y'] = df_prediction['median_y'].fillna(df_prediction['latitude'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_prediction = df_prediction[['id','median_y','median_x']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_prediction.drop_duplicates(inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Criar json com resposta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'24-05-19_19'"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_files[0][25:36]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "JSON salvo em 24-05-19_19_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",
    "\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": null,
   "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": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "POST bem-sucedido!\n",
      "Resposta do servidor:\n",
      "{'msg': 'Problemas!', 'arquivo teste': 'teste-2024-05-19_19.json', 'rmse': 3342.7749790812763, 'ids não encontrados': 0, 'ids testados': 88732, 'total na tabela': 89258}\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": null,
   "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>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>-22.88206</td>\n",
       "      <td>-43.32547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>-22.90037</td>\n",
       "      <td>-43.23099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>-22.88279</td>\n",
       "      <td>-43.29586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>-22.90811</td>\n",
       "      <td>-43.23055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>-22.88593</td>\n",
       "      <td>-43.30216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156637</th>\n",
       "      <td>156638</td>\n",
       "      <td>-22.87820</td>\n",
       "      <td>-43.24119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156638</th>\n",
       "      <td>156639</td>\n",
       "      <td>-22.83674</td>\n",
       "      <td>-43.28420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156639</th>\n",
       "      <td>156640</td>\n",
       "      <td>-22.96980</td>\n",
       "      <td>-43.18537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156640</th>\n",
       "      <td>156641</td>\n",
       "      <td>-22.84024</td>\n",
       "      <td>-43.27761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156641</th>\n",
       "      <td>156642</td>\n",
       "      <td>-22.86608</td>\n",
       "      <td>-43.25179</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>156642 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            id  latitude  longitude\n",
       "0            1 -22.88206  -43.32547\n",
       "1            2 -22.90037  -43.23099\n",
       "2            3 -22.88279  -43.29586\n",
       "3            4 -22.90811  -43.23055\n",
       "4            5 -22.88593  -43.30216\n",
       "...        ...       ...        ...\n",
       "156637  156638 -22.87820  -43.24119\n",
       "156638  156639 -22.83674  -43.28420\n",
       "156639  156640 -22.96980  -43.18537\n",
       "156640  156641 -22.84024  -43.27761\n",
       "156641  156642 -22.86608  -43.25179\n",
       "\n",
       "[156642 rows x 3 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "answer = pd.read_json(answer_files[0], encoding='latin-1')\n",
    "answer['latitude'] = answer['latitude'].str.replace(',', '.').astype(float)\n",
    "answer['longitude'] = answer['longitude'].str.replace(',', '.').astype(float)\n",
    "answer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to calculate the distance between two points in a sphere in meters\n",
    "def haversine(lat1, lon1, lat2, lon2):\n",
    "    R = 6371000  # radius of Earth in meters\n",
    "    phi1 = np.radians(lat1)\n",
    "    phi2 = np.radians(lat2)\n",
    "    delta_phi = np.radians(lat2 - lat1)\n",
    "    delta_lambda = np.radians(lon2 - lon1)\n",
    "    \n",
    "    a = np.sin(delta_phi / 2.0) ** 2 + np.cos(phi1) * np.cos(phi2) * np.sin(delta_lambda / 2.0) ** 2\n",
    "    c = 2 * np.arctan2(np.sqrt(a), np.sqrt(1 - a))\n",
    "    \n",
    "    meters = R * c\n",
    "    return meters\n",
    "\n",
    "final_df = answer.merge(df_prediction, on='id', suffixes=('_true', '_pred'))\n",
    "\n",
    "final_df['error_meters'] = final_df.apply(\n",
    "    lambda row: haversine(row['latitude'], row['longitude'], row['median_y'], row['median_x']), axis=1\n",
    ")\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average error in meters: nan m\n",
      "Standard deviation of the error in meters: nan m\n",
      "Median error in meters: nan m\n"
     ]
    }
   ],
   "source": [
    "mean_error = final_df['error_meters'].mean()\n",
    "std_deviation = final_df['error_meters'].std()\n",
    "median_error = final_df['error_meters'].median()\n",
    "\n",
    "print(f'Average error in meters: {mean_error:.2f} m')\n",
    "print(f'Standard deviation of the error in meters: {std_deviation:.2f} m')\n",
    "print(f'Median error in meters: {median_error:.2f} m')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}