eyn-original.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
    "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['ey-nextwave', 'ndscindexes']\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from tqdm import tqdm_notebook as tqdm\n",
    "print(os.listdir(\"../input\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train = pd.read_csv(\"../input/ey-nextwave/data_train/data_train.csv\")\n",
    "df_test = pd.read_csv(\"../input/ey-nextwave/data_test/data_test.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Unnamed: 0</th>\n",
       "      <th>hash</th>\n",
       "      <th>trajectory_id</th>\n",
       "      <th>time_entry</th>\n",
       "      <th>time_exit</th>\n",
       "      <th>vmax</th>\n",
       "      <th>vmin</th>\n",
       "      <th>vmean</th>\n",
       "      <th>x_entry</th>\n",
       "      <th>y_entry</th>\n",
       "      <th>x_exit</th>\n",
       "      <th>y_exit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>814257</th>\n",
       "      <td>814257</td>\n",
       "      <td>ffffc6359725f0e1feac9ef1872ab207_11</td>\n",
       "      <td>traj_ffffc6359725f0e1feac9ef1872ab207_11_4</td>\n",
       "      <td>02:21:11</td>\n",
       "      <td>02:21:11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.744666e+06</td>\n",
       "      <td>-1.925679e+07</td>\n",
       "      <td>3.744666e+06</td>\n",
       "      <td>-1.925679e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>814258</th>\n",
       "      <td>814258</td>\n",
       "      <td>ffffc6359725f0e1feac9ef1872ab207_11</td>\n",
       "      <td>traj_ffffc6359725f0e1feac9ef1872ab207_11_5</td>\n",
       "      <td>06:02:17</td>\n",
       "      <td>06:02:17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.744732e+06</td>\n",
       "      <td>-1.925614e+07</td>\n",
       "      <td>3.744732e+06</td>\n",
       "      <td>-1.925614e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>814259</th>\n",
       "      <td>814259</td>\n",
       "      <td>ffffc6359725f0e1feac9ef1872ab207_11</td>\n",
       "      <td>traj_ffffc6359725f0e1feac9ef1872ab207_11_7</td>\n",
       "      <td>09:52:13</td>\n",
       "      <td>09:52:13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.744666e+06</td>\n",
       "      <td>-1.925679e+07</td>\n",
       "      <td>3.744666e+06</td>\n",
       "      <td>-1.925679e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>814260</th>\n",
       "      <td>814260</td>\n",
       "      <td>ffffc6359725f0e1feac9ef1872ab207_11</td>\n",
       "      <td>traj_ffffc6359725f0e1feac9ef1872ab207_11_8</td>\n",
       "      <td>14:20:26</td>\n",
       "      <td>14:27:15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.741043e+06</td>\n",
       "      <td>-1.929051e+07</td>\n",
       "      <td>3.741057e+06</td>\n",
       "      <td>-1.928936e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>814261</th>\n",
       "      <td>814261</td>\n",
       "      <td>ffffc6359725f0e1feac9ef1872ab207_11</td>\n",
       "      <td>traj_ffffc6359725f0e1feac9ef1872ab207_11_10</td>\n",
       "      <td>14:56:46</td>\n",
       "      <td>15:04:05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.743948e+06</td>\n",
       "      <td>-1.923560e+07</td>\n",
       "      <td>3.744842e+06</td>\n",
       "      <td>-1.926218e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Unnamed: 0      ...             y_exit\n",
       "814257      814257      ...      -1.925679e+07\n",
       "814258      814258      ...      -1.925614e+07\n",
       "814259      814259      ...      -1.925679e+07\n",
       "814260      814260      ...      -1.928936e+07\n",
       "814261      814261      ...      -1.926218e+07\n",
       "\n",
       "[5 rows x 12 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Unnamed: 0</th>\n",
       "      <th>hash</th>\n",
       "      <th>trajectory_id</th>\n",
       "      <th>time_entry</th>\n",
       "      <th>time_exit</th>\n",
       "      <th>vmax</th>\n",
       "      <th>vmin</th>\n",
       "      <th>vmean</th>\n",
       "      <th>x_entry</th>\n",
       "      <th>y_entry</th>\n",
       "      <th>x_exit</th>\n",
       "      <th>y_exit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>202932</th>\n",
       "      <td>202932</td>\n",
       "      <td>fff9552047b095e8242b4913f3289a26_25</td>\n",
       "      <td>traj_fff9552047b095e8242b4913f3289a26_25_3</td>\n",
       "      <td>11:23:33</td>\n",
       "      <td>11:23:33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.762713e+06</td>\n",
       "      <td>-1.935493e+07</td>\n",
       "      <td>3.762713e+06</td>\n",
       "      <td>-1.935493e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202933</th>\n",
       "      <td>202933</td>\n",
       "      <td>fff9552047b095e8242b4913f3289a26_25</td>\n",
       "      <td>traj_fff9552047b095e8242b4913f3289a26_25_4</td>\n",
       "      <td>12:12:10</td>\n",
       "      <td>12:12:10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.761040e+06</td>\n",
       "      <td>-1.935274e+07</td>\n",
       "      <td>3.761040e+06</td>\n",
       "      <td>-1.935274e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202934</th>\n",
       "      <td>202934</td>\n",
       "      <td>fff9552047b095e8242b4913f3289a26_25</td>\n",
       "      <td>traj_fff9552047b095e8242b4913f3289a26_25_5</td>\n",
       "      <td>13:08:14</td>\n",
       "      <td>13:12:01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.762680e+06</td>\n",
       "      <td>-1.935570e+07</td>\n",
       "      <td>3.762683e+06</td>\n",
       "      <td>-1.935529e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202935</th>\n",
       "      <td>202935</td>\n",
       "      <td>fff9552047b095e8242b4913f3289a26_25</td>\n",
       "      <td>traj_fff9552047b095e8242b4913f3289a26_25_6</td>\n",
       "      <td>14:14:36</td>\n",
       "      <td>14:14:36</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.761776e+06</td>\n",
       "      <td>-1.935772e+07</td>\n",
       "      <td>3.761776e+06</td>\n",
       "      <td>-1.935772e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202936</th>\n",
       "      <td>202936</td>\n",
       "      <td>fff9552047b095e8242b4913f3289a26_25</td>\n",
       "      <td>traj_fff9552047b095e8242b4913f3289a26_25_7</td>\n",
       "      <td>15:30:54</td>\n",
       "      <td>15:30:54</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.762713e+06</td>\n",
       "      <td>-1.935478e+07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Unnamed: 0      ...             y_exit\n",
       "202932      202932      ...      -1.935493e+07\n",
       "202933      202933      ...      -1.935274e+07\n",
       "202934      202934      ...      -1.935529e+07\n",
       "202935      202935      ...      -1.935772e+07\n",
       "202936      202936      ...                NaN\n",
       "\n",
       "[5 rows x 12 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "_cell_guid": "79c7e3d0-c299-4dcb-8224-4455121ee9b0",
    "_uuid": "d629ff2d2480ee46fbb7e2d37f6b5fab8052498a"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Borders:\n",
      "-1.0000 < X < 1.0000\n",
      "-0.3000 < Y < 0.3000\n"
     ]
    }
   ],
   "source": [
    "# normalising location information\n",
    "X_MIN = 3750901.5068\n",
    "X_MAX = 3770901.5068\n",
    "X_MID = X_MIN + 0.5 * (X_MAX - X_MIN)\n",
    "Y_MIN = -19268905.6133\n",
    "Y_MAX = -19208905.6133\n",
    "Y_MID = Y_MIN + 0.5 * (Y_MAX - Y_MIN)\n",
    "\n",
    "def normalise_X(arr):\n",
    "    return (arr - X_MID) / 10000\n",
    "\n",
    "def normalise_Y(arr):\n",
    "    return (arr - Y_MID) / 100000  \n",
    "    # extra zero by design, seems to make figure to be in proportion\n",
    "    # looking for evidence that the x-axis and y-axis fulfil some ratio\n",
    "\n",
    "x_min, x_max = normalise_X(X_MIN), normalise_X(X_MAX)\n",
    "y_min, y_max = normalise_Y(Y_MIN), normalise_Y(Y_MAX)\n",
    "print(\"Borders:\")\n",
    "print(\"{:.4f} < X < {:.4f}\".format(x_min, x_max))\n",
    "print(\"{:.4f} < Y < {:.4f}\".format(y_min, y_max))\n",
    "\n",
    "df_train['x_entry'], df_train['x_exit'] = normalise_X(df_train['x_entry']), normalise_X(df_train['x_exit'])\n",
    "df_train['y_entry'], df_train['y_exit'] = normalise_Y(df_train['y_entry']), normalise_Y(df_train['y_exit'])\n",
    "df_test['x_entry'], df_test['x_exit'] = normalise_X(df_test['x_entry']), normalise_X(df_test['x_exit'])\n",
    "df_test['y_entry'], df_test['y_exit'] = normalise_Y(df_test['y_entry']), normalise_Y(df_test['y_exit'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# normalising time information\n",
    "def convert_time(time_sting):\n",
    "    hms = time_sting.split(\":\")\n",
    "    seconds = int(hms[0])*60*60 + int(hms[1])*60 + int(hms[2])\n",
    "    seconds = (seconds-15*60*60)/(10*60*60)\n",
    "    return seconds\n",
    "\n",
    "df_train[\"t_entry\"] = df_train[\"time_entry\"].apply(lambda x: convert_time(x))\n",
    "df_train[\"t_exit\"] = df_train[\"time_exit\"].apply(lambda x: convert_time(x))\n",
    "df_test[\"t_entry\"] = df_test[\"time_entry\"].apply(lambda x: convert_time(x))\n",
    "df_test[\"t_exit\"] = df_test[\"time_exit\"].apply(lambda x: convert_time(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# obtaining metadata from IDs\n",
    "df_train['tid_0'] = [tid.split(\"_\")[-1] for tid in df_train['trajectory_id']]\n",
    "df_train['tid_1'] = [tid.split(\"_\")[-2] for tid in df_train['trajectory_id']]\n",
    "df_test['tid_0'] = [tid.split(\"_\")[-1] for tid in df_test['trajectory_id']]\n",
    "df_test['tid_1'] = [tid.split(\"_\")[-2] for tid in df_test['trajectory_id']]\n",
    "df_train['tid_0'], df_test['tid_0'] = df_train['tid_0'].astype(int), df_test['tid_0'].astype(int)\n",
    "df_train['tid_1'], df_test['tid_1'] = df_train['tid_1'].astype(int), df_test['tid_1'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# extract relevant infromation and rearrange\n",
    "columns = ['hash','tid_0',\n",
    "           't_entry','t_exit',\n",
    "           'x_entry','y_entry','x_exit','y_exit',\n",
    "           'vmax','vmin','vmean',\n",
    "           'time_entry','time_exit',\n",
    "           'trajectory_id','tid_1']\n",
    "df_train = df_train[columns]\n",
    "df_test = df_test[columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "54 31\n"
     ]
    }
   ],
   "source": [
    "# tid_1 is likely the day of the month, this information may be useful\n",
    "print(max([int(x) for x in df_test['tid_0']]), max([int(x) for x in df_test['tid_1']]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>hash</th>\n",
       "      <th>tid_0</th>\n",
       "      <th>t_entry</th>\n",
       "      <th>t_exit</th>\n",
       "      <th>x_entry</th>\n",
       "      <th>y_entry</th>\n",
       "      <th>x_exit</th>\n",
       "      <th>y_exit</th>\n",
       "      <th>vmax</th>\n",
       "      <th>vmin</th>\n",
       "      <th>vmean</th>\n",
       "      <th>time_entry</th>\n",
       "      <th>time_exit</th>\n",
       "      <th>trajectory_id</th>\n",
       "      <th>tid_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>808281</th>\n",
       "      <td>fe1ebe07b2f3adca2e60e10f2d95099f_9</td>\n",
       "      <td>0</td>\n",
       "      <td>-1.439111</td>\n",
       "      <td>-1.439111</td>\n",
       "      <td>0.196111</td>\n",
       "      <td>-0.000211</td>\n",
       "      <td>0.196111</td>\n",
       "      <td>-0.000211</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>00:36:32</td>\n",
       "      <td>00:36:32</td>\n",
       "      <td>traj_fe1ebe07b2f3adca2e60e10f2d95099f_9_0</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808282</th>\n",
       "      <td>fe1ebe07b2f3adca2e60e10f2d95099f_9</td>\n",
       "      <td>1</td>\n",
       "      <td>-1.404222</td>\n",
       "      <td>-1.404222</td>\n",
       "      <td>0.196445</td>\n",
       "      <td>0.004345</td>\n",
       "      <td>0.196445</td>\n",
       "      <td>0.004345</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>00:57:28</td>\n",
       "      <td>00:57:28</td>\n",
       "      <td>traj_fe1ebe07b2f3adca2e60e10f2d95099f_9_1</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808283</th>\n",
       "      <td>fe1ebe07b2f3adca2e60e10f2d95099f_9</td>\n",
       "      <td>2</td>\n",
       "      <td>-1.369028</td>\n",
       "      <td>-1.369028</td>\n",
       "      <td>0.200341</td>\n",
       "      <td>0.004003</td>\n",
       "      <td>0.200341</td>\n",
       "      <td>0.004003</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>01:18:35</td>\n",
       "      <td>01:18:35</td>\n",
       "      <td>traj_fe1ebe07b2f3adca2e60e10f2d95099f_9_2</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808284</th>\n",
       "      <td>fe1ebe07b2f3adca2e60e10f2d95099f_9</td>\n",
       "      <td>3</td>\n",
       "      <td>-1.334472</td>\n",
       "      <td>-1.334472</td>\n",
       "      <td>0.196111</td>\n",
       "      <td>-0.000325</td>\n",
       "      <td>0.196111</td>\n",
       "      <td>-0.000325</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>01:39:19</td>\n",
       "      <td>01:39:19</td>\n",
       "      <td>traj_fe1ebe07b2f3adca2e60e10f2d95099f_9_3</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808285</th>\n",
       "      <td>fe1ebe07b2f3adca2e60e10f2d95099f_9</td>\n",
       "      <td>4</td>\n",
       "      <td>-1.300222</td>\n",
       "      <td>-1.300222</td>\n",
       "      <td>0.197447</td>\n",
       "      <td>0.006965</td>\n",
       "      <td>0.197447</td>\n",
       "      <td>0.006965</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>01:59:52</td>\n",
       "      <td>01:59:52</td>\n",
       "      <td>traj_fe1ebe07b2f3adca2e60e10f2d95099f_9_4</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808286</th>\n",
       "      <td>fe1ebe07b2f3adca2e60e10f2d95099f_9</td>\n",
       "      <td>5</td>\n",
       "      <td>-1.204111</td>\n",
       "      <td>-1.204111</td>\n",
       "      <td>0.197113</td>\n",
       "      <td>0.009357</td>\n",
       "      <td>0.197113</td>\n",
       "      <td>0.009357</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>02:57:32</td>\n",
       "      <td>02:57:32</td>\n",
       "      <td>traj_fe1ebe07b2f3adca2e60e10f2d95099f_9_5</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808287</th>\n",
       "      <td>fe1ebe07b2f3adca2e60e10f2d95099f_9</td>\n",
       "      <td>6</td>\n",
       "      <td>-1.064333</td>\n",
       "      <td>-1.064333</td>\n",
       "      <td>0.197781</td>\n",
       "      <td>0.006737</td>\n",
       "      <td>0.197781</td>\n",
       "      <td>0.006737</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>04:21:24</td>\n",
       "      <td>04:21:24</td>\n",
       "      <td>traj_fe1ebe07b2f3adca2e60e10f2d95099f_9_6</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808288</th>\n",
       "      <td>fe1ebe07b2f3adca2e60e10f2d95099f_9</td>\n",
       "      <td>7</td>\n",
       "      <td>-1.031722</td>\n",
       "      <td>-1.031722</td>\n",
       "      <td>0.201566</td>\n",
       "      <td>0.006395</td>\n",
       "      <td>0.201566</td>\n",
       "      <td>0.006395</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>04:40:58</td>\n",
       "      <td>04:40:58</td>\n",
       "      <td>traj_fe1ebe07b2f3adca2e60e10f2d95099f_9_7</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808289</th>\n",
       "      <td>fe1ebe07b2f3adca2e60e10f2d95099f_9</td>\n",
       "      <td>8</td>\n",
       "      <td>-0.966444</td>\n",
       "      <td>-0.966444</td>\n",
       "      <td>0.196000</td>\n",
       "      <td>0.004915</td>\n",
       "      <td>0.196000</td>\n",
       "      <td>0.004915</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>05:20:08</td>\n",
       "      <td>05:20:08</td>\n",
       "      <td>traj_fe1ebe07b2f3adca2e60e10f2d95099f_9_8</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808290</th>\n",
       "      <td>fe1ebe07b2f3adca2e60e10f2d95099f_9</td>\n",
       "      <td>9</td>\n",
       "      <td>-0.864306</td>\n",
       "      <td>-0.864306</td>\n",
       "      <td>0.198226</td>\n",
       "      <td>0.005028</td>\n",
       "      <td>0.198226</td>\n",
       "      <td>0.005028</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>06:21:25</td>\n",
       "      <td>06:21:25</td>\n",
       "      <td>traj_fe1ebe07b2f3adca2e60e10f2d95099f_9_9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808291</th>\n",
       "      <td>fe1ebe07b2f3adca2e60e10f2d95099f_9</td>\n",
       "      <td>10</td>\n",
       "      <td>-0.829944</td>\n",
       "      <td>-0.829944</td>\n",
       "      <td>0.194218</td>\n",
       "      <td>0.005826</td>\n",
       "      <td>0.194218</td>\n",
       "      <td>0.005826</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>06:42:02</td>\n",
       "      <td>06:42:02</td>\n",
       "      <td>traj_fe1ebe07b2f3adca2e60e10f2d95099f_9_10</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808292</th>\n",
       "      <td>fe1ebe07b2f3adca2e60e10f2d95099f_9</td>\n",
       "      <td>11</td>\n",
       "      <td>-0.798083</td>\n",
       "      <td>-0.798083</td>\n",
       "      <td>0.194775</td>\n",
       "      <td>0.003889</td>\n",
       "      <td>0.194775</td>\n",
       "      <td>0.003889</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>07:01:09</td>\n",
       "      <td>07:01:09</td>\n",
       "      <td>traj_fe1ebe07b2f3adca2e60e10f2d95099f_9_11</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808293</th>\n",
       "      <td>fe1ebe07b2f3adca2e60e10f2d95099f_9</td>\n",
       "      <td>13</td>\n",
       "      <td>-0.601361</td>\n",
       "      <td>-0.601361</td>\n",
       "      <td>0.195777</td>\n",
       "      <td>0.002636</td>\n",
       "      <td>0.195777</td>\n",
       "      <td>0.002636</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>08:59:11</td>\n",
       "      <td>08:59:11</td>\n",
       "      <td>traj_fe1ebe07b2f3adca2e60e10f2d95099f_9_13</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808294</th>\n",
       "      <td>fe1ebe07b2f3adca2e60e10f2d95099f_9</td>\n",
       "      <td>14</td>\n",
       "      <td>-0.547361</td>\n",
       "      <td>-0.547361</td>\n",
       "      <td>0.196111</td>\n",
       "      <td>-0.000325</td>\n",
       "      <td>0.196111</td>\n",
       "      <td>-0.000325</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>09:31:35</td>\n",
       "      <td>09:31:35</td>\n",
       "      <td>traj_fe1ebe07b2f3adca2e60e10f2d95099f_9_14</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808295</th>\n",
       "      <td>fe1ebe07b2f3adca2e60e10f2d95099f_9</td>\n",
       "      <td>15</td>\n",
       "      <td>-0.494833</td>\n",
       "      <td>-0.494833</td>\n",
       "      <td>0.199005</td>\n",
       "      <td>0.003320</td>\n",
       "      <td>0.199005</td>\n",
       "      <td>0.003320</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>10:03:06</td>\n",
       "      <td>10:03:06</td>\n",
       "      <td>traj_fe1ebe07b2f3adca2e60e10f2d95099f_9_15</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808296</th>\n",
       "      <td>fe1ebe07b2f3adca2e60e10f2d95099f_9</td>\n",
       "      <td>16</td>\n",
       "      <td>-0.446250</td>\n",
       "      <td>-0.446250</td>\n",
       "      <td>0.197113</td>\n",
       "      <td>0.007193</td>\n",
       "      <td>0.197113</td>\n",
       "      <td>0.007193</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>10:32:15</td>\n",
       "      <td>10:32:15</td>\n",
       "      <td>traj_fe1ebe07b2f3adca2e60e10f2d95099f_9_16</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808297</th>\n",
       "      <td>fe1ebe07b2f3adca2e60e10f2d95099f_9</td>\n",
       "      <td>18</td>\n",
       "      <td>-0.390444</td>\n",
       "      <td>-0.390444</td>\n",
       "      <td>0.192994</td>\n",
       "      <td>0.002864</td>\n",
       "      <td>0.192994</td>\n",
       "      <td>0.002864</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>11:05:44</td>\n",
       "      <td>11:05:44</td>\n",
       "      <td>traj_fe1ebe07b2f3adca2e60e10f2d95099f_9_18</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808298</th>\n",
       "      <td>fe1ebe07b2f3adca2e60e10f2d95099f_9</td>\n",
       "      <td>19</td>\n",
       "      <td>-0.299278</td>\n",
       "      <td>-0.278250</td>\n",
       "      <td>0.177409</td>\n",
       "      <td>-0.007275</td>\n",
       "      <td>0.765955</td>\n",
       "      <td>0.330428</td>\n",
       "      <td>13.19</td>\n",
       "      <td>13.19</td>\n",
       "      <td>13.19</td>\n",
       "      <td>12:00:26</td>\n",
       "      <td>12:13:03</td>\n",
       "      <td>traj_fe1ebe07b2f3adca2e60e10f2d95099f_9_19</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808299</th>\n",
       "      <td>fe1ebe07b2f3adca2e60e10f2d95099f_9</td>\n",
       "      <td>20</td>\n",
       "      <td>-0.227278</td>\n",
       "      <td>-0.227278</td>\n",
       "      <td>0.754044</td>\n",
       "      <td>0.335640</td>\n",
       "      <td>0.754044</td>\n",
       "      <td>0.335640</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>12:43:38</td>\n",
       "      <td>12:43:38</td>\n",
       "      <td>traj_fe1ebe07b2f3adca2e60e10f2d95099f_9_20</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808300</th>\n",
       "      <td>fe1ebe07b2f3adca2e60e10f2d95099f_9</td>\n",
       "      <td>22</td>\n",
       "      <td>0.025722</td>\n",
       "      <td>0.078194</td>\n",
       "      <td>0.754378</td>\n",
       "      <td>0.307984</td>\n",
       "      <td>0.197447</td>\n",
       "      <td>0.005826</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>15:15:26</td>\n",
       "      <td>15:46:55</td>\n",
       "      <td>traj_fe1ebe07b2f3adca2e60e10f2d95099f_9_22</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      hash  ...    tid_1\n",
       "808281  fe1ebe07b2f3adca2e60e10f2d95099f_9  ...        9\n",
       "808282  fe1ebe07b2f3adca2e60e10f2d95099f_9  ...        9\n",
       "808283  fe1ebe07b2f3adca2e60e10f2d95099f_9  ...        9\n",
       "808284  fe1ebe07b2f3adca2e60e10f2d95099f_9  ...        9\n",
       "808285  fe1ebe07b2f3adca2e60e10f2d95099f_9  ...        9\n",
       "808286  fe1ebe07b2f3adca2e60e10f2d95099f_9  ...        9\n",
       "808287  fe1ebe07b2f3adca2e60e10f2d95099f_9  ...        9\n",
       "808288  fe1ebe07b2f3adca2e60e10f2d95099f_9  ...        9\n",
       "808289  fe1ebe07b2f3adca2e60e10f2d95099f_9  ...        9\n",
       "808290  fe1ebe07b2f3adca2e60e10f2d95099f_9  ...        9\n",
       "808291  fe1ebe07b2f3adca2e60e10f2d95099f_9  ...        9\n",
       "808292  fe1ebe07b2f3adca2e60e10f2d95099f_9  ...        9\n",
       "808293  fe1ebe07b2f3adca2e60e10f2d95099f_9  ...        9\n",
       "808294  fe1ebe07b2f3adca2e60e10f2d95099f_9  ...        9\n",
       "808295  fe1ebe07b2f3adca2e60e10f2d95099f_9  ...        9\n",
       "808296  fe1ebe07b2f3adca2e60e10f2d95099f_9  ...        9\n",
       "808297  fe1ebe07b2f3adca2e60e10f2d95099f_9  ...        9\n",
       "808298  fe1ebe07b2f3adca2e60e10f2d95099f_9  ...        9\n",
       "808299  fe1ebe07b2f3adca2e60e10f2d95099f_9  ...        9\n",
       "808300  fe1ebe07b2f3adca2e60e10f2d95099f_9  ...        9\n",
       "\n",
       "[20 rows x 15 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hash_most_freq = df_train['hash'].mode().tail(1).item()\n",
    "df_train.loc[df_train['hash'] == hash_most_freq]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# BASELINE SUBMISSION"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>202899</th>\n",
       "      <td>traj_ffe98f6e0adf12f9c7b51c4e9607a87a_15_13</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202913</th>\n",
       "      <td>traj_fff607ecd3f8d3dcb65791e8b4c22a5f_3_25</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202914</th>\n",
       "      <td>traj_fff813b56230c2f026f783f5b9f9ca90_19_0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202929</th>\n",
       "      <td>traj_fff9400843a88c3bfe52e7ce8bf97316_19_17</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202936</th>\n",
       "      <td>traj_fff9552047b095e8242b4913f3289a26_25_7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 id  target\n",
       "202899  traj_ffe98f6e0adf12f9c7b51c4e9607a87a_15_13     0.0\n",
       "202913   traj_fff607ecd3f8d3dcb65791e8b4c22a5f_3_25     1.0\n",
       "202914   traj_fff813b56230c2f026f783f5b9f9ca90_19_0     0.0\n",
       "202929  traj_fff9400843a88c3bfe52e7ce8bf97316_19_17     0.0\n",
       "202936   traj_fff9552047b095e8242b4913f3289a26_25_7     0.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test_1st_traj_only = df_test[df_test['x_exit'].isnull()]\n",
    "df_submit = df_test_1st_traj_only[['trajectory_id']].copy()\n",
    "df_submit = df_submit.rename(columns = {'trajectory_id':'id'})\n",
    "\n",
    "# helper function to determine if point is inside\n",
    "def is_inside(arr_x, arr_y):\n",
    "    return ((arr_x > x_min) & \n",
    "            (arr_x < x_max) & \n",
    "            (arr_y > y_min) & \n",
    "            (arr_y < y_max)).astype(float)\n",
    "\n",
    "df_submit['target'] = is_inside(df_test_1st_traj_only['x_entry'],\n",
    "                                df_test_1st_traj_only['y_entry'])\n",
    "df_submit.to_csv('submission.csv', index=False)\n",
    "df_submit.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DATASET PIVOTING"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"40\" halign=\"left\">t_entry</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"40\" halign=\"left\">tid_1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tid_0</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "      <th>15</th>\n",
       "      <th>16</th>\n",
       "      <th>17</th>\n",
       "      <th>18</th>\n",
       "      <th>19</th>\n",
       "      <th>20</th>\n",
       "      <th>21</th>\n",
       "      <th>22</th>\n",
       "      <th>23</th>\n",
       "      <th>24</th>\n",
       "      <th>25</th>\n",
       "      <th>26</th>\n",
       "      <th>27</th>\n",
       "      <th>28</th>\n",
       "      <th>29</th>\n",
       "      <th>30</th>\n",
       "      <th>31</th>\n",
       "      <th>32</th>\n",
       "      <th>33</th>\n",
       "      <th>34</th>\n",
       "      <th>35</th>\n",
       "      <th>36</th>\n",
       "      <th>37</th>\n",
       "      <th>38</th>\n",
       "      <th>39</th>\n",
       "      <th>...</th>\n",
       "      <th>25</th>\n",
       "      <th>26</th>\n",
       "      <th>27</th>\n",
       "      <th>28</th>\n",
       "      <th>29</th>\n",
       "      <th>30</th>\n",
       "      <th>31</th>\n",
       "      <th>32</th>\n",
       "      <th>33</th>\n",
       "      <th>34</th>\n",
       "      <th>35</th>\n",
       "      <th>36</th>\n",
       "      <th>37</th>\n",
       "      <th>38</th>\n",
       "      <th>39</th>\n",
       "      <th>40</th>\n",
       "      <th>41</th>\n",
       "      <th>42</th>\n",
       "      <th>43</th>\n",
       "      <th>44</th>\n",
       "      <th>45</th>\n",
       "      <th>46</th>\n",
       "      <th>47</th>\n",
       "      <th>48</th>\n",
       "      <th>49</th>\n",
       "      <th>50</th>\n",
       "      <th>51</th>\n",
       "      <th>52</th>\n",
       "      <th>53</th>\n",
       "      <th>54</th>\n",
       "      <th>55</th>\n",
       "      <th>56</th>\n",
       "      <th>58</th>\n",
       "      <th>59</th>\n",
       "      <th>60</th>\n",
       "      <th>61</th>\n",
       "      <th>62</th>\n",
       "      <th>64</th>\n",
       "      <th>65</th>\n",
       "      <th>66</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hash</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></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></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></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></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></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>fffd57d840cb6c16553cc8efc907ef6d_29</th>\n",
       "      <td>-0.322222</td>\n",
       "      <td>-0.304667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.243778</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.094722</td>\n",
       "      <td>0.020083</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fffe0c49b72b5d066949457ea383a77e_3</th>\n",
       "      <td>-0.700111</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.651556</td>\n",
       "      <td>0.002611</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fffe926c252d7dfc79dec28c3a7ba0bb_3</th>\n",
       "      <td>-0.945639</td>\n",
       "      <td>-0.905194</td>\n",
       "      <td>-0.712694</td>\n",
       "      <td>-0.661806</td>\n",
       "      <td>-0.109528</td>\n",
       "      <td>0.053472</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ffff74ab47d4fa20695231ce8a293c98_1</th>\n",
       "      <td>-0.474583</td>\n",
       "      <td>-0.294972</td>\n",
       "      <td>-0.274083</td>\n",
       "      <td>0.013861</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ffffc6359725f0e1feac9ef1872ab207_11</th>\n",
       "      <td>-1.474278</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.296194</td>\n",
       "      <td>-1.264694</td>\n",
       "      <td>-0.896194</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.512972</td>\n",
       "      <td>-0.065944</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.005389</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      t_entry           ... tid_1    \n",
       "tid_0                                      0         1  ...    65  66\n",
       "hash                                                    ...          \n",
       "fffd57d840cb6c16553cc8efc907ef6d_29 -0.322222 -0.304667 ...   NaN NaN\n",
       "fffe0c49b72b5d066949457ea383a77e_3  -0.700111       NaN ...   NaN NaN\n",
       "fffe926c252d7dfc79dec28c3a7ba0bb_3  -0.945639 -0.905194 ...   NaN NaN\n",
       "ffff74ab47d4fa20695231ce8a293c98_1  -0.474583 -0.294972 ...   NaN NaN\n",
       "ffffc6359725f0e1feac9ef1872ab207_11 -1.474278       NaN ...   NaN NaN\n",
       "\n",
       "[5 rows x 845 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "p_train = df_train.pivot('hash', 'tid_0')\n",
    "p_train.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"40\" halign=\"left\">t_entry</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"40\" halign=\"left\">tid_1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tid_0</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "      <th>15</th>\n",
       "      <th>16</th>\n",
       "      <th>17</th>\n",
       "      <th>18</th>\n",
       "      <th>19</th>\n",
       "      <th>20</th>\n",
       "      <th>21</th>\n",
       "      <th>22</th>\n",
       "      <th>23</th>\n",
       "      <th>24</th>\n",
       "      <th>25</th>\n",
       "      <th>26</th>\n",
       "      <th>27</th>\n",
       "      <th>28</th>\n",
       "      <th>29</th>\n",
       "      <th>30</th>\n",
       "      <th>31</th>\n",
       "      <th>32</th>\n",
       "      <th>33</th>\n",
       "      <th>34</th>\n",
       "      <th>35</th>\n",
       "      <th>36</th>\n",
       "      <th>37</th>\n",
       "      <th>38</th>\n",
       "      <th>39</th>\n",
       "      <th>...</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "      <th>15</th>\n",
       "      <th>16</th>\n",
       "      <th>17</th>\n",
       "      <th>18</th>\n",
       "      <th>19</th>\n",
       "      <th>20</th>\n",
       "      <th>21</th>\n",
       "      <th>22</th>\n",
       "      <th>23</th>\n",
       "      <th>24</th>\n",
       "      <th>25</th>\n",
       "      <th>26</th>\n",
       "      <th>27</th>\n",
       "      <th>28</th>\n",
       "      <th>29</th>\n",
       "      <th>30</th>\n",
       "      <th>31</th>\n",
       "      <th>32</th>\n",
       "      <th>33</th>\n",
       "      <th>34</th>\n",
       "      <th>35</th>\n",
       "      <th>36</th>\n",
       "      <th>37</th>\n",
       "      <th>38</th>\n",
       "      <th>39</th>\n",
       "      <th>40</th>\n",
       "      <th>41</th>\n",
       "      <th>42</th>\n",
       "      <th>43</th>\n",
       "      <th>44</th>\n",
       "      <th>45</th>\n",
       "      <th>46</th>\n",
       "      <th>47</th>\n",
       "      <th>48</th>\n",
       "      <th>49</th>\n",
       "      <th>50</th>\n",
       "      <th>51</th>\n",
       "      <th>54</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hash</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></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></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></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></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></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>ffe98f6e0adf12f9c7b51c4e9607a87a_15</th>\n",
       "      <td>-1.365694</td>\n",
       "      <td>-1.269750</td>\n",
       "      <td>-1.219111</td>\n",
       "      <td>-1.120028</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.652972</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.506472</td>\n",
       "      <td>-0.447389</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.095722</td>\n",
       "      <td>-0.000444</td>\n",
       "      <td>0.061639</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>15.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fff607ecd3f8d3dcb65791e8b4c22a5f_3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.414667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.317861</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.048056</td>\n",
       "      <td>-1.023556</td>\n",
       "      <td>-1.005722</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.980222</td>\n",
       "      <td>-0.949167</td>\n",
       "      <td>-0.923944</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.724139</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.683139</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.347278</td>\n",
       "      <td>-0.322722</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.041139</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.008472</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fff813b56230c2f026f783f5b9f9ca90_19</th>\n",
       "      <td>0.013389</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fff9400843a88c3bfe52e7ce8bf97316_19</th>\n",
       "      <td>-1.098333</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.932833</td>\n",
       "      <td>-0.899167</td>\n",
       "      <td>-0.784583</td>\n",
       "      <td>-0.619917</td>\n",
       "      <td>-0.539389</td>\n",
       "      <td>-0.517889</td>\n",
       "      <td>-0.498500</td>\n",
       "      <td>-0.438667</td>\n",
       "      <td>-0.409528</td>\n",
       "      <td>-0.377250</td>\n",
       "      <td>-0.317889</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.098722</td>\n",
       "      <td>-0.048556</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.022861</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fff9552047b095e8242b4913f3289a26_25</th>\n",
       "      <td>-0.702889</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.395750</td>\n",
       "      <td>-0.360750</td>\n",
       "      <td>-0.279722</td>\n",
       "      <td>-0.186278</td>\n",
       "      <td>-0.075667</td>\n",
       "      <td>0.051500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      t_entry           ... tid_1    \n",
       "tid_0                                      0         1  ...    51  54\n",
       "hash                                                    ...          \n",
       "ffe98f6e0adf12f9c7b51c4e9607a87a_15 -1.365694 -1.269750 ...   NaN NaN\n",
       "fff607ecd3f8d3dcb65791e8b4c22a5f_3        NaN -1.414667 ...   NaN NaN\n",
       "fff813b56230c2f026f783f5b9f9ca90_19  0.013389       NaN ...   NaN NaN\n",
       "fff9400843a88c3bfe52e7ce8bf97316_19 -1.098333       NaN ...   NaN NaN\n",
       "fff9552047b095e8242b4913f3289a26_25 -0.702889       NaN ...   NaN NaN\n",
       "\n",
       "[5 rows x 689 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "p_test = df_test.pivot('hash', 'tid_0')\n",
    "p_test.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(21, 11)\n",
      "(-1.6122742073539644, -0.29982424471039326)\n"
     ]
    },
    {
     "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>t_entry</th>\n",
       "      <th>t_exit</th>\n",
       "      <th>x_entry</th>\n",
       "      <th>y_entry</th>\n",
       "      <th>x_exit</th>\n",
       "      <th>y_exit</th>\n",
       "      <th>vmax</th>\n",
       "      <th>vmin</th>\n",
       "      <th>vmean</th>\n",
       "      <th>tid_0</th>\n",
       "      <th>tid_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.028083</td>\n",
       "      <td>0.028083</td>\n",
       "      <td>-1.612274</td>\n",
       "      <td>-0.299824</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-0.152417</td>\n",
       "      <td>-0.152417</td>\n",
       "      <td>-1.612274</td>\n",
       "      <td>-0.299824</td>\n",
       "      <td>-1.612274</td>\n",
       "      <td>-0.299824</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-0.298111</td>\n",
       "      <td>-0.298111</td>\n",
       "      <td>-1.612274</td>\n",
       "      <td>-0.299824</td>\n",
       "      <td>-1.612274</td>\n",
       "      <td>-0.299824</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-0.430444</td>\n",
       "      <td>-0.430444</td>\n",
       "      <td>-1.612274</td>\n",
       "      <td>-0.299824</td>\n",
       "      <td>-1.612274</td>\n",
       "      <td>-0.299824</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-0.501750</td>\n",
       "      <td>-0.501750</td>\n",
       "      <td>-1.612274</td>\n",
       "      <td>-0.299824</td>\n",
       "      <td>-1.612274</td>\n",
       "      <td>-0.299824</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>-0.807222</td>\n",
       "      <td>-0.807222</td>\n",
       "      <td>-1.612274</td>\n",
       "      <td>-0.299824</td>\n",
       "      <td>-1.612274</td>\n",
       "      <td>-0.299824</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>-0.891333</td>\n",
       "      <td>-0.891333</td>\n",
       "      <td>-1.856843</td>\n",
       "      <td>-0.489747</td>\n",
       "      <td>-1.856843</td>\n",
       "      <td>-0.489747</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>-0.908806</td>\n",
       "      <td>-0.908806</td>\n",
       "      <td>-1.619621</td>\n",
       "      <td>-0.572908</td>\n",
       "      <td>-1.619621</td>\n",
       "      <td>-0.572908</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>-0.944778</td>\n",
       "      <td>-0.944778</td>\n",
       "      <td>-1.596022</td>\n",
       "      <td>-0.546938</td>\n",
       "      <td>-1.596022</td>\n",
       "      <td>-0.546938</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>-0.979806</td>\n",
       "      <td>-0.979806</td>\n",
       "      <td>-1.605150</td>\n",
       "      <td>-0.574633</td>\n",
       "      <td>-1.605150</td>\n",
       "      <td>-0.574633</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>-0.997194</td>\n",
       "      <td>-0.997194</td>\n",
       "      <td>-1.603814</td>\n",
       "      <td>-0.582909</td>\n",
       "      <td>-1.603814</td>\n",
       "      <td>-0.582909</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>-1.030556</td>\n",
       "      <td>-1.014028</td>\n",
       "      <td>-1.624074</td>\n",
       "      <td>-0.317108</td>\n",
       "      <td>-1.621514</td>\n",
       "      <td>-0.309095</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>-1.098333</td>\n",
       "      <td>-1.098333</td>\n",
       "      <td>-1.630308</td>\n",
       "      <td>-0.254290</td>\n",
       "      <td>-1.630308</td>\n",
       "      <td>-0.254290</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>-1.141639</td>\n",
       "      <td>-1.141639</td>\n",
       "      <td>-1.783372</td>\n",
       "      <td>-0.415293</td>\n",
       "      <td>-1.783372</td>\n",
       "      <td>-0.415293</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>-1.353278</td>\n",
       "      <td>-1.353278</td>\n",
       "      <td>-1.868643</td>\n",
       "      <td>-0.681041</td>\n",
       "      <td>-1.868643</td>\n",
       "      <td>-0.681041</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     t_entry    t_exit   x_entry   y_entry  ...    vmin  vmean  tid_0  tid_1\n",
       "0   0.028083  0.028083 -1.612274 -0.299824  ...     NaN    NaN   17.0   19.0\n",
       "1  -0.152417 -0.152417 -1.612274 -0.299824  ...     NaN    NaN   16.0   19.0\n",
       "2  -0.298111 -0.298111 -1.612274 -0.299824  ...     NaN    NaN   15.0   19.0\n",
       "3  -0.430444 -0.430444 -1.612274 -0.299824  ...     NaN    NaN   14.0   19.0\n",
       "4  -0.501750 -0.501750 -1.612274 -0.299824  ...     NaN    NaN   13.0   19.0\n",
       "5  -0.807222 -0.807222 -1.612274 -0.299824  ...     NaN    NaN   11.0   19.0\n",
       "6  -0.891333 -0.891333 -1.856843 -0.489747  ...     NaN    NaN   10.0   19.0\n",
       "7  -0.908806 -0.908806 -1.619621 -0.572908  ...     NaN    NaN    9.0   19.0\n",
       "8  -0.944778 -0.944778 -1.596022 -0.546938  ...     NaN    NaN    8.0   19.0\n",
       "9  -0.979806 -0.979806 -1.605150 -0.574633  ...     NaN    NaN    7.0   19.0\n",
       "10 -0.997194 -0.997194 -1.603814 -0.582909  ...     NaN    NaN    6.0   19.0\n",
       "11 -1.030556 -1.014028 -1.624074 -0.317108  ...     NaN    NaN    5.0   19.0\n",
       "12 -1.098333 -1.098333 -1.630308 -0.254290  ...     NaN    NaN    3.0   19.0\n",
       "13 -1.141639 -1.141639 -1.783372 -0.415293  ...     NaN    NaN    1.0   19.0\n",
       "14 -1.353278 -1.353278 -1.868643 -0.681041  ...     NaN    NaN    0.0   19.0\n",
       "99       NaN       NaN       NaN       NaN  ...     NaN    NaN    NaN    NaN\n",
       "99       NaN       NaN       NaN       NaN  ...     NaN    NaN    NaN    NaN\n",
       "99       NaN       NaN       NaN       NaN  ...     NaN    NaN    NaN    NaN\n",
       "99       NaN       NaN       NaN       NaN  ...     NaN    NaN    NaN    NaN\n",
       "99       NaN       NaN       NaN       NaN  ...     NaN    NaN    NaN    NaN\n",
       "99       NaN       NaN       NaN       NaN  ...     NaN    NaN    NaN    NaN\n",
       "\n",
       "[21 rows x 11 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def obtain_matrix(row):\n",
    "    df_hash = row.stack().iloc[::-1].reset_index()\n",
    "    trajectory_id = df_hash.loc[0,\"trajectory_id\"]\n",
    "    df_hash = df_hash[['t_entry','t_exit',\n",
    "                       'x_entry','y_entry','x_exit','y_exit',\n",
    "                       'vmax','vmin','vmean','tid_0','tid_1']]\n",
    "    targets = df_hash.loc[0,\"x_exit\"], df_hash.loc[0,\"y_exit\"]\n",
    "\n",
    "    df_hash.loc[0,\"x_exit\"] = np.nan\n",
    "    df_hash.loc[0,\"y_exit\"] = np.nan\n",
    "    embeds = np.transpose(df_hash.values)\n",
    "    df_hash = df_hash.append(pd.DataFrame([[np.nan]*df_hash.shape[1]], \n",
    "                                            columns=list(df_hash),\n",
    "                                            index=[99]*(21-df_hash.shape[0])))\n",
    "    return {\"targets\" : targets, \n",
    "            \"df_hash\" : df_hash,\n",
    "            \"matrix\" : df_hash.values,\n",
    "            \"trajectory_id\" : trajectory_id}\n",
    "\n",
    "print(np.shape(obtain_matrix(p_train.iloc[[323]])[\"matrix\"]))\n",
    "print(obtain_matrix(p_train.iloc[[323]])[\"targets\"])\n",
    "obtain_matrix(p_train.iloc[[323]])[\"df_hash\"]\n",
    "# note that x_exit and y_exit is removed from matrix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6f132e660234409eb0bb99836bf9a9c2",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=33515), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "test_data = []\n",
    "test_ids = []\n",
    "\n",
    "for i in tqdm(range(p_test.shape[0])):\n",
    "    output = obtain_matrix(p_test.iloc[[i]])\n",
    "    test_data.append(output[\"matrix\"])\n",
    "    test_ids.append(output[\"trajectory_id\"])\n",
    "#     if i>100:\n",
    "#         break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(33515, 21, 11)\n",
      "(33515,)\n"
     ]
    }
   ],
   "source": [
    "print(np.shape(test_data))\n",
    "print(np.shape(test_ids))\n",
    "np.save(\"test_data\", test_data)\n",
    "np.save(\"test_ids\", test_ids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "468a3582a6c24312856685107cb2949e",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=134063), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "train_data = []\n",
    "train_targets = []\n",
    "\n",
    "for i in tqdm(range(p_train.shape[0])):\n",
    "    output = obtain_matrix(p_train.iloc[[i]])\n",
    "    train_data.append(output[\"matrix\"])\n",
    "    train_targets.append(output[\"targets\"])\n",
    "#     if i>100:\n",
    "#         break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# evaluate if the targets are inside\n",
    "train_targets = np.array(train_targets)\n",
    "train_targets_inside = is_inside(train_targets[:,0], train_targets[:,1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(134063, 21, 11)\n",
      "(134063, 2)\n",
      "(134063,)\n"
     ]
    }
   ],
   "source": [
    "print(np.shape(train_data))\n",
    "print(np.shape(train_targets))\n",
    "print(np.shape(train_targets_inside))\n",
    "np.save(\"train_data\", train_data)\n",
    "np.save(\"train_targets\", train_targets)\n",
    "np.save(\"train_targets_inside\", train_targets_inside)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# TRAIN-TEST SPLIT INDICES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# standardised 4-fold train-test split for clustering purposes\n",
    "from sklearn.model_selection import StratifiedKFold, KFold\n",
    "skf = StratifiedKFold(n_splits=4, shuffle=True, random_state=42)\n",
    "trn_index_list = []\n",
    "val_index_list = []\n",
    "for trn_index, val_index in skf.split(np.arange(len(train_data)),\n",
    "                                      train_targets_inside.astype(int)):\n",
    "    trn_index_list.append(trn_index)\n",
    "    val_index_list.append(val_index)\n",
    "    \n",
    "np.save(\"trn_index_list\",trn_index_list)\n",
    "np.save(\"val_index_list\",val_index_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "__notebook__.ipynb  test_ids.npy\t      trn_index_list.npy\r\n",
      "__output__.json     train_data.npy\t      val_index_list.npy\r\n",
      "submission.csv\t    train_targets.npy\r\n",
      "test_data.npy\t    train_targets_inside.npy\r\n"
     ]
    }
   ],
   "source": [
    "!ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# to document: specifications for all if not clear enough\n",
    "# might not care to do: make the pivot table to 3D array faster"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}