Kiribati2020_BeneficialOwnership.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "eec9f35e-ef88-4e87-9dfe-8463eca8c25c",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Pairing down the KIR effort database to assess how many vessels are already identified"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "2a5c921c-8a5a-4465-95cc-6f69a8df8eb3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/home/jovyan/PNACaseStudy'"
      ]
     },
     "execution_count": 48,
     "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": 49,
   "id": "2eeae940-a0b4-4534-9782-4e1f413ca4f4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: openpyxl in /srv/conda/envs/notebook/lib/python3.10/site-packages (3.1.2)\n",
      "Requirement already satisfied: et-xmlfile in /srv/conda/envs/notebook/lib/python3.10/site-packages (from openpyxl) (1.1.0)\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install openpyxl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "a622d59b-1acd-4609-8858-df6318f843ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_directory = '/home/jovyan/PNACaseStudy'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "2a75e42d-8743-47d7-9297-bb0b302e2a1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "effort = gpd.read_file('KIR_2020_identity.shp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "466e1d45-ceb9-44f7-95f2-543c4af12155",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        mmsi        date  cell_ll_la  cell_ll_lo   hours  fishing_ho flag_gfw  \\\n",
      "0  440823000  2020-10-02        -4.1       173.6  0.4080       0.000      KOR   \n",
      "1  416253900  2020-07-29        -4.1       173.6  0.0827       0.000      TWN   \n",
      "2  412439604  2020-09-19        -4.1       173.5  0.6422       0.145      CHN   \n",
      "3  554126120  2020-09-15        -4.1       173.6  0.3547       0.000      NRU   \n",
      "4  416253900  2020-09-30        -4.1       174.6  0.6497       0.000      TWN   \n",
      "\n",
      "  flag_ais         vessel_cla                         vessel_rec  ...  \\\n",
      "0      KOR  tuna_purse_seines   FFA-24968|IMO-8812227|WCPFC-2240  ...   \n",
      "1      TWN  tuna_purse_seines  FFA-36646|IMO-9764271|WCPFC-11247  ...   \n",
      "2      CHN  tuna_purse_seines   FFA-35019|IMO-8996176|WCPFC-4431  ...   \n",
      "3     None  tuna_purse_seines  FFA-36609|IMO-9746281|WCPFC-11164  ...   \n",
      "4      TWN  tuna_purse_seines  FFA-36646|IMO-9764271|WCPFC-11247  ...   \n",
      "\n",
      "         imo flag           geartype length_m tonnage_gt  engine_pow  \\\n",
      "0  8812227.0  KOR  tuna_purse_seines     73.3     1349.1      2706.9   \n",
      "1  9764271.0  TWN  tuna_purse_seines     71.8     1369.8      2647.0   \n",
      "2  8996176.0  CHN  tuna_purse_seines     67.1     1152.0      2574.0   \n",
      "3  9746281.0  NRU  tuna_purse_seines     71.8     1350.0      2473.0   \n",
      "4  9764271.0  TWN  tuna_purse_seines     71.8     1369.8      2647.0   \n",
      "\n",
      "                           owner  owner_flag       source_cod  \\\n",
      "0             DONGWON ENTERPRISE         KOR   RESEARCH-PAPER   \n",
      "1             FONG KUO FISHERIES         TWN  FFA, IMO, WCPFC   \n",
      "2  NINGBO YONGFA OCEAN FISHERIES         CHN  FFA, IMO, WCPFC   \n",
      "3                           None         TWN              TWN   \n",
      "4             FONG KUO FISHERIES         TWN  FFA, IMO, WCPFC   \n",
      "\n",
      "                     geometry  \n",
      "0  POINT (173.60000 -4.10000)  \n",
      "1  POINT (173.60000 -4.10000)  \n",
      "2  POINT (173.50000 -4.10000)  \n",
      "3  POINT (173.60000 -4.10000)  \n",
      "4  POINT (174.60000 -4.10000)  \n",
      "\n",
      "[5 rows x 22 columns]\n"
     ]
    }
   ],
   "source": [
    "print(effort.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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 the column names\n",
    "print(effort.columns.tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "33d1b1d9-bc86-4213-b3b7-d8b2cb296327",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "157\n"
     ]
    }
   ],
   "source": [
    "# Calculate the number of unique MMSI values\n",
    "unique_mmsi_count = effort[\"mmsi\"].nunique()\n",
    "print(unique_mmsi_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "f520f8cf-7ed7-429d-8e12-3e7cb9307058",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "154\n"
     ]
    }
   ],
   "source": [
    "# Calculate the number of unique IMO codes (cross-check MMSI)\n",
    "# This tells us that not every vessel has an mmsi AND imo\n",
    "unique_imo_count = effort[\"imo\"].nunique()\n",
    "print(unique_imo_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "e6f79b72-b29f-4aab-8f67-93e74cd71879",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Output Excel file path\n",
    "output_excel_file = \"/home/jovyan/PNACaseStudy/KIR_2020_VesselList.xlsx\" \n",
    "\n",
    "# List of column names to remove\n",
    "columns_to_remove = [\"date\", \"cell_ll_la\", \"cell_ll_lo\", \"flag_gfw\", \"flag_ais\", \"vessel_cla\", \"geometry\", \"vessel_rec\", \"n_callsign\", \"geartype\", \"hours\", \"fishing_ho\"] \n",
    "\n",
    "# Remove the specified columns from the GeoDataFrame\n",
    "filtered_gdf = effort.drop(columns=columns_to_remove)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "9374c6a0-af01-47a5-bb25-0fd645766118",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        mmsi    n_shipname        imo flag  length_m  tonnage_gt  engine_pow  \\\n",
      "0  440823000   OCEANMASTER  8812227.0  KOR      73.3      1349.1      2706.9   \n",
      "1  416253900    FONGKUO688  9764271.0  TWN      71.8      1369.8      2647.0   \n",
      "2  412439604  FENGXIANG818  8996176.0  CHN      67.1      1152.0      2574.0   \n",
      "3  554126120      NEWCANOE  9746281.0  NRU      71.8      1350.0      2473.0   \n",
      "4  416253900    FONGKUO688  9764271.0  TWN      71.8      1369.8      2647.0   \n",
      "\n",
      "                           owner owner_flag       source_cod  \n",
      "0             DONGWON ENTERPRISE        KOR   RESEARCH-PAPER  \n",
      "1             FONG KUO FISHERIES        TWN  FFA, IMO, WCPFC  \n",
      "2  NINGBO YONGFA OCEAN FISHERIES        CHN  FFA, IMO, WCPFC  \n",
      "3                           None        TWN              TWN  \n",
      "4             FONG KUO FISHERIES        TWN  FFA, IMO, WCPFC  \n"
     ]
    }
   ],
   "source": [
    "print(filtered_gdf.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "6e63188c-18d9-4fa7-b08d-93513d1f082e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "180871"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(filtered_gdf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "55a9cdc5-5356-4db5-b253-8eabb380bf1b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop duplicate rows based on the \"MMSI\" column\n",
    "df_unique = filtered_gdf.drop_duplicates(subset=[\"mmsi\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "c47e4b8d-5901-440e-baf8-e2981455b1fd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "157"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_unique)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "cf91587f-4315-4ea8-ad1f-a8a079d03a40",
   "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_VesselList\"\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": "e79bd803-78e4-48b3-8534-093d6a7b600b",
   "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
}