processing_CutTimeSlice_EventId.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e3871dd7",
   "metadata": {},
   "source": [
    "#### Note: 20221212\n",
    "- sensor會重複寫入無法校正\n",
    "    - 直接使用\n",
    "\n",
    "- 篩除掉sensor NaN、mismatch後的資料直接使用"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c14db932",
   "metadata": {},
   "source": [
    "\n",
    "## Filtering potentially high-risk data\n",
    "- Based on Andy's preprocessed data\n",
    "    - \"data/preprocessed_Andy/..._Target.csv\"\n",
    "    - preprocessed pipeline base on `multi.py`\n",
    "#### Criteria\n",
    "\n",
    "- Threshold\n",
    "    - Speed > 3.0 m/s (10.8 hm/h)\n",
    "    - gsensor > 0.15g (0.15g = 1.5 m/s^2, 0.3g = 2.94 m/s^2)\n",
    "- Alignment\n",
    "    - Ignore timestamps < 10\n",
    "    - Ignore timestamp mismatches (between gsensor and gyroscope)\n",
    "\n",
    "#### Time slice cut out from sensor data (sharp changes)\n",
    "- Establish `event_id` fieled"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5a4e4bec-72f9-4121-b0f4-76570a2fff8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import json\n",
    "from pathlib import Path\n",
    "from datetime import timedelta\n",
    "from multiprocessing import Pool\n",
    "# import feather\n",
    "\n",
    "dir_data = Path('data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "fa913e48",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total 1514640\n",
      "-rwxrwxrwx 1 yunghui yunghui 1550988456 Nov 18 10:45 cars.7z\n",
      "drwxrwxrwx 1 yunghui yunghui        512 Dec  9 11:13 preprocessed_Andy\n",
      "drwxrwxrwx 1 yunghui yunghui        512 Dec  8 16:56 preprocessed_CH\n",
      "total 685M\n",
      "-rwxrwxrwx 1 yunghui yunghui 6.3M Dec  7 11:35 HR55AD8921_Target.csv\n",
      "-rwxrwxrwx 1 yunghui yunghui  37M Dec  7 11:35 KA01AF2992_Target.csv\n",
      "-rwxrwxrwx 1 yunghui yunghui 148M Dec  7 11:35 KA01AF482_Target.csv\n",
      "-rwxrwxrwx 1 yunghui yunghui  50M Dec  7 11:35 KA04MW6569_Target.csv\n",
      "-rwxrwxrwx 1 yunghui yunghui 242M Dec  7 11:35 KA51AG8742_Target.csv\n",
      "-rwxrwxrwx 1 yunghui yunghui  86M Dec  7 11:35 KA51AG8752_Target.csv\n",
      "-rwxrwxrwx 1 yunghui yunghui 8.2M Dec  7 11:35 MH01DE9816_Target.csv\n",
      "-rwxrwxrwx 1 yunghui yunghui  33M Dec  7 11:35 MH04JK4981_Target.csv\n",
      "-rwxrwxrwx 1 yunghui yunghui 380K Dec  7 11:35 MH04JK8738_Target.csv\n",
      "-rwxrwxrwx 1 yunghui yunghui  77M Dec  7 11:35 MH04KF8187_Target.csv\n"
     ]
    }
   ],
   "source": [
    "!ls -l 'data/'\n",
    "!ls -lh 'data/preprocessed_Andy'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3fd085d-3f8e-4a34-ab99-dc4b10d46368",
   "metadata": {},
   "source": [
    "# Data Loading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "7181463c-0642-4ea5-ae78-d8c5b2403547",
   "metadata": {},
   "outputs": [],
   "source": [
    "filename = 'KA04MW6569'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "1fa12de5-6498-43f9-aa1b-2ab5384590ae",
   "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>vehicle_id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>speed</th>\n",
       "      <th>heading</th>\n",
       "      <th>acc_signal</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>gsensor</th>\n",
       "      <th>gyro</th>\n",
       "      <th>...</th>\n",
       "      <th>gsen_X</th>\n",
       "      <th>gsen_Y</th>\n",
       "      <th>gsen_Z</th>\n",
       "      <th>subid</th>\n",
       "      <th>gyro_X</th>\n",
       "      <th>gyro_Y</th>\n",
       "      <th>gyro_Z</th>\n",
       "      <th>unsafe_gsenX</th>\n",
       "      <th>unsafe_gsenY</th>\n",
       "      <th>unsafe_gsenZ</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14404</th>\n",
       "      <td>14533700</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030617</td>\n",
       "      <td>77.516181</td>\n",
       "      <td>8.261585</td>\n",
       "      <td>104.002220</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160496000</td>\n",
       "      <td>[{\"timeTick\":3934154,\"X\":0.34491777420043945,\"...</td>\n",
       "      <td>[{\"timeTick\":3934154,\"X\":-0.0347137451171875,\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.344918</td>\n",
       "      <td>-0.487084</td>\n",
       "      <td>0.095874</td>\n",
       "      <td>14533700.1</td>\n",
       "      <td>-0.034714</td>\n",
       "      <td>0.005981</td>\n",
       "      <td>-0.011765</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14405</th>\n",
       "      <td>14533700</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030617</td>\n",
       "      <td>77.516181</td>\n",
       "      <td>8.261585</td>\n",
       "      <td>104.002220</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160496000</td>\n",
       "      <td>[{\"timeTick\":3934154,\"X\":0.34491777420043945,\"...</td>\n",
       "      <td>[{\"timeTick\":3934154,\"X\":-0.0347137451171875,\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.314513</td>\n",
       "      <td>-0.392850</td>\n",
       "      <td>-0.181710</td>\n",
       "      <td>14533700.2</td>\n",
       "      <td>-0.027267</td>\n",
       "      <td>-0.061874</td>\n",
       "      <td>-0.034241</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14406</th>\n",
       "      <td>14533700</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030617</td>\n",
       "      <td>77.516181</td>\n",
       "      <td>8.261585</td>\n",
       "      <td>104.002220</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160496000</td>\n",
       "      <td>[{\"timeTick\":3934154,\"X\":0.34491777420043945,\"...</td>\n",
       "      <td>[{\"timeTick\":3934154,\"X\":-0.0347137451171875,\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.536195</td>\n",
       "      <td>-0.042270</td>\n",
       "      <td>-0.034724</td>\n",
       "      <td>14533700.3</td>\n",
       "      <td>-0.009262</td>\n",
       "      <td>-0.004028</td>\n",
       "      <td>-0.017731</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14407</th>\n",
       "      <td>14533700</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030617</td>\n",
       "      <td>77.516181</td>\n",
       "      <td>8.261585</td>\n",
       "      <td>104.002220</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160496000</td>\n",
       "      <td>[{\"timeTick\":3934154,\"X\":0.34491777420043945,\"...</td>\n",
       "      <td>[{\"timeTick\":3934154,\"X\":-0.0347137451171875,\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.044947</td>\n",
       "      <td>-0.010064</td>\n",
       "      <td>-0.224942</td>\n",
       "      <td>14533700.4</td>\n",
       "      <td>-0.024384</td>\n",
       "      <td>-0.020645</td>\n",
       "      <td>-0.018356</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14408</th>\n",
       "      <td>14533700</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030617</td>\n",
       "      <td>77.516181</td>\n",
       "      <td>8.261585</td>\n",
       "      <td>104.002220</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160496000</td>\n",
       "      <td>[{\"timeTick\":3934154,\"X\":0.34491777420043945,\"...</td>\n",
       "      <td>[{\"timeTick\":3934154,\"X\":-0.0347137451171875,\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.168777</td>\n",
       "      <td>0.152842</td>\n",
       "      <td>-0.232790</td>\n",
       "      <td>14533700.5</td>\n",
       "      <td>-0.012451</td>\n",
       "      <td>-0.012115</td>\n",
       "      <td>-0.018677</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14409</th>\n",
       "      <td>14533704</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030598</td>\n",
       "      <td>77.516253</td>\n",
       "      <td>7.508620</td>\n",
       "      <td>102.957184</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160497000</td>\n",
       "      <td>[{\"timeTick\":3935154,\"X\":0.34683752059936523,\"...</td>\n",
       "      <td>[{\"timeTick\":3935154,\"X\":-0.0047760009765625,\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.346838</td>\n",
       "      <td>0.116714</td>\n",
       "      <td>-0.192143</td>\n",
       "      <td>14533704.1</td>\n",
       "      <td>-0.004776</td>\n",
       "      <td>-0.052170</td>\n",
       "      <td>-0.011871</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14410</th>\n",
       "      <td>14533704</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030598</td>\n",
       "      <td>77.516253</td>\n",
       "      <td>7.508620</td>\n",
       "      <td>102.957184</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160497000</td>\n",
       "      <td>[{\"timeTick\":3935154,\"X\":0.34683752059936523,\"...</td>\n",
       "      <td>[{\"timeTick\":3935154,\"X\":-0.0047760009765625,\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.134666</td>\n",
       "      <td>0.173285</td>\n",
       "      <td>-0.045174</td>\n",
       "      <td>14533704.2</td>\n",
       "      <td>0.010986</td>\n",
       "      <td>-0.020004</td>\n",
       "      <td>-0.000153</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14411</th>\n",
       "      <td>14533704</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030598</td>\n",
       "      <td>77.516253</td>\n",
       "      <td>7.508620</td>\n",
       "      <td>102.957184</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160497000</td>\n",
       "      <td>[{\"timeTick\":3935154,\"X\":0.34683752059936523,\"...</td>\n",
       "      <td>[{\"timeTick\":3935154,\"X\":-0.0047760009765625,\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.213028</td>\n",
       "      <td>-0.058266</td>\n",
       "      <td>0.085586</td>\n",
       "      <td>14533704.3</td>\n",
       "      <td>0.000229</td>\n",
       "      <td>-0.010529</td>\n",
       "      <td>0.013809</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14412</th>\n",
       "      <td>14533704</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030598</td>\n",
       "      <td>77.516253</td>\n",
       "      <td>7.508620</td>\n",
       "      <td>102.957184</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160497000</td>\n",
       "      <td>[{\"timeTick\":3935154,\"X\":0.34683752059936523,\"...</td>\n",
       "      <td>[{\"timeTick\":3935154,\"X\":-0.0047760009765625,\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.237432</td>\n",
       "      <td>-0.398034</td>\n",
       "      <td>0.042169</td>\n",
       "      <td>14533704.4</td>\n",
       "      <td>-0.033127</td>\n",
       "      <td>-0.003601</td>\n",
       "      <td>0.000061</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14413</th>\n",
       "      <td>14533704</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030598</td>\n",
       "      <td>77.516253</td>\n",
       "      <td>7.508620</td>\n",
       "      <td>102.957184</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160497000</td>\n",
       "      <td>[{\"timeTick\":3935154,\"X\":0.34683752059936523,\"...</td>\n",
       "      <td>[{\"timeTick\":3935154,\"X\":-0.0047760009765625,\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.102970</td>\n",
       "      <td>-0.212659</td>\n",
       "      <td>0.037861</td>\n",
       "      <td>14533704.5</td>\n",
       "      <td>-0.006271</td>\n",
       "      <td>0.029205</td>\n",
       "      <td>-0.011765</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             id  vehicle_id   latitude  longitude     speed     heading  \\\n",
       "14404  14533700  KA04MW6569  13.030617  77.516181  8.261585  104.002220   \n",
       "14405  14533700  KA04MW6569  13.030617  77.516181  8.261585  104.002220   \n",
       "14406  14533700  KA04MW6569  13.030617  77.516181  8.261585  104.002220   \n",
       "14407  14533700  KA04MW6569  13.030617  77.516181  8.261585  104.002220   \n",
       "14408  14533700  KA04MW6569  13.030617  77.516181  8.261585  104.002220   \n",
       "14409  14533704  KA04MW6569  13.030598  77.516253  7.508620  102.957184   \n",
       "14410  14533704  KA04MW6569  13.030598  77.516253  7.508620  102.957184   \n",
       "14411  14533704  KA04MW6569  13.030598  77.516253  7.508620  102.957184   \n",
       "14412  14533704  KA04MW6569  13.030598  77.516253  7.508620  102.957184   \n",
       "14413  14533704  KA04MW6569  13.030598  77.516253  7.508620  102.957184   \n",
       "\n",
       "       acc_signal      timestamp  \\\n",
       "14404           1  1651160496000   \n",
       "14405           1  1651160496000   \n",
       "14406           1  1651160496000   \n",
       "14407           1  1651160496000   \n",
       "14408           1  1651160496000   \n",
       "14409           1  1651160497000   \n",
       "14410           1  1651160497000   \n",
       "14411           1  1651160497000   \n",
       "14412           1  1651160497000   \n",
       "14413           1  1651160497000   \n",
       "\n",
       "                                                 gsensor  \\\n",
       "14404  [{\"timeTick\":3934154,\"X\":0.34491777420043945,\"...   \n",
       "14405  [{\"timeTick\":3934154,\"X\":0.34491777420043945,\"...   \n",
       "14406  [{\"timeTick\":3934154,\"X\":0.34491777420043945,\"...   \n",
       "14407  [{\"timeTick\":3934154,\"X\":0.34491777420043945,\"...   \n",
       "14408  [{\"timeTick\":3934154,\"X\":0.34491777420043945,\"...   \n",
       "14409  [{\"timeTick\":3935154,\"X\":0.34683752059936523,\"...   \n",
       "14410  [{\"timeTick\":3935154,\"X\":0.34683752059936523,\"...   \n",
       "14411  [{\"timeTick\":3935154,\"X\":0.34683752059936523,\"...   \n",
       "14412  [{\"timeTick\":3935154,\"X\":0.34683752059936523,\"...   \n",
       "14413  [{\"timeTick\":3935154,\"X\":0.34683752059936523,\"...   \n",
       "\n",
       "                                                    gyro  ...    gsen_X  \\\n",
       "14404  [{\"timeTick\":3934154,\"X\":-0.0347137451171875,\"...  ...  0.344918   \n",
       "14405  [{\"timeTick\":3934154,\"X\":-0.0347137451171875,\"...  ...  0.314513   \n",
       "14406  [{\"timeTick\":3934154,\"X\":-0.0347137451171875,\"...  ...  0.536195   \n",
       "14407  [{\"timeTick\":3934154,\"X\":-0.0347137451171875,\"...  ...  0.044947   \n",
       "14408  [{\"timeTick\":3934154,\"X\":-0.0347137451171875,\"...  ...  0.168777   \n",
       "14409  [{\"timeTick\":3935154,\"X\":-0.0047760009765625,\"...  ...  0.346838   \n",
       "14410  [{\"timeTick\":3935154,\"X\":-0.0047760009765625,\"...  ...  0.134666   \n",
       "14411  [{\"timeTick\":3935154,\"X\":-0.0047760009765625,\"...  ... -0.213028   \n",
       "14412  [{\"timeTick\":3935154,\"X\":-0.0047760009765625,\"...  ... -0.237432   \n",
       "14413  [{\"timeTick\":3935154,\"X\":-0.0047760009765625,\"...  ...  0.102970   \n",
       "\n",
       "         gsen_Y    gsen_Z       subid    gyro_X    gyro_Y    gyro_Z  \\\n",
       "14404 -0.487084  0.095874  14533700.1 -0.034714  0.005981 -0.011765   \n",
       "14405 -0.392850 -0.181710  14533700.2 -0.027267 -0.061874 -0.034241   \n",
       "14406 -0.042270 -0.034724  14533700.3 -0.009262 -0.004028 -0.017731   \n",
       "14407 -0.010064 -0.224942  14533700.4 -0.024384 -0.020645 -0.018356   \n",
       "14408  0.152842 -0.232790  14533700.5 -0.012451 -0.012115 -0.018677   \n",
       "14409  0.116714 -0.192143  14533704.1 -0.004776 -0.052170 -0.011871   \n",
       "14410  0.173285 -0.045174  14533704.2  0.010986 -0.020004 -0.000153   \n",
       "14411 -0.058266  0.085586  14533704.3  0.000229 -0.010529  0.013809   \n",
       "14412 -0.398034  0.042169  14533704.4 -0.033127 -0.003601  0.000061   \n",
       "14413 -0.212659  0.037861  14533704.5 -0.006271  0.029205 -0.011765   \n",
       "\n",
       "       unsafe_gsenX  unsafe_gsenY  unsafe_gsenZ  \n",
       "14404         False         False         False  \n",
       "14405         False         False         False  \n",
       "14406         False         False         False  \n",
       "14407         False         False         False  \n",
       "14408         False         False         False  \n",
       "14409         False         False         False  \n",
       "14410         False         False         False  \n",
       "14411         False         False         False  \n",
       "14412         False         False         False  \n",
       "14413         False         False         False  \n",
       "\n",
       "[10 rows x 21 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df = feather.read_dataframe(f'{filename}.feather')\n",
    "df = pd.read_csv(dir_data/'preprocessed_Andy'/f'{filename}_Target.csv', index_col=0)\n",
    "df['timestamp_conv'] = df['timestamp_conv'].astype('datetime64[ms]')\n",
    "# df = pd.concat(map(pd.read_csv, filename))\n",
    "df[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "583fdd20",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['id', 'vehicle_id', 'latitude', 'longitude', 'speed', 'heading',\n",
      "       'acc_signal', 'timestamp', 'gsensor', 'gyro', 'timestamp_conv',\n",
      "       'gsen_X', 'gsen_Y', 'gsen_Z', 'subid', 'gyro_X', 'gyro_Y', 'gyro_Z',\n",
      "       'unsafe_gsenX', 'unsafe_gsenY', 'unsafe_gsenZ'],\n",
      "      dtype='object')\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 40473 entries, 14404 to 4107668\n",
      "Data columns (total 21 columns):\n",
      " #   Column          Non-Null Count  Dtype         \n",
      "---  ------          --------------  -----         \n",
      " 0   id              40473 non-null  int64         \n",
      " 1   vehicle_id      40473 non-null  object        \n",
      " 2   latitude        40473 non-null  float64       \n",
      " 3   longitude       40473 non-null  float64       \n",
      " 4   speed           40473 non-null  float64       \n",
      " 5   heading         40473 non-null  float64       \n",
      " 6   acc_signal      40473 non-null  int64         \n",
      " 7   timestamp       40473 non-null  int64         \n",
      " 8   gsensor         40473 non-null  object        \n",
      " 9   gyro            40473 non-null  object        \n",
      " 10  timestamp_conv  40473 non-null  datetime64[ns]\n",
      " 11  gsen_X          40473 non-null  float64       \n",
      " 12  gsen_Y          40473 non-null  float64       \n",
      " 13  gsen_Z          40473 non-null  float64       \n",
      " 14  subid           40473 non-null  float64       \n",
      " 15  gyro_X          40473 non-null  float64       \n",
      " 16  gyro_Y          40473 non-null  float64       \n",
      " 17  gyro_Z          40473 non-null  float64       \n",
      " 18  unsafe_gsenX    40473 non-null  bool          \n",
      " 19  unsafe_gsenY    40473 non-null  bool          \n",
      " 20  unsafe_gsenZ    40473 non-null  bool          \n",
      "dtypes: bool(3), datetime64[ns](1), float64(11), int64(3), object(3)\n",
      "memory usage: 6.0+ MB\n"
     ]
    }
   ],
   "source": [
    "print(df.columns)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c83ca8ec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>speed</th>\n",
       "      <th>heading</th>\n",
       "      <th>acc_signal</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>gsen_X</th>\n",
       "      <th>gsen_Y</th>\n",
       "      <th>gsen_Z</th>\n",
       "      <th>subid</th>\n",
       "      <th>gyro_X</th>\n",
       "      <th>gyro_Y</th>\n",
       "      <th>gyro_Z</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>4.047300e+04</td>\n",
       "      <td>40473.000000</td>\n",
       "      <td>40473.000000</td>\n",
       "      <td>40473.000000</td>\n",
       "      <td>40473.000000</td>\n",
       "      <td>40473.0</td>\n",
       "      <td>4.047300e+04</td>\n",
       "      <td>40473.000000</td>\n",
       "      <td>40473.000000</td>\n",
       "      <td>40473.000000</td>\n",
       "      <td>4.047300e+04</td>\n",
       "      <td>40473.000000</td>\n",
       "      <td>40473.000000</td>\n",
       "      <td>40473.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>3.409103e+07</td>\n",
       "      <td>13.049680</td>\n",
       "      <td>77.556480</td>\n",
       "      <td>6.534554</td>\n",
       "      <td>183.202849</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.657719e+12</td>\n",
       "      <td>0.016572</td>\n",
       "      <td>-0.044124</td>\n",
       "      <td>-0.015594</td>\n",
       "      <td>3.409103e+07</td>\n",
       "      <td>0.000479</td>\n",
       "      <td>-0.001248</td>\n",
       "      <td>0.000556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1.218819e+07</td>\n",
       "      <td>0.034538</td>\n",
       "      <td>0.133762</td>\n",
       "      <td>4.096620</td>\n",
       "      <td>120.275444</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.739235e+09</td>\n",
       "      <td>0.707582</td>\n",
       "      <td>0.951826</td>\n",
       "      <td>0.687863</td>\n",
       "      <td>1.218819e+07</td>\n",
       "      <td>0.074052</td>\n",
       "      <td>0.059970</td>\n",
       "      <td>0.062181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.453370e+07</td>\n",
       "      <td>12.829170</td>\n",
       "      <td>77.398033</td>\n",
       "      <td>0.000602</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.651160e+12</td>\n",
       "      <td>-4.845965</td>\n",
       "      <td>-5.656498</td>\n",
       "      <td>-4.071292</td>\n",
       "      <td>1.453370e+07</td>\n",
       "      <td>-0.611145</td>\n",
       "      <td>-0.634613</td>\n",
       "      <td>-0.501495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2.347323e+07</td>\n",
       "      <td>13.032835</td>\n",
       "      <td>77.508619</td>\n",
       "      <td>4.000906</td>\n",
       "      <td>74.297699</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.654741e+12</td>\n",
       "      <td>-0.272125</td>\n",
       "      <td>-0.531077</td>\n",
       "      <td>-0.344411</td>\n",
       "      <td>2.347323e+07</td>\n",
       "      <td>-0.016678</td>\n",
       "      <td>-0.018219</td>\n",
       "      <td>-0.017670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>3.271389e+07</td>\n",
       "      <td>13.046274</td>\n",
       "      <td>77.512778</td>\n",
       "      <td>5.837009</td>\n",
       "      <td>175.870682</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.657167e+12</td>\n",
       "      <td>0.000893</td>\n",
       "      <td>0.001315</td>\n",
       "      <td>-0.005700</td>\n",
       "      <td>3.271389e+07</td>\n",
       "      <td>0.000214</td>\n",
       "      <td>-0.000137</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>4.184950e+07</td>\n",
       "      <td>13.061233</td>\n",
       "      <td>77.516388</td>\n",
       "      <td>8.152754</td>\n",
       "      <td>305.065491</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.659911e+12</td>\n",
       "      <td>0.295524</td>\n",
       "      <td>0.551351</td>\n",
       "      <td>0.287445</td>\n",
       "      <td>4.184950e+07</td>\n",
       "      <td>0.018585</td>\n",
       "      <td>0.017731</td>\n",
       "      <td>0.019379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>5.697301e+07</td>\n",
       "      <td>13.198758</td>\n",
       "      <td>78.010177</td>\n",
       "      <td>28.438448</td>\n",
       "      <td>359.981323</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.664799e+12</td>\n",
       "      <td>5.266725</td>\n",
       "      <td>4.348207</td>\n",
       "      <td>6.423663</td>\n",
       "      <td>5.697301e+07</td>\n",
       "      <td>0.622650</td>\n",
       "      <td>0.408310</td>\n",
       "      <td>0.548920</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 id      latitude     longitude         speed       heading  \\\n",
       "count  4.047300e+04  40473.000000  40473.000000  40473.000000  40473.000000   \n",
       "mean   3.409103e+07     13.049680     77.556480      6.534554    183.202849   \n",
       "std    1.218819e+07      0.034538      0.133762      4.096620    120.275444   \n",
       "min    1.453370e+07     12.829170     77.398033      0.000602      0.000002   \n",
       "25%    2.347323e+07     13.032835     77.508619      4.000906     74.297699   \n",
       "50%    3.271389e+07     13.046274     77.512778      5.837009    175.870682   \n",
       "75%    4.184950e+07     13.061233     77.516388      8.152754    305.065491   \n",
       "max    5.697301e+07     13.198758     78.010177     28.438448    359.981323   \n",
       "\n",
       "       acc_signal     timestamp        gsen_X        gsen_Y        gsen_Z  \\\n",
       "count     40473.0  4.047300e+04  40473.000000  40473.000000  40473.000000   \n",
       "mean          1.0  1.657719e+12      0.016572     -0.044124     -0.015594   \n",
       "std           0.0  3.739235e+09      0.707582      0.951826      0.687863   \n",
       "min           1.0  1.651160e+12     -4.845965     -5.656498     -4.071292   \n",
       "25%           1.0  1.654741e+12     -0.272125     -0.531077     -0.344411   \n",
       "50%           1.0  1.657167e+12      0.000893      0.001315     -0.005700   \n",
       "75%           1.0  1.659911e+12      0.295524      0.551351      0.287445   \n",
       "max           1.0  1.664799e+12      5.266725      4.348207      6.423663   \n",
       "\n",
       "              subid        gyro_X        gyro_Y        gyro_Z  \n",
       "count  4.047300e+04  40473.000000  40473.000000  40473.000000  \n",
       "mean   3.409103e+07      0.000479     -0.001248      0.000556  \n",
       "std    1.218819e+07      0.074052      0.059970      0.062181  \n",
       "min    1.453370e+07     -0.611145     -0.634613     -0.501495  \n",
       "25%    2.347323e+07     -0.016678     -0.018219     -0.017670  \n",
       "50%    3.271389e+07      0.000214     -0.000137      0.000000  \n",
       "75%    4.184950e+07      0.018585      0.017731      0.019379  \n",
       "max    5.697301e+07      0.622650      0.408310      0.548920  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()\n",
    "\n",
    "# Not yet filtered "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "id": "a8ca5531",
   "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>vehicle_id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>speed</th>\n",
       "      <th>heading</th>\n",
       "      <th>acc_signal</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>gsensor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14424</th>\n",
       "      <td>14533718</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030544</td>\n",
       "      <td>77.516465</td>\n",
       "      <td>6.214806</td>\n",
       "      <td>103.905037</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160500000</td>\n",
       "      <td>[{\"timeTick\":3938155,\"X\":0.1266622543334961,\"Y...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14425</th>\n",
       "      <td>14533718</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030544</td>\n",
       "      <td>77.516465</td>\n",
       "      <td>6.214806</td>\n",
       "      <td>103.905037</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160500000</td>\n",
       "      <td>[{\"timeTick\":3938155,\"X\":0.1266622543334961,\"Y...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14426</th>\n",
       "      <td>14533718</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030544</td>\n",
       "      <td>77.516465</td>\n",
       "      <td>6.214806</td>\n",
       "      <td>103.905037</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160500000</td>\n",
       "      <td>[{\"timeTick\":3938155,\"X\":0.1266622543334961,\"Y...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14427</th>\n",
       "      <td>14533718</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030544</td>\n",
       "      <td>77.516465</td>\n",
       "      <td>6.214806</td>\n",
       "      <td>103.905037</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160500000</td>\n",
       "      <td>[{\"timeTick\":3938155,\"X\":0.1266622543334961,\"Y...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14428</th>\n",
       "      <td>14533718</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030544</td>\n",
       "      <td>77.516465</td>\n",
       "      <td>6.214806</td>\n",
       "      <td>103.905037</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160500000</td>\n",
       "      <td>[{\"timeTick\":3938155,\"X\":0.1266622543334961,\"Y...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14429</th>\n",
       "      <td>14533728</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030531</td>\n",
       "      <td>77.516514</td>\n",
       "      <td>5.504738</td>\n",
       "      <td>101.953148</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160501000</td>\n",
       "      <td>[{\"timeTick\":3939157,\"X\":3.1830759048461914,\"Y...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14430</th>\n",
       "      <td>14533728</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030531</td>\n",
       "      <td>77.516514</td>\n",
       "      <td>5.504738</td>\n",
       "      <td>101.953148</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160501000</td>\n",
       "      <td>[{\"timeTick\":3939157,\"X\":3.1830759048461914,\"Y...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14431</th>\n",
       "      <td>14533728</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030531</td>\n",
       "      <td>77.516514</td>\n",
       "      <td>5.504738</td>\n",
       "      <td>101.953148</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160501000</td>\n",
       "      <td>[{\"timeTick\":3939157,\"X\":3.1830759048461914,\"Y...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14432</th>\n",
       "      <td>14533728</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030531</td>\n",
       "      <td>77.516514</td>\n",
       "      <td>5.504738</td>\n",
       "      <td>101.953148</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160501000</td>\n",
       "      <td>[{\"timeTick\":3939157,\"X\":3.1830759048461914,\"Y...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14433</th>\n",
       "      <td>14533728</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030531</td>\n",
       "      <td>77.516514</td>\n",
       "      <td>5.504738</td>\n",
       "      <td>101.953148</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160501000</td>\n",
       "      <td>[{\"timeTick\":3939157,\"X\":3.1830759048461914,\"Y...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             id  vehicle_id   latitude  longitude     speed     heading  \\\n",
       "14424  14533718  KA04MW6569  13.030544  77.516465  6.214806  103.905037   \n",
       "14425  14533718  KA04MW6569  13.030544  77.516465  6.214806  103.905037   \n",
       "14426  14533718  KA04MW6569  13.030544  77.516465  6.214806  103.905037   \n",
       "14427  14533718  KA04MW6569  13.030544  77.516465  6.214806  103.905037   \n",
       "14428  14533718  KA04MW6569  13.030544  77.516465  6.214806  103.905037   \n",
       "14429  14533728  KA04MW6569  13.030531  77.516514  5.504738  101.953148   \n",
       "14430  14533728  KA04MW6569  13.030531  77.516514  5.504738  101.953148   \n",
       "14431  14533728  KA04MW6569  13.030531  77.516514  5.504738  101.953148   \n",
       "14432  14533728  KA04MW6569  13.030531  77.516514  5.504738  101.953148   \n",
       "14433  14533728  KA04MW6569  13.030531  77.516514  5.504738  101.953148   \n",
       "\n",
       "       acc_signal      timestamp  \\\n",
       "14424           1  1651160500000   \n",
       "14425           1  1651160500000   \n",
       "14426           1  1651160500000   \n",
       "14427           1  1651160500000   \n",
       "14428           1  1651160500000   \n",
       "14429           1  1651160501000   \n",
       "14430           1  1651160501000   \n",
       "14431           1  1651160501000   \n",
       "14432           1  1651160501000   \n",
       "14433           1  1651160501000   \n",
       "\n",
       "                                                 gsensor  \n",
       "14424  [{\"timeTick\":3938155,\"X\":0.1266622543334961,\"Y...  \n",
       "14425  [{\"timeTick\":3938155,\"X\":0.1266622543334961,\"Y...  \n",
       "14426  [{\"timeTick\":3938155,\"X\":0.1266622543334961,\"Y...  \n",
       "14427  [{\"timeTick\":3938155,\"X\":0.1266622543334961,\"Y...  \n",
       "14428  [{\"timeTick\":3938155,\"X\":0.1266622543334961,\"Y...  \n",
       "14429  [{\"timeTick\":3939157,\"X\":3.1830759048461914,\"Y...  \n",
       "14430  [{\"timeTick\":3939157,\"X\":3.1830759048461914,\"Y...  \n",
       "14431  [{\"timeTick\":3939157,\"X\":3.1830759048461914,\"Y...  \n",
       "14432  [{\"timeTick\":3939157,\"X\":3.1830759048461914,\"Y...  \n",
       "14433  [{\"timeTick\":3939157,\"X\":3.1830759048461914,\"Y...  "
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[20:30,:9]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "id": "4602612e",
   "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>gsen_X</th>\n",
       "      <th>gsen_Y</th>\n",
       "      <th>gsen_Z</th>\n",
       "      <th>subid</th>\n",
       "      <th>gyro_X</th>\n",
       "      <th>gyro_Y</th>\n",
       "      <th>gyro_Z</th>\n",
       "      <th>unsafe_gsenX</th>\n",
       "      <th>unsafe_gsenY</th>\n",
       "      <th>unsafe_gsenZ</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14424</th>\n",
       "      <td>0.126662</td>\n",
       "      <td>-1.055346</td>\n",
       "      <td>0.823699</td>\n",
       "      <td>14533718.1</td>\n",
       "      <td>-0.005096</td>\n",
       "      <td>0.001938</td>\n",
       "      <td>0.024567</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14425</th>\n",
       "      <td>-0.265245</td>\n",
       "      <td>-1.468928</td>\n",
       "      <td>0.753511</td>\n",
       "      <td>14533718.2</td>\n",
       "      <td>-0.010849</td>\n",
       "      <td>-0.004883</td>\n",
       "      <td>0.018173</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14426</th>\n",
       "      <td>0.676355</td>\n",
       "      <td>-0.574580</td>\n",
       "      <td>1.203745</td>\n",
       "      <td>14533718.3</td>\n",
       "      <td>0.055298</td>\n",
       "      <td>-0.015213</td>\n",
       "      <td>-0.018890</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14427</th>\n",
       "      <td>-1.250422</td>\n",
       "      <td>-2.332044</td>\n",
       "      <td>-0.076195</td>\n",
       "      <td>14533718.4</td>\n",
       "      <td>-0.141571</td>\n",
       "      <td>0.090775</td>\n",
       "      <td>0.115219</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14428</th>\n",
       "      <td>-1.301949</td>\n",
       "      <td>-1.207870</td>\n",
       "      <td>-0.529979</td>\n",
       "      <td>14533718.5</td>\n",
       "      <td>-0.213791</td>\n",
       "      <td>-0.028214</td>\n",
       "      <td>0.056946</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14429</th>\n",
       "      <td>3.183076</td>\n",
       "      <td>2.567991</td>\n",
       "      <td>2.252641</td>\n",
       "      <td>14533728.1</td>\n",
       "      <td>0.176208</td>\n",
       "      <td>0.050629</td>\n",
       "      <td>-0.088562</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14430</th>\n",
       "      <td>-0.794319</td>\n",
       "      <td>-0.029197</td>\n",
       "      <td>-1.163878</td>\n",
       "      <td>14533728.2</td>\n",
       "      <td>0.209335</td>\n",
       "      <td>-0.133133</td>\n",
       "      <td>-0.159302</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14431</th>\n",
       "      <td>-0.070409</td>\n",
       "      <td>-0.024428</td>\n",
       "      <td>-0.600643</td>\n",
       "      <td>14533728.3</td>\n",
       "      <td>-0.019272</td>\n",
       "      <td>-0.126419</td>\n",
       "      <td>0.010406</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14432</th>\n",
       "      <td>2.555487</td>\n",
       "      <td>2.778320</td>\n",
       "      <td>1.741617</td>\n",
       "      <td>14533728.4</td>\n",
       "      <td>0.152771</td>\n",
       "      <td>0.131577</td>\n",
       "      <td>-0.121689</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14433</th>\n",
       "      <td>-4.042803</td>\n",
       "      <td>-3.189753</td>\n",
       "      <td>-3.758142</td>\n",
       "      <td>14533728.5</td>\n",
       "      <td>-0.095108</td>\n",
       "      <td>-0.197800</td>\n",
       "      <td>0.108398</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         gsen_X    gsen_Y    gsen_Z       subid    gyro_X    gyro_Y    gyro_Z  \\\n",
       "14424  0.126662 -1.055346  0.823699  14533718.1 -0.005096  0.001938  0.024567   \n",
       "14425 -0.265245 -1.468928  0.753511  14533718.2 -0.010849 -0.004883  0.018173   \n",
       "14426  0.676355 -0.574580  1.203745  14533718.3  0.055298 -0.015213 -0.018890   \n",
       "14427 -1.250422 -2.332044 -0.076195  14533718.4 -0.141571  0.090775  0.115219   \n",
       "14428 -1.301949 -1.207870 -0.529979  14533718.5 -0.213791 -0.028214  0.056946   \n",
       "14429  3.183076  2.567991  2.252641  14533728.1  0.176208  0.050629 -0.088562   \n",
       "14430 -0.794319 -0.029197 -1.163878  14533728.2  0.209335 -0.133133 -0.159302   \n",
       "14431 -0.070409 -0.024428 -0.600643  14533728.3 -0.019272 -0.126419  0.010406   \n",
       "14432  2.555487  2.778320  1.741617  14533728.4  0.152771  0.131577 -0.121689   \n",
       "14433 -4.042803 -3.189753 -3.758142  14533728.5 -0.095108 -0.197800  0.108398   \n",
       "\n",
       "       unsafe_gsenX  unsafe_gsenY  unsafe_gsenZ  \n",
       "14424         False         False         False  \n",
       "14425         False         False         False  \n",
       "14426         False         False         False  \n",
       "14427         False         False         False  \n",
       "14428         False         False         False  \n",
       "14429          True         False         False  \n",
       "14430         False         False         False  \n",
       "14431         False         False         False  \n",
       "14432         False         False         False  \n",
       "14433          True          True          True  "
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[20:30, 9+2:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "id": "874ec5e3",
   "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>unsafe_gsenX</th>\n",
       "      <th>unsafe_gsenY</th>\n",
       "      <th>unsafe_gsenZ</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    unsafe_gsenX  unsafe_gsenY  unsafe_gsenZ\n",
       "20         False         False         False\n",
       "21         False         False         False\n",
       "22         False         False         False\n",
       "23         False         False         False\n",
       "24         False         False         False\n",
       "25          True         False         False\n",
       "26         False         False         False\n",
       "27         False         False         False\n",
       "28         False         False         False\n",
       "29          True          True          True"
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.filter(regex='unsafe')[20:30]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "id": "2de1ea7f-2a82-4de0-83b5-bc893ae756f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# feather.write_dataframe(df, f'{filename}.feather')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "id": "d912097b-62b4-483f-9464-c22597313f2f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# col_sel = ['id', 'vehicle_id', 'latitude', 'longitude', 'speed', 'heading', 'acc_signal', 'timestamp', 'timestamp_conv']\n",
    "# col_sel += ['gsensor', 'gyro', 'gsen_X', 'gsen_Y', 'gsen_Z', 'gyro_X', 'gyro_Y', 'gyro_Z', 'subid']\n",
    "# col_sel\n",
    "# df = df[col_sel]\n",
    "# df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "104f26cd-0b26-49cb-95ce-026296785bc4",
   "metadata": {},
   "source": [
    "## Split json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "18e9b062-ad82-46af-a660-f3df0eed6f44",
   "metadata": {},
   "outputs": [],
   "source": [
    "# str to list. [dict()]。 \n",
    "# [{'timeTick': v, 'X': v, 'Y': v, 'Z': v}]\n",
    "# df['gsensor_json'] = [json.loads(x) for x in df.gsensor]\n",
    "# df['gyro_json'] = [json.loads(x) for x in df.gyro]      \n",
    "\n",
    "# print(type(df.gsensor[0]), ' to ', type(df['gsensor_json'][0]))\n",
    "# df['gsensor_json'][0][0]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "id": "ca0a5424-cfb1-4ba0-ad7b-83526ac90946",
   "metadata": {},
   "outputs": [],
   "source": [
    "# def split_json(df: pd.DataFrame) -> pd.DataFrame:\n",
    "#     for item in ['timeTick', 'X', 'Y', 'Z']:\n",
    "#         df[f'gsen_{item}'] = [[x[f'{item}'] for x in xs] for xs in df['gsensor_json']]\n",
    "#         df[f'gyro_{item}'] = [[x[f'{item}'] for x in xs] for xs in df['gyro_json']]\n",
    "        \n",
    "#     return df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "id": "eafb52d2-7a41-428e-812a-a53977ce6da0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# split_json(df) \n",
    "# df.drop(columns=['acc_signal', 'gyro', 'gsensor', 'gsensor_json', 'gyro_json'], inplace=True)\n",
    "# df.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0adbb568-89ca-434c-bdbd-5a327dd27f9e",
   "metadata": {},
   "source": [
    "## file saving"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "714b8153-33a9-44e4-9885-87edccc92555",
   "metadata": {},
   "outputs": [],
   "source": [
    "# feather.write_dataframe(df, f'df_preProcess_{filename}.feather')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da3447dd-c56e-419d-8922-90925ed69a67",
   "metadata": {},
   "source": [
    "# Filtering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "3dc9089c-8807-45a9-8c1b-adac8da5276e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # convert 13 digits unix ts to date time\n",
    "# df.loc[:,'timestamp_conv'] = pd.to_datetime(df['timestamp'], unit='ms')\n",
    "\n",
    "# # filter out 車子熄火的狀態 \n",
    "# # df = df[df['acc_signal'] == 1]\n",
    "\n",
    "# # filter out low speed (5.5 m/s = 20 kph; 2.7 m/s = 10kph)  \n",
    "# speed = 2.7  # threshold of Andy : 3.0\n",
    "\n",
    "# df = df[df['speed'] > speed]\n",
    "\n",
    "# df.reset_index(inplace=True, drop=True)\n",
    "# # filter out low Z-axis gsensor (0.15g = 1.5 m/s^2, 0.3g = 2.94 m/s^2)\n",
    "# threshold = 2.94\n",
    "\n",
    "# df['filter_gsen_X'] = [True if (np.max(x, initial=0) > threshold) | (np.min(x, initial=0) < -threshold) else False for x in df.gsen_X] \n",
    "# df['filter_gsen_Y'] = [True if (np.max(x, initial=0) > threshold) | (np.min(x, initial=0) < -threshold) else False for x in df.gsen_Y] \n",
    "# df['filter_gsen_Z'] = [True if (np.max(x, initial=0) > threshold) | (np.min(x, initial=0) < -threshold) else False for x in df.gsen_Z] \n",
    "\n",
    "# df_outlier = df[(df['filter_gsen_X'] == True) | (df['filter_gsen_Y'] == True) | (df['filter_gsen_Z'] == True)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f60a589c",
   "metadata": {},
   "source": [
    "#### Criteria\n",
    "- Threshold\n",
    "    - Speed > 3.0 m/s (10.8 hm/h)\n",
    "    - gsensor > 0.15g (0.15g = 1.5 m/s^2, 0.3g = 2.94 m/s^2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3107e32d",
   "metadata": {},
   "source": [
    "- 以秒為單位\n",
    "    - 需要將1秒內的5筆sensor採樣點資料一起打包\n",
    "\n",
    "- 以timestamp為單位\n",
    "    - 或直接以異常值發生的timestamp當單位，往前後採用\n",
    "    \n",
    "- 同樣都要處理gps 採樣與 sensor採樣不一致的問題\n",
    "\n",
    "- sensor data 問題\n",
    "    - 重複寫入\n",
    "    - 沒有對齊\n",
    "        - gyro與gsensor寫入不同步\n",
    "    - 缺失(gyro與gsensor同步缺失)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ab007ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iloc[20:30, 9+2:]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "622c4fb8",
   "metadata": {},
   "source": [
    "- 這個篩選是以timestamp為單位，無法保留一秒內的完整採樣資料\n",
    "- 需要改以＂秒＂為單位，保留整批次採樣資料"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "0be0db2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "con_gsen = (df['unsafe_gsenX'] == True) | (df['unsafe_gsenY'] == True) | (df['unsafe_gsenZ'] == True)\n",
    "con_speed = df['speed'] > 3.0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "65a95d5b",
   "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>vehicle_id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>speed</th>\n",
       "      <th>heading</th>\n",
       "      <th>acc_signal</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>gsensor</th>\n",
       "      <th>gyro</th>\n",
       "      <th>...</th>\n",
       "      <th>gsen_X</th>\n",
       "      <th>gsen_Y</th>\n",
       "      <th>gsen_Z</th>\n",
       "      <th>subid</th>\n",
       "      <th>gyro_X</th>\n",
       "      <th>gyro_Y</th>\n",
       "      <th>gyro_Z</th>\n",
       "      <th>unsafe_gsenX</th>\n",
       "      <th>unsafe_gsenY</th>\n",
       "      <th>unsafe_gsenZ</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14429</th>\n",
       "      <td>14533728</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030531</td>\n",
       "      <td>77.516514</td>\n",
       "      <td>5.504738</td>\n",
       "      <td>101.953148</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160501000</td>\n",
       "      <td>[{\"timeTick\":3939157,\"X\":3.1830759048461914,\"Y...</td>\n",
       "      <td>[{\"timeTick\":3939155,\"X\":0.17620849609375,\"Y\":...</td>\n",
       "      <td>...</td>\n",
       "      <td>3.183076</td>\n",
       "      <td>2.567991</td>\n",
       "      <td>2.252641</td>\n",
       "      <td>14533728.1</td>\n",
       "      <td>0.176208</td>\n",
       "      <td>0.050629</td>\n",
       "      <td>-0.088562</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14433</th>\n",
       "      <td>14533728</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.030531</td>\n",
       "      <td>77.516514</td>\n",
       "      <td>5.504738</td>\n",
       "      <td>101.953148</td>\n",
       "      <td>1</td>\n",
       "      <td>1651160501000</td>\n",
       "      <td>[{\"timeTick\":3939157,\"X\":3.1830759048461914,\"Y...</td>\n",
       "      <td>[{\"timeTick\":3939155,\"X\":0.17620849609375,\"Y\":...</td>\n",
       "      <td>...</td>\n",
       "      <td>-4.042803</td>\n",
       "      <td>-3.189753</td>\n",
       "      <td>-3.758142</td>\n",
       "      <td>14533728.5</td>\n",
       "      <td>-0.095108</td>\n",
       "      <td>-0.197800</td>\n",
       "      <td>0.108398</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21044</th>\n",
       "      <td>14539046</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>12.935314</td>\n",
       "      <td>77.536465</td>\n",
       "      <td>3.626204</td>\n",
       "      <td>118.965805</td>\n",
       "      <td>1</td>\n",
       "      <td>1651161833000</td>\n",
       "      <td>[{\"timeTick\":5270040,\"X\":0.2576866149902344,\"Y...</td>\n",
       "      <td>[{\"timeTick\":5270040,\"X\":-0.0821380615234375,\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>-2.761128</td>\n",
       "      <td>-3.288322</td>\n",
       "      <td>-0.578127</td>\n",
       "      <td>14539046.2</td>\n",
       "      <td>-0.197937</td>\n",
       "      <td>0.073746</td>\n",
       "      <td>0.119522</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26476</th>\n",
       "      <td>16600513</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>12.933928</td>\n",
       "      <td>77.538801</td>\n",
       "      <td>4.593207</td>\n",
       "      <td>299.664276</td>\n",
       "      <td>1</td>\n",
       "      <td>1651634053000</td>\n",
       "      <td>[{\"timeTick\":477491175,\"X\":1.372842788696289,\"...</td>\n",
       "      <td>[{\"timeTick\":477491175,\"X\":0.1339569091796875,...</td>\n",
       "      <td>...</td>\n",
       "      <td>-3.069280</td>\n",
       "      <td>-2.316227</td>\n",
       "      <td>-1.660519</td>\n",
       "      <td>16600513.3</td>\n",
       "      <td>-0.105515</td>\n",
       "      <td>0.030426</td>\n",
       "      <td>0.054260</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26595</th>\n",
       "      <td>16600585</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>12.934813</td>\n",
       "      <td>77.537266</td>\n",
       "      <td>3.544833</td>\n",
       "      <td>300.971924</td>\n",
       "      <td>1</td>\n",
       "      <td>1651634077000</td>\n",
       "      <td>[{\"timeTick\":477515181,\"X\":0.7608447074890137,...</td>\n",
       "      <td>[{\"timeTick\":477515180,\"X\":0.07452392578125,\"Y...</td>\n",
       "      <td>...</td>\n",
       "      <td>-2.964461</td>\n",
       "      <td>-0.817166</td>\n",
       "      <td>-2.036281</td>\n",
       "      <td>16600585.2</td>\n",
       "      <td>-0.028061</td>\n",
       "      <td>0.057266</td>\n",
       "      <td>0.025177</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4095232</th>\n",
       "      <td>56931429</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.032693</td>\n",
       "      <td>77.520603</td>\n",
       "      <td>7.673488</td>\n",
       "      <td>12.993739</td>\n",
       "      <td>1</td>\n",
       "      <td>1664789540434</td>\n",
       "      <td>[{\"timeTick\":129735195,\"X\":1.42252779006958,\"Y...</td>\n",
       "      <td>[{\"timeTick\":129735194,\"X\":-0.0021820068359375...</td>\n",
       "      <td>...</td>\n",
       "      <td>1.840670</td>\n",
       "      <td>3.633478</td>\n",
       "      <td>-0.763492</td>\n",
       "      <td>56931429.4</td>\n",
       "      <td>0.026047</td>\n",
       "      <td>-0.054764</td>\n",
       "      <td>-0.142960</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4095233</th>\n",
       "      <td>56931429</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.032693</td>\n",
       "      <td>77.520603</td>\n",
       "      <td>7.673488</td>\n",
       "      <td>12.993739</td>\n",
       "      <td>1</td>\n",
       "      <td>1664789540434</td>\n",
       "      <td>[{\"timeTick\":129735195,\"X\":1.42252779006958,\"Y...</td>\n",
       "      <td>[{\"timeTick\":129735194,\"X\":-0.0021820068359375...</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.137802</td>\n",
       "      <td>-3.533251</td>\n",
       "      <td>0.745377</td>\n",
       "      <td>56931429.5</td>\n",
       "      <td>-0.015503</td>\n",
       "      <td>0.065186</td>\n",
       "      <td>0.168625</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4095246</th>\n",
       "      <td>56931442</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.032919</td>\n",
       "      <td>77.520658</td>\n",
       "      <td>6.090010</td>\n",
       "      <td>12.693027</td>\n",
       "      <td>1</td>\n",
       "      <td>1664789544455</td>\n",
       "      <td>[{\"timeTick\":129739197,\"X\":0.6400856971740723,...</td>\n",
       "      <td>[{\"timeTick\":129739197,\"X\":0.00091552734375,\"Y...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.955173</td>\n",
       "      <td>2.954717</td>\n",
       "      <td>-1.012249</td>\n",
       "      <td>56931442.3</td>\n",
       "      <td>0.013153</td>\n",
       "      <td>-0.066589</td>\n",
       "      <td>-0.179504</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4105112</th>\n",
       "      <td>56970605</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.047241</td>\n",
       "      <td>77.508049</td>\n",
       "      <td>7.165850</td>\n",
       "      <td>181.923080</td>\n",
       "      <td>1</td>\n",
       "      <td>1664798704812</td>\n",
       "      <td>[{\"timeTick\":138899071,\"X\":-0.0773973464965820...</td>\n",
       "      <td>[{\"timeTick\":138899071,\"X\":-0.036102294921875,...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.646813</td>\n",
       "      <td>2.951618</td>\n",
       "      <td>-0.789567</td>\n",
       "      <td>56970605.2</td>\n",
       "      <td>0.043365</td>\n",
       "      <td>-0.018524</td>\n",
       "      <td>-0.246399</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4107658</th>\n",
       "      <td>56972997</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.033912</td>\n",
       "      <td>77.512645</td>\n",
       "      <td>6.947242</td>\n",
       "      <td>186.427322</td>\n",
       "      <td>1</td>\n",
       "      <td>1664799445905</td>\n",
       "      <td>[{\"timeTick\":139640031,\"X\":1.9055163860321045,...</td>\n",
       "      <td>[{\"timeTick\":139640031,\"X\":-0.0292816162109375...</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.106940</td>\n",
       "      <td>-3.210121</td>\n",
       "      <td>1.151676</td>\n",
       "      <td>56972997.5</td>\n",
       "      <td>-0.085754</td>\n",
       "      <td>0.049881</td>\n",
       "      <td>0.248749</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1131 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               id  vehicle_id   latitude  longitude     speed     heading  \\\n",
       "14429    14533728  KA04MW6569  13.030531  77.516514  5.504738  101.953148   \n",
       "14433    14533728  KA04MW6569  13.030531  77.516514  5.504738  101.953148   \n",
       "21044    14539046  KA04MW6569  12.935314  77.536465  3.626204  118.965805   \n",
       "26476    16600513  KA04MW6569  12.933928  77.538801  4.593207  299.664276   \n",
       "26595    16600585  KA04MW6569  12.934813  77.537266  3.544833  300.971924   \n",
       "...           ...         ...        ...        ...       ...         ...   \n",
       "4095232  56931429  KA04MW6569  13.032693  77.520603  7.673488   12.993739   \n",
       "4095233  56931429  KA04MW6569  13.032693  77.520603  7.673488   12.993739   \n",
       "4095246  56931442  KA04MW6569  13.032919  77.520658  6.090010   12.693027   \n",
       "4105112  56970605  KA04MW6569  13.047241  77.508049  7.165850  181.923080   \n",
       "4107658  56972997  KA04MW6569  13.033912  77.512645  6.947242  186.427322   \n",
       "\n",
       "         acc_signal      timestamp  \\\n",
       "14429             1  1651160501000   \n",
       "14433             1  1651160501000   \n",
       "21044             1  1651161833000   \n",
       "26476             1  1651634053000   \n",
       "26595             1  1651634077000   \n",
       "...             ...            ...   \n",
       "4095232           1  1664789540434   \n",
       "4095233           1  1664789540434   \n",
       "4095246           1  1664789544455   \n",
       "4105112           1  1664798704812   \n",
       "4107658           1  1664799445905   \n",
       "\n",
       "                                                   gsensor  \\\n",
       "14429    [{\"timeTick\":3939157,\"X\":3.1830759048461914,\"Y...   \n",
       "14433    [{\"timeTick\":3939157,\"X\":3.1830759048461914,\"Y...   \n",
       "21044    [{\"timeTick\":5270040,\"X\":0.2576866149902344,\"Y...   \n",
       "26476    [{\"timeTick\":477491175,\"X\":1.372842788696289,\"...   \n",
       "26595    [{\"timeTick\":477515181,\"X\":0.7608447074890137,...   \n",
       "...                                                    ...   \n",
       "4095232  [{\"timeTick\":129735195,\"X\":1.42252779006958,\"Y...   \n",
       "4095233  [{\"timeTick\":129735195,\"X\":1.42252779006958,\"Y...   \n",
       "4095246  [{\"timeTick\":129739197,\"X\":0.6400856971740723,...   \n",
       "4105112  [{\"timeTick\":138899071,\"X\":-0.0773973464965820...   \n",
       "4107658  [{\"timeTick\":139640031,\"X\":1.9055163860321045,...   \n",
       "\n",
       "                                                      gyro  ...    gsen_X  \\\n",
       "14429    [{\"timeTick\":3939155,\"X\":0.17620849609375,\"Y\":...  ...  3.183076   \n",
       "14433    [{\"timeTick\":3939155,\"X\":0.17620849609375,\"Y\":...  ... -4.042803   \n",
       "21044    [{\"timeTick\":5270040,\"X\":-0.0821380615234375,\"...  ... -2.761128   \n",
       "26476    [{\"timeTick\":477491175,\"X\":0.1339569091796875,...  ... -3.069280   \n",
       "26595    [{\"timeTick\":477515180,\"X\":0.07452392578125,\"Y...  ... -2.964461   \n",
       "...                                                    ...  ...       ...   \n",
       "4095232  [{\"timeTick\":129735194,\"X\":-0.0021820068359375...  ...  1.840670   \n",
       "4095233  [{\"timeTick\":129735194,\"X\":-0.0021820068359375...  ... -0.137802   \n",
       "4095246  [{\"timeTick\":129739197,\"X\":0.00091552734375,\"Y...  ...  0.955173   \n",
       "4105112  [{\"timeTick\":138899071,\"X\":-0.036102294921875,...  ...  0.646813   \n",
       "4107658  [{\"timeTick\":139640031,\"X\":-0.0292816162109375...  ... -0.106940   \n",
       "\n",
       "           gsen_Y    gsen_Z       subid    gyro_X    gyro_Y    gyro_Z  \\\n",
       "14429    2.567991  2.252641  14533728.1  0.176208  0.050629 -0.088562   \n",
       "14433   -3.189753 -3.758142  14533728.5 -0.095108 -0.197800  0.108398   \n",
       "21044   -3.288322 -0.578127  14539046.2 -0.197937  0.073746  0.119522   \n",
       "26476   -2.316227 -1.660519  16600513.3 -0.105515  0.030426  0.054260   \n",
       "26595   -0.817166 -2.036281  16600585.2 -0.028061  0.057266  0.025177   \n",
       "...           ...       ...         ...       ...       ...       ...   \n",
       "4095232  3.633478 -0.763492  56931429.4  0.026047 -0.054764 -0.142960   \n",
       "4095233 -3.533251  0.745377  56931429.5 -0.015503  0.065186  0.168625   \n",
       "4095246  2.954717 -1.012249  56931442.3  0.013153 -0.066589 -0.179504   \n",
       "4105112  2.951618 -0.789567  56970605.2  0.043365 -0.018524 -0.246399   \n",
       "4107658 -3.210121  1.151676  56972997.5 -0.085754  0.049881  0.248749   \n",
       "\n",
       "         unsafe_gsenX  unsafe_gsenY  unsafe_gsenZ  \n",
       "14429            True         False         False  \n",
       "14433            True          True          True  \n",
       "21044           False          True         False  \n",
       "26476            True         False         False  \n",
       "26595            True         False         False  \n",
       "...               ...           ...           ...  \n",
       "4095232         False          True         False  \n",
       "4095233         False          True         False  \n",
       "4095246         False          True         False  \n",
       "4105112         False          True         False  \n",
       "4107658         False          True         False  \n",
       "\n",
       "[1131 rows x 21 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[con_gsen & con_speed]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bce6d1a8",
   "metadata": {},
   "source": [
    "##### Check number of data per timestamp "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "446037f9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([  1,   2,   3,   4,   5,   6,   7,   8,   9,  10,  11,  12,  15,\n",
       "        18,  20,  25,  30,  40,  45,  50,  60,  65,  70,  75,  93,  99,\n",
       "       105, 120, 135, 165, 195, 207, 219, 225, 240, 255, 267])"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('timestamp').id.count().sort_values().unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "8a622a21",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 1,  2,  3,  4,  5,  6,  7,  8,  9, 10, 12, 15, 18, 20])"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('id').id.count().sort_values().unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "0741123d",
   "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>vehicle_id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>speed</th>\n",
       "      <th>heading</th>\n",
       "      <th>acc_signal</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>gsensor</th>\n",
       "      <th>gyro</th>\n",
       "      <th>...</th>\n",
       "      <th>gsen_X</th>\n",
       "      <th>gsen_Y</th>\n",
       "      <th>gsen_Z</th>\n",
       "      <th>subid</th>\n",
       "      <th>gyro_X</th>\n",
       "      <th>gyro_Y</th>\n",
       "      <th>gyro_Z</th>\n",
       "      <th>unsafe_gsenX</th>\n",
       "      <th>unsafe_gsenY</th>\n",
       "      <th>unsafe_gsenZ</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>686894</th>\n",
       "      <td>20090421</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.031567</td>\n",
       "      <td>77.512923</td>\n",
       "      <td>4.041155</td>\n",
       "      <td>9.479123</td>\n",
       "      <td>1</td>\n",
       "      <td>1653268144000</td>\n",
       "      <td>[{\"timeTick\":2111582139,\"X\":-0.747638702392578...</td>\n",
       "      <td>[{\"timeTick\":2111582139,\"X\":-0.070663452148437...</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.747639</td>\n",
       "      <td>0.964187</td>\n",
       "      <td>-0.009824</td>\n",
       "      <td>20090421.1</td>\n",
       "      <td>-0.070663</td>\n",
       "      <td>-0.066895</td>\n",
       "      <td>-0.217941</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              id  vehicle_id   latitude  longitude     speed   heading  \\\n",
       "686894  20090421  KA04MW6569  13.031567  77.512923  4.041155  9.479123   \n",
       "\n",
       "        acc_signal      timestamp  \\\n",
       "686894           1  1653268144000   \n",
       "\n",
       "                                                  gsensor  \\\n",
       "686894  [{\"timeTick\":2111582139,\"X\":-0.747638702392578...   \n",
       "\n",
       "                                                     gyro  ...    gsen_X  \\\n",
       "686894  [{\"timeTick\":2111582139,\"X\":-0.070663452148437...  ... -0.747639   \n",
       "\n",
       "          gsen_Y    gsen_Z       subid    gyro_X    gyro_Y    gyro_Z  \\\n",
       "686894  0.964187 -0.009824  20090421.1 -0.070663 -0.066895 -0.217941   \n",
       "\n",
       "        unsafe_gsenX  unsafe_gsenY  unsafe_gsenZ  \n",
       "686894         False         False         False  \n",
       "\n",
       "[1 rows x 21 columns]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mask = df.groupby('id').id.count() == 1\n",
    "idx_ = df.groupby('id').id.count()[mask].index\n",
    "\n",
    "id = idx_[0]\n",
    "df.query('id==@id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "f27501f1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['[{\"timeTick\":2111582139,\"X\":-0.7476387023925781,\"Y\":0.9641866683959961,\"Z\":-0.009823799133300781}]'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts_c = \"2022-05-23 01:09:04\"\n",
    "df.query('timestamp_conv==@ts_c').gsensor.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "51a71daa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "20090421 20\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>id</th>\n",
       "      <th>vehicle_id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>speed</th>\n",
       "      <th>heading</th>\n",
       "      <th>acc_signal</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>gsensor</th>\n",
       "      <th>gyro</th>\n",
       "      <th>...</th>\n",
       "      <th>gsen_X</th>\n",
       "      <th>gsen_Y</th>\n",
       "      <th>gsen_Z</th>\n",
       "      <th>subid</th>\n",
       "      <th>gyro_X</th>\n",
       "      <th>gyro_Y</th>\n",
       "      <th>gyro_Z</th>\n",
       "      <th>unsafe_gsenX</th>\n",
       "      <th>unsafe_gsenY</th>\n",
       "      <th>unsafe_gsenZ</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2527581</th>\n",
       "      <td>36552501</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.042435</td>\n",
       "      <td>77.514638</td>\n",
       "      <td>8.346089</td>\n",
       "      <td>111.50071</td>\n",
       "      <td>1</td>\n",
       "      <td>1657887300000</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.1645574569702148...</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.009918212890625,...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.164557</td>\n",
       "      <td>-0.301471</td>\n",
       "      <td>0.123606</td>\n",
       "      <td>36552501.1</td>\n",
       "      <td>0.009918</td>\n",
       "      <td>-0.000793</td>\n",
       "      <td>-0.009598</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2527582</th>\n",
       "      <td>36552501</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.042435</td>\n",
       "      <td>77.514638</td>\n",
       "      <td>8.346089</td>\n",
       "      <td>111.50071</td>\n",
       "      <td>1</td>\n",
       "      <td>1657887300000</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.1645574569702148...</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.009918212890625,...</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.000784</td>\n",
       "      <td>-0.221695</td>\n",
       "      <td>0.134298</td>\n",
       "      <td>36552501.2</td>\n",
       "      <td>0.013535</td>\n",
       "      <td>0.001862</td>\n",
       "      <td>-0.000229</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2527583</th>\n",
       "      <td>36552501</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.042435</td>\n",
       "      <td>77.514638</td>\n",
       "      <td>8.346089</td>\n",
       "      <td>111.50071</td>\n",
       "      <td>1</td>\n",
       "      <td>1657887300000</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.1645574569702148...</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.009918212890625,...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.147655</td>\n",
       "      <td>-0.139551</td>\n",
       "      <td>0.141956</td>\n",
       "      <td>36552501.3</td>\n",
       "      <td>0.024506</td>\n",
       "      <td>-0.007614</td>\n",
       "      <td>-0.006302</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2527584</th>\n",
       "      <td>36552501</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.042435</td>\n",
       "      <td>77.514638</td>\n",
       "      <td>8.346089</td>\n",
       "      <td>111.50071</td>\n",
       "      <td>1</td>\n",
       "      <td>1657887300000</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.1645574569702148...</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.009918212890625,...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.096092</td>\n",
       "      <td>-0.102794</td>\n",
       "      <td>0.083211</td>\n",
       "      <td>36552501.4</td>\n",
       "      <td>0.046234</td>\n",
       "      <td>0.004105</td>\n",
       "      <td>-0.013000</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2527585</th>\n",
       "      <td>36552501</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.042435</td>\n",
       "      <td>77.514638</td>\n",
       "      <td>8.346089</td>\n",
       "      <td>111.50071</td>\n",
       "      <td>1</td>\n",
       "      <td>1657887300000</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.1645574569702148...</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.009918212890625,...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.114291</td>\n",
       "      <td>1.021729</td>\n",
       "      <td>0.693035</td>\n",
       "      <td>36552501.5</td>\n",
       "      <td>0.099609</td>\n",
       "      <td>0.004318</td>\n",
       "      <td>-0.014603</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2527581</th>\n",
       "      <td>36552501</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.042435</td>\n",
       "      <td>77.514638</td>\n",
       "      <td>8.346089</td>\n",
       "      <td>111.50071</td>\n",
       "      <td>1</td>\n",
       "      <td>1657887300000</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.1645574569702148...</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.009918212890625,...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.164557</td>\n",
       "      <td>-0.301471</td>\n",
       "      <td>0.123606</td>\n",
       "      <td>36552501.1</td>\n",
       "      <td>0.009918</td>\n",
       "      <td>-0.000793</td>\n",
       "      <td>-0.009598</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2527582</th>\n",
       "      <td>36552501</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.042435</td>\n",
       "      <td>77.514638</td>\n",
       "      <td>8.346089</td>\n",
       "      <td>111.50071</td>\n",
       "      <td>1</td>\n",
       "      <td>1657887300000</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.1645574569702148...</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.009918212890625,...</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.000784</td>\n",
       "      <td>-0.221695</td>\n",
       "      <td>0.134298</td>\n",
       "      <td>36552501.2</td>\n",
       "      <td>0.013535</td>\n",
       "      <td>0.001862</td>\n",
       "      <td>-0.000229</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2527583</th>\n",
       "      <td>36552501</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.042435</td>\n",
       "      <td>77.514638</td>\n",
       "      <td>8.346089</td>\n",
       "      <td>111.50071</td>\n",
       "      <td>1</td>\n",
       "      <td>1657887300000</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.1645574569702148...</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.009918212890625,...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.147655</td>\n",
       "      <td>-0.139551</td>\n",
       "      <td>0.141956</td>\n",
       "      <td>36552501.3</td>\n",
       "      <td>0.024506</td>\n",
       "      <td>-0.007614</td>\n",
       "      <td>-0.006302</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2527584</th>\n",
       "      <td>36552501</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.042435</td>\n",
       "      <td>77.514638</td>\n",
       "      <td>8.346089</td>\n",
       "      <td>111.50071</td>\n",
       "      <td>1</td>\n",
       "      <td>1657887300000</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.1645574569702148...</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.009918212890625,...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.096092</td>\n",
       "      <td>-0.102794</td>\n",
       "      <td>0.083211</td>\n",
       "      <td>36552501.4</td>\n",
       "      <td>0.046234</td>\n",
       "      <td>0.004105</td>\n",
       "      <td>-0.013000</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2527585</th>\n",
       "      <td>36552501</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.042435</td>\n",
       "      <td>77.514638</td>\n",
       "      <td>8.346089</td>\n",
       "      <td>111.50071</td>\n",
       "      <td>1</td>\n",
       "      <td>1657887300000</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.1645574569702148...</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.009918212890625,...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.114291</td>\n",
       "      <td>1.021729</td>\n",
       "      <td>0.693035</td>\n",
       "      <td>36552501.5</td>\n",
       "      <td>0.099609</td>\n",
       "      <td>0.004318</td>\n",
       "      <td>-0.014603</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2527581</th>\n",
       "      <td>36552501</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.042435</td>\n",
       "      <td>77.514638</td>\n",
       "      <td>8.346089</td>\n",
       "      <td>111.50071</td>\n",
       "      <td>1</td>\n",
       "      <td>1657887300000</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.1645574569702148...</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.009918212890625,...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.164557</td>\n",
       "      <td>-0.301471</td>\n",
       "      <td>0.123606</td>\n",
       "      <td>36552501.1</td>\n",
       "      <td>0.009918</td>\n",
       "      <td>-0.000793</td>\n",
       "      <td>-0.009598</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2527582</th>\n",
       "      <td>36552501</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.042435</td>\n",
       "      <td>77.514638</td>\n",
       "      <td>8.346089</td>\n",
       "      <td>111.50071</td>\n",
       "      <td>1</td>\n",
       "      <td>1657887300000</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.1645574569702148...</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.009918212890625,...</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.000784</td>\n",
       "      <td>-0.221695</td>\n",
       "      <td>0.134298</td>\n",
       "      <td>36552501.2</td>\n",
       "      <td>0.013535</td>\n",
       "      <td>0.001862</td>\n",
       "      <td>-0.000229</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2527583</th>\n",
       "      <td>36552501</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.042435</td>\n",
       "      <td>77.514638</td>\n",
       "      <td>8.346089</td>\n",
       "      <td>111.50071</td>\n",
       "      <td>1</td>\n",
       "      <td>1657887300000</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.1645574569702148...</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.009918212890625,...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.147655</td>\n",
       "      <td>-0.139551</td>\n",
       "      <td>0.141956</td>\n",
       "      <td>36552501.3</td>\n",
       "      <td>0.024506</td>\n",
       "      <td>-0.007614</td>\n",
       "      <td>-0.006302</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2527584</th>\n",
       "      <td>36552501</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.042435</td>\n",
       "      <td>77.514638</td>\n",
       "      <td>8.346089</td>\n",
       "      <td>111.50071</td>\n",
       "      <td>1</td>\n",
       "      <td>1657887300000</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.1645574569702148...</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.009918212890625,...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.096092</td>\n",
       "      <td>-0.102794</td>\n",
       "      <td>0.083211</td>\n",
       "      <td>36552501.4</td>\n",
       "      <td>0.046234</td>\n",
       "      <td>0.004105</td>\n",
       "      <td>-0.013000</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2527585</th>\n",
       "      <td>36552501</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.042435</td>\n",
       "      <td>77.514638</td>\n",
       "      <td>8.346089</td>\n",
       "      <td>111.50071</td>\n",
       "      <td>1</td>\n",
       "      <td>1657887300000</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.1645574569702148...</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.009918212890625,...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.114291</td>\n",
       "      <td>1.021729</td>\n",
       "      <td>0.693035</td>\n",
       "      <td>36552501.5</td>\n",
       "      <td>0.099609</td>\n",
       "      <td>0.004318</td>\n",
       "      <td>-0.014603</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2527581</th>\n",
       "      <td>36552501</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.042435</td>\n",
       "      <td>77.514638</td>\n",
       "      <td>8.346089</td>\n",
       "      <td>111.50071</td>\n",
       "      <td>1</td>\n",
       "      <td>1657887300000</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.1645574569702148...</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.009918212890625,...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.164557</td>\n",
       "      <td>-0.301471</td>\n",
       "      <td>0.123606</td>\n",
       "      <td>36552501.1</td>\n",
       "      <td>0.009918</td>\n",
       "      <td>-0.000793</td>\n",
       "      <td>-0.009598</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2527582</th>\n",
       "      <td>36552501</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.042435</td>\n",
       "      <td>77.514638</td>\n",
       "      <td>8.346089</td>\n",
       "      <td>111.50071</td>\n",
       "      <td>1</td>\n",
       "      <td>1657887300000</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.1645574569702148...</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.009918212890625,...</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.000784</td>\n",
       "      <td>-0.221695</td>\n",
       "      <td>0.134298</td>\n",
       "      <td>36552501.2</td>\n",
       "      <td>0.013535</td>\n",
       "      <td>0.001862</td>\n",
       "      <td>-0.000229</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2527583</th>\n",
       "      <td>36552501</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.042435</td>\n",
       "      <td>77.514638</td>\n",
       "      <td>8.346089</td>\n",
       "      <td>111.50071</td>\n",
       "      <td>1</td>\n",
       "      <td>1657887300000</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.1645574569702148...</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.009918212890625,...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.147655</td>\n",
       "      <td>-0.139551</td>\n",
       "      <td>0.141956</td>\n",
       "      <td>36552501.3</td>\n",
       "      <td>0.024506</td>\n",
       "      <td>-0.007614</td>\n",
       "      <td>-0.006302</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2527584</th>\n",
       "      <td>36552501</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.042435</td>\n",
       "      <td>77.514638</td>\n",
       "      <td>8.346089</td>\n",
       "      <td>111.50071</td>\n",
       "      <td>1</td>\n",
       "      <td>1657887300000</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.1645574569702148...</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.009918212890625,...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.096092</td>\n",
       "      <td>-0.102794</td>\n",
       "      <td>0.083211</td>\n",
       "      <td>36552501.4</td>\n",
       "      <td>0.046234</td>\n",
       "      <td>0.004105</td>\n",
       "      <td>-0.013000</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2527585</th>\n",
       "      <td>36552501</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>13.042435</td>\n",
       "      <td>77.514638</td>\n",
       "      <td>8.346089</td>\n",
       "      <td>111.50071</td>\n",
       "      <td>1</td>\n",
       "      <td>1657887300000</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.1645574569702148...</td>\n",
       "      <td>[{\"timeTick\":1293552183,\"X\":0.009918212890625,...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.114291</td>\n",
       "      <td>1.021729</td>\n",
       "      <td>0.693035</td>\n",
       "      <td>36552501.5</td>\n",
       "      <td>0.099609</td>\n",
       "      <td>0.004318</td>\n",
       "      <td>-0.014603</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               id  vehicle_id   latitude  longitude     speed    heading  \\\n",
       "2527581  36552501  KA04MW6569  13.042435  77.514638  8.346089  111.50071   \n",
       "2527582  36552501  KA04MW6569  13.042435  77.514638  8.346089  111.50071   \n",
       "2527583  36552501  KA04MW6569  13.042435  77.514638  8.346089  111.50071   \n",
       "2527584  36552501  KA04MW6569  13.042435  77.514638  8.346089  111.50071   \n",
       "2527585  36552501  KA04MW6569  13.042435  77.514638  8.346089  111.50071   \n",
       "2527581  36552501  KA04MW6569  13.042435  77.514638  8.346089  111.50071   \n",
       "2527582  36552501  KA04MW6569  13.042435  77.514638  8.346089  111.50071   \n",
       "2527583  36552501  KA04MW6569  13.042435  77.514638  8.346089  111.50071   \n",
       "2527584  36552501  KA04MW6569  13.042435  77.514638  8.346089  111.50071   \n",
       "2527585  36552501  KA04MW6569  13.042435  77.514638  8.346089  111.50071   \n",
       "2527581  36552501  KA04MW6569  13.042435  77.514638  8.346089  111.50071   \n",
       "2527582  36552501  KA04MW6569  13.042435  77.514638  8.346089  111.50071   \n",
       "2527583  36552501  KA04MW6569  13.042435  77.514638  8.346089  111.50071   \n",
       "2527584  36552501  KA04MW6569  13.042435  77.514638  8.346089  111.50071   \n",
       "2527585  36552501  KA04MW6569  13.042435  77.514638  8.346089  111.50071   \n",
       "2527581  36552501  KA04MW6569  13.042435  77.514638  8.346089  111.50071   \n",
       "2527582  36552501  KA04MW6569  13.042435  77.514638  8.346089  111.50071   \n",
       "2527583  36552501  KA04MW6569  13.042435  77.514638  8.346089  111.50071   \n",
       "2527584  36552501  KA04MW6569  13.042435  77.514638  8.346089  111.50071   \n",
       "2527585  36552501  KA04MW6569  13.042435  77.514638  8.346089  111.50071   \n",
       "\n",
       "         acc_signal      timestamp  \\\n",
       "2527581           1  1657887300000   \n",
       "2527582           1  1657887300000   \n",
       "2527583           1  1657887300000   \n",
       "2527584           1  1657887300000   \n",
       "2527585           1  1657887300000   \n",
       "2527581           1  1657887300000   \n",
       "2527582           1  1657887300000   \n",
       "2527583           1  1657887300000   \n",
       "2527584           1  1657887300000   \n",
       "2527585           1  1657887300000   \n",
       "2527581           1  1657887300000   \n",
       "2527582           1  1657887300000   \n",
       "2527583           1  1657887300000   \n",
       "2527584           1  1657887300000   \n",
       "2527585           1  1657887300000   \n",
       "2527581           1  1657887300000   \n",
       "2527582           1  1657887300000   \n",
       "2527583           1  1657887300000   \n",
       "2527584           1  1657887300000   \n",
       "2527585           1  1657887300000   \n",
       "\n",
       "                                                   gsensor  \\\n",
       "2527581  [{\"timeTick\":1293552183,\"X\":0.1645574569702148...   \n",
       "2527582  [{\"timeTick\":1293552183,\"X\":0.1645574569702148...   \n",
       "2527583  [{\"timeTick\":1293552183,\"X\":0.1645574569702148...   \n",
       "2527584  [{\"timeTick\":1293552183,\"X\":0.1645574569702148...   \n",
       "2527585  [{\"timeTick\":1293552183,\"X\":0.1645574569702148...   \n",
       "2527581  [{\"timeTick\":1293552183,\"X\":0.1645574569702148...   \n",
       "2527582  [{\"timeTick\":1293552183,\"X\":0.1645574569702148...   \n",
       "2527583  [{\"timeTick\":1293552183,\"X\":0.1645574569702148...   \n",
       "2527584  [{\"timeTick\":1293552183,\"X\":0.1645574569702148...   \n",
       "2527585  [{\"timeTick\":1293552183,\"X\":0.1645574569702148...   \n",
       "2527581  [{\"timeTick\":1293552183,\"X\":0.1645574569702148...   \n",
       "2527582  [{\"timeTick\":1293552183,\"X\":0.1645574569702148...   \n",
       "2527583  [{\"timeTick\":1293552183,\"X\":0.1645574569702148...   \n",
       "2527584  [{\"timeTick\":1293552183,\"X\":0.1645574569702148...   \n",
       "2527585  [{\"timeTick\":1293552183,\"X\":0.1645574569702148...   \n",
       "2527581  [{\"timeTick\":1293552183,\"X\":0.1645574569702148...   \n",
       "2527582  [{\"timeTick\":1293552183,\"X\":0.1645574569702148...   \n",
       "2527583  [{\"timeTick\":1293552183,\"X\":0.1645574569702148...   \n",
       "2527584  [{\"timeTick\":1293552183,\"X\":0.1645574569702148...   \n",
       "2527585  [{\"timeTick\":1293552183,\"X\":0.1645574569702148...   \n",
       "\n",
       "                                                      gyro  ...    gsen_X  \\\n",
       "2527581  [{\"timeTick\":1293552183,\"X\":0.009918212890625,...  ...  0.164557   \n",
       "2527582  [{\"timeTick\":1293552183,\"X\":0.009918212890625,...  ... -0.000784   \n",
       "2527583  [{\"timeTick\":1293552183,\"X\":0.009918212890625,...  ...  0.147655   \n",
       "2527584  [{\"timeTick\":1293552183,\"X\":0.009918212890625,...  ...  0.096092   \n",
       "2527585  [{\"timeTick\":1293552183,\"X\":0.009918212890625,...  ...  0.114291   \n",
       "2527581  [{\"timeTick\":1293552183,\"X\":0.009918212890625,...  ...  0.164557   \n",
       "2527582  [{\"timeTick\":1293552183,\"X\":0.009918212890625,...  ... -0.000784   \n",
       "2527583  [{\"timeTick\":1293552183,\"X\":0.009918212890625,...  ...  0.147655   \n",
       "2527584  [{\"timeTick\":1293552183,\"X\":0.009918212890625,...  ...  0.096092   \n",
       "2527585  [{\"timeTick\":1293552183,\"X\":0.009918212890625,...  ...  0.114291   \n",
       "2527581  [{\"timeTick\":1293552183,\"X\":0.009918212890625,...  ...  0.164557   \n",
       "2527582  [{\"timeTick\":1293552183,\"X\":0.009918212890625,...  ... -0.000784   \n",
       "2527583  [{\"timeTick\":1293552183,\"X\":0.009918212890625,...  ...  0.147655   \n",
       "2527584  [{\"timeTick\":1293552183,\"X\":0.009918212890625,...  ...  0.096092   \n",
       "2527585  [{\"timeTick\":1293552183,\"X\":0.009918212890625,...  ...  0.114291   \n",
       "2527581  [{\"timeTick\":1293552183,\"X\":0.009918212890625,...  ...  0.164557   \n",
       "2527582  [{\"timeTick\":1293552183,\"X\":0.009918212890625,...  ... -0.000784   \n",
       "2527583  [{\"timeTick\":1293552183,\"X\":0.009918212890625,...  ...  0.147655   \n",
       "2527584  [{\"timeTick\":1293552183,\"X\":0.009918212890625,...  ...  0.096092   \n",
       "2527585  [{\"timeTick\":1293552183,\"X\":0.009918212890625,...  ...  0.114291   \n",
       "\n",
       "           gsen_Y    gsen_Z       subid    gyro_X    gyro_Y    gyro_Z  \\\n",
       "2527581 -0.301471  0.123606  36552501.1  0.009918 -0.000793 -0.009598   \n",
       "2527582 -0.221695  0.134298  36552501.2  0.013535  0.001862 -0.000229   \n",
       "2527583 -0.139551  0.141956  36552501.3  0.024506 -0.007614 -0.006302   \n",
       "2527584 -0.102794  0.083211  36552501.4  0.046234  0.004105 -0.013000   \n",
       "2527585  1.021729  0.693035  36552501.5  0.099609  0.004318 -0.014603   \n",
       "2527581 -0.301471  0.123606  36552501.1  0.009918 -0.000793 -0.009598   \n",
       "2527582 -0.221695  0.134298  36552501.2  0.013535  0.001862 -0.000229   \n",
       "2527583 -0.139551  0.141956  36552501.3  0.024506 -0.007614 -0.006302   \n",
       "2527584 -0.102794  0.083211  36552501.4  0.046234  0.004105 -0.013000   \n",
       "2527585  1.021729  0.693035  36552501.5  0.099609  0.004318 -0.014603   \n",
       "2527581 -0.301471  0.123606  36552501.1  0.009918 -0.000793 -0.009598   \n",
       "2527582 -0.221695  0.134298  36552501.2  0.013535  0.001862 -0.000229   \n",
       "2527583 -0.139551  0.141956  36552501.3  0.024506 -0.007614 -0.006302   \n",
       "2527584 -0.102794  0.083211  36552501.4  0.046234  0.004105 -0.013000   \n",
       "2527585  1.021729  0.693035  36552501.5  0.099609  0.004318 -0.014603   \n",
       "2527581 -0.301471  0.123606  36552501.1  0.009918 -0.000793 -0.009598   \n",
       "2527582 -0.221695  0.134298  36552501.2  0.013535  0.001862 -0.000229   \n",
       "2527583 -0.139551  0.141956  36552501.3  0.024506 -0.007614 -0.006302   \n",
       "2527584 -0.102794  0.083211  36552501.4  0.046234  0.004105 -0.013000   \n",
       "2527585  1.021729  0.693035  36552501.5  0.099609  0.004318 -0.014603   \n",
       "\n",
       "         unsafe_gsenX  unsafe_gsenY  unsafe_gsenZ  \n",
       "2527581         False         False         False  \n",
       "2527582         False         False         False  \n",
       "2527583         False         False         False  \n",
       "2527584         False         False         False  \n",
       "2527585         False         False         False  \n",
       "2527581         False         False         False  \n",
       "2527582         False         False         False  \n",
       "2527583         False         False         False  \n",
       "2527584         False         False         False  \n",
       "2527585         False         False         False  \n",
       "2527581         False         False         False  \n",
       "2527582         False         False         False  \n",
       "2527583         False         False         False  \n",
       "2527584         False         False         False  \n",
       "2527585         False         False         False  \n",
       "2527581         False         False         False  \n",
       "2527582         False         False         False  \n",
       "2527583         False         False         False  \n",
       "2527584         False         False         False  \n",
       "2527585         False         False         False  \n",
       "\n",
       "[20 rows x 21 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mask = df.groupby('id').id.count() == 20\n",
    "idx_ = df.groupby('id').id.count()[mask].index\n",
    "\n",
    "\n",
    "id = idx_[0]\n",
    "print(ts_c, len(df.query('id==@id').gsensor.values))\n",
    "df.query('id==@id')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "895b732f",
   "metadata": {},
   "source": [
    "\n",
    "- 用 \"id\"  做groupbyzp分組計算 sensor data資料數量，仍有 1 - 20 筆的變化\n",
    "    - 實際統計結果 \n",
    "        - [ 1,  2,  3,  4,  5,  6,  7,  8,  9, 10, 12, 15, 18, 20]   \n",
    "    - multi.py \n",
    "        - 已刪除過<10的資料\n",
    "        - gsensor與gyro資料數量已經對齊過"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5bd7172c",
   "metadata": {},
   "outputs": [],
   "source": [
    "    # data = data[data['speed'] > 3]\n",
    "    # data = data[\n",
    "    #     (data['unsafe_gsenX'] == True) | (data['unsafe_gsenY'] == True) | (data['unsafe_gsenZ'] == True)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46d2078b-92ad-4c56-b7df-6e3d961aebc9",
   "metadata": {},
   "source": [
    "## file saving"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "id": "3eeb7821-792b-497c-a02a-9cbbff5ca1b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# feather.write_dataframe(df_outlier.reset_index(drop=True), f'df_outlier_{filename}.feather')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c19e73e1-7067-4573-a7dd-900d35112b75",
   "metadata": {},
   "source": [
    "# Zooming"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "71b3f786-ada3-47cb-a845-1d036a764551",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# items = df_outlier.index\n",
    "\n",
    "# pic_n =1\n",
    "# df_event = pd.DataFrame()\n",
    "\n",
    "# for item in items:\n",
    "#     df_zoom = df[df.index == item].copy()\n",
    "# #     start = item - 1 # -1 index\n",
    "# #     end = item + 1 # +1 index\n",
    "# #     df_zoom = df[(df.index >= start) & (df.index <= end)].copy()\n",
    "    \n",
    "# #     # filtering critiria \n",
    "# #     if (int(df[df.index == item].timestamp - int(df[df.index == start].timestamp)) <= 950) | \\\n",
    "# #     (int(df[df.index == item].timestamp) - int(df[df.index == start].timestamp) >= 1000) | \\\n",
    "# #     (int(df[df.index == end].timestamp) - int(df[df.index == item].timestamp) <= 950) | \\\n",
    "# #     (int(df[df.index == end].timestamp) - int(df[df.index == item].timestamp) >= 1000) :\n",
    "# #         continue\n",
    "    \n",
    "#     # filter OUT more than 3 or less than 2 records within 3s\n",
    "#     # if (df_zoom.iloc[:,1].count() > 3) or (df_zoom.iloc[:,1].count() <= 1) :\n",
    "#     #     continue\n",
    "#     # # time range between the last and the first records must longer than 3s    \n",
    "#     # if (df_zoom['timestamp'].iloc[-1] - df_zoom['timestamp'].iloc[0]) < 3000 :\n",
    "#     #     continue\n",
    "        \n",
    "#     df_zoom.loc[:,'event_id'] = pic_n\n",
    "#     df_event = pd.concat([df_event, df_zoom], axis=0)\n",
    "    \n",
    "#     pic_n = pic_n + 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4504c934",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14404          2022-04-28 15:41:36\n",
       "14405          2022-04-28 15:41:36\n",
       "14406          2022-04-28 15:41:36\n",
       "14407          2022-04-28 15:41:36\n",
       "14408          2022-04-28 15:41:36\n",
       "                    ...           \n",
       "4107664    2022-10-03 12:17:28.965\n",
       "4107665    2022-10-03 12:17:28.965\n",
       "4107666    2022-10-03 12:17:28.965\n",
       "4107667    2022-10-03 12:17:28.965\n",
       "4107668    2022-10-03 12:17:28.965\n",
       "Name: timestamp_conv, Length: 40473, dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.timestamp_conv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "842c8faf",
   "metadata": {},
   "outputs": [],
   "source": [
    "#  items = data['timestamp_conv'].unique()\n",
    "\n",
    "def threading_item2(target):\n",
    "    pic_n = target[0]\n",
    "    item = target[1]\n",
    "    dataframe = target[2]\n",
    "    draw_list = []\n",
    "    start = item - np.timedelta64(5, 's')  # -5 sesonds\n",
    "    end = item + np.timedelta64(5, 's')  # +5 seconds\n",
    "    df_zoom = dataframe[(dataframe['timestamp_conv'] >= start) & (dataframe['timestamp_conv'] <= end)]\n",
    "    if df_zoom['timestamp_conv'].nunique() < 7:\n",
    "        return\n",
    "    # time range between the last and the first records must longer than 3s\n",
    "    if df_zoom['timestamp_conv'].iloc[-1] - df_zoom['timestamp_conv'].iloc[0] < timedelta(seconds=3):\n",
    "        return\n",
    "    df_dict = df_zoom.to_dict()\n",
    "    draw_list.append([pic_n, df_dict, 0])\n",
    "    return draw_list\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "6f1b560e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6446\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "items = df['timestamp_conv'].unique()\n",
    "print(len(items))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "38d4cb60",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "t_slice = 1\n",
    "\n",
    "draw_list = []\n",
    "\n",
    "for pic_n, t_stamp_ in enumerate(items):\n",
    "    start = t_stamp_ - np.timedelta64(t_slice, 's')  # - t_slice sesonds\n",
    "    end = t_stamp_ + np.timedelta64(t_slice, 's')  # + t_slice seconds\n",
    "    df_zoom = df[(df['timestamp_conv'] >= start) & (df['timestamp_conv'] <= end)]\n",
    "    # if df_zoom['timestamp_conv'].nunique() < 1:\n",
    "    #     continue\n",
    "    # # time range between the last and the first records must longer than 3s\n",
    "    # if df_zoom['timestamp_conv'].iloc[-1] - df_zoom['timestamp_conv'].iloc[0] < timedelta(seconds=3):\n",
    "    #     continue\n",
    "    df_dict = df_zoom.to_dict()\n",
    "    draw_list.append([pic_n, df_dict, 0])\n",
    "    \n",
    "    if pic_n==10:\n",
    "        break\n",
    "    \n",
    "len(draw_list)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "11768652",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'id': {21018: 14539026,\n",
       "  21019: 14539026,\n",
       "  21020: 14539026,\n",
       "  21021: 14539026,\n",
       "  21022: 14539026,\n",
       "  21023: 14539030,\n",
       "  21024: 14539030,\n",
       "  21025: 14539030,\n",
       "  21026: 14539030,\n",
       "  21027: 14539030},\n",
       " 'vehicle_id': {21018: 'KA04MW6569',\n",
       "  21019: 'KA04MW6569',\n",
       "  21020: 'KA04MW6569',\n",
       "  21021: 'KA04MW6569',\n",
       "  21022: 'KA04MW6569',\n",
       "  21023: 'KA04MW6569',\n",
       "  21024: 'KA04MW6569',\n",
       "  21025: 'KA04MW6569',\n",
       "  21026: 'KA04MW6569',\n",
       "  21027: 'KA04MW6569'},\n",
       " 'latitude': {21018: 12.935417,\n",
       "  21019: 12.935417,\n",
       "  21020: 12.935417,\n",
       "  21021: 12.935417,\n",
       "  21022: 12.935417,\n",
       "  21023: 12.9353945,\n",
       "  21024: 12.9353945,\n",
       "  21025: 12.9353945,\n",
       "  21026: 12.9353945,\n",
       "  21027: 12.9353945},\n",
       " 'longitude': {21018: 77.5362628,\n",
       "  21019: 77.5362628,\n",
       "  21020: 77.5362628,\n",
       "  21021: 77.5362628,\n",
       "  21022: 77.5362628,\n",
       "  21023: 77.5363113,\n",
       "  21024: 77.5363113,\n",
       "  21025: 77.5363113,\n",
       "  21026: 77.5363113,\n",
       "  21027: 77.5363113},\n",
       " 'speed': {21018: 5.12672233581543,\n",
       "  21019: 5.12672233581543,\n",
       "  21020: 5.12672233581543,\n",
       "  21021: 5.12672233581543,\n",
       "  21022: 5.12672233581543,\n",
       "  21023: 5.110618114471436,\n",
       "  21024: 5.110618114471436,\n",
       "  21025: 5.110618114471436,\n",
       "  21026: 5.110618114471436,\n",
       "  21027: 5.110618114471436},\n",
       " 'heading': {21018: 117.08984375,\n",
       "  21019: 117.08984375,\n",
       "  21020: 117.08984375,\n",
       "  21021: 117.08984375,\n",
       "  21022: 117.08984375,\n",
       "  21023: 116.01966857910156,\n",
       "  21024: 116.01966857910156,\n",
       "  21025: 116.01966857910156,\n",
       "  21026: 116.01966857910156,\n",
       "  21027: 116.01966857910156},\n",
       " 'acc_signal': {21018: 1,\n",
       "  21019: 1,\n",
       "  21020: 1,\n",
       "  21021: 1,\n",
       "  21022: 1,\n",
       "  21023: 1,\n",
       "  21024: 1,\n",
       "  21025: 1,\n",
       "  21026: 1,\n",
       "  21027: 1},\n",
       " 'timestamp': {21018: 1651161828000,\n",
       "  21019: 1651161828000,\n",
       "  21020: 1651161828000,\n",
       "  21021: 1651161828000,\n",
       "  21022: 1651161828000,\n",
       "  21023: 1651161829000,\n",
       "  21024: 1651161829000,\n",
       "  21025: 1651161829000,\n",
       "  21026: 1651161829000,\n",
       "  21027: 1651161829000},\n",
       " 'gsensor': {21018: '[{\"timeTick\":5265039,\"X\":-0.0822439193725586,\"Y\":1.4507970809936523,\"Z\":-0.8814692497253418},{\"timeTick\":5265239,\"X\":0.575592041015625,\"Y\":2.322385311126709,\"Z\":-0.9777116775512695},{\"timeTick\":5265439,\"X\":0.5894012451171875,\"Y\":2.8561601638793945,\"Z\":-0.9564275741577148},{\"timeTick\":5265639,\"X\":-2.551581859588623,\"Y\":-0.3223743438720703,\"Z\":-2.3409876823425293},{\"timeTick\":5265841,\"X\":-0.483431339263916,\"Y\":0.31670331954956055,\"Z\":-1.203639030456543}]',\n",
       "  21019: '[{\"timeTick\":5265039,\"X\":-0.0822439193725586,\"Y\":1.4507970809936523,\"Z\":-0.8814692497253418},{\"timeTick\":5265239,\"X\":0.575592041015625,\"Y\":2.322385311126709,\"Z\":-0.9777116775512695},{\"timeTick\":5265439,\"X\":0.5894012451171875,\"Y\":2.8561601638793945,\"Z\":-0.9564275741577148},{\"timeTick\":5265639,\"X\":-2.551581859588623,\"Y\":-0.3223743438720703,\"Z\":-2.3409876823425293},{\"timeTick\":5265841,\"X\":-0.483431339263916,\"Y\":0.31670331954956055,\"Z\":-1.203639030456543}]',\n",
       "  21020: '[{\"timeTick\":5265039,\"X\":-0.0822439193725586,\"Y\":1.4507970809936523,\"Z\":-0.8814692497253418},{\"timeTick\":5265239,\"X\":0.575592041015625,\"Y\":2.322385311126709,\"Z\":-0.9777116775512695},{\"timeTick\":5265439,\"X\":0.5894012451171875,\"Y\":2.8561601638793945,\"Z\":-0.9564275741577148},{\"timeTick\":5265639,\"X\":-2.551581859588623,\"Y\":-0.3223743438720703,\"Z\":-2.3409876823425293},{\"timeTick\":5265841,\"X\":-0.483431339263916,\"Y\":0.31670331954956055,\"Z\":-1.203639030456543}]',\n",
       "  21021: '[{\"timeTick\":5265039,\"X\":-0.0822439193725586,\"Y\":1.4507970809936523,\"Z\":-0.8814692497253418},{\"timeTick\":5265239,\"X\":0.575592041015625,\"Y\":2.322385311126709,\"Z\":-0.9777116775512695},{\"timeTick\":5265439,\"X\":0.5894012451171875,\"Y\":2.8561601638793945,\"Z\":-0.9564275741577148},{\"timeTick\":5265639,\"X\":-2.551581859588623,\"Y\":-0.3223743438720703,\"Z\":-2.3409876823425293},{\"timeTick\":5265841,\"X\":-0.483431339263916,\"Y\":0.31670331954956055,\"Z\":-1.203639030456543}]',\n",
       "  21022: '[{\"timeTick\":5265039,\"X\":-0.0822439193725586,\"Y\":1.4507970809936523,\"Z\":-0.8814692497253418},{\"timeTick\":5265239,\"X\":0.575592041015625,\"Y\":2.322385311126709,\"Z\":-0.9777116775512695},{\"timeTick\":5265439,\"X\":0.5894012451171875,\"Y\":2.8561601638793945,\"Z\":-0.9564275741577148},{\"timeTick\":5265639,\"X\":-2.551581859588623,\"Y\":-0.3223743438720703,\"Z\":-2.3409876823425293},{\"timeTick\":5265841,\"X\":-0.483431339263916,\"Y\":0.31670331954956055,\"Z\":-1.203639030456543}]',\n",
       "  21023: '[{\"timeTick\":5266039,\"X\":1.094522476196289,\"Y\":1.821171760559082,\"Z\":0.4468827247619629},{\"timeTick\":5266240,\"X\":-1.1376333236694336,\"Y\":1.0949954986572266,\"Z\":-1.3879408836364746},{\"timeTick\":5266439,\"X\":0.04625749588012695,\"Y\":0.37151575088500977,\"Z\":-0.9896121025085449},{\"timeTick\":5266639,\"X\":0.012919902801513672,\"Y\":0.7288427352905273,\"Z\":0.04639577865600586},{\"timeTick\":5266839,\"X\":-0.48552942276000977,\"Y\":0.9060540199279785,\"Z\":-0.6650362014770508}]',\n",
       "  21024: '[{\"timeTick\":5266039,\"X\":1.094522476196289,\"Y\":1.821171760559082,\"Z\":0.4468827247619629},{\"timeTick\":5266240,\"X\":-1.1376333236694336,\"Y\":1.0949954986572266,\"Z\":-1.3879408836364746},{\"timeTick\":5266439,\"X\":0.04625749588012695,\"Y\":0.37151575088500977,\"Z\":-0.9896121025085449},{\"timeTick\":5266639,\"X\":0.012919902801513672,\"Y\":0.7288427352905273,\"Z\":0.04639577865600586},{\"timeTick\":5266839,\"X\":-0.48552942276000977,\"Y\":0.9060540199279785,\"Z\":-0.6650362014770508}]',\n",
       "  21025: '[{\"timeTick\":5266039,\"X\":1.094522476196289,\"Y\":1.821171760559082,\"Z\":0.4468827247619629},{\"timeTick\":5266240,\"X\":-1.1376333236694336,\"Y\":1.0949954986572266,\"Z\":-1.3879408836364746},{\"timeTick\":5266439,\"X\":0.04625749588012695,\"Y\":0.37151575088500977,\"Z\":-0.9896121025085449},{\"timeTick\":5266639,\"X\":0.012919902801513672,\"Y\":0.7288427352905273,\"Z\":0.04639577865600586},{\"timeTick\":5266839,\"X\":-0.48552942276000977,\"Y\":0.9060540199279785,\"Z\":-0.6650362014770508}]',\n",
       "  21026: '[{\"timeTick\":5266039,\"X\":1.094522476196289,\"Y\":1.821171760559082,\"Z\":0.4468827247619629},{\"timeTick\":5266240,\"X\":-1.1376333236694336,\"Y\":1.0949954986572266,\"Z\":-1.3879408836364746},{\"timeTick\":5266439,\"X\":0.04625749588012695,\"Y\":0.37151575088500977,\"Z\":-0.9896121025085449},{\"timeTick\":5266639,\"X\":0.012919902801513672,\"Y\":0.7288427352905273,\"Z\":0.04639577865600586},{\"timeTick\":5266839,\"X\":-0.48552942276000977,\"Y\":0.9060540199279785,\"Z\":-0.6650362014770508}]',\n",
       "  21027: '[{\"timeTick\":5266039,\"X\":1.094522476196289,\"Y\":1.821171760559082,\"Z\":0.4468827247619629},{\"timeTick\":5266240,\"X\":-1.1376333236694336,\"Y\":1.0949954986572266,\"Z\":-1.3879408836364746},{\"timeTick\":5266439,\"X\":0.04625749588012695,\"Y\":0.37151575088500977,\"Z\":-0.9896121025085449},{\"timeTick\":5266639,\"X\":0.012919902801513672,\"Y\":0.7288427352905273,\"Z\":0.04639577865600586},{\"timeTick\":5266839,\"X\":-0.48552942276000977,\"Y\":0.9060540199279785,\"Z\":-0.6650362014770508}]'},\n",
       " 'gyro': {21018: '[{\"timeTick\":5265039,\"X\":-0.00714111328125,\"Y\":-0.0016632080078125,\"Z\":0.0081939697265625},{\"timeTick\":5265240,\"X\":0.1106719970703125,\"Y\":-0.0815582275390625,\"Z\":-0.086090087890625},{\"timeTick\":5265439,\"X\":0.150421142578125,\"Y\":-0.0194549560546875,\"Z\":-0.1387176513671875},{\"timeTick\":5265638,\"X\":-0.0563507080078125,\"Y\":0.004302978515625,\"Z\":0.047821044921875},{\"timeTick\":5265839,\"X\":-0.122406005859375,\"Y\":0.056610107421875,\"Z\":0.1382598876953125}]',\n",
       "  21019: '[{\"timeTick\":5265039,\"X\":-0.00714111328125,\"Y\":-0.0016632080078125,\"Z\":0.0081939697265625},{\"timeTick\":5265240,\"X\":0.1106719970703125,\"Y\":-0.0815582275390625,\"Z\":-0.086090087890625},{\"timeTick\":5265439,\"X\":0.150421142578125,\"Y\":-0.0194549560546875,\"Z\":-0.1387176513671875},{\"timeTick\":5265638,\"X\":-0.0563507080078125,\"Y\":0.004302978515625,\"Z\":0.047821044921875},{\"timeTick\":5265839,\"X\":-0.122406005859375,\"Y\":0.056610107421875,\"Z\":0.1382598876953125}]',\n",
       "  21020: '[{\"timeTick\":5265039,\"X\":-0.00714111328125,\"Y\":-0.0016632080078125,\"Z\":0.0081939697265625},{\"timeTick\":5265240,\"X\":0.1106719970703125,\"Y\":-0.0815582275390625,\"Z\":-0.086090087890625},{\"timeTick\":5265439,\"X\":0.150421142578125,\"Y\":-0.0194549560546875,\"Z\":-0.1387176513671875},{\"timeTick\":5265638,\"X\":-0.0563507080078125,\"Y\":0.004302978515625,\"Z\":0.047821044921875},{\"timeTick\":5265839,\"X\":-0.122406005859375,\"Y\":0.056610107421875,\"Z\":0.1382598876953125}]',\n",
       "  21021: '[{\"timeTick\":5265039,\"X\":-0.00714111328125,\"Y\":-0.0016632080078125,\"Z\":0.0081939697265625},{\"timeTick\":5265240,\"X\":0.1106719970703125,\"Y\":-0.0815582275390625,\"Z\":-0.086090087890625},{\"timeTick\":5265439,\"X\":0.150421142578125,\"Y\":-0.0194549560546875,\"Z\":-0.1387176513671875},{\"timeTick\":5265638,\"X\":-0.0563507080078125,\"Y\":0.004302978515625,\"Z\":0.047821044921875},{\"timeTick\":5265839,\"X\":-0.122406005859375,\"Y\":0.056610107421875,\"Z\":0.1382598876953125}]',\n",
       "  21022: '[{\"timeTick\":5265039,\"X\":-0.00714111328125,\"Y\":-0.0016632080078125,\"Z\":0.0081939697265625},{\"timeTick\":5265240,\"X\":0.1106719970703125,\"Y\":-0.0815582275390625,\"Z\":-0.086090087890625},{\"timeTick\":5265439,\"X\":0.150421142578125,\"Y\":-0.0194549560546875,\"Z\":-0.1387176513671875},{\"timeTick\":5265638,\"X\":-0.0563507080078125,\"Y\":0.004302978515625,\"Z\":0.047821044921875},{\"timeTick\":5265839,\"X\":-0.122406005859375,\"Y\":0.056610107421875,\"Z\":0.1382598876953125}]',\n",
       "  21023: '[{\"timeTick\":5266038,\"X\":-0.1475372314453125,\"Y\":0.1324462890625,\"Z\":0.1013946533203125},{\"timeTick\":5266239,\"X\":0.14007568359375,\"Y\":-0.0735626220703125,\"Z\":-0.121551513671875},{\"timeTick\":5266439,\"X\":-0.0254669189453125,\"Y\":-0.0085906982421875,\"Z\":0.05389404296875},{\"timeTick\":5266641,\"X\":-0.0363311767578125,\"Y\":0.0541534423828125,\"Z\":0.0104217529296875},{\"timeTick\":5266839,\"X\":0.05303955078125,\"Y\":-0.0030517578125,\"Z\":-0.047943115234375}]',\n",
       "  21024: '[{\"timeTick\":5266038,\"X\":-0.1475372314453125,\"Y\":0.1324462890625,\"Z\":0.1013946533203125},{\"timeTick\":5266239,\"X\":0.14007568359375,\"Y\":-0.0735626220703125,\"Z\":-0.121551513671875},{\"timeTick\":5266439,\"X\":-0.0254669189453125,\"Y\":-0.0085906982421875,\"Z\":0.05389404296875},{\"timeTick\":5266641,\"X\":-0.0363311767578125,\"Y\":0.0541534423828125,\"Z\":0.0104217529296875},{\"timeTick\":5266839,\"X\":0.05303955078125,\"Y\":-0.0030517578125,\"Z\":-0.047943115234375}]',\n",
       "  21025: '[{\"timeTick\":5266038,\"X\":-0.1475372314453125,\"Y\":0.1324462890625,\"Z\":0.1013946533203125},{\"timeTick\":5266239,\"X\":0.14007568359375,\"Y\":-0.0735626220703125,\"Z\":-0.121551513671875},{\"timeTick\":5266439,\"X\":-0.0254669189453125,\"Y\":-0.0085906982421875,\"Z\":0.05389404296875},{\"timeTick\":5266641,\"X\":-0.0363311767578125,\"Y\":0.0541534423828125,\"Z\":0.0104217529296875},{\"timeTick\":5266839,\"X\":0.05303955078125,\"Y\":-0.0030517578125,\"Z\":-0.047943115234375}]',\n",
       "  21026: '[{\"timeTick\":5266038,\"X\":-0.1475372314453125,\"Y\":0.1324462890625,\"Z\":0.1013946533203125},{\"timeTick\":5266239,\"X\":0.14007568359375,\"Y\":-0.0735626220703125,\"Z\":-0.121551513671875},{\"timeTick\":5266439,\"X\":-0.0254669189453125,\"Y\":-0.0085906982421875,\"Z\":0.05389404296875},{\"timeTick\":5266641,\"X\":-0.0363311767578125,\"Y\":0.0541534423828125,\"Z\":0.0104217529296875},{\"timeTick\":5266839,\"X\":0.05303955078125,\"Y\":-0.0030517578125,\"Z\":-0.047943115234375}]',\n",
       "  21027: '[{\"timeTick\":5266038,\"X\":-0.1475372314453125,\"Y\":0.1324462890625,\"Z\":0.1013946533203125},{\"timeTick\":5266239,\"X\":0.14007568359375,\"Y\":-0.0735626220703125,\"Z\":-0.121551513671875},{\"timeTick\":5266439,\"X\":-0.0254669189453125,\"Y\":-0.0085906982421875,\"Z\":0.05389404296875},{\"timeTick\":5266641,\"X\":-0.0363311767578125,\"Y\":0.0541534423828125,\"Z\":0.0104217529296875},{\"timeTick\":5266839,\"X\":0.05303955078125,\"Y\":-0.0030517578125,\"Z\":-0.047943115234375}]'},\n",
       " 'timestamp_conv': {21018: Timestamp('2022-04-28 16:03:48'),\n",
       "  21019: Timestamp('2022-04-28 16:03:48'),\n",
       "  21020: Timestamp('2022-04-28 16:03:48'),\n",
       "  21021: Timestamp('2022-04-28 16:03:48'),\n",
       "  21022: Timestamp('2022-04-28 16:03:48'),\n",
       "  21023: Timestamp('2022-04-28 16:03:49'),\n",
       "  21024: Timestamp('2022-04-28 16:03:49'),\n",
       "  21025: Timestamp('2022-04-28 16:03:49'),\n",
       "  21026: Timestamp('2022-04-28 16:03:49'),\n",
       "  21027: Timestamp('2022-04-28 16:03:49')},\n",
       " 'gsen_X': {21018: -0.0822439193725586,\n",
       "  21019: 0.575592041015625,\n",
       "  21020: 0.5894012451171875,\n",
       "  21021: -2.551581859588623,\n",
       "  21022: -0.483431339263916,\n",
       "  21023: 1.094522476196289,\n",
       "  21024: -1.1376333236694336,\n",
       "  21025: 0.0462574958801269,\n",
       "  21026: 0.0129199028015136,\n",
       "  21027: -0.4855294227600097},\n",
       " 'gsen_Y': {21018: 1.4507970809936523,\n",
       "  21019: 2.322385311126709,\n",
       "  21020: 2.8561601638793945,\n",
       "  21021: -0.3223743438720703,\n",
       "  21022: 0.3167033195495605,\n",
       "  21023: 1.821171760559082,\n",
       "  21024: 1.0949954986572266,\n",
       "  21025: 0.3715157508850097,\n",
       "  21026: 0.7288427352905273,\n",
       "  21027: 0.9060540199279784},\n",
       " 'gsen_Z': {21018: -0.8814692497253418,\n",
       "  21019: -0.9777116775512696,\n",
       "  21020: -0.9564275741577148,\n",
       "  21021: -2.3409876823425293,\n",
       "  21022: -1.203639030456543,\n",
       "  21023: 0.4468827247619629,\n",
       "  21024: -1.3879408836364746,\n",
       "  21025: -0.9896121025085448,\n",
       "  21026: 0.0463957786560058,\n",
       "  21027: -0.6650362014770508},\n",
       " 'subid': {21018: 14539026.1,\n",
       "  21019: 14539026.2,\n",
       "  21020: 14539026.3,\n",
       "  21021: 14539026.4,\n",
       "  21022: 14539026.5,\n",
       "  21023: 14539030.1,\n",
       "  21024: 14539030.2,\n",
       "  21025: 14539030.3,\n",
       "  21026: 14539030.4,\n",
       "  21027: 14539030.5},\n",
       " 'gyro_X': {21018: -0.00714111328125,\n",
       "  21019: 0.1106719970703125,\n",
       "  21020: 0.150421142578125,\n",
       "  21021: -0.0563507080078125,\n",
       "  21022: -0.122406005859375,\n",
       "  21023: -0.1475372314453125,\n",
       "  21024: 0.14007568359375,\n",
       "  21025: -0.0254669189453125,\n",
       "  21026: -0.0363311767578125,\n",
       "  21027: 0.05303955078125},\n",
       " 'gyro_Y': {21018: -0.0016632080078125,\n",
       "  21019: -0.0815582275390625,\n",
       "  21020: -0.0194549560546875,\n",
       "  21021: 0.004302978515625,\n",
       "  21022: 0.056610107421875,\n",
       "  21023: 0.1324462890625,\n",
       "  21024: -0.0735626220703125,\n",
       "  21025: -0.0085906982421875,\n",
       "  21026: 0.0541534423828125,\n",
       "  21027: -0.0030517578125},\n",
       " 'gyro_Z': {21018: 0.0081939697265625,\n",
       "  21019: -0.086090087890625,\n",
       "  21020: -0.1387176513671875,\n",
       "  21021: 0.047821044921875,\n",
       "  21022: 0.1382598876953125,\n",
       "  21023: 0.1013946533203125,\n",
       "  21024: -0.121551513671875,\n",
       "  21025: 0.05389404296875,\n",
       "  21026: 0.0104217529296875,\n",
       "  21027: -0.047943115234375},\n",
       " 'unsafe_gsenX': {21018: False,\n",
       "  21019: False,\n",
       "  21020: False,\n",
       "  21021: False,\n",
       "  21022: False,\n",
       "  21023: False,\n",
       "  21024: False,\n",
       "  21025: False,\n",
       "  21026: False,\n",
       "  21027: False},\n",
       " 'unsafe_gsenY': {21018: False,\n",
       "  21019: False,\n",
       "  21020: False,\n",
       "  21021: False,\n",
       "  21022: False,\n",
       "  21023: False,\n",
       "  21024: False,\n",
       "  21025: False,\n",
       "  21026: False,\n",
       "  21027: False},\n",
       " 'unsafe_gsenZ': {21018: False,\n",
       "  21019: False,\n",
       "  21020: False,\n",
       "  21021: False,\n",
       "  21022: False,\n",
       "  21023: False,\n",
       "  21024: False,\n",
       "  21025: False,\n",
       "  21026: False,\n",
       "  21027: False}}"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "draw_list[-1][1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "3767d171",
   "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>vehicle_id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>speed</th>\n",
       "      <th>heading</th>\n",
       "      <th>acc_signal</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>gsensor</th>\n",
       "      <th>gyro</th>\n",
       "      <th>...</th>\n",
       "      <th>gsen_X</th>\n",
       "      <th>gsen_Y</th>\n",
       "      <th>gsen_Z</th>\n",
       "      <th>subid</th>\n",
       "      <th>gyro_X</th>\n",
       "      <th>gyro_Y</th>\n",
       "      <th>gyro_Z</th>\n",
       "      <th>unsafe_gsenX</th>\n",
       "      <th>unsafe_gsenY</th>\n",
       "      <th>unsafe_gsenZ</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>21018</th>\n",
       "      <td>14539026</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>12.935417</td>\n",
       "      <td>77.536263</td>\n",
       "      <td>5.126722</td>\n",
       "      <td>117.089844</td>\n",
       "      <td>1</td>\n",
       "      <td>1651161828000</td>\n",
       "      <td>[{\"timeTick\":5265039,\"X\":-0.0822439193725586,\"...</td>\n",
       "      <td>[{\"timeTick\":5265039,\"X\":-0.00714111328125,\"Y\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.082244</td>\n",
       "      <td>1.450797</td>\n",
       "      <td>-0.881469</td>\n",
       "      <td>14539026.1</td>\n",
       "      <td>-0.007141</td>\n",
       "      <td>-0.001663</td>\n",
       "      <td>0.008194</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21019</th>\n",
       "      <td>14539026</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>12.935417</td>\n",
       "      <td>77.536263</td>\n",
       "      <td>5.126722</td>\n",
       "      <td>117.089844</td>\n",
       "      <td>1</td>\n",
       "      <td>1651161828000</td>\n",
       "      <td>[{\"timeTick\":5265039,\"X\":-0.0822439193725586,\"...</td>\n",
       "      <td>[{\"timeTick\":5265039,\"X\":-0.00714111328125,\"Y\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.575592</td>\n",
       "      <td>2.322385</td>\n",
       "      <td>-0.977712</td>\n",
       "      <td>14539026.2</td>\n",
       "      <td>0.110672</td>\n",
       "      <td>-0.081558</td>\n",
       "      <td>-0.086090</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21020</th>\n",
       "      <td>14539026</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>12.935417</td>\n",
       "      <td>77.536263</td>\n",
       "      <td>5.126722</td>\n",
       "      <td>117.089844</td>\n",
       "      <td>1</td>\n",
       "      <td>1651161828000</td>\n",
       "      <td>[{\"timeTick\":5265039,\"X\":-0.0822439193725586,\"...</td>\n",
       "      <td>[{\"timeTick\":5265039,\"X\":-0.00714111328125,\"Y\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.589401</td>\n",
       "      <td>2.856160</td>\n",
       "      <td>-0.956428</td>\n",
       "      <td>14539026.3</td>\n",
       "      <td>0.150421</td>\n",
       "      <td>-0.019455</td>\n",
       "      <td>-0.138718</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21021</th>\n",
       "      <td>14539026</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>12.935417</td>\n",
       "      <td>77.536263</td>\n",
       "      <td>5.126722</td>\n",
       "      <td>117.089844</td>\n",
       "      <td>1</td>\n",
       "      <td>1651161828000</td>\n",
       "      <td>[{\"timeTick\":5265039,\"X\":-0.0822439193725586,\"...</td>\n",
       "      <td>[{\"timeTick\":5265039,\"X\":-0.00714111328125,\"Y\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>-2.551582</td>\n",
       "      <td>-0.322374</td>\n",
       "      <td>-2.340988</td>\n",
       "      <td>14539026.4</td>\n",
       "      <td>-0.056351</td>\n",
       "      <td>0.004303</td>\n",
       "      <td>0.047821</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21022</th>\n",
       "      <td>14539026</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>12.935417</td>\n",
       "      <td>77.536263</td>\n",
       "      <td>5.126722</td>\n",
       "      <td>117.089844</td>\n",
       "      <td>1</td>\n",
       "      <td>1651161828000</td>\n",
       "      <td>[{\"timeTick\":5265039,\"X\":-0.0822439193725586,\"...</td>\n",
       "      <td>[{\"timeTick\":5265039,\"X\":-0.00714111328125,\"Y\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.483431</td>\n",
       "      <td>0.316703</td>\n",
       "      <td>-1.203639</td>\n",
       "      <td>14539026.5</td>\n",
       "      <td>-0.122406</td>\n",
       "      <td>0.056610</td>\n",
       "      <td>0.138260</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21023</th>\n",
       "      <td>14539030</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>12.935394</td>\n",
       "      <td>77.536311</td>\n",
       "      <td>5.110618</td>\n",
       "      <td>116.019669</td>\n",
       "      <td>1</td>\n",
       "      <td>1651161829000</td>\n",
       "      <td>[{\"timeTick\":5266039,\"X\":1.094522476196289,\"Y\"...</td>\n",
       "      <td>[{\"timeTick\":5266038,\"X\":-0.1475372314453125,\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>1.094522</td>\n",
       "      <td>1.821172</td>\n",
       "      <td>0.446883</td>\n",
       "      <td>14539030.1</td>\n",
       "      <td>-0.147537</td>\n",
       "      <td>0.132446</td>\n",
       "      <td>0.101395</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21024</th>\n",
       "      <td>14539030</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>12.935394</td>\n",
       "      <td>77.536311</td>\n",
       "      <td>5.110618</td>\n",
       "      <td>116.019669</td>\n",
       "      <td>1</td>\n",
       "      <td>1651161829000</td>\n",
       "      <td>[{\"timeTick\":5266039,\"X\":1.094522476196289,\"Y\"...</td>\n",
       "      <td>[{\"timeTick\":5266038,\"X\":-0.1475372314453125,\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.137633</td>\n",
       "      <td>1.094995</td>\n",
       "      <td>-1.387941</td>\n",
       "      <td>14539030.2</td>\n",
       "      <td>0.140076</td>\n",
       "      <td>-0.073563</td>\n",
       "      <td>-0.121552</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21025</th>\n",
       "      <td>14539030</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>12.935394</td>\n",
       "      <td>77.536311</td>\n",
       "      <td>5.110618</td>\n",
       "      <td>116.019669</td>\n",
       "      <td>1</td>\n",
       "      <td>1651161829000</td>\n",
       "      <td>[{\"timeTick\":5266039,\"X\":1.094522476196289,\"Y\"...</td>\n",
       "      <td>[{\"timeTick\":5266038,\"X\":-0.1475372314453125,\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.046257</td>\n",
       "      <td>0.371516</td>\n",
       "      <td>-0.989612</td>\n",
       "      <td>14539030.3</td>\n",
       "      <td>-0.025467</td>\n",
       "      <td>-0.008591</td>\n",
       "      <td>0.053894</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21026</th>\n",
       "      <td>14539030</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>12.935394</td>\n",
       "      <td>77.536311</td>\n",
       "      <td>5.110618</td>\n",
       "      <td>116.019669</td>\n",
       "      <td>1</td>\n",
       "      <td>1651161829000</td>\n",
       "      <td>[{\"timeTick\":5266039,\"X\":1.094522476196289,\"Y\"...</td>\n",
       "      <td>[{\"timeTick\":5266038,\"X\":-0.1475372314453125,\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>0.012920</td>\n",
       "      <td>0.728843</td>\n",
       "      <td>0.046396</td>\n",
       "      <td>14539030.4</td>\n",
       "      <td>-0.036331</td>\n",
       "      <td>0.054153</td>\n",
       "      <td>0.010422</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21027</th>\n",
       "      <td>14539030</td>\n",
       "      <td>KA04MW6569</td>\n",
       "      <td>12.935394</td>\n",
       "      <td>77.536311</td>\n",
       "      <td>5.110618</td>\n",
       "      <td>116.019669</td>\n",
       "      <td>1</td>\n",
       "      <td>1651161829000</td>\n",
       "      <td>[{\"timeTick\":5266039,\"X\":1.094522476196289,\"Y\"...</td>\n",
       "      <td>[{\"timeTick\":5266038,\"X\":-0.1475372314453125,\"...</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.485529</td>\n",
       "      <td>0.906054</td>\n",
       "      <td>-0.665036</td>\n",
       "      <td>14539030.5</td>\n",
       "      <td>0.053040</td>\n",
       "      <td>-0.003052</td>\n",
       "      <td>-0.047943</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             id  vehicle_id   latitude  longitude     speed     heading  \\\n",
       "21018  14539026  KA04MW6569  12.935417  77.536263  5.126722  117.089844   \n",
       "21019  14539026  KA04MW6569  12.935417  77.536263  5.126722  117.089844   \n",
       "21020  14539026  KA04MW6569  12.935417  77.536263  5.126722  117.089844   \n",
       "21021  14539026  KA04MW6569  12.935417  77.536263  5.126722  117.089844   \n",
       "21022  14539026  KA04MW6569  12.935417  77.536263  5.126722  117.089844   \n",
       "21023  14539030  KA04MW6569  12.935394  77.536311  5.110618  116.019669   \n",
       "21024  14539030  KA04MW6569  12.935394  77.536311  5.110618  116.019669   \n",
       "21025  14539030  KA04MW6569  12.935394  77.536311  5.110618  116.019669   \n",
       "21026  14539030  KA04MW6569  12.935394  77.536311  5.110618  116.019669   \n",
       "21027  14539030  KA04MW6569  12.935394  77.536311  5.110618  116.019669   \n",
       "\n",
       "       acc_signal      timestamp  \\\n",
       "21018           1  1651161828000   \n",
       "21019           1  1651161828000   \n",
       "21020           1  1651161828000   \n",
       "21021           1  1651161828000   \n",
       "21022           1  1651161828000   \n",
       "21023           1  1651161829000   \n",
       "21024           1  1651161829000   \n",
       "21025           1  1651161829000   \n",
       "21026           1  1651161829000   \n",
       "21027           1  1651161829000   \n",
       "\n",
       "                                                 gsensor  \\\n",
       "21018  [{\"timeTick\":5265039,\"X\":-0.0822439193725586,\"...   \n",
       "21019  [{\"timeTick\":5265039,\"X\":-0.0822439193725586,\"...   \n",
       "21020  [{\"timeTick\":5265039,\"X\":-0.0822439193725586,\"...   \n",
       "21021  [{\"timeTick\":5265039,\"X\":-0.0822439193725586,\"...   \n",
       "21022  [{\"timeTick\":5265039,\"X\":-0.0822439193725586,\"...   \n",
       "21023  [{\"timeTick\":5266039,\"X\":1.094522476196289,\"Y\"...   \n",
       "21024  [{\"timeTick\":5266039,\"X\":1.094522476196289,\"Y\"...   \n",
       "21025  [{\"timeTick\":5266039,\"X\":1.094522476196289,\"Y\"...   \n",
       "21026  [{\"timeTick\":5266039,\"X\":1.094522476196289,\"Y\"...   \n",
       "21027  [{\"timeTick\":5266039,\"X\":1.094522476196289,\"Y\"...   \n",
       "\n",
       "                                                    gyro  ...    gsen_X  \\\n",
       "21018  [{\"timeTick\":5265039,\"X\":-0.00714111328125,\"Y\"...  ... -0.082244   \n",
       "21019  [{\"timeTick\":5265039,\"X\":-0.00714111328125,\"Y\"...  ...  0.575592   \n",
       "21020  [{\"timeTick\":5265039,\"X\":-0.00714111328125,\"Y\"...  ...  0.589401   \n",
       "21021  [{\"timeTick\":5265039,\"X\":-0.00714111328125,\"Y\"...  ... -2.551582   \n",
       "21022  [{\"timeTick\":5265039,\"X\":-0.00714111328125,\"Y\"...  ... -0.483431   \n",
       "21023  [{\"timeTick\":5266038,\"X\":-0.1475372314453125,\"...  ...  1.094522   \n",
       "21024  [{\"timeTick\":5266038,\"X\":-0.1475372314453125,\"...  ... -1.137633   \n",
       "21025  [{\"timeTick\":5266038,\"X\":-0.1475372314453125,\"...  ...  0.046257   \n",
       "21026  [{\"timeTick\":5266038,\"X\":-0.1475372314453125,\"...  ...  0.012920   \n",
       "21027  [{\"timeTick\":5266038,\"X\":-0.1475372314453125,\"...  ... -0.485529   \n",
       "\n",
       "         gsen_Y    gsen_Z       subid    gyro_X    gyro_Y    gyro_Z  \\\n",
       "21018  1.450797 -0.881469  14539026.1 -0.007141 -0.001663  0.008194   \n",
       "21019  2.322385 -0.977712  14539026.2  0.110672 -0.081558 -0.086090   \n",
       "21020  2.856160 -0.956428  14539026.3  0.150421 -0.019455 -0.138718   \n",
       "21021 -0.322374 -2.340988  14539026.4 -0.056351  0.004303  0.047821   \n",
       "21022  0.316703 -1.203639  14539026.5 -0.122406  0.056610  0.138260   \n",
       "21023  1.821172  0.446883  14539030.1 -0.147537  0.132446  0.101395   \n",
       "21024  1.094995 -1.387941  14539030.2  0.140076 -0.073563 -0.121552   \n",
       "21025  0.371516 -0.989612  14539030.3 -0.025467 -0.008591  0.053894   \n",
       "21026  0.728843  0.046396  14539030.4 -0.036331  0.054153  0.010422   \n",
       "21027  0.906054 -0.665036  14539030.5  0.053040 -0.003052 -0.047943   \n",
       "\n",
       "       unsafe_gsenX  unsafe_gsenY  unsafe_gsenZ  \n",
       "21018         False         False         False  \n",
       "21019         False         False         False  \n",
       "21020         False         False         False  \n",
       "21021         False         False         False  \n",
       "21022         False         False         False  \n",
       "21023         False         False         False  \n",
       "21024         False         False         False  \n",
       "21025         False         False         False  \n",
       "21026         False         False         False  \n",
       "21027         False         False         False  \n",
       "\n",
       "[10 rows x 21 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_zoom"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "id": "57fe08d5-0af7-416f-ac23-9d934fac81d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "items = df_outlier['timestamp'].unique()\n",
    "\n",
    "pic_n = 1\n",
    "df_event = pd.DataFrame()\n",
    "\n",
    "for item in items:\n",
    "    start = item - 1000 # -1 sesonds\n",
    "    end = item + 1000 # +1 seconds\n",
    "    df_zoom = df[(df['timestamp'] >= start) & (df['timestamp'] <= end)].copy()\n",
    "    \n",
    "    # filtering critiria \n",
    "    # filter OUT more than 3 or less than 2 records within 3s\n",
    "    if (df_zoom.iloc[:,1].count() > 3) or (df_zoom.iloc[:,1].count() <= 1) :\n",
    "        continue\n",
    "    # # time range between the last and the first records must longer than 3s    \n",
    "    # if (df_zoom['timestamp'].iloc[-1] - df_zoom['timestamp'].iloc[0]) < 3000 :\n",
    "    #     continue\n",
    "        \n",
    "    df_zoom.loc[:,'event_id'] = pic_n\n",
    "    df_event = pd.concat([df_event, df_zoom], axis=0)\n",
    "    \n",
    "    pic_n = pic_n + 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "id": "1b736e29",
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df_event"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c206a9aa-5cc1-4acd-a480-7675aa843060",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2116"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_event)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70f88296-7a4a-45c2-8017-b8291cead2dd",
   "metadata": {},
   "source": [
    "## file saving"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "70890f54-b1ad-4c31-876f-146bc6401ccd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# feather.write_dataframe(df_event.reset_index(drop=True), f'df_event_{filename}.feather')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80ac510a-2e96-4902-adbd-84ffd35e7bb1",
   "metadata": {},
   "source": [
    "# Stats making"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "98e86700-646a-4cc4-b5ba-0d8cfdcc7f39",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df_event_agg = df_event.groupby(by='event_id').agg({'gsen_X': 'sum', 'gsen_Y': 'sum', 'gsen_Z': 'sum', \\\n",
    "                                    'gyro_X': 'sum', 'gyro_Y': 'sum', 'gyro_Z': 'sum', \\\n",
    "                                    'latitude': 'mean', \\\n",
    "                                    'longitude': 'mean', \\\n",
    "                                    'speed': ['mean', 'std'], \\\n",
    "                                    'vehicle_id': 'first'})\n",
    "\n",
    "df_event_agg.columns = ['gsen_X', 'gsen_Y', 'gsen_Z', \\\n",
    "                        'gyro_X', 'gyro_Y', 'gyro_Z', \\\n",
    "                        'lat_mean', 'long_mean', \\\n",
    "                        'speed_mean', 'speed_std', 'vehicle_id']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "988fdc57-7cac-4053-bd56-792328bfd63a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_stats(df: pd.DataFrame) -> pd.DataFrame:\n",
    "    for item in ['X', 'Y', 'Z']:\n",
    "        df[f'gsen_{item}_mean'] = [np.mean(x) if len(x) > 0 else 0 for x in df[f'gsen_{item}']]\n",
    "        df[f'gsen_{item}_std'] = [np.std(x) if len(x) > 0 else 0 for x in df[f'gsen_{item}']]\n",
    "        df[f'gsen_{item}_max'] = [np.max(x) if len(x) > 0 else 0 for x in df[f'gsen_{item}']]\n",
    "        df[f'gsen_{item}_min'] = [np.min(x) if len(x) > 0 else 0 for x in df[f'gsen_{item}']]\n",
    "        df[f'gsen_{item}_range'] = [np.ptp(x) if len(x) > 0 else 0 for x in df[f'gsen_{item}']]\n",
    "        df[f'gyro_{item}_mean'] = [np.mean(x) if len(x) > 0 else 0 for x in df[f'gyro_{item}']]\n",
    "        df[f'gyro_{item}_std'] = [np.std(x) if len(x) > 0 else 0 for x in df[f'gyro_{item}']]\n",
    "        df[f'gyro_{item}_max'] = [np.max(x) if len(x) > 0 else 0 for x in df[f'gyro_{item}']]\n",
    "        df[f'gyro_{item}_min'] = [np.min(x) if len(x) > 0 else 0 for x in df[f'gyro_{item}']]\n",
    "        df[f'gyro_{item}_range'] = [np.ptp(x) if len(x) > 0 else 0 for x in df[f'gyro_{item}']]\n",
    "     \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a594d5e-7f4a-4c7c-9d7c-5b0380a4e5ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_event_agg = make_stats(df_event_agg)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e06f6813-a825-44e9-a446-f92aec0854cd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "798"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_event_agg)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "533de1e9-093e-499b-8061-4e7c2acf937c",
   "metadata": {},
   "source": [
    "## file saving"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "34d0c601-069c-4d31-b00c-2e18a469525f",
   "metadata": {},
   "outputs": [],
   "source": [
    "feather.write_dataframe(df_event_agg.reset_index(), f'df_event_agg_{filename}.feather')\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.8 ('py310')",
   "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.10.8"
  },
  "vscode": {
   "interpreter": {
    "hash": "232549b139df47585d633415423a36abd47509bb62f4ebc8ef6ad9054909bda8"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}