Data Preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5ed90f00-24a0-4c34-9313-583502869fad",
   "metadata": {},
   "source": [
    "# Data Preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "33bceba6-6702-4946-b11d-31af3f28c160",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load required packages\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "377be733-a7e2-4bd0-a067-3e22f47bcfe5",
   "metadata": {},
   "source": [
    "### Load Data Set\n",
    "\n",
    "This data set was downloaded from the Convention on International Trade in Endangered Species of Wild Fauna and Flora (CITES) website, available through https://trade.cites.org/, on 31 May 2024. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "9cf2e037-541e-4277-8dad-d69b4cc684ce",
   "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>Year</th>\n",
       "      <th>App.</th>\n",
       "      <th>Taxon</th>\n",
       "      <th>Class</th>\n",
       "      <th>Order</th>\n",
       "      <th>Family</th>\n",
       "      <th>Genus</th>\n",
       "      <th>Importer</th>\n",
       "      <th>Exporter</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Importer reported quantity</th>\n",
       "      <th>Exporter reported quantity</th>\n",
       "      <th>Term</th>\n",
       "      <th>Unit</th>\n",
       "      <th>Purpose</th>\n",
       "      <th>Source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1977</td>\n",
       "      <td>II</td>\n",
       "      <td>Manis javanica</td>\n",
       "      <td>Mammalia</td>\n",
       "      <td>Pholidota</td>\n",
       "      <td>Manidae</td>\n",
       "      <td>Manis</td>\n",
       "      <td>FR</td>\n",
       "      <td>GB</td>\n",
       "      <td>XX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>skins</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1977</td>\n",
       "      <td>II</td>\n",
       "      <td>Manis javanica</td>\n",
       "      <td>Mammalia</td>\n",
       "      <td>Pholidota</td>\n",
       "      <td>Manidae</td>\n",
       "      <td>Manis</td>\n",
       "      <td>US</td>\n",
       "      <td>ES</td>\n",
       "      <td>XX</td>\n",
       "      <td>215.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>shoes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1977</td>\n",
       "      <td>II</td>\n",
       "      <td>Manis javanica</td>\n",
       "      <td>Mammalia</td>\n",
       "      <td>Pholidota</td>\n",
       "      <td>Manidae</td>\n",
       "      <td>Manis</td>\n",
       "      <td>US</td>\n",
       "      <td>FR</td>\n",
       "      <td>ID</td>\n",
       "      <td>NaN</td>\n",
       "      <td>444.0</td>\n",
       "      <td>skins</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year App.           Taxon     Class      Order   Family  Genus Importer  \\\n",
       "0  1977   II  Manis javanica  Mammalia  Pholidota  Manidae  Manis       FR   \n",
       "1  1977   II  Manis javanica  Mammalia  Pholidota  Manidae  Manis       US   \n",
       "2  1977   II  Manis javanica  Mammalia  Pholidota  Manidae  Manis       US   \n",
       "\n",
       "  Exporter Origin  Importer reported quantity  Exporter reported quantity  \\\n",
       "0       GB     XX                         NaN                         4.0   \n",
       "1       ES     XX                       215.0                         NaN   \n",
       "2       FR     ID                         NaN                       444.0   \n",
       "\n",
       "    Term Unit Purpose Source  \n",
       "0  skins  NaN     NaN    NaN  \n",
       "1  shoes  NaN     NaN    NaN  \n",
       "2  skins  NaN     NaN    NaN  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Import original data set\n",
    "CITES_full = pd.read_csv(\"Datasets/CITES31May.csv\", sep = \";\")\n",
    "\n",
    "# Check dataset \n",
    "CITES_full.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b63d6c0-ca0a-4d0a-afaf-ca1d9aa78eaf",
   "metadata": {},
   "source": [
    "Check that the DataFrame has been loaded correctly, i.e. with 16 named columns from `Year` to `Source`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb6c4406-1880-4bf0-a399-0cac608754b7",
   "metadata": {},
   "source": [
    "### Remove Unnecessary Variables\n",
    "\n",
    "Several columns in the original data set will not be of use for this project, so we remove them now."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "c8a04afe-30e7-4074-a86f-faaaefc955c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove unnecessary variables from CITES data\n",
    "CITES = CITES_full.drop(['App.', 'Class', 'Order', 'Family', 'Genus', 'Purpose', 'Source'], axis = 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80bd8fa1-e90d-4499-92c6-2a6d1d80f2f0",
   "metadata": {},
   "source": [
    "### Select African and Asian Exporters\n",
    "\n",
    "Pangolins only occur naturally in Africa and certain parts of Asia (Rotich, 2018), and are incredibly difficult to breed in captivity (Environmental Investigation Agency, 2024).  We make the assumption that all `Exporter` countries that are not in regions where pangolins occur naturally have imported their stock from elsewhere, and hence we remove these transactions to avoid double-counting the items traded.  See \"Appendix A - Country Codes\" for a list of country codes and their regions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "874f18c0-bb94-495c-b0ca-7e331deb2f9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create lists of African and Asian countries\n",
    "African_countries = [\"ZA\", \"TG\", \"CG\", \"CF\", \"CM\", \"LR\", \"NG\", \"TZ\", \"BJ\", \"DJ\", \"CI\", \"GN\", \"GQ\", \n",
    "                     \"GA\", \"CD\", \"KE\", \"GH\", \"UG\", \"BI\", \"BW\", \"SZ\", \"SN\", \"MZ\"]\n",
    "Asian_countries = [\"CN\", \"JP\", \"TH\", \"SG\", \"ID\", \"MY\", \"TW\", \"PH\", \"LA\", \"HK\", \"KR\", \"MO\", \"VN\", \n",
    "                   \"KH\", \"PK\", \"LK\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "a53c33fc-4b5a-4d81-b40e-0df6a1cc22f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove entries for non-African and non-Asian countries, but keep those with no recorded exporter\n",
    "CITES = CITES[CITES['Exporter'].isin(African_countries + Asian_countries + ['XX', np.nan])]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9639fffe-9d06-40ce-b813-43726808d711",
   "metadata": {},
   "source": [
    "If you are using this code for a data set more recent than the one provided for this project, you may need to update the lists of African and Asian countries based on what appears in the `Exporter` column.  Refer to the article by V. Rotich for more information on which countries contain wild pangolin populations."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e22388c6-44b9-42cd-8139-bb0a4fa895ec",
   "metadata": {},
   "source": [
    "### Remove Double-Counted Transactions\n",
    "\n",
    "According to the CITES Database Guidelines for Use (CITES Secretariat and UNEP-WCMC, 2022), there may be cases where one transaction is recorded twice in the data set - once using the importer's report and once using the exporter's report.  We must remove these instances to avoid counting the same transactions twice."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "92a08d95-28f9-4fa0-825a-22e52047bdbc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create index for which rows to remove\n",
    "repeat_index = []\n",
    "\n",
    "# Loop to find repeated transactions\n",
    "for i in range(len(CITES)-1):\n",
    "    # Extract required elements from the data set\n",
    "    importer_now = CITES['Importer'].iloc[i]\n",
    "    importer_next = CITES['Importer'].iloc[i+1]\n",
    "    exporter_now = CITES['Exporter'].iloc[i]\n",
    "    exporter_next = CITES['Exporter'].iloc[i+1]\n",
    "    importer_quantity_now = CITES['Importer reported quantity'].iloc[i]\n",
    "    importer_quantity_next = CITES['Importer reported quantity'].iloc[i+1]\n",
    "    exporter_quantity_now = CITES['Exporter reported quantity'].iloc[i]\n",
    "    exporter_quantity_next = CITES['Exporter reported quantity'].iloc[i+1]\n",
    "    \n",
    "    # Check for repeated importer and exporter\n",
    "    if (importer_now == importer_next) & (exporter_now == exporter_next ):\n",
    "        # Check for repeated quantities\n",
    "        if (importer_quantity_now == exporter_quantity_next) | (exporter_quantity_now == importer_quantity_next):\n",
    "            repeat_index.append(i)\n",
    "\n",
    "    # Check for resales\n",
    "    if (exporter_now == importer_next) & (CITES['Origin'].iloc[i] == exporter_next):\n",
    "        if (importer_quantity_now == exporter_quantity_next) | (exporter_quantity_now == importer_quantity_next):\n",
    "            repeat_index.append(i)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f3c4620-d411-4c8c-a1dd-7fbbc2fac7a3",
   "metadata": {},
   "source": [
    "I further inspected the data and found a few anomalies.  There is no way to code a function to find these so I will remove them manually."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "a9919dc6-a432-4a2b-9332-7e05c480c8ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add the manual indices to repeat_index\n",
    "repeat_index = repeat_index + [379, 412, 451, 485, 487, 493, 593, 663, 1014, 1029, 1073, 1095, 1130]\n",
    "repeat_index = sorted(repeat_index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "5625ea63-51ab-4657-8a1e-ff2dd6bf63e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove the selected rows from the dataframe\n",
    "CITES.drop(CITES.index[repeat_index], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc2b496a-ad98-4a4e-896e-b945f0fa51f6",
   "metadata": {},
   "source": [
    "Finally, we combine importer and exporter quantities to get one `Quantity` value per transaction.  According to the CITES Guidelines (CITES Secretariat and UNEP-WCMC, 2022) the exporter reported quantity is generally more reliable, so we will use that value where it is available.  Otherwise, we will use the importer reported quantity."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "2c252d71-a14e-4ee3-a702-185ee1d3f829",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Copy Exporter reported quantity for Quantity\n",
    "CITES['Quantity'] = CITES['Exporter reported quantity']\n",
    "\n",
    "# Find column number of the 'Quantity' column\n",
    "quan_ind = CITES.columns.get_loc('Quantity')\n",
    "\n",
    "# Fill in NaN Quantity values with Importer reported quantity\n",
    "for i in range(len(CITES)):\n",
    "    if np.isnan(CITES.iloc[i,quan_ind]):\n",
    "        CITES.iloc[i,quan_ind] = CITES['Importer reported quantity'].iloc[i]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "d5e07a87-6f53-4a01-8491-00dd84a5a23f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove the Exporter and Importer reported quantity columns\n",
    "CITES = CITES.drop(['Exporter reported quantity', 'Importer reported quantity'], axis = 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54f7d8c0-c129-4fa5-84d6-b59ddd013534",
   "metadata": {},
   "source": [
    "### Indicator for African or Asian Species\n",
    "\n",
    "Some entries in the data set list the `Taxon` as \"Manidae spp.\" or \"Manis spp.\".  These are general terms, and are sometimes used intentionally by exporters to dodge regulations.  In this case we will assign the pangolins to the region of their `Origin` if it is listed, and if not then we assign it to the `Exporter`'s region."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "8bf5ac82-ccc2-45aa-bc77-c14cf7b0fe35",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lists of species per region\n",
    "African_species = [\"Manis tetradactyla\", \"Manis tricuspis\", \"Manis gigantea\", \"Manis temminckii\"]\n",
    "Asian_species = [\"Manis crassicaudata\", \"Manis javanica\", \"Manis culionensis\", \"Manis pentadactyla\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "f51db018-338f-4fdd-ba3d-04b9c06a33f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create empty variable for Region\n",
    "CITES['Region'] = ['Other']*len(CITES)\n",
    "reg_ind = CITES.columns.get_loc('Region')\n",
    "\n",
    "# Loop through to assign Region\n",
    "for i in range(len(CITES)):\n",
    "    if CITES['Taxon'].iloc[i] in African_species: \n",
    "        CITES.iloc[i,reg_ind] = \"African\"\n",
    "    elif CITES['Taxon'].iloc[i] in Asian_species: \n",
    "        CITES.iloc[i,reg_ind] = \"Asian\"\n",
    "    elif CITES['Origin'].iloc[i] in African_countries: \n",
    "        CITES.iloc[i,reg_ind] = \"African\"\n",
    "    elif CITES['Origin'].iloc[i] in Asian_countries: \n",
    "        CITES.iloc[i,reg_ind] = \"Asian\"\n",
    "    elif CITES['Exporter'].iloc[i] in African_countries: \n",
    "        CITES.iloc[i,reg_ind] = \"African\"\n",
    "    elif CITES['Exporter'].iloc[i] in Asian_countries: \n",
    "        CITES.iloc[i,reg_ind] = \"Asian\"\n",
    "\n",
    "# Convert to factor\n",
    "CITES['Region'] = CITES['Region'].astype('category')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25c6d437-e70e-4ac7-994a-8aefc77bdaa4",
   "metadata": {},
   "source": [
    "### Calculate Number of Pangolins per Transaction\n",
    "\n",
    "Each row in the data set lists a `Term`, detailing the type of product sold (e.g. skins, meat, specimens) and a `Unit` (e.g. kg, number of specimens, cartons).  We now convert these to the equivalent number of pangolins required to produce each `Term`.  See \"Appendix B - Term Classification and Conversion\" for more details."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "1734fb1b-3984-455a-9cb8-449f19235aff",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set up a list for all the terms where 1 unit equates to 1 pangolin\n",
    "direct_equiv = [\"bodies\", \"carvings\", \"claws\", \"leather\", \"leather items\", \"leather products (large)\", \n",
    "                \"live\", \"shoes\", \"skeletons\", \"skulls\", \"tails\", \"trophies\"]\n",
    "\n",
    "# Create data frame with the average weight for each species of pangolin\n",
    "weights = {'Species': [\"Manis gigantea\", \"Manis crassicaudata\", \"Manis temminckii\", \"Manis javanica\", \n",
    "                       \"Manis pentadactyla\", \"Manis tetradactyla\", \"Manis culionensis\", \n",
    "                       \"Manis tricuspis\", \"Manis spp.\", \"Manidae spp.\"], \n",
    "           'Weight kg': [33, 13, 12, 4.9, 3.6, 2.7, 2.1, 1.5, 9.1, 9.1]}\n",
    "weights_df = pd.DataFrame(weights)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "510d3624-37a7-4303-a673-070591e399eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create column in CITES for number of pangolins\n",
    "CITES['Number'] = [0.0]*len(CITES)\n",
    "num_ind = CITES.columns.get_loc('Number')\n",
    "\n",
    "# Create column in CITES for kg (to be removed once we have populated the Number column)\n",
    "CITES['kg'] = [0.0]*len(CITES)\n",
    "kg_ind = CITES.columns.get_loc('kg')\n",
    "\n",
    "# Fill in kg values for each entry\n",
    "for i in range(len(CITES)):\n",
    "    for j in range(len(weights_df)):\n",
    "        if CITES['Taxon'].iloc[i] == weights_df['Species'].iloc[j]:\n",
    "            CITES.iloc[i,kg_ind] = CITES['Quantity'].iloc[i]/weights_df['Weight kg'].iloc[j]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed28da21-6465-4f23-8434-174d550a5421",
   "metadata": {},
   "source": [
    "Due to the wide variety of combinations in Term and Unit, we have a slightly complicated process for converting these to the overall number of whole pangolins traded.  This is detailed below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "4085b709-8911-40c9-ab0e-c4347061d78b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find Number for each entry\n",
    "for i in range(len(CITES)):\n",
    "    # Extract relevant values from the dataset\n",
    "    i_term = CITES['Term'].iloc[i]\n",
    "    i_unit = CITES['Unit'].iloc[i]\n",
    "    i_quantity = CITES['Quantity'].iloc[i]\n",
    "    i_kg = CITES['kg'].iloc[i]\n",
    "\n",
    "    # Check for directly equivalent quantities, by term or unit or both\n",
    "    if (i_term in direct_equiv) | (i_unit in ['Number of specimens', 'pairs', 'flasks']) | (pd.isnull(i_unit) & \n",
    "            (i_term in ['medicine', 'skins', 'specimens', 'unspecified'])): \n",
    "        CITES.iloc[i,num_ind] = i_quantity    \n",
    "\n",
    "    # For units measured in kg, g, and ml according to total bodyweight of pangolin\n",
    "    elif i_term in ['derivatives', 'medicine', 'powder', 'specimens', 'unspecified']:  \n",
    "        if (i_unit == 'kg') | (pd.isnull(i_unit)):\n",
    "            CITES.iloc[i,num_ind] = i_kg\n",
    "        elif i_unit in ['g', 'ml']:\n",
    "            CITES.iloc[i,num_ind] = i_kg/1000\n",
    "        elif i_unit in ['boxes', 'cartons']:\n",
    "            CITES.iloc[i,num_ind] = 20*i_kg\n",
    "\n",
    "    # For 'scales', 'skin pieces', and 'skins'\n",
    "    elif i_term in ['scales', 'skin pieces', 'skins']:\n",
    "        if (i_unit == 'kg') | pd.isnull(i_unit): \n",
    "            CITES.iloc[i,num_ind] = i_kg/0.2\n",
    "        elif i_unit == 'g':\n",
    "            CITES.iloc[i,num_ind] = i_kg/200\n",
    "        elif i_unit == 'cartons':\n",
    "            CITES.iloc[i,num_ind] = 100*i_kg\n",
    "        elif i_unit in ['m', 'm2']:\n",
    "            CITES.iloc[i,num_ind] = i_quantity/1.2\n",
    "        elif i_unit == 'cm':\n",
    "            CITES.iloc[i,num_ind] = i_quantity/120\n",
    "\n",
    "    # For 'meat'\n",
    "    elif i_term == 'meat':\n",
    "        if i_unit == 'g': CITES.iloc[i,num_ind] = i_kg/300\n",
    "        else: CITES.iloc[i,num_ind] = i_kg/0.3\n",
    "\n",
    "    # For all 'feet'\n",
    "    elif i_term == 'feet': \n",
    "        CITES.iloc[i,num_ind] = i_quantity/4\n",
    "    \n",
    "    # For all 'leather products (small)' and 'fur product (small)'\n",
    "    elif i_term in ['leather products (small)', 'fur product (small)']: \n",
    "        CITES.iloc[i,num_ind] = i_quantity/2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0aa65f15-3ffb-4e17-b4af-072fe387e749",
   "metadata": {},
   "source": [
    "Now that we have all the conversions, we make one final assumption, which is that one cannot kill a fraction of a pangolin. We therefore will round all `Number` values up to the nearest whole number.  We remove the 'kg' variable as it is no longer needed.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "6ab72e66-c197-4db8-8e17-5d1cbfa14cbd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Round up all Number values\n",
    "CITES['Number'] = np.ceil(CITES['Number'])\n",
    "\n",
    "# Remove 'kg' column\n",
    "CITES = CITES.drop(['kg'], axis = 1) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71be8423-cad5-4663-bc1a-6a91071a49c4",
   "metadata": {},
   "source": [
    "### Grouping of Trade Purposes\n",
    "\n",
    "There are many different types of `Term` items listed, so I would like to group similar ones together to simplify the analysis.  For example, \"leather\" and \"leather items\" could be joined together into one group."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "218014aa-5d2d-4fbf-8b79-22d17f195936",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create lists for groupings\n",
    "groupnames = [\"whole\", \"trophy\", \"skins\", \"consumption\", \"derivatives\", \"medicine\"]\n",
    "whole = [\"bodies\", \"live\", \"specimens\"]\n",
    "trophy = [\"trophies\", \"carvings\", \"shoes\"]\n",
    "skins = [\"fur product (small)\", \"leather\", \"leather items\", \"leather products (large)\", \n",
    "         \"leather products (small)\", \"scales\", \"skin pieces\", \"skins\"]\n",
    "consumption = [\"meat\"]\n",
    "derivatives = [\"derivatives\", \"feet\", \"claws\", \"skeletons\", \"skulls\", \"tails\", \"unspecified\"]\n",
    "medicine = [\"medicine\", \"powder\"]\n",
    "all_groups = [whole, trophy, skins, consumption, derivatives, medicine]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "3bb1f243-6037-4847-a11e-ca203a69b1eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create new column in CITES to store the group name\n",
    "CITES['Purpose'] = ['h']*len(CITES)\n",
    "pur_ind = CITES.columns.get_loc('Purpose')\n",
    "\n",
    "# Loop through all Terms to group them according to purpose\n",
    "for i in range(len(CITES)):\n",
    "    for j in range(len(groupnames)):\n",
    "        if CITES['Term'].iloc[i] in all_groups[j]:\n",
    "            CITES.iloc[i, pur_ind] = groupnames[j]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "02e47105-1399-4bd7-8a21-f9bd9fb980f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reset index\n",
    "CITES = CITES.reset_index(drop = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "ac6d3191-cd93-4bbb-995e-5f2e5b08d891",
   "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>Year</th>\n",
       "      <th>Taxon</th>\n",
       "      <th>Importer</th>\n",
       "      <th>Exporter</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Term</th>\n",
       "      <th>Unit</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Region</th>\n",
       "      <th>Number</th>\n",
       "      <th>Purpose</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1977</td>\n",
       "      <td>Manis pentadactyla</td>\n",
       "      <td>AU</td>\n",
       "      <td>CN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>live</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Asian</td>\n",
       "      <td>2.0</td>\n",
       "      <td>whole</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1978</td>\n",
       "      <td>Manis crassicaudata</td>\n",
       "      <td>US</td>\n",
       "      <td>JP</td>\n",
       "      <td>XX</td>\n",
       "      <td>skins</td>\n",
       "      <td>NaN</td>\n",
       "      <td>298.0</td>\n",
       "      <td>Asian</td>\n",
       "      <td>298.0</td>\n",
       "      <td>skins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1978</td>\n",
       "      <td>Manis javanica</td>\n",
       "      <td>US</td>\n",
       "      <td>JP</td>\n",
       "      <td>XX</td>\n",
       "      <td>skins</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1016.0</td>\n",
       "      <td>Asian</td>\n",
       "      <td>1016.0</td>\n",
       "      <td>skins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1978</td>\n",
       "      <td>Manis javanica</td>\n",
       "      <td>US</td>\n",
       "      <td>TH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>live</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Asian</td>\n",
       "      <td>1.0</td>\n",
       "      <td>whole</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1978</td>\n",
       "      <td>Manis pentadactyla</td>\n",
       "      <td>AU</td>\n",
       "      <td>SG</td>\n",
       "      <td>NaN</td>\n",
       "      <td>leather products (small)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Asian</td>\n",
       "      <td>1.0</td>\n",
       "      <td>skins</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year                Taxon Importer Exporter Origin  \\\n",
       "0  1977   Manis pentadactyla       AU       CN    NaN   \n",
       "1  1978  Manis crassicaudata       US       JP     XX   \n",
       "2  1978       Manis javanica       US       JP     XX   \n",
       "3  1978       Manis javanica       US       TH    NaN   \n",
       "4  1978   Manis pentadactyla       AU       SG    NaN   \n",
       "\n",
       "                       Term Unit  Quantity Region  Number Purpose  \n",
       "0                      live  NaN       2.0  Asian     2.0   whole  \n",
       "1                     skins  NaN     298.0  Asian   298.0   skins  \n",
       "2                     skins  NaN    1016.0  Asian  1016.0   skins  \n",
       "3                      live  NaN       1.0  Asian     1.0   whole  \n",
       "4  leather products (small)  NaN       1.0  Asian     1.0   skins  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Final check\n",
    "CITES.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "659303c8-e641-4610-870c-a638197475d9",
   "metadata": {},
   "source": [
    "Finally we have our 'tidied' dataset, which contains columns indicating the type, unit, and quantity of pangolin products traded; the importer, exporter, and origin; the species and region of origin of each pangolin; the number of whole pangolins involved per trade; and a broad grouping of different purposes for pangolin products.  We are now ready to export this data set and begin our analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "a3cc1ea3-f0ad-4351-82ec-f9a97fe48210",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export the tidied data set\n",
    "CITES.to_csv('Datasets/CITES.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd22b00c-81ab-4ea1-afa1-f1575df0d25e",
   "metadata": {},
   "source": [
    "### References\n",
    "\n",
    "1. CITES Secretariat and UNEP-WCMC (2022). A guide to using the CITES Trade Database. \n",
    "Version 9. Geneva, Switzerland, and Cambridge, UK. https://trade.cites.org/cites_trade_guidelines/en-CITES_Trade_Database_Guide.pdf\n",
    "2. Environmental Investigation Agency. (2024). Saving Pangolins from Extinction. Retrieved from Environmental Investigation Agency: https://eia-international.org/wildlife/helping-pangolins/saving-pangolins-from-extinction/\n",
    "3. Rotich, V. (2018, September 04). Where Do Pangolins Live. Retrieved from WorldAtlas: https://www.worldatlas.com/articles/what-is-a-pangolin.html"
   ]
  }
 ],
 "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.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}