aggregateData.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "summary = pd.read_csv('compiledData/compiledSummaryData.csv', index_col=False)\n",
    "tonnage = pd.read_csv('compiledData/compiledTonnageData.csv', index_col=False)\n",
    "dimensions = pd.read_csv('compiledData/compiledDimensionsData.csv', index_col=False)\n",
    "particulars = pd.read_csv('compiledData/compiledParticularsData.csv', index_col=False)"
   ]
  },
  {
   "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>VesselId</th>\n",
       "      <th>DimensionTypeLookupId</th>\n",
       "      <th>DimensionTypeLookupName</th>\n",
       "      <th>BreadthInFeet</th>\n",
       "      <th>DepthInFeet</th>\n",
       "      <th>LengthInFeet</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>Length Between Perpendiculars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>541.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>Pre-1990/Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>176.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>Hull(Overall) Simplified</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>576.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>Hull(Overall) Formal</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>576.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>Length</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>176.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>Design Draft</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    VesselId  DimensionTypeLookupId        DimensionTypeLookupName  \\\n",
       "11         3                      8  Length Between Perpendiculars   \n",
       "12         3                      7                 Pre-1990/Other   \n",
       "13         3                      4       Hull(Overall) Simplified   \n",
       "14         3                     10           Hull(Overall) Formal   \n",
       "15         3                     11                         Length   \n",
       "16         3                      9                   Design Draft   \n",
       "\n",
       "    BreadthInFeet  DepthInFeet  LengthInFeet  \n",
       "11            NaN          NaN         541.3  \n",
       "12            NaN          NaN         176.7  \n",
       "13            NaN          NaN         576.0  \n",
       "14            NaN          NaN         576.0  \n",
       "15            NaN          NaN         176.7  \n",
       "16            NaN          NaN          28.1  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dimensions[dimensions['VesselId'] == 3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "dimensions = dimensions.astype({'VesselId': 'int64', 'BreadthInFeet': 'float64', 'DepthInFeet': 'float64','LengthInFeet': 'float64' })\n",
    "\n",
    "lengths = dimensions[dimensions['DimensionTypeLookupId'] == 11]\n",
    "lengths = lengths[['VesselId', 'LengthInFeet']]\n",
    "lengths = lengths.drop_duplicates(['VesselId'])\n",
    "lengths = lengths.set_index('VesselId')\n",
    "\n",
    "widths = dimensions[dimensions['DimensionTypeLookupId'] == 2]\n",
    "widths = widths[['VesselId', 'BreadthInFeet']]\n",
    "widths = widths.drop_duplicates(['VesselId'])\n",
    "widths = widths.set_index('VesselId')\n",
    "\n",
    "depths = dimensions[dimensions['DimensionTypeLookupId'] == 2]\n",
    "depths = depths[['VesselId', 'DepthInFeet']]\n",
    "depths = depths.drop_duplicates(['VesselId'])\n",
    "depths = depths.set_index('VesselId')\n",
    "\n",
    "dims = lengths.join(widths)\n",
    "dims = dims.join(depths)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>LengthInFeet</th>\n",
       "      <th>BreadthInFeet</th>\n",
       "      <th>DepthInFeet</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VesselId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>902.0</td>\n",
       "      <td>105.6</td>\n",
       "      <td>69.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>485.0</td>\n",
       "      <td>79.4</td>\n",
       "      <td>41.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>176.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>578.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>948.1</td>\n",
       "      <td>147.6</td>\n",
       "      <td>78.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705569</th>\n",
       "      <td>19.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705570</th>\n",
       "      <td>22.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705571</th>\n",
       "      <td>19.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705572</th>\n",
       "      <td>19.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705574</th>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1405690 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          LengthInFeet  BreadthInFeet  DepthInFeet\n",
       "VesselId                                          \n",
       "1                902.0          105.6         69.6\n",
       "2                485.0           79.4         41.9\n",
       "3                176.7            NaN          NaN\n",
       "4                578.8            NaN          NaN\n",
       "5                948.1          147.6         78.7\n",
       "...                ...            ...          ...\n",
       "1705569           19.0            NaN          NaN\n",
       "1705570           22.0            NaN          NaN\n",
       "1705571           19.0            NaN          NaN\n",
       "1705572           19.0            NaN          NaN\n",
       "1705574           20.0            NaN          NaN\n",
       "\n",
       "[1405690 rows x 3 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dims"
   ]
  },
  {
   "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>VesselId</th>\n",
       "      <th>MeasureOfWeight</th>\n",
       "      <th>TonnageTypeLookupId</th>\n",
       "      <th>TonnageTypeLookupName</th>\n",
       "      <th>TonnageUnitOfMeasurementFilterLookupId</th>\n",
       "      <th>UnitOfMeasureLookupName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>59093</td>\n",
       "      <td>5</td>\n",
       "      <td>Dead Weight</td>\n",
       "      <td>120</td>\n",
       "      <td>Ton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>50350</td>\n",
       "      <td>1</td>\n",
       "      <td>Convention (Subpart B)</td>\n",
       "      <td>111</td>\n",
       "      <td>Short Ton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>28369</td>\n",
       "      <td>3</td>\n",
       "      <td>Regulatory (Subpart C or D)</td>\n",
       "      <td>64</td>\n",
       "      <td>Long Ton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>50350</td>\n",
       "      <td>1</td>\n",
       "      <td>Convention (Subpart B)</td>\n",
       "      <td>64</td>\n",
       "      <td>Long Ton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>28369</td>\n",
       "      <td>3</td>\n",
       "      <td>Regulatory (Subpart C or D)</td>\n",
       "      <td>111</td>\n",
       "      <td>Short Ton</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   VesselId  MeasureOfWeight  TonnageTypeLookupId  \\\n",
       "0         1            59093                    5   \n",
       "1         1            50350                    1   \n",
       "2         1            28369                    3   \n",
       "3         1            50350                    1   \n",
       "4         1            28369                    3   \n",
       "\n",
       "         TonnageTypeLookupName  TonnageUnitOfMeasurementFilterLookupId  \\\n",
       "0                  Dead Weight                                     120   \n",
       "1       Convention (Subpart B)                                     111   \n",
       "2  Regulatory (Subpart C or D)                                      64   \n",
       "3       Convention (Subpart B)                                      64   \n",
       "4  Regulatory (Subpart C or D)                                     111   \n",
       "\n",
       "  UnitOfMeasureLookupName  \n",
       "0                     Ton  \n",
       "1               Short Ton  \n",
       "2                Long Ton  \n",
       "3                Long Ton  \n",
       "4               Short Ton  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tonnage[tonnage['VesselId'] == 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "tonnage = tonnage.astype({'VesselId': 'int64', 'MeasureOfWeight': 'float64', 'TonnageTypeLookupId': 'int64','TonnageUnitOfMeasurementFilterLookupId': 'int64' })\n",
    "\n",
    "grt = tonnage[(tonnage['TonnageTypeLookupId'] == 3) & (tonnage['TonnageUnitOfMeasurementFilterLookupId'] == 111)]\n",
    "grt = grt[['VesselId', 'MeasureOfWeight']]\n",
    "grt = grt.drop_duplicates(['VesselId'])\n",
    "grt = grt.set_index('VesselId')\n",
    "grt = grt.rename(columns={'MeasureOfWeight': 'grt'})\n",
    "\n",
    "gt = tonnage[(tonnage['TonnageTypeLookupId'] == 1) & (tonnage['TonnageUnitOfMeasurementFilterLookupId'] == 111)]\n",
    "gt = gt[['VesselId', 'MeasureOfWeight']]\n",
    "gt = gt.drop_duplicates(['VesselId'])\n",
    "gt = gt.set_index('VesselId')\n",
    "gt = gt.rename(columns={'MeasureOfWeight': 'gt'})\n",
    "\n",
    "tons = grt.join(gt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>grt</th>\n",
       "      <th>gt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VesselId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>28369.0</td>\n",
       "      <td>50350.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11172.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>36993.0</td>\n",
       "      <td>36993.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>108701.0</td>\n",
       "      <td>108701.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1689380</th>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1693781</th>\n",
       "      <td>110448.0</td>\n",
       "      <td>319202.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1693840</th>\n",
       "      <td>36148.0</td>\n",
       "      <td>36148.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1699014</th>\n",
       "      <td>19311.0</td>\n",
       "      <td>19311.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1699231</th>\n",
       "      <td>20175.0</td>\n",
       "      <td>20175.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>130591 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               grt        gt\n",
       "VesselId                    \n",
       "1          28369.0   50350.0\n",
       "3          11172.0       NaN\n",
       "170            4.0       NaN\n",
       "188        36993.0   36993.0\n",
       "200       108701.0  108701.0\n",
       "...            ...       ...\n",
       "1689380        2.0       NaN\n",
       "1693781   110448.0  319202.0\n",
       "1693840    36148.0   36148.0\n",
       "1699014    19311.0   19311.0\n",
       "1699231    20175.0   20175.0\n",
       "\n",
       "[130591 rows x 2 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tons"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "particulars = particulars.astype({'VesselId': 'int64'})\n",
    "particulars = particulars[['VesselId', 'ServiceSubType', 'IdentificationTypeLookupName', 'CargoAuthorizationDescription']]\n",
    "particulars = particulars.set_index('VesselId')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ServiceSubType</th>\n",
       "      <th>IdentificationTypeLookupName</th>\n",
       "      <th>CargoAuthorizationDescription</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VesselId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>General Cargo/Container</td>\n",
       "      <td>IMO Number</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Oil &amp;amp; Chemical Tank Ship</td>\n",
       "      <td>IMO Number</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>General</td>\n",
       "      <td>IMO Number</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>General</td>\n",
       "      <td>IMO Number</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>General 2</td>\n",
       "      <td>IMO Number</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705571</th>\n",
       "      <td>General</td>\n",
       "      <td>State Number</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705572</th>\n",
       "      <td>Runabout</td>\n",
       "      <td>State Number</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705573</th>\n",
       "      <td>General</td>\n",
       "      <td>IMO Number</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705574</th>\n",
       "      <td>General</td>\n",
       "      <td>State Number</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705575</th>\n",
       "      <td>General</td>\n",
       "      <td>State Number</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1663013 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        ServiceSubType IdentificationTypeLookupName  \\\n",
       "VesselId                                                              \n",
       "1              General Cargo/Container                   IMO Number   \n",
       "2         Oil &amp; Chemical Tank Ship                   IMO Number   \n",
       "3                              General                   IMO Number   \n",
       "4                              General                   IMO Number   \n",
       "5                            General 2                   IMO Number   \n",
       "...                                ...                          ...   \n",
       "1705571                        General                 State Number   \n",
       "1705572                       Runabout                 State Number   \n",
       "1705573                        General                   IMO Number   \n",
       "1705574                        General                 State Number   \n",
       "1705575                        General                 State Number   \n",
       "\n",
       "         CargoAuthorizationDescription  \n",
       "VesselId                                \n",
       "1                                  NaN  \n",
       "2                                  NaN  \n",
       "3                                  NaN  \n",
       "4                                  NaN  \n",
       "5                                  NaN  \n",
       "...                                ...  \n",
       "1705571                            NaN  \n",
       "1705572                            NaN  \n",
       "1705573                            NaN  \n",
       "1705574                            NaN  \n",
       "1705575                            NaN  \n",
       "\n",
       "[1663013 rows x 3 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "particulars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "summary = summary[['VesselId', 'VesselName','VesselCallSign', 'ServiceType', 'ConstructionCompletedYear', 'StatusLookupName', 'Identification', 'ManufacturerHullNumber', 'CountryLookupName', 'HIN', 'OutOfServiceDate']]\n",
    "summary = summary.set_index('VesselId')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead 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>VesselName</th>\n",
       "      <th>VesselCallSign</th>\n",
       "      <th>ServiceType</th>\n",
       "      <th>ConstructionCompletedYear</th>\n",
       "      <th>StatusLookupName</th>\n",
       "      <th>Identification</th>\n",
       "      <th>ManufacturerHullNumber</th>\n",
       "      <th>CountryLookupName</th>\n",
       "      <th>HIN</th>\n",
       "      <th>OutOfServiceDate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VesselId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MAERSK DELMONT</td>\n",
       "      <td>MTFH5</td>\n",
       "      <td>Freight Ship</td>\n",
       "      <td>1995</td>\n",
       "      <td>Active</td>\n",
       "      <td>9103037</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNITED KINGDOM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CHEMSTAR VENUS</td>\n",
       "      <td>3FEX9</td>\n",
       "      <td>Tank Ship</td>\n",
       "      <td>1999</td>\n",
       "      <td>Active</td>\n",
       "      <td>9185841</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PANAMA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NOCC PAMPLONA</td>\n",
       "      <td>LALG5</td>\n",
       "      <td>Freight Ship</td>\n",
       "      <td>1999</td>\n",
       "      <td>Active</td>\n",
       "      <td>9177038</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NORWAY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SAXONIA</td>\n",
       "      <td>ELWR3</td>\n",
       "      <td>Freight Ship</td>\n",
       "      <td>1998</td>\n",
       "      <td>Active</td>\n",
       "      <td>9182631</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LIBERIA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>GINGO</td>\n",
       "      <td>ZCEH2</td>\n",
       "      <td>Freight Ship</td>\n",
       "      <td>1999</td>\n",
       "      <td>Active</td>\n",
       "      <td>9182710</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CYPRUS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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>1705571</th>\n",
       "      <td>TX4075CC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Recreational</td>\n",
       "      <td>0</td>\n",
       "      <td>Active</td>\n",
       "      <td>TX4075CC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNITED STATES</td>\n",
       "      <td>JNT20613F314</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705572</th>\n",
       "      <td>VA7276ZZ</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Recreational</td>\n",
       "      <td>1989</td>\n",
       "      <td>Active</td>\n",
       "      <td>VA7276ZZ</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNITED STATES</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705573</th>\n",
       "      <td>BBG GUILIN</td>\n",
       "      <td>D5ZL4</td>\n",
       "      <td>Freight Ship</td>\n",
       "      <td>0</td>\n",
       "      <td>Active</td>\n",
       "      <td>9922249</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LIBERIA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705574</th>\n",
       "      <td>TX3086DV</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Recreational</td>\n",
       "      <td>0</td>\n",
       "      <td>Active</td>\n",
       "      <td>TX3086DV</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNITED STATES</td>\n",
       "      <td>PLC00569K788</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705575</th>\n",
       "      <td>LA 2512 FP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Public Vessel, Unclassified</td>\n",
       "      <td>0</td>\n",
       "      <td>Active</td>\n",
       "      <td>LA2512FP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNITED STATES</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1642692 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              VesselName VesselCallSign                  ServiceType  \\\n",
       "VesselId                                                               \n",
       "1         MAERSK DELMONT          MTFH5                 Freight Ship   \n",
       "2         CHEMSTAR VENUS          3FEX9                    Tank Ship   \n",
       "3          NOCC PAMPLONA          LALG5                 Freight Ship   \n",
       "4                SAXONIA          ELWR3                 Freight Ship   \n",
       "5                  GINGO          ZCEH2                 Freight Ship   \n",
       "...                  ...            ...                          ...   \n",
       "1705571         TX4075CC            NaN                 Recreational   \n",
       "1705572         VA7276ZZ            NaN                 Recreational   \n",
       "1705573       BBG GUILIN          D5ZL4                 Freight Ship   \n",
       "1705574         TX3086DV            NaN                 Recreational   \n",
       "1705575       LA 2512 FP            NaN  Public Vessel, Unclassified   \n",
       "\n",
       "          ConstructionCompletedYear StatusLookupName Identification  \\\n",
       "VesselId                                                              \n",
       "1                              1995           Active        9103037   \n",
       "2                              1999           Active        9185841   \n",
       "3                              1999           Active        9177038   \n",
       "4                              1998           Active        9182631   \n",
       "5                              1999           Active        9182710   \n",
       "...                             ...              ...            ...   \n",
       "1705571                           0           Active       TX4075CC   \n",
       "1705572                        1989           Active       VA7276ZZ   \n",
       "1705573                           0           Active        9922249   \n",
       "1705574                           0           Active       TX3086DV   \n",
       "1705575                           0           Active       LA2512FP   \n",
       "\n",
       "         ManufacturerHullNumber CountryLookupName           HIN  \\\n",
       "VesselId                                                          \n",
       "1                           NaN    UNITED KINGDOM           NaN   \n",
       "2                           NaN            PANAMA           NaN   \n",
       "3                           NaN            NORWAY           NaN   \n",
       "4                           NaN           LIBERIA           NaN   \n",
       "5                           NaN            CYPRUS           NaN   \n",
       "...                         ...               ...           ...   \n",
       "1705571                     NaN     UNITED STATES  JNT20613F314   \n",
       "1705572                     NaN     UNITED STATES           NaN   \n",
       "1705573                     NaN           LIBERIA           NaN   \n",
       "1705574                     NaN     UNITED STATES  PLC00569K788   \n",
       "1705575                     NaN     UNITED STATES           NaN   \n",
       "\n",
       "         OutOfServiceDate  \n",
       "VesselId                   \n",
       "1                     NaN  \n",
       "2                     NaN  \n",
       "3                     NaN  \n",
       "4                     NaN  \n",
       "5                     NaN  \n",
       "...                   ...  \n",
       "1705571               NaN  \n",
       "1705572               NaN  \n",
       "1705573               NaN  \n",
       "1705574               NaN  \n",
       "1705575               NaN  \n",
       "\n",
       "[1642692 rows x 10 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "fullSummary = summary.join(particulars)\n",
    "fullSummary = fullSummary.join(tons)\n",
    "fullSummary = fullSummary.join(dims)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>VesselName</th>\n",
       "      <th>VesselCallSign</th>\n",
       "      <th>ServiceType</th>\n",
       "      <th>ConstructionCompletedYear</th>\n",
       "      <th>StatusLookupName</th>\n",
       "      <th>Identification</th>\n",
       "      <th>ManufacturerHullNumber</th>\n",
       "      <th>CountryLookupName</th>\n",
       "      <th>HIN</th>\n",
       "      <th>OutOfServiceDate</th>\n",
       "      <th>ServiceSubType</th>\n",
       "      <th>IdentificationTypeLookupName</th>\n",
       "      <th>CargoAuthorizationDescription</th>\n",
       "      <th>grt</th>\n",
       "      <th>gt</th>\n",
       "      <th>LengthInFeet</th>\n",
       "      <th>BreadthInFeet</th>\n",
       "      <th>DepthInFeet</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VesselId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MAERSK DELMONT</td>\n",
       "      <td>MTFH5</td>\n",
       "      <td>Freight Ship</td>\n",
       "      <td>1995</td>\n",
       "      <td>Active</td>\n",
       "      <td>9103037</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNITED KINGDOM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>General Cargo/Container</td>\n",
       "      <td>IMO Number</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28369.0</td>\n",
       "      <td>50350.0</td>\n",
       "      <td>902.0</td>\n",
       "      <td>105.6</td>\n",
       "      <td>69.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CHEMSTAR VENUS</td>\n",
       "      <td>3FEX9</td>\n",
       "      <td>Tank Ship</td>\n",
       "      <td>1999</td>\n",
       "      <td>Active</td>\n",
       "      <td>9185841</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PANAMA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Oil &amp;amp; Chemical Tank Ship</td>\n",
       "      <td>IMO Number</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>485.0</td>\n",
       "      <td>79.4</td>\n",
       "      <td>41.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NOCC PAMPLONA</td>\n",
       "      <td>LALG5</td>\n",
       "      <td>Freight Ship</td>\n",
       "      <td>1999</td>\n",
       "      <td>Active</td>\n",
       "      <td>9177038</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NORWAY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>General</td>\n",
       "      <td>IMO Number</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11172.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>176.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SAXONIA</td>\n",
       "      <td>ELWR3</td>\n",
       "      <td>Freight Ship</td>\n",
       "      <td>1998</td>\n",
       "      <td>Active</td>\n",
       "      <td>9182631</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LIBERIA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>General</td>\n",
       "      <td>IMO Number</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>578.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>GINGO</td>\n",
       "      <td>ZCEH2</td>\n",
       "      <td>Freight Ship</td>\n",
       "      <td>1999</td>\n",
       "      <td>Active</td>\n",
       "      <td>9182710</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CYPRUS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>General 2</td>\n",
       "      <td>IMO Number</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>948.1</td>\n",
       "      <td>147.6</td>\n",
       "      <td>78.7</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705571</th>\n",
       "      <td>TX4075CC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Recreational</td>\n",
       "      <td>0</td>\n",
       "      <td>Active</td>\n",
       "      <td>TX4075CC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNITED STATES</td>\n",
       "      <td>JNT20613F314</td>\n",
       "      <td>NaN</td>\n",
       "      <td>General</td>\n",
       "      <td>State Number</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705572</th>\n",
       "      <td>VA7276ZZ</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Recreational</td>\n",
       "      <td>1989</td>\n",
       "      <td>Active</td>\n",
       "      <td>VA7276ZZ</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNITED STATES</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Runabout</td>\n",
       "      <td>State Number</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705573</th>\n",
       "      <td>BBG GUILIN</td>\n",
       "      <td>D5ZL4</td>\n",
       "      <td>Freight Ship</td>\n",
       "      <td>0</td>\n",
       "      <td>Active</td>\n",
       "      <td>9922249</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LIBERIA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>General</td>\n",
       "      <td>IMO Number</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705574</th>\n",
       "      <td>TX3086DV</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Recreational</td>\n",
       "      <td>0</td>\n",
       "      <td>Active</td>\n",
       "      <td>TX3086DV</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNITED STATES</td>\n",
       "      <td>PLC00569K788</td>\n",
       "      <td>NaN</td>\n",
       "      <td>General</td>\n",
       "      <td>State Number</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705575</th>\n",
       "      <td>LA 2512 FP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Public Vessel, Unclassified</td>\n",
       "      <td>0</td>\n",
       "      <td>Active</td>\n",
       "      <td>LA2512FP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNITED STATES</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>General</td>\n",
       "      <td>State Number</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1663013 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              VesselName VesselCallSign                  ServiceType  \\\n",
       "VesselId                                                               \n",
       "1         MAERSK DELMONT          MTFH5                 Freight Ship   \n",
       "2         CHEMSTAR VENUS          3FEX9                    Tank Ship   \n",
       "3          NOCC PAMPLONA          LALG5                 Freight Ship   \n",
       "4                SAXONIA          ELWR3                 Freight Ship   \n",
       "5                  GINGO          ZCEH2                 Freight Ship   \n",
       "...                  ...            ...                          ...   \n",
       "1705571         TX4075CC            NaN                 Recreational   \n",
       "1705572         VA7276ZZ            NaN                 Recreational   \n",
       "1705573       BBG GUILIN          D5ZL4                 Freight Ship   \n",
       "1705574         TX3086DV            NaN                 Recreational   \n",
       "1705575       LA 2512 FP            NaN  Public Vessel, Unclassified   \n",
       "\n",
       "          ConstructionCompletedYear StatusLookupName Identification  \\\n",
       "VesselId                                                              \n",
       "1                              1995           Active        9103037   \n",
       "2                              1999           Active        9185841   \n",
       "3                              1999           Active        9177038   \n",
       "4                              1998           Active        9182631   \n",
       "5                              1999           Active        9182710   \n",
       "...                             ...              ...            ...   \n",
       "1705571                           0           Active       TX4075CC   \n",
       "1705572                        1989           Active       VA7276ZZ   \n",
       "1705573                           0           Active        9922249   \n",
       "1705574                           0           Active       TX3086DV   \n",
       "1705575                           0           Active       LA2512FP   \n",
       "\n",
       "         ManufacturerHullNumber CountryLookupName           HIN  \\\n",
       "VesselId                                                          \n",
       "1                           NaN    UNITED KINGDOM           NaN   \n",
       "2                           NaN            PANAMA           NaN   \n",
       "3                           NaN            NORWAY           NaN   \n",
       "4                           NaN           LIBERIA           NaN   \n",
       "5                           NaN            CYPRUS           NaN   \n",
       "...                         ...               ...           ...   \n",
       "1705571                     NaN     UNITED STATES  JNT20613F314   \n",
       "1705572                     NaN     UNITED STATES           NaN   \n",
       "1705573                     NaN           LIBERIA           NaN   \n",
       "1705574                     NaN     UNITED STATES  PLC00569K788   \n",
       "1705575                     NaN     UNITED STATES           NaN   \n",
       "\n",
       "         OutOfServiceDate                ServiceSubType  \\\n",
       "VesselId                                                  \n",
       "1                     NaN       General Cargo/Container   \n",
       "2                     NaN  Oil &amp; Chemical Tank Ship   \n",
       "3                     NaN                       General   \n",
       "4                     NaN                       General   \n",
       "5                     NaN                     General 2   \n",
       "...                   ...                           ...   \n",
       "1705571               NaN                       General   \n",
       "1705572               NaN                      Runabout   \n",
       "1705573               NaN                       General   \n",
       "1705574               NaN                       General   \n",
       "1705575               NaN                       General   \n",
       "\n",
       "         IdentificationTypeLookupName CargoAuthorizationDescription      grt  \\\n",
       "VesselId                                                                       \n",
       "1                          IMO Number                           NaN  28369.0   \n",
       "2                          IMO Number                           NaN      NaN   \n",
       "3                          IMO Number                           NaN  11172.0   \n",
       "4                          IMO Number                           NaN      NaN   \n",
       "5                          IMO Number                           NaN      NaN   \n",
       "...                               ...                           ...      ...   \n",
       "1705571                  State Number                           NaN      NaN   \n",
       "1705572                  State Number                           NaN      NaN   \n",
       "1705573                    IMO Number                           NaN      NaN   \n",
       "1705574                  State Number                           NaN      NaN   \n",
       "1705575                  State Number                           NaN      NaN   \n",
       "\n",
       "               gt  LengthInFeet  BreadthInFeet  DepthInFeet  \n",
       "VesselId                                                     \n",
       "1         50350.0         902.0          105.6         69.6  \n",
       "2             NaN         485.0           79.4         41.9  \n",
       "3             NaN         176.7            NaN          NaN  \n",
       "4             NaN         578.8            NaN          NaN  \n",
       "5             NaN         948.1          147.6         78.7  \n",
       "...           ...           ...            ...          ...  \n",
       "1705571       NaN          19.0            NaN          NaN  \n",
       "1705572       NaN          19.0            NaN          NaN  \n",
       "1705573       NaN           NaN            NaN          NaN  \n",
       "1705574       NaN          20.0            NaN          NaN  \n",
       "1705575       NaN           NaN            NaN          NaN  \n",
       "\n",
       "[1663013 rows x 18 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fullSummary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "fullSummary = fullSummary[['VesselName', 'VesselCallSign', 'ServiceType', 'ServiceSubType', \n",
    "       'ConstructionCompletedYear',  'Identification', 'IdentificationTypeLookupName',\n",
    "       'grt', 'gt', 'LengthInFeet',\n",
    "       'BreadthInFeet', 'DepthInFeet', 'CountryLookupName', 'ManufacturerHullNumber', 'HIN', 'StatusLookupName', 'CargoAuthorizationDescription', 'OutOfServiceDate', ]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "fullSummary = fullSummary.rename(columns={'IdentificationTypeLookupName': 'IdentificationType', 'CountryLookupName': 'Country'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>VesselName</th>\n",
       "      <th>VesselCallSign</th>\n",
       "      <th>ServiceType</th>\n",
       "      <th>ServiceSubType</th>\n",
       "      <th>ConstructionCompletedYear</th>\n",
       "      <th>Identification</th>\n",
       "      <th>IdentificationType</th>\n",
       "      <th>grt</th>\n",
       "      <th>gt</th>\n",
       "      <th>LengthInFeet</th>\n",
       "      <th>BreadthInFeet</th>\n",
       "      <th>DepthInFeet</th>\n",
       "      <th>Country</th>\n",
       "      <th>ManufacturerHullNumber</th>\n",
       "      <th>HIN</th>\n",
       "      <th>StatusLookupName</th>\n",
       "      <th>CargoAuthorizationDescription</th>\n",
       "      <th>OutOfServiceDate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VesselId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MAERSK DELMONT</td>\n",
       "      <td>MTFH5</td>\n",
       "      <td>Freight Ship</td>\n",
       "      <td>General Cargo/Container</td>\n",
       "      <td>1995</td>\n",
       "      <td>9103037</td>\n",
       "      <td>IMO Number</td>\n",
       "      <td>28369.0</td>\n",
       "      <td>50350.0</td>\n",
       "      <td>902.0</td>\n",
       "      <td>105.6</td>\n",
       "      <td>69.6</td>\n",
       "      <td>UNITED KINGDOM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Active</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CHEMSTAR VENUS</td>\n",
       "      <td>3FEX9</td>\n",
       "      <td>Tank Ship</td>\n",
       "      <td>Oil &amp;amp; Chemical Tank Ship</td>\n",
       "      <td>1999</td>\n",
       "      <td>9185841</td>\n",
       "      <td>IMO Number</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>485.0</td>\n",
       "      <td>79.4</td>\n",
       "      <td>41.9</td>\n",
       "      <td>PANAMA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Active</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NOCC PAMPLONA</td>\n",
       "      <td>LALG5</td>\n",
       "      <td>Freight Ship</td>\n",
       "      <td>General</td>\n",
       "      <td>1999</td>\n",
       "      <td>9177038</td>\n",
       "      <td>IMO Number</td>\n",
       "      <td>11172.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>176.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NORWAY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Active</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SAXONIA</td>\n",
       "      <td>ELWR3</td>\n",
       "      <td>Freight Ship</td>\n",
       "      <td>General</td>\n",
       "      <td>1998</td>\n",
       "      <td>9182631</td>\n",
       "      <td>IMO Number</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>578.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LIBERIA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Active</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>GINGO</td>\n",
       "      <td>ZCEH2</td>\n",
       "      <td>Freight Ship</td>\n",
       "      <td>General 2</td>\n",
       "      <td>1999</td>\n",
       "      <td>9182710</td>\n",
       "      <td>IMO Number</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>948.1</td>\n",
       "      <td>147.6</td>\n",
       "      <td>78.7</td>\n",
       "      <td>CYPRUS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Active</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705571</th>\n",
       "      <td>TX4075CC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Recreational</td>\n",
       "      <td>General</td>\n",
       "      <td>0</td>\n",
       "      <td>TX4075CC</td>\n",
       "      <td>State Number</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNITED STATES</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JNT20613F314</td>\n",
       "      <td>Active</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705572</th>\n",
       "      <td>VA7276ZZ</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Recreational</td>\n",
       "      <td>Runabout</td>\n",
       "      <td>1989</td>\n",
       "      <td>VA7276ZZ</td>\n",
       "      <td>State Number</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNITED STATES</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Active</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705573</th>\n",
       "      <td>BBG GUILIN</td>\n",
       "      <td>D5ZL4</td>\n",
       "      <td>Freight Ship</td>\n",
       "      <td>General</td>\n",
       "      <td>0</td>\n",
       "      <td>9922249</td>\n",
       "      <td>IMO Number</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LIBERIA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Active</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705574</th>\n",
       "      <td>TX3086DV</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Recreational</td>\n",
       "      <td>General</td>\n",
       "      <td>0</td>\n",
       "      <td>TX3086DV</td>\n",
       "      <td>State Number</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNITED STATES</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PLC00569K788</td>\n",
       "      <td>Active</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1705575</th>\n",
       "      <td>LA 2512 FP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Public Vessel, Unclassified</td>\n",
       "      <td>General</td>\n",
       "      <td>0</td>\n",
       "      <td>LA2512FP</td>\n",
       "      <td>State Number</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNITED STATES</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Active</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1663013 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              VesselName VesselCallSign                  ServiceType  \\\n",
       "VesselId                                                               \n",
       "1         MAERSK DELMONT          MTFH5                 Freight Ship   \n",
       "2         CHEMSTAR VENUS          3FEX9                    Tank Ship   \n",
       "3          NOCC PAMPLONA          LALG5                 Freight Ship   \n",
       "4                SAXONIA          ELWR3                 Freight Ship   \n",
       "5                  GINGO          ZCEH2                 Freight Ship   \n",
       "...                  ...            ...                          ...   \n",
       "1705571         TX4075CC            NaN                 Recreational   \n",
       "1705572         VA7276ZZ            NaN                 Recreational   \n",
       "1705573       BBG GUILIN          D5ZL4                 Freight Ship   \n",
       "1705574         TX3086DV            NaN                 Recreational   \n",
       "1705575       LA 2512 FP            NaN  Public Vessel, Unclassified   \n",
       "\n",
       "                        ServiceSubType  ConstructionCompletedYear  \\\n",
       "VesselId                                                            \n",
       "1              General Cargo/Container                       1995   \n",
       "2         Oil &amp; Chemical Tank Ship                       1999   \n",
       "3                              General                       1999   \n",
       "4                              General                       1998   \n",
       "5                            General 2                       1999   \n",
       "...                                ...                        ...   \n",
       "1705571                        General                          0   \n",
       "1705572                       Runabout                       1989   \n",
       "1705573                        General                          0   \n",
       "1705574                        General                          0   \n",
       "1705575                        General                          0   \n",
       "\n",
       "         Identification IdentificationType      grt       gt  LengthInFeet  \\\n",
       "VesselId                                                                     \n",
       "1               9103037         IMO Number  28369.0  50350.0         902.0   \n",
       "2               9185841         IMO Number      NaN      NaN         485.0   \n",
       "3               9177038         IMO Number  11172.0      NaN         176.7   \n",
       "4               9182631         IMO Number      NaN      NaN         578.8   \n",
       "5               9182710         IMO Number      NaN      NaN         948.1   \n",
       "...                 ...                ...      ...      ...           ...   \n",
       "1705571        TX4075CC       State Number      NaN      NaN          19.0   \n",
       "1705572        VA7276ZZ       State Number      NaN      NaN          19.0   \n",
       "1705573         9922249         IMO Number      NaN      NaN           NaN   \n",
       "1705574        TX3086DV       State Number      NaN      NaN          20.0   \n",
       "1705575        LA2512FP       State Number      NaN      NaN           NaN   \n",
       "\n",
       "          BreadthInFeet  DepthInFeet         Country ManufacturerHullNumber  \\\n",
       "VesselId                                                                      \n",
       "1                 105.6         69.6  UNITED KINGDOM                    NaN   \n",
       "2                  79.4         41.9          PANAMA                    NaN   \n",
       "3                   NaN          NaN          NORWAY                    NaN   \n",
       "4                   NaN          NaN         LIBERIA                    NaN   \n",
       "5                 147.6         78.7          CYPRUS                    NaN   \n",
       "...                 ...          ...             ...                    ...   \n",
       "1705571             NaN          NaN   UNITED STATES                    NaN   \n",
       "1705572             NaN          NaN   UNITED STATES                    NaN   \n",
       "1705573             NaN          NaN         LIBERIA                    NaN   \n",
       "1705574             NaN          NaN   UNITED STATES                    NaN   \n",
       "1705575             NaN          NaN   UNITED STATES                    NaN   \n",
       "\n",
       "                   HIN StatusLookupName CargoAuthorizationDescription  \\\n",
       "VesselId                                                                \n",
       "1                  NaN           Active                           NaN   \n",
       "2                  NaN           Active                           NaN   \n",
       "3                  NaN           Active                           NaN   \n",
       "4                  NaN           Active                           NaN   \n",
       "5                  NaN           Active                           NaN   \n",
       "...                ...              ...                           ...   \n",
       "1705571   JNT20613F314           Active                           NaN   \n",
       "1705572            NaN           Active                           NaN   \n",
       "1705573            NaN           Active                           NaN   \n",
       "1705574   PLC00569K788           Active                           NaN   \n",
       "1705575            NaN           Active                           NaN   \n",
       "\n",
       "         OutOfServiceDate  \n",
       "VesselId                   \n",
       "1                     NaN  \n",
       "2                     NaN  \n",
       "3                     NaN  \n",
       "4                     NaN  \n",
       "5                     NaN  \n",
       "...                   ...  \n",
       "1705571               NaN  \n",
       "1705572               NaN  \n",
       "1705573               NaN  \n",
       "1705574               NaN  \n",
       "1705575               NaN  \n",
       "\n",
       "[1663013 rows x 18 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fullSummary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "fullSummary.to_csv('compiledData/fullSummary.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
  },
  "kernelspec": {
   "display_name": "Python 3.9.5 64-bit",
   "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.9.4"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}