data-wrangling.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Project: Data Cleaning and Manipulation with Pandas\n",
    "\n",
    "# Dataset : Shark Attacks "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Importing libraries and Data exploration."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 1.1. We import the relevant libraries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import re\n",
    "import math"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 1.2. Then we load the data set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"/Users/Fabi/Documents/GitHub/data-ber-10-19/module-1_projects/pandas-project/your-code/GSAF5.csv\",encoding = \"ISO-8859-1\")\n",
    "\n",
    "#This will remove trailing spaces at the end of the column names\n",
    "\n",
    "df.columns = df.columns.str.rstrip()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 1.3. We explore the data set by looking its first rows. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Case Number</th>\n",
       "      <th>Date</th>\n",
       "      <th>Year</th>\n",
       "      <th>Type</th>\n",
       "      <th>Country</th>\n",
       "      <th>Area</th>\n",
       "      <th>Location</th>\n",
       "      <th>Activity</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>...</th>\n",
       "      <th>Species</th>\n",
       "      <th>Investigator or Source</th>\n",
       "      <th>pdf</th>\n",
       "      <th>href formula</th>\n",
       "      <th>href</th>\n",
       "      <th>Case Number.1</th>\n",
       "      <th>Case Number.2</th>\n",
       "      <th>original order</th>\n",
       "      <th>Unnamed: 22</th>\n",
       "      <th>Unnamed: 23</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016.09.18.c</td>\n",
       "      <td>18-Sep-16</td>\n",
       "      <td>2016</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>USA</td>\n",
       "      <td>Florida</td>\n",
       "      <td>New Smyrna Beach, Volusia County</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>male</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Orlando Sentinel, 9/19/2016</td>\n",
       "      <td>2016.09.18.c-NSB.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2016.09.18.c</td>\n",
       "      <td>2016.09.18.c</td>\n",
       "      <td>5993</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016.09.18.b</td>\n",
       "      <td>18-Sep-16</td>\n",
       "      <td>2016</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>USA</td>\n",
       "      <td>Florida</td>\n",
       "      <td>New Smyrna Beach, Volusia County</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>Chucky Luciano</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Orlando Sentinel, 9/19/2016</td>\n",
       "      <td>2016.09.18.b-Luciano.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2016.09.18.b</td>\n",
       "      <td>2016.09.18.b</td>\n",
       "      <td>5992</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016.09.18.a</td>\n",
       "      <td>18-Sep-16</td>\n",
       "      <td>2016</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>USA</td>\n",
       "      <td>Florida</td>\n",
       "      <td>New Smyrna Beach, Volusia County</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>male</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Orlando Sentinel, 9/19/2016</td>\n",
       "      <td>2016.09.18.a-NSB.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2016.09.18.a</td>\n",
       "      <td>2016.09.18.a</td>\n",
       "      <td>5991</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016.09.17</td>\n",
       "      <td>17-Sep-16</td>\n",
       "      <td>2016</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>AUSTRALIA</td>\n",
       "      <td>Victoria</td>\n",
       "      <td>Thirteenth Beach</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>Rory Angiolella</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>The Age, 9/18/2016</td>\n",
       "      <td>2016.09.17-Angiolella.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2016.09.17</td>\n",
       "      <td>2016.09.17</td>\n",
       "      <td>5990</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016.09.15</td>\n",
       "      <td>16-Sep-16</td>\n",
       "      <td>2016</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>AUSTRALIA</td>\n",
       "      <td>Victoria</td>\n",
       "      <td>Bells Beach</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>male</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>2 m shark</td>\n",
       "      <td>The Age, 9/16/2016</td>\n",
       "      <td>2016.09.16-BellsBeach.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2016.09.16</td>\n",
       "      <td>2016.09.15</td>\n",
       "      <td>5989</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Case Number       Date  Year        Type    Country      Area  \\\n",
       "0  2016.09.18.c  18-Sep-16  2016  Unprovoked        USA   Florida   \n",
       "1  2016.09.18.b  18-Sep-16  2016  Unprovoked        USA   Florida   \n",
       "2  2016.09.18.a  18-Sep-16  2016  Unprovoked        USA   Florida   \n",
       "3    2016.09.17  17-Sep-16  2016  Unprovoked  AUSTRALIA  Victoria   \n",
       "4    2016.09.15  16-Sep-16  2016  Unprovoked  AUSTRALIA  Victoria   \n",
       "\n",
       "                           Location Activity             Name Sex  ...  \\\n",
       "0  New Smyrna Beach, Volusia County  Surfing             male   M  ...   \n",
       "1  New Smyrna Beach, Volusia County  Surfing   Chucky Luciano   M  ...   \n",
       "2  New Smyrna Beach, Volusia County  Surfing             male   M  ...   \n",
       "3                  Thirteenth Beach  Surfing  Rory Angiolella   M  ...   \n",
       "4                       Bells Beach  Surfing             male   M  ...   \n",
       "\n",
       "     Species       Investigator or Source                        pdf  \\\n",
       "0        NaN  Orlando Sentinel, 9/19/2016       2016.09.18.c-NSB.pdf   \n",
       "1        NaN  Orlando Sentinel, 9/19/2016   2016.09.18.b-Luciano.pdf   \n",
       "2        NaN  Orlando Sentinel, 9/19/2016       2016.09.18.a-NSB.pdf   \n",
       "3        NaN           The Age, 9/18/2016  2016.09.17-Angiolella.pdf   \n",
       "4  2 m shark           The Age, 9/16/2016  2016.09.16-BellsBeach.pdf   \n",
       "\n",
       "                                        href formula  \\\n",
       "0  http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "1  http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "2  http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "3  http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "4  http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "\n",
       "                                                href Case Number.1  \\\n",
       "0  http://sharkattackfile.net/spreadsheets/pdf_di...  2016.09.18.c   \n",
       "1  http://sharkattackfile.net/spreadsheets/pdf_di...  2016.09.18.b   \n",
       "2  http://sharkattackfile.net/spreadsheets/pdf_di...  2016.09.18.a   \n",
       "3  http://sharkattackfile.net/spreadsheets/pdf_di...    2016.09.17   \n",
       "4  http://sharkattackfile.net/spreadsheets/pdf_di...    2016.09.16   \n",
       "\n",
       "  Case Number.2 original order Unnamed: 22 Unnamed: 23  \n",
       "0  2016.09.18.c           5993         NaN         NaN  \n",
       "1  2016.09.18.b           5992         NaN         NaN  \n",
       "2  2016.09.18.a           5991         NaN         NaN  \n",
       "3    2016.09.17           5990         NaN         NaN  \n",
       "4    2016.09.15           5989         NaN         NaN  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 1.4. We want to know as well how many rows and columns has the data set to further explore whether there are too many null columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5992, 24)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 1.5. Data types\n",
    "\n",
    "We want to know whether the type of data for each column is suitable or we sould change it. We see that is a must to change the data type for columns \"Age\" to integer or float and \"Date\" to timestamp."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Case Number               object\n",
       "Date                      object\n",
       "Year                       int64\n",
       "Type                      object\n",
       "Country                   object\n",
       "Area                      object\n",
       "Location                  object\n",
       "Activity                  object\n",
       "Name                      object\n",
       "Sex                       object\n",
       "Age                       object\n",
       "Injury                    object\n",
       "Fatal (Y/N)               object\n",
       "Time                      object\n",
       "Species                   object\n",
       "Investigator or Source    object\n",
       "pdf                       object\n",
       "href formula              object\n",
       "href                      object\n",
       "Case Number.1             object\n",
       "Case Number.2             object\n",
       "original order             int64\n",
       "Unnamed: 22               object\n",
       "Unnamed: 23               object\n",
       "dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Duplicated rows\n",
    "\n",
    "We don't have duplicated rows in our data frame:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Case Number</th>\n",
       "      <th>Date</th>\n",
       "      <th>Year</th>\n",
       "      <th>Type</th>\n",
       "      <th>Country</th>\n",
       "      <th>Area</th>\n",
       "      <th>Location</th>\n",
       "      <th>Activity</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>...</th>\n",
       "      <th>Time</th>\n",
       "      <th>Species</th>\n",
       "      <th>Investigator or Source</th>\n",
       "      <th>pdf</th>\n",
       "      <th>href formula</th>\n",
       "      <th>href</th>\n",
       "      <th>Case Number.1</th>\n",
       "      <th>Case Number.2</th>\n",
       "      <th>Unnamed: 22</th>\n",
       "      <th>Unnamed: 23</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Case Number, Date, Year, Type, Country, Area, Location, Activity, Name, Sex, Age, Injury, Fatal (Y/N), Time, Species, Investigator or Source, pdf, href formula, href, Case Number.1, Case Number.2, Unnamed: 22, Unnamed: 23]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 23 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "# Here we exclude this possible indexing column:\n",
    "\n",
    "(df[[col for col in list(df.columns)  if col!= \"original order\"]]\n",
    "[df[[col for col in list(df.columns) if col!= \"original order\"]]\n",
    ".duplicated()])\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Duplicated columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.1. In almost all the rows, the columns Case Number, Case Number.1  and Case Number.2 have the same values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5979, 24)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df[\n",
    "    (df[\"Case Number\"] == df[\"Case Number.1\"])\n",
    "    & \n",
    "    (df[\"Case Number\"] == df[\"Case Number.2\"])\n",
    "].shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, we can drop the columns \"Case Number.1\" and \"Case Number.2\" as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(columns = [\"Case Number.1\", \"Case Number.2\"], inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.2. Similarly, the columns \"href\" and \"href formula\" might contain the same information. To validate this, the next line show us the rows where the values for these two columns are different."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(54, 2)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df[\"href formula\"] != df[\"href\"]][[\"href formula\", \"href\"]].shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have 54 rows with information that differs in both columns; this is roughly 0.9% fo the total rows. As \"href\" has 3 NaNs and \"href formula\" has just 1 NaN, we can drop the\n",
    "column  \"href\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3019    http://sharkattackfile.net/spreadsheets/pdf_di...\n",
       "Name: href, dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Locating the value of the missing row in href formula which is present in href:\n",
    "\n",
    "df[(df[\"href formula\"] != df[\"href\"]) & (df[\"href formula\"]).isna()][\"href\"]\n",
    "\n",
    "#shark_attack.drop(columns = [\"href\"], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Then assign this value to the dataset:\n",
    "\n",
    "df.at[3019, 'href formula'] = df.loc[3019][\"href\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'http://sharkattackfile.net/spreadsheets/pdf_directory/1975.01.19-Barrowman.pdf'"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Validating the change:\n",
    "\n",
    "df.loc[3019][\"href formula\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can finally drop the column \"href\" form the data set:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(columns = [\"href\"], inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.3. We will order the data frame by \"original order number\" and have a look to the columns \"Case Number\" and \"original order\". Possibly we can drop one of them and make it an index for our data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>Case Number</th>\n",
       "      <th>Date</th>\n",
       "      <th>original order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5991</th>\n",
       "      <td>ND.0001</td>\n",
       "      <td>1845-1853</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5990</th>\n",
       "      <td>ND.0002</td>\n",
       "      <td>1883-1889</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5989</th>\n",
       "      <td>ND.0003</td>\n",
       "      <td>1900-1905</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5988</th>\n",
       "      <td>ND.0004</td>\n",
       "      <td>Before 1903</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5987</th>\n",
       "      <td>ND.0005</td>\n",
       "      <td>Before 1903</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5986</th>\n",
       "      <td>ND.0006</td>\n",
       "      <td>Before 1906</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5985</th>\n",
       "      <td>ND.0007</td>\n",
       "      <td>Before 1906</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5984</th>\n",
       "      <td>ND.0008</td>\n",
       "      <td>Before 1906</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5983</th>\n",
       "      <td>ND.0009</td>\n",
       "      <td>Before 1906</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5982</th>\n",
       "      <td>ND.0010</td>\n",
       "      <td>Circa 1862</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Case Number         Date  original order\n",
       "5991     ND.0001    1845-1853               2\n",
       "5990     ND.0002    1883-1889               3\n",
       "5989     ND.0003    1900-1905               4\n",
       "5988     ND.0004  Before 1903               5\n",
       "5987     ND.0005  Before 1903               6\n",
       "5986     ND.0006  Before 1906               7\n",
       "5985     ND.0007  Before 1906               8\n",
       "5984     ND.0008  Before 1906               9\n",
       "5983     ND.0009  Before 1906              10\n",
       "5982     ND.0010   Circa 1862              11"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[[\"Case Number\", \"Date\", \"original order\"]].sort_values(by ='original order').head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Missing values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We want to know now whether there is missing information in the data set. To this end, we use the function isna provided in Pandas. Also we introduce a function that give us the percentage of NaN's contained in each column over the total of rows in the data set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "def null_cols(data):\n",
    "    \n",
    "    \"\"\"\n",
    "    This function takes a dataframe df and shows the columns of df that have NaN values\n",
    "    and the number of them\n",
    "    \n",
    "    \"\"\"\n",
    "    \n",
    "    nulls = data.isna().sum()\n",
    "    return nulls[nulls > 0] / len(data) * 100\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country                    0.717623\n",
       "Area                       6.708945\n",
       "Location                   8.277704\n",
       "Activity                   8.795060\n",
       "Name                       3.337784\n",
       "Sex                        9.462617\n",
       "Age                       44.742991\n",
       "Injury                     0.450601\n",
       "Fatal (Y/N)                0.317089\n",
       "Time                      53.621495\n",
       "Species                   48.965287\n",
       "Investigator or Source     0.250334\n",
       "Unnamed: 22               99.983311\n",
       "Unnamed: 23               99.966622\n",
       "dtype: float64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_cols(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Some preliminar conclusions:\n",
    "\n",
    "* The unnamed columns 22 and 23 should be dropped, because are almost entirely null.\n",
    "\n",
    "* Due to the relevance of the \"Species\" column in the data set, we cannot drop this column, even when the percentage of NaN's there is near to 50%.\n",
    "\n",
    "* The columns \"Time\" and \"Year\" seem to be irrelevant. However we will explore it further.\n",
    "\n",
    "* The columns \"Country\", \"Area\" and \"Location\" are related, so it might be possible to infer the missing values one from the others.\n",
    "\n",
    "* The column \"Age\" maybe can be infered from anothers after further inspection and it seems to be relevant also in our context so we decided not to drop it.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.1. Droping \"Unnamed\" and \"Time\" columns\n",
    "\n",
    "Since the unnamed columns are not relevant, we can drop from our dataset. Also we decided to drop the column \"Time\" because it has over 50% of null values and contains information which is not precise."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(columns = [\"Unnamed: 22\", \"Unnamed: 23\", \"Time\"], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country                    0.717623\n",
       "Area                       6.708945\n",
       "Location                   8.277704\n",
       "Activity                   8.795060\n",
       "Name                       3.337784\n",
       "Sex                        9.462617\n",
       "Age                       44.742991\n",
       "Injury                     0.450601\n",
       "Fatal (Y/N)                0.317089\n",
       "Species                   48.965287\n",
       "Investigator or Source     0.250334\n",
       "dtype: float64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_cols(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.2. Filling values in \"Injury\" and \"Fatal(Y/N)\" heuristically\n",
    "\n",
    "\"Injury\" and \"Fatal(Y/N)\" are correlated. We can use the information of each other in order to fill the missing information. This line code show us the rows in which we have values for injury but NaNs in Fatal(Y/N)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>Injury</th>\n",
       "      <th>Fatal (Y/N)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>No injury, but sharks repeatedly hit their fin...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1844</th>\n",
       "      <td>Reported as shark attack but probable drowning</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2449</th>\n",
       "      <td>FATAL</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3280</th>\n",
       "      <td>Diver shot the shark, then it injured his arm ...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3435</th>\n",
       "      <td>Disappeared, probable drowning but sharks in a...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3901</th>\n",
       "      <td>Boat damaged</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4107</th>\n",
       "      <td>No injury to occupants. Shark tore nets &amp; traw...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4112</th>\n",
       "      <td>Human remains found in shark</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5307</th>\n",
       "      <td>Disappeared, but shark involvement unconfirmed</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5437</th>\n",
       "      <td>No injury, no attack</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5468</th>\n",
       "      <td>Human remains found in 4m, 900 kg shark</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5642</th>\n",
       "      <td>No injury</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5699</th>\n",
       "      <td>Possible drowning and scavenging</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5718</th>\n",
       "      <td>FATAL</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5747</th>\n",
       "      <td>Death preceded shark involvement</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5793</th>\n",
       "      <td>Shark caught contained human remains</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5794</th>\n",
       "      <td>Shark caught, contained human remains</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5820</th>\n",
       "      <td>Probable drowning</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5849</th>\n",
       "      <td>Partial hominid remains recovered from shark, ...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 Injury Fatal (Y/N)\n",
       "54    No injury, but sharks repeatedly hit their fin...         NaN\n",
       "1844    Reported as shark attack but probable drowning          NaN\n",
       "2449                                              FATAL         NaN\n",
       "3280  Diver shot the shark, then it injured his arm ...         NaN\n",
       "3435  Disappeared, probable drowning but sharks in a...         NaN\n",
       "3901                                       Boat damaged         NaN\n",
       "4107  No injury to occupants. Shark tore nets & traw...         NaN\n",
       "4112                       Human remains found in shark         NaN\n",
       "5307     Disappeared, but shark involvement unconfirmed         NaN\n",
       "5437                               No injury, no attack         NaN\n",
       "5468            Human remains found in 4m, 900 kg shark         NaN\n",
       "5642                                          No injury         NaN\n",
       "5699                   Possible drowning and scavenging         NaN\n",
       "5718                                              FATAL         NaN\n",
       "5747                   Death preceded shark involvement         NaN\n",
       "5793               Shark caught contained human remains         NaN\n",
       "5794              Shark caught, contained human remains         NaN\n",
       "5820                                 Probable drowning          NaN\n",
       "5849  Partial hominid remains recovered from shark, ...         NaN"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df[\"Injury\"].isna()== False) & (df[\"Fatal (Y/N)\"].isna())][[\"Injury\", \"Fatal (Y/N)\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is a list with the indexes of the rows which have NaN values in the column \"Fatal\"\n",
    "rows_missing = list(df[(df[\"Injury\"].isna()== False) & (df[\"Fatal (Y/N)\"].isna())].index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can infer the missing values as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "inferences = [\"N\", \"Y\", \"Y\", \"N\", \"Y\", \"N\", \"N\", \"Y\", \"N\", \"N\", \"Y\", \"N\", \"Y\", \"Y\", \"Y\", \"Y\", \"Y\", \"Y\", \"Y\"]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At this point we introduce the function \"filling\" which allows us to fill or replace data existent in our dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "def filling(data, indexes, values, col_name):\n",
    "    \n",
    "    \"\"\"\n",
    "    \n",
    "    This function fills a column col_name of a data frame df at the places located\n",
    "    by indexes with the corresponding values in values.\n",
    "    \n",
    "    \"\"\"\n",
    "    \n",
    "    j = 0\n",
    "    for i in indexes:   \n",
    "        data.at[ i, col_name] = values[j]\n",
    "        j = j+1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "filling(df, rows_missing, inferences, \"Fatal (Y/N)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country                    0.717623\n",
       "Area                       6.708945\n",
       "Location                   8.277704\n",
       "Activity                   8.795060\n",
       "Name                       3.337784\n",
       "Sex                        9.462617\n",
       "Age                       44.742991\n",
       "Injury                     0.450601\n",
       "Species                   48.965287\n",
       "Investigator or Source     0.250334\n",
       "dtype: float64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_cols(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.3. Filling values in \"Country\", \"Area\" and \"Location\"\n",
    "\n",
    "Since the columns \"Country\", \"Area\" and \"Location\" are related, we will try to infer the missing values for one of these columns from the other two. We are going to infer the missing values of \"country\" first.\n",
    "\n",
    "##### 4.3.1. Missing values in \"Country\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>Country</th>\n",
       "      <th>Area</th>\n",
       "      <th>Location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3162</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Caribbean Sea</td>\n",
       "      <td>Between St. Kitts &amp; Nevis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4040</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Between Comores &amp; Madagascar</td>\n",
       "      <td>Geyser Bank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4271</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Caribbean Sea</td>\n",
       "      <td>Between  Cuba  &amp; Costa Rica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4790</th>\n",
       "      <td>NaN</td>\n",
       "      <td>French Southern Territories</td>\n",
       "      <td>Île Saint-Paul</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Country                          Area                     Location\n",
       "3162     NaN                 Caribbean Sea    Between St. Kitts & Nevis\n",
       "4040     NaN  Between Comores & Madagascar                  Geyser Bank\n",
       "4271     NaN                 Caribbean Sea  Between  Cuba  & Costa Rica\n",
       "4790     NaN   French Southern Territories               Île Saint-Paul"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df.Country.isna()) & (df.Area.isna()== False) & (df.Location.isna()==False)][[\"Country\", \"Area\", \"Location\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "rows_missing = list(df[(df.Country.isna()) & (df.Area.isna()== False) & (df.Location.isna()==False)].index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "inferences = [\"Saint Kitts and Nevis\", \"France\", \"Cuba\", \"France\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "filling(df, rows_missing, inferences, \"Country\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>Country</th>\n",
       "      <th>Area</th>\n",
       "      <th>Location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3379</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Florida Strait</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4412</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>225 miles east of Hong Kong</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5189</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Near the equator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5560</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Santa Cruz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5847</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Carlisle Bay</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5896</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>In a river feeding into the Bay of Bengal</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Country Area                                   Location\n",
       "3379     NaN  NaN                             Florida Strait\n",
       "4412     NaN  NaN                225 miles east of Hong Kong\n",
       "5189     NaN  NaN                           Near the equator\n",
       "5560     NaN  NaN                                 Santa Cruz\n",
       "5847     NaN  NaN                               Carlisle Bay\n",
       "5896     NaN  NaN  In a river feeding into the Bay of Bengal"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df.Country.isna()) & (df.Area.isna()) & (df.Location.isna()==False)][[\"Country\", \"Area\", \"Location\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "rows_missing = list(df[(df.Country.isna()) & (df.Area.isna()) & (df.Location.isna()==False)].index)\n",
    "\n",
    "inferences = [\"US\", \"China\", \"Ecuador\", \"US\", \"Barbados\", \"India\"]\n",
    "inferences1 = [\"Florida\", \"Hong Kong\", \"Ecuador\", \"California\", \"Carlisle Bay\", \"Bengal\"]\n",
    "\n",
    "filling(df, rows_missing, inferences, \"Country\")\n",
    "filling(df, rows_missing, inferences1, \"Area\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country                    0.550734\n",
       "Area                       6.608812\n",
       "Location                   8.277704\n",
       "Activity                   8.795060\n",
       "Name                       3.337784\n",
       "Sex                        9.462617\n",
       "Age                       44.742991\n",
       "Injury                     0.450601\n",
       "Species                   48.965287\n",
       "Investigator or Source     0.250334\n",
       "dtype: float64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_cols(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we have the three relevant columns null, we cannot infer any of the fields. As they are a very small part of the data frame (< 0.6%)  we decided to drop these rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "rows_missing = list(df[df.Country.isna() & ( df.Area.isna()) & (df.Location.isna())].index)\n",
    "\n",
    "df = df.drop(rows_missing, axis =0).copy()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>Case Number</th>\n",
       "      <th>Date</th>\n",
       "      <th>Year</th>\n",
       "      <th>Type</th>\n",
       "      <th>Country</th>\n",
       "      <th>Area</th>\n",
       "      <th>Location</th>\n",
       "      <th>Activity</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>Injury</th>\n",
       "      <th>Fatal (Y/N)</th>\n",
       "      <th>Species</th>\n",
       "      <th>Investigator or Source</th>\n",
       "      <th>pdf</th>\n",
       "      <th>href formula</th>\n",
       "      <th>original order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2731</th>\n",
       "      <td>1983.00.00.d</td>\n",
       "      <td>Ca. 1983</td>\n",
       "      <td>1983</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>NaN</td>\n",
       "      <td>English Channel</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Swimming</td>\n",
       "      <td>Padma Shri Taranath Narayan Shenoy</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Left leg bitten</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Times of India, 2/5/2012</td>\n",
       "      <td>1983.00.00.d-Shenoy.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>3262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3792</th>\n",
       "      <td>1960.01.26</td>\n",
       "      <td>26-Jan-60</td>\n",
       "      <td>1960</td>\n",
       "      <td>Sea Disaster</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Between Timor &amp; Darwin, Australia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Portuguese Airliner with 9 people aboard went ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>As searchers approached wreckage, sharks circl...</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>V.M. Coppleson (1962), p.260</td>\n",
       "      <td>1960.01.26-Portuguese airliner.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4005</th>\n",
       "      <td>1956.09.13</td>\n",
       "      <td>13-Sep-56</td>\n",
       "      <td>1956</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Near the Andaman &amp; Nicobar Islands</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Climbing back on ship</td>\n",
       "      <td>male</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FATAL</td>\n",
       "      <td>Y</td>\n",
       "      <td>Blue shark</td>\n",
       "      <td>M. Hosina</td>\n",
       "      <td>1956.09.13-TunaBoat.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>1988</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4473</th>\n",
       "      <td>1942.11.00.a</td>\n",
       "      <td>Nov-42</td>\n",
       "      <td>1942</td>\n",
       "      <td>Sea Disaster</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Off South American coast</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dutch merchant ship Zaandam torpedoed  by the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FATAL</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M.  Murphy; V.M. Coppleson (1962), pp.207-208</td>\n",
       "      <td>1942.11.00.a-Izzi.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>1520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4485</th>\n",
       "      <td>1942.06.00</td>\n",
       "      <td>Jun-42</td>\n",
       "      <td>1942</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>NaN</td>\n",
       "      <td>300 miles east of St. Thomas (Virgin Islands)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>On life raft tethered to lifeboat. A seaman pu...</td>\n",
       "      <td>male</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Forearm lacerated</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>V.M. Coppleson (1962), p.258</td>\n",
       "      <td>1942.06.00-on-life-raft.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>1508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5370</th>\n",
       "      <td>1897.03.15.b.R</td>\n",
       "      <td>Reported 15-Mar-1897</td>\n",
       "      <td>1897</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Mediterranean Sea</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Swimming</td>\n",
       "      <td>male</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FATAL</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Daily Northwestern, 5/15/1897</td>\n",
       "      <td>1897.03.15.b.R-Mediterranean.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5558</th>\n",
       "      <td>1881.08.16.R</td>\n",
       "      <td>Reported 16-Aug-1881</td>\n",
       "      <td>1881</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Western Banks</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Floating, holding onto an oar after dory capsized</td>\n",
       "      <td>George Sedgwick</td>\n",
       "      <td>M</td>\n",
       "      <td>20</td>\n",
       "      <td>FATAL</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Lewiston Evening Journal, 8/16/1881</td>\n",
       "      <td>1881.08.16.R-Sedgwick.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5866</th>\n",
       "      <td>0077.00.00</td>\n",
       "      <td>77  A.D.</td>\n",
       "      <td>77</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ionian Sea</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Sponge diving</td>\n",
       "      <td>males</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FATAL</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Perils mentioned by Pliny the Elder (23 A.D. t...</td>\n",
       "      <td>77AD-Pliny.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5868</th>\n",
       "      <td>0.0214</td>\n",
       "      <td>Ca. 214 B.C.</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ionian Sea</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ascending from a dive</td>\n",
       "      <td>Tharsys, a sponge diver</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FATAL, shark/s bit him in two</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Reported by Greek poet, Leonidas of Tarentum (...</td>\n",
       "      <td>214BC-Tharsys.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>125</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Case Number                  Date  Year          Type Country  \\\n",
       "2731    1983.00.00.d              Ca. 1983  1983    Unprovoked     NaN   \n",
       "3792      1960.01.26             26-Jan-60  1960  Sea Disaster     NaN   \n",
       "4005      1956.09.13             13-Sep-56  1956    Unprovoked     NaN   \n",
       "4473    1942.11.00.a                Nov-42  1942  Sea Disaster     NaN   \n",
       "4485      1942.06.00                Jun-42  1942    Unprovoked     NaN   \n",
       "5370  1897.03.15.b.R  Reported 15-Mar-1897  1897    Unprovoked     NaN   \n",
       "5558    1881.08.16.R  Reported 16-Aug-1881  1881    Unprovoked     NaN   \n",
       "5866      0077.00.00              77  A.D.    77    Unprovoked     NaN   \n",
       "5868          0.0214          Ca. 214 B.C.     0    Unprovoked     NaN   \n",
       "\n",
       "                                               Area Location  \\\n",
       "2731                                English Channel      NaN   \n",
       "3792              Between Timor & Darwin, Australia      NaN   \n",
       "4005             Near the Andaman & Nicobar Islands      NaN   \n",
       "4473                       Off South American coast      NaN   \n",
       "4485  300 miles east of St. Thomas (Virgin Islands)      NaN   \n",
       "5370                              Mediterranean Sea      NaN   \n",
       "5558                                  Western Banks      NaN   \n",
       "5866                                     Ionian Sea      NaN   \n",
       "5868                                     Ionian Sea      NaN   \n",
       "\n",
       "                                               Activity  \\\n",
       "2731                                           Swimming   \n",
       "3792  Portuguese Airliner with 9 people aboard went ...   \n",
       "4005                              Climbing back on ship   \n",
       "4473  Dutch merchant ship Zaandam torpedoed  by the ...   \n",
       "4485  On life raft tethered to lifeboat. A seaman pu...   \n",
       "5370                                           Swimming   \n",
       "5558  Floating, holding onto an oar after dory capsized   \n",
       "5866                                      Sponge diving   \n",
       "5868                              Ascending from a dive   \n",
       "\n",
       "                                    Name  Sex  Age  \\\n",
       "2731  Padma Shri Taranath Narayan Shenoy    M  NaN   \n",
       "3792                                 NaN  NaN  NaN   \n",
       "4005                                male    M  NaN   \n",
       "4473                                 NaN    M  NaN   \n",
       "4485                                male    M  NaN   \n",
       "5370                                male    M  NaN   \n",
       "5558                     George Sedgwick    M   20   \n",
       "5866                               males    M  NaN   \n",
       "5868             Tharsys, a sponge diver    M  NaN   \n",
       "\n",
       "                                                 Injury Fatal (Y/N)  \\\n",
       "2731                                    Left leg bitten           N   \n",
       "3792  As searchers approached wreckage, sharks circl...           N   \n",
       "4005                                              FATAL           Y   \n",
       "4473                                              FATAL           Y   \n",
       "4485                                  Forearm lacerated           N   \n",
       "5370                                              FATAL           Y   \n",
       "5558                                              FATAL           Y   \n",
       "5866                                              FATAL           Y   \n",
       "5868                      FATAL, shark/s bit him in two           Y   \n",
       "\n",
       "         Species                             Investigator or Source  \\\n",
       "2731         NaN                           Times of India, 2/5/2012   \n",
       "3792         NaN                       V.M. Coppleson (1962), p.260   \n",
       "4005  Blue shark                                          M. Hosina   \n",
       "4473         NaN      M.  Murphy; V.M. Coppleson (1962), pp.207-208   \n",
       "4485         NaN                       V.M. Coppleson (1962), p.258   \n",
       "5370         NaN                      Daily Northwestern, 5/15/1897   \n",
       "5558         NaN                Lewiston Evening Journal, 8/16/1881   \n",
       "5866         NaN  Perils mentioned by Pliny the Elder (23 A.D. t...   \n",
       "5868         NaN  Reported by Greek poet, Leonidas of Tarentum (...   \n",
       "\n",
       "                                     pdf  \\\n",
       "2731             1983.00.00.d-Shenoy.pdf   \n",
       "3792  1960.01.26-Portuguese airliner.pdf   \n",
       "4005             1956.09.13-TunaBoat.pdf   \n",
       "4473               1942.11.00.a-Izzi.pdf   \n",
       "4485         1942.06.00-on-life-raft.pdf   \n",
       "5370    1897.03.15.b.R-Mediterranean.pdf   \n",
       "5558           1881.08.16.R-Sedgwick.pdf   \n",
       "5866                      77AD-Pliny.pdf   \n",
       "5868                   214BC-Tharsys.pdf   \n",
       "\n",
       "                                           href formula  original order  \n",
       "2731  http://sharkattackfile.net/spreadsheets/pdf_di...            3262  \n",
       "3792  http://sharkattackfile.net/spreadsheets/pdf_di...            2201  \n",
       "4005  http://sharkattackfile.net/spreadsheets/pdf_di...            1988  \n",
       "4473  http://sharkattackfile.net/spreadsheets/pdf_di...            1520  \n",
       "4485  http://sharkattackfile.net/spreadsheets/pdf_di...            1508  \n",
       "5370  http://sharkattackfile.net/spreadsheets/pdf_di...             623  \n",
       "5558  http://sharkattackfile.net/spreadsheets/pdf_di...             435  \n",
       "5866  http://sharkattackfile.net/spreadsheets/pdf_di...             127  \n",
       "5868  http://sharkattackfile.net/spreadsheets/pdf_di...             125  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.Country.isna() & ( (df.Area.isna()== False) & (df.Location.isna()))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We drop certain rows for which \"Area\" values are not enough to infer the rest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop([4473, 5370, 5558, 5866, 5868]).copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "rows_missing = list(df[df.Country.isna() & ( (df.Area.isna()== False) & (df.Location.isna()))].index)\n",
    "\n",
    "# Putting values for countries \n",
    "inferences = [\"England\", \"Australia\", \"India\", \"St. Thomas\"]\n",
    "filling(df, rows_missing, inferences, \"Country\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "#inferences = [\"English Channel\", \"Darwin\", \"Bengal\", \"St. Thomas\"]\n",
    "\n",
    "inferences = list(df[((df.Area.isna()== False) & (df.Location.isna()))][\"Area\"].loc[rows_missing])\n",
    "\n",
    "filling(df, rows_missing, inferences, \"Location\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "inferences = [\"English Channel\", \"Darwin\", \"Bengal\", \"St. Thomas\"]\n",
    "filling(df, rows_missing, inferences, \"Area\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 4.3.2. We make sure every country is written in upper case:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"Country\"] = df['Country'].str.upper() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.4. Cleaning and parsing the column Date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will order the data frame by \"original order number\" and have a look to the columns \"Case Number\" and \"original order\". Possibly we can drop one of them and make it an index for our data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>Case Number</th>\n",
       "      <th>Date</th>\n",
       "      <th>Year</th>\n",
       "      <th>Type</th>\n",
       "      <th>Country</th>\n",
       "      <th>Area</th>\n",
       "      <th>Location</th>\n",
       "      <th>Activity</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>Injury</th>\n",
       "      <th>Fatal (Y/N)</th>\n",
       "      <th>Species</th>\n",
       "      <th>Investigator or Source</th>\n",
       "      <th>pdf</th>\n",
       "      <th>href formula</th>\n",
       "      <th>original order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5991</th>\n",
       "      <td>ND.0001</td>\n",
       "      <td>1845-1853</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>CEYLON (SRI LANKA)</td>\n",
       "      <td>Eastern Province</td>\n",
       "      <td>Below the English fort, Trincomalee</td>\n",
       "      <td>Swimming</td>\n",
       "      <td>male</td>\n",
       "      <td>M</td>\n",
       "      <td>15</td>\n",
       "      <td>FATAL. \"Shark bit him in half, carrying away t...</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S.W. Baker</td>\n",
       "      <td>ND-0001-Ceylon.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5990</th>\n",
       "      <td>ND.0002</td>\n",
       "      <td>1883-1889</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>PANAMA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Panama Bay 8ºN, 79ºW</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Jules Patterson</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FATAL</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>The Sun, 10/20/1938</td>\n",
       "      <td>ND-0002-JulesPatterson.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5989</th>\n",
       "      <td>ND.0003</td>\n",
       "      <td>1900-1905</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>USA</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Ocracoke Inlet</td>\n",
       "      <td>Swimming</td>\n",
       "      <td>Coast Guard personnel</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FATAL</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F. Schwartz, p.23; C. Creswell, GSAF</td>\n",
       "      <td>ND-0003-Ocracoke_1900-1905.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5988</th>\n",
       "      <td>ND.0004</td>\n",
       "      <td>Before 1903</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>AUSTRALIA</td>\n",
       "      <td>Western Australia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Pearl diving</td>\n",
       "      <td>Ahmun</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FATAL</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>H. Taunton; N. Bartlett,  pp. 233-234</td>\n",
       "      <td>ND-0004-Ahmun.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5987</th>\n",
       "      <td>ND.0005</td>\n",
       "      <td>Before 1903</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>AUSTRALIA</td>\n",
       "      <td>Western Australia</td>\n",
       "      <td>Roebuck Bay</td>\n",
       "      <td>Diving</td>\n",
       "      <td>male</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FATAL</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>H. Taunton; N. Bartlett,  p. 234</td>\n",
       "      <td>ND-0005-RoebuckBay.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Case Number         Date  Year        Type             Country  \\\n",
       "5991     ND.0001    1845-1853     0  Unprovoked  CEYLON (SRI LANKA)   \n",
       "5990     ND.0002    1883-1889     0  Unprovoked              PANAMA   \n",
       "5989     ND.0003    1900-1905     0  Unprovoked                 USA   \n",
       "5988     ND.0004  Before 1903     0  Unprovoked           AUSTRALIA   \n",
       "5987     ND.0005  Before 1903     0  Unprovoked           AUSTRALIA   \n",
       "\n",
       "                   Area                             Location      Activity  \\\n",
       "5991   Eastern Province  Below the English fort, Trincomalee      Swimming   \n",
       "5990                NaN                 Panama Bay 8ºN, 79ºW           NaN   \n",
       "5989     North Carolina                       Ocracoke Inlet      Swimming   \n",
       "5988  Western Australia                                  NaN  Pearl diving   \n",
       "5987  Western Australia                          Roebuck Bay        Diving   \n",
       "\n",
       "                       Name Sex  Age  \\\n",
       "5991                   male   M   15   \n",
       "5990        Jules Patterson   M  NaN   \n",
       "5989  Coast Guard personnel   M  NaN   \n",
       "5988                  Ahmun   M  NaN   \n",
       "5987                   male   M  NaN   \n",
       "\n",
       "                                                 Injury Fatal (Y/N) Species  \\\n",
       "5991  FATAL. \"Shark bit him in half, carrying away t...           Y     NaN   \n",
       "5990                                              FATAL           Y     NaN   \n",
       "5989                                              FATAL           Y     NaN   \n",
       "5988                                              FATAL           Y     NaN   \n",
       "5987                                              FATAL           Y     NaN   \n",
       "\n",
       "                     Investigator or Source                             pdf  \\\n",
       "5991                             S.W. Baker              ND-0001-Ceylon.pdf   \n",
       "5990                    The Sun, 10/20/1938      ND-0002-JulesPatterson.pdf   \n",
       "5989   F. Schwartz, p.23; C. Creswell, GSAF  ND-0003-Ocracoke_1900-1905.pdf   \n",
       "5988  H. Taunton; N. Bartlett,  pp. 233-234               ND-0004-Ahmun.pdf   \n",
       "5987       H. Taunton; N. Bartlett,  p. 234          ND-0005-RoebuckBay.pdf   \n",
       "\n",
       "                                           href formula  original order  \n",
       "5991  http://sharkattackfile.net/spreadsheets/pdf_di...               2  \n",
       "5990  http://sharkattackfile.net/spreadsheets/pdf_di...               3  \n",
       "5989  http://sharkattackfile.net/spreadsheets/pdf_di...               4  \n",
       "5988  http://sharkattackfile.net/spreadsheets/pdf_di...               5  \n",
       "5987  http://sharkattackfile.net/spreadsheets/pdf_di...               6  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values(by ='original order').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that the first entries of the column Case Number are related with the original order. Also, the Case Number contains the date in case there is one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>Case Number</th>\n",
       "      <th>Date</th>\n",
       "      <th>Year</th>\n",
       "      <th>Type</th>\n",
       "      <th>Country</th>\n",
       "      <th>Area</th>\n",
       "      <th>Location</th>\n",
       "      <th>Activity</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>Injury</th>\n",
       "      <th>Fatal (Y/N)</th>\n",
       "      <th>Species</th>\n",
       "      <th>Investigator or Source</th>\n",
       "      <th>pdf</th>\n",
       "      <th>href formula</th>\n",
       "      <th>original order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5899</th>\n",
       "      <td>ND.0110</td>\n",
       "      <td>No date, late 1960s</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>VENEZUELA</td>\n",
       "      <td>Los Roques  Islands</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Spearfishing</td>\n",
       "      <td>4 French divers</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FATAL (x3), one survived with minor injuries</td>\n",
       "      <td>Y</td>\n",
       "      <td>said to involve 2.5 m hammerhead sharks</td>\n",
       "      <td>http://waterco.com.br/ataque_tubarao.htm</td>\n",
       "      <td>ND-0110-FrenchDivers.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5905</th>\n",
       "      <td>ND.0102</td>\n",
       "      <td>No date, Before 1963</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>BAHREIN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Pearl diving</td>\n",
       "      <td>male</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FATAL</td>\n",
       "      <td>Y</td>\n",
       "      <td>Tiger shark</td>\n",
       "      <td>A.C. Doyle</td>\n",
       "      <td>ND-0102-Bahrein.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5907</th>\n",
       "      <td>ND.0097</td>\n",
       "      <td>No date</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>USA</td>\n",
       "      <td>Florida</td>\n",
       "      <td>Key West, Monroe County</td>\n",
       "      <td>Kitesurfing</td>\n",
       "      <td>Paul Menta</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Hand bitten</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Internet</td>\n",
       "      <td>ND-0097-PaulMenta.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5908</th>\n",
       "      <td>ND.0096</td>\n",
       "      <td>No date</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>REUNION</td>\n",
       "      <td>Grand'Anse</td>\n",
       "      <td>Petite-île</td>\n",
       "      <td>yachtsman in a zodiac</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Survived</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>G. Van Grevelynghe</td>\n",
       "      <td>ND-0096-Zodiac-Reunion.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5910</th>\n",
       "      <td>ND.0094</td>\n",
       "      <td>No date, Before May-1996</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>KOREA</td>\n",
       "      <td>South Korea</td>\n",
       "      <td>Cheju Island</td>\n",
       "      <td>Diving</td>\n",
       "      <td>female, a Hae Nyeo</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FATAL, injured while diving, then shark bit her</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>K. Amsler, Divernet.com</td>\n",
       "      <td>ND-0094-HaeNyeo.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5911</th>\n",
       "      <td>ND.0093</td>\n",
       "      <td>No date, Before Mar-1995</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>FRENCH POLYNESIA</td>\n",
       "      <td>Tuamotus</td>\n",
       "      <td>Rangiroa</td>\n",
       "      <td>Fishing</td>\n",
       "      <td>male</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Speared a shark, fell overboard and another sh...</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>J. Windh</td>\n",
       "      <td>ND-0093-Rangiroa.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5913</th>\n",
       "      <td>ND.0090</td>\n",
       "      <td>No date, Before Aug-1989</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>VANUATU</td>\n",
       "      <td>Malampa Province</td>\n",
       "      <td>Malakula</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FATAL</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S. Combs</td>\n",
       "      <td>ND-0090-Vanuatu-female.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5914</th>\n",
       "      <td>ND.0089</td>\n",
       "      <td>No date, Before Aug-1987</td>\n",
       "      <td>0</td>\n",
       "      <td>Provoked</td>\n",
       "      <td>VANUATU</td>\n",
       "      <td>Malampa Province</td>\n",
       "      <td>Hokai, Malakula</td>\n",
       "      <td>Attempting to drive shark from area</td>\n",
       "      <td>a chief</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Speared shark broke outrigger of canoe throwin...</td>\n",
       "      <td>N</td>\n",
       "      <td>A large hammerhead shark</td>\n",
       "      <td>S. Combs</td>\n",
       "      <td>ND-0089-VanuatuChief.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5915</th>\n",
       "      <td>ND.0088</td>\n",
       "      <td>No date, Before 1987</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>IRAN</td>\n",
       "      <td>Khuzestan Province</td>\n",
       "      <td>Ahvaz, on the Karun River</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Mr. Jabar-Kaaby</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Foot severed</td>\n",
       "      <td>N</td>\n",
       "      <td>Bull shark</td>\n",
       "      <td>B. Coad &amp; F. Papahn</td>\n",
       "      <td>ND-0088-Jabar-Kaaby.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5916</th>\n",
       "      <td>ND.0087</td>\n",
       "      <td>No date, Before  1975</td>\n",
       "      <td>0</td>\n",
       "      <td>Provoked</td>\n",
       "      <td>USA</td>\n",
       "      <td>Florida</td>\n",
       "      <td>Riviera Beach, Palm Beach County</td>\n",
       "      <td>Skin diving. Grabbed shark's tail; shark turne...</td>\n",
       "      <td>Carl Bruster</td>\n",
       "      <td>M</td>\n",
       "      <td>19</td>\n",
       "      <td>Ankle punctured &amp; lacerated, hands abraded PRO...</td>\n",
       "      <td>N</td>\n",
       "      <td>Nurse shark, 2.1 m [7']</td>\n",
       "      <td>R. Skocik, p.176</td>\n",
       "      <td>ND-0087-Carl-Bruster.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5917</th>\n",
       "      <td>ND.0086</td>\n",
       "      <td>No date, Before 1975</td>\n",
       "      <td>0</td>\n",
       "      <td>Invalid</td>\n",
       "      <td>PAPUA NEW GUINEA</td>\n",
       "      <td>Milne Bay  Province</td>\n",
       "      <td>D'Entrecasteaux islands, 20 miles off the coast</td>\n",
       "      <td>Scuba diving</td>\n",
       "      <td>Dan Hogan</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Said to be fatal but incident highly questionable</td>\n",
       "      <td>Y</td>\n",
       "      <td>Tiger shark, 4.6 m to 6 m [15' to 20']</td>\n",
       "      <td>F. Dennis, pp.15-16</td>\n",
       "      <td>ND-0086-DanHogan.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5918</th>\n",
       "      <td>ND.0085</td>\n",
       "      <td>No date, Before 1969</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>RED SEA?</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Free diving</td>\n",
       "      <td>Jill Reed</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Shoulder scratched, swim fin bitten</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Captain T. Falcon-Barker, pp. 91-93</td>\n",
       "      <td>ND-0085-JillReed.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5919</th>\n",
       "      <td>ND.0084</td>\n",
       "      <td>No date, Before 3-Jan-1967</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>SOUTH AFRICA</td>\n",
       "      <td>Eastern Cape Province</td>\n",
       "      <td>Keiskamma River mouth</td>\n",
       "      <td>Crossing river on a raft</td>\n",
       "      <td>Sinsa</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FATAL, leg severed</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Whitaker, The Sun, 1/3/1967</td>\n",
       "      <td>ND-0084-Sinsa.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5920</th>\n",
       "      <td>ND.0083</td>\n",
       "      <td>No date, Before 1963</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>USA</td>\n",
       "      <td>California</td>\n",
       "      <td>LaJolla, San Diego County</td>\n",
       "      <td>Free diving, collecting sand dollars</td>\n",
       "      <td>Charles Fleming</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Calf bitten</td>\n",
       "      <td>N</td>\n",
       "      <td>Shovelnose guitarfish, adult male</td>\n",
       "      <td>C. Limbaugh in Sharks &amp; Survival, pp.77-78</td>\n",
       "      <td>ND-0083-Fleming.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5921</th>\n",
       "      <td>ND.0082</td>\n",
       "      <td>No date, Before 8-May-1965</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>GREECE</td>\n",
       "      <td>Island of Volos</td>\n",
       "      <td>Eastern shore</td>\n",
       "      <td>Swimming</td>\n",
       "      <td>woman</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FATAL</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Sydney Morning Herald, 5/8/1965</td>\n",
       "      <td>ND-0082-Volos-Greece.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5922</th>\n",
       "      <td>ND.0081</td>\n",
       "      <td>No date, Before 1963</td>\n",
       "      <td>0</td>\n",
       "      <td>Invalid</td>\n",
       "      <td>USA</td>\n",
       "      <td>Hawaii</td>\n",
       "      <td>Portlock, Oahu</td>\n",
       "      <td>Diving</td>\n",
       "      <td>Val Valentine</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A 4.3 m [14'] shark made threat display. No in...</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B. Sojka &amp; D. Lloyd</td>\n",
       "      <td>ND-0081-Valentine.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5923</th>\n",
       "      <td>ND.0078</td>\n",
       "      <td>No date, Before 1902</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>USA</td>\n",
       "      <td>Florida</td>\n",
       "      <td>Mosquito Inlet (Ponce Inlet), Volusia County</td>\n",
       "      <td>Canoeing</td>\n",
       "      <td>male</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FATAL</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>W.H. Gregg, p.19;      L. Schultz &amp; M. Malin, ...</td>\n",
       "      <td>ND-0078-canoeist-mail-carrier.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5924</th>\n",
       "      <td>ND.0076</td>\n",
       "      <td>No date, Before 1902</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>MEDITERRANEAN SEA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>male, a ship carpenter</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FATAL</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>W.H. Gregg, p.22;      L. Schultz &amp; M. Malin, ...</td>\n",
       "      <td>ND-0076-Mediterranean.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5925</th>\n",
       "      <td>ND.0075</td>\n",
       "      <td>No date, Before 1963</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>AUSTRALIA</td>\n",
       "      <td>Torres Strait</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Diving for trochus</td>\n",
       "      <td>male</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Calf removed</td>\n",
       "      <td>N</td>\n",
       "      <td>0.9 m [3'] shark</td>\n",
       "      <td>G.P. Whitley (1952), p.192, cites A. Marshall</td>\n",
       "      <td>ND-0075-TorresStrait.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5926</th>\n",
       "      <td>ND.0074</td>\n",
       "      <td>No date, After August 1926 and before 1936</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>AUSTRALIA</td>\n",
       "      <td>Western Australia</td>\n",
       "      <td>Cossack Creek</td>\n",
       "      <td>Pearl diving</td>\n",
       "      <td>Ted Luck</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Leg bitten</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N. Caldwell; T. Peake, GSAF</td>\n",
       "      <td>ND-0074-Ted-Luck.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5927</th>\n",
       "      <td>ND.0073</td>\n",
       "      <td>No date, Before 1963</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>SINGAPORE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Keppel Harbor,  2 miles from Singapore city ce...</td>\n",
       "      <td>Swimming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Recovered</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>V.M. Coppleson (1958), p.266</td>\n",
       "      <td>ND-0073-KeppelHarbourSingapore.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5965</th>\n",
       "      <td>ND.0027</td>\n",
       "      <td>No date</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>GREECE</td>\n",
       "      <td>Dodecanese Islands</td>\n",
       "      <td>Symi Island</td>\n",
       "      <td>Sponge diving</td>\n",
       "      <td>Psarofa-gomenes</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Head bitten</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M. N. Kalafatas</td>\n",
       "      <td>ND-0027-Psarofagomenos.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5969</th>\n",
       "      <td>ND.0023</td>\n",
       "      <td>No date</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>SOUTH AFRICA</td>\n",
       "      <td>Western Cape Province</td>\n",
       "      <td>Arniston</td>\n",
       "      <td>Wading</td>\n",
       "      <td>Madelaine Dalton</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ankle bitten</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>L. Green in Tavern of the Seas, p.182</td>\n",
       "      <td>ND-0023-Dalton.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5970</th>\n",
       "      <td>ND.0022</td>\n",
       "      <td>No date</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>AUSTRALIA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Pearl diving</td>\n",
       "      <td>Jaringoorli</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Lacerations to thigh</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Adelaide Advertiser, 1/11/1940</td>\n",
       "      <td>ND-0022-Jaringoorli.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5971</th>\n",
       "      <td>ND.0021</td>\n",
       "      <td>No date</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>SOUTH AFRICA</td>\n",
       "      <td>KwaZulu-Natal</td>\n",
       "      <td>Durban</td>\n",
       "      <td>Swimming in pool formed by construction of a w...</td>\n",
       "      <td>Indian boy</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FATAL, leg severed</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>L. Green in South African Beachcomber, p.97</td>\n",
       "      <td>ND-0021-DurbanIndianBoy.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5979</th>\n",
       "      <td>ND.0013</td>\n",
       "      <td>No date (3 days after preceding incident) &amp; pr...</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>SOUTH AFRICA</td>\n",
       "      <td>KwaZulu-Natal</td>\n",
       "      <td>Durban</td>\n",
       "      <td>Fishing</td>\n",
       "      <td>a native fisherman</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FATAL, body not recovered but shark was caught...</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Rural New Yorker, 7/19/1913</td>\n",
       "      <td>ND-0013-Durban-native-fisherman.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Case Number                                               Date  Year  \\\n",
       "5899     ND.0110                                No date, late 1960s     0   \n",
       "5905     ND.0102                               No date, Before 1963     0   \n",
       "5907     ND.0097                                            No date     0   \n",
       "5908     ND.0096                                            No date     0   \n",
       "5910     ND.0094                           No date, Before May-1996     0   \n",
       "5911     ND.0093                           No date, Before Mar-1995     0   \n",
       "5913     ND.0090                           No date, Before Aug-1989     0   \n",
       "5914     ND.0089                           No date, Before Aug-1987     0   \n",
       "5915     ND.0088                               No date, Before 1987     0   \n",
       "5916     ND.0087                              No date, Before  1975     0   \n",
       "5917     ND.0086                               No date, Before 1975     0   \n",
       "5918     ND.0085                               No date, Before 1969     0   \n",
       "5919     ND.0084                         No date, Before 3-Jan-1967     0   \n",
       "5920     ND.0083                               No date, Before 1963     0   \n",
       "5921     ND.0082                         No date, Before 8-May-1965     0   \n",
       "5922     ND.0081                               No date, Before 1963     0   \n",
       "5923     ND.0078                               No date, Before 1902     0   \n",
       "5924     ND.0076                               No date, Before 1902     0   \n",
       "5925     ND.0075                               No date, Before 1963     0   \n",
       "5926     ND.0074         No date, After August 1926 and before 1936     0   \n",
       "5927     ND.0073                               No date, Before 1963     0   \n",
       "5965     ND.0027                                            No date     0   \n",
       "5969     ND.0023                                            No date     0   \n",
       "5970     ND.0022                                            No date     0   \n",
       "5971     ND.0021                                            No date     0   \n",
       "5979     ND.0013  No date (3 days after preceding incident) & pr...     0   \n",
       "\n",
       "            Type            Country                   Area  \\\n",
       "5899  Unprovoked          VENEZUELA    Los Roques  Islands   \n",
       "5905  Unprovoked            BAHREIN                    NaN   \n",
       "5907  Unprovoked                USA                Florida   \n",
       "5908  Unprovoked            REUNION             Grand'Anse   \n",
       "5910  Unprovoked              KOREA            South Korea   \n",
       "5911  Unprovoked   FRENCH POLYNESIA               Tuamotus   \n",
       "5913  Unprovoked            VANUATU       Malampa Province   \n",
       "5914    Provoked            VANUATU       Malampa Province   \n",
       "5915  Unprovoked               IRAN     Khuzestan Province   \n",
       "5916    Provoked                USA                Florida   \n",
       "5917     Invalid   PAPUA NEW GUINEA    Milne Bay  Province   \n",
       "5918  Unprovoked           RED SEA?                    NaN   \n",
       "5919  Unprovoked       SOUTH AFRICA  Eastern Cape Province   \n",
       "5920  Unprovoked                USA             California   \n",
       "5921  Unprovoked             GREECE        Island of Volos   \n",
       "5922     Invalid                USA                 Hawaii   \n",
       "5923  Unprovoked                USA                Florida   \n",
       "5924  Unprovoked  MEDITERRANEAN SEA                    NaN   \n",
       "5925  Unprovoked          AUSTRALIA          Torres Strait   \n",
       "5926  Unprovoked          AUSTRALIA      Western Australia   \n",
       "5927  Unprovoked          SINGAPORE                    NaN   \n",
       "5965  Unprovoked             GREECE     Dodecanese Islands   \n",
       "5969  Unprovoked       SOUTH AFRICA  Western Cape Province   \n",
       "5970  Unprovoked          AUSTRALIA                    NaN   \n",
       "5971  Unprovoked       SOUTH AFRICA          KwaZulu-Natal   \n",
       "5979  Unprovoked       SOUTH AFRICA          KwaZulu-Natal   \n",
       "\n",
       "                                               Location  \\\n",
       "5899                                                NaN   \n",
       "5905                                                NaN   \n",
       "5907                            Key West, Monroe County   \n",
       "5908                                         Petite-île   \n",
       "5910                                       Cheju Island   \n",
       "5911                                           Rangiroa   \n",
       "5913                                           Malakula   \n",
       "5914                                    Hokai, Malakula   \n",
       "5915                          Ahvaz, on the Karun River   \n",
       "5916                   Riviera Beach, Palm Beach County   \n",
       "5917    D'Entrecasteaux islands, 20 miles off the coast   \n",
       "5918                                                NaN   \n",
       "5919                              Keiskamma River mouth   \n",
       "5920                          LaJolla, San Diego County   \n",
       "5921                                      Eastern shore   \n",
       "5922                                     Portlock, Oahu   \n",
       "5923       Mosquito Inlet (Ponce Inlet), Volusia County   \n",
       "5924                                                NaN   \n",
       "5925                                                NaN   \n",
       "5926                                      Cossack Creek   \n",
       "5927  Keppel Harbor,  2 miles from Singapore city ce...   \n",
       "5965                                        Symi Island   \n",
       "5969                                           Arniston   \n",
       "5970                                                NaN   \n",
       "5971                                            Durban    \n",
       "5979                                             Durban   \n",
       "\n",
       "                                               Activity  \\\n",
       "5899                                      Spearfishing    \n",
       "5905                                       Pearl diving   \n",
       "5907                                        Kitesurfing   \n",
       "5908                              yachtsman in a zodiac   \n",
       "5910                                             Diving   \n",
       "5911                                            Fishing   \n",
       "5913                                                NaN   \n",
       "5914                Attempting to drive shark from area   \n",
       "5915                                                NaN   \n",
       "5916  Skin diving. Grabbed shark's tail; shark turne...   \n",
       "5917                                       Scuba diving   \n",
       "5918                                        Free diving   \n",
       "5919                           Crossing river on a raft   \n",
       "5920               Free diving, collecting sand dollars   \n",
       "5921                                           Swimming   \n",
       "5922                                             Diving   \n",
       "5923                                           Canoeing   \n",
       "5924                                                NaN   \n",
       "5925                                 Diving for trochus   \n",
       "5926                                       Pearl diving   \n",
       "5927                                           Swimming   \n",
       "5965                                      Sponge diving   \n",
       "5969                                             Wading   \n",
       "5970                                       Pearl diving   \n",
       "5971  Swimming in pool formed by construction of a w...   \n",
       "5979                                            Fishing   \n",
       "\n",
       "                        Name  Sex  Age  \\\n",
       "5899         4 French divers    M  NaN   \n",
       "5905                    male    M  NaN   \n",
       "5907              Paul Menta    M  NaN   \n",
       "5908                     NaN    M  NaN   \n",
       "5910      female, a Hae Nyeo    F  NaN   \n",
       "5911                    male    M  NaN   \n",
       "5913                  female    F  NaN   \n",
       "5914                 a chief    M  NaN   \n",
       "5915         Mr. Jabar-Kaaby    M  NaN   \n",
       "5916            Carl Bruster    M   19   \n",
       "5917               Dan Hogan    M  NaN   \n",
       "5918               Jill Reed    F  NaN   \n",
       "5919                   Sinsa    M  NaN   \n",
       "5920         Charles Fleming    M  NaN   \n",
       "5921                   woman    F  NaN   \n",
       "5922           Val Valentine    M  NaN   \n",
       "5923                    male    M  NaN   \n",
       "5924  male, a ship carpenter    M  NaN   \n",
       "5925                    male    M  NaN   \n",
       "5926                Ted Luck    M  NaN   \n",
       "5927                     NaN  NaN  NaN   \n",
       "5965         Psarofa-gomenes    M  NaN   \n",
       "5969        Madelaine Dalton    F  NaN   \n",
       "5970             Jaringoorli    M  NaN   \n",
       "5971              Indian boy    M  NaN   \n",
       "5979      a native fisherman    M  NaN   \n",
       "\n",
       "                                                 Injury Fatal (Y/N)  \\\n",
       "5899       FATAL (x3), one survived with minor injuries           Y   \n",
       "5905                                              FATAL           Y   \n",
       "5907                                        Hand bitten           N   \n",
       "5908                                           Survived           N   \n",
       "5910   FATAL, injured while diving, then shark bit her            Y   \n",
       "5911  Speared a shark, fell overboard and another sh...           N   \n",
       "5913                                              FATAL           Y   \n",
       "5914  Speared shark broke outrigger of canoe throwin...           N   \n",
       "5915                                       Foot severed           N   \n",
       "5916  Ankle punctured & lacerated, hands abraded PRO...           N   \n",
       "5917  Said to be fatal but incident highly questionable           Y   \n",
       "5918                Shoulder scratched, swim fin bitten           N   \n",
       "5919                                 FATAL, leg severed           Y   \n",
       "5920                                        Calf bitten           N   \n",
       "5921                                              FATAL           Y   \n",
       "5922  A 4.3 m [14'] shark made threat display. No in...           N   \n",
       "5923                                              FATAL           Y   \n",
       "5924                                              FATAL           Y   \n",
       "5925                                       Calf removed           N   \n",
       "5926                                         Leg bitten           N   \n",
       "5927                                          Recovered           N   \n",
       "5965                                        Head bitten           N   \n",
       "5969                                       Ankle bitten           N   \n",
       "5970                               Lacerations to thigh           N   \n",
       "5971                                FATAL, leg severed            Y   \n",
       "5979  FATAL, body not recovered but shark was caught...           Y   \n",
       "\n",
       "                                      Species  \\\n",
       "5899  said to involve 2.5 m hammerhead sharks   \n",
       "5905                              Tiger shark   \n",
       "5907                                      NaN   \n",
       "5908                                      NaN   \n",
       "5910                                      NaN   \n",
       "5911                                      NaN   \n",
       "5913                                      NaN   \n",
       "5914                 A large hammerhead shark   \n",
       "5915                               Bull shark   \n",
       "5916                 Nurse shark, 2.1 m [7']    \n",
       "5917  Tiger shark, 4.6 m to 6 m [15' to 20']    \n",
       "5918                                      NaN   \n",
       "5919                                      NaN   \n",
       "5920       Shovelnose guitarfish, adult male    \n",
       "5921                                      NaN   \n",
       "5922                                      NaN   \n",
       "5923                                      NaN   \n",
       "5924                                      NaN   \n",
       "5925                         0.9 m [3'] shark   \n",
       "5926                                      NaN   \n",
       "5927                                      NaN   \n",
       "5965                                      NaN   \n",
       "5969                                      NaN   \n",
       "5970                                      NaN   \n",
       "5971                                      NaN   \n",
       "5979                                      NaN   \n",
       "\n",
       "                                 Investigator or Source  \\\n",
       "5899           http://waterco.com.br/ataque_tubarao.htm   \n",
       "5905                                         A.C. Doyle   \n",
       "5907                                           Internet   \n",
       "5908                                 G. Van Grevelynghe   \n",
       "5910                            K. Amsler, Divernet.com   \n",
       "5911                                           J. Windh   \n",
       "5913                                           S. Combs   \n",
       "5914                                           S. Combs   \n",
       "5915                                B. Coad & F. Papahn   \n",
       "5916                                   R. Skocik, p.176   \n",
       "5917                                F. Dennis, pp.15-16   \n",
       "5918                Captain T. Falcon-Barker, pp. 91-93   \n",
       "5919                        Whitaker, The Sun, 1/3/1967   \n",
       "5920         C. Limbaugh in Sharks & Survival, pp.77-78   \n",
       "5921                   Sydney Morning Herald, 5/8/1965    \n",
       "5922                                B. Sojka & D. Lloyd   \n",
       "5923  W.H. Gregg, p.19;      L. Schultz & M. Malin, ...   \n",
       "5924  W.H. Gregg, p.22;      L. Schultz & M. Malin, ...   \n",
       "5925      G.P. Whitley (1952), p.192, cites A. Marshall   \n",
       "5926                        N. Caldwell; T. Peake, GSAF   \n",
       "5927                       V.M. Coppleson (1958), p.266   \n",
       "5965                                   M. N. Kalafatas    \n",
       "5969              L. Green in Tavern of the Seas, p.182   \n",
       "5970                     Adelaide Advertiser, 1/11/1940   \n",
       "5971        L. Green in South African Beachcomber, p.97   \n",
       "5979                       Rural New Yorker, 7/19/1913    \n",
       "\n",
       "                                      pdf  \\\n",
       "5899             ND-0110-FrenchDivers.pdf   \n",
       "5905                  ND-0102-Bahrein.pdf   \n",
       "5907                ND-0097-PaulMenta.pdf   \n",
       "5908           ND-0096-Zodiac-Reunion.pdf   \n",
       "5910                  ND-0094-HaeNyeo.pdf   \n",
       "5911                 ND-0093-Rangiroa.pdf   \n",
       "5913           ND-0090-Vanuatu-female.pdf   \n",
       "5914             ND-0089-VanuatuChief.pdf   \n",
       "5915              ND-0088-Jabar-Kaaby.pdf   \n",
       "5916             ND-0087-Carl-Bruster.pdf   \n",
       "5917                 ND-0086-DanHogan.pdf   \n",
       "5918                 ND-0085-JillReed.pdf   \n",
       "5919                    ND-0084-Sinsa.pdf   \n",
       "5920                  ND-0083-Fleming.pdf   \n",
       "5921             ND-0082-Volos-Greece.pdf   \n",
       "5922                ND-0081-Valentine.pdf   \n",
       "5923    ND-0078-canoeist-mail-carrier.pdf   \n",
       "5924            ND-0076-Mediterranean.pdf   \n",
       "5925             ND-0075-TorresStrait.pdf   \n",
       "5926                 ND-0074-Ted-Luck.pdf   \n",
       "5927   ND-0073-KeppelHarbourSingapore.pdf   \n",
       "5965           ND-0027-Psarofagomenos.pdf   \n",
       "5969                   ND-0023-Dalton.pdf   \n",
       "5970              ND-0022-Jaringoorli.pdf   \n",
       "5971          ND-0021-DurbanIndianBoy.pdf   \n",
       "5979  ND-0013-Durban-native-fisherman.pdf   \n",
       "\n",
       "                                           href formula  original order  \n",
       "5899  http://sharkattackfile.net/spreadsheets/pdf_di...              94  \n",
       "5905  http://sharkattackfile.net/spreadsheets/pdf_di...              88  \n",
       "5907  http://sharkattackfile.net/spreadsheets/pdf_di...              86  \n",
       "5908  http://sharkattackfile.net/spreadsheets/pdf_di...              85  \n",
       "5910  http://sharkattackfile.net/spreadsheets/pdf_di...              83  \n",
       "5911  http://sharkattackfile.net/spreadsheets/pdf_di...              82  \n",
       "5913  http://sharkattackfile.net/spreadsheets/pdf_di...              80  \n",
       "5914  http://sharkattackfile.net/spreadsheets/pdf_di...              79  \n",
       "5915  http://sharkattackfile.net/spreadsheets/pdf_di...              78  \n",
       "5916  http://sharkattackfile.net/spreadsheets/pdf_di...              77  \n",
       "5917  http://sharkattackfile.net/spreadsheets/pdf_di...              76  \n",
       "5918  http://sharkattackfile.net/spreadsheets/pdf_di...              75  \n",
       "5919  http://sharkattackfile.net/spreadsheets/pdf_di...              74  \n",
       "5920  http://sharkattackfile.net/spreadsheets/pdf_di...              73  \n",
       "5921  http://sharkattackfile.net/spreadsheets/pdf_di...              72  \n",
       "5922  http://sharkattackfile.net/spreadsheets/pdf_di...              71  \n",
       "5923  http://sharkattackfile.net/spreadsheets/pdf_di...              70  \n",
       "5924  http://sharkattackfile.net/spreadsheets/pdf_di...              69  \n",
       "5925  http://sharkattackfile.net/spreadsheets/pdf_di...              68  \n",
       "5926  http://sharkattackfile.net/spreadsheets/pdf_di...              67  \n",
       "5927  http://sharkattackfile.net/spreadsheets/pdf_di...              66  \n",
       "5965  http://sharkattackfile.net/spreadsheets/pdf_di...              28  \n",
       "5969  http://sharkattackfile.net/spreadsheets/pdf_di...              24  \n",
       "5970  http://sharkattackfile.net/spreadsheets/pdf_di...              23  \n",
       "5971  http://sharkattackfile.net/spreadsheets/pdf_di...              22  \n",
       "5979  http://sharkattackfile.net/spreadsheets/pdf_di...              14  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df[\"Case Number\"].str.contains(\"ND\")) & (df[\"Date\"].str.contains(\"No date\"))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The string \"ND\" present in the entries of \"Date\" means that there is no precise date. Also in these rows the value for \"Year\" is 0. If there is \"ND\" in \"Case Number\" and additionaly the string \"No date\" match with the value in \"Date\", we decide to drop the row.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "to_drop = list(df[(df[\"Case Number\"].str.contains(\"ND\")) & (df[\"Date\"].eq(\"No date\"))].index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(to_drop).copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we are going to replace some words as \"No Date\" and \"Before\" from the column \"Date\". As they are not a significative number of rows in the data set, we will maintain just the year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "rows = list(df[(df[\"Case Number\"].str.contains(\"ND\")) & (df[\"Date\"].str.match(\"No date\"))].index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>Case Number</th>\n",
       "      <th>Date</th>\n",
       "      <th>Year</th>\n",
       "      <th>Type</th>\n",
       "      <th>Country</th>\n",
       "      <th>Area</th>\n",
       "      <th>Location</th>\n",
       "      <th>Activity</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>Injury</th>\n",
       "      <th>Fatal (Y/N)</th>\n",
       "      <th>Species</th>\n",
       "      <th>Investigator or Source</th>\n",
       "      <th>pdf</th>\n",
       "      <th>href formula</th>\n",
       "      <th>original order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5979</th>\n",
       "      <td>ND.0013</td>\n",
       "      <td>No date (3 days after preceding incident) &amp; pr...</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>SOUTH AFRICA</td>\n",
       "      <td>KwaZulu-Natal</td>\n",
       "      <td>Durban</td>\n",
       "      <td>Fishing</td>\n",
       "      <td>a native fisherman</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FATAL, body not recovered but shark was caught...</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Rural New Yorker, 7/19/1913</td>\n",
       "      <td>ND-0013-Durban-native-fisherman.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Case Number                                               Date  Year  \\\n",
       "5979     ND.0013  No date (3 days after preceding incident) & pr...     0   \n",
       "\n",
       "            Type       Country           Area Location Activity  \\\n",
       "5979  Unprovoked  SOUTH AFRICA  KwaZulu-Natal   Durban  Fishing   \n",
       "\n",
       "                    Name Sex  Age  \\\n",
       "5979  a native fisherman   M  NaN   \n",
       "\n",
       "                                                 Injury Fatal (Y/N) Species  \\\n",
       "5979  FATAL, body not recovered but shark was caught...           Y     NaN   \n",
       "\n",
       "            Investigator or Source                                  pdf  \\\n",
       "5979  Rural New Yorker, 7/19/1913   ND-0013-Durban-native-fisherman.pdf   \n",
       "\n",
       "                                           href formula  original order  \n",
       "5979  http://sharkattackfile.net/spreadsheets/pdf_di...              14  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"Date\"] = df[\"Date\"].str.replace(\"No Date, Before\", \"\").copy()\n",
    "df[\"Date\"] = df[\"Date\"].str.replace(\"Before\", \"\").copy()\n",
    "df[\"Date\"] = df[\"Date\"].str.replace(\"No date,\", \"\").copy()\n",
    "df[(df[\"Case Number\"].str.contains(\"ND\")) & (df[\"Date\"].str.match(\"No date\"))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We drop this row which does not have relevant information\n",
    "df = df.drop(5979).copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "rows_missing = list(df[(df[\"Case Number\"].str.contains(\"ND.\")) & (df[\"Date\"].str.contains(\"Between\"))].index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "filling(df, rows_missing, [\"1957\", \"1928\"], \"Date\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We don't want to lose information about the Species, because we consider it very important. However, we want to drop the rows which not contain a valid date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rows which have information about Species but don't have a valid date and Case number with no date\n",
    "\n",
    "rows_missing = list(df[(df[\"Case Number\"].str.contains(\"ND\")) & (df[\"Species\"].isna()== False)].index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Seriescorresponding to column Date of the rows above\n",
    "\n",
    "dates = df[(df[\"Case Number\"].str.contains(\"ND\")) & (df[\"Species\"].isna()== False)][\"Date\"].copy()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we iterate over the index of dates and replace the values with valid dates heuristically. We take the first day of the year\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "year_pattern = r\"\\d{4}$\"\n",
    "\n",
    "year_s_pattern = r\"(\\d{4})(\\w{1})$\"\n",
    "\n",
    "for i in rows_missing:\n",
    "    year = re.findall(year_pattern, dates[i]) # for strings containing just a year\n",
    "    years = re.findall(year_s_pattern, dates[i]) #for strings containing XXXXs\n",
    "    if len(year) > 0:\n",
    "        dates[i] = year[0] +  \"-01-01\"\n",
    "    if len(years) > 0:\n",
    "        dates[i] = years[0][0] + \"-01-01\"\n",
    "        \n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filling the column Date with the values obtained above\n",
    "\n",
    "filling(df, list(dates.index), list(dates), \"Date\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "dates = df[df[\"Date\"].str.match(r\"\\d{4}$\")][\"Date\"].copy()\n",
    "rows_missing = list(df[df[\"Date\"].str.match(r\"\\d{4}$\")].index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in rows_missing:\n",
    "    year = re.findall(year_pattern, dates[i]) # for strings containing just a year\n",
    "    years = re.findall(year_s_pattern, dates[i]) #for strings containing XXXXs\n",
    "    if len(year) > 0:\n",
    "        dates[i] = year[0] +  \"-01-01\"\n",
    "    if len(years) > 0:\n",
    "        dates[i] = years[0][0] + \"-01-01\"       "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "filling(df, list(dates.index), list(dates), \"Date\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have almost clean the column \"Date\":"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df[\"Date\"].str.match(r\"\\d{4}$\")]\n",
    "df[\"Date\"] = df[\"Date\"].str.replace(\"Reported \", \"\").copy()\n",
    "df[\"Date\"] = df[\"Date\"].str.replace(\"Reported  \", \"\").copy()\n",
    "df[\"Date\"] = df[\"Date\"].str.lstrip(\"Circa\").copy()\n",
    "df[\"Date\"] = df[\"Date\"].str.lstrip().copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again we repeat the process above with these last changes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "dates = df[df[\"Date\"].str.match(r\"(\\d{4})(\\w{1})$\")][\"Date\"].copy()\n",
    "rows_missing = list(dates.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in rows_missing:\n",
    "    years = re.findall(year_s_pattern, dates[i]) #for strings containing XXXXs\n",
    "    if len(years) > 0:\n",
    "        dates[i] = years[0][0] + \"-01-01\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "filling(df, rows_missing, list(dates), \"Date\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"Date\"] = pd.to_datetime(df[\"Date\"], errors = \"coerce\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "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>Case Number</th>\n",
       "      <th>Date</th>\n",
       "      <th>Year</th>\n",
       "      <th>Type</th>\n",
       "      <th>Country</th>\n",
       "      <th>Area</th>\n",
       "      <th>Location</th>\n",
       "      <th>Activity</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>Injury</th>\n",
       "      <th>Fatal (Y/N)</th>\n",
       "      <th>Species</th>\n",
       "      <th>Investigator or Source</th>\n",
       "      <th>pdf</th>\n",
       "      <th>href formula</th>\n",
       "      <th>original order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5896</th>\n",
       "      <td>nd-0114</td>\n",
       "      <td>2012-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>INDIA</td>\n",
       "      <td>Bengal</td>\n",
       "      <td>In a river feeding into the Bay of Bengal</td>\n",
       "      <td>Netting shrimp</td>\n",
       "      <td>Sametra Mestri</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Hand severed</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>National Georgraphic Television</td>\n",
       "      <td>ND-0114-BayOfBengal.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016.09.18.c</td>\n",
       "      <td>2016-09-18</td>\n",
       "      <td>2016</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>USA</td>\n",
       "      <td>Florida</td>\n",
       "      <td>New Smyrna Beach, Volusia County</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>male</td>\n",
       "      <td>M</td>\n",
       "      <td>16</td>\n",
       "      <td>Minor injury to thigh</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Orlando Sentinel, 9/19/2016</td>\n",
       "      <td>2016.09.18.c-NSB.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>5993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016.09.18.b</td>\n",
       "      <td>2016-09-18</td>\n",
       "      <td>2016</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>USA</td>\n",
       "      <td>Florida</td>\n",
       "      <td>New Smyrna Beach, Volusia County</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>Chucky Luciano</td>\n",
       "      <td>M</td>\n",
       "      <td>36</td>\n",
       "      <td>Lacerations to hands</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Orlando Sentinel, 9/19/2016</td>\n",
       "      <td>2016.09.18.b-Luciano.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>5992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016.09.18.a</td>\n",
       "      <td>2016-09-18</td>\n",
       "      <td>2016</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>USA</td>\n",
       "      <td>Florida</td>\n",
       "      <td>New Smyrna Beach, Volusia County</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>male</td>\n",
       "      <td>M</td>\n",
       "      <td>43</td>\n",
       "      <td>Lacerations to lower leg</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Orlando Sentinel, 9/19/2016</td>\n",
       "      <td>2016.09.18.a-NSB.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>5991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016.09.17</td>\n",
       "      <td>2016-09-17</td>\n",
       "      <td>2016</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>AUSTRALIA</td>\n",
       "      <td>Victoria</td>\n",
       "      <td>Thirteenth Beach</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>Rory Angiolella</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Struck by fin on chest &amp; leg</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>The Age, 9/18/2016</td>\n",
       "      <td>2016.09.17-Angiolella.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>5990</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Case Number       Date  Year        Type    Country      Area  \\\n",
       "5896       nd-0114 2012-01-01     0  Unprovoked      INDIA    Bengal   \n",
       "0     2016.09.18.c 2016-09-18  2016  Unprovoked        USA   Florida   \n",
       "1     2016.09.18.b 2016-09-18  2016  Unprovoked        USA   Florida   \n",
       "2     2016.09.18.a 2016-09-18  2016  Unprovoked        USA   Florida   \n",
       "3       2016.09.17 2016-09-17  2016  Unprovoked  AUSTRALIA  Victoria   \n",
       "\n",
       "                                       Location        Activity  \\\n",
       "5896  In a river feeding into the Bay of Bengal  Netting shrimp   \n",
       "0              New Smyrna Beach, Volusia County         Surfing   \n",
       "1              New Smyrna Beach, Volusia County         Surfing   \n",
       "2              New Smyrna Beach, Volusia County         Surfing   \n",
       "3                              Thirteenth Beach         Surfing   \n",
       "\n",
       "                 Name Sex  Age                        Injury Fatal (Y/N)  \\\n",
       "5896   Sametra Mestri   F  NaN                  Hand severed           N   \n",
       "0                male   M   16         Minor injury to thigh           N   \n",
       "1      Chucky Luciano   M   36          Lacerations to hands           N   \n",
       "2                male   M   43      Lacerations to lower leg           N   \n",
       "3     Rory Angiolella   M  NaN  Struck by fin on chest & leg           N   \n",
       "\n",
       "     Species           Investigator or Source                        pdf  \\\n",
       "5896     NaN  National Georgraphic Television    ND-0114-BayOfBengal.pdf   \n",
       "0        NaN      Orlando Sentinel, 9/19/2016       2016.09.18.c-NSB.pdf   \n",
       "1        NaN      Orlando Sentinel, 9/19/2016   2016.09.18.b-Luciano.pdf   \n",
       "2        NaN      Orlando Sentinel, 9/19/2016       2016.09.18.a-NSB.pdf   \n",
       "3        NaN               The Age, 9/18/2016  2016.09.17-Angiolella.pdf   \n",
       "\n",
       "                                           href formula  original order  \n",
       "5896  http://sharkattackfile.net/spreadsheets/pdf_di...              97  \n",
       "0     http://sharkattackfile.net/spreadsheets/pdf_di...            5993  \n",
       "1     http://sharkattackfile.net/spreadsheets/pdf_di...            5992  \n",
       "2     http://sharkattackfile.net/spreadsheets/pdf_di...            5991  \n",
       "3     http://sharkattackfile.net/spreadsheets/pdf_di...            5990  "
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Filtering the greatest real date: Notice that the info is contained in the column Case Number\n",
    "\n",
    "df[~(df[\"Case Number\"].str.match(\"ND\"))].sort_values(by = \"Case Number\", ascending = False).head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We realize that in the column \"Date\" there are unrealistic dates, way after the greatest real date: \"2016-09-18\": "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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>Case Number</th>\n",
       "      <th>Date</th>\n",
       "      <th>Year</th>\n",
       "      <th>Type</th>\n",
       "      <th>Country</th>\n",
       "      <th>Area</th>\n",
       "      <th>Location</th>\n",
       "      <th>Activity</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>Injury</th>\n",
       "      <th>Fatal (Y/N)</th>\n",
       "      <th>Species</th>\n",
       "      <th>Investigator or Source</th>\n",
       "      <th>pdf</th>\n",
       "      <th>href formula</th>\n",
       "      <th>original order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3852</th>\n",
       "      <td>1959.08.02</td>\n",
       "      <td>2176-01-01</td>\n",
       "      <td>1959</td>\n",
       "      <td>Invalid</td>\n",
       "      <td>ITALY</td>\n",
       "      <td>Tuscany</td>\n",
       "      <td>Cala del Corvo, Isola del Giglio</td>\n",
       "      <td>Scuba diving</td>\n",
       "      <td>Karl Pollerer &amp; Eric Eisesenid</td>\n",
       "      <td>M</td>\n",
       "      <td>34 &amp; 19</td>\n",
       "      <td>Probable drowing. Shark involvement unconfirmed</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C. Moore, GSAF</td>\n",
       "      <td>1959.08.02-Giglio.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3220</th>\n",
       "      <td>1968.12.29</td>\n",
       "      <td>2068-12-29</td>\n",
       "      <td>1968</td>\n",
       "      <td>Invalid</td>\n",
       "      <td>SOUTH AFRICA</td>\n",
       "      <td>KwaZulu-Natal</td>\n",
       "      <td>Port St. John's</td>\n",
       "      <td>Freediving</td>\n",
       "      <td>John Domoney</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No injury</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>H.D.Baldridge (1994) SAF Case #1588. Note: Una...</td>\n",
       "      <td>1968.12.29-NV-Domoney.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3221</th>\n",
       "      <td>1968.12.26</td>\n",
       "      <td>2068-12-26</td>\n",
       "      <td>1968</td>\n",
       "      <td>Provoked</td>\n",
       "      <td>AUSTRALIA</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>Marineland Aquarium, Manley, Sydney</td>\n",
       "      <td>Feeding mullet to sharks</td>\n",
       "      <td>Peter Jones</td>\n",
       "      <td>M</td>\n",
       "      <td>27</td>\n",
       "      <td>Laceration to finger by a captive shark PROVOK...</td>\n",
       "      <td>N</td>\n",
       "      <td>Grey nurse shark, 10'</td>\n",
       "      <td>The Age, 12/27/1968</td>\n",
       "      <td>1968.12.26-Jones.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3222</th>\n",
       "      <td>1968.12.25</td>\n",
       "      <td>2068-12-25</td>\n",
       "      <td>1968</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>NEW ZEALAND</td>\n",
       "      <td>South Island</td>\n",
       "      <td>St. Clair Beach, Dunedin</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>Gary Barton</td>\n",
       "      <td>M</td>\n",
       "      <td>17</td>\n",
       "      <td>Hit in face by shark, arm abraded, surfboard b...</td>\n",
       "      <td>N</td>\n",
       "      <td>White shark</td>\n",
       "      <td>R. D. Weeks, GSAF; Otago Daily Times, 12/26/1968</td>\n",
       "      <td>1968.12.25-Barton.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3223</th>\n",
       "      <td>1968.12.09</td>\n",
       "      <td>2068-12-09</td>\n",
       "      <td>1968</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>AUSTRALIA</td>\n",
       "      <td>South Australia</td>\n",
       "      <td>Thistle Island</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dick OBrien</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Survived</td>\n",
       "      <td>N</td>\n",
       "      <td>White shark</td>\n",
       "      <td>T. Peake, GSAF</td>\n",
       "      <td>1968.12.09-O'Brien.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2770</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Case Number       Date  Year        Type       Country             Area  \\\n",
       "3852  1959.08.02 2176-01-01  1959     Invalid         ITALY          Tuscany   \n",
       "3220  1968.12.29 2068-12-29  1968     Invalid  SOUTH AFRICA    KwaZulu-Natal   \n",
       "3221  1968.12.26 2068-12-26  1968    Provoked     AUSTRALIA  New South Wales   \n",
       "3222  1968.12.25 2068-12-25  1968  Unprovoked   NEW ZEALAND     South Island   \n",
       "3223  1968.12.09 2068-12-09  1968  Unprovoked     AUSTRALIA  South Australia   \n",
       "\n",
       "                                 Location                  Activity  \\\n",
       "3852     Cala del Corvo, Isola del Giglio              Scuba diving   \n",
       "3220                      Port St. John's                Freediving   \n",
       "3221  Marineland Aquarium, Manley, Sydney  Feeding mullet to sharks   \n",
       "3222             St. Clair Beach, Dunedin                   Surfing   \n",
       "3223                       Thistle Island                       NaN   \n",
       "\n",
       "                                Name Sex      Age  \\\n",
       "3852  Karl Pollerer & Eric Eisesenid   M  34 & 19   \n",
       "3220                    John Domoney   M      NaN   \n",
       "3221                     Peter Jones   M       27   \n",
       "3222                     Gary Barton   M       17   \n",
       "3223                    Dick OBrien   M      NaN   \n",
       "\n",
       "                                                 Injury Fatal (Y/N)  \\\n",
       "3852    Probable drowing. Shark involvement unconfirmed           Y   \n",
       "3220                                          No injury           N   \n",
       "3221  Laceration to finger by a captive shark PROVOK...           N   \n",
       "3222  Hit in face by shark, arm abraded, surfboard b...           N   \n",
       "3223                                           Survived           N   \n",
       "\n",
       "                     Species  \\\n",
       "3852                     NaN   \n",
       "3220                     NaN   \n",
       "3221  Grey nurse shark, 10'    \n",
       "3222             White shark   \n",
       "3223             White shark   \n",
       "\n",
       "                                 Investigator or Source  \\\n",
       "3852                                     C. Moore, GSAF   \n",
       "3220  H.D.Baldridge (1994) SAF Case #1588. Note: Una...   \n",
       "3221                                The Age, 12/27/1968   \n",
       "3222   R. D. Weeks, GSAF; Otago Daily Times, 12/26/1968   \n",
       "3223                                     T. Peake, GSAF   \n",
       "\n",
       "                            pdf  \\\n",
       "3852      1959.08.02-Giglio.pdf   \n",
       "3220  1968.12.29-NV-Domoney.pdf   \n",
       "3221       1968.12.26-Jones.pdf   \n",
       "3222      1968.12.25-Barton.pdf   \n",
       "3223     1968.12.09-O'Brien.pdf   \n",
       "\n",
       "                                           href formula  original order  \n",
       "3852  http://sharkattackfile.net/spreadsheets/pdf_di...            2141  \n",
       "3220  http://sharkattackfile.net/spreadsheets/pdf_di...            2773  \n",
       "3221  http://sharkattackfile.net/spreadsheets/pdf_di...            2772  \n",
       "3222  http://sharkattackfile.net/spreadsheets/pdf_di...            2771  \n",
       "3223  http://sharkattackfile.net/spreadsheets/pdf_di...            2770  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from datetime import date \n",
    "\n",
    "df[df[\"Date\"]> \"2016.09.18\"].sort_values(by = \"Date\", ascending = False).head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we put in the right way all the dates exceding \"2016.09.18\"; notice that all of them are 100 years over the real date.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "bad_dates = df[df[\"Date\"]> \"2016.09.18\"].copy()\n",
    "\n",
    "bad_dates[\"Date\"] = bad_dates['Date'].apply(lambda x: x.replace(x.year - 100))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "rows_missing = list(bad_dates.index)\n",
    "filling(df, rows_missing, list(bad_dates.Date), \"Date\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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>Case Number</th>\n",
       "      <th>Date</th>\n",
       "      <th>Year</th>\n",
       "      <th>Type</th>\n",
       "      <th>Country</th>\n",
       "      <th>Area</th>\n",
       "      <th>Location</th>\n",
       "      <th>Activity</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>Injury</th>\n",
       "      <th>Fatal (Y/N)</th>\n",
       "      <th>Species</th>\n",
       "      <th>Investigator or Source</th>\n",
       "      <th>pdf</th>\n",
       "      <th>href formula</th>\n",
       "      <th>original order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3852</th>\n",
       "      <td>1959.08.02</td>\n",
       "      <td>2076-01-01</td>\n",
       "      <td>1959</td>\n",
       "      <td>Invalid</td>\n",
       "      <td>ITALY</td>\n",
       "      <td>Tuscany</td>\n",
       "      <td>Cala del Corvo, Isola del Giglio</td>\n",
       "      <td>Scuba diving</td>\n",
       "      <td>Karl Pollerer &amp; Eric Eisesenid</td>\n",
       "      <td>M</td>\n",
       "      <td>34 &amp; 19</td>\n",
       "      <td>Probable drowing. Shark involvement unconfirmed</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C. Moore, GSAF</td>\n",
       "      <td>1959.08.02-Giglio.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2141</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Case Number       Date  Year     Type Country     Area  \\\n",
       "3852  1959.08.02 2076-01-01  1959  Invalid   ITALY  Tuscany   \n",
       "\n",
       "                              Location      Activity  \\\n",
       "3852  Cala del Corvo, Isola del Giglio  Scuba diving   \n",
       "\n",
       "                                Name Sex      Age  \\\n",
       "3852  Karl Pollerer & Eric Eisesenid   M  34 & 19   \n",
       "\n",
       "                                               Injury Fatal (Y/N) Species  \\\n",
       "3852  Probable drowing. Shark involvement unconfirmed           Y     NaN   \n",
       "\n",
       "     Investigator or Source                    pdf  \\\n",
       "3852         C. Moore, GSAF  1959.08.02-Giglio.pdf   \n",
       "\n",
       "                                           href formula  original order  \n",
       "3852  http://sharkattackfile.net/spreadsheets/pdf_di...            2141  "
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df[\"Date\"]> \"2016.09.18\"].sort_values(by = \"Date\", ascending = False).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We fill manually the correct date for this entry:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "filling(df, [3852], [pd.Timestamp(\"1959.08.02\")], \"Date\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we drop rows which do not have valuable information about the date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "rows_to_drop = list(df[(df[\"Date\"].isna()) & (df[\"Case Number\"].str.contains(\"ND\"))].index)\n",
    "df = df.drop(rows_to_drop, axis =0).copy()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Still there are rows with unreallistic values for date. Now we use regex to obtain good data values at the column  \"Case Number\":"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "bad_dates = df[(df[\"Date\"].isna())].copy()\n",
    "bad_dates[\"Case Number\"] = bad_dates[\"Case Number\"].str.replace(\".[a-z]\", \"\").copy()\n",
    "bad_dates[\"Case Number\"] = bad_dates[\"Case Number\"].str.replace(\".[A-Z]\", \"\").copy()\n",
    "bad_dates[\"Case Number\"] = bad_dates[\"Case Number\"].str.replace(\".\", \"\").copy()\n",
    "bad_dates[\"Case Number\"] = bad_dates[\"Case Number\"].str.replace(\"0000\", \"0101\").copy()\n",
    "bad_dates_2 = bad_dates[bad_dates[\"Case Number\"].str.contains(\"00$\")].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This function repairs the dates ending in \"00\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "def f(x):\n",
    "    x = x[:-2]\n",
    "    return x + \"01\"\n",
    "\n",
    "bad_dates_2[\"Case Number\"] = bad_dates['Case Number'].apply(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Parsing the dates\n",
    "\n",
    "bad_dates_2[\"Case Number\"] = pd.to_datetime(bad_dates_2[\"Case Number\"], errors = \"coerce\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We use the column \"Case Number\" to fill in a suitable way the \"Date\" column:\n",
    "bad_dates_2[\"Date\"] = bad_dates_2[\"Case Number\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "rows_missing = list(bad_dates_2.index)\n",
    "filling(bad_dates, rows_missing, list(bad_dates_2[\"Date\"]), \"Date\")\n",
    "bad_dates[\"Case Number\"] = pd.to_datetime(bad_dates[\"Case Number\"], errors = \"coerce\")\n",
    "rows_missing= list(bad_dates.index)\n",
    "filling(df, rows_missing, list(bad_dates[\"Case Number\"]), \"Date\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will drop all the rows with no date and no species\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "rows_to_drop = list(df[df.Date.isna() & df.Species.isna()].index)\n",
    "df = df.drop(rows_to_drop, axis=0)\n",
    "bad_dates = df[df.Date.isna()].copy()\n",
    "bad_dates[\"Case Number\"] = bad_dates['Case Number'].apply(f)\n",
    "bad_dates[\"Date\"] = pd.to_datetime(bad_dates[\"Case Number\"], errors = \"coerce\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "rows_missing= list(bad_dates.index)\n",
    "filling(df, rows_missing, list(bad_dates[\"Date\"]), \"Date\")\n",
    "bad_dates = df[df.Date.isna()].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we define a function similar to *f* which let us put in a good way the column \"Date\" without droping values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "def g(x):\n",
    "    if re.search(\"00$\", x):\n",
    "        x = x[:-4]\n",
    "        return x+ \"1.01\"\n",
    "    else:\n",
    "        x = x[:-4]\n",
    "        if re.search(\"00.$\", x):\n",
    "            x = x[:-2]\n",
    "            return x + \"1.01\"\n",
    "        else:\n",
    "            return x + \"01\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "bad_dates[\"Date\"] = bad_dates['Case Number'].apply(g).copy()\n",
    "bad_dates[\"Date\"] = pd.to_datetime(bad_dates[\"Date\"], errors = \"coerce\").copy()\n",
    "rows_missing= list(bad_dates.index)\n",
    "filling(df, rows_missing, list(bad_dates[\"Date\"]), \"Date\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "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>Case Number</th>\n",
       "      <th>Date</th>\n",
       "      <th>Year</th>\n",
       "      <th>Type</th>\n",
       "      <th>Country</th>\n",
       "      <th>Area</th>\n",
       "      <th>Location</th>\n",
       "      <th>Activity</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>Injury</th>\n",
       "      <th>Fatal (Y/N)</th>\n",
       "      <th>Species</th>\n",
       "      <th>Investigator or Source</th>\n",
       "      <th>pdf</th>\n",
       "      <th>href formula</th>\n",
       "      <th>original order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5863</th>\n",
       "      <td>1554.00.00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>1554</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>FRANCE</td>\n",
       "      <td>Nice &amp; Marseilles</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>males (wearing armor)</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>Possibly white sharks</td>\n",
       "      <td>G. Rondelet</td>\n",
       "      <td>1554.00.00-Rondelet.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>130</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Case Number Date  Year        Type Country               Area Location  \\\n",
       "5863  1554.00.00  NaT  1554  Unprovoked  FRANCE  Nice & Marseilles      NaN   \n",
       "\n",
       "     Activity                   Name Sex  Age Injury Fatal (Y/N)  \\\n",
       "5863      NaN  males (wearing armor)   M  NaN    NaN     UNKNOWN   \n",
       "\n",
       "                    Species Investigator or Source                      pdf  \\\n",
       "5863  Possibly white sharks           G. Rondelet   1554.00.00-Rondelet.pdf   \n",
       "\n",
       "                                           href formula  original order  \n",
       "5863  http://sharkattackfile.net/spreadsheets/pdf_di...             130  "
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.Date.isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We drop this entry:\n",
    "\n",
    "df= df.drop([5863], axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.2. Droping the column \"Case Number\", \"Year\" and \"original order\"\n",
    "\n",
    "Now, we have suitable values at the column \"Date\". We can then drop the column \"Case Number\",\"Year\" and \"original order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(columns = [\"Case Number\", \"Year\", \"original order\"], inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.3. Filling values for \"Name\" and \"Investigator or Source\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Also, we will fill the NaN's at the column \"Name\" as \"Anonymous\", and the \"Investigator or Source\" field as \"Unknown\":"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"Name\"].fillna(\"Anonymous\", inplace = True) \n",
    "df[\"Investigator or Source\"].fillna(\"Unknown\", inplace = True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Area         6.148867\n",
       "Location     7.579629\n",
       "Activity     8.550502\n",
       "Sex          9.419179\n",
       "Age         43.893715\n",
       "Injury       0.340657\n",
       "Species     48.032703\n",
       "dtype: float64"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_cols(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.4. Columns \"Age\" and \"Activity\"\n",
    "\n",
    "The field \"Age\" might be relevant, so we are not going to drop these column. However, we suggest this is in correlation with the field \"Activity\".\n",
    "\n",
    "#### 4.4.1. Column \"Age\" conversion type\n",
    "We define a function to recover most of the dates and convert them into integers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['16', '36', '43', '60s', '51', '50', '12', '9', '22', '25', '37',\n",
       "       '20', '49', '15', '21', '40', '72', '18', '29', '31', '11', '10',\n",
       "       '59', '42', '34', '35', '19', '6', '27', '64', '60', '23', '52',\n",
       "       '13', '57', '48', '39', '24', '26', '69', '46', 'Teen', '41', '45',\n",
       "       '65', '38', '71', '32', '58', '28', '54', '44', '14', '7', '62',\n",
       "       '40s', '68', '47', '17', '30', '63', '70', '18 months', '53',\n",
       "       '20s', '33', '30s', '50s', '8', '61', '55', 'teen', '66', '77',\n",
       "       '74', '3', '56', '28 & 26', '5', '86', '18 or 20', '12 or 13',\n",
       "       '46 & 34', '28, 23 & 30', 'Teens', '36 & 26', '8 or 10', '84',\n",
       "       '\\xa0 ', ' ', '30 or 36', '6½', '21 & ?', '75', '33 or 37',\n",
       "       'mid-30s', '73', '23 & 20', '7      &    31', '20?', \"60's\",\n",
       "       '32 & 30', '16 to 18', '87', '67', 'Elderly', 'mid-20s', 'Ca. 33',\n",
       "       '21 or 26', '>50', '18 to 22', 'adult', '9 & 12', '? & 19',\n",
       "       '9 months', '25 to 35', '23 & 26', '1', '(adult)', '33 & 37',\n",
       "       '25 or 28', '37, 67, 35, 27,  ? & 27', '21, 34,24 & 35', '30 & 32',\n",
       "       '50 & 30', '17 & 35', 'X', '\"middle-age\"', '13 or 18', '34 & 19',\n",
       "       '33 & 26', '2 to 3 months', 'MAKE LINE GREEN', '81', '\"young\"',\n",
       "       '7 or 8', '78', '17 & 16', 'F', 'Both 11', '9 or 10', 'young',\n",
       "       '36 & 23', '  ', 'A.M.', '?    &   14', '10 or 12', '31 or 33',\n",
       "       'M', '2½', '13 or 14'], dtype=object)"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.Age.isna()== False].Age.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "def extract_ages(word):\n",
    "    \n",
    "    \"\"\"\n",
    "    This function extract the numeric values contained in the field \"Age\".\n",
    "    word is a string and it returns a float or NaN\n",
    "    \"\"\"\n",
    "    \n",
    "    if  re.findall(r\"adult\", str(word)):\n",
    "        return 35\n",
    "    elif re.findall(r\"teen\", str(word).lower()):\n",
    "        return 15\n",
    "    elif re.findall(r\"young\", str(word)):\n",
    "        return 25\n",
    "    elif re.findall(r\"middle-age\", str(word)):\n",
    "        return 45\n",
    "    elif re.findall(r\"Elderly\", str(word)):\n",
    "        return 75\n",
    "    elif re.findall(r\"mid\", str(word)):\n",
    "        return int(re.findall(r\"\\d{1,2}\", str(word))[0])\n",
    "    elif re.findall(r\"month\", str(word)):\n",
    "        return math.floor(int(re.findall(r\"\\d{1,2}\", str(word))[0])/12)\n",
    "    elif re.findall(r\"or|&|to|½\", str(word)):\n",
    "        return int(re.findall(r\"\\d{1,2}\", str(word))[0])\n",
    "    elif re.findall(r\"s\", str(word)):\n",
    "        return int(re.findall(r\"\\d{1,2}\", str(word))[0])+5\n",
    "    else:\n",
    "        return pd.to_numeric(word, errors = \"coerce\")\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"Age\"] = df[\"Age\"].apply(extract_ages)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.4.2. Tiding column \"Activity\"\n",
    "\n",
    "* First, we remove the empty spaces at the end of the strings of the field \"Activity\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"Activity\"] = df[\"Activity\"].str.rstrip().copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From the description of \"Activity\" we want to extract the first verb in gerund contained in it and identify the Activity with it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Pattern for verb in gerund\n",
    "\n",
    "lista = list(df[df.Activity.isna()== False].index)\n",
    "\n",
    "ing = r'\\b(\\w+ing)\\b'\n",
    "\n",
    "#Here we use list comprehension to obtain the capitalized actions:\n",
    "\n",
    "to_fill= [re.findall(ing, df.Activity[ind])[0].capitalize() if re.findall(ing, df.Activity[ind]) else df.Activity[ind] for ind in lista]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Replacing the values in the column of the data frame.\n",
    "\n",
    "filling(df, lista, to_fill, \"Activity\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.4.3. Predicting values for Age and Activity"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This function shows us the distribution through the top categories in column \"Y\" of the most frequent value at the column \"X\":"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "def predictor_0(Y, X, data):\n",
    "    \n",
    "    \"\"\"\n",
    "    This function gives us a pair (most_X, dataframe) showing the distribution of frequencies\n",
    "    of X over the most frequent Y of the top n.\n",
    "    \n",
    "    X and Y are columns of a dataframe data, and n is an integer which defines the rank that we give\n",
    "    to the column Y.\n",
    "    \"\"\"\n",
    "    most_freq_Y = list(data[Y].value_counts().index)\n",
    "    data_filter = data[(data[Y].isin(most_freq_Y)) & (df[X].isna()== False)][[Y, X]][X].value_counts()\n",
    "    most_X = list(data_filter.index)[0]\n",
    "    return (most_X, data[(data[Y].isin(most_freq_Y)) & (data[X]== most_X)][[Y, X]][Y].value_counts())\n",
    "\n",
    "\n",
    "# for i in list(data_filter.index)\n",
    "\n",
    "# a.append((i, data[(data[Y].isin(most_freq_Y)) & (data[X]== i)][[Y, X]][Y].value_counts())[1].index[0])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following indicates that for the values in the column \"Activity\", the most popular age is 15 years old and the distribution of the totals for people with this Age for the other activities is as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(15.0, Surfing           53\n",
       " Swimming          30\n",
       " Standing          10\n",
       " Boarding           9\n",
       " Wading             7\n",
       " Spearfishing       6\n",
       " Fishing            5\n",
       " Diving             4\n",
       " Bathing            3\n",
       " Treading           2\n",
       " Splashing          2\n",
       " Freediving         2\n",
       " Snorkeling         2\n",
       " Clamming           1\n",
       " Paddling           1\n",
       " Playing            1\n",
       " Fell overboard     1\n",
       " Holding            1\n",
       " Lying              1\n",
       " Walking            1\n",
       " Sitting            1\n",
       " Sea Disaster       1\n",
       " Name: Activity, dtype: int64)"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "predictor_0(\"Activity\", \"Age\", df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "def predictor(Y, X, data):\n",
    "    \n",
    "    \"\"\"\n",
    "    X and Y are two variables correlated.\n",
    "    This function gives us a dictionary whose keys are the values of column X and for each value in\n",
    "    give us the more frequent value of Y.\n",
    "   \n",
    "    \"\"\"\n",
    "    most_freq_Y = list(data[Y].value_counts().index)\n",
    "    data_filter = data[(data[Y].isin(most_freq_Y)) & (df[X].isna()== False)][[Y, X]][X].value_counts()\n",
    "    a= dict()\n",
    "    for i in list(data_filter.index):\n",
    "        a[i] = data[(data[Y].isin(most_freq_Y)) & (data[X]== i)][[Y, X]][Y].value_counts().index[0]\n",
    "    return a\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is possible to infer with this dictionary the values for \"Age\" in the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "predictions = predictor(\"Age\", \"Activity\", df)\n",
    "\n",
    "df_2 = df[(df.Activity.isna()== False) & (df.Age.isna())]    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then, we use our dictionary of predictions in order to fill the NaN values of \"Age\" who has non NaN's values in the column \"Activity\":"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "lista= list(df_2.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "to_fill = [predictions[df.Activity[ind]] if df.Activity[ind] in predictions.keys() else df.Age[ind] for ind in lista ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "filling(df, lista, to_fill, \"Age\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "lista= (df[(df.Age.isna()) & (df.Activity.isna()== False)].index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once again, we see how much we are decreasing the NaN values in the dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Area         6.148867\n",
       "Location     7.579629\n",
       "Activity     8.550502\n",
       "Sex          9.419179\n",
       "Age         12.008176\n",
       "Injury       0.340657\n",
       "Species     48.032703\n",
       "dtype: float64"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_cols(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we are going to predict the values in \"Activity\" based on the known values of \"Age\"; we do a dual procedure:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "lista = list(df[(df.Age.isna()== False) & df.Activity.isna()].index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "predictions = predictor(\"Activity\", \"Age\", df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "to_fill = [predictions[df.Age[ind]] if df.Age[ind] in predictions.keys() else df.Activity[ind] for ind in lista ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "filling(df, lista, to_fill, \"Activity\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Area         6.148867\n",
       "Location     7.579629\n",
       "Activity     6.285130\n",
       "Sex          9.419179\n",
       "Age         12.008176\n",
       "Injury       0.340657\n",
       "Species     48.032703\n",
       "dtype: float64"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_cols(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.5. Filling values for column \"Species\". \n",
    "\n",
    "It might be possible to infer the species from the Country and Area but we do not how to reach this goal. We suggest that there is a correlation between these variables but for now we will fill\n",
    "this NaN's with \"Unidentified\":"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"Species\"].fillna(\"Unidentified\", inplace = True) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.6. Droping irrelevant rows based on NaN's counting by columns\n",
    "\n",
    "So far, we have significatively decreased the number of NaN's in the columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Area         6.148867\n",
       "Location     7.579629\n",
       "Activity     6.285130\n",
       "Sex          9.419179\n",
       "Age         12.008176\n",
       "Injury       0.340657\n",
       "dtype: float64"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_cols(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As \"Injury\" has a pretty low number of NaN rows, we can drop them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [],
   "source": [
    "to_drop = list(df[(df.Injury.isna())].index)\n",
    "df = df.drop(to_drop)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Area         6.135703\n",
       "Location     7.554264\n",
       "Activity     6.204068\n",
       "Sex          9.280465\n",
       "Age         11.912494\n",
       "dtype: float64"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_cols(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.6.1. Predicting values for Area and Location\n",
    "\n",
    "We will predict the value of the missing rows in \"Area\" using the column \"Country\". Similarly, we predict the value of \"Location\"\n",
    "by using the column \"Area\":"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "lista = list(df[df.Area.isna()].index)\n",
    "predictions = predictor(\"Area\", \"Country\", df)\n",
    "to_fill = [predictions[df.Country[ind]] if df.Country[ind] in predictions.keys() else df.Area[ind] for ind in lista ]\n",
    "filling(df, lista, to_fill, \"Area\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "lista = list(df[(df.Location.isna()) & (df.Area.isna()== False)].index)\n",
    "predictions = predictor(\"Location\", \"Area\", df)\n",
    "to_fill = [predictions[df.Area[ind]] if df.Area[ind] in predictions.keys() else df.Location[ind] for ind in lista ]\n",
    "filling(df, lista, to_fill, \"Location\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are some countries and Areas with misspellings; we correct those entries:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.replace(\"COLUMBIA\", \"COLOMBIA\")\n",
    "df = df.replace(\"Isla provedencia\", \"Isla Providencia\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Area         0.752008\n",
       "Location     2.409844\n",
       "Activity     6.204068\n",
       "Sex          9.280465\n",
       "Age         11.912494\n",
       "dtype: float64"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_cols(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can drop the rest of the rows with NaN's values in Area because are <1% of the total."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Location     1.963148\n",
       "Activity     6.164973\n",
       "Sex          9.264681\n",
       "Age         11.727226\n",
       "dtype: float64"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.dropna(subset=['Area'])\n",
    "null_cols(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.6.2. Assing the values fo column \"Fatal (Y/N) \" to boolean\n",
    "\n",
    "If we have \"Y\" assing True and \"N\" assign False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"Fatal (Y/N)\"]= (df[\"Fatal (Y/N)\"] == \"Y\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. Changing column names and Re-indexing\n",
    "\n",
    "We will rename the columns with more descriptive names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Date', 'Type', 'Country', 'Area', 'Location', 'Activity', 'Name',\n",
       "       'Sex', 'Age', 'Injury', 'Fatal (Y/N)', 'Species',\n",
       "       'Investigator or Source', 'pdf', 'href formula'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = [\"Date\", \"Type\", 'Country', \"Area\", \"Location\", \"Activity_during_attack\", \"Victim_Name\", \"Gender\", \"Age\", \"Injury\", \"Fatal_attack\", \"Shark_species\", \"Source_information\", \"Documentation_pdf\", \"Documentation_href\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we order the data set by \"Date\" in descending order, and also we reindex the rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "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>Date</th>\n",
       "      <th>Type</th>\n",
       "      <th>Country</th>\n",
       "      <th>Area</th>\n",
       "      <th>Location</th>\n",
       "      <th>Activity_during_attack</th>\n",
       "      <th>Victim_Name</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Age</th>\n",
       "      <th>Injury</th>\n",
       "      <th>Fatal_attack</th>\n",
       "      <th>Shark_species</th>\n",
       "      <th>Source_information</th>\n",
       "      <th>Documentation_pdf</th>\n",
       "      <th>Documentation_href</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-09-18</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>USA</td>\n",
       "      <td>Florida</td>\n",
       "      <td>New Smyrna Beach, Volusia County</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>male</td>\n",
       "      <td>M</td>\n",
       "      <td>16.0</td>\n",
       "      <td>Minor injury to thigh</td>\n",
       "      <td>False</td>\n",
       "      <td>Unidentified</td>\n",
       "      <td>Orlando Sentinel, 9/19/2016</td>\n",
       "      <td>2016.09.18.c-NSB.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-09-18</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>USA</td>\n",
       "      <td>Florida</td>\n",
       "      <td>New Smyrna Beach, Volusia County</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>male</td>\n",
       "      <td>M</td>\n",
       "      <td>43.0</td>\n",
       "      <td>Lacerations to lower leg</td>\n",
       "      <td>False</td>\n",
       "      <td>Unidentified</td>\n",
       "      <td>Orlando Sentinel, 9/19/2016</td>\n",
       "      <td>2016.09.18.a-NSB.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-09-18</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>USA</td>\n",
       "      <td>Florida</td>\n",
       "      <td>New Smyrna Beach, Volusia County</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>Chucky Luciano</td>\n",
       "      <td>M</td>\n",
       "      <td>36.0</td>\n",
       "      <td>Lacerations to hands</td>\n",
       "      <td>False</td>\n",
       "      <td>Unidentified</td>\n",
       "      <td>Orlando Sentinel, 9/19/2016</td>\n",
       "      <td>2016.09.18.b-Luciano.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-09-17</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>AUSTRALIA</td>\n",
       "      <td>Victoria</td>\n",
       "      <td>Thirteenth Beach</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>Rory Angiolella</td>\n",
       "      <td>M</td>\n",
       "      <td>17.0</td>\n",
       "      <td>Struck by fin on chest &amp; leg</td>\n",
       "      <td>False</td>\n",
       "      <td>Unidentified</td>\n",
       "      <td>The Age, 9/18/2016</td>\n",
       "      <td>2016.09.17-Angiolella.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-09-16</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>AUSTRALIA</td>\n",
       "      <td>Victoria</td>\n",
       "      <td>Bells Beach</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>male</td>\n",
       "      <td>M</td>\n",
       "      <td>17.0</td>\n",
       "      <td>No injury: Knocked off board by shark</td>\n",
       "      <td>False</td>\n",
       "      <td>2 m shark</td>\n",
       "      <td>The Age, 9/16/2016</td>\n",
       "      <td>2016.09.16-BellsBeach.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date        Type    Country      Area  \\\n",
       "0 2016-09-18  Unprovoked        USA   Florida   \n",
       "2 2016-09-18  Unprovoked        USA   Florida   \n",
       "1 2016-09-18  Unprovoked        USA   Florida   \n",
       "3 2016-09-17  Unprovoked  AUSTRALIA  Victoria   \n",
       "4 2016-09-16  Unprovoked  AUSTRALIA  Victoria   \n",
       "\n",
       "                           Location Activity_during_attack      Victim_Name  \\\n",
       "0  New Smyrna Beach, Volusia County                Surfing             male   \n",
       "2  New Smyrna Beach, Volusia County                Surfing             male   \n",
       "1  New Smyrna Beach, Volusia County                Surfing   Chucky Luciano   \n",
       "3                  Thirteenth Beach                Surfing  Rory Angiolella   \n",
       "4                       Bells Beach                Surfing             male   \n",
       "\n",
       "  Gender   Age                                 Injury  Fatal_attack  \\\n",
       "0      M  16.0                  Minor injury to thigh         False   \n",
       "2      M  43.0               Lacerations to lower leg         False   \n",
       "1      M  36.0                   Lacerations to hands         False   \n",
       "3      M  17.0           Struck by fin on chest & leg         False   \n",
       "4      M  17.0  No injury: Knocked off board by shark         False   \n",
       "\n",
       "  Shark_species           Source_information          Documentation_pdf  \\\n",
       "0  Unidentified  Orlando Sentinel, 9/19/2016       2016.09.18.c-NSB.pdf   \n",
       "2  Unidentified  Orlando Sentinel, 9/19/2016       2016.09.18.a-NSB.pdf   \n",
       "1  Unidentified  Orlando Sentinel, 9/19/2016   2016.09.18.b-Luciano.pdf   \n",
       "3  Unidentified           The Age, 9/18/2016  2016.09.17-Angiolella.pdf   \n",
       "4     2 m shark           The Age, 9/16/2016  2016.09.16-BellsBeach.pdf   \n",
       "\n",
       "                                  Documentation_href  \n",
       "0  http://sharkattackfile.net/spreadsheets/pdf_di...  \n",
       "2  http://sharkattackfile.net/spreadsheets/pdf_di...  \n",
       "1  http://sharkattackfile.net/spreadsheets/pdf_di...  \n",
       "3  http://sharkattackfile.net/spreadsheets/pdf_di...  \n",
       "4  http://sharkattackfile.net/spreadsheets/pdf_di...  "
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values(by = \"Date\", ascending = False, inplace = True)\n",
    "\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. Exporting dataframe as csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"/Users/Fabi/Documents/GitHub/data-ber-10-19/module-1_projects/pandas-project/your-code/Shark_attacks_clean.csv\", index= False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}