pivoting_and_joining.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a3fd74f8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "93ccaa4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in the snaps data\n",
    "# df = pd.read_csv(\"RED_snaps.csv\")\n",
    "# df = pd.read_csv(\"GREY_snaps.csv\")\n",
    "# df = pd.read_csv(\"ORANGE_snaps.csv\")\n",
    "df = pd.read_csv(\"BLACK_snaps.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "81701601",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in the data without snaps\n",
    "# df_no_snaps = pd.read_csv(\"RED_all_faults.csv\")\n",
    "# df_no_snaps = pd.read_csv(\"GREY_all_faults.csv\")\n",
    "# df_no_snaps = pd.read_csv(\"ORANGE_all_faults.csv\")\n",
    "df_no_snaps = pd.read_csv(\"BLACK_all_faults.csv\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "84ef924a",
   "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>component_id</th>\n",
       "      <th>fault_alarm_id</th>\n",
       "      <th>vehicle_id</th>\n",
       "      <th>message_date_time</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>city_name</th>\n",
       "      <th>state_cd</th>\n",
       "      <th>fault_count</th>\n",
       "      <th>component_type</th>\n",
       "      <th>fmi_id</th>\n",
       "      <th>ecu_type</th>\n",
       "      <th>fault_active</th>\n",
       "      <th>position_timestamp</th>\n",
       "      <th>create_date</th>\n",
       "      <th>heading</th>\n",
       "      <th>elevation</th>\n",
       "      <th>date</th>\n",
       "      <th>odometer_reading</th>\n",
       "      <th>engine_status</th>\n",
       "      <th>idle_engine_hours</th>\n",
       "      <th>total_fuel</th>\n",
       "      <th>gross_combined_weight</th>\n",
       "      <th>engine_hours</th>\n",
       "      <th>rank</th>\n",
       "      <th>vehicle_id.1</th>\n",
       "      <th>vehicle_type</th>\n",
       "      <th>vehicle_spec_id</th>\n",
       "      <th>vehicle_model_year</th>\n",
       "      <th>vehicle_model</th>\n",
       "      <th>vehicle_make</th>\n",
       "      <th>veh_version</th>\n",
       "      <th>tgw_serial</th>\n",
       "      <th>retail_dt</th>\n",
       "      <th>product_type</th>\n",
       "      <th>notif_type</th>\n",
       "      <th>mq_message_put_dt</th>\n",
       "      <th>model_family</th>\n",
       "      <th>main_assembly_dt</th>\n",
       "      <th>inactive_cd</th>\n",
       "      <th>in_service_dt</th>\n",
       "      <th>in_process</th>\n",
       "      <th>imsi</th>\n",
       "      <th>gross_wt_rating</th>\n",
       "      <th>final_assembly_dt</th>\n",
       "      <th>factory_invoice_dt</th>\n",
       "      <th>engine_type</th>\n",
       "      <th>engine_make</th>\n",
       "      <th>dispatch_dt</th>\n",
       "      <th>create_dt</th>\n",
       "      <th>country_of_operation</th>\n",
       "      <th>chassis_series</th>\n",
       "      <th>chassis_number</th>\n",
       "      <th>snap_shot_item_id</th>\n",
       "      <th>fault_alarm_id.1</th>\n",
       "      <th>description</th>\n",
       "      <th>measure_unit</th>\n",
       "      <th>item_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>84</td>\n",
       "      <td>2342965238</td>\n",
       "      <td>100480421</td>\n",
       "      <td>2022-07-01 00:15:13</td>\n",
       "      <td>-112.131979</td>\n",
       "      <td>33.493082</td>\n",
       "      <td>Phoenix</td>\n",
       "      <td>AZ</td>\n",
       "      <td>76</td>\n",
       "      <td>PID</td>\n",
       "      <td>9</td>\n",
       "      <td>140</td>\n",
       "      <td>N</td>\n",
       "      <td>2022-07-01 00:15:31</td>\n",
       "      <td>2022-07-01 00:16:32</td>\n",
       "      <td>315.0</td>\n",
       "      <td>1096.0</td>\n",
       "      <td>2022-07-01</td>\n",
       "      <td>26328.467</td>\n",
       "      <td>1</td>\n",
       "      <td>232.350</td>\n",
       "      <td>3688.106023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>682.501</td>\n",
       "      <td>1</td>\n",
       "      <td>100480421</td>\n",
       "      <td>D</td>\n",
       "      <td>4031299</td>\n",
       "      <td>2022</td>\n",
       "      <td>VNL64T N</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>69</td>\n",
       "      <td>21257087.0</td>\n",
       "      <td>2022-04-04 00:00:00</td>\n",
       "      <td>VNL6</td>\n",
       "      <td>UPDATED</td>\n",
       "      <td>2023-01-05 20:14:09</td>\n",
       "      <td>VN</td>\n",
       "      <td>2021-10-14 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-04-04 00:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>3.104104e+14</td>\n",
       "      <td>22987.0</td>\n",
       "      <td>2021-10-14 00:00:00</td>\n",
       "      <td>2021-11-16 00:00:00</td>\n",
       "      <td>US21</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-09-26 22:22:30</td>\n",
       "      <td>USA</td>\n",
       "      <td>NR</td>\n",
       "      <td>304473</td>\n",
       "      <td>34</td>\n",
       "      <td>2342965238</td>\n",
       "      <td>Battery Level</td>\n",
       "      <td>Voltage</td>\n",
       "      <td>13.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>84</td>\n",
       "      <td>2342965238</td>\n",
       "      <td>100480421</td>\n",
       "      <td>2022-07-01 00:15:13</td>\n",
       "      <td>-112.131979</td>\n",
       "      <td>33.493082</td>\n",
       "      <td>Phoenix</td>\n",
       "      <td>AZ</td>\n",
       "      <td>76</td>\n",
       "      <td>PID</td>\n",
       "      <td>9</td>\n",
       "      <td>140</td>\n",
       "      <td>N</td>\n",
       "      <td>2022-07-01 00:15:31</td>\n",
       "      <td>2022-07-01 00:16:32</td>\n",
       "      <td>315.0</td>\n",
       "      <td>1096.0</td>\n",
       "      <td>2022-07-01</td>\n",
       "      <td>26328.467</td>\n",
       "      <td>1</td>\n",
       "      <td>232.350</td>\n",
       "      <td>3688.106023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>682.501</td>\n",
       "      <td>1</td>\n",
       "      <td>100480421</td>\n",
       "      <td>D</td>\n",
       "      <td>4031299</td>\n",
       "      <td>2022</td>\n",
       "      <td>VNL64T N</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>69</td>\n",
       "      <td>21257087.0</td>\n",
       "      <td>2022-04-04 00:00:00</td>\n",
       "      <td>VNL6</td>\n",
       "      <td>UPDATED</td>\n",
       "      <td>2023-01-05 20:14:09</td>\n",
       "      <td>VN</td>\n",
       "      <td>2021-10-14 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-04-04 00:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>3.104104e+14</td>\n",
       "      <td>22987.0</td>\n",
       "      <td>2021-10-14 00:00:00</td>\n",
       "      <td>2021-11-16 00:00:00</td>\n",
       "      <td>US21</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-09-26 22:22:30</td>\n",
       "      <td>USA</td>\n",
       "      <td>NR</td>\n",
       "      <td>304473</td>\n",
       "      <td>18</td>\n",
       "      <td>2342965238</td>\n",
       "      <td>Coolant Temperature</td>\n",
       "      <td>Temperature</td>\n",
       "      <td>179.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>84</td>\n",
       "      <td>2342965238</td>\n",
       "      <td>100480421</td>\n",
       "      <td>2022-07-01 00:15:13</td>\n",
       "      <td>-112.131979</td>\n",
       "      <td>33.493082</td>\n",
       "      <td>Phoenix</td>\n",
       "      <td>AZ</td>\n",
       "      <td>76</td>\n",
       "      <td>PID</td>\n",
       "      <td>9</td>\n",
       "      <td>140</td>\n",
       "      <td>N</td>\n",
       "      <td>2022-07-01 00:15:31</td>\n",
       "      <td>2022-07-01 00:16:32</td>\n",
       "      <td>315.0</td>\n",
       "      <td>1096.0</td>\n",
       "      <td>2022-07-01</td>\n",
       "      <td>26328.467</td>\n",
       "      <td>1</td>\n",
       "      <td>232.350</td>\n",
       "      <td>3688.106023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>682.501</td>\n",
       "      <td>1</td>\n",
       "      <td>100480421</td>\n",
       "      <td>D</td>\n",
       "      <td>4031299</td>\n",
       "      <td>2022</td>\n",
       "      <td>VNL64T N</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>69</td>\n",
       "      <td>21257087.0</td>\n",
       "      <td>2022-04-04 00:00:00</td>\n",
       "      <td>VNL6</td>\n",
       "      <td>UPDATED</td>\n",
       "      <td>2023-01-05 20:14:09</td>\n",
       "      <td>VN</td>\n",
       "      <td>2021-10-14 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-04-04 00:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>3.104104e+14</td>\n",
       "      <td>22987.0</td>\n",
       "      <td>2021-10-14 00:00:00</td>\n",
       "      <td>2021-11-16 00:00:00</td>\n",
       "      <td>US21</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-09-26 22:22:30</td>\n",
       "      <td>USA</td>\n",
       "      <td>NR</td>\n",
       "      <td>304473</td>\n",
       "      <td>11</td>\n",
       "      <td>2342965238</td>\n",
       "      <td>Oil Pressure</td>\n",
       "      <td>Pressure</td>\n",
       "      <td>19.14498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>84</td>\n",
       "      <td>2342965238</td>\n",
       "      <td>100480421</td>\n",
       "      <td>2022-07-01 00:15:13</td>\n",
       "      <td>-112.131979</td>\n",
       "      <td>33.493082</td>\n",
       "      <td>Phoenix</td>\n",
       "      <td>AZ</td>\n",
       "      <td>76</td>\n",
       "      <td>PID</td>\n",
       "      <td>9</td>\n",
       "      <td>140</td>\n",
       "      <td>N</td>\n",
       "      <td>2022-07-01 00:15:31</td>\n",
       "      <td>2022-07-01 00:16:32</td>\n",
       "      <td>315.0</td>\n",
       "      <td>1096.0</td>\n",
       "      <td>2022-07-01</td>\n",
       "      <td>26328.467</td>\n",
       "      <td>1</td>\n",
       "      <td>232.350</td>\n",
       "      <td>3688.106023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>682.501</td>\n",
       "      <td>1</td>\n",
       "      <td>100480421</td>\n",
       "      <td>D</td>\n",
       "      <td>4031299</td>\n",
       "      <td>2022</td>\n",
       "      <td>VNL64T N</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>69</td>\n",
       "      <td>21257087.0</td>\n",
       "      <td>2022-04-04 00:00:00</td>\n",
       "      <td>VNL6</td>\n",
       "      <td>UPDATED</td>\n",
       "      <td>2023-01-05 20:14:09</td>\n",
       "      <td>VN</td>\n",
       "      <td>2021-10-14 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-04-04 00:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>3.104104e+14</td>\n",
       "      <td>22987.0</td>\n",
       "      <td>2021-10-14 00:00:00</td>\n",
       "      <td>2021-11-16 00:00:00</td>\n",
       "      <td>US21</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-09-26 22:22:30</td>\n",
       "      <td>USA</td>\n",
       "      <td>NR</td>\n",
       "      <td>304473</td>\n",
       "      <td>17</td>\n",
       "      <td>2342965238</td>\n",
       "      <td>Fan Status On/Off</td>\n",
       "      <td>Status</td>\n",
       "      <td>on</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>84</td>\n",
       "      <td>2342965238</td>\n",
       "      <td>100480421</td>\n",
       "      <td>2022-07-01 00:15:13</td>\n",
       "      <td>-112.131979</td>\n",
       "      <td>33.493082</td>\n",
       "      <td>Phoenix</td>\n",
       "      <td>AZ</td>\n",
       "      <td>76</td>\n",
       "      <td>PID</td>\n",
       "      <td>9</td>\n",
       "      <td>140</td>\n",
       "      <td>N</td>\n",
       "      <td>2022-07-01 00:15:31</td>\n",
       "      <td>2022-07-01 00:16:32</td>\n",
       "      <td>315.0</td>\n",
       "      <td>1096.0</td>\n",
       "      <td>2022-07-01</td>\n",
       "      <td>26328.467</td>\n",
       "      <td>1</td>\n",
       "      <td>232.350</td>\n",
       "      <td>3688.106023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>682.501</td>\n",
       "      <td>1</td>\n",
       "      <td>100480421</td>\n",
       "      <td>D</td>\n",
       "      <td>4031299</td>\n",
       "      <td>2022</td>\n",
       "      <td>VNL64T N</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>69</td>\n",
       "      <td>21257087.0</td>\n",
       "      <td>2022-04-04 00:00:00</td>\n",
       "      <td>VNL6</td>\n",
       "      <td>UPDATED</td>\n",
       "      <td>2023-01-05 20:14:09</td>\n",
       "      <td>VN</td>\n",
       "      <td>2021-10-14 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-04-04 00:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>3.104104e+14</td>\n",
       "      <td>22987.0</td>\n",
       "      <td>2021-10-14 00:00:00</td>\n",
       "      <td>2021-11-16 00:00:00</td>\n",
       "      <td>US21</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-09-26 22:22:30</td>\n",
       "      <td>USA</td>\n",
       "      <td>NR</td>\n",
       "      <td>304473</td>\n",
       "      <td>4</td>\n",
       "      <td>2342965238</td>\n",
       "      <td>Engine Speed</td>\n",
       "      <td>Revolutions</td>\n",
       "      <td>597</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",
       "      <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",
       "      <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>65679929</th>\n",
       "      <td>10</td>\n",
       "      <td>2664774231</td>\n",
       "      <td>100301186</td>\n",
       "      <td>2022-12-31 09:32:35</td>\n",
       "      <td>-102.299922</td>\n",
       "      <td>33.911938</td>\n",
       "      <td>Littlefield</td>\n",
       "      <td>TX</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID</td>\n",
       "      <td>5</td>\n",
       "      <td>216</td>\n",
       "      <td>N</td>\n",
       "      <td>2022-12-31 09:32:54</td>\n",
       "      <td>2022-12-31 09:33:58</td>\n",
       "      <td>271.0</td>\n",
       "      <td>3537.0</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>186978.856</td>\n",
       "      <td>1</td>\n",
       "      <td>2628.404</td>\n",
       "      <td>32865.116690</td>\n",
       "      <td>17416.519</td>\n",
       "      <td>6254.059</td>\n",
       "      <td>1</td>\n",
       "      <td>100301186</td>\n",
       "      <td>D</td>\n",
       "      <td>3623404</td>\n",
       "      <td>2020</td>\n",
       "      <td>VNM64T N</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>121</td>\n",
       "      <td>19070131.0</td>\n",
       "      <td>2020-03-27 00:00:00</td>\n",
       "      <td>VNR6</td>\n",
       "      <td>UPDATED</td>\n",
       "      <td>2022-10-26 01:15:17</td>\n",
       "      <td>VN</td>\n",
       "      <td>2019-03-21 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-03-26 00:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>3.104102e+14</td>\n",
       "      <td>22679.0</td>\n",
       "      <td>2019-03-21 00:00:00</td>\n",
       "      <td>2019-03-28 00:00:00</td>\n",
       "      <td>US19</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-03-04 00:39:00</td>\n",
       "      <td>USA</td>\n",
       "      <td>NR</td>\n",
       "      <td>257257</td>\n",
       "      <td>18</td>\n",
       "      <td>2664774231</td>\n",
       "      <td>Coolant Temperature</td>\n",
       "      <td>Temperature</td>\n",
       "      <td>179.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65679930</th>\n",
       "      <td>10</td>\n",
       "      <td>2664774231</td>\n",
       "      <td>100301186</td>\n",
       "      <td>2022-12-31 09:32:35</td>\n",
       "      <td>-102.299922</td>\n",
       "      <td>33.911938</td>\n",
       "      <td>Littlefield</td>\n",
       "      <td>TX</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID</td>\n",
       "      <td>5</td>\n",
       "      <td>216</td>\n",
       "      <td>N</td>\n",
       "      <td>2022-12-31 09:32:54</td>\n",
       "      <td>2022-12-31 09:33:58</td>\n",
       "      <td>271.0</td>\n",
       "      <td>3537.0</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>186978.856</td>\n",
       "      <td>1</td>\n",
       "      <td>2628.404</td>\n",
       "      <td>32865.116690</td>\n",
       "      <td>17416.519</td>\n",
       "      <td>6254.059</td>\n",
       "      <td>1</td>\n",
       "      <td>100301186</td>\n",
       "      <td>D</td>\n",
       "      <td>3623404</td>\n",
       "      <td>2020</td>\n",
       "      <td>VNM64T N</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>121</td>\n",
       "      <td>19070131.0</td>\n",
       "      <td>2020-03-27 00:00:00</td>\n",
       "      <td>VNR6</td>\n",
       "      <td>UPDATED</td>\n",
       "      <td>2022-10-26 01:15:17</td>\n",
       "      <td>VN</td>\n",
       "      <td>2019-03-21 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-03-26 00:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>3.104102e+14</td>\n",
       "      <td>22679.0</td>\n",
       "      <td>2019-03-21 00:00:00</td>\n",
       "      <td>2019-03-28 00:00:00</td>\n",
       "      <td>US19</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-03-04 00:39:00</td>\n",
       "      <td>USA</td>\n",
       "      <td>NR</td>\n",
       "      <td>257257</td>\n",
       "      <td>11</td>\n",
       "      <td>2664774231</td>\n",
       "      <td>Oil Pressure</td>\n",
       "      <td>Pressure</td>\n",
       "      <td>21.46559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65679931</th>\n",
       "      <td>10</td>\n",
       "      <td>2664774231</td>\n",
       "      <td>100301186</td>\n",
       "      <td>2022-12-31 09:32:35</td>\n",
       "      <td>-102.299922</td>\n",
       "      <td>33.911938</td>\n",
       "      <td>Littlefield</td>\n",
       "      <td>TX</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID</td>\n",
       "      <td>5</td>\n",
       "      <td>216</td>\n",
       "      <td>N</td>\n",
       "      <td>2022-12-31 09:32:54</td>\n",
       "      <td>2022-12-31 09:33:58</td>\n",
       "      <td>271.0</td>\n",
       "      <td>3537.0</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>186978.856</td>\n",
       "      <td>1</td>\n",
       "      <td>2628.404</td>\n",
       "      <td>32865.116690</td>\n",
       "      <td>17416.519</td>\n",
       "      <td>6254.059</td>\n",
       "      <td>1</td>\n",
       "      <td>100301186</td>\n",
       "      <td>D</td>\n",
       "      <td>3623404</td>\n",
       "      <td>2020</td>\n",
       "      <td>VNM64T N</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>121</td>\n",
       "      <td>19070131.0</td>\n",
       "      <td>2020-03-27 00:00:00</td>\n",
       "      <td>VNR6</td>\n",
       "      <td>UPDATED</td>\n",
       "      <td>2022-10-26 01:15:17</td>\n",
       "      <td>VN</td>\n",
       "      <td>2019-03-21 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-03-26 00:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>3.104102e+14</td>\n",
       "      <td>22679.0</td>\n",
       "      <td>2019-03-21 00:00:00</td>\n",
       "      <td>2019-03-28 00:00:00</td>\n",
       "      <td>US19</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-03-04 00:39:00</td>\n",
       "      <td>USA</td>\n",
       "      <td>NR</td>\n",
       "      <td>257257</td>\n",
       "      <td>34</td>\n",
       "      <td>2664774231</td>\n",
       "      <td>Battery Level</td>\n",
       "      <td>Voltage</td>\n",
       "      <td>14.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65679932</th>\n",
       "      <td>10</td>\n",
       "      <td>2664774231</td>\n",
       "      <td>100301186</td>\n",
       "      <td>2022-12-31 09:32:35</td>\n",
       "      <td>-102.299922</td>\n",
       "      <td>33.911938</td>\n",
       "      <td>Littlefield</td>\n",
       "      <td>TX</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID</td>\n",
       "      <td>5</td>\n",
       "      <td>216</td>\n",
       "      <td>N</td>\n",
       "      <td>2022-12-31 09:32:54</td>\n",
       "      <td>2022-12-31 09:33:58</td>\n",
       "      <td>271.0</td>\n",
       "      <td>3537.0</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>186978.856</td>\n",
       "      <td>1</td>\n",
       "      <td>2628.404</td>\n",
       "      <td>32865.116690</td>\n",
       "      <td>17416.519</td>\n",
       "      <td>6254.059</td>\n",
       "      <td>1</td>\n",
       "      <td>100301186</td>\n",
       "      <td>D</td>\n",
       "      <td>3623404</td>\n",
       "      <td>2020</td>\n",
       "      <td>VNM64T N</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>121</td>\n",
       "      <td>19070131.0</td>\n",
       "      <td>2020-03-27 00:00:00</td>\n",
       "      <td>VNR6</td>\n",
       "      <td>UPDATED</td>\n",
       "      <td>2022-10-26 01:15:17</td>\n",
       "      <td>VN</td>\n",
       "      <td>2019-03-21 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-03-26 00:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>3.104102e+14</td>\n",
       "      <td>22679.0</td>\n",
       "      <td>2019-03-21 00:00:00</td>\n",
       "      <td>2019-03-28 00:00:00</td>\n",
       "      <td>US19</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-03-04 00:39:00</td>\n",
       "      <td>USA</td>\n",
       "      <td>NR</td>\n",
       "      <td>257257</td>\n",
       "      <td>9</td>\n",
       "      <td>2664774231</td>\n",
       "      <td>Intake Air Pressure</td>\n",
       "      <td>Pressure</td>\n",
       "      <td>11.89309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65679933</th>\n",
       "      <td>10</td>\n",
       "      <td>2664774231</td>\n",
       "      <td>100301186</td>\n",
       "      <td>2022-12-31 09:32:35</td>\n",
       "      <td>-102.299922</td>\n",
       "      <td>33.911938</td>\n",
       "      <td>Littlefield</td>\n",
       "      <td>TX</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID</td>\n",
       "      <td>5</td>\n",
       "      <td>216</td>\n",
       "      <td>N</td>\n",
       "      <td>2022-12-31 09:32:54</td>\n",
       "      <td>2022-12-31 09:33:58</td>\n",
       "      <td>271.0</td>\n",
       "      <td>3537.0</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>186978.856</td>\n",
       "      <td>1</td>\n",
       "      <td>2628.404</td>\n",
       "      <td>32865.116690</td>\n",
       "      <td>17416.519</td>\n",
       "      <td>6254.059</td>\n",
       "      <td>1</td>\n",
       "      <td>100301186</td>\n",
       "      <td>D</td>\n",
       "      <td>3623404</td>\n",
       "      <td>2020</td>\n",
       "      <td>VNM64T N</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>121</td>\n",
       "      <td>19070131.0</td>\n",
       "      <td>2020-03-27 00:00:00</td>\n",
       "      <td>VNR6</td>\n",
       "      <td>UPDATED</td>\n",
       "      <td>2022-10-26 01:15:17</td>\n",
       "      <td>VN</td>\n",
       "      <td>2019-03-21 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-03-26 00:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>3.104102e+14</td>\n",
       "      <td>22679.0</td>\n",
       "      <td>2019-03-21 00:00:00</td>\n",
       "      <td>2019-03-28 00:00:00</td>\n",
       "      <td>US19</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-03-04 00:39:00</td>\n",
       "      <td>USA</td>\n",
       "      <td>NR</td>\n",
       "      <td>257257</td>\n",
       "      <td>4</td>\n",
       "      <td>2664774231</td>\n",
       "      <td>Engine Speed</td>\n",
       "      <td>Revolutions</td>\n",
       "      <td>600</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>65679934 rows × 58 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          component_id  fault_alarm_id  vehicle_id    message_date_time   \n",
       "0                   84      2342965238   100480421  2022-07-01 00:15:13  \\\n",
       "1                   84      2342965238   100480421  2022-07-01 00:15:13   \n",
       "2                   84      2342965238   100480421  2022-07-01 00:15:13   \n",
       "3                   84      2342965238   100480421  2022-07-01 00:15:13   \n",
       "4                   84      2342965238   100480421  2022-07-01 00:15:13   \n",
       "...                ...             ...         ...                  ...   \n",
       "65679929            10      2664774231   100301186  2022-12-31 09:32:35   \n",
       "65679930            10      2664774231   100301186  2022-12-31 09:32:35   \n",
       "65679931            10      2664774231   100301186  2022-12-31 09:32:35   \n",
       "65679932            10      2664774231   100301186  2022-12-31 09:32:35   \n",
       "65679933            10      2664774231   100301186  2022-12-31 09:32:35   \n",
       "\n",
       "           longitude   latitude    city_name state_cd  fault_count   \n",
       "0        -112.131979  33.493082      Phoenix       AZ           76  \\\n",
       "1        -112.131979  33.493082      Phoenix       AZ           76   \n",
       "2        -112.131979  33.493082      Phoenix       AZ           76   \n",
       "3        -112.131979  33.493082      Phoenix       AZ           76   \n",
       "4        -112.131979  33.493082      Phoenix       AZ           76   \n",
       "...              ...        ...          ...      ...          ...   \n",
       "65679929 -102.299922  33.911938  Littlefield       TX            0   \n",
       "65679930 -102.299922  33.911938  Littlefield       TX            0   \n",
       "65679931 -102.299922  33.911938  Littlefield       TX            0   \n",
       "65679932 -102.299922  33.911938  Littlefield       TX            0   \n",
       "65679933 -102.299922  33.911938  Littlefield       TX            0   \n",
       "\n",
       "         component_type  fmi_id  ecu_type fault_active   position_timestamp   \n",
       "0                   PID       9       140            N  2022-07-01 00:15:31  \\\n",
       "1                   PID       9       140            N  2022-07-01 00:15:31   \n",
       "2                   PID       9       140            N  2022-07-01 00:15:31   \n",
       "3                   PID       9       140            N  2022-07-01 00:15:31   \n",
       "4                   PID       9       140            N  2022-07-01 00:15:31   \n",
       "...                 ...     ...       ...          ...                  ...   \n",
       "65679929           PSID       5       216            N  2022-12-31 09:32:54   \n",
       "65679930           PSID       5       216            N  2022-12-31 09:32:54   \n",
       "65679931           PSID       5       216            N  2022-12-31 09:32:54   \n",
       "65679932           PSID       5       216            N  2022-12-31 09:32:54   \n",
       "65679933           PSID       5       216            N  2022-12-31 09:32:54   \n",
       "\n",
       "                  create_date  heading  elevation        date   \n",
       "0         2022-07-01 00:16:32    315.0     1096.0  2022-07-01  \\\n",
       "1         2022-07-01 00:16:32    315.0     1096.0  2022-07-01   \n",
       "2         2022-07-01 00:16:32    315.0     1096.0  2022-07-01   \n",
       "3         2022-07-01 00:16:32    315.0     1096.0  2022-07-01   \n",
       "4         2022-07-01 00:16:32    315.0     1096.0  2022-07-01   \n",
       "...                       ...      ...        ...         ...   \n",
       "65679929  2022-12-31 09:33:58    271.0     3537.0  2022-12-31   \n",
       "65679930  2022-12-31 09:33:58    271.0     3537.0  2022-12-31   \n",
       "65679931  2022-12-31 09:33:58    271.0     3537.0  2022-12-31   \n",
       "65679932  2022-12-31 09:33:58    271.0     3537.0  2022-12-31   \n",
       "65679933  2022-12-31 09:33:58    271.0     3537.0  2022-12-31   \n",
       "\n",
       "          odometer_reading  engine_status  idle_engine_hours    total_fuel   \n",
       "0                26328.467              1            232.350   3688.106023  \\\n",
       "1                26328.467              1            232.350   3688.106023   \n",
       "2                26328.467              1            232.350   3688.106023   \n",
       "3                26328.467              1            232.350   3688.106023   \n",
       "4                26328.467              1            232.350   3688.106023   \n",
       "...                    ...            ...                ...           ...   \n",
       "65679929        186978.856              1           2628.404  32865.116690   \n",
       "65679930        186978.856              1           2628.404  32865.116690   \n",
       "65679931        186978.856              1           2628.404  32865.116690   \n",
       "65679932        186978.856              1           2628.404  32865.116690   \n",
       "65679933        186978.856              1           2628.404  32865.116690   \n",
       "\n",
       "          gross_combined_weight  engine_hours  rank  vehicle_id.1   \n",
       "0                           NaN       682.501     1     100480421  \\\n",
       "1                           NaN       682.501     1     100480421   \n",
       "2                           NaN       682.501     1     100480421   \n",
       "3                           NaN       682.501     1     100480421   \n",
       "4                           NaN       682.501     1     100480421   \n",
       "...                         ...           ...   ...           ...   \n",
       "65679929              17416.519      6254.059     1     100301186   \n",
       "65679930              17416.519      6254.059     1     100301186   \n",
       "65679931              17416.519      6254.059     1     100301186   \n",
       "65679932              17416.519      6254.059     1     100301186   \n",
       "65679933              17416.519      6254.059     1     100301186   \n",
       "\n",
       "         vehicle_type  vehicle_spec_id  vehicle_model_year vehicle_model   \n",
       "0                   D          4031299                2022      VNL64T N  \\\n",
       "1                   D          4031299                2022      VNL64T N   \n",
       "2                   D          4031299                2022      VNL64T N   \n",
       "3                   D          4031299                2022      VNL64T N   \n",
       "4                   D          4031299                2022      VNL64T N   \n",
       "...               ...              ...                 ...           ...   \n",
       "65679929            D          3623404                2020      VNM64T N   \n",
       "65679930            D          3623404                2020      VNM64T N   \n",
       "65679931            D          3623404                2020      VNM64T N   \n",
       "65679932            D          3623404                2020      VNM64T N   \n",
       "65679933            D          3623404                2020      VNM64T N   \n",
       "\n",
       "         vehicle_make  veh_version  tgw_serial            retail_dt   \n",
       "0               VOLVO           69  21257087.0  2022-04-04 00:00:00  \\\n",
       "1               VOLVO           69  21257087.0  2022-04-04 00:00:00   \n",
       "2               VOLVO           69  21257087.0  2022-04-04 00:00:00   \n",
       "3               VOLVO           69  21257087.0  2022-04-04 00:00:00   \n",
       "4               VOLVO           69  21257087.0  2022-04-04 00:00:00   \n",
       "...               ...          ...         ...                  ...   \n",
       "65679929        VOLVO          121  19070131.0  2020-03-27 00:00:00   \n",
       "65679930        VOLVO          121  19070131.0  2020-03-27 00:00:00   \n",
       "65679931        VOLVO          121  19070131.0  2020-03-27 00:00:00   \n",
       "65679932        VOLVO          121  19070131.0  2020-03-27 00:00:00   \n",
       "65679933        VOLVO          121  19070131.0  2020-03-27 00:00:00   \n",
       "\n",
       "         product_type notif_type    mq_message_put_dt model_family   \n",
       "0                VNL6    UPDATED  2023-01-05 20:14:09           VN  \\\n",
       "1                VNL6    UPDATED  2023-01-05 20:14:09           VN   \n",
       "2                VNL6    UPDATED  2023-01-05 20:14:09           VN   \n",
       "3                VNL6    UPDATED  2023-01-05 20:14:09           VN   \n",
       "4                VNL6    UPDATED  2023-01-05 20:14:09           VN   \n",
       "...               ...        ...                  ...          ...   \n",
       "65679929         VNR6    UPDATED  2022-10-26 01:15:17           VN   \n",
       "65679930         VNR6    UPDATED  2022-10-26 01:15:17           VN   \n",
       "65679931         VNR6    UPDATED  2022-10-26 01:15:17           VN   \n",
       "65679932         VNR6    UPDATED  2022-10-26 01:15:17           VN   \n",
       "65679933         VNR6    UPDATED  2022-10-26 01:15:17           VN   \n",
       "\n",
       "             main_assembly_dt  inactive_cd        in_service_dt in_process   \n",
       "0         2021-10-14 00:00:00          NaN  2022-04-04 00:00:00          N  \\\n",
       "1         2021-10-14 00:00:00          NaN  2022-04-04 00:00:00          N   \n",
       "2         2021-10-14 00:00:00          NaN  2022-04-04 00:00:00          N   \n",
       "3         2021-10-14 00:00:00          NaN  2022-04-04 00:00:00          N   \n",
       "4         2021-10-14 00:00:00          NaN  2022-04-04 00:00:00          N   \n",
       "...                       ...          ...                  ...        ...   \n",
       "65679929  2019-03-21 00:00:00          NaN  2020-03-26 00:00:00          N   \n",
       "65679930  2019-03-21 00:00:00          NaN  2020-03-26 00:00:00          N   \n",
       "65679931  2019-03-21 00:00:00          NaN  2020-03-26 00:00:00          N   \n",
       "65679932  2019-03-21 00:00:00          NaN  2020-03-26 00:00:00          N   \n",
       "65679933  2019-03-21 00:00:00          NaN  2020-03-26 00:00:00          N   \n",
       "\n",
       "                  imsi  gross_wt_rating    final_assembly_dt   \n",
       "0         3.104104e+14          22987.0  2021-10-14 00:00:00  \\\n",
       "1         3.104104e+14          22987.0  2021-10-14 00:00:00   \n",
       "2         3.104104e+14          22987.0  2021-10-14 00:00:00   \n",
       "3         3.104104e+14          22987.0  2021-10-14 00:00:00   \n",
       "4         3.104104e+14          22987.0  2021-10-14 00:00:00   \n",
       "...                ...              ...                  ...   \n",
       "65679929  3.104102e+14          22679.0  2019-03-21 00:00:00   \n",
       "65679930  3.104102e+14          22679.0  2019-03-21 00:00:00   \n",
       "65679931  3.104102e+14          22679.0  2019-03-21 00:00:00   \n",
       "65679932  3.104102e+14          22679.0  2019-03-21 00:00:00   \n",
       "65679933  3.104102e+14          22679.0  2019-03-21 00:00:00   \n",
       "\n",
       "           factory_invoice_dt engine_type engine_make  dispatch_dt   \n",
       "0         2021-11-16 00:00:00        US21       VOLVO          NaN  \\\n",
       "1         2021-11-16 00:00:00        US21       VOLVO          NaN   \n",
       "2         2021-11-16 00:00:00        US21       VOLVO          NaN   \n",
       "3         2021-11-16 00:00:00        US21       VOLVO          NaN   \n",
       "4         2021-11-16 00:00:00        US21       VOLVO          NaN   \n",
       "...                       ...         ...         ...          ...   \n",
       "65679929  2019-03-28 00:00:00        US19       VOLVO          NaN   \n",
       "65679930  2019-03-28 00:00:00        US19       VOLVO          NaN   \n",
       "65679931  2019-03-28 00:00:00        US19       VOLVO          NaN   \n",
       "65679932  2019-03-28 00:00:00        US19       VOLVO          NaN   \n",
       "65679933  2019-03-28 00:00:00        US19       VOLVO          NaN   \n",
       "\n",
       "                    create_dt country_of_operation chassis_series   \n",
       "0         2021-09-26 22:22:30                  USA             NR  \\\n",
       "1         2021-09-26 22:22:30                  USA             NR   \n",
       "2         2021-09-26 22:22:30                  USA             NR   \n",
       "3         2021-09-26 22:22:30                  USA             NR   \n",
       "4         2021-09-26 22:22:30                  USA             NR   \n",
       "...                       ...                  ...            ...   \n",
       "65679929  2019-03-04 00:39:00                  USA             NR   \n",
       "65679930  2019-03-04 00:39:00                  USA             NR   \n",
       "65679931  2019-03-04 00:39:00                  USA             NR   \n",
       "65679932  2019-03-04 00:39:00                  USA             NR   \n",
       "65679933  2019-03-04 00:39:00                  USA             NR   \n",
       "\n",
       "          chassis_number  snap_shot_item_id  fault_alarm_id.1   \n",
       "0                 304473                 34        2342965238  \\\n",
       "1                 304473                 18        2342965238   \n",
       "2                 304473                 11        2342965238   \n",
       "3                 304473                 17        2342965238   \n",
       "4                 304473                  4        2342965238   \n",
       "...                  ...                ...               ...   \n",
       "65679929          257257                 18        2664774231   \n",
       "65679930          257257                 11        2664774231   \n",
       "65679931          257257                 34        2664774231   \n",
       "65679932          257257                  9        2664774231   \n",
       "65679933          257257                  4        2664774231   \n",
       "\n",
       "                  description measure_unit item_value  \n",
       "0               Battery Level      Voltage       13.8  \n",
       "1         Coolant Temperature  Temperature      179.6  \n",
       "2                Oil Pressure     Pressure   19.14498  \n",
       "3           Fan Status On/Off       Status         on  \n",
       "4                Engine Speed  Revolutions        597  \n",
       "...                       ...          ...        ...  \n",
       "65679929  Coolant Temperature  Temperature      179.6  \n",
       "65679930         Oil Pressure     Pressure   21.46559  \n",
       "65679931        Battery Level      Voltage       14.1  \n",
       "65679932  Intake Air Pressure     Pressure   11.89309  \n",
       "65679933         Engine Speed  Revolutions        600  \n",
       "\n",
       "[65679934 rows x 58 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "with pd.option_context('display.max_columns', None):\n",
    "    display(df)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "1cc07125",
   "metadata": {},
   "source": [
    "# Pivoting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "61f9eda5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pivot df avoiding duplicates\n",
    "df_p = df.pivot_table(index=\"fault_alarm_id\", columns=\"description\", values=\"item_value\", aggfunc='first').reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5c74c060",
   "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>description</th>\n",
       "      <th>fault_alarm_id</th>\n",
       "      <th>Battery Level</th>\n",
       "      <th>Coolant Temperature</th>\n",
       "      <th>Engine Crankcase Pressure</th>\n",
       "      <th>Engine Exhaust Temperature</th>\n",
       "      <th>Engine Oil Temperature</th>\n",
       "      <th>Engine Speed</th>\n",
       "      <th>Fan Status On/Off</th>\n",
       "      <th>Intake Air Pressure</th>\n",
       "      <th>Oil Pressure</th>\n",
       "      <th>Road Speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2342947715</td>\n",
       "      <td>14.1</td>\n",
       "      <td>179.6</td>\n",
       "      <td>14.3297285</td>\n",
       "      <td>569.075</td>\n",
       "      <td>227.19</td>\n",
       "      <td>1166</td>\n",
       "      <td>on</td>\n",
       "      <td>36.83959</td>\n",
       "      <td>50.47313</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2342948236</td>\n",
       "      <td>14.1</td>\n",
       "      <td>179.6</td>\n",
       "      <td>14.3297285</td>\n",
       "      <td>569.075</td>\n",
       "      <td>227.19</td>\n",
       "      <td>1166</td>\n",
       "      <td>on</td>\n",
       "      <td>36.83959</td>\n",
       "      <td>50.47313</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2342948245</td>\n",
       "      <td>14.1</td>\n",
       "      <td>179.6</td>\n",
       "      <td>14.3297285</td>\n",
       "      <td>569.075</td>\n",
       "      <td>227.19</td>\n",
       "      <td>1166</td>\n",
       "      <td>on</td>\n",
       "      <td>36.83959</td>\n",
       "      <td>50.47313</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2342948249</td>\n",
       "      <td>14.2</td>\n",
       "      <td>179.6</td>\n",
       "      <td>14.3297285</td>\n",
       "      <td>569.075</td>\n",
       "      <td>227.19</td>\n",
       "      <td>1166</td>\n",
       "      <td>on</td>\n",
       "      <td>36.83959</td>\n",
       "      <td>50.47313</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2342948270</td>\n",
       "      <td>14.1</td>\n",
       "      <td>179.6</td>\n",
       "      <td>14.3297285</td>\n",
       "      <td>569.075</td>\n",
       "      <td>227.19</td>\n",
       "      <td>1166</td>\n",
       "      <td>on</td>\n",
       "      <td>36.83959</td>\n",
       "      <td>50.47313</td>\n",
       "      <td>70</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6592868</th>\n",
       "      <td>2683998884</td>\n",
       "      <td>14.2</td>\n",
       "      <td>177.8</td>\n",
       "      <td>14.619804</td>\n",
       "      <td>240.6875</td>\n",
       "      <td>191.98</td>\n",
       "      <td>1199</td>\n",
       "      <td>on</td>\n",
       "      <td>15.08392</td>\n",
       "      <td>42.35102</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6592869</th>\n",
       "      <td>2687062843</td>\n",
       "      <td>13.2</td>\n",
       "      <td>190.4</td>\n",
       "      <td>13.7785851</td>\n",
       "      <td>242.2625</td>\n",
       "      <td>194.74</td>\n",
       "      <td>650</td>\n",
       "      <td>on</td>\n",
       "      <td>11.31294</td>\n",
       "      <td>27.26709</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6592870</th>\n",
       "      <td>2687062844</td>\n",
       "      <td>13.1</td>\n",
       "      <td>190.4</td>\n",
       "      <td>13.7785851</td>\n",
       "      <td>242.2625</td>\n",
       "      <td>194.74</td>\n",
       "      <td>650</td>\n",
       "      <td>on</td>\n",
       "      <td>11.31294</td>\n",
       "      <td>27.26709</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6592871</th>\n",
       "      <td>2713453327</td>\n",
       "      <td>14.1</td>\n",
       "      <td>150.8</td>\n",
       "      <td>14.5182776</td>\n",
       "      <td>559.4</td>\n",
       "      <td>111.09</td>\n",
       "      <td>1279</td>\n",
       "      <td>on</td>\n",
       "      <td>21.46559</td>\n",
       "      <td>59.1754</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6592872</th>\n",
       "      <td>2713453376</td>\n",
       "      <td>14.1</td>\n",
       "      <td>161.6</td>\n",
       "      <td>14.5037738</td>\n",
       "      <td>411.4625</td>\n",
       "      <td>124.48</td>\n",
       "      <td>816</td>\n",
       "      <td>on</td>\n",
       "      <td>18.27475</td>\n",
       "      <td>58.0151</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6592873 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "description  fault_alarm_id Battery Level Coolant Temperature   \n",
       "0                2342947715          14.1               179.6  \\\n",
       "1                2342948236          14.1               179.6   \n",
       "2                2342948245          14.1               179.6   \n",
       "3                2342948249          14.2               179.6   \n",
       "4                2342948270          14.1               179.6   \n",
       "...                     ...           ...                 ...   \n",
       "6592868          2683998884          14.2               177.8   \n",
       "6592869          2687062843          13.2               190.4   \n",
       "6592870          2687062844          13.1               190.4   \n",
       "6592871          2713453327          14.1               150.8   \n",
       "6592872          2713453376          14.1               161.6   \n",
       "\n",
       "description Engine Crankcase Pressure Engine Exhaust Temperature   \n",
       "0                          14.3297285                    569.075  \\\n",
       "1                          14.3297285                    569.075   \n",
       "2                          14.3297285                    569.075   \n",
       "3                          14.3297285                    569.075   \n",
       "4                          14.3297285                    569.075   \n",
       "...                               ...                        ...   \n",
       "6592868                     14.619804                   240.6875   \n",
       "6592869                    13.7785851                   242.2625   \n",
       "6592870                    13.7785851                   242.2625   \n",
       "6592871                    14.5182776                      559.4   \n",
       "6592872                    14.5037738                   411.4625   \n",
       "\n",
       "description Engine Oil Temperature Engine Speed Fan Status On/Off   \n",
       "0                           227.19         1166                on  \\\n",
       "1                           227.19         1166                on   \n",
       "2                           227.19         1166                on   \n",
       "3                           227.19         1166                on   \n",
       "4                           227.19         1166                on   \n",
       "...                            ...          ...               ...   \n",
       "6592868                     191.98         1199                on   \n",
       "6592869                     194.74          650                on   \n",
       "6592870                     194.74          650                on   \n",
       "6592871                     111.09         1279                on   \n",
       "6592872                     124.48          816                on   \n",
       "\n",
       "description Intake Air Pressure Oil Pressure Road Speed  \n",
       "0                      36.83959     50.47313         70  \n",
       "1                      36.83959     50.47313         70  \n",
       "2                      36.83959     50.47313         70  \n",
       "3                      36.83959     50.47313         70  \n",
       "4                      36.83959     50.47313         70  \n",
       "...                         ...          ...        ...  \n",
       "6592868                15.08392     42.35102          4  \n",
       "6592869                11.31294     27.26709          0  \n",
       "6592870                11.31294     27.26709          0  \n",
       "6592871                21.46559      59.1754         39  \n",
       "6592872                18.27475      58.0151         11  \n",
       "\n",
       "[6592873 rows x 11 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "with pd.option_context('display.max_columns', None):\n",
    "    display(df_p)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "900ddddf",
   "metadata": {},
   "source": [
    "# Joining"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "11001dc0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join df_p with df\n",
    "df_all = df_p.join(df_no_snaps.set_index(\"fault_alarm_id\"), on=\"fault_alarm_id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "7a2a58f1",
   "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>fault_alarm_id</th>\n",
       "      <th>Battery Level</th>\n",
       "      <th>Coolant Temperature</th>\n",
       "      <th>Engine Crankcase Pressure</th>\n",
       "      <th>Engine Exhaust Temperature</th>\n",
       "      <th>Engine Oil Temperature</th>\n",
       "      <th>Engine Speed</th>\n",
       "      <th>Fan Status On/Off</th>\n",
       "      <th>Intake Air Pressure</th>\n",
       "      <th>Oil Pressure</th>\n",
       "      <th>Road Speed</th>\n",
       "      <th>component_id</th>\n",
       "      <th>vehicle_id</th>\n",
       "      <th>message_date_time</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>city_name</th>\n",
       "      <th>state_cd</th>\n",
       "      <th>fault_count</th>\n",
       "      <th>component_type</th>\n",
       "      <th>fmi_id</th>\n",
       "      <th>ecu_type</th>\n",
       "      <th>fault_active</th>\n",
       "      <th>position_timestamp</th>\n",
       "      <th>create_date</th>\n",
       "      <th>heading</th>\n",
       "      <th>elevation</th>\n",
       "      <th>date</th>\n",
       "      <th>odometer_reading</th>\n",
       "      <th>engine_status</th>\n",
       "      <th>idle_engine_hours</th>\n",
       "      <th>total_fuel</th>\n",
       "      <th>gross_combined_weight</th>\n",
       "      <th>engine_hours</th>\n",
       "      <th>rank</th>\n",
       "      <th>vehicle_id.1</th>\n",
       "      <th>vehicle_type</th>\n",
       "      <th>vehicle_spec_id</th>\n",
       "      <th>vehicle_model_year</th>\n",
       "      <th>vehicle_model</th>\n",
       "      <th>vehicle_make</th>\n",
       "      <th>veh_version</th>\n",
       "      <th>tgw_serial</th>\n",
       "      <th>retail_dt</th>\n",
       "      <th>product_type</th>\n",
       "      <th>notif_type</th>\n",
       "      <th>mq_message_put_dt</th>\n",
       "      <th>model_family</th>\n",
       "      <th>main_assembly_dt</th>\n",
       "      <th>inactive_cd</th>\n",
       "      <th>in_service_dt</th>\n",
       "      <th>in_process</th>\n",
       "      <th>imsi</th>\n",
       "      <th>gross_wt_rating</th>\n",
       "      <th>final_assembly_dt</th>\n",
       "      <th>factory_invoice_dt</th>\n",
       "      <th>engine_type</th>\n",
       "      <th>engine_make</th>\n",
       "      <th>dispatch_dt</th>\n",
       "      <th>create_dt</th>\n",
       "      <th>country_of_operation</th>\n",
       "      <th>chassis_series</th>\n",
       "      <th>chassis_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2342947715</td>\n",
       "      <td>14.1</td>\n",
       "      <td>179.6</td>\n",
       "      <td>14.3297285</td>\n",
       "      <td>569.075</td>\n",
       "      <td>227.19</td>\n",
       "      <td>1166</td>\n",
       "      <td>on</td>\n",
       "      <td>36.83959</td>\n",
       "      <td>50.47313</td>\n",
       "      <td>70</td>\n",
       "      <td>2370</td>\n",
       "      <td>100477846</td>\n",
       "      <td>2022-07-01 00:00:03</td>\n",
       "      <td>-90.256296</td>\n",
       "      <td>38.035002</td>\n",
       "      <td>Bloomsdale</td>\n",
       "      <td>MO</td>\n",
       "      <td>126</td>\n",
       "      <td>SPN</td>\n",
       "      <td>2</td>\n",
       "      <td>232</td>\n",
       "      <td>I</td>\n",
       "      <td>2022-07-01 00:00:14</td>\n",
       "      <td>2022-07-01 00:00:19</td>\n",
       "      <td>129.0</td>\n",
       "      <td>561.0</td>\n",
       "      <td>2022-07-01</td>\n",
       "      <td>155442.466</td>\n",
       "      <td>1</td>\n",
       "      <td>1032.351</td>\n",
       "      <td>19367.245675</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3587.855</td>\n",
       "      <td>1</td>\n",
       "      <td>100477846</td>\n",
       "      <td>D</td>\n",
       "      <td>3986058</td>\n",
       "      <td>2022</td>\n",
       "      <td>VNL64T N</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>83</td>\n",
       "      <td>21337251.0</td>\n",
       "      <td>2021-11-02 00:00:00</td>\n",
       "      <td>VNL6</td>\n",
       "      <td>UPDATED</td>\n",
       "      <td>2023-02-08 20:32:19</td>\n",
       "      <td>VN</td>\n",
       "      <td>2021-09-08 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-11-12 00:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>3.104104e+14</td>\n",
       "      <td>22838.0</td>\n",
       "      <td>2021-09-08 00:00:00</td>\n",
       "      <td>2021-10-13 00:00:00</td>\n",
       "      <td>US21</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-08-08 22:09:58</td>\n",
       "      <td>USA</td>\n",
       "      <td>NR</td>\n",
       "      <td>283934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2342948236</td>\n",
       "      <td>14.1</td>\n",
       "      <td>179.6</td>\n",
       "      <td>14.3297285</td>\n",
       "      <td>569.075</td>\n",
       "      <td>227.19</td>\n",
       "      <td>1166</td>\n",
       "      <td>on</td>\n",
       "      <td>36.83959</td>\n",
       "      <td>50.47313</td>\n",
       "      <td>70</td>\n",
       "      <td>11</td>\n",
       "      <td>100477846</td>\n",
       "      <td>2022-07-01 00:00:03</td>\n",
       "      <td>-90.256581</td>\n",
       "      <td>38.035188</td>\n",
       "      <td>Bloomsdale</td>\n",
       "      <td>MO</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID</td>\n",
       "      <td>5</td>\n",
       "      <td>216</td>\n",
       "      <td>Y</td>\n",
       "      <td>2022-07-01 00:00:13</td>\n",
       "      <td>2022-07-01 00:00:45</td>\n",
       "      <td>130.0</td>\n",
       "      <td>558.0</td>\n",
       "      <td>2022-07-01</td>\n",
       "      <td>155442.466</td>\n",
       "      <td>1</td>\n",
       "      <td>1035.351</td>\n",
       "      <td>19368.566535</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3590.855</td>\n",
       "      <td>1</td>\n",
       "      <td>100477846</td>\n",
       "      <td>D</td>\n",
       "      <td>3986058</td>\n",
       "      <td>2022</td>\n",
       "      <td>VNL64T N</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>83</td>\n",
       "      <td>21337251.0</td>\n",
       "      <td>2021-11-02 00:00:00</td>\n",
       "      <td>VNL6</td>\n",
       "      <td>UPDATED</td>\n",
       "      <td>2023-02-08 20:32:19</td>\n",
       "      <td>VN</td>\n",
       "      <td>2021-09-08 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-11-12 00:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>3.104104e+14</td>\n",
       "      <td>22838.0</td>\n",
       "      <td>2021-09-08 00:00:00</td>\n",
       "      <td>2021-10-13 00:00:00</td>\n",
       "      <td>US21</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-08-08 22:09:58</td>\n",
       "      <td>USA</td>\n",
       "      <td>NR</td>\n",
       "      <td>283934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2342948245</td>\n",
       "      <td>14.1</td>\n",
       "      <td>179.6</td>\n",
       "      <td>14.3297285</td>\n",
       "      <td>569.075</td>\n",
       "      <td>227.19</td>\n",
       "      <td>1166</td>\n",
       "      <td>on</td>\n",
       "      <td>36.83959</td>\n",
       "      <td>50.47313</td>\n",
       "      <td>70</td>\n",
       "      <td>14</td>\n",
       "      <td>100477846</td>\n",
       "      <td>2022-07-01 00:00:03</td>\n",
       "      <td>-90.256581</td>\n",
       "      <td>38.035188</td>\n",
       "      <td>Bloomsdale</td>\n",
       "      <td>MO</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID</td>\n",
       "      <td>5</td>\n",
       "      <td>216</td>\n",
       "      <td>Y</td>\n",
       "      <td>2022-07-01 00:00:13</td>\n",
       "      <td>2022-07-01 00:00:46</td>\n",
       "      <td>130.0</td>\n",
       "      <td>558.0</td>\n",
       "      <td>2022-07-01</td>\n",
       "      <td>155442.466</td>\n",
       "      <td>1</td>\n",
       "      <td>1035.351</td>\n",
       "      <td>19368.566535</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3590.855</td>\n",
       "      <td>1</td>\n",
       "      <td>100477846</td>\n",
       "      <td>D</td>\n",
       "      <td>3986058</td>\n",
       "      <td>2022</td>\n",
       "      <td>VNL64T N</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>83</td>\n",
       "      <td>21337251.0</td>\n",
       "      <td>2021-11-02 00:00:00</td>\n",
       "      <td>VNL6</td>\n",
       "      <td>UPDATED</td>\n",
       "      <td>2023-02-08 20:32:19</td>\n",
       "      <td>VN</td>\n",
       "      <td>2021-09-08 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-11-12 00:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>3.104104e+14</td>\n",
       "      <td>22838.0</td>\n",
       "      <td>2021-09-08 00:00:00</td>\n",
       "      <td>2021-10-13 00:00:00</td>\n",
       "      <td>US21</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-08-08 22:09:58</td>\n",
       "      <td>USA</td>\n",
       "      <td>NR</td>\n",
       "      <td>283934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2342948249</td>\n",
       "      <td>14.2</td>\n",
       "      <td>179.6</td>\n",
       "      <td>14.3297285</td>\n",
       "      <td>569.075</td>\n",
       "      <td>227.19</td>\n",
       "      <td>1166</td>\n",
       "      <td>on</td>\n",
       "      <td>36.83959</td>\n",
       "      <td>50.47313</td>\n",
       "      <td>70</td>\n",
       "      <td>45</td>\n",
       "      <td>100477846</td>\n",
       "      <td>2022-07-01 00:00:03</td>\n",
       "      <td>-90.256581</td>\n",
       "      <td>38.035188</td>\n",
       "      <td>Bloomsdale</td>\n",
       "      <td>MO</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID</td>\n",
       "      <td>5</td>\n",
       "      <td>216</td>\n",
       "      <td>Y</td>\n",
       "      <td>2022-07-01 00:00:13</td>\n",
       "      <td>2022-07-01 00:00:46</td>\n",
       "      <td>130.0</td>\n",
       "      <td>558.0</td>\n",
       "      <td>2022-07-01</td>\n",
       "      <td>155442.466</td>\n",
       "      <td>1</td>\n",
       "      <td>1035.351</td>\n",
       "      <td>19368.566535</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3590.855</td>\n",
       "      <td>1</td>\n",
       "      <td>100477846</td>\n",
       "      <td>D</td>\n",
       "      <td>3986058</td>\n",
       "      <td>2022</td>\n",
       "      <td>VNL64T N</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>83</td>\n",
       "      <td>21337251.0</td>\n",
       "      <td>2021-11-02 00:00:00</td>\n",
       "      <td>VNL6</td>\n",
       "      <td>UPDATED</td>\n",
       "      <td>2023-02-08 20:32:19</td>\n",
       "      <td>VN</td>\n",
       "      <td>2021-09-08 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-11-12 00:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>3.104104e+14</td>\n",
       "      <td>22838.0</td>\n",
       "      <td>2021-09-08 00:00:00</td>\n",
       "      <td>2021-10-13 00:00:00</td>\n",
       "      <td>US21</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-08-08 22:09:58</td>\n",
       "      <td>USA</td>\n",
       "      <td>NR</td>\n",
       "      <td>283934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2342948270</td>\n",
       "      <td>14.1</td>\n",
       "      <td>179.6</td>\n",
       "      <td>14.3297285</td>\n",
       "      <td>569.075</td>\n",
       "      <td>227.19</td>\n",
       "      <td>1166</td>\n",
       "      <td>on</td>\n",
       "      <td>36.83959</td>\n",
       "      <td>50.47313</td>\n",
       "      <td>70</td>\n",
       "      <td>242</td>\n",
       "      <td>100477846</td>\n",
       "      <td>2022-07-01 00:00:03</td>\n",
       "      <td>-90.256296</td>\n",
       "      <td>38.035002</td>\n",
       "      <td>Bloomsdale</td>\n",
       "      <td>MO</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID</td>\n",
       "      <td>12</td>\n",
       "      <td>140</td>\n",
       "      <td>Y</td>\n",
       "      <td>2022-07-01 00:00:14</td>\n",
       "      <td>2022-07-01 00:00:47</td>\n",
       "      <td>129.0</td>\n",
       "      <td>561.0</td>\n",
       "      <td>2022-07-01</td>\n",
       "      <td>155442.466</td>\n",
       "      <td>1</td>\n",
       "      <td>1032.351</td>\n",
       "      <td>19367.245675</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3587.855</td>\n",
       "      <td>1</td>\n",
       "      <td>100477846</td>\n",
       "      <td>D</td>\n",
       "      <td>3986058</td>\n",
       "      <td>2022</td>\n",
       "      <td>VNL64T N</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>83</td>\n",
       "      <td>21337251.0</td>\n",
       "      <td>2021-11-02 00:00:00</td>\n",
       "      <td>VNL6</td>\n",
       "      <td>UPDATED</td>\n",
       "      <td>2023-02-08 20:32:19</td>\n",
       "      <td>VN</td>\n",
       "      <td>2021-09-08 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-11-12 00:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>3.104104e+14</td>\n",
       "      <td>22838.0</td>\n",
       "      <td>2021-09-08 00:00:00</td>\n",
       "      <td>2021-10-13 00:00:00</td>\n",
       "      <td>US21</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-08-08 22:09:58</td>\n",
       "      <td>USA</td>\n",
       "      <td>NR</td>\n",
       "      <td>283934</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",
       "      <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",
       "      <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>6592868</th>\n",
       "      <td>2683998884</td>\n",
       "      <td>14.2</td>\n",
       "      <td>177.8</td>\n",
       "      <td>14.619804</td>\n",
       "      <td>240.6875</td>\n",
       "      <td>191.98</td>\n",
       "      <td>1199</td>\n",
       "      <td>on</td>\n",
       "      <td>15.08392</td>\n",
       "      <td>42.35102</td>\n",
       "      <td>4</td>\n",
       "      <td>5610</td>\n",
       "      <td>100307867</td>\n",
       "      <td>2022-12-19 18:11:07</td>\n",
       "      <td>-87.088977</td>\n",
       "      <td>33.280599</td>\n",
       "      <td>Lake View</td>\n",
       "      <td>AL</td>\n",
       "      <td>5</td>\n",
       "      <td>SPN</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>N</td>\n",
       "      <td>2022-12-19 18:11:01</td>\n",
       "      <td>2023-01-11 13:04:27</td>\n",
       "      <td>0.0</td>\n",
       "      <td>627.0</td>\n",
       "      <td>2022-12-19</td>\n",
       "      <td>355276.684</td>\n",
       "      <td>0</td>\n",
       "      <td>9831.114</td>\n",
       "      <td>52568.257129</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15802.373</td>\n",
       "      <td>1</td>\n",
       "      <td>100307867</td>\n",
       "      <td>D</td>\n",
       "      <td>3631299</td>\n",
       "      <td>2020</td>\n",
       "      <td>VNL64T N</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>148</td>\n",
       "      <td>19120121.0</td>\n",
       "      <td>2020-03-23 00:00:00</td>\n",
       "      <td>VNL6</td>\n",
       "      <td>UPDATED</td>\n",
       "      <td>2022-11-07 18:44:36</td>\n",
       "      <td>VN</td>\n",
       "      <td>2019-04-25 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-03-23 00:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>3.104102e+14</td>\n",
       "      <td>23223.0</td>\n",
       "      <td>2019-04-25 00:00:00</td>\n",
       "      <td>2019-05-01 00:00:00</td>\n",
       "      <td>US19</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-04-07 23:46:18</td>\n",
       "      <td>USA</td>\n",
       "      <td>NR</td>\n",
       "      <td>253757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6592869</th>\n",
       "      <td>2687062843</td>\n",
       "      <td>13.2</td>\n",
       "      <td>190.4</td>\n",
       "      <td>13.7785851</td>\n",
       "      <td>242.2625</td>\n",
       "      <td>194.74</td>\n",
       "      <td>650</td>\n",
       "      <td>on</td>\n",
       "      <td>11.31294</td>\n",
       "      <td>27.26709</td>\n",
       "      <td>0</td>\n",
       "      <td>520298</td>\n",
       "      <td>100310214</td>\n",
       "      <td>2022-07-08 18:38:25</td>\n",
       "      <td>-118.809534</td>\n",
       "      <td>55.076558</td>\n",
       "      <td>Grovedale</td>\n",
       "      <td>AB</td>\n",
       "      <td>1</td>\n",
       "      <td>SPN</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>2022-07-08 18:38:33</td>\n",
       "      <td>2023-01-12 16:46:55</td>\n",
       "      <td>234.0</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>2022-07-08</td>\n",
       "      <td>33070.506</td>\n",
       "      <td>1</td>\n",
       "      <td>698.551</td>\n",
       "      <td>7192.480384</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2154.303</td>\n",
       "      <td>1</td>\n",
       "      <td>100310214</td>\n",
       "      <td>D</td>\n",
       "      <td>3634198</td>\n",
       "      <td>2020</td>\n",
       "      <td>AN64T</td>\n",
       "      <td>MACK</td>\n",
       "      <td>130</td>\n",
       "      <td>19095351.0</td>\n",
       "      <td>2020-05-28 00:00:00</td>\n",
       "      <td>CMM</td>\n",
       "      <td>UPDATED</td>\n",
       "      <td>2022-03-14 16:59:28</td>\n",
       "      <td>AN</td>\n",
       "      <td>2019-05-13 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-05-19 00:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>3.104102e+14</td>\n",
       "      <td>24200.0</td>\n",
       "      <td>2019-05-13 00:00:00</td>\n",
       "      <td>2019-05-14 00:00:00</td>\n",
       "      <td>US19</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-04-19 23:48:58</td>\n",
       "      <td>CAN</td>\n",
       "      <td>M742</td>\n",
       "      <td>13591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6592870</th>\n",
       "      <td>2687062844</td>\n",
       "      <td>13.1</td>\n",
       "      <td>190.4</td>\n",
       "      <td>13.7785851</td>\n",
       "      <td>242.2625</td>\n",
       "      <td>194.74</td>\n",
       "      <td>650</td>\n",
       "      <td>on</td>\n",
       "      <td>11.31294</td>\n",
       "      <td>27.26709</td>\n",
       "      <td>0</td>\n",
       "      <td>202</td>\n",
       "      <td>100310214</td>\n",
       "      <td>2022-07-08 18:38:25</td>\n",
       "      <td>-118.809534</td>\n",
       "      <td>55.076558</td>\n",
       "      <td>Grovedale</td>\n",
       "      <td>AB</td>\n",
       "      <td>0</td>\n",
       "      <td>PSID</td>\n",
       "      <td>9</td>\n",
       "      <td>216</td>\n",
       "      <td>N</td>\n",
       "      <td>2022-07-08 18:38:33</td>\n",
       "      <td>2023-01-12 16:46:55</td>\n",
       "      <td>234.0</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>2022-07-08</td>\n",
       "      <td>33070.506</td>\n",
       "      <td>1</td>\n",
       "      <td>698.551</td>\n",
       "      <td>7192.480384</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2154.303</td>\n",
       "      <td>1</td>\n",
       "      <td>100310214</td>\n",
       "      <td>D</td>\n",
       "      <td>3634198</td>\n",
       "      <td>2020</td>\n",
       "      <td>AN64T</td>\n",
       "      <td>MACK</td>\n",
       "      <td>130</td>\n",
       "      <td>19095351.0</td>\n",
       "      <td>2020-05-28 00:00:00</td>\n",
       "      <td>CMM</td>\n",
       "      <td>UPDATED</td>\n",
       "      <td>2022-03-14 16:59:28</td>\n",
       "      <td>AN</td>\n",
       "      <td>2019-05-13 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-05-19 00:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>3.104102e+14</td>\n",
       "      <td>24200.0</td>\n",
       "      <td>2019-05-13 00:00:00</td>\n",
       "      <td>2019-05-14 00:00:00</td>\n",
       "      <td>US19</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-04-19 23:48:58</td>\n",
       "      <td>CAN</td>\n",
       "      <td>M742</td>\n",
       "      <td>13591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6592871</th>\n",
       "      <td>2713453327</td>\n",
       "      <td>14.1</td>\n",
       "      <td>150.8</td>\n",
       "      <td>14.5182776</td>\n",
       "      <td>559.4</td>\n",
       "      <td>111.09</td>\n",
       "      <td>1279</td>\n",
       "      <td>on</td>\n",
       "      <td>21.46559</td>\n",
       "      <td>59.1754</td>\n",
       "      <td>39</td>\n",
       "      <td>1072</td>\n",
       "      <td>100426784</td>\n",
       "      <td>2022-12-28 07:50:14</td>\n",
       "      <td>-75.092346</td>\n",
       "      <td>40.204310</td>\n",
       "      <td>Hartsville</td>\n",
       "      <td>PA</td>\n",
       "      <td>1</td>\n",
       "      <td>SPN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>I</td>\n",
       "      <td>2022-12-28 07:50:14</td>\n",
       "      <td>2023-01-25 17:18:59</td>\n",
       "      <td>217.0</td>\n",
       "      <td>354.0</td>\n",
       "      <td>2022-12-28</td>\n",
       "      <td>163696.693</td>\n",
       "      <td>1</td>\n",
       "      <td>1453.452</td>\n",
       "      <td>32215.121355</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4991.257</td>\n",
       "      <td>1</td>\n",
       "      <td>100426784</td>\n",
       "      <td>D</td>\n",
       "      <td>3860720</td>\n",
       "      <td>2021</td>\n",
       "      <td>GR64F</td>\n",
       "      <td>MACK</td>\n",
       "      <td>81</td>\n",
       "      <td>20497238.0</td>\n",
       "      <td>2021-03-11 00:00:00</td>\n",
       "      <td>CML 64R</td>\n",
       "      <td>UPDATED</td>\n",
       "      <td>2022-10-31 19:26:37</td>\n",
       "      <td>GR</td>\n",
       "      <td>2020-12-23 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-03-10 00:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>3.104103e+14</td>\n",
       "      <td>29029.0</td>\n",
       "      <td>2020-12-23 00:00:00</td>\n",
       "      <td>2021-01-07 00:00:00</td>\n",
       "      <td>US20</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-11-24 23:45:08</td>\n",
       "      <td>USA</td>\n",
       "      <td>M359</td>\n",
       "      <td>24621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6592872</th>\n",
       "      <td>2713453376</td>\n",
       "      <td>14.1</td>\n",
       "      <td>161.6</td>\n",
       "      <td>14.5037738</td>\n",
       "      <td>411.4625</td>\n",
       "      <td>124.48</td>\n",
       "      <td>816</td>\n",
       "      <td>on</td>\n",
       "      <td>18.27475</td>\n",
       "      <td>58.0151</td>\n",
       "      <td>11</td>\n",
       "      <td>1072</td>\n",
       "      <td>100426784</td>\n",
       "      <td>2022-12-28 07:51:14</td>\n",
       "      <td>-75.099516</td>\n",
       "      <td>40.206567</td>\n",
       "      <td>Hartsville</td>\n",
       "      <td>PA</td>\n",
       "      <td>1</td>\n",
       "      <td>SPN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>2022-12-28 07:51:14</td>\n",
       "      <td>2023-01-25 17:19:00</td>\n",
       "      <td>307.0</td>\n",
       "      <td>331.0</td>\n",
       "      <td>2022-12-28</td>\n",
       "      <td>163699.290</td>\n",
       "      <td>1</td>\n",
       "      <td>1453.502</td>\n",
       "      <td>32216.442215</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4991.407</td>\n",
       "      <td>1</td>\n",
       "      <td>100426784</td>\n",
       "      <td>D</td>\n",
       "      <td>3860720</td>\n",
       "      <td>2021</td>\n",
       "      <td>GR64F</td>\n",
       "      <td>MACK</td>\n",
       "      <td>81</td>\n",
       "      <td>20497238.0</td>\n",
       "      <td>2021-03-11 00:00:00</td>\n",
       "      <td>CML 64R</td>\n",
       "      <td>UPDATED</td>\n",
       "      <td>2022-10-31 19:26:37</td>\n",
       "      <td>GR</td>\n",
       "      <td>2020-12-23 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-03-10 00:00:00</td>\n",
       "      <td>N</td>\n",
       "      <td>3.104103e+14</td>\n",
       "      <td>29029.0</td>\n",
       "      <td>2020-12-23 00:00:00</td>\n",
       "      <td>2021-01-07 00:00:00</td>\n",
       "      <td>US20</td>\n",
       "      <td>VOLVO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-11-24 23:45:08</td>\n",
       "      <td>USA</td>\n",
       "      <td>M359</td>\n",
       "      <td>24621</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6592873 rows × 63 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         fault_alarm_id Battery Level Coolant Temperature   \n",
       "0            2342947715          14.1               179.6  \\\n",
       "1            2342948236          14.1               179.6   \n",
       "2            2342948245          14.1               179.6   \n",
       "3            2342948249          14.2               179.6   \n",
       "4            2342948270          14.1               179.6   \n",
       "...                 ...           ...                 ...   \n",
       "6592868      2683998884          14.2               177.8   \n",
       "6592869      2687062843          13.2               190.4   \n",
       "6592870      2687062844          13.1               190.4   \n",
       "6592871      2713453327          14.1               150.8   \n",
       "6592872      2713453376          14.1               161.6   \n",
       "\n",
       "        Engine Crankcase Pressure Engine Exhaust Temperature   \n",
       "0                      14.3297285                    569.075  \\\n",
       "1                      14.3297285                    569.075   \n",
       "2                      14.3297285                    569.075   \n",
       "3                      14.3297285                    569.075   \n",
       "4                      14.3297285                    569.075   \n",
       "...                           ...                        ...   \n",
       "6592868                 14.619804                   240.6875   \n",
       "6592869                13.7785851                   242.2625   \n",
       "6592870                13.7785851                   242.2625   \n",
       "6592871                14.5182776                      559.4   \n",
       "6592872                14.5037738                   411.4625   \n",
       "\n",
       "        Engine Oil Temperature Engine Speed Fan Status On/Off   \n",
       "0                       227.19         1166                on  \\\n",
       "1                       227.19         1166                on   \n",
       "2                       227.19         1166                on   \n",
       "3                       227.19         1166                on   \n",
       "4                       227.19         1166                on   \n",
       "...                        ...          ...               ...   \n",
       "6592868                 191.98         1199                on   \n",
       "6592869                 194.74          650                on   \n",
       "6592870                 194.74          650                on   \n",
       "6592871                 111.09         1279                on   \n",
       "6592872                 124.48          816                on   \n",
       "\n",
       "        Intake Air Pressure Oil Pressure Road Speed  component_id  vehicle_id   \n",
       "0                  36.83959     50.47313         70          2370   100477846  \\\n",
       "1                  36.83959     50.47313         70            11   100477846   \n",
       "2                  36.83959     50.47313         70            14   100477846   \n",
       "3                  36.83959     50.47313         70            45   100477846   \n",
       "4                  36.83959     50.47313         70           242   100477846   \n",
       "...                     ...          ...        ...           ...         ...   \n",
       "6592868            15.08392     42.35102          4          5610   100307867   \n",
       "6592869            11.31294     27.26709          0        520298   100310214   \n",
       "6592870            11.31294     27.26709          0           202   100310214   \n",
       "6592871            21.46559      59.1754         39          1072   100426784   \n",
       "6592872            18.27475      58.0151         11          1072   100426784   \n",
       "\n",
       "           message_date_time   longitude   latitude   city_name state_cd   \n",
       "0        2022-07-01 00:00:03  -90.256296  38.035002  Bloomsdale       MO  \\\n",
       "1        2022-07-01 00:00:03  -90.256581  38.035188  Bloomsdale       MO   \n",
       "2        2022-07-01 00:00:03  -90.256581  38.035188  Bloomsdale       MO   \n",
       "3        2022-07-01 00:00:03  -90.256581  38.035188  Bloomsdale       MO   \n",
       "4        2022-07-01 00:00:03  -90.256296  38.035002  Bloomsdale       MO   \n",
       "...                      ...         ...        ...         ...      ...   \n",
       "6592868  2022-12-19 18:11:07  -87.088977  33.280599   Lake View       AL   \n",
       "6592869  2022-07-08 18:38:25 -118.809534  55.076558   Grovedale       AB   \n",
       "6592870  2022-07-08 18:38:25 -118.809534  55.076558   Grovedale       AB   \n",
       "6592871  2022-12-28 07:50:14  -75.092346  40.204310  Hartsville       PA   \n",
       "6592872  2022-12-28 07:51:14  -75.099516  40.206567  Hartsville       PA   \n",
       "\n",
       "         fault_count component_type  fmi_id  ecu_type fault_active   \n",
       "0                126            SPN       2       232            I  \\\n",
       "1                  0           PSID       5       216            Y   \n",
       "2                  0           PSID       5       216            Y   \n",
       "3                  0           PSID       5       216            Y   \n",
       "4                  0           PSID      12       140            Y   \n",
       "...              ...            ...     ...       ...          ...   \n",
       "6592868            5            SPN      11         3            N   \n",
       "6592869            1            SPN       9         0            N   \n",
       "6592870            0           PSID       9       216            N   \n",
       "6592871            1            SPN       1         0            I   \n",
       "6592872            1            SPN       1         0            N   \n",
       "\n",
       "          position_timestamp          create_date  heading  elevation   \n",
       "0        2022-07-01 00:00:14  2022-07-01 00:00:19    129.0      561.0  \\\n",
       "1        2022-07-01 00:00:13  2022-07-01 00:00:45    130.0      558.0   \n",
       "2        2022-07-01 00:00:13  2022-07-01 00:00:46    130.0      558.0   \n",
       "3        2022-07-01 00:00:13  2022-07-01 00:00:46    130.0      558.0   \n",
       "4        2022-07-01 00:00:14  2022-07-01 00:00:47    129.0      561.0   \n",
       "...                      ...                  ...      ...        ...   \n",
       "6592868  2022-12-19 18:11:01  2023-01-11 13:04:27      0.0      627.0   \n",
       "6592869  2022-07-08 18:38:33  2023-01-12 16:46:55    234.0     1900.0   \n",
       "6592870  2022-07-08 18:38:33  2023-01-12 16:46:55    234.0     1900.0   \n",
       "6592871  2022-12-28 07:50:14  2023-01-25 17:18:59    217.0      354.0   \n",
       "6592872  2022-12-28 07:51:14  2023-01-25 17:19:00    307.0      331.0   \n",
       "\n",
       "               date  odometer_reading  engine_status  idle_engine_hours   \n",
       "0        2022-07-01        155442.466              1           1032.351  \\\n",
       "1        2022-07-01        155442.466              1           1035.351   \n",
       "2        2022-07-01        155442.466              1           1035.351   \n",
       "3        2022-07-01        155442.466              1           1035.351   \n",
       "4        2022-07-01        155442.466              1           1032.351   \n",
       "...             ...               ...            ...                ...   \n",
       "6592868  2022-12-19        355276.684              0           9831.114   \n",
       "6592869  2022-07-08         33070.506              1            698.551   \n",
       "6592870  2022-07-08         33070.506              1            698.551   \n",
       "6592871  2022-12-28        163696.693              1           1453.452   \n",
       "6592872  2022-12-28        163699.290              1           1453.502   \n",
       "\n",
       "           total_fuel  gross_combined_weight  engine_hours  rank   \n",
       "0        19367.245675                    NaN      3587.855     1  \\\n",
       "1        19368.566535                    NaN      3590.855     1   \n",
       "2        19368.566535                    NaN      3590.855     1   \n",
       "3        19368.566535                    NaN      3590.855     1   \n",
       "4        19367.245675                    NaN      3587.855     1   \n",
       "...               ...                    ...           ...   ...   \n",
       "6592868  52568.257129                    NaN     15802.373     1   \n",
       "6592869   7192.480384                    NaN      2154.303     1   \n",
       "6592870   7192.480384                    NaN      2154.303     1   \n",
       "6592871  32215.121355                    NaN      4991.257     1   \n",
       "6592872  32216.442215                    NaN      4991.407     1   \n",
       "\n",
       "         vehicle_id.1 vehicle_type  vehicle_spec_id  vehicle_model_year   \n",
       "0           100477846            D          3986058                2022  \\\n",
       "1           100477846            D          3986058                2022   \n",
       "2           100477846            D          3986058                2022   \n",
       "3           100477846            D          3986058                2022   \n",
       "4           100477846            D          3986058                2022   \n",
       "...               ...          ...              ...                 ...   \n",
       "6592868     100307867            D          3631299                2020   \n",
       "6592869     100310214            D          3634198                2020   \n",
       "6592870     100310214            D          3634198                2020   \n",
       "6592871     100426784            D          3860720                2021   \n",
       "6592872     100426784            D          3860720                2021   \n",
       "\n",
       "        vehicle_model vehicle_make  veh_version  tgw_serial   \n",
       "0            VNL64T N        VOLVO           83  21337251.0  \\\n",
       "1            VNL64T N        VOLVO           83  21337251.0   \n",
       "2            VNL64T N        VOLVO           83  21337251.0   \n",
       "3            VNL64T N        VOLVO           83  21337251.0   \n",
       "4            VNL64T N        VOLVO           83  21337251.0   \n",
       "...               ...          ...          ...         ...   \n",
       "6592868      VNL64T N        VOLVO          148  19120121.0   \n",
       "6592869         AN64T         MACK          130  19095351.0   \n",
       "6592870         AN64T         MACK          130  19095351.0   \n",
       "6592871         GR64F         MACK           81  20497238.0   \n",
       "6592872         GR64F         MACK           81  20497238.0   \n",
       "\n",
       "                   retail_dt product_type notif_type    mq_message_put_dt   \n",
       "0        2021-11-02 00:00:00         VNL6    UPDATED  2023-02-08 20:32:19  \\\n",
       "1        2021-11-02 00:00:00         VNL6    UPDATED  2023-02-08 20:32:19   \n",
       "2        2021-11-02 00:00:00         VNL6    UPDATED  2023-02-08 20:32:19   \n",
       "3        2021-11-02 00:00:00         VNL6    UPDATED  2023-02-08 20:32:19   \n",
       "4        2021-11-02 00:00:00         VNL6    UPDATED  2023-02-08 20:32:19   \n",
       "...                      ...          ...        ...                  ...   \n",
       "6592868  2020-03-23 00:00:00         VNL6    UPDATED  2022-11-07 18:44:36   \n",
       "6592869  2020-05-28 00:00:00          CMM    UPDATED  2022-03-14 16:59:28   \n",
       "6592870  2020-05-28 00:00:00          CMM    UPDATED  2022-03-14 16:59:28   \n",
       "6592871  2021-03-11 00:00:00      CML 64R    UPDATED  2022-10-31 19:26:37   \n",
       "6592872  2021-03-11 00:00:00      CML 64R    UPDATED  2022-10-31 19:26:37   \n",
       "\n",
       "        model_family     main_assembly_dt  inactive_cd        in_service_dt   \n",
       "0                 VN  2021-09-08 00:00:00          NaN  2021-11-12 00:00:00  \\\n",
       "1                 VN  2021-09-08 00:00:00          NaN  2021-11-12 00:00:00   \n",
       "2                 VN  2021-09-08 00:00:00          NaN  2021-11-12 00:00:00   \n",
       "3                 VN  2021-09-08 00:00:00          NaN  2021-11-12 00:00:00   \n",
       "4                 VN  2021-09-08 00:00:00          NaN  2021-11-12 00:00:00   \n",
       "...              ...                  ...          ...                  ...   \n",
       "6592868           VN  2019-04-25 00:00:00          NaN  2020-03-23 00:00:00   \n",
       "6592869           AN  2019-05-13 00:00:00          NaN  2020-05-19 00:00:00   \n",
       "6592870           AN  2019-05-13 00:00:00          NaN  2020-05-19 00:00:00   \n",
       "6592871           GR  2020-12-23 00:00:00          NaN  2021-03-10 00:00:00   \n",
       "6592872           GR  2020-12-23 00:00:00          NaN  2021-03-10 00:00:00   \n",
       "\n",
       "        in_process          imsi  gross_wt_rating    final_assembly_dt   \n",
       "0                N  3.104104e+14          22838.0  2021-09-08 00:00:00  \\\n",
       "1                N  3.104104e+14          22838.0  2021-09-08 00:00:00   \n",
       "2                N  3.104104e+14          22838.0  2021-09-08 00:00:00   \n",
       "3                N  3.104104e+14          22838.0  2021-09-08 00:00:00   \n",
       "4                N  3.104104e+14          22838.0  2021-09-08 00:00:00   \n",
       "...            ...           ...              ...                  ...   \n",
       "6592868          N  3.104102e+14          23223.0  2019-04-25 00:00:00   \n",
       "6592869          N  3.104102e+14          24200.0  2019-05-13 00:00:00   \n",
       "6592870          N  3.104102e+14          24200.0  2019-05-13 00:00:00   \n",
       "6592871          N  3.104103e+14          29029.0  2020-12-23 00:00:00   \n",
       "6592872          N  3.104103e+14          29029.0  2020-12-23 00:00:00   \n",
       "\n",
       "          factory_invoice_dt engine_type engine_make  dispatch_dt   \n",
       "0        2021-10-13 00:00:00        US21       VOLVO          NaN  \\\n",
       "1        2021-10-13 00:00:00        US21       VOLVO          NaN   \n",
       "2        2021-10-13 00:00:00        US21       VOLVO          NaN   \n",
       "3        2021-10-13 00:00:00        US21       VOLVO          NaN   \n",
       "4        2021-10-13 00:00:00        US21       VOLVO          NaN   \n",
       "...                      ...         ...         ...          ...   \n",
       "6592868  2019-05-01 00:00:00        US19       VOLVO          NaN   \n",
       "6592869  2019-05-14 00:00:00        US19       VOLVO          NaN   \n",
       "6592870  2019-05-14 00:00:00        US19       VOLVO          NaN   \n",
       "6592871  2021-01-07 00:00:00        US20       VOLVO          NaN   \n",
       "6592872  2021-01-07 00:00:00        US20       VOLVO          NaN   \n",
       "\n",
       "                   create_dt country_of_operation chassis_series   \n",
       "0        2021-08-08 22:09:58                  USA             NR  \\\n",
       "1        2021-08-08 22:09:58                  USA             NR   \n",
       "2        2021-08-08 22:09:58                  USA             NR   \n",
       "3        2021-08-08 22:09:58                  USA             NR   \n",
       "4        2021-08-08 22:09:58                  USA             NR   \n",
       "...                      ...                  ...            ...   \n",
       "6592868  2019-04-07 23:46:18                  USA             NR   \n",
       "6592869  2019-04-19 23:48:58                  CAN           M742   \n",
       "6592870  2019-04-19 23:48:58                  CAN           M742   \n",
       "6592871  2020-11-24 23:45:08                  USA           M359   \n",
       "6592872  2020-11-24 23:45:08                  USA           M359   \n",
       "\n",
       "         chassis_number  \n",
       "0                283934  \n",
       "1                283934  \n",
       "2                283934  \n",
       "3                283934  \n",
       "4                283934  \n",
       "...                 ...  \n",
       "6592868          253757  \n",
       "6592869           13591  \n",
       "6592870           13591  \n",
       "6592871           24621  \n",
       "6592872           24621  \n",
       "\n",
       "[6592873 rows x 63 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "with pd.option_context('display.max_columns', None):  # more options can be specified also\n",
    "    display(df_all)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4bd64774",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write df_all to csv\n",
    "# df_all.to_csv(\"RED_snaps_pivoted_joined.csv\", index=False)\n",
    "# df_all.to_csv(\"GREY_snaps_pivoted_joined.csv\", index=False)\n",
    "# df_all.to_csv(\"ORANGE_snaps_pivoted_joined.csv\", index=False)\n",
    "df_all.to_csv(\"BLACK_snaps_pivoted_joined.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}