BGB-voyages to DAS ship IDs.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# BGB-voyages to DAS ship IDs\n",
    "\n",
    "*Gerhard de Kok*\n",
    "\n",
    "This script enriches the data from the Bookkeeper-General Batavia dataset (BGB) with links to the data in the Dutch-Asiatic Shipping dataset (DAS).\n",
    "\n",
    "> #### BGB\n",
    ">The BGB is a dataset with information on almost 20,000 voyages of the Dutch East India Company (VOC), which includes information on cargoes carried and ship used. It **does not** contain a disambiguated list of ships, i.e. a single ship name may refer to multiple physical vessels.\n",
    "> #### DAS\n",
    "> DAS is a dataset containing information on more than 4,700 voyages of the VOC, but it is limited to intercontinental voyages between Europe and Asia. It **does** contain a disambiguated list of ships.\n",
    "\n",
    "## The problem\n",
    "BGB has as list of voyages, which have non-unique IDs. The IDs are not unique, since multiple ships may partake in a single voyage and each ship gets its own entry or row in the dataset. The unique IDs are found on the level of 'shipvoyage IDs', which are combinations of a voyage and a ship. BGB ships have IDs, but these are not disambiguated (i.e. a single ship name with a single ID may refer to multiple physical vessels).\n",
    "\n",
    "Some of the ships in BGB are also present in DAS, which only contains intercontinental voyages. If a voyage is present in both BGB and DAS, a connection is (usually) already made between the BGB ship ID and the DAS ship ID. For about 14,500 (mostly intra-Asiatic) voyages in BGB, such a connection is not present. However, many of the vessels used for these voyages are present with a separate ship ID in DAS. The challenge is to link BGB ship IDs to DAS ship IDs in such cases.\n",
    "\n",
    "## Automated solution\n",
    "The present script tries to resolve this issue in a rule-based manner. Basically, the rules are as follows:\n",
    "\n",
    "1. For each voyage in BGB, check if the ship already has an associated DAS ship ID from another voyage in the original BGB dataset (if so, check if it is correct and then use that.)\n",
    "\n",
    "2. If no link is already present, fuzzy match the BGB ship name with a list of ship names in DAS. Then check to see if the DAS ship was active in the same time period.\n",
    "\n",
    "> #### Definition for 'same time period'\n",
    ">The active timerange of a DAS ship is defined as: (first mention of ship in DAS) until (last mention in DAS + 20 years).\n",
    "> If the BGB voyage took place inside that timerange (or rather: if it was booked in the same timerange), make the match between the BGB shipvoyage (a unique ID of a ship/voyage-combination) and the DAS ship ID.\n",
    "\n",
    "## Output\n",
    "The script produces an Excel-file with the following sheets:\n",
    "1. Matches: the matches between BGB shipvoyage IDs and DAS ship IDs made by the script.\n",
    "2. Ambiguous: cases were multiple DAS ship IDs matched the rules for a single BGB shipvoyage ID.\n",
    "3. No match: cases where a match could not be made for a BGB shipvoyage ID."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import necessary modules\n",
    "import pandas as pd\n",
    "import difflib\n",
    "\n",
    "def within_range(checknumber, range_start, range_end):\n",
    "    \"\"\"\" \n",
    "    Function to check if a BGB booking year falls within the timespan\n",
    "    in DAS referred to in the introduction to this script\n",
    "    \n",
    "    \"\"\"\n",
    "    if (checknumber >= range_start) & (checknumber <= (range_end + 20)):\n",
    "        return True\n",
    "    else:\n",
    "        return False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First, load the entire BGB database and the DAS database (Excel format)\n",
    "bgb = pd.ExcelFile('bgb.xlsx')\n",
    "das = pd.ExcelFile('das.xlsx')\n",
    "\n",
    "# Parse the Excel sheets we will be using from BGB\n",
    "# For the bgb_relations dataframe, some operations are needed to convert IDs to integers\n",
    "bgb_ships = bgb.parse('bgb_ship')\n",
    "bgb_ships = bgb_ships.set_index('id')\n",
    "bgb_relations = bgb.parse('bgb_relVoyageShip')\n",
    "bgb_relations.dropna(subset=['voyId'], how='all', inplace=True)\n",
    "bgb_relations['voyId'] = pd.to_numeric(bgb_relations['voyId'], downcast='integer', errors='coerce')\n",
    "bgb_relations = bgb_relations.set_index('id')\n",
    "bgb_voyages = bgb.parse('bgb_voyage')\n",
    "bgb_voyages = bgb_voyages.set_index('voyId')\n",
    "\n",
    "# From DAS, we need the list of ship names ...\n",
    "# ... and the years between which these ships were employed by the VOC\n",
    "das_ships = das.parse('shipNameVariant')\n",
    "das_ships = das_ships.set_index('shipNameVariantID')\n",
    "das_voyages = das.parse('das_voyage')\n",
    "das_voyages = das_voyages.set_index('voyId')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>shipID</th>\n",
       "      <th>shipNameVariant</th>\n",
       "      <th>shipNameVariantRemark</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>shipNameVariantID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>DAS_snv0001</th>\n",
       "      <td>DAS_ship0001</td>\n",
       "      <td>'s Heer Arendskerke</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DAS_snv0002</th>\n",
       "      <td>DAS_ship0002</td>\n",
       "      <td>'s Lands Welvaren</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DAS_snv0003</th>\n",
       "      <td>DAS_ship0003</td>\n",
       "      <td>'s-Graveland</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DAS_snv0004</th>\n",
       "      <td>DAS_ship0004</td>\n",
       "      <td>'s-Graveland</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DAS_snv0005</th>\n",
       "      <td>DAS_ship0005</td>\n",
       "      <td>'s-Gravenhage</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>DAS_snv1893</th>\n",
       "      <td>DAS_ship1852</td>\n",
       "      <td>Schaapherder</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DAS_snv1894</th>\n",
       "      <td>DAS_ship1853</td>\n",
       "      <td>Senhor De Bonfim E Sancta Maria</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DAS_snv1895</th>\n",
       "      <td>DAS_ship1854</td>\n",
       "      <td>Toevalligheid</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DAS_snv1896</th>\n",
       "      <td>DAS_ship1855</td>\n",
       "      <td>Batavier</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DAS_snv1897</th>\n",
       "      <td>DAS_ship1856</td>\n",
       "      <td>Herstelder</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1897 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         shipID                  shipNameVariant  \\\n",
       "shipNameVariantID                                                  \n",
       "DAS_snv0001        DAS_ship0001              's Heer Arendskerke   \n",
       "DAS_snv0002        DAS_ship0002                's Lands Welvaren   \n",
       "DAS_snv0003        DAS_ship0003                     's-Graveland   \n",
       "DAS_snv0004        DAS_ship0004                     's-Graveland   \n",
       "DAS_snv0005        DAS_ship0005                    's-Gravenhage   \n",
       "...                         ...                              ...   \n",
       "DAS_snv1893        DAS_ship1852                     Schaapherder   \n",
       "DAS_snv1894        DAS_ship1853  Senhor De Bonfim E Sancta Maria   \n",
       "DAS_snv1895        DAS_ship1854                    Toevalligheid   \n",
       "DAS_snv1896        DAS_ship1855                         Batavier   \n",
       "DAS_snv1897        DAS_ship1856                       Herstelder   \n",
       "\n",
       "                  shipNameVariantRemark  \n",
       "shipNameVariantID                        \n",
       "DAS_snv0001                         NaN  \n",
       "DAS_snv0002                         NaN  \n",
       "DAS_snv0003                         NaN  \n",
       "DAS_snv0004                         NaN  \n",
       "DAS_snv0005                         NaN  \n",
       "...                                 ...  \n",
       "DAS_snv1893                         NaN  \n",
       "DAS_snv1894                         NaN  \n",
       "DAS_snv1895                         NaN  \n",
       "DAS_snv1896                         NaN  \n",
       "DAS_snv1897                         NaN  \n",
       "\n",
       "[1897 rows x 3 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "das_ships"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now I want a list of individual ships from DAS and the years between which they were employed\n",
    "# Converting the date to datetime is not possible, since many voyages took place before 1677 (out of bounds)\n",
    "# This means we lose vectorization advantages anyway, so I'll generate a dataframe using a Python loop \n",
    "\n",
    "# Create an empty list to hold the data on ships and dates\n",
    "das_ship_dates = []\n",
    "\n",
    "# Populate the list with data from DAS\n",
    "for voyage in das_voyages.index:\n",
    "    current_ship_id = das_voyages.loc[voyage, 'shipID']\n",
    "    current_ship_name_id = das_voyages.loc[voyage, 'shipName']\n",
    "    current_ship_departure = das_voyages.loc[voyage, 'voyDepartureEDTF']\n",
    "    current_ship_arrival = das_voyages.loc[voyage, 'voyArrivalDateEDTF']\n",
    "    current_ship_name = das_ships.loc[current_ship_name_id, 'shipNameVariant']\n",
    "    \n",
    "    # Convert dates to 4 digits (datetime not possible w/o workarounds)\n",
    "    current_ship_departure = str(current_ship_departure)\n",
    "    current_ship_departure = current_ship_departure[:4]\n",
    "    current_ship_arrival = str(current_ship_arrival)\n",
    "    current_ship_arrival = current_ship_arrival[:4]\n",
    "\n",
    "    # Construct a list with data on this voyage\n",
    "    this_voyage = (voyage, current_ship_name, current_ship_id, current_ship_name_id, current_ship_departure, current_ship_arrival)\n",
    "    \n",
    "    # Append that list to the aforementioned list (of lists)\n",
    "    das_ship_dates.append(this_voyage)\n",
    "    \n",
    "# Create a Pandas dataframe from the list of lists\n",
    "fulldata = pd.DataFrame.from_records(das_ship_dates, columns=['DasID', 'Shipname', 'DasShipID', 'DasShipNameVariant', 'Startyear', 'Endyear'])\n",
    "fulldata = fulldata.set_index('DasID')\n",
    "\n",
    "# Convert the yearcolumns to numeric values (they include some messed up data)\n",
    "fulldata['Startyear'] = pd.to_numeric(fulldata['Startyear'], errors='coerce')\n",
    "fulldata['Endyear'] = pd.to_numeric(fulldata['Endyear'], errors='coerce')\n",
    "\n",
    "# Now we can drop NaNs (error coercion made these NaN)\n",
    "# And subsequently convert from float to int (due to messed up data, to_numeric couldn't do this)\n",
    "fulldata['Startyear'] = fulldata['Startyear'].astype(pd.Int32Dtype())\n",
    "fulldata['Endyear'] = fulldata['Endyear'].astype(pd.Int32Dtype())\n"
   ]
  },
  {
   "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>Shipname</th>\n",
       "      <th>DasShipID</th>\n",
       "      <th>Startyear</th>\n",
       "      <th>Endyear</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DasShipNameVariant</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>DAS_snv0001</th>\n",
       "      <td>'s Heer Arendskerke</td>\n",
       "      <td>DAS_ship0001</td>\n",
       "      <td>1725</td>\n",
       "      <td>1742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DAS_snv0002</th>\n",
       "      <td>'s Lands Welvaren</td>\n",
       "      <td>DAS_ship0002</td>\n",
       "      <td>1763</td>\n",
       "      <td>1773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DAS_snv0003</th>\n",
       "      <td>'s-Graveland</td>\n",
       "      <td>DAS_ship0003</td>\n",
       "      <td>1659</td>\n",
       "      <td>1660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DAS_snv0004</th>\n",
       "      <td>'s-Graveland</td>\n",
       "      <td>DAS_ship0004</td>\n",
       "      <td>1723</td>\n",
       "      <td>1726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DAS_snv0005</th>\n",
       "      <td>'s-Gravenhage</td>\n",
       "      <td>DAS_ship0005</td>\n",
       "      <td>1628</td>\n",
       "      <td>1635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DAS_snv1893</th>\n",
       "      <td>Schaapherder</td>\n",
       "      <td>DAS_ship1852</td>\n",
       "      <td>1690</td>\n",
       "      <td>1693</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DAS_snv1894</th>\n",
       "      <td>Senhor De Bonfim E Sancta Maria</td>\n",
       "      <td>DAS_ship1853</td>\n",
       "      <td>1782</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DAS_snv1895</th>\n",
       "      <td>Toevalligheid</td>\n",
       "      <td>DAS_ship1854</td>\n",
       "      <td>1745</td>\n",
       "      <td>1745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DAS_snv1896</th>\n",
       "      <td>Batavier</td>\n",
       "      <td>DAS_ship1855</td>\n",
       "      <td>1736</td>\n",
       "      <td>1758</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DAS_snv1897</th>\n",
       "      <td>Herstelder</td>\n",
       "      <td>DAS_ship1856</td>\n",
       "      <td>1772</td>\n",
       "      <td>1781</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1892 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           Shipname     DasShipID  Startyear  \\\n",
       "DasShipNameVariant                                                             \n",
       "DAS_snv0001                     's Heer Arendskerke  DAS_ship0001       1725   \n",
       "DAS_snv0002                       's Lands Welvaren  DAS_ship0002       1763   \n",
       "DAS_snv0003                            's-Graveland  DAS_ship0003       1659   \n",
       "DAS_snv0004                            's-Graveland  DAS_ship0004       1723   \n",
       "DAS_snv0005                           's-Gravenhage  DAS_ship0005       1628   \n",
       "...                                             ...           ...        ...   \n",
       "DAS_snv1893                            Schaapherder  DAS_ship1852       1690   \n",
       "DAS_snv1894         Senhor De Bonfim E Sancta Maria  DAS_ship1853       1782   \n",
       "DAS_snv1895                           Toevalligheid  DAS_ship1854       1745   \n",
       "DAS_snv1896                                Batavier  DAS_ship1855       1736   \n",
       "DAS_snv1897                              Herstelder  DAS_ship1856       1772   \n",
       "\n",
       "                    Endyear  \n",
       "DasShipNameVariant           \n",
       "DAS_snv0001            1742  \n",
       "DAS_snv0002            1773  \n",
       "DAS_snv0003            1660  \n",
       "DAS_snv0004            1726  \n",
       "DAS_snv0005            1635  \n",
       "...                     ...  \n",
       "DAS_snv1893            1693  \n",
       "DAS_snv1894            <NA>  \n",
       "DAS_snv1895            1745  \n",
       "DAS_snv1896            1758  \n",
       "DAS_snv1897            1781  \n",
       "\n",
       "[1892 rows x 4 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We now have all the data to make a dataframe with: DAS IDs, shipnames, first year the ship was active,\n",
    "# and last year the ship was active (on intercontinental voyages)\n",
    "summary = fulldata.groupby(['DasShipNameVariant']).agg({'Shipname':'last', 'DasShipID':'last', 'Startyear':'min', 'Endyear':'max'})\n",
    "summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generate a list with unique ship names in DAS for fuzzy matching later on\n",
    "dasshiplist = list(summary['Shipname'].unique())\n",
    "\n",
    "# Add all bgb_relVoyageShip ids to a list \n",
    "checklist = list(bgb_relations.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Loop over the list with BGB voyages IDs\n",
    "# Gather the necessary information to apply the rules\n",
    "\n",
    "# Define an empty list to hold the results\n",
    "voyagedata = []\n",
    "ambiguous = []\n",
    "notmatched = []\n",
    "\n",
    "# Check each voyage/ship-relation in BGB\n",
    "for bgb_shipvoyageid in checklist:\n",
    "    \n",
    "    bgb_shipvoyageid = int(bgb_shipvoyageid)    \n",
    "\n",
    "    # Get the BGB Voyage ID  \n",
    "    bgb_voyageid = int(bgb_relations.loc[bgb_shipvoyageid, ['voyId']])\n",
    "\n",
    "    # Get some basic information from BGB on the ships used in this ship/voyage combination\n",
    "    bgb_shipid = bgb_relations.loc[bgb_shipvoyageid, 'shipId']\n",
    "\n",
    "    # For some BGB ship IDs, there is no corresponding ship name in the BGB dataset (skip those)\n",
    "    try:\n",
    "        bgb_name = bgb_ships.loc[bgb_shipid, 'naam']\n",
    "    except:\n",
    "        continue\n",
    "\n",
    "    # Get the BGB booking year (With try/except since some BGB voyage IDs are not in the BGB voyages table)\n",
    "    try:\n",
    "        bgb_bookingyear = int(bgb_voyages.loc[bgb_voyageid, 'voyBookingYear'])\n",
    "    except:\n",
    "        bgb_bookingyear = 0\n",
    "\n",
    "    # APPLICATION OF THE RULES (see introduction to this script)       \n",
    "    # RULE 1: CHECK FOR EXISTING BGB-DAS MATCH FOR THIS SHIP \n",
    "    \n",
    "    existing_link = bgb_relations.loc[bgb_shipvoyageid, 'DAS_voyage']\n",
    "    if not pd.isna(existing_link):\n",
    "        das_shipnamevariantid = das_voyages.loc[int(existing_link), 'shipName']\n",
    "        das_shipnameid = das_voyages.loc[int(existing_link), 'shipID']\n",
    "        das_shipnamevariant = summary.loc[das_shipnamevariantid, 'Shipname']\n",
    "        \n",
    "        # Get voyage dates from DAS\n",
    "        startdate_das = das_voyages.loc[int(existing_link), 'voyDepartureEDTF']\n",
    "        try:\n",
    "            startdate_das = int(str(startdate_das[0:4]))\n",
    "        except:\n",
    "            startdate_das = 0\n",
    "        \n",
    "        enddate_das = das_voyages.loc[int(existing_link), 'voyArrivalDateEDTF']\n",
    "        try:\n",
    "            enddate_das = int(str(enddate_das[0:4]))\n",
    "        except:\n",
    "            enddate_das = 0\n",
    "         \n",
    "        # Some voyages do not contain information on the endyears, in that case: set it equal to startyear\n",
    "        if enddate_das == 0:\n",
    "            enddate_das = startdate_das\n",
    "        \n",
    "        # Quality check 1: is the shipname equal enough between BGB and DAS?\n",
    "        if difflib.get_close_matches(bgb_name, [das_shipnamevariant], n=3, cutoff=0.70):\n",
    "            \n",
    "            # Quality check 2: did the BGB voyage take place in the same time range as in DAS?\n",
    "            # This filters out just the Leiden\n",
    "            if not abs(bgb_bookingyear - startdate_das) > 20:\n",
    "            \n",
    "                # Verify the link\n",
    "                method = 1\n",
    "                this_voyage = (bgb_shipvoyageid, bgb_voyageid, bgb_shipid, int(existing_link), das_shipnameid, das_shipnamevariantid, bgb_name,das_shipnamevariant, bgb_bookingyear, startdate_das, enddate_das, method)\n",
    "                voyagedata.append(this_voyage)\n",
    "                continue\n",
    "\n",
    "    # RULE 2: NAME MATCHING FOR SHIPS WITHOUT (VALID) EXISTING LINK\n",
    "    \n",
    "    # Check for a closely matching ship name in DAS\n",
    "    checking = difflib.get_close_matches(bgb_name, dasshiplist, n=3, cutoff=0.85)\n",
    "\n",
    "    # Go to next voyage if there are no matches based on ship name\n",
    "    if not checking:\n",
    "        notmatched.append(bgb_shipvoyageid)\n",
    "        continue\n",
    "        \n",
    "    # There may be multiple DAS ships that fuzzy match with the BGB name, I use only the closest match\n",
    "    das_shipnamevariant = checking[0]  \n",
    "\n",
    "    # Bandaid solution: the ship 'Postiljon' is the only one for which we have no startyear and no endyear in DAS\n",
    "    # This messes up the code, so just skip this ship\n",
    "    if das_shipnamevariant == 'Postiljon':\n",
    "        notmatched.append(bgb_shipvoyageid)\n",
    "        continue\n",
    "    \n",
    "    # Get the start and end years for each of these similarly named DAS ships\n",
    "    startyears = list(summary.loc[summary['Shipname'] == das_shipnamevariant]['Startyear'])\n",
    "    endyears = list(summary.loc[summary['Shipname'] == das_shipnamevariant]['Endyear'])  \n",
    "    \n",
    "    # Some voyages do not contain information on the endyears, in that case: set it equal to startyear\n",
    "    for index, value in enumerate(endyears):\n",
    "        if pd.isnull(value):\n",
    "            endyears[index] = startyears[index]\n",
    "   \n",
    "    # Check if these years fall within the time range, and if so: make the match\n",
    "    templist = []\n",
    "    for startdate_das, enddate_das in zip(startyears, endyears):\n",
    "\n",
    "        if within_range(bgb_bookingyear, startdate_das, enddate_das):\n",
    "            \n",
    "            # Look up the ship ID and shipnamevariant ID from DAS\n",
    "            das_shipnamevariantid_match = summary.loc[(summary['Shipname'] == das_shipnamevariant) & (summary['Startyear'] == startdate_das)].index[0]\n",
    "            das_shipnameid_match = summary.loc[das_shipnamevariantid_match, 'DasShipID']\n",
    "\n",
    "            # Add voyages to a temporary holding list\n",
    "            method = 2\n",
    "            this_voyage = (bgb_shipvoyageid, bgb_voyageid, bgb_shipid, \"No link\", das_shipnameid_match, das_shipnamevariantid_match, bgb_name,das_shipnamevariant, bgb_bookingyear, startdate_das, enddate_das, method)\n",
    "            templist.append(this_voyage)\n",
    "        \n",
    "    # Check to see if the temporary list holds more than one result, if so: put it on the ambiguous-list\n",
    "    if len(templist) == 1:\n",
    "        for result in templist:\n",
    "            voyagedata.append(result)\n",
    "            continue\n",
    "\n",
    "    if len(templist) > 1:\n",
    "        for result in templist:\n",
    "            ambiguous.append(result)\n",
    "            \n",
    "# Make dataframes        \n",
    "matched = pd.DataFrame.from_records(voyagedata, columns=['BGB Shipvoyage ID', 'BGB Voyage ID', 'BGB ship ID', 'DAS voyage ID', 'DAS shipname ID', 'DAS shipnamevariant ID', 'BGB ship name', 'DAS ship name', 'BGB Booking year', 'DAS first seen', 'DAS last seen', 'Matched based on rule'])        \n",
    "multimatched = pd.DataFrame.from_records(ambiguous, columns=['BGB Shipvoyage ID', 'BGB Voyage ID', 'BGB ship ID', 'DAS voyage ID', 'DAS shipname ID', 'DAS shipnamevariant ID', 'BGB ship name', 'DAS ship name', 'BGB Booking year', 'DAS first seen', 'DAS last seen', 'Matched based on rule'])        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make a dataframe with information about the failed matches\n",
    "emtpylist = []\n",
    "\n",
    "for bgb_shipvoyageid in notmatched:\n",
    "    \n",
    "    # Get the BGB Voyage ID  \n",
    "    bgb_voyageid = int(bgb_relations.loc[bgb_shipvoyageid, ['voyId']])\n",
    "\n",
    "    # Get some basic information from BGB on the ships used in this ship/voyage combination\n",
    "    bgb_shipid = bgb_relations.loc[bgb_shipvoyageid, 'shipId']\n",
    "\n",
    "    # For some BGB ship IDs, there is no corresponding ship name in the BGB dataset (skip those)\n",
    "    try:\n",
    "        bgb_name = bgb_ships.loc[bgb_shipid, 'naam']\n",
    "    except:\n",
    "        continue\n",
    "\n",
    "    # Get the BGB booking year (With try/except since some BGB voyage IDs are not in the BGB voyages table)\n",
    "    try:\n",
    "        bgb_bookingyear = int(bgb_voyages.loc[bgb_voyageid, 'voyBookingYear'])\n",
    "    except:\n",
    "        bgb_bookingyear = 0\n",
    "    \n",
    "    this_voyage = (bgb_shipvoyageid, bgb_voyageid, bgb_shipid, bgb_name, bgb_bookingyear)\n",
    "    emtpylist.append(this_voyage)\n",
    "    \n",
    "nomatch = pd.DataFrame.from_records(emtpylist, columns=['BGB Shipvoyage ID', 'BGB Voyage ID', 'BGB ship ID', 'BGB ship name', 'BGB Booking year'])        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save in Excel sheet\n",
    "with pd.ExcelWriter('Matching_results.xlsx') as writer: \n",
    "    matched.to_excel(writer, sheet_name='Matches')\n",
    "    multimatched.to_excel(writer, sheet_name='Ambiguous')\n",
    "    nomatch.to_excel(writer, sheet_name='No match')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}