02-import-data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2-Import Data\n",
    "In this notebook, we read data from the downloaded CSV files and Excel files, and import it into a SQLite database.\n",
    "\n",
    "**Requirements:**\n",
    "\n",
    "- Please run the `01-download-ved.ipynb` notebook first.\n",
    "- Recommended install: [ipywidgets](https://ipywidgets.readthedocs.io/en/stable/user_install.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import sqlite3\n",
    "import os\n",
    "\n",
    "from tqdm.notebook import tqdm\n",
    "from db.api import VedDb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set the data path and target file name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_path = \"./data\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `read_data_frame` reads data from a single CSV file into a Pandas DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def read_data_frame(filename):\n",
    "    columns = ['DayNum', 'VehId', 'Trip', 'Timestamp(ms)', \n",
    "               'Latitude[deg]', 'Longitude[deg]', \n",
    "               'Vehicle Speed[km/h]', 'MAF[g/sec]', \n",
    "               'Engine RPM[RPM]', 'Absolute Load[%]',\n",
    "               'OAT[DegC]', 'Fuel Rate[L/hr]', \n",
    "               'Air Conditioning Power[kW]', 'Air Conditioning Power[Watts]',\n",
    "               'Heater Power[Watts]', 'HV Battery Current[A]', \n",
    "               'HV Battery SOC[%]', 'HV Battery Voltage[V]',\n",
    "               'Short Term Fuel Trim Bank 1[%]', 'Short Term Fuel Trim Bank 2[%]',\n",
    "               'Long Term Fuel Trim Bank 1[%]', 'Long Term Fuel Trim Bank 2[%]'\n",
    "              ]\n",
    "    types = {'VehId': np.int64,\n",
    "             'Trip': np.int64,\n",
    "             'Timestamp(ms)': np.int64\n",
    "            }\n",
    "    df = pd.read_csv(filename, usecols=columns, dtype=types)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Enumerate the CSV files from the expanded data directory."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "files = [os.path.join(data_path, file) for file in tqdm(os.listdir(data_path)) if file.endswith(\".csv\")]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a `VedDb` object. This is the API to interface with the SQLite database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "db = VedDb()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Iterate through the data files, import them into a Pandas DataFrame and then insert the signals into the database. Note that the signals are bulk-inserted, so the DataFrame is actually converted to a list of tuples."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for file in tqdm(files):\n",
    "    df = read_data_frame(file)\n",
    "    \n",
    "    signals = []\n",
    "    for row in df.itertuples(index=False):\n",
    "        signals.append(row)\n",
    "        \n",
    "    db.insert_signals(signals)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can load the static data from the ICE & HEV vehicles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ice_hev = pd.read_excel(\"./ved/Data/VED_Static_Data_ICE&HEV.xlsx\").replace('NO DATA', np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ice_hev.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We follow by reading the PHEV and EV static data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_phev_ev = pd.read_excel(\"./ved/Data/VED_Static_Data_PHEV&EV.xlsx\").replace('NO DATA', np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_phev_ev.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we collect all vehicle definitions in one list and bulk insert it into the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "vehicles = []\n",
    "\n",
    "for row in df_ice_hev.itertuples(index=False):\n",
    "    vehicles.append(row)\n",
    "for row in df_phev_ev.itertuples(index=False):\n",
    "    vehicles.append(row)\n",
    "    \n",
    "db.insert_vehicles(vehicles)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This creates the first image of the database. In subsequent notebooks we will use it to further analyse the data and derive some (hopefully) interesting models."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Generate data for the `move` table. Here we identify single trips per vehicle. Later we will use this table for joins when exploring the trajectory data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "db.generate_moves()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}