docs/examples/academy/25. Filtering by Vessel.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Module 5\n",
    "\n",
    "## Video 25: Filtering by Vessel\n",
    "**Python for the Energy Industry**\n",
    "\n",
    "[Vessels endpoint documentation.](https://vortechsa.github.io/python-sdk/endpoints/vessels/)\n",
    "\n",
    "By now, you should be well used to the process of filtering Cargo Movements, and searching the relevant endpoints to get IDs of interest for filtering. This works in exactly the same way for filtering Cargo Movements that are being carried on a known Vessel:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Some initial imports / settings\n",
    "from datetime import datetime\n",
    "from dateutil.relativedelta import relativedelta\n",
    "import vortexasdk as v\n",
    "\n",
    "now = datetime.utcnow()\n",
    "one_month_ago = now - relativedelta(months=1)\n",
    "\n",
    "required_columns = [\"vessels.0.name\",\"vessels.0.vessel_class\",\"product.group.label\",\"product.grade.label\",\"quantity\",\n",
    "                    \"status\",\"events.cargo_port_load_event.0.location.port.label\",\"events.cargo_port_load_event.0.end_timestamp\",\n",
    "                    \"events.cargo_port_unload_event.0.location.port.label\",\"events.cargo_port_unload_event.0.end_timestamp\"]\n",
    "\n",
    "new_labels = [\"vessel_name\",\"vessel_class\",\"product_group\",\"product_grade\",\"quantity\",\"status\",\"loading_port\",\"loading_finish\",\"unloading_port\",\"unloading_finish\"]\n",
    "\n",
    "relabel = dict(zip(required_columns,new_labels))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead 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>vessel_name</th>\n",
       "      <th>vessel_class</th>\n",
       "      <th>product_group</th>\n",
       "      <th>product_grade</th>\n",
       "      <th>quantity</th>\n",
       "      <th>status</th>\n",
       "      <th>loading_port</th>\n",
       "      <th>loading_finish</th>\n",
       "      <th>unloading_port</th>\n",
       "      <th>unloading_finish</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CRIMSON PEARL</td>\n",
       "      <td>handymax</td>\n",
       "      <td>Clean Petroleum Products</td>\n",
       "      <td>NaN</td>\n",
       "      <td>292375</td>\n",
       "      <td>unloaded_state</td>\n",
       "      <td>Brofjorden [SE]</td>\n",
       "      <td>2021-01-12 01:33:36+00:00</td>\n",
       "      <td>Amsterdam [NL]</td>\n",
       "      <td>2021-01-16 05:50:14+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>OCEANIC CRIMSON</td>\n",
       "      <td>general_purpose</td>\n",
       "      <td>Clean Petroleum Products</td>\n",
       "      <td>NaN</td>\n",
       "      <td>57142</td>\n",
       "      <td>unloaded_state</td>\n",
       "      <td>Hamburg [DE]</td>\n",
       "      <td>2021-01-10 18:03:14+00:00</td>\n",
       "      <td>Bejaia [DZ]</td>\n",
       "      <td>2021-01-23 15:22:33+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CRIMSON RAY</td>\n",
       "      <td>general_purpose</td>\n",
       "      <td>Clean Petroleum Products</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17730</td>\n",
       "      <td>unloaded_state</td>\n",
       "      <td>Aqaba [JO]</td>\n",
       "      <td>2021-01-23 19:17:56+00:00</td>\n",
       "      <td>Barcelona [ES]</td>\n",
       "      <td>2021-02-03 08:30:38+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CRIMSON PEARL</td>\n",
       "      <td>handymax</td>\n",
       "      <td>Clean Petroleum Products</td>\n",
       "      <td>Motor Spirit</td>\n",
       "      <td>181794</td>\n",
       "      <td>transiting_state</td>\n",
       "      <td>Antwerp [BE]</td>\n",
       "      <td>2021-01-22 23:40:00+00:00</td>\n",
       "      <td>Conakry [GN]</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LADY ROISE</td>\n",
       "      <td>sgc</td>\n",
       "      <td>Clean Petroleum Products</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13284</td>\n",
       "      <td>unloaded_state</td>\n",
       "      <td>Daesan [KR]</td>\n",
       "      <td>2021-01-30 10:56:38+00:00</td>\n",
       "      <td>Kaohsiung [TW]</td>\n",
       "      <td>2021-02-03 10:05:07+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>SAEHAN SELINA</td>\n",
       "      <td>sgc</td>\n",
       "      <td>Clean Petroleum Products</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18982</td>\n",
       "      <td>unloaded_state</td>\n",
       "      <td>Daesan [KR]</td>\n",
       "      <td>2021-01-24 21:08:52+00:00</td>\n",
       "      <td>Dongying [CN]</td>\n",
       "      <td>2021-02-03 05:35:44+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SAEHAN VIRTUS</td>\n",
       "      <td>sgc</td>\n",
       "      <td>Clean Petroleum Products</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20580</td>\n",
       "      <td>unloaded_state</td>\n",
       "      <td>Daesan [KR]</td>\n",
       "      <td>2021-01-23 21:01:02+00:00</td>\n",
       "      <td>Mizushima [JP]</td>\n",
       "      <td>2021-02-04 08:39:12+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SAEHAN SELINA</td>\n",
       "      <td>sgc</td>\n",
       "      <td>Clean Petroleum Products</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20580</td>\n",
       "      <td>loading_state</td>\n",
       "      <td>Daesan [KR]</td>\n",
       "      <td>2021-02-09 08:04:59+00:00</td>\n",
       "      <td>Cao Jing [CN]</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>SAEHAN VIRTUS</td>\n",
       "      <td>sgc</td>\n",
       "      <td>Clean Petroleum Products</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20240</td>\n",
       "      <td>unloaded_state</td>\n",
       "      <td>Jiangyin [CN]</td>\n",
       "      <td>2021-01-17 23:15:33+00:00</td>\n",
       "      <td>Ulsan, Onsan [KR]</td>\n",
       "      <td>2021-01-20 14:47:25+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>SAEHAN VIRTUS</td>\n",
       "      <td>sgc</td>\n",
       "      <td>Clean Petroleum Products</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20240</td>\n",
       "      <td>transiting_state</td>\n",
       "      <td>Daesan [KR]</td>\n",
       "      <td>2021-02-07 15:31:59+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>SAEHAN SELINA</td>\n",
       "      <td>sgc</td>\n",
       "      <td>Clean Petroleum Products</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20580</td>\n",
       "      <td>unloaded_state</td>\n",
       "      <td>Daesan [KR]</td>\n",
       "      <td>2021-01-17 23:49:51+00:00</td>\n",
       "      <td>Lianyungang [CN]</td>\n",
       "      <td>2021-01-20 04:36:55+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>CRIMSON PEARL</td>\n",
       "      <td>handymax</td>\n",
       "      <td>Clean Petroleum Products</td>\n",
       "      <td>Motor Spirit</td>\n",
       "      <td>123940</td>\n",
       "      <td>unloaded_state</td>\n",
       "      <td>Antwerp [BE]</td>\n",
       "      <td>2021-01-22 23:40:00+00:00</td>\n",
       "      <td>Freetown [SL]</td>\n",
       "      <td>2021-02-06 11:26:30+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>LADY ROISE</td>\n",
       "      <td>sgc</td>\n",
       "      <td>Clean Petroleum Products</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14768</td>\n",
       "      <td>unloaded_state</td>\n",
       "      <td>Ulsan, Onsan [KR]</td>\n",
       "      <td>2021-01-19 18:24:41+00:00</td>\n",
       "      <td>Kaohsiung [TW]</td>\n",
       "      <td>2021-01-24 03:45:55+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>LADY ROISE</td>\n",
       "      <td>sgc</td>\n",
       "      <td>Clean Petroleum Products</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14768</td>\n",
       "      <td>unloaded_state</td>\n",
       "      <td>Daesan [KR]</td>\n",
       "      <td>2021-01-11 21:58:38+00:00</td>\n",
       "      <td>Taixing [CN]</td>\n",
       "      <td>2021-01-17 02:52:13+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>OCEANIC CRIMSON</td>\n",
       "      <td>general_purpose</td>\n",
       "      <td>Clean Petroleum Products</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90673</td>\n",
       "      <td>transiting_state</td>\n",
       "      <td>Nikolayev (Mykolaiv) [UA]</td>\n",
       "      <td>2021-02-07 22:22:30+00:00</td>\n",
       "      <td>Cartagena [ES]</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        vessel_name     vessel_class             product_group product_grade  \\\n",
       "0     CRIMSON PEARL         handymax  Clean Petroleum Products           NaN   \n",
       "1   OCEANIC CRIMSON  general_purpose  Clean Petroleum Products           NaN   \n",
       "2       CRIMSON RAY  general_purpose  Clean Petroleum Products           NaN   \n",
       "3     CRIMSON PEARL         handymax  Clean Petroleum Products  Motor Spirit   \n",
       "4        LADY ROISE              sgc  Clean Petroleum Products           NaN   \n",
       "5     SAEHAN SELINA              sgc  Clean Petroleum Products           NaN   \n",
       "6     SAEHAN VIRTUS              sgc  Clean Petroleum Products           NaN   \n",
       "7     SAEHAN SELINA              sgc  Clean Petroleum Products           NaN   \n",
       "8     SAEHAN VIRTUS              sgc  Clean Petroleum Products           NaN   \n",
       "9     SAEHAN VIRTUS              sgc  Clean Petroleum Products           NaN   \n",
       "10    SAEHAN SELINA              sgc  Clean Petroleum Products           NaN   \n",
       "11    CRIMSON PEARL         handymax  Clean Petroleum Products  Motor Spirit   \n",
       "12       LADY ROISE              sgc  Clean Petroleum Products           NaN   \n",
       "13       LADY ROISE              sgc  Clean Petroleum Products           NaN   \n",
       "14  OCEANIC CRIMSON  general_purpose  Clean Petroleum Products           NaN   \n",
       "\n",
       "    quantity            status               loading_port  \\\n",
       "0     292375    unloaded_state            Brofjorden [SE]   \n",
       "1      57142    unloaded_state               Hamburg [DE]   \n",
       "2      17730    unloaded_state                 Aqaba [JO]   \n",
       "3     181794  transiting_state               Antwerp [BE]   \n",
       "4      13284    unloaded_state                Daesan [KR]   \n",
       "5      18982    unloaded_state                Daesan [KR]   \n",
       "6      20580    unloaded_state                Daesan [KR]   \n",
       "7      20580     loading_state                Daesan [KR]   \n",
       "8      20240    unloaded_state              Jiangyin [CN]   \n",
       "9      20240  transiting_state                Daesan [KR]   \n",
       "10     20580    unloaded_state                Daesan [KR]   \n",
       "11    123940    unloaded_state               Antwerp [BE]   \n",
       "12     14768    unloaded_state          Ulsan, Onsan [KR]   \n",
       "13     14768    unloaded_state                Daesan [KR]   \n",
       "14     90673  transiting_state  Nikolayev (Mykolaiv) [UA]   \n",
       "\n",
       "              loading_finish     unloading_port          unloading_finish  \n",
       "0  2021-01-12 01:33:36+00:00     Amsterdam [NL] 2021-01-16 05:50:14+00:00  \n",
       "1  2021-01-10 18:03:14+00:00        Bejaia [DZ] 2021-01-23 15:22:33+00:00  \n",
       "2  2021-01-23 19:17:56+00:00     Barcelona [ES] 2021-02-03 08:30:38+00:00  \n",
       "3  2021-01-22 23:40:00+00:00       Conakry [GN]                       NaT  \n",
       "4  2021-01-30 10:56:38+00:00     Kaohsiung [TW] 2021-02-03 10:05:07+00:00  \n",
       "5  2021-01-24 21:08:52+00:00      Dongying [CN] 2021-02-03 05:35:44+00:00  \n",
       "6  2021-01-23 21:01:02+00:00     Mizushima [JP] 2021-02-04 08:39:12+00:00  \n",
       "7  2021-02-09 08:04:59+00:00      Cao Jing [CN]                       NaT  \n",
       "8  2021-01-17 23:15:33+00:00  Ulsan, Onsan [KR] 2021-01-20 14:47:25+00:00  \n",
       "9  2021-02-07 15:31:59+00:00                NaN                       NaT  \n",
       "10 2021-01-17 23:49:51+00:00   Lianyungang [CN] 2021-01-20 04:36:55+00:00  \n",
       "11 2021-01-22 23:40:00+00:00      Freetown [SL] 2021-02-06 11:26:30+00:00  \n",
       "12 2021-01-19 18:24:41+00:00     Kaohsiung [TW] 2021-01-24 03:45:55+00:00  \n",
       "13 2021-01-11 21:58:38+00:00       Taixing [CN] 2021-01-17 02:52:13+00:00  \n",
       "14 2021-02-07 22:22:30+00:00     Cartagena [ES]                       NaT  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get ID for all vessels with 'CRIMSON' in the name\n",
    "crimson_id = [ves['id'] for ves in v.Vessels().search(term='CRIMSON')]\n",
    "\n",
    "# filter for cargo movements on CRIMSON\n",
    "crimson_cm = v.CargoMovements().search(\n",
    "    filter_activity=\"loading_state\",\n",
    "    filter_time_min=one_month_ago,\n",
    "    filter_time_max=now,\n",
    "    filter_vessels=crimson_id)\n",
    "\n",
    "crimson_cm.to_df(columns=required_columns).rename(relabel,axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another way of using the Vessels endpoint is to limit searches based on vessel class. Say we are interested only in VLCC movements from Saudi Arabia:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get all vessels of vlcc class\n",
    "vlcc = v.Vessels().search(vessel_classes='vlcc')\n",
    "\n",
    "# get the ids of these vessels\n",
    "vlcc_ids = [ves['id'] for ves in vlcc]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# saudi arabia id\n",
    "sa_search = v.Geographies().search(\"Saudi Arabia\",exact_term_match=True)\n",
    "assert len(sa_search) == 1\n",
    "sa_ID = sa_search[0]['id']\n",
    "\n",
    "# filter for vlcc movements out of saudi arabia\n",
    "vlcc_query = v.CargoMovements().search(\n",
    "    filter_activity=\"loading_state\",\n",
    "    filter_time_min=one_month_ago,\n",
    "    filter_time_max=now,\n",
    "    filter_vessels=vlcc_ids,\n",
    "    filter_origins=sa_ID)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>vessel_name</th>\n",
       "      <th>vessel_class</th>\n",
       "      <th>product_group</th>\n",
       "      <th>product_grade</th>\n",
       "      <th>quantity</th>\n",
       "      <th>status</th>\n",
       "      <th>loading_port</th>\n",
       "      <th>loading_finish</th>\n",
       "      <th>unloading_port</th>\n",
       "      <th>unloading_finish</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>KASAGISAN</td>\n",
       "      <td>vlcc_plus</td>\n",
       "      <td>Crude/Condensates</td>\n",
       "      <td>Arab Medium</td>\n",
       "      <td>2110718</td>\n",
       "      <td>unloaded_state</td>\n",
       "      <td>Juaymah Terminal [SA]</td>\n",
       "      <td>2020-11-14 07:16:42+00:00</td>\n",
       "      <td>Quanzhou [CN]</td>\n",
       "      <td>2020-12-07 08:53:54+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NEW PROSPECT</td>\n",
       "      <td>vlcc_plus</td>\n",
       "      <td>Crude/Condensates</td>\n",
       "      <td>Arab Light</td>\n",
       "      <td>1969827</td>\n",
       "      <td>transiting_state</td>\n",
       "      <td>Juaymah Terminal [SA]</td>\n",
       "      <td>2020-11-26 23:17:14+00:00</td>\n",
       "      <td>Kaohsiung [TW]</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C.MIGHTY</td>\n",
       "      <td>vlcc_plus</td>\n",
       "      <td>Crude/Condensates</td>\n",
       "      <td>Arab Medium</td>\n",
       "      <td>2035637</td>\n",
       "      <td>transiting_state</td>\n",
       "      <td>Juaymah Terminal [SA]</td>\n",
       "      <td>2020-11-21 08:56:21+00:00</td>\n",
       "      <td>Zhoushan [CN]</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GASSAN</td>\n",
       "      <td>vlcc_plus</td>\n",
       "      <td>Crude/Condensates</td>\n",
       "      <td>Arab Light</td>\n",
       "      <td>301149</td>\n",
       "      <td>transiting_state</td>\n",
       "      <td>Ras Tanura [SA]</td>\n",
       "      <td>2020-11-12 05:32:48+00:00</td>\n",
       "      <td>Sendai, Miyagi [JP]</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TENRYU</td>\n",
       "      <td>vlcc_plus</td>\n",
       "      <td>Crude/Condensates</td>\n",
       "      <td>Arab Light</td>\n",
       "      <td>565346</td>\n",
       "      <td>transiting_state</td>\n",
       "      <td>Ras Tanura [SA]</td>\n",
       "      <td>2020-11-17 16:49:22+00:00</td>\n",
       "      <td>Singapore [SG]</td>\n",
       "      <td>NaT</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>ABQAIQ</td>\n",
       "      <td>vlcc_plus</td>\n",
       "      <td>Crude/Condensates</td>\n",
       "      <td>Arab Light</td>\n",
       "      <td>2102642</td>\n",
       "      <td>transiting_state</td>\n",
       "      <td>Yanbu Industrial Port [SA]</td>\n",
       "      <td>2020-12-10 00:34:23+00:00</td>\n",
       "      <td>Ain Sukhna [EG]</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>M.T. TENJUN</td>\n",
       "      <td>vlcc_plus</td>\n",
       "      <td>Crude/Condensates</td>\n",
       "      <td>Arab Heavy</td>\n",
       "      <td>591455</td>\n",
       "      <td>loading_state</td>\n",
       "      <td>Ras Tanura [SA]</td>\n",
       "      <td>2020-12-11 10:32:47+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>ATHINA II</td>\n",
       "      <td>vlcc_plus</td>\n",
       "      <td>Crude/Condensates</td>\n",
       "      <td>Arab Heavy</td>\n",
       "      <td>1383057</td>\n",
       "      <td>transiting_state</td>\n",
       "      <td>Juaymah Terminal [SA]</td>\n",
       "      <td>2020-12-04 02:22:16+00:00</td>\n",
       "      <td>New Mangalore [IN]</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>ABQAIQ</td>\n",
       "      <td>vlcc_plus</td>\n",
       "      <td>Crude/Condensates</td>\n",
       "      <td>Arab Light</td>\n",
       "      <td>2102705</td>\n",
       "      <td>unloaded_state</td>\n",
       "      <td>Yanbu Industrial Port [SA]</td>\n",
       "      <td>2020-11-27 23:27:01+00:00</td>\n",
       "      <td>Ain Sukhna [EG]</td>\n",
       "      <td>2020-12-04 20:17:20+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>ENEOS TOKYO</td>\n",
       "      <td>vlcc_plus</td>\n",
       "      <td>Crude/Condensates</td>\n",
       "      <td>Arab Light</td>\n",
       "      <td>760348</td>\n",
       "      <td>transiting_state</td>\n",
       "      <td>Ras Tanura [SA]</td>\n",
       "      <td>2020-12-09 01:49:46+00:00</td>\n",
       "      <td>Fujairah [AE]</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>107 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      vessel_name vessel_class      product_group product_grade  quantity  \\\n",
       "0       KASAGISAN    vlcc_plus  Crude/Condensates   Arab Medium   2110718   \n",
       "1    NEW PROSPECT    vlcc_plus  Crude/Condensates    Arab Light   1969827   \n",
       "2        C.MIGHTY    vlcc_plus  Crude/Condensates   Arab Medium   2035637   \n",
       "3          GASSAN    vlcc_plus  Crude/Condensates    Arab Light    301149   \n",
       "4          TENRYU    vlcc_plus  Crude/Condensates    Arab Light    565346   \n",
       "..            ...          ...                ...           ...       ...   \n",
       "102        ABQAIQ    vlcc_plus  Crude/Condensates    Arab Light   2102642   \n",
       "103   M.T. TENJUN    vlcc_plus  Crude/Condensates    Arab Heavy    591455   \n",
       "104     ATHINA II    vlcc_plus  Crude/Condensates    Arab Heavy   1383057   \n",
       "105        ABQAIQ    vlcc_plus  Crude/Condensates    Arab Light   2102705   \n",
       "106   ENEOS TOKYO    vlcc_plus  Crude/Condensates    Arab Light    760348   \n",
       "\n",
       "               status                loading_port            loading_finish  \\\n",
       "0      unloaded_state       Juaymah Terminal [SA] 2020-11-14 07:16:42+00:00   \n",
       "1    transiting_state       Juaymah Terminal [SA] 2020-11-26 23:17:14+00:00   \n",
       "2    transiting_state       Juaymah Terminal [SA] 2020-11-21 08:56:21+00:00   \n",
       "3    transiting_state             Ras Tanura [SA] 2020-11-12 05:32:48+00:00   \n",
       "4    transiting_state             Ras Tanura [SA] 2020-11-17 16:49:22+00:00   \n",
       "..                ...                         ...                       ...   \n",
       "102  transiting_state  Yanbu Industrial Port [SA] 2020-12-10 00:34:23+00:00   \n",
       "103     loading_state             Ras Tanura [SA] 2020-12-11 10:32:47+00:00   \n",
       "104  transiting_state       Juaymah Terminal [SA] 2020-12-04 02:22:16+00:00   \n",
       "105    unloaded_state  Yanbu Industrial Port [SA] 2020-11-27 23:27:01+00:00   \n",
       "106  transiting_state             Ras Tanura [SA] 2020-12-09 01:49:46+00:00   \n",
       "\n",
       "          unloading_port          unloading_finish  \n",
       "0          Quanzhou [CN] 2020-12-07 08:53:54+00:00  \n",
       "1         Kaohsiung [TW]                       NaT  \n",
       "2          Zhoushan [CN]                       NaT  \n",
       "3    Sendai, Miyagi [JP]                       NaT  \n",
       "4         Singapore [SG]                       NaT  \n",
       "..                   ...                       ...  \n",
       "102      Ain Sukhna [EG]                       NaT  \n",
       "103                  NaN                       NaT  \n",
       "104   New Mangalore [IN]                       NaT  \n",
       "105      Ain Sukhna [EG] 2020-12-04 20:17:20+00:00  \n",
       "106        Fujairah [AE]                       NaT  \n",
       "\n",
       "[107 rows x 10 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert to dataframe\n",
    "vlcc_query.to_df(columns=required_columns).rename(relabel,axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Storing IDs in a Dictionary\n",
    "\n",
    "If there is a group of vessels of interest, it may be useful to store their IDs in a dictionary for easy access (of course, this applies to IDs for all other types of filter, as well)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>imo</th>\n",
       "      <th>vessel_class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a8daaa7b46f01a01b0b003f51116882c6df4b18210c2b8...</td>\n",
       "      <td>DELTA GLORY</td>\n",
       "      <td>9602617</td>\n",
       "      <td>vlcc_plus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>51d8eeb4fbb6691756594f6143e1b3ce8b74437f50c800...</td>\n",
       "      <td>ENEOS GLORY</td>\n",
       "      <td>9851608</td>\n",
       "      <td>vlcc_plus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>05263ff056489d1cf2c264e708e6e8bb1da502a216ece6...</td>\n",
       "      <td>V. GLORY</td>\n",
       "      <td>9849851</td>\n",
       "      <td>vlcc_plus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6a9b0048c3356d9975bef63da82e3a1ec04c9668066353...</td>\n",
       "      <td>ATHENIAN GLORY</td>\n",
       "      <td>9429302</td>\n",
       "      <td>vlcc_plus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5f9d7b1329f44ef585bfa506d1d0c52a16eb3d5ef01c1b...</td>\n",
       "      <td>CARIBBEAN GLORY</td>\n",
       "      <td>9788875</td>\n",
       "      <td>vlcc_plus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>c7cd0caacfc8d5b1ffafe6a623cd0c437843fb76100a58...</td>\n",
       "      <td>BALTIC GLORY</td>\n",
       "      <td>9307645</td>\n",
       "      <td>vlcc_plus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>e6ff63f16630fdb1e82d856f3e3c2466ed8359d41a43d7...</td>\n",
       "      <td>LANDBRIDGE GLORY</td>\n",
       "      <td>9828778</td>\n",
       "      <td>vlcc_plus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>8faaab9fad4706d8bd19d908d55ab4368fa28ef422893e...</td>\n",
       "      <td>PLATA GLORY</td>\n",
       "      <td>9172674</td>\n",
       "      <td>vlcc_plus</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   id              name  \\\n",
       "0   a8daaa7b46f01a01b0b003f51116882c6df4b18210c2b8...       DELTA GLORY   \n",
       "1   51d8eeb4fbb6691756594f6143e1b3ce8b74437f50c800...       ENEOS GLORY   \n",
       "2   05263ff056489d1cf2c264e708e6e8bb1da502a216ece6...          V. GLORY   \n",
       "3   6a9b0048c3356d9975bef63da82e3a1ec04c9668066353...    ATHENIAN GLORY   \n",
       "4   5f9d7b1329f44ef585bfa506d1d0c52a16eb3d5ef01c1b...   CARIBBEAN GLORY   \n",
       "7   c7cd0caacfc8d5b1ffafe6a623cd0c437843fb76100a58...      BALTIC GLORY   \n",
       "9   e6ff63f16630fdb1e82d856f3e3c2466ed8359d41a43d7...  LANDBRIDGE GLORY   \n",
       "14  8faaab9fad4706d8bd19d908d55ab4368fa28ef422893e...       PLATA GLORY   \n",
       "\n",
       "        imo vessel_class  \n",
       "0   9602617    vlcc_plus  \n",
       "1   9851608    vlcc_plus  \n",
       "2   9849851    vlcc_plus  \n",
       "3   9429302    vlcc_plus  \n",
       "4   9788875    vlcc_plus  \n",
       "7   9307645    vlcc_plus  \n",
       "9   9828778    vlcc_plus  \n",
       "14  9172674    vlcc_plus  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# search for all vlcc vessels with the term 'glory'\n",
    "vlcc_glory = v.Vessels().search(vessel_classes='vlcc',term='glory')\n",
    "vlcc_glory_df = vlcc_glory.to_df()\n",
    "\n",
    "# limit to those with 'glory' in the name\n",
    "vlcc_glory_df = vlcc_glory_df[vlcc_glory_df['name'].str.contains('GLORY')]\n",
    "vlcc_glory_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "vlcc_glory_dict = dict(zip(vlcc_glory_df['name'],vlcc_glory_df['id']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'DELTA GLORY': 'a8daaa7b46f01a01b0b003f51116882c6df4b18210c2b8c26e96dccac3eba696',\n",
       " 'ENEOS GLORY': '51d8eeb4fbb6691756594f6143e1b3ce8b74437f50c80062785c1101f46aa652',\n",
       " 'V. GLORY': '05263ff056489d1cf2c264e708e6e8bb1da502a216ece68cfeee1105307dbd0a',\n",
       " 'ATHENIAN GLORY': '6a9b0048c3356d9975bef63da82e3a1ec04c966806635377ae6aa9b42d97c66e',\n",
       " 'CARIBBEAN GLORY': '5f9d7b1329f44ef585bfa506d1d0c52a16eb3d5ef01c1bf031da37849c3bfdf0',\n",
       " 'BALTIC GLORY': 'c7cd0caacfc8d5b1ffafe6a623cd0c437843fb76100a5828793d38cf92da6344',\n",
       " 'LANDBRIDGE GLORY': 'e6ff63f16630fdb1e82d856f3e3c2466ed8359d41a43d74d220e8e770769b240',\n",
       " 'PLATA GLORY': '8faaab9fad4706d8bd19d908d55ab4368fa28ef422893e25745216558088366c'}"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vlcc_glory_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Loading from API: 0it [00:00, ?it/s]\n"
     ]
    }
   ],
   "source": [
    "vlcc_EG_query = v.CargoMovements().search(\n",
    "    filter_activity=\"loading_state\",\n",
    "    filter_time_min=one_month_ago,\n",
    "    filter_time_max=now,\n",
    "    filter_vessels=vlcc_glory_dict['DELTA GLORY'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead 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>vessel_name</th>\n",
       "      <th>vessel_class</th>\n",
       "      <th>product_group</th>\n",
       "      <th>product_grade</th>\n",
       "      <th>quantity</th>\n",
       "      <th>status</th>\n",
       "      <th>loading_port</th>\n",
       "      <th>loading_finish</th>\n",
       "      <th>unloading_port</th>\n",
       "      <th>unloading_finish</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [vessel_name, vessel_class, product_group, product_grade, quantity, status, loading_port, loading_finish, unloading_port, unloading_finish]\n",
       "Index: []"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vlcc_EG_query.to_df(columns=required_columns).rename(relabel,axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise\n",
    "\n",
    "Make a DataFrame with all vessels. Aggregate this by vessel class, and plot a pie chart of the number of vessels in each class."
   ]
  },
  {
   "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.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}