Kiribati2020_Merge_EffortBO.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "74df453c-a4b1-4555-8f5c-e0ae31691058",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Appending beneficial ownership data to KIR 2020 effort dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "008a4fd6-14fb-4feb-a232-0bd2dcc448f4",
   "metadata": {},
   "source": [
    "## Importing necessary packages and data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2a5c921c-8a5a-4465-95cc-6f69a8df8eb3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/home/jovyan/PNACaseStudy'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import os\n",
    "os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "0ce26659-d56c-4918-9708-2086228265a8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting openpyxl\n",
      "  Using cached openpyxl-3.1.2-py2.py3-none-any.whl (249 kB)\n",
      "Collecting et-xmlfile\n",
      "  Using cached et_xmlfile-1.1.0-py3-none-any.whl (4.7 kB)\n",
      "Installing collected packages: et-xmlfile, openpyxl\n",
      "Successfully installed et-xmlfile-1.1.0 openpyxl-3.1.2\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install openpyxl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a622d59b-1acd-4609-8858-df6318f843ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_directory = '/home/jovyan/PNACaseStudy'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2a75e42d-8743-47d7-9297-bb0b302e2a1f",
   "metadata": {},
   "outputs": [
    {
     "ename": "DriverError",
     "evalue": "KIR_2020_identity.shp: No such file or directory",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mCPLE_OpenFailedError\u001b[0m                      Traceback (most recent call last)",
      "File \u001b[0;32mfiona/_shim.pyx:83\u001b[0m, in \u001b[0;36mfiona._shim.gdal_open_vector\u001b[0;34m()\u001b[0m\n",
      "File \u001b[0;32mfiona/_err.pyx:291\u001b[0m, in \u001b[0;36mfiona._err.exc_wrap_pointer\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mCPLE_OpenFailedError\u001b[0m: KIR_2020_identity.shp: No such file or directory",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mDriverError\u001b[0m                               Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[4], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m effort \u001b[38;5;241m=\u001b[39m \u001b[43mgpd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread_file\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mKIR_2020_identity.shp\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m/srv/conda/envs/notebook/lib/python3.10/site-packages/geopandas/io/file.py:259\u001b[0m, in \u001b[0;36m_read_file\u001b[0;34m(filename, bbox, mask, rows, engine, **kwargs)\u001b[0m\n\u001b[1;32m    256\u001b[0m     path_or_bytes \u001b[38;5;241m=\u001b[39m filename\n\u001b[1;32m    258\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m engine \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mfiona\u001b[39m\u001b[38;5;124m\"\u001b[39m:\n\u001b[0;32m--> 259\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43m_read_file_fiona\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m    260\u001b[0m \u001b[43m        \u001b[49m\u001b[43mpath_or_bytes\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mfrom_bytes\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mbbox\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mbbox\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mmask\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mmask\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mrows\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mrows\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\n\u001b[1;32m    261\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    262\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m engine \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mpyogrio\u001b[39m\u001b[38;5;124m\"\u001b[39m:\n\u001b[1;32m    263\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m _read_file_pyogrio(\n\u001b[1;32m    264\u001b[0m         path_or_bytes, bbox\u001b[38;5;241m=\u001b[39mbbox, mask\u001b[38;5;241m=\u001b[39mmask, rows\u001b[38;5;241m=\u001b[39mrows, \u001b[38;5;241m*\u001b[39m\u001b[38;5;241m*\u001b[39mkwargs\n\u001b[1;32m    265\u001b[0m     )\n",
      "File \u001b[0;32m/srv/conda/envs/notebook/lib/python3.10/site-packages/geopandas/io/file.py:303\u001b[0m, in \u001b[0;36m_read_file_fiona\u001b[0;34m(path_or_bytes, from_bytes, bbox, mask, rows, where, **kwargs)\u001b[0m\n\u001b[1;32m    300\u001b[0m     reader \u001b[38;5;241m=\u001b[39m fiona\u001b[38;5;241m.\u001b[39mopen\n\u001b[1;32m    302\u001b[0m \u001b[38;5;28;01mwith\u001b[39;00m fiona_env():\n\u001b[0;32m--> 303\u001b[0m     \u001b[38;5;28;01mwith\u001b[39;00m \u001b[43mreader\u001b[49m\u001b[43m(\u001b[49m\u001b[43mpath_or_bytes\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m \u001b[38;5;28;01mas\u001b[39;00m features:\n\u001b[1;32m    304\u001b[0m         crs \u001b[38;5;241m=\u001b[39m features\u001b[38;5;241m.\u001b[39mcrs_wkt\n\u001b[1;32m    305\u001b[0m         \u001b[38;5;66;03m# attempt to get EPSG code\u001b[39;00m\n",
      "File \u001b[0;32m/srv/conda/envs/notebook/lib/python3.10/site-packages/fiona/env.py:408\u001b[0m, in \u001b[0;36mensure_env_with_credentials.<locals>.wrapper\u001b[0;34m(*args, **kwargs)\u001b[0m\n\u001b[1;32m    405\u001b[0m \u001b[38;5;129m@wraps\u001b[39m(f)\n\u001b[1;32m    406\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mwrapper\u001b[39m(\u001b[38;5;241m*\u001b[39margs, \u001b[38;5;241m*\u001b[39m\u001b[38;5;241m*\u001b[39mkwargs):\n\u001b[1;32m    407\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m local\u001b[38;5;241m.\u001b[39m_env:\n\u001b[0;32m--> 408\u001b[0m         \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mf\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43margs\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    409\u001b[0m     \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[1;32m    410\u001b[0m         \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(args[\u001b[38;5;241m0\u001b[39m], \u001b[38;5;28mstr\u001b[39m):\n",
      "File \u001b[0;32m/srv/conda/envs/notebook/lib/python3.10/site-packages/fiona/__init__.py:264\u001b[0m, in \u001b[0;36mopen\u001b[0;34m(fp, mode, driver, schema, crs, encoding, layer, vfs, enabled_drivers, crs_wkt, **kwargs)\u001b[0m\n\u001b[1;32m    261\u001b[0m     path \u001b[38;5;241m=\u001b[39m parse_path(fp)\n\u001b[1;32m    263\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m mode \u001b[38;5;129;01min\u001b[39;00m (\u001b[38;5;124m'\u001b[39m\u001b[38;5;124ma\u001b[39m\u001b[38;5;124m'\u001b[39m, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mr\u001b[39m\u001b[38;5;124m'\u001b[39m):\n\u001b[0;32m--> 264\u001b[0m     c \u001b[38;5;241m=\u001b[39m \u001b[43mCollection\u001b[49m\u001b[43m(\u001b[49m\u001b[43mpath\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mmode\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mdriver\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdriver\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mencoding\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mencoding\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    265\u001b[0m \u001b[43m                   \u001b[49m\u001b[43mlayer\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mlayer\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43menabled_drivers\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43menabled_drivers\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    266\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m mode \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mw\u001b[39m\u001b[38;5;124m'\u001b[39m:\n\u001b[1;32m    267\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m schema:\n\u001b[1;32m    268\u001b[0m         \u001b[38;5;66;03m# Make an ordered dict of schema properties.\u001b[39;00m\n",
      "File \u001b[0;32m/srv/conda/envs/notebook/lib/python3.10/site-packages/fiona/collection.py:162\u001b[0m, in \u001b[0;36mCollection.__init__\u001b[0;34m(self, path, mode, driver, schema, crs, encoding, layer, vsi, archive, enabled_drivers, crs_wkt, ignore_fields, ignore_geometry, **kwargs)\u001b[0m\n\u001b[1;32m    160\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mmode \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mr\u001b[39m\u001b[38;5;124m'\u001b[39m:\n\u001b[1;32m    161\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39msession \u001b[38;5;241m=\u001b[39m Session()\n\u001b[0;32m--> 162\u001b[0m     \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43msession\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mstart\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    163\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mmode \u001b[38;5;129;01min\u001b[39;00m (\u001b[38;5;124m'\u001b[39m\u001b[38;5;124ma\u001b[39m\u001b[38;5;124m'\u001b[39m, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mw\u001b[39m\u001b[38;5;124m'\u001b[39m):\n\u001b[1;32m    164\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39msession \u001b[38;5;241m=\u001b[39m WritingSession()\n",
      "File \u001b[0;32mfiona/ogrext.pyx:540\u001b[0m, in \u001b[0;36mfiona.ogrext.Session.start\u001b[0;34m()\u001b[0m\n",
      "File \u001b[0;32mfiona/_shim.pyx:90\u001b[0m, in \u001b[0;36mfiona._shim.gdal_open_vector\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mDriverError\u001b[0m: KIR_2020_identity.shp: No such file or directory"
     ]
    }
   ],
   "source": [
    "effort = gpd.read_file('KIR_2020_identity.shp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "bcb042f9-f0b7-4091-b0ed-db560313cb5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "beneficial = pd.read_excel('Final_VesselList.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a4166a6-9f08-40a5-b068-316ddcd8af24",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Tidying and merging data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5380c8f5-9b57-452b-9d54-702e1b23379f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['mmsi', 'n_shipname', 'imo', 'flag', 'length_m', 'tonnage_gt', 'engine_pow', 'owner_gfw', 'owner_flag', 'source_cod', 'owner', 'owner_address', 'owner_source', 'corporate_actor', 'corporate_actor_source_1', 'corporate_actor_source_2', 'corporate_actor_source_3', 'corporate_actor_source_4', 'corporate_actor_source_5', 'beneficial_owner', 'beneficial_owner_flag', 'subsidiaries_actor', 'subsidiaries_parent', 'SA_actor', 'SA_parent', 'subsidiaries_and_affiliates', 'beneficial_owner_and_subsidiaries_source_1', 'benficial_owner_and_subsidiaries_source_2', 'beneficial_owner_and_subsidiaries_source_3', 'beneficial_owner_and_subsidiaries_source_4', 'beneficial_owner_and_subsidiaries_source_5', 'beneficial_owner_and_subsidiaries_source_6', 'beneficial_owner_and_subsidiaries_source_7', 'beneficial_owner_and_subsidiaries_source_8', 'remarks_1', 'remarks_2', 'RFV_listed', 'MSC_F_31245', 'MSC_F_31441', 'MSC_F_30076', 'MSC_F_31362 (MSC_C_53088)', 'MSC_cert']\n"
     ]
    }
   ],
   "source": [
    "# Viewing column names to determine which will be removed for mapping \n",
    "print(beneficial.columns.tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "33ec8c30-861b-4991-b800-97c3c0c78079",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['mmsi', 'date', 'cell_ll_la', 'cell_ll_lo', 'hours', 'fishing_ho', 'flag_gfw', 'flag_ais', 'vessel_cla', 'vessel_rec', 'n_shipname', 'n_callsign', 'imo', 'flag', 'geartype', 'length_m', 'tonnage_gt', 'engine_pow', 'owner', 'owner_flag', 'source_cod', 'geometry']\n"
     ]
    }
   ],
   "source": [
    "print(effort.columns.tolist())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b2f9337-ed93-43d2-a7cf-3a76f89d2f7a",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "### Abandoned code - merging Caribe vessels by MMSI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "8067adae-0445-411a-b656-c694e7548b56",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MMSI 444000124 is present in the 'effort' dataset.\n"
     ]
    }
   ],
   "source": [
    "# Troubleshooting identity spoofing\n",
    "# I want to know if the mmsi number I found for the rogue vessel exists in the effort dataset\n",
    "#mmsi_444000124_present = (effort['mmsi'] == 444000124).any()\n",
    "\n",
    "#if mmsi_444000124_present:\n",
    "  #  print(\"MMSI 444000124 is present in the 'effort' dataset.\")\n",
    "#else:\n",
    "   # print(\"MMSI 444000124 is not present in the 'effort' dataset.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "30c5ccfd-008d-4d57-8d8d-beda5be6fab2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Give all 'Caribe' vessels the same 'flag_gfw'\n",
    "#filtered_df.loc[filtered_df['mmsi'] == 444000124, 'flag_gfw'] = filtered_df.loc[filtered_df['mmsi'] == 572381220, 'flag_gfw'].values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ac676ab0-0a08-4e7c-a2b1-7bcbd2d0a82e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change the mmsi values of all 'Caribe' vessels from '444000124' to '572381220'\n",
    "#effort['mmsi'] = effort['mmsi'].replace({444000124: 572381220})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "61ca7973-ad24-4a7f-b979-f79c60de4d7f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "156"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the replacement worked (should drop from 157 to 156 unique vessels)\n",
    "#effort['mmsi'].nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ccedeb4a-e5a9-4ce2-8b68-6d6f029f9e08",
   "metadata": {},
   "source": [
    "### Continue Tidying"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "35460baa-eb58-4475-8f02-bf2ac6bbc8c9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "n_shipname\n",
      "imo\n",
      "flag\n",
      "length_m\n",
      "tonnage_gt\n",
      "engine_pow\n",
      "owner_flag\n",
      "source_cod\n",
      "owner\n"
     ]
    }
   ],
   "source": [
    "# Dropping columns from 'beneficial' that already exist in 'effort'\n",
    "\n",
    "# Loop through columns in 'beneficial'\n",
    "columns_to_drop = []\n",
    "for column in beneficial.columns:\n",
    "    if column in effort.columns and column != 'mmsi':\n",
    "        columns_to_drop.append(column)\n",
    "        print(column)\n",
    "\n",
    "# Drop duplicate columns\n",
    "beneficial = beneficial.drop(columns=columns_to_drop)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "e07aff84-598a-4f4d-ac07-dc8005563555",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['mmsi', 'owner_gfw', 'owner_address', 'owner_source', 'corporate_actor', 'corporate_actor_source_1', 'corporate_actor_source_2', 'corporate_actor_source_3', 'corporate_actor_source_4', 'corporate_actor_source_5', 'beneficial_owner', 'beneficial_owner_flag', 'subsidiaries_actor', 'subsidiaries_parent', 'SA_actor', 'SA_parent', 'subsidiaries_and_affiliates', 'beneficial_owner_and_subsidiaries_source_1', 'benficial_owner_and_subsidiaries_source_2', 'beneficial_owner_and_subsidiaries_source_3', 'beneficial_owner_and_subsidiaries_source_4', 'beneficial_owner_and_subsidiaries_source_5', 'beneficial_owner_and_subsidiaries_source_6', 'beneficial_owner_and_subsidiaries_source_7', 'beneficial_owner_and_subsidiaries_source_8', 'remarks_1', 'remarks_2', 'RFV_listed', 'MSC_F_31245', 'MSC_F_31441', 'MSC_F_30076', 'MSC_F_31362 (MSC_C_53088)', 'MSC_cert']\n"
     ]
    }
   ],
   "source": [
    "# Check the drop worked \n",
    "print(beneficial.columns.tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "cf91587f-4315-4ea8-ad1f-a8a079d03a40",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merging the two data frames based on mmsi\n",
    "merged_df = pd.merge(effort, beneficial, on='mmsi', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "e79bd803-78e4-48b3-8534-093d6a7b600b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['mmsi', 'date', 'cell_ll_la', 'cell_ll_lo', 'hours', 'fishing_ho', 'flag_gfw', 'flag_ais', 'vessel_cla', 'vessel_rec', 'n_shipname', 'n_callsign', 'imo', 'flag', 'geartype', 'length_m', 'tonnage_gt', 'engine_pow', 'owner', 'owner_flag', 'source_cod', 'geometry', 'owner_gfw', 'owner_address', 'owner_source', 'corporate_actor', 'corporate_actor_source_1', 'corporate_actor_source_2', 'corporate_actor_source_3', 'corporate_actor_source_4', 'corporate_actor_source_5', 'beneficial_owner', 'beneficial_owner_flag', 'subsidiaries_actor', 'subsidiaries_parent', 'SA_actor', 'SA_parent', 'subsidiaries_and_affiliates', 'beneficial_owner_and_subsidiaries_source_1', 'benficial_owner_and_subsidiaries_source_2', 'beneficial_owner_and_subsidiaries_source_3', 'beneficial_owner_and_subsidiaries_source_4', 'beneficial_owner_and_subsidiaries_source_5', 'beneficial_owner_and_subsidiaries_source_6', 'beneficial_owner_and_subsidiaries_source_7', 'beneficial_owner_and_subsidiaries_source_8', 'remarks_1', 'remarks_2', 'RFV_listed', 'MSC_F_31245', 'MSC_F_31441', 'MSC_F_30076', 'MSC_F_31362 (MSC_C_53088)', 'MSC_cert']\n"
     ]
    }
   ],
   "source": [
    "# Checking that the merge worked\n",
    "print(merged_df.columns.tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "4a00970d-f0af-4547-9237-e0445d1559cf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "180871"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(merged_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "0aa3325a-763d-49db-936c-38a415df6334",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             mmsi        date  cell_ll_la  cell_ll_lo   hours  fishing_ho  \\\n",
      "2       412439604  2020-09-19        -4.1       173.5  0.6422      0.1450   \n",
      "5       544124120  2020-09-22        -4.1       174.5  2.2516      1.6033   \n",
      "6       412371222  2020-01-23        -4.1       174.6  0.6397      0.6397   \n",
      "8       538060008  2020-09-20        -4.1       174.6  1.9733      1.9733   \n",
      "11      416253900  2020-07-28        -4.1       174.4  3.3338      2.2352   \n",
      "...           ...         ...         ...         ...     ...         ...   \n",
      "180777  572975210  2020-02-16         4.3      -161.6  2.9877      2.9877   \n",
      "180798  572975210  2020-02-16         4.4      -161.5  4.1386      2.9347   \n",
      "180799  572975210  2020-02-16         4.4      -161.4  3.9136      0.8833   \n",
      "180803  529440000  2020-06-23         4.4      -161.8  6.1908      1.1650   \n",
      "180804  303343000  2020-02-05         4.6      -161.7  1.2300      1.2300   \n",
      "\n",
      "       flag_gfw flag_ais         vessel_cla  \\\n",
      "2           CHN      CHN  tuna_purse_seines   \n",
      "5           NRU      NRU  tuna_purse_seines   \n",
      "6           CHN      CHN  tuna_purse_seines   \n",
      "8           MHL      MHL  tuna_purse_seines   \n",
      "11          TWN      TWN  tuna_purse_seines   \n",
      "...         ...      ...                ...   \n",
      "180777      TUV      TUV  tuna_purse_seines   \n",
      "180798      TUV      TUV  tuna_purse_seines   \n",
      "180799      TUV      TUV  tuna_purse_seines   \n",
      "180803      KIR      KIR  tuna_purse_seines   \n",
      "180804      USA      USA  tuna_purse_seines   \n",
      "\n",
      "                                               vessel_rec  ...  \\\n",
      "2                        FFA-35019|IMO-8996176|WCPFC-4431  ...   \n",
      "5            FFA-35772|IMO-9417397|USA-1214250|WCPFC-4611  ...   \n",
      "6                       FFA-36739|IMO-9808687|WCPFC-11438  ...   \n",
      "8                        FFA-35339|IMO-9349899|WCPFC-5143  ...   \n",
      "11                      FFA-36646|IMO-9764271|WCPFC-11247  ...   \n",
      "...                                                   ...  ...   \n",
      "180777          FFA-4705|IMO-8102921|IOTC-8415|WCPFC-2245  ...   \n",
      "180798          FFA-4705|IMO-8102921|IOTC-8415|WCPFC-2245  ...   \n",
      "180799          FFA-4705|IMO-8102921|IOTC-8415|WCPFC-2245  ...   \n",
      "180803                   FFA-22889|IMO-8003230|WCPFC-2244  ...   \n",
      "180804  FFA-25957|IATTC-4117|IMO-9018892|USA-962922|WC...  ...   \n",
      "\n",
      "       beneficial_owner_and_subsidiaries_source_7  \\\n",
      "2                                             NaN   \n",
      "5                                             NaN   \n",
      "6         https://www.skmic.sh.cn/en/company.html   \n",
      "8                                             NaN   \n",
      "11                                            NaN   \n",
      "...                                           ...   \n",
      "180777                                        NaN   \n",
      "180798                                        NaN   \n",
      "180799                                        NaN   \n",
      "180803                                        NaN   \n",
      "180804                                        NaN   \n",
      "\n",
      "       beneficial_owner_and_subsidiaries_source_8  \\\n",
      "2                                             NaN   \n",
      "5                                             NaN   \n",
      "6                                             NaN   \n",
      "8                                             NaN   \n",
      "11                                            NaN   \n",
      "...                                           ...   \n",
      "180777                                        NaN   \n",
      "180798                                        NaN   \n",
      "180799                                        NaN   \n",
      "180803                                        NaN   \n",
      "180804                                        NaN   \n",
      "\n",
      "                                                remarks_1  \\\n",
      "2                              Linked to observer deaths.   \n",
      "5       Tsai Chen Hao (or Howard Tsai) is the operatio...   \n",
      "6       Available information on website changes depen...   \n",
      "8       \"MIMRA has a joint venture with Koo's Fishing ...   \n",
      "11      Vessel name: changed from KWILA 888, checked w...   \n",
      "...                                                   ...   \n",
      "180777         Vessel name: checked with Marine Traffic.    \n",
      "180798         Vessel name: checked with Marine Traffic.    \n",
      "180799         Vessel name: checked with Marine Traffic.    \n",
      "180803                   Address led to Sajo Industries     \n",
      "180804  CAPE Elizabeth FISHING LP Same address as Tri ...   \n",
      "\n",
      "                                                remarks_2 RFV_listed  \\\n",
      "2                                                     NaN          Y   \n",
      "5       Connection to Konstantinos Barkas and South Pa...          Y   \n",
      "6                                                     NaN          Y   \n",
      "8                                                     NaN          Y   \n",
      "11                                                    NaN          Y   \n",
      "...                                                   ...        ...   \n",
      "180777                                                NaN          Y   \n",
      "180798                                                NaN          Y   \n",
      "180799                                                NaN          Y   \n",
      "180803                                                NaN          Y   \n",
      "180804                                                NaN          Y   \n",
      "\n",
      "        MSC_F_31245  MSC_F_31441  MSC_F_30076 MSC_F_31362 (MSC_C_53088)  \\\n",
      "2                 N            N            N                         Y   \n",
      "5                 N            N            N                         Y   \n",
      "6                 N            N            N                         Y   \n",
      "8                 N            N            N                         Y   \n",
      "11                N            N            N                         Y   \n",
      "...             ...          ...          ...                       ...   \n",
      "180777            N            N            N                         Y   \n",
      "180798            N            N            N                         Y   \n",
      "180799            N            N            N                         Y   \n",
      "180803            N            N            N                         Y   \n",
      "180804            Y            N            N                         Y   \n",
      "\n",
      "       MSC_cert  \n",
      "2             Y  \n",
      "5             Y  \n",
      "6             Y  \n",
      "8             Y  \n",
      "11            Y  \n",
      "...         ...  \n",
      "180777        Y  \n",
      "180798        Y  \n",
      "180799        Y  \n",
      "180803        Y  \n",
      "180804        Y  \n",
      "\n",
      "[21663 rows x 54 columns]\n"
     ]
    }
   ],
   "source": [
    "# Filtering out rows where fishing_ho equals 0\n",
    "filtered_df = merged_df[merged_df['fishing_ho'] != 0]\n",
    "\n",
    "# Print the filtered DataFrame\n",
    "print(filtered_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "65611a6c-f33d-44f0-8f74-7081cee7f529",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21663"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(filtered_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "696a5a8f-92da-4818-8471-e8b5f24eba44",
   "metadata": {},
   "source": [
    "## Troubleshooting flag columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "29526f88-c337-4621-95b7-e434fe166ba1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           flag_gfw  flag_ais  flag\n",
      "imo                                \n",
      "9745823.0         2         1     2\n",
      "9771951.0         2         1     2\n"
     ]
    }
   ],
   "source": [
    "# Deciding which flag column to use for analysis\n",
    "# Seeing if flag state differs for each mmsi across columns\n",
    "\n",
    "# Select relevant columns\n",
    "flag_columns = ['imo', 'flag_gfw', 'flag_ais', 'flag']\n",
    "\n",
    "# Create a new DataFrame with the selected columns\n",
    "flag_comparison_df = filtered_df[flag_columns]\n",
    "\n",
    "# Group by 'mmsi' and check if there are differing flag codes\n",
    "differing_flags = flag_comparison_df.groupby('imo').nunique()\n",
    "\n",
    "# Filter rows where the number of unique flags is greater than 1\n",
    "differing_flags = differing_flags[differing_flags['flag'] > 1]\n",
    "\n",
    "# Display the rows where differing flags are observed\n",
    "print(differing_flags)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "64ba541d-4081-4240-bfdd-ae296af214d5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "imo value 9386665.0 has null flag values: {'flag_gfw': 'KOR', 'flag_ais': None, 'flag': 'KOR'}\n",
      "imo value 9745823.0 has null flag values: {'flag_gfw': 'NRU', 'flag_ais': None, 'flag': 'NRU'}\n",
      "imo value 9746281.0 has null flag values: {'flag_gfw': 'NRU', 'flag_ais': None, 'flag': 'NRU'}\n",
      "imo value 9771951.0 has null flag values: {'flag_gfw': 'NRU', 'flag_ais': None, 'flag': 'NRU'}\n"
     ]
    }
   ],
   "source": [
    "# Here, I am checking which vessels are missing flags\n",
    "\n",
    "# Select relevant columns\n",
    "flag_columns = ['imo', 'flag_gfw', 'flag_ais', 'flag']\n",
    "\n",
    "# Group by 'imo' and iterate through each group\n",
    "for group_name, group_df in filtered_df.groupby('imo')[flag_columns]:\n",
    "    imo_value = group_name\n",
    "    flags = group_df[['flag_gfw', 'flag_ais', 'flag']].iloc[0].to_dict()\n",
    "\n",
    "    # Check for null values in the flags\n",
    "    if any(pd.isnull(flag) for flag in flags.values()):\n",
    "        print(f\"imo value {imo_value} has null flag values: {flags}\")\n",
    "        \n",
    "# I will use 'flag_gfw' in my analysis. Flags do not differ across columns, and 'flag_gfw' (and 'flag') have no null values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "f77cc8f7-7fb1-40b5-838a-4cfbeb93a0e0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are no null values under the 'flag_gfw' column.\n"
     ]
    }
   ],
   "source": [
    "# Remnant of troubleshooting issue with 'flag_gfw'.\n",
    "\n",
    "null_flag_gfw_rows = filtered_df[filtered_df['flag_gfw'].isnull()]\n",
    "unique_null_shipnames = null_flag_gfw_rows['mmsi'].unique()\n",
    "\n",
    "if not null_flag_gfw_rows.empty:\n",
    "    print(f\"There are {len(unique_null_shipnames)} unique 'n_shipname' values with null 'flag_gfw' values.\")\n",
    "    print(unique_null_shipnames)\n",
    "else:\n",
    "    print(\"There are no null values under the 'flag_gfw' column.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "a12a70fe-e21e-4501-bd64-e4488892fe40",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Give all 'Caribe' vessels the same 'flag_gfw'\n",
    "filtered_df.loc[filtered_df['mmsi'] == 444000124, 'flag_gfw'] = filtered_df.loc[filtered_df['mmsi'] == 572381220, 'flag_gfw'].values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "99e88a83-e790-4839-bb7a-b693671678b0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of unique 'mmsi' values: 148\n",
      "Number of unique 'imo' values: 145\n"
     ]
    }
   ],
   "source": [
    "# Count the unique values in the 'mms' and 'imo' columns\n",
    "unique_count_mmsi = filtered_df['mmsi'].nunique()\n",
    "unique_count_imo = filtered_df['imo'].nunique()\n",
    "\n",
    "print(\"Number of unique 'mmsi' values:\", unique_count_mmsi)\n",
    "print(\"Number of unique 'imo' values:\", unique_count_imo)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "0d0ea6b1-d740-4fc5-b9ea-d2eec4dd54f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "del merged_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a685ac96-58f5-405d-882d-7cdf2d14703f",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Calculating effort (and # of vessels for columns other than imo) by main company, company flag, vessel flag, MSC certification, and mmsi."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "3cbd83d1-f55f-4110-ae15-404e85d58eaf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Results for beneficial_owner:\n",
      "                                    beneficial_owner  annual_hours  \\\n",
      "0                                 AFT Holdings, Inc.     2287.2778   \n",
      "1                                       Bolton Group     1181.4145   \n",
      "2                      Bright Food (Group) Co., Ltd.    17088.6324   \n",
      "3                     Caroline Fisheries Corporation      233.4445   \n",
      "4  China National Agricultural Development Group ...     3389.5310   \n",
      "\n",
      "   annual_fishing_hours  vessel_count  \n",
      "0             1631.2503             5  \n",
      "1              841.8735             7  \n",
      "2            11515.7207            11  \n",
      "3              158.6865             4  \n",
      "4             2494.4482             2  \n",
      "\n",
      "Results for flag_gfw:\n",
      "  flag_gfw  annual_hours  annual_fishing_hours  vessel_count\n",
      "0      CHN    23312.2346            16196.8133            13\n",
      "1      ECU      894.4141              613.2148             3\n",
      "2      ESP        1.9327                1.4136             1\n",
      "3      FSM     4548.8524             3138.2150            15\n",
      "4      JPN       16.7549               12.8935             2\n",
      "\n",
      "Results for beneficial_owner_flag:\n",
      "  beneficial_owner_flag  annual_hours  annual_fishing_hours  vessel_count\n",
      "0                   CHN    31332.3380            21541.7842            19\n",
      "1                   ESP      446.5229              316.6715             4\n",
      "2                   FSM      253.4422              170.3933             5\n",
      "3                   HKG     1475.2152              995.3919             4\n",
      "4                   ITA     1181.4145              841.8735             7\n",
      "\n",
      "Results for imo:\n",
      "         imo  annual_hours  annual_fishing_hours\n",
      "0  6717801.0        4.3530                4.3530\n",
      "1  7508893.0        9.8475                5.8462\n",
      "2  7514842.0      245.9768              168.8651\n",
      "3  7641580.0     1164.8131              969.9943\n",
      "4  7803255.0      144.7344              119.9596\n",
      "\n",
      "Results for mmsi:\n",
      "        mmsi  annual_hours  annual_fishing_hours\n",
      "0  224523000        1.9327                1.4136\n",
      "1  303343000       52.4886               43.7911\n",
      "2  338013000       64.5733               51.7672\n",
      "3  338712000      162.3318              125.7483\n",
      "4  338793000      198.5785              156.4493\n",
      "\n",
      "Results for MSC_F_31245:\n",
      "  MSC_F_31245  annual_hours  annual_fishing_hours  vessel_count  company_count\n",
      "0           N    72993.1883            50659.7382           140             44\n",
      "1           Y      620.7699              465.2461             5              1\n",
      "\n",
      "Results for MSC_F_31441:\n",
      "  MSC_F_31441  annual_hours  annual_fishing_hours  vessel_count  company_count\n",
      "0           N    72052.6237            50048.6213           135             44\n",
      "1           Y     1561.3345             1076.3630            10              1\n",
      "\n",
      "Results for MSC_F_30076:\n",
      "  MSC_F_30076  annual_hours  annual_fishing_hours  vessel_count  company_count\n",
      "0           N    59298.6973            40834.2459           132             42\n",
      "1           Y    14315.2609            10290.7384            13              3\n",
      "\n",
      "Results for MSC_F_31362 (MSC_C_53088):\n",
      "  MSC_F_31362 (MSC_C_53088)  annual_hours  annual_fishing_hours  vessel_count  \\\n",
      "0                         N     4613.9842             2932.4397            22   \n",
      "1                         Y    68618.1041            47938.6204           124   \n",
      "\n",
      "   company_count  \n",
      "0             14  \n",
      "1             37  \n",
      "\n",
      "Results for MSC_cert:\n",
      "  MSC_cert  annual_hours  annual_fishing_hours  vessel_count  company_count\n",
      "0        N     4995.8541             3186.3639            23             15\n",
      "1        Y    68618.1041            47938.6204           124             37\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Calculating annual fishing hours and hours for each vessel, company, flag, company flag, and MSC certification\n",
    "\n",
    "fields = filtered_df[['beneficial_owner', 'flag_gfw', 'beneficial_owner_flag', 'imo', 'mmsi', 'MSC_F_31245', 'MSC_F_31441', 'MSC_F_30076', 'MSC_F_31362 (MSC_C_53088)', 'MSC_cert']]\n",
    "\n",
    "combined_df = pd.DataFrame() # empty df to store the results\n",
    "\n",
    "result_dfs = {} # empty dictionary to store the dfs\n",
    "\n",
    "for field in fields:\n",
    "    \n",
    "    if field == 'mmsi' or field == 'imo':\n",
    "        df = filtered_df.groupby(field).agg({'hours': 'sum', 'fishing_ho': 'sum'}).reset_index()\n",
    "        df.columns = [field, 'annual_hours', 'annual_fishing_hours']\n",
    "        \n",
    "    #elif field == 'imo':\n",
    "        #df = filtered_df.groupby(field).agg({'hours': 'sum', 'fishing_ho': 'sum'}).reset_index()\n",
    "        #df.columns = [field, 'annual_hours', 'annual_fishing_hours']\n",
    "    \n",
    "    elif field.startswith('MSC'):\n",
    "        df = filtered_df.groupby(field).agg({'hours': 'sum', 'fishing_ho': 'sum', 'imo': 'nunique', 'beneficial_owner': 'nunique'}).reset_index()\n",
    "        df.columns = [field, 'annual_hours', 'annual_fishing_hours', 'vessel_count', 'company_count']\n",
    "    \n",
    "    else:\n",
    "        df = filtered_df.groupby(field).agg({'hours': 'sum', 'fishing_ho': 'sum', 'imo' : 'nunique'}).reset_index()\n",
    "        df.columns = [field, 'annual_hours', 'annual_fishing_hours', 'vessel_count']\n",
    "        \n",
    "    # Save the result df to the dictionary\n",
    "    result_dfs[field] = df\n",
    "\n",
    "    # Concatenate the current df with the combined df\n",
    "    combined_df = pd.concat([combined_df, df], ignore_index=True)\n",
    "\n",
    "    # Checking that the aggregation worked\n",
    "    print(f\"Results for {field}:\\n{df.head(5)}\\n\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "88d43dbd-341c-451e-88b1-5175b0154068",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total effort for the beneficial_owner file 51124.9843 hours.\n",
      "Total effort for the flag_gfw file 51124.984300000004 hours.\n",
      "Total effort for the beneficial_owner_flag file 51124.9843 hours.\n",
      "Total effort for the imo file 51124.9843 hours.\n",
      "Total effort for the mmsi file 51124.9843 hours.\n",
      "Total effort for the MSC_F_31245 file 51124.9843 hours.\n",
      "Total effort for the MSC_F_31441 file 51124.9843 hours.\n",
      "Total effort for the MSC_F_30076 file 51124.984300000004 hours.\n",
      "Total effort for the MSC_F_31362 (MSC_C_53088) file 50871.0601 hours.\n",
      "Total effort for the MSC_cert file 51124.9843 hours.\n"
     ]
    }
   ],
   "source": [
    "# Cross-checking total fishing effort across csv files and merged shapefile\n",
    "# The MSC certifications will not amount to total annual effort because each covers a subsection of the fishery.\n",
    "for field, df in result_dfs.items():\n",
    "    total = df['annual_fishing_hours'].sum()\n",
    "    print(f\"Total effort for the {field} file {total} hours.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d151f24a-e287-42ee-a924-91bed0adf7d8",
   "metadata": {},
   "source": [
    "### Troubleshooting null 'imo' value"
   ]
  },
  {
   "cell_type": "raw",
   "id": "52deffe5-c27b-448e-a026-0f39d6fea90c",
   "metadata": {},
   "source": [
    "You will have to re-run effort calculations after assigning an imo number to 'Caribe'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "3d05c2bc-0da9-4bd0-bba9-f8e52810c21b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are no null values under the 'imo' column.\n"
     ]
    }
   ],
   "source": [
    "null_imo_rows = filtered_df[filtered_df['imo'].isnull()]\n",
    "unique_null_shipnames = null_imo_rows['mmsi'].unique()\n",
    "\n",
    "if not null_imo_rows.empty:\n",
    "    print(f\"There are {len(unique_null_shipnames)} unique 'n_shipname' values with null 'imo' values.\")\n",
    "    print(unique_null_shipnames)\n",
    "else:\n",
    "    print(\"There are no null values under the 'imo' column.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "1ed886b2-a968-4ba2-b57d-eea9537a2efc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Give both Caribe vessels the same imo number\n",
    "filtered_df.loc[effort['mmsi'] == 444000124, 'imo'] = effort.loc[effort['mmsi'] == 572381220, 'imo'].values[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "078c0405-be65-4a67-8eb6-4977b1be2770",
   "metadata": {},
   "source": [
    "### Saving output of calculations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "db9a847a-b6b2-49fa-92fa-655f02891cd6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "beneficial_owner_Effort.csv saved.\n",
      "flag_gfw_Effort.csv saved.\n",
      "beneficial_owner_flag_Effort.csv saved.\n",
      "imo_Effort.csv saved.\n",
      "mmsi_Effort.csv saved.\n",
      "MSC_F_31245_Effort.csv saved.\n",
      "MSC_F_31441_Effort.csv saved.\n",
      "MSC_F_30076_Effort.csv saved.\n",
      "MSC_F_31362 (MSC_C_53088)_Effort.csv saved.\n",
      "MSC_cert_Effort.csv saved.\n"
     ]
    }
   ],
   "source": [
    "# Save each df to a separate csv\n",
    "for field, df in result_dfs.items():\n",
    "    df.to_csv(f\"{field}_Effort.csv\", index=False)\n",
    "    print(f\"{field}_Effort.csv saved.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65a1dac7-6c1e-4519-beab-f43d2ad58fa7",
   "metadata": {},
   "source": [
    "## Adding annual hours and effort of each vessel to the main effort dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "4bb02ffb-c34a-4a11-9ee8-10e7a06199b9",
   "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>mmsi</th>\n",
       "      <th>date</th>\n",
       "      <th>cell_ll_la</th>\n",
       "      <th>cell_ll_lo</th>\n",
       "      <th>hours</th>\n",
       "      <th>fishing_ho</th>\n",
       "      <th>flag_gfw</th>\n",
       "      <th>flag_ais</th>\n",
       "      <th>vessel_cla</th>\n",
       "      <th>vessel_rec</th>\n",
       "      <th>...</th>\n",
       "      <th>remarks_1</th>\n",
       "      <th>remarks_2</th>\n",
       "      <th>RFV_listed</th>\n",
       "      <th>MSC_F_31245</th>\n",
       "      <th>MSC_F_31441</th>\n",
       "      <th>MSC_F_30076</th>\n",
       "      <th>MSC_F_31362 (MSC_C_53088)</th>\n",
       "      <th>MSC_cert</th>\n",
       "      <th>hours_sum</th>\n",
       "      <th>fishing_ho_sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>412439604</td>\n",
       "      <td>2020-09-19</td>\n",
       "      <td>-4.1</td>\n",
       "      <td>173.5</td>\n",
       "      <td>0.6422</td>\n",
       "      <td>0.1450</td>\n",
       "      <td>CHN</td>\n",
       "      <td>CHN</td>\n",
       "      <td>tuna_purse_seines</td>\n",
       "      <td>FFA-35019|IMO-8996176|WCPFC-4431</td>\n",
       "      <td>...</td>\n",
       "      <td>Linked to observer deaths.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>1230.557</td>\n",
       "      <td>855.6366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>412439604</td>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>-4.1</td>\n",
       "      <td>176.5</td>\n",
       "      <td>5.2555</td>\n",
       "      <td>4.3694</td>\n",
       "      <td>CHN</td>\n",
       "      <td>CHN</td>\n",
       "      <td>tuna_purse_seines</td>\n",
       "      <td>FFA-35019|IMO-8996176|WCPFC-4431</td>\n",
       "      <td>...</td>\n",
       "      <td>Linked to observer deaths.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>1230.557</td>\n",
       "      <td>855.6366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>412439604</td>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>-4.1</td>\n",
       "      <td>176.4</td>\n",
       "      <td>5.7683</td>\n",
       "      <td>4.3713</td>\n",
       "      <td>CHN</td>\n",
       "      <td>CHN</td>\n",
       "      <td>tuna_purse_seines</td>\n",
       "      <td>FFA-35019|IMO-8996176|WCPFC-4431</td>\n",
       "      <td>...</td>\n",
       "      <td>Linked to observer deaths.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>1230.557</td>\n",
       "      <td>855.6366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>412439604</td>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>176.5</td>\n",
       "      <td>2.7400</td>\n",
       "      <td>2.3875</td>\n",
       "      <td>CHN</td>\n",
       "      <td>CHN</td>\n",
       "      <td>tuna_purse_seines</td>\n",
       "      <td>FFA-35019|IMO-8996176|WCPFC-4431</td>\n",
       "      <td>...</td>\n",
       "      <td>Linked to observer deaths.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>1230.557</td>\n",
       "      <td>855.6366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>412439604</td>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>176.4</td>\n",
       "      <td>3.0694</td>\n",
       "      <td>2.1052</td>\n",
       "      <td>CHN</td>\n",
       "      <td>CHN</td>\n",
       "      <td>tuna_purse_seines</td>\n",
       "      <td>FFA-35019|IMO-8996176|WCPFC-4431</td>\n",
       "      <td>...</td>\n",
       "      <td>Linked to observer deaths.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>1230.557</td>\n",
       "      <td>855.6366</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 56 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        mmsi        date  cell_ll_la  cell_ll_lo   hours  fishing_ho flag_gfw  \\\n",
       "0  412439604  2020-09-19        -4.1       173.5  0.6422      0.1450      CHN   \n",
       "1  412439604  2020-03-02        -4.1       176.5  5.2555      4.3694      CHN   \n",
       "2  412439604  2020-03-02        -4.1       176.4  5.7683      4.3713      CHN   \n",
       "3  412439604  2020-03-02        -4.0       176.5  2.7400      2.3875      CHN   \n",
       "4  412439604  2020-03-02        -4.0       176.4  3.0694      2.1052      CHN   \n",
       "\n",
       "  flag_ais         vessel_cla                        vessel_rec  ...  \\\n",
       "0      CHN  tuna_purse_seines  FFA-35019|IMO-8996176|WCPFC-4431  ...   \n",
       "1      CHN  tuna_purse_seines  FFA-35019|IMO-8996176|WCPFC-4431  ...   \n",
       "2      CHN  tuna_purse_seines  FFA-35019|IMO-8996176|WCPFC-4431  ...   \n",
       "3      CHN  tuna_purse_seines  FFA-35019|IMO-8996176|WCPFC-4431  ...   \n",
       "4      CHN  tuna_purse_seines  FFA-35019|IMO-8996176|WCPFC-4431  ...   \n",
       "\n",
       "                    remarks_1 remarks_2  RFV_listed MSC_F_31245 MSC_F_31441  \\\n",
       "0  Linked to observer deaths.       NaN           Y           N           N   \n",
       "1  Linked to observer deaths.       NaN           Y           N           N   \n",
       "2  Linked to observer deaths.       NaN           Y           N           N   \n",
       "3  Linked to observer deaths.       NaN           Y           N           N   \n",
       "4  Linked to observer deaths.       NaN           Y           N           N   \n",
       "\n",
       "   MSC_F_30076  MSC_F_31362 (MSC_C_53088)  MSC_cert hours_sum fishing_ho_sum  \n",
       "0            N                          Y         Y  1230.557       855.6366  \n",
       "1            N                          Y         Y  1230.557       855.6366  \n",
       "2            N                          Y         Y  1230.557       855.6366  \n",
       "3            N                          Y         Y  1230.557       855.6366  \n",
       "4            N                          Y         Y  1230.557       855.6366  \n",
       "\n",
       "[5 rows x 56 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = filtered_df.groupby('imo').agg({'hours': 'sum', 'fishing_ho': 'sum'}).reset_index()\n",
    "\n",
    "# Merge annual sums to the original DataFrame based on 'mmsi'\n",
    "merged_df = pd.merge(filtered_df, df, on='imo', suffixes=('', '_sum'))\n",
    "\n",
    "# Checking merge worked\n",
    "merged_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "12c245bc-1252-4441-b917-88c6b3982c25",
   "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>mmsi</th>\n",
       "      <th>date</th>\n",
       "      <th>cell_ll_la</th>\n",
       "      <th>cell_ll_lo</th>\n",
       "      <th>hours</th>\n",
       "      <th>fishing_ho</th>\n",
       "      <th>flag_gfw</th>\n",
       "      <th>flag_ais</th>\n",
       "      <th>vessel_cla</th>\n",
       "      <th>vessel_rec</th>\n",
       "      <th>...</th>\n",
       "      <th>remarks_1</th>\n",
       "      <th>remarks_2</th>\n",
       "      <th>RFV_listed</th>\n",
       "      <th>MSC_F_31245</th>\n",
       "      <th>MSC_F_31441</th>\n",
       "      <th>MSC_F_30076</th>\n",
       "      <th>MSC_F_31362 (MSC_C_53088)</th>\n",
       "      <th>MSC_cert</th>\n",
       "      <th>ann_hrs</th>\n",
       "      <th>ann_fsh_hrs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>412439604</td>\n",
       "      <td>2020-09-19</td>\n",
       "      <td>-4.1</td>\n",
       "      <td>173.5</td>\n",
       "      <td>0.6422</td>\n",
       "      <td>0.1450</td>\n",
       "      <td>CHN</td>\n",
       "      <td>CHN</td>\n",
       "      <td>tuna_purse_seines</td>\n",
       "      <td>FFA-35019|IMO-8996176|WCPFC-4431</td>\n",
       "      <td>...</td>\n",
       "      <td>Linked to observer deaths.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>1230.557</td>\n",
       "      <td>855.6366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>412439604</td>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>-4.1</td>\n",
       "      <td>176.5</td>\n",
       "      <td>5.2555</td>\n",
       "      <td>4.3694</td>\n",
       "      <td>CHN</td>\n",
       "      <td>CHN</td>\n",
       "      <td>tuna_purse_seines</td>\n",
       "      <td>FFA-35019|IMO-8996176|WCPFC-4431</td>\n",
       "      <td>...</td>\n",
       "      <td>Linked to observer deaths.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>1230.557</td>\n",
       "      <td>855.6366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>412439604</td>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>-4.1</td>\n",
       "      <td>176.4</td>\n",
       "      <td>5.7683</td>\n",
       "      <td>4.3713</td>\n",
       "      <td>CHN</td>\n",
       "      <td>CHN</td>\n",
       "      <td>tuna_purse_seines</td>\n",
       "      <td>FFA-35019|IMO-8996176|WCPFC-4431</td>\n",
       "      <td>...</td>\n",
       "      <td>Linked to observer deaths.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>1230.557</td>\n",
       "      <td>855.6366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>412439604</td>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>176.5</td>\n",
       "      <td>2.7400</td>\n",
       "      <td>2.3875</td>\n",
       "      <td>CHN</td>\n",
       "      <td>CHN</td>\n",
       "      <td>tuna_purse_seines</td>\n",
       "      <td>FFA-35019|IMO-8996176|WCPFC-4431</td>\n",
       "      <td>...</td>\n",
       "      <td>Linked to observer deaths.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>1230.557</td>\n",
       "      <td>855.6366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>412439604</td>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>176.4</td>\n",
       "      <td>3.0694</td>\n",
       "      <td>2.1052</td>\n",
       "      <td>CHN</td>\n",
       "      <td>CHN</td>\n",
       "      <td>tuna_purse_seines</td>\n",
       "      <td>FFA-35019|IMO-8996176|WCPFC-4431</td>\n",
       "      <td>...</td>\n",
       "      <td>Linked to observer deaths.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>1230.557</td>\n",
       "      <td>855.6366</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 56 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        mmsi        date  cell_ll_la  cell_ll_lo   hours  fishing_ho flag_gfw  \\\n",
       "0  412439604  2020-09-19        -4.1       173.5  0.6422      0.1450      CHN   \n",
       "1  412439604  2020-03-02        -4.1       176.5  5.2555      4.3694      CHN   \n",
       "2  412439604  2020-03-02        -4.1       176.4  5.7683      4.3713      CHN   \n",
       "3  412439604  2020-03-02        -4.0       176.5  2.7400      2.3875      CHN   \n",
       "4  412439604  2020-03-02        -4.0       176.4  3.0694      2.1052      CHN   \n",
       "\n",
       "  flag_ais         vessel_cla                        vessel_rec  ...  \\\n",
       "0      CHN  tuna_purse_seines  FFA-35019|IMO-8996176|WCPFC-4431  ...   \n",
       "1      CHN  tuna_purse_seines  FFA-35019|IMO-8996176|WCPFC-4431  ...   \n",
       "2      CHN  tuna_purse_seines  FFA-35019|IMO-8996176|WCPFC-4431  ...   \n",
       "3      CHN  tuna_purse_seines  FFA-35019|IMO-8996176|WCPFC-4431  ...   \n",
       "4      CHN  tuna_purse_seines  FFA-35019|IMO-8996176|WCPFC-4431  ...   \n",
       "\n",
       "                    remarks_1 remarks_2  RFV_listed MSC_F_31245 MSC_F_31441  \\\n",
       "0  Linked to observer deaths.       NaN           Y           N           N   \n",
       "1  Linked to observer deaths.       NaN           Y           N           N   \n",
       "2  Linked to observer deaths.       NaN           Y           N           N   \n",
       "3  Linked to observer deaths.       NaN           Y           N           N   \n",
       "4  Linked to observer deaths.       NaN           Y           N           N   \n",
       "\n",
       "   MSC_F_30076  MSC_F_31362 (MSC_C_53088)  MSC_cert   ann_hrs ann_fsh_hrs  \n",
       "0            N                          Y         Y  1230.557    855.6366  \n",
       "1            N                          Y         Y  1230.557    855.6366  \n",
       "2            N                          Y         Y  1230.557    855.6366  \n",
       "3            N                          Y         Y  1230.557    855.6366  \n",
       "4            N                          Y         Y  1230.557    855.6366  \n",
       "\n",
       "[5 rows x 56 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create new columns 'annual_hours' and 'annual_fishing_hours'\n",
    "merged_df['ann_hrs'] = merged_df['hours_sum']\n",
    "merged_df['ann_fsh_hrs'] = merged_df['fishing_ho_sum']\n",
    "\n",
    "# Drop unnecessary columns\n",
    "merged_df = merged_df.drop(['hours_sum', 'fishing_ho_sum'], axis=1)\n",
    "\n",
    "# Checking new fields were added, and intermediate fields dropped\n",
    "merged_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "a293bfdd-f911-4dde-b432-ccf86db77a5e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "145"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checking that no vessels were lost through processing\n",
    "merged_df['imo'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "3ca15712-0101-4c9d-b7d3-c1a74b3cfb01",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21663"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(merged_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "3dd6ec74-9e9b-4038-90af-69f678ec34d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Exporting df as a csv\n",
    "merged_df.to_csv(\"KIR_2020_Beneficial.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "1187b4db-2acb-463c-948e-7341964e5962",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Converting 'merged_df' to a gdf\n",
    "merged_df = gpd.GeoDataFrame(merged_df, geometry=gpd.points_from_xy(merged_df.cell_ll_lo, merged_df.cell_ll_la), crs=\"EPSG:4326\") "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "9e3e4ef7-3bd4-4e42-9cd0-5801a18cb2c5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "geopandas.geodataframe.GeoDataFrame"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(merged_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "c498b7cc-5633-4cff-8044-beaead1b0209",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_343/2489858186.py:2: UserWarning: Column names longer than 10 characters will be truncated when saved to ESRI Shapefile.\n",
      "  merged_df.to_file(\"KIR_2020_Beneficial.shp\")\n"
     ]
    }
   ],
   "source": [
    "# Exporting final effort dataset with beneficial ownership info and annual effort to shapefile\n",
    "merged_df.to_file(\"KIR_2020_Beneficial.shp\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6222954b-cb9b-4d29-9d90-05836e5de9cc",
   "metadata": {},
   "source": [
    "# Pulling final list of active vessels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c1d907d9-b653-4c5a-b281-85c765de04de",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('KIR_2020_Beneficial.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6f86366c-a4ca-4d22-b27d-434f5540a494",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop duplicate rows based on the \"imo\" column\n",
    "df_unique = df.drop_duplicates(subset=['imo'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "82c44a0b-ce6f-4bb9-a656-4bdf73e4046b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "145"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_unique)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "40fb1a5c-5b5f-480d-865a-2a51900db878",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Unnamed: 0', 'mmsi', 'date', 'cell_ll_la', 'cell_ll_lo', 'hours', 'fishing_ho', 'flag_gfw', 'flag_ais', 'vessel_cla', 'vessel_rec', 'n_shipname', 'n_callsign', 'imo', 'flag', 'geartype', 'length_m', 'tonnage_gt', 'engine_pow', 'owner', 'owner_flag', 'source_cod', 'geometry', 'owner_gfw', 'owner_address', 'owner_source', 'corporate_actor', 'corporate_actor_source_1', 'corporate_actor_source_2', 'corporate_actor_source_3', 'corporate_actor_source_4', 'corporate_actor_source_5', 'beneficial_owner', 'beneficial_owner_flag', 'subsidiaries_actor', 'subsidiaries_parent', 'SA_actor', 'SA_parent', 'subsidiaries_and_affiliates', 'beneficial_owner_and_subsidiaries_source_1', 'benficial_owner_and_subsidiaries_source_2', 'beneficial_owner_and_subsidiaries_source_3', 'beneficial_owner_and_subsidiaries_source_4', 'beneficial_owner_and_subsidiaries_source_5', 'beneficial_owner_and_subsidiaries_source_6', 'beneficial_owner_and_subsidiaries_source_7', 'beneficial_owner_and_subsidiaries_source_8', 'remarks_1', 'remarks_2', 'RFV_listed', 'MSC_F_31245', 'MSC_F_31441', 'MSC_F_30076', 'MSC_F_31362 (MSC_C_53088)', 'MSC_cert', 'ann_hrs', 'ann_fsh_hrs']\n"
     ]
    }
   ],
   "source": [
    "print(df_unique.columns.tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "40608ac7-7d11-4e0e-bb2a-d4712862ceed",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Output Excel file path\n",
    "output_excel_file = \"/home/jovyan/PNACaseStudy/KIR_2020_ActiveVessels.xlsx\" \n",
    "\n",
    "# List of column names to remove\n",
    "columns_to_remove = [\"date\", \"cell_ll_la\", \"cell_ll_lo\", \"n_callsign\", \"vessel_cla\", \"geartype\", \"geometry\", \"hours\", \"fishing_ho\"] \n",
    "\n",
    "# Remove the specified columns from the GeoDataFrame\n",
    "df_unique = df_unique.drop(columns=columns_to_remove)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "2e0c6734-bc42-458c-8b4d-426ac53c2437",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Unnamed: 0', 'mmsi', 'flag_gfw', 'flag_ais', 'vessel_rec', 'n_shipname', 'imo', 'flag', 'length_m', 'tonnage_gt', 'engine_pow', 'owner', 'owner_flag', 'source_cod', 'owner_gfw', 'owner_address', 'owner_source', 'corporate_actor', 'corporate_actor_source_1', 'corporate_actor_source_2', 'corporate_actor_source_3', 'corporate_actor_source_4', 'corporate_actor_source_5', 'beneficial_owner', 'beneficial_owner_flag', 'subsidiaries_actor', 'subsidiaries_parent', 'SA_actor', 'SA_parent', 'subsidiaries_and_affiliates', 'beneficial_owner_and_subsidiaries_source_1', 'benficial_owner_and_subsidiaries_source_2', 'beneficial_owner_and_subsidiaries_source_3', 'beneficial_owner_and_subsidiaries_source_4', 'beneficial_owner_and_subsidiaries_source_5', 'beneficial_owner_and_subsidiaries_source_6', 'beneficial_owner_and_subsidiaries_source_7', 'beneficial_owner_and_subsidiaries_source_8', 'remarks_1', 'remarks_2', 'RFV_listed', 'MSC_F_31245', 'MSC_F_31441', 'MSC_F_30076', 'MSC_F_31362 (MSC_C_53088)', 'MSC_cert', 'ann_hrs', 'ann_fsh_hrs']\n"
     ]
    }
   ],
   "source": [
    "print(df_unique.columns.tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "78598cc7-762e-4af1-b96b-faecef0ce708",
   "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>Unnamed: 0</th>\n",
       "      <th>mmsi</th>\n",
       "      <th>flag_gfw</th>\n",
       "      <th>flag_ais</th>\n",
       "      <th>vessel_rec</th>\n",
       "      <th>n_shipname</th>\n",
       "      <th>imo</th>\n",
       "      <th>flag</th>\n",
       "      <th>length_m</th>\n",
       "      <th>tonnage_gt</th>\n",
       "      <th>...</th>\n",
       "      <th>remarks_1</th>\n",
       "      <th>remarks_2</th>\n",
       "      <th>RFV_listed</th>\n",
       "      <th>MSC_F_31245</th>\n",
       "      <th>MSC_F_31441</th>\n",
       "      <th>MSC_F_30076</th>\n",
       "      <th>MSC_F_31362 (MSC_C_53088)</th>\n",
       "      <th>MSC_cert</th>\n",
       "      <th>ann_hrs</th>\n",
       "      <th>ann_fsh_hrs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>412439604</td>\n",
       "      <td>CHN</td>\n",
       "      <td>CHN</td>\n",
       "      <td>FFA-35019|IMO-8996176|WCPFC-4431</td>\n",
       "      <td>FENGXIANG818</td>\n",
       "      <td>8996176.0</td>\n",
       "      <td>CHN</td>\n",
       "      <td>67.1</td>\n",
       "      <td>1152.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Linked to observer deaths.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>1230.5570</td>\n",
       "      <td>855.6366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278</th>\n",
       "      <td>278</td>\n",
       "      <td>544124120</td>\n",
       "      <td>NRU</td>\n",
       "      <td>NRU</td>\n",
       "      <td>FFA-35772|IMO-9417397|USA-1214250|WCPFC-4611</td>\n",
       "      <td>CENTURYPRIDE</td>\n",
       "      <td>9417397.0</td>\n",
       "      <td>NRU</td>\n",
       "      <td>69.8</td>\n",
       "      <td>1416.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Tsai Chen Hao (or Howard Tsai) is the operatio...</td>\n",
       "      <td>Connection to Konstantinos Barkas and South Pa...</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>943.3182</td>\n",
       "      <td>623.1040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>484</th>\n",
       "      <td>484</td>\n",
       "      <td>412371222</td>\n",
       "      <td>CHN</td>\n",
       "      <td>CHN</td>\n",
       "      <td>FFA-36739|IMO-9808687|WCPFC-11438</td>\n",
       "      <td>JINHUI58</td>\n",
       "      <td>9808687.0</td>\n",
       "      <td>CHN</td>\n",
       "      <td>71.3</td>\n",
       "      <td>1782.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Available information on website changes depen...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>1407.1282</td>\n",
       "      <td>908.6893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>821</th>\n",
       "      <td>821</td>\n",
       "      <td>538060008</td>\n",
       "      <td>MHL</td>\n",
       "      <td>MHL</td>\n",
       "      <td>FFA-35339|IMO-9349899|WCPFC-5143</td>\n",
       "      <td>KOOS102</td>\n",
       "      <td>9349899.0</td>\n",
       "      <td>MHL</td>\n",
       "      <td>68.8</td>\n",
       "      <td>1152.0</td>\n",
       "      <td>...</td>\n",
       "      <td>\"MIMRA has a joint venture with Koo's Fishing ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>294.5679</td>\n",
       "      <td>209.8904</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>934</th>\n",
       "      <td>934</td>\n",
       "      <td>416253900</td>\n",
       "      <td>TWN</td>\n",
       "      <td>TWN</td>\n",
       "      <td>FFA-36646|IMO-9764271|WCPFC-11247</td>\n",
       "      <td>FONGKUO688</td>\n",
       "      <td>9764271.0</td>\n",
       "      <td>TWN</td>\n",
       "      <td>71.8</td>\n",
       "      <td>1369.8</td>\n",
       "      <td>...</td>\n",
       "      <td>Vessel name: changed from KWILA 888, checked w...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>1139.3417</td>\n",
       "      <td>815.2734</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 48 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Unnamed: 0       mmsi flag_gfw flag_ais  \\\n",
       "0             0  412439604      CHN      CHN   \n",
       "278         278  544124120      NRU      NRU   \n",
       "484         484  412371222      CHN      CHN   \n",
       "821         821  538060008      MHL      MHL   \n",
       "934         934  416253900      TWN      TWN   \n",
       "\n",
       "                                       vessel_rec    n_shipname        imo  \\\n",
       "0                FFA-35019|IMO-8996176|WCPFC-4431  FENGXIANG818  8996176.0   \n",
       "278  FFA-35772|IMO-9417397|USA-1214250|WCPFC-4611  CENTURYPRIDE  9417397.0   \n",
       "484             FFA-36739|IMO-9808687|WCPFC-11438      JINHUI58  9808687.0   \n",
       "821              FFA-35339|IMO-9349899|WCPFC-5143       KOOS102  9349899.0   \n",
       "934             FFA-36646|IMO-9764271|WCPFC-11247    FONGKUO688  9764271.0   \n",
       "\n",
       "    flag  length_m  tonnage_gt  ...  \\\n",
       "0    CHN      67.1      1152.0  ...   \n",
       "278  NRU      69.8      1416.0  ...   \n",
       "484  CHN      71.3      1782.0  ...   \n",
       "821  MHL      68.8      1152.0  ...   \n",
       "934  TWN      71.8      1369.8  ...   \n",
       "\n",
       "                                             remarks_1  \\\n",
       "0                           Linked to observer deaths.   \n",
       "278  Tsai Chen Hao (or Howard Tsai) is the operatio...   \n",
       "484  Available information on website changes depen...   \n",
       "821  \"MIMRA has a joint venture with Koo's Fishing ...   \n",
       "934  Vessel name: changed from KWILA 888, checked w...   \n",
       "\n",
       "                                             remarks_2 RFV_listed MSC_F_31245  \\\n",
       "0                                                  NaN          Y           N   \n",
       "278  Connection to Konstantinos Barkas and South Pa...          Y           N   \n",
       "484                                                NaN          Y           N   \n",
       "821                                                NaN          Y           N   \n",
       "934                                                NaN          Y           N   \n",
       "\n",
       "    MSC_F_31441 MSC_F_30076 MSC_F_31362 (MSC_C_53088) MSC_cert    ann_hrs  \\\n",
       "0             N           N                         Y        Y  1230.5570   \n",
       "278           N           N                         Y        Y   943.3182   \n",
       "484           N           N                         Y        Y  1407.1282   \n",
       "821           N           N                         Y        Y   294.5679   \n",
       "934           N           N                         Y        Y  1139.3417   \n",
       "\n",
       "    ann_fsh_hrs  \n",
       "0      855.6366  \n",
       "278    623.1040  \n",
       "484    908.6893  \n",
       "821    209.8904  \n",
       "934    815.2734  \n",
       "\n",
       "[5 rows x 48 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_unique.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "cf069292-f176-4d0f-9fd6-3ca5f7cd9307",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Excel file with filtered data saved successfully.\n"
     ]
    }
   ],
   "source": [
    "# Save the filtered GeoDataFrame to an Excel file\n",
    "output_sheet_name = \"KIR_2020_ActiveVessels\"\n",
    "df_unique.to_excel(output_excel_file, sheet_name=output_sheet_name, index=False)\n",
    "\n",
    "print(\"Excel file with filtered data saved successfully.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bdf86df7-7115-4ecd-b4bf-e96dd1dc1991",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}