Proceso explicado.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "a9aadeb1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Defaulting to user installation because normal site-packages is not writeable\n",
      "Requirement already satisfied: mysql-connector-python in /home/jbloch3/.local/lib/python3.8/site-packages (8.0.24)\n",
      "Requirement already satisfied: protobuf>=3.0.0 in /home/jbloch3/.local/lib/python3.8/site-packages (from mysql-connector-python) (3.15.8)\n",
      "Requirement already satisfied: six>=1.9 in /usr/lib/python3/dist-packages (from protobuf>=3.0.0->mysql-connector-python) (1.14.0)\n",
      "\u001b[33mWARNING: You are using pip version 21.0.1; however, version 21.1.1 is available.\n",
      "You should consider upgrading via the '/usr/bin/python3 -m pip install --upgrade pip' command.\u001b[0m\n",
      "Defaulting to user installation because normal site-packages is not writeable\n",
      "Requirement already satisfied: SQLAlchemy in /home/jbloch3/.local/lib/python3.8/site-packages (1.4.14)\n",
      "Requirement already satisfied: greenlet!=0.4.17 in /home/jbloch3/.local/lib/python3.8/site-packages (from SQLAlchemy) (1.1.0)\n",
      "\u001b[33mWARNING: You are using pip version 21.0.1; however, version 21.1.1 is available.\n",
      "You should consider upgrading via the '/usr/bin/python3 -m pip install --upgrade pip' command.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "import numpy as np \n",
    "import pandas as pd\n",
    "import re\n",
    "from datetime import datetime\n",
    "import time\n",
    "import warnings\n",
    "import pymysql\n",
    "!pip install mysql-connector-python\n",
    "!pip install SQLAlchemy\n",
    "import mysql.connector as conn\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "from pandas.core.common import SettingWithCopyWarning\n",
    "\n",
    "warnings.simplefilter(action=\"ignore\", category=SettingWithCopyWarning)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6680bdc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"/home/jbloch3/Documents/Proyecto_TL//attacks.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f8d0e47",
   "metadata": {},
   "source": [
    "**Observamos el contenido de las columnas del DataFrame**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0757320f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.describe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b427264a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Case Number', 'Date', 'Year', 'Type', 'Country', 'Area', 'Location',\n",
       "       'Activity', 'Name', 'Sex ', 'Age', 'Injury', 'Fatal (Y/N)', 'Time',\n",
       "       'Species ', 'Investigator or Source', 'pdf', 'href formula', 'href',\n",
       "       'Case Number.1', 'Case Number.2', 'original order', 'Unnamed: 22',\n",
       "       'Unnamed: 23'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(5)\n",
    "\n",
    "df.columns\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d1e2b57",
   "metadata": {},
   "source": [
    "**Adecuamos los nombres de las columnas para poder manipularlas con facilidad**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "fb316906",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df = df.rename(columns={k:v for k,v in [('Unnamed: 22','Unnamed22'), ('Species ', 'Species'), ('Sex ', 'Sex'), ('Investigator or Source', 'Investigator_Source'), ('Unnamed: 23', 'Unnamed23'), ('Fatal (Y/N)', 'Fatal'), ('original order', 'Original_order'), ('href formula', 'Href_formula'), ('href', 'Href'), ('Case Number', 'Case_Number'), ('Case Number.1', 'Case_Number_1'), ('Case Number.2', 'Case_Number_2')]})\n",
    "# cambiamos los nombres de las columnas con un diccionario para no tener problemas al subsetear"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "9bc2152b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Case_Number', 'Date', 'Year', 'Type', 'Country', 'Area', 'Location',\n",
       "       'Activity', 'Name', 'Sex', 'Age', 'Injury', 'Fatal', 'Time', 'Species',\n",
       "       'Investigator_Source', 'pdf', 'Href_formula', 'Href', 'Case_Number_1',\n",
       "       'Case_Number_2', 'Original_order', 'Unnamed22', 'Unnamed23'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8872dd5",
   "metadata": {},
   "source": [
    "**Guardamos la dimensión original del DF**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "efe31287",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(25723, 24)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dim_original=df.shape\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a59da7f",
   "metadata": {},
   "source": [
    "**Cambiamos las casillas vacías por na para facilitar el tratamiento**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "abbad311",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.replace(\"\", np.nan, inplace=True)\n",
    "#cambiamos las casillas con strings vaciás por na"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca35b763",
   "metadata": {},
   "source": [
    "**Borramos todas las filas con mas de 3 fallos**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "daf1043e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.dropna(0, thresh=3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "e4de34bf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6302, 24)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "54fe2a79",
   "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_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>Unnamed22</th>\n",
       "      <th>Unnamed23</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018.06.25</td>\n",
       "      <td>25-Jun-2018</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>Boating</td>\n",
       "      <td>USA</td>\n",
       "      <td>California</td>\n",
       "      <td>Oceanside, San Diego County</td>\n",
       "      <td>Paddling</td>\n",
       "      <td>Julie Wolfe</td>\n",
       "      <td>F</td>\n",
       "      <td>...</td>\n",
       "      <td>White shark</td>\n",
       "      <td>R. Collier, GSAF</td>\n",
       "      <td>2018.06.25-Wolfe.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2018.06.25</td>\n",
       "      <td>2018.06.25</td>\n",
       "      <td>6303.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018.06.18</td>\n",
       "      <td>18-Jun-2018</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>USA</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>St. Simon Island, Glynn County</td>\n",
       "      <td>Standing</td>\n",
       "      <td>Adyson�McNeely</td>\n",
       "      <td>F</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>K.McMurray, TrackingSharks.com</td>\n",
       "      <td>2018.06.18-McNeely.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2018.06.18</td>\n",
       "      <td>2018.06.18</td>\n",
       "      <td>6302.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018.06.09</td>\n",
       "      <td>09-Jun-2018</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>Invalid</td>\n",
       "      <td>USA</td>\n",
       "      <td>Hawaii</td>\n",
       "      <td>Habush, Oahu</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>John Denges</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>K.McMurray, TrackingSharks.com</td>\n",
       "      <td>2018.06.09-Denges.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2018.06.09</td>\n",
       "      <td>2018.06.09</td>\n",
       "      <td>6301.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018.06.08</td>\n",
       "      <td>08-Jun-2018</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>AUSTRALIA</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>Arrawarra Headland</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>male</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>2 m shark</td>\n",
       "      <td>B. Myatt, GSAF</td>\n",
       "      <td>2018.06.08-Arrawarra.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2018.06.08</td>\n",
       "      <td>2018.06.08</td>\n",
       "      <td>6300.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018.06.04</td>\n",
       "      <td>04-Jun-2018</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>Provoked</td>\n",
       "      <td>MEXICO</td>\n",
       "      <td>Colima</td>\n",
       "      <td>La Ticla</td>\n",
       "      <td>Free diving</td>\n",
       "      <td>Gustavo Ramos</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>Tiger shark, 3m</td>\n",
       "      <td>A .Kipper</td>\n",
       "      <td>2018.06.04-Ramos.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2018.06.04</td>\n",
       "      <td>2018.06.04</td>\n",
       "      <td>6299.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6297</th>\n",
       "      <td>ND.0005</td>\n",
       "      <td>Before 1903</td>\n",
       "      <td>0.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>...</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>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>ND.0005</td>\n",
       "      <td>ND.0005</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6298</th>\n",
       "      <td>ND.0004</td>\n",
       "      <td>Before 1903</td>\n",
       "      <td>0.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>...</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>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>ND.0004</td>\n",
       "      <td>ND.0004</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6299</th>\n",
       "      <td>ND.0003</td>\n",
       "      <td>1900-1905</td>\n",
       "      <td>0.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>...</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>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>ND.0003</td>\n",
       "      <td>ND.0003</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6300</th>\n",
       "      <td>ND.0002</td>\n",
       "      <td>1883-1889</td>\n",
       "      <td>0.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>...</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>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>ND.0002</td>\n",
       "      <td>ND.0002</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6301</th>\n",
       "      <td>ND.0001</td>\n",
       "      <td>1845-1853</td>\n",
       "      <td>0.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>...</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>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>ND.0001</td>\n",
       "      <td>ND.0001</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6302 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Case_Number         Date    Year        Type             Country  \\\n",
       "0     2018.06.25  25-Jun-2018  2018.0     Boating                 USA   \n",
       "1     2018.06.18  18-Jun-2018  2018.0  Unprovoked                 USA   \n",
       "2     2018.06.09  09-Jun-2018  2018.0     Invalid                 USA   \n",
       "3     2018.06.08  08-Jun-2018  2018.0  Unprovoked           AUSTRALIA   \n",
       "4     2018.06.04  04-Jun-2018  2018.0    Provoked              MEXICO   \n",
       "...          ...          ...     ...         ...                 ...   \n",
       "6297     ND.0005  Before 1903     0.0  Unprovoked           AUSTRALIA   \n",
       "6298     ND.0004  Before 1903     0.0  Unprovoked           AUSTRALIA   \n",
       "6299     ND.0003    1900-1905     0.0  Unprovoked                 USA   \n",
       "6300     ND.0002    1883-1889     0.0  Unprovoked              PANAMA   \n",
       "6301     ND.0001    1845-1853     0.0  Unprovoked  CEYLON (SRI LANKA)   \n",
       "\n",
       "                   Area                             Location      Activity  \\\n",
       "0            California          Oceanside, San Diego County      Paddling   \n",
       "1               Georgia       St. Simon Island, Glynn County      Standing   \n",
       "2                Hawaii                         Habush, Oahu       Surfing   \n",
       "3       New South Wales                   Arrawarra Headland       Surfing   \n",
       "4                Colima                             La Ticla   Free diving   \n",
       "...                 ...                                  ...           ...   \n",
       "6297  Western Australia                          Roebuck Bay        Diving   \n",
       "6298  Western Australia                                  NaN  Pearl diving   \n",
       "6299     North Carolina                       Ocracoke Inlet      Swimming   \n",
       "6300                NaN                 Panama Bay 8�N, 79�W           NaN   \n",
       "6301   Eastern Province  Below the English fort, Trincomalee      Swimming   \n",
       "\n",
       "                       Name Sex  ...          Species  \\\n",
       "0               Julie Wolfe   F  ...      White shark   \n",
       "1           Adyson�McNeely    F  ...              NaN   \n",
       "2               John Denges   M  ...              NaN   \n",
       "3                      male   M  ...        2 m shark   \n",
       "4            Gustavo Ramos    M  ...  Tiger shark, 3m   \n",
       "...                     ...  ..  ...              ...   \n",
       "6297                   male   M  ...              NaN   \n",
       "6298                  Ahmun   M  ...              NaN   \n",
       "6299  Coast Guard personnel   M  ...              NaN   \n",
       "6300        Jules Patterson   M  ...              NaN   \n",
       "6301                   male   M  ...              NaN   \n",
       "\n",
       "                        Investigator_Source                             pdf  \\\n",
       "0                          R. Collier, GSAF            2018.06.25-Wolfe.pdf   \n",
       "1            K.McMurray, TrackingSharks.com          2018.06.18-McNeely.pdf   \n",
       "2            K.McMurray, TrackingSharks.com           2018.06.09-Denges.pdf   \n",
       "3                            B. Myatt, GSAF        2018.06.08-Arrawarra.pdf   \n",
       "4                                 A .Kipper            2018.06.04-Ramos.pdf   \n",
       "...                                     ...                             ...   \n",
       "6297       H. Taunton; N. Bartlett,  p. 234          ND-0005-RoebuckBay.pdf   \n",
       "6298  H. Taunton; N. Bartlett,  pp. 233-234               ND-0004-Ahmun.pdf   \n",
       "6299   F. Schwartz, p.23; C. Creswell, GSAF  ND-0003-Ocracoke_1900-1905.pdf   \n",
       "6300                    The Sun, 10/20/1938      ND-0002-JulesPatterson.pdf   \n",
       "6301                             S.W. Baker              ND-0001-Ceylon.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",
       "6297  http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "6298  http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "6299  http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "6300  http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "6301  http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "\n",
       "                                                   Href Case_Number_1  \\\n",
       "0     http://sharkattackfile.net/spreadsheets/pdf_di...    2018.06.25   \n",
       "1     http://sharkattackfile.net/spreadsheets/pdf_di...    2018.06.18   \n",
       "2     http://sharkattackfile.net/spreadsheets/pdf_di...    2018.06.09   \n",
       "3     http://sharkattackfile.net/spreadsheets/pdf_di...    2018.06.08   \n",
       "4     http://sharkattackfile.net/spreadsheets/pdf_di...    2018.06.04   \n",
       "...                                                 ...           ...   \n",
       "6297  http://sharkattackfile.net/spreadsheets/pdf_di...       ND.0005   \n",
       "6298  http://sharkattackfile.net/spreadsheets/pdf_di...       ND.0004   \n",
       "6299  http://sharkattackfile.net/spreadsheets/pdf_di...       ND.0003   \n",
       "6300  http://sharkattackfile.net/spreadsheets/pdf_di...       ND.0002   \n",
       "6301  http://sharkattackfile.net/spreadsheets/pdf_di...       ND.0001   \n",
       "\n",
       "     Case_Number_2 Original_order Unnamed22 Unnamed23  \n",
       "0       2018.06.25         6303.0       NaN       NaN  \n",
       "1       2018.06.18         6302.0       NaN       NaN  \n",
       "2       2018.06.09         6301.0       NaN       NaN  \n",
       "3       2018.06.08         6300.0       NaN       NaN  \n",
       "4       2018.06.04         6299.0       NaN       NaN  \n",
       "...            ...            ...       ...       ...  \n",
       "6297       ND.0005            6.0       NaN       NaN  \n",
       "6298       ND.0004            5.0       NaN       NaN  \n",
       "6299       ND.0003            4.0       NaN       NaN  \n",
       "6300       ND.0002            3.0       NaN       NaN  \n",
       "6301       ND.0001            2.0       NaN       NaN  \n",
       "\n",
       "[6302 rows x 24 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ccfc51f",
   "metadata": {},
   "source": [
    "**Observamos los valores na por columnas**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "053f37b3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Case_Number               1\n",
       "Year                      2\n",
       "Type                      4\n",
       "Country                  50\n",
       "Area                    455\n",
       "Location                540\n",
       "Activity                544\n",
       "Name                    210\n",
       "Sex                     565\n",
       "Age                    2831\n",
       "Injury                   28\n",
       "Fatal                   539\n",
       "Time                   3354\n",
       "Species                2838\n",
       "Investigator_Source      17\n",
       "Href_formula              1\n",
       "Unnamed22              6301\n",
       "Unnamed23              6300\n",
       "dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_cols = df.isnull().sum()\n",
    "null_cols[null_cols > 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "8b91cb6a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Case_Number             object\n",
       "Date                    object\n",
       "Year                   float64\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                   object\n",
       "Time                    object\n",
       "Species                 object\n",
       "Investigator_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         float64\n",
       "Unnamed22               object\n",
       "Unnamed23               object\n",
       "dtype: object"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab417315",
   "metadata": {},
   "source": [
    "**Buscamos los valores no na de la columna Unnamed22**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "1810693a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([1478], dtype='int64')"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bad_index=df.Unnamed22[df.Unnamed22.notna()].index\n",
    "\n",
    "bad_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "8875d940",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Case_Number                                                   2006.05.27\n",
       "Date                                                         27-May-2006\n",
       "Year                                                              2006.0\n",
       "Type                                                          Unprovoked\n",
       "Country                                                              USA\n",
       "Area                                                              Hawaii\n",
       "Location                                              North Shore, O'ahu\n",
       "Activity                                                         Surfing\n",
       "Name                                                        Bret Desmond\n",
       "Sex                                                                    M\n",
       "Age                                                                   31\n",
       "Injury                                 No injury, shark bumped surfboard\n",
       "Fatal                                                                  N\n",
       "Time                                                               16h00\n",
       "Species                                                              NaN\n",
       "Investigator_Source                                           R. Collier\n",
       "pdf                                               2006.05.27-Desmond.pdf\n",
       "Href_formula           http://sharkattackfile.net/spreadsheets/pdf_di...\n",
       "Href                   http://sharkattackfile.net/spreadsheets/pdf_di...\n",
       "Case_Number_1                                                 2006.05.27\n",
       "Case_Number_2                                                 2006.05.27\n",
       "Original_order                                                    4825.0\n",
       "Unnamed22                                                   stopped here\n",
       "Unnamed23                                                            NaN\n",
       "Name: 1478, dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[1478,]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "963fd77b",
   "metadata": {},
   "source": [
    "**Usando set(df.Type) vemos en que columnas podemos cambiar na por unknown**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "30e26b18",
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "na_to_ukwn = ['Species', 'Investigator_Source', 'Country', 'Area', 'Location', 'Activity', 'Name', 'Injury', 'Type', 'Href_formula']\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84ff1b5e",
   "metadata": {},
   "source": [
    "**Rellenamos por Unknown**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "418d23d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in na_to_ukwn:\n",
    "    df[i]=df[i].fillna('Unknown')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "742e6403",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Case_Number       1\n",
       "Year              2\n",
       "Sex             565\n",
       "Age            2831\n",
       "Fatal           539\n",
       "Time           3354\n",
       "Unnamed22      6301\n",
       "Unnamed23      6300\n",
       "dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_cols = df.isnull().sum()\n",
    "null_cols[null_cols > 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5240986e",
   "metadata": {},
   "source": [
    "**Normalizamos la variable Sex**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "6efcd6c8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'.', 'F', 'M', 'M ', 'N', 'lli', nan}"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "set(df.Sex)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "4a85098c",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(df)):\n",
    "    if str(df.Sex[i]).startswith('M'):\n",
    "        df.Sex[i] = 'M'\n",
    "    elif str(df.Sex[i]).startswith('F'):\n",
    "        df.Sex[i] = 'F'\n",
    "    else:\n",
    "        df.Sex[i] = 'Unknown'\n",
    "        \n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "7717c0b7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'F', 'M', 'Unknown'}"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(df.Sex)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "85c6a6e2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Case_Number       1\n",
       "Year              2\n",
       "Age            2831\n",
       "Fatal           539\n",
       "Time           3354\n",
       "Unnamed22      6301\n",
       "Unnamed23      6300\n",
       "dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_cols = df.isnull().sum()\n",
    "null_cols[null_cols > 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8273a439",
   "metadata": {},
   "source": [
    "**Normalizamos la variable Age**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "d7230f7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Buscada edad media ataques de tiburón(29 años)\n",
    "lst= []\n",
    "patron = '\\d+'\n",
    "for e in df.Age:\n",
    "    try:\n",
    "        lst.append(int(re.findall(patron,e)[0]))\n",
    "    except:\n",
    "        lst.append(int(29))\n",
    "\n",
    "df.Age=lst    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "ccf808a0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{1, 2, 3, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 77, 78, 81, 82, 84, 86, 87}\n"
     ]
    }
   ],
   "source": [
    "print(set(df.Age))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "2d1dc0cf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Case_Number       1\n",
       "Year              2\n",
       "Fatal           539\n",
       "Time           3354\n",
       "Unnamed22      6301\n",
       "Unnamed23      6300\n",
       "dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_cols = df.isnull().sum()\n",
    "null_cols[null_cols > 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "464b5cb7",
   "metadata": {},
   "source": [
    "**Normalizamos la variable Year**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "1cc3a71b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([187, 6079], dtype='int64')"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#buscando las filas con año na, sustituimos por el año de la fecha\n",
    "bad_index=df.Year[df.Year.isna()].index\n",
    "bad_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "f3d3a151",
   "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_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>Unnamed22</th>\n",
       "      <th>Unnamed23</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>2017.01.08.R</td>\n",
       "      <td>Reported 08-Jan-2017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Invalid</td>\n",
       "      <td>AUSTRALIA</td>\n",
       "      <td>Queensland</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Spearfishing</td>\n",
       "      <td>Kerry Daniel</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>Bull shark</td>\n",
       "      <td>Liquid Vision 1/8/2017</td>\n",
       "      <td>2017.01.08.R-KerryDaniel.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2017.01.08.R</td>\n",
       "      <td>2017.01.08.R</td>\n",
       "      <td>6116.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6079</th>\n",
       "      <td>1836.08.19.R</td>\n",
       "      <td>Reported 19-Aug-1836</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>ENGLAND</td>\n",
       "      <td>Cumberland</td>\n",
       "      <td>Whitehaven</td>\n",
       "      <td>Swimming</td>\n",
       "      <td>a boy</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>C. Moore, GSAF</td>\n",
       "      <td>1835.08.19.R-Whitehaven.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>1836.08.19.R</td>\n",
       "      <td>1836.08.19.R</td>\n",
       "      <td>224.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Case_Number                  Date  Year        Type    Country  \\\n",
       "187   2017.01.08.R  Reported 08-Jan-2017   NaN     Invalid  AUSTRALIA   \n",
       "6079  1836.08.19.R  Reported 19-Aug-1836   NaN  Unprovoked    ENGLAND   \n",
       "\n",
       "            Area    Location      Activity          Name Sex  ...     Species  \\\n",
       "187   Queensland     Unknown  Spearfishing  Kerry Daniel   M  ...  Bull shark   \n",
       "6079  Cumberland  Whitehaven      Swimming         a boy   M  ...     Unknown   \n",
       "\n",
       "         Investigator_Source                           pdf  \\\n",
       "187   Liquid Vision 1/8/2017  2017.01.08.R-KerryDaniel.pdf   \n",
       "6079          C. Moore, GSAF   1835.08.19.R-Whitehaven.pdf   \n",
       "\n",
       "                                           Href_formula  \\\n",
       "187   http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "6079  http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "\n",
       "                                                   Href Case_Number_1  \\\n",
       "187   http://sharkattackfile.net/spreadsheets/pdf_di...  2017.01.08.R   \n",
       "6079  http://sharkattackfile.net/spreadsheets/pdf_di...  1836.08.19.R   \n",
       "\n",
       "     Case_Number_2 Original_order Unnamed22 Unnamed23  \n",
       "187   2017.01.08.R         6116.0       NaN       NaN  \n",
       "6079  1836.08.19.R          224.0       NaN       NaN  \n",
       "\n",
       "[2 rows x 24 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[(187,6079),] #las mostramos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "fef100f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Year[187] = (2017) #Las cambiamos por el ano de Date\n",
    "df.Year[6079] = int(1836)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "e124e3fa",
   "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_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>Unnamed22</th>\n",
       "      <th>Unnamed23</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>2017.01.08.R</td>\n",
       "      <td>Reported 08-Jan-2017</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>Invalid</td>\n",
       "      <td>AUSTRALIA</td>\n",
       "      <td>Queensland</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Spearfishing</td>\n",
       "      <td>Kerry Daniel</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>Bull shark</td>\n",
       "      <td>Liquid Vision 1/8/2017</td>\n",
       "      <td>2017.01.08.R-KerryDaniel.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2017.01.08.R</td>\n",
       "      <td>2017.01.08.R</td>\n",
       "      <td>6116.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6079</th>\n",
       "      <td>1836.08.19.R</td>\n",
       "      <td>Reported 19-Aug-1836</td>\n",
       "      <td>1836.0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>ENGLAND</td>\n",
       "      <td>Cumberland</td>\n",
       "      <td>Whitehaven</td>\n",
       "      <td>Swimming</td>\n",
       "      <td>a boy</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>C. Moore, GSAF</td>\n",
       "      <td>1835.08.19.R-Whitehaven.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>1836.08.19.R</td>\n",
       "      <td>1836.08.19.R</td>\n",
       "      <td>224.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Case_Number                  Date    Year        Type    Country  \\\n",
       "187   2017.01.08.R  Reported 08-Jan-2017  2017.0     Invalid  AUSTRALIA   \n",
       "6079  1836.08.19.R  Reported 19-Aug-1836  1836.0  Unprovoked    ENGLAND   \n",
       "\n",
       "            Area    Location      Activity          Name Sex  ...     Species  \\\n",
       "187   Queensland     Unknown  Spearfishing  Kerry Daniel   M  ...  Bull shark   \n",
       "6079  Cumberland  Whitehaven      Swimming         a boy   M  ...     Unknown   \n",
       "\n",
       "         Investigator_Source                           pdf  \\\n",
       "187   Liquid Vision 1/8/2017  2017.01.08.R-KerryDaniel.pdf   \n",
       "6079          C. Moore, GSAF   1835.08.19.R-Whitehaven.pdf   \n",
       "\n",
       "                                           Href_formula  \\\n",
       "187   http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "6079  http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "\n",
       "                                                   Href Case_Number_1  \\\n",
       "187   http://sharkattackfile.net/spreadsheets/pdf_di...  2017.01.08.R   \n",
       "6079  http://sharkattackfile.net/spreadsheets/pdf_di...  1836.08.19.R   \n",
       "\n",
       "     Case_Number_2 Original_order Unnamed22 Unnamed23  \n",
       "187   2017.01.08.R         6116.0       NaN       NaN  \n",
       "6079  1836.08.19.R          224.0       NaN       NaN  \n",
       "\n",
       "[2 rows x 24 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[(187,6079),]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "91f9785d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Case_Number       1\n",
       "Fatal           539\n",
       "Time           3354\n",
       "Unnamed22      6301\n",
       "Unnamed23      6300\n",
       "dtype: int64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_cols = df.isnull().sum()\n",
    "null_cols[null_cols > 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07404b5e",
   "metadata": {},
   "source": [
    "**Normalizamos la variable Unnamed23**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "2641768c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([4415, 5840], dtype='int64')"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bad_index=df.Unnamed23[df.Unnamed23.notna()].index\n",
    "bad_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "f03c357e",
   "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_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>Unnamed22</th>\n",
       "      <th>Unnamed23</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4415</th>\n",
       "      <td>1952.03.30</td>\n",
       "      <td>30-Mar-1952</td>\n",
       "      <td>1952.0</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>NETHERLANDS ANTILLES</td>\n",
       "      <td>Curacao</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Went to aid of child being menaced by the shark</td>\n",
       "      <td>A.J. Eggink</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>Bull shark, 2.7 m [9'] was captured &amp; dragged ...</td>\n",
       "      <td>J. Randall, p.352 in Sharks &amp; Survival; H.D. B...</td>\n",
       "      <td>1952.03.30-Eggink.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>1952.03.30</td>\n",
       "      <td>1952.03.30</td>\n",
       "      <td>1888.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Teramo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5840</th>\n",
       "      <td>1878.09.14.R</td>\n",
       "      <td>Reported 14-Sep-1878</td>\n",
       "      <td>1878.0</td>\n",
       "      <td>Provoked</td>\n",
       "      <td>USA</td>\n",
       "      <td>Connecticut</td>\n",
       "      <td>Branford, New Haven County</td>\n",
       "      <td>Fishing</td>\n",
       "      <td>Captain Pattison</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>St. Joseph Herald, 9/14/1878</td>\n",
       "      <td>1878.09.14.R-Pattison.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>1878.09.14.R</td>\n",
       "      <td>1878.09.14.R</td>\n",
       "      <td>463.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>change filename</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Case_Number                  Date    Year        Type  \\\n",
       "4415    1952.03.30           30-Mar-1952  1952.0  Unprovoked   \n",
       "5840  1878.09.14.R  Reported 14-Sep-1878  1878.0    Provoked   \n",
       "\n",
       "                   Country         Area                    Location  \\\n",
       "4415  NETHERLANDS ANTILLES      Curacao                     Unknown   \n",
       "5840                   USA  Connecticut  Branford, New Haven County   \n",
       "\n",
       "                                             Activity              Name Sex  \\\n",
       "4415  Went to aid of child being menaced by the shark       A.J. Eggink   M   \n",
       "5840                                          Fishing  Captain Pattison   M   \n",
       "\n",
       "      ...                                            Species  \\\n",
       "4415  ...  Bull shark, 2.7 m [9'] was captured & dragged ...   \n",
       "5840  ...                                            Unknown   \n",
       "\n",
       "                                    Investigator_Source  \\\n",
       "4415  J. Randall, p.352 in Sharks & Survival; H.D. B...   \n",
       "5840                       St. Joseph Herald, 9/14/1878   \n",
       "\n",
       "                            pdf  \\\n",
       "4415      1952.03.30-Eggink.pdf   \n",
       "5840  1878.09.14.R-Pattison.pdf   \n",
       "\n",
       "                                           Href_formula  \\\n",
       "4415  http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "5840  http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "\n",
       "                                                   Href Case_Number_1  \\\n",
       "4415  http://sharkattackfile.net/spreadsheets/pdf_di...    1952.03.30   \n",
       "5840  http://sharkattackfile.net/spreadsheets/pdf_di...  1878.09.14.R   \n",
       "\n",
       "     Case_Number_2 Original_order Unnamed22        Unnamed23  \n",
       "4415    1952.03.30         1888.0       NaN           Teramo  \n",
       "5840  1878.09.14.R          463.0       NaN  change filename  \n",
       "\n",
       "[2 rows x 24 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[(4415, 5840),]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f8e8403",
   "metadata": {},
   "source": [
    "**Los rellenamos con 0 con los na**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "c2c50fb4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Unnamed23[4415] = 0\n",
    "df.Unnamed23[5840] = 0\n",
    "df.Unnamed23=df.Unnamed23.fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34cbf4c5",
   "metadata": {},
   "source": [
    "**Igual con Unnamed22**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "1ad87c96",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([1478], dtype='int64')"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bad_index2=df.Unnamed22[df.Unnamed22.notna()].index\n",
    "bad_index2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "9d6a7969",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Unnamed22[1478] = 0\n",
    "df.Unnamed22=df.Unnamed22.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "f37445ee",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Case_Number       1\n",
       "Fatal           539\n",
       "Time           3354\n",
       "dtype: int64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_cols = df.isnull().sum()\n",
    "null_cols[null_cols > 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "cfd55d16",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.to_excel('/home/jbloch3/Documents/prueba.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "6b63c108",
   "metadata": {},
   "outputs": [],
   "source": [
    "#set(df.Date)Zambezi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "b5d9e7e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a29eb1dc",
   "metadata": {},
   "source": [
    "**Normalizamos la variable Date**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e1c23f5",
   "metadata": {},
   "source": [
    "**Limpiamos la columna de typos**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "cce372c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Date'] = df.Date.apply(lambda x: x.replace('Reported ', '').replace('Before', '').replace('Early ', '')\n",
    "                           .replace('Late ', '').replace('--',  '-').strip())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "2c4fed44",
   "metadata": {},
   "outputs": [],
   "source": [
    "#set(df.Date)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c449b10b",
   "metadata": {},
   "source": [
    "**Normalizamos la fecha**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "2cc2a0c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Date = pd.to_datetime(df.Date, errors='coerce')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "f8fd4a6e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0      2018-06-25\n",
      "1      2018-06-18\n",
      "2      2018-06-09\n",
      "3      2018-06-08\n",
      "4      2018-06-04\n",
      "          ...    \n",
      "6297   1903-01-01\n",
      "6298   1903-01-01\n",
      "6299          NaT\n",
      "6300          NaT\n",
      "6301          NaT\n",
      "Name: Date, Length: 6302, dtype: datetime64[ns]\n"
     ]
    }
   ],
   "source": [
    "print(df.Date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "d7501c66",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Date = df.Date.dt.strftime('%d/%m/%y')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b42a721",
   "metadata": {},
   "source": [
    "**Rellenamos na's con fillna con los métodos \"bfill y ffill\"**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "274bb8d1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Case_Number       1\n",
       "Date            218\n",
       "Fatal           539\n",
       "Time           3354\n",
       "dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_cols = df.isnull().sum()\n",
    "null_cols[null_cols > 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "0dc943fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Date = df.Date.fillna(method='bfill') #Rellenamos con el último valor abservado bueno\n",
    "df.Date = df.Date.fillna(method='ffill') #Rellenamos con el anterior valor abservado bueno los últimos 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "530bea0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Date = pd.to_datetime(df.Date, errors='coerce')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cfa0e1d9",
   "metadata": {},
   "source": [
    "**Rehacemos la variable Year con los datos de Date**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "4e277c6d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Year'] = df['Date'].dt.year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "47335a85",
   "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_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>Unnamed22</th>\n",
       "      <th>Unnamed23</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018.06.25</td>\n",
       "      <td>2018-06-25</td>\n",
       "      <td>2018</td>\n",
       "      <td>Boating</td>\n",
       "      <td>USA</td>\n",
       "      <td>California</td>\n",
       "      <td>Oceanside, San Diego County</td>\n",
       "      <td>Paddling</td>\n",
       "      <td>Julie Wolfe</td>\n",
       "      <td>F</td>\n",
       "      <td>...</td>\n",
       "      <td>White shark</td>\n",
       "      <td>R. Collier, GSAF</td>\n",
       "      <td>2018.06.25-Wolfe.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2018.06.25</td>\n",
       "      <td>2018.06.25</td>\n",
       "      <td>6303.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018.06.18</td>\n",
       "      <td>2018-06-18</td>\n",
       "      <td>2018</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>USA</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>St. Simon Island, Glynn County</td>\n",
       "      <td>Standing</td>\n",
       "      <td>Adyson�McNeely</td>\n",
       "      <td>F</td>\n",
       "      <td>...</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>K.McMurray, TrackingSharks.com</td>\n",
       "      <td>2018.06.18-McNeely.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2018.06.18</td>\n",
       "      <td>2018.06.18</td>\n",
       "      <td>6302.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018.06.09</td>\n",
       "      <td>2018-09-06</td>\n",
       "      <td>2018</td>\n",
       "      <td>Invalid</td>\n",
       "      <td>USA</td>\n",
       "      <td>Hawaii</td>\n",
       "      <td>Habush, Oahu</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>John Denges</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>K.McMurray, TrackingSharks.com</td>\n",
       "      <td>2018.06.09-Denges.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2018.06.09</td>\n",
       "      <td>2018.06.09</td>\n",
       "      <td>6301.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018.06.08</td>\n",
       "      <td>2018-08-06</td>\n",
       "      <td>2018</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>AUSTRALIA</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>Arrawarra Headland</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>male</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>2 m shark</td>\n",
       "      <td>B. Myatt, GSAF</td>\n",
       "      <td>2018.06.08-Arrawarra.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2018.06.08</td>\n",
       "      <td>2018.06.08</td>\n",
       "      <td>6300.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018.06.04</td>\n",
       "      <td>2018-04-06</td>\n",
       "      <td>2018</td>\n",
       "      <td>Provoked</td>\n",
       "      <td>MEXICO</td>\n",
       "      <td>Colima</td>\n",
       "      <td>La Ticla</td>\n",
       "      <td>Free diving</td>\n",
       "      <td>Gustavo Ramos</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>Tiger shark, 3m</td>\n",
       "      <td>A .Kipper</td>\n",
       "      <td>2018.06.04-Ramos.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2018.06.04</td>\n",
       "      <td>2018.06.04</td>\n",
       "      <td>6299.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6297</th>\n",
       "      <td>ND.0005</td>\n",
       "      <td>2003-01-01</td>\n",
       "      <td>2003</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>...</td>\n",
       "      <td>Unknown</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>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>ND.0005</td>\n",
       "      <td>ND.0005</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6298</th>\n",
       "      <td>ND.0004</td>\n",
       "      <td>2003-01-01</td>\n",
       "      <td>2003</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>AUSTRALIA</td>\n",
       "      <td>Western Australia</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Pearl diving</td>\n",
       "      <td>Ahmun</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>Unknown</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>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>ND.0004</td>\n",
       "      <td>ND.0004</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6299</th>\n",
       "      <td>ND.0003</td>\n",
       "      <td>2003-01-01</td>\n",
       "      <td>2003</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>...</td>\n",
       "      <td>Unknown</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>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>ND.0003</td>\n",
       "      <td>ND.0003</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6300</th>\n",
       "      <td>ND.0002</td>\n",
       "      <td>2003-01-01</td>\n",
       "      <td>2003</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>PANAMA</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Panama Bay 8�N, 79�W</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Jules Patterson</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>Unknown</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>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>ND.0002</td>\n",
       "      <td>ND.0002</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6301</th>\n",
       "      <td>ND.0001</td>\n",
       "      <td>2003-01-01</td>\n",
       "      <td>2003</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>...</td>\n",
       "      <td>Unknown</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>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>ND.0001</td>\n",
       "      <td>ND.0001</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6302 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Case_Number       Date  Year        Type             Country  \\\n",
       "0     2018.06.25 2018-06-25  2018     Boating                 USA   \n",
       "1     2018.06.18 2018-06-18  2018  Unprovoked                 USA   \n",
       "2     2018.06.09 2018-09-06  2018     Invalid                 USA   \n",
       "3     2018.06.08 2018-08-06  2018  Unprovoked           AUSTRALIA   \n",
       "4     2018.06.04 2018-04-06  2018    Provoked              MEXICO   \n",
       "...          ...        ...   ...         ...                 ...   \n",
       "6297     ND.0005 2003-01-01  2003  Unprovoked           AUSTRALIA   \n",
       "6298     ND.0004 2003-01-01  2003  Unprovoked           AUSTRALIA   \n",
       "6299     ND.0003 2003-01-01  2003  Unprovoked                 USA   \n",
       "6300     ND.0002 2003-01-01  2003  Unprovoked              PANAMA   \n",
       "6301     ND.0001 2003-01-01  2003  Unprovoked  CEYLON (SRI LANKA)   \n",
       "\n",
       "                   Area                             Location      Activity  \\\n",
       "0            California          Oceanside, San Diego County      Paddling   \n",
       "1               Georgia       St. Simon Island, Glynn County      Standing   \n",
       "2                Hawaii                         Habush, Oahu       Surfing   \n",
       "3       New South Wales                   Arrawarra Headland       Surfing   \n",
       "4                Colima                             La Ticla   Free diving   \n",
       "...                 ...                                  ...           ...   \n",
       "6297  Western Australia                          Roebuck Bay        Diving   \n",
       "6298  Western Australia                              Unknown  Pearl diving   \n",
       "6299     North Carolina                       Ocracoke Inlet      Swimming   \n",
       "6300            Unknown                 Panama Bay 8�N, 79�W       Unknown   \n",
       "6301   Eastern Province  Below the English fort, Trincomalee      Swimming   \n",
       "\n",
       "                       Name Sex  ...          Species  \\\n",
       "0               Julie Wolfe   F  ...      White shark   \n",
       "1           Adyson�McNeely    F  ...          Unknown   \n",
       "2               John Denges   M  ...          Unknown   \n",
       "3                      male   M  ...        2 m shark   \n",
       "4            Gustavo Ramos    M  ...  Tiger shark, 3m   \n",
       "...                     ...  ..  ...              ...   \n",
       "6297                   male   M  ...          Unknown   \n",
       "6298                  Ahmun   M  ...          Unknown   \n",
       "6299  Coast Guard personnel   M  ...          Unknown   \n",
       "6300        Jules Patterson   M  ...          Unknown   \n",
       "6301                   male   M  ...          Unknown   \n",
       "\n",
       "                        Investigator_Source                             pdf  \\\n",
       "0                          R. Collier, GSAF            2018.06.25-Wolfe.pdf   \n",
       "1            K.McMurray, TrackingSharks.com          2018.06.18-McNeely.pdf   \n",
       "2            K.McMurray, TrackingSharks.com           2018.06.09-Denges.pdf   \n",
       "3                            B. Myatt, GSAF        2018.06.08-Arrawarra.pdf   \n",
       "4                                 A .Kipper            2018.06.04-Ramos.pdf   \n",
       "...                                     ...                             ...   \n",
       "6297       H. Taunton; N. Bartlett,  p. 234          ND-0005-RoebuckBay.pdf   \n",
       "6298  H. Taunton; N. Bartlett,  pp. 233-234               ND-0004-Ahmun.pdf   \n",
       "6299   F. Schwartz, p.23; C. Creswell, GSAF  ND-0003-Ocracoke_1900-1905.pdf   \n",
       "6300                    The Sun, 10/20/1938      ND-0002-JulesPatterson.pdf   \n",
       "6301                             S.W. Baker              ND-0001-Ceylon.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",
       "6297  http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "6298  http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "6299  http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "6300  http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "6301  http://sharkattackfile.net/spreadsheets/pdf_di...   \n",
       "\n",
       "                                                   Href Case_Number_1  \\\n",
       "0     http://sharkattackfile.net/spreadsheets/pdf_di...    2018.06.25   \n",
       "1     http://sharkattackfile.net/spreadsheets/pdf_di...    2018.06.18   \n",
       "2     http://sharkattackfile.net/spreadsheets/pdf_di...    2018.06.09   \n",
       "3     http://sharkattackfile.net/spreadsheets/pdf_di...    2018.06.08   \n",
       "4     http://sharkattackfile.net/spreadsheets/pdf_di...    2018.06.04   \n",
       "...                                                 ...           ...   \n",
       "6297  http://sharkattackfile.net/spreadsheets/pdf_di...       ND.0005   \n",
       "6298  http://sharkattackfile.net/spreadsheets/pdf_di...       ND.0004   \n",
       "6299  http://sharkattackfile.net/spreadsheets/pdf_di...       ND.0003   \n",
       "6300  http://sharkattackfile.net/spreadsheets/pdf_di...       ND.0002   \n",
       "6301  http://sharkattackfile.net/spreadsheets/pdf_di...       ND.0001   \n",
       "\n",
       "     Case_Number_2 Original_order Unnamed22 Unnamed23  \n",
       "0       2018.06.25         6303.0         0         0  \n",
       "1       2018.06.18         6302.0         0         0  \n",
       "2       2018.06.09         6301.0         0         0  \n",
       "3       2018.06.08         6300.0         0         0  \n",
       "4       2018.06.04         6299.0         0         0  \n",
       "...            ...            ...       ...       ...  \n",
       "6297       ND.0005            6.0         0         0  \n",
       "6298       ND.0004            5.0         0         0  \n",
       "6299       ND.0003            4.0         0         0  \n",
       "6300       ND.0002            3.0         0         0  \n",
       "6301       ND.0001            2.0         0         0  \n",
       "\n",
       "[6302 rows x 24 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e7087e5a",
   "metadata": {},
   "source": [
    "**Limpiamos la variable Country**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "d84ebf40",
   "metadata": {},
   "outputs": [],
   "source": [
    "#set(df.Country)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "ecd0c61a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Country'] = df.Country.apply(lambda x: x.replace('TURKS & CAICOS', 'TURKEY').replace('SOLOMON ISLANDS / VANUATU', 'SOLOMON ISLANDS')\n",
    "                                 .replace('ITALY / CROATIA', 'ITALY').replace('EQUATORIAL GUINEA / CAMEROON', 'EQUATORIAL GUINEA')\n",
    "                                 .replace('TUNISIA', 'TURKEY').replace('?', '').replace('IRAN / IRAQ', 'IRAN').replace('ANDAMAN / NICOBAR ISLANDAS','ANDAMAN ISLANDS')\n",
    "                                 .replace('EGYPT / ISRAEL', 'EGYPT').replace('CEYLON (SRI LANKA)', 'CEYLON').replace(' (UAE)', '')\n",
    "                                 .replace('Between PORTUGAL & INDIA', 'PORTUGAL').replace('RED SEA / INDIAN OCEAN', 'RED SEA')\n",
    "                                 .strip().upper())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18d46029",
   "metadata": {},
   "source": [
    "**Limpiamos las variables Country, Location, Area, Activity, Name e Injury**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "6662f936",
   "metadata": {},
   "outputs": [],
   "source": [
    "#set(df.Country)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "70d6c355",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Country = df.Country.apply(lambda x: x.strip().upper())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "f637adf5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Location = df.Location.apply(lambda x: x.strip())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "ad03e2cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Area = df.Area.apply(lambda x: x.strip())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "f4128789",
   "metadata": {},
   "outputs": [],
   "source": [
    "#set(df.Activity)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "33bb5f4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Activity = df.Activity.apply(lambda x: x.strip())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "f7bd7516",
   "metadata": {},
   "outputs": [],
   "source": [
    "#set(df.Name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "3d6fdbcf",
   "metadata": {},
   "outputs": [],
   "source": [
    "#todos ofrecen info. Borro info del sexo que tenemos en la siguiente columna\n",
    "df.Name = df.Name.apply(lambda x: x.strip().strip(\"fe\").strip(\"_\").replace('male','Unknown')\n",
    "                        .replace('female','Unknown').replace(',',''))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "00975611",
   "metadata": {},
   "outputs": [],
   "source": [
    "#set(df.Injury)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "f7243967",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Injury = df.Injury.apply(lambda x: x.strip().strip(',').replace(',','').strip(','))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "4a5cfabf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{' N', '2017', 'M', 'N', 'N ', 'UNKNOWN', 'Y', nan, 'y'}"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(df.Fatal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "2a8b72ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Fatal'] = df['Fatal'].astype('str') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "617cb9dd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{' N', '2017', 'M', 'N', 'N ', 'UNKNOWN', 'Y', 'nan', 'y'}"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(df.Fatal)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0199a4f0",
   "metadata": {},
   "source": [
    "**Correjimos con replace las variables mal escritas**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "d652bcfe",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Fatal = df.Fatal.apply(lambda x: x.strip().replace('2017','Unknown').replace('nan','Unknown').replace('M','N')\n",
    "                          .replace('UNKNOWN','Unknown').replace('y','Y'))\n",
    "\n",
    "df['Fatal']=df['Fatal'].fillna('Unknown')\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "093104e9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'N', 'Unknown', 'Y'}"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(df.Fatal)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e83dc9fe",
   "metadata": {},
   "source": [
    "**Hacemnos un indice de los datos Unknown de Fatal y los printeamos con Injury**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "b3a5818f",
   "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</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Multiple severe injuries to arms and leg leg s...</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>FATAL</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>Lacerations to right ankle foot &amp; toe</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>FATAL but death was probably due to drowning</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Lacerations to left foot</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6169</th>\n",
       "      <td>Leg severed mid-thigh hand severed arm above e...</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6171</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6172</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6232</th>\n",
       "      <td>A 4.3 m [14'] shark made threat display. No in...</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6277</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>611 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 Injury    Fatal\n",
       "27    Multiple severe injuries to arms and leg leg s...  Unknown\n",
       "76                                                FATAL  Unknown\n",
       "79                Lacerations to right ankle foot & toe  Unknown\n",
       "84         FATAL but death was probably due to drowning  Unknown\n",
       "98                             Lacerations to left foot  Unknown\n",
       "...                                                 ...      ...\n",
       "6169  Leg severed mid-thigh hand severed arm above e...  Unknown\n",
       "6171                                            Unknown  Unknown\n",
       "6172                                            Unknown  Unknown\n",
       "6232  A 4.3 m [14'] shark made threat display. No in...  Unknown\n",
       "6277                                            Unknown  Unknown\n",
       "\n",
       "[611 rows x 2 columns]"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ind = df[df[\"Fatal\"]=='Unknown'].index.values\n",
    "\n",
    "unknown_fatal = df[(df['Fatal']=='Unknown')]\n",
    "unknown_fatal = unknown_fatal[['Injury', 'Fatal']]\n",
    "unknown_fatal"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0196cff9",
   "metadata": {},
   "source": [
    "**Habiendo leido todas las palbras clave que prueban muerte en set(df.Injury[ind]), las utilizamos para completar la columna fatal con información de Injury**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "521fd418",
   "metadata": {},
   "outputs": [],
   "source": [
    "#si una de las palabras del fatality_causes está en en los injuries, asumimos que ha muerto\n",
    "fatality_causes = [\"fatal\", \"body\", \"remains\", \"washed\", \"killed\", \"perished\", \"drowned\"\n",
    "                   , \"drowning\", \"bodies\", \"drowning\", \"death\", \"died\", \"death\"]\n",
    "for i in ind:\n",
    "    for j in fatality_causes:\n",
    "        if j in df.Injury[i].lower():\n",
    "            df.Fatal[i] = \"Y\"\n",
    "        elif df.Fatal[i] == \"Unknown\":\n",
    "            pass\n",
    "        elif j not in df.Injury[i].lower():\n",
    "            df.Fatal[i] = \"N\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "f3fa4a1f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'N', 'Unknown', 'Y'}"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(df.Fatal)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e6195b3",
   "metadata": {},
   "source": [
    "**Corregimos los valores N y Y**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "5112b71d",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(df)):\n",
    "    if df.Fatal[i] == \"Y\":\n",
    "        df.Fatal[i] = True\n",
    "    elif df.Fatal[i] == \"N\":\n",
    "        df.Fatal[i] = False\n",
    "    else:\n",
    "        pass\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "8e0710ec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{False, True, 'Unknown'}"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(df.Fatal)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ed38c9e",
   "metadata": {},
   "source": [
    "**Cambiamos valores restantes con strings por False(asumimos que sobrevivieron)(son pocos datos)**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "edb49782",
   "metadata": {},
   "outputs": [],
   "source": [
    "ft = [True, False, \"Unknown\"]\n",
    "for i in range(len(df)):\n",
    "    if df.Fatal[i] not in ft:\n",
    "        df.Fatal[i] = \"False\" "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "76870a53",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4     False\n",
       "5     False\n",
       "6      True\n",
       "7     False\n",
       "8     False\n",
       "9     False\n",
       "10    False\n",
       "11    False\n",
       "12    False\n",
       "13    False\n",
       "14    False\n",
       "Name: Fatal, dtype: object"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.Fatal[4:15]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3ac0894",
   "metadata": {},
   "source": [
    "**Normalizamos la variable Time:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "c7020ec8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Case_Number                    object\n",
       "Date                   datetime64[ns]\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                             int64\n",
       "Injury                         object\n",
       "Fatal                          object\n",
       "Time                           object\n",
       "Species                        object\n",
       "Investigator_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                float64\n",
       "Unnamed22                       int64\n",
       "Unnamed23                       int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "70b312aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Time = df.Time.astype(str)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cefba973",
   "metadata": {},
   "source": [
    "**Sustituimos algunas palablas claves por la hora correspondiente:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "b31190af",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Time'] = df.Time.apply(lambda x: x.replace('Morning', '06h00').replace('Dark', '10h00').replace('Dawn', '06h30')\n",
    "                           .replace('Dusk', '20h00').replace('Afternoon', '15h00').replace('Midday', '12h00')\n",
    "                           .replace('Noon', '12h00').replace('Midnight', '00h00').replace('Night', '12h00')\n",
    "                           .replace('Sundown', '20h00').replace('morning', '06h00').replace('dark', '10h00').replace('Dawn', '06h30')\n",
    "                           .replace('dusk', '20h00').replace('afternoon', '15h00').replace('midday', '12h00')\n",
    "                           .replace('noon', '12h00').replace('midnight', '00h00').replace('night', '12h00')\n",
    "                           .replace('sundown', '20h00'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ade0c751",
   "metadata": {},
   "source": [
    "**Buscamos el patron y sustiyuimos la columna por la lista de patrones. si no hay ponemos la hora media buscada en:**\n",
    "(https://underwater.com.au/article/id/10214-shark-attacks-7-answers-to-frequently-asked-questions/#:~:text=Most%20attacks%20world%2Dwide%20happen,spending%20time%20in%20the%20water.)\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "1ed8fca1",
   "metadata": {},
   "outputs": [],
   "source": [
    "patron=r'\\d\\dh\\d\\d'\n",
    "lst= []\n",
    "for e in df.Time:\n",
    "    try:\n",
    "        lst.append(re.findall(patron,e)[0])\n",
    "    except:\n",
    "        lst.append(\"13h00\")\n",
    "\n",
    "df.Time=lst    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "1abf8673",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       18h00\n",
       "1       14h00\n",
       "2       07h45\n",
       "3       13h00\n",
       "4       13h00\n",
       "        ...  \n",
       "6297    13h00\n",
       "6298    13h00\n",
       "6299    13h00\n",
       "6300    13h00\n",
       "6301    13h00\n",
       "Name: Time, Length: 6302, dtype: object"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Time']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "bafb126b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Time'] = df.Time.apply(lambda x: x.replace('h', ':'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "edb7e355",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       18:00\n",
       "1       14:00\n",
       "2       07:45\n",
       "3       13:00\n",
       "4       13:00\n",
       "        ...  \n",
       "6297    13:00\n",
       "6298    13:00\n",
       "6299    13:00\n",
       "6300    13:00\n",
       "6301    13:00\n",
       "Name: Time, Length: 6302, dtype: object"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Time']\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec6f520c",
   "metadata": {},
   "source": [
    "**Intento de ponerlo en formato datetime:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "53119b12",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Time = pd.to_datetime(df.Time, format= '%H:%M')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "64bbcd99",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      1900-01-01 18:00:00\n",
       "1      1900-01-01 14:00:00\n",
       "2      1900-01-01 07:45:00\n",
       "3      1900-01-01 13:00:00\n",
       "4      1900-01-01 13:00:00\n",
       "               ...        \n",
       "6297   1900-01-01 13:00:00\n",
       "6298   1900-01-01 13:00:00\n",
       "6299   1900-01-01 13:00:00\n",
       "6300   1900-01-01 13:00:00\n",
       "6301   1900-01-01 13:00:00\n",
       "Name: Time, Length: 6302, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Time']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "c9c06638",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('<M8[ns]')"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Time'].dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "a0084284",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.Time = df.Time.dt.strftime('%H:%M')\n",
    "\n",
    "#df.Time = pd.to_datetime(df.Time, format= '%H:%M')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b709bfd4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "693e7d7f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       18:00\n",
       "1       14:00\n",
       "2       07:45\n",
       "3       13:00\n",
       "4       13:00\n",
       "        ...  \n",
       "6297    13:00\n",
       "6298    13:00\n",
       "6299    13:00\n",
       "6300    13:00\n",
       "6301    13:00\n",
       "Name: Time, Length: 6302, dtype: object"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.Time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "710c910d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "d348148e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Time'].dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82f87763",
   "metadata": {},
   "source": [
    "**Hacemos una lista de especies sacadas de la columna, y hacemos una columna nueva con las especies limpias**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "dccd22d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#set(df[\"Species\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "3c9921d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "shark_species = [\"white\", \"carpet\", \"zambesi\", \"banjo\", \"blue\", \"grey\", \"wobbegong\", \"blacktip\", \"raggedtooth\", \"wobbegong\", \"salmon\", \"raggedtooth\", \"silky\", \"metre\", \"shovelnose\", \"nurse\", \"basking\", \"hammerhead\", \"blackfin\", \"blacktip\", \"port jackson\", \"sevengill\", \"whaler\", \"lemon\", \"blacktip\", \"tiger\", \"caribbean\", \"spinner\", \"dusky\", \"sandbar\", \"sandtiger\", \"smoothhound\", \"bronze\"]\n",
    "for i in range(len(df)):\n",
    "    for j in shark_species:\n",
    "        if j in df.Species[i].lower():\n",
    "            df.Unnamed22[i] = j.capitalize() \n",
    "            break\n",
    "        else:\n",
    "            df.Unnamed22[i] = \"Undetermined\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c415d15",
   "metadata": {},
   "source": [
    "**Cambiamos los nombres de las colunas y las ordenamos**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "33d1e721",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.rename(columns = {'Unnamed22': 'Sark_species', 'Species': 'Shark_info'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "c9f59851",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Case_Number', 'Date', 'Year', 'Type', 'Country', 'Area', 'Location',\n",
       "       'Activity', 'Name', 'Sex', 'Age', 'Injury', 'Fatal', 'Time',\n",
       "       'Shark_info', 'Investigator_Source', 'pdf', 'Href_formula', 'Href',\n",
       "       'Case_Number_1', 'Case_Number_2', 'Original_order', 'Sark_species',\n",
       "       'Unnamed23'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "08445967",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[['Case_Number', 'Date', 'Year', 'Type', 'Country', 'Area', 'Location',\n",
    "       'Activity', 'Name', 'Sex', 'Age', 'Injury', 'Fatal', 'Time',\n",
    "       'Shark_info', 'Sark_species', 'Investigator_Source', 'pdf', 'Href_formula', 'Href',\n",
    "       'Case_Number_1', 'Case_Number_2', 'Original_order',\n",
    "       'Unnamed23']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e1daf64",
   "metadata": {},
   "source": [
    "**Reacemos la columna Case_Number con un índice especial(hay una copia en Case_Number.2)**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "8906a314",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(df)):\n",
    "    \n",
    "    num = str(6302-int(i))\n",
    "    \n",
    "    if i < 5303:       \n",
    "        df.Case_Number[i] = \"ND.\"+ num\n",
    "    elif i < 6203:\n",
    "        df.Case_Number[i] = \"ND.0\"+num\n",
    "    elif i< 6293:\n",
    "        df.Case_Number[i] = \"ND.00\"+num\n",
    "    elif i < 6302:\n",
    "        df.Case_Number[i] = \"ND.000\"+num"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "4339a4a2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       ND.6302\n",
       "1       ND.6301\n",
       "2       ND.6300\n",
       "3       ND.6299\n",
       "4       ND.6298\n",
       "         ...   \n",
       "6297    ND.0005\n",
       "6298    ND.0004\n",
       "6299    ND.0003\n",
       "6300    ND.0002\n",
       "6301    ND.0001\n",
       "Name: Case_Number, Length: 6302, dtype: object"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.Case_Number"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3e4c0b5",
   "metadata": {},
   "source": [
    "**Hemos acabado con los na:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "f5ead3bc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Series([], dtype: int64)"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_cols = df.isnull().sum()\n",
    "null_cols[null_cols > 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "252c9d03",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "ae10b417",
   "metadata": {
    "scrolled": true
   },
   "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>Shark_info</th>\n",
       "      <th>Sark_species</th>\n",
       "      <th>Investigator_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>Unnamed23</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ND.6302</td>\n",
       "      <td>2018-06-25</td>\n",
       "      <td>2018</td>\n",
       "      <td>Boating</td>\n",
       "      <td>USA</td>\n",
       "      <td>California</td>\n",
       "      <td>Oceanside, San Diego County</td>\n",
       "      <td>Paddling</td>\n",
       "      <td>Julie Wol</td>\n",
       "      <td>F</td>\n",
       "      <td>...</td>\n",
       "      <td>White shark</td>\n",
       "      <td>White</td>\n",
       "      <td>R. Collier, GSAF</td>\n",
       "      <td>2018.06.25-Wolfe.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2018.06.25</td>\n",
       "      <td>2018.06.25</td>\n",
       "      <td>6303.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ND.6301</td>\n",
       "      <td>2018-06-18</td>\n",
       "      <td>2018</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>USA</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>St. Simon Island, Glynn County</td>\n",
       "      <td>Standing</td>\n",
       "      <td>Adyson�McNeely</td>\n",
       "      <td>F</td>\n",
       "      <td>...</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Undetermined</td>\n",
       "      <td>K.McMurray, TrackingSharks.com</td>\n",
       "      <td>2018.06.18-McNeely.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2018.06.18</td>\n",
       "      <td>2018.06.18</td>\n",
       "      <td>6302.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ND.6300</td>\n",
       "      <td>2018-09-06</td>\n",
       "      <td>2018</td>\n",
       "      <td>Invalid</td>\n",
       "      <td>USA</td>\n",
       "      <td>Hawaii</td>\n",
       "      <td>Habush, Oahu</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>John Denges</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Undetermined</td>\n",
       "      <td>K.McMurray, TrackingSharks.com</td>\n",
       "      <td>2018.06.09-Denges.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2018.06.09</td>\n",
       "      <td>2018.06.09</td>\n",
       "      <td>6301.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ND.6299</td>\n",
       "      <td>2018-08-06</td>\n",
       "      <td>2018</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>AUSTRALIA</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>Arrawarra Headland</td>\n",
       "      <td>Surfing</td>\n",
       "      <td>mal</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>2 m shark</td>\n",
       "      <td>Undetermined</td>\n",
       "      <td>B. Myatt, GSAF</td>\n",
       "      <td>2018.06.08-Arrawarra.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2018.06.08</td>\n",
       "      <td>2018.06.08</td>\n",
       "      <td>6300.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ND.6298</td>\n",
       "      <td>2018-04-06</td>\n",
       "      <td>2018</td>\n",
       "      <td>Provoked</td>\n",
       "      <td>MEXICO</td>\n",
       "      <td>Colima</td>\n",
       "      <td>La Ticla</td>\n",
       "      <td>Free diving</td>\n",
       "      <td>Gustavo Ramos</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>Tiger shark, 3m</td>\n",
       "      <td>Tiger</td>\n",
       "      <td>A .Kipper</td>\n",
       "      <td>2018.06.04-Ramos.pdf</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>2018.06.04</td>\n",
       "      <td>2018.06.04</td>\n",
       "      <td>6299.0</td>\n",
       "      <td>0</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     ND.6302 2018-06-25  2018     Boating        USA       California   \n",
       "1     ND.6301 2018-06-18  2018  Unprovoked        USA          Georgia   \n",
       "2     ND.6300 2018-09-06  2018     Invalid        USA           Hawaii   \n",
       "3     ND.6299 2018-08-06  2018  Unprovoked  AUSTRALIA  New South Wales   \n",
       "4     ND.6298 2018-04-06  2018    Provoked     MEXICO           Colima   \n",
       "\n",
       "                         Location     Activity            Name Sex  ...  \\\n",
       "0     Oceanside, San Diego County     Paddling       Julie Wol   F  ...   \n",
       "1  St. Simon Island, Glynn County     Standing  Adyson�McNeely   F  ...   \n",
       "2                    Habush, Oahu      Surfing     John Denges   M  ...   \n",
       "3              Arrawarra Headland      Surfing             mal   M  ...   \n",
       "4                        La Ticla  Free diving   Gustavo Ramos   M  ...   \n",
       "\n",
       "        Shark_info  Sark_species             Investigator_Source  \\\n",
       "0      White shark         White                R. Collier, GSAF   \n",
       "1          Unknown  Undetermined  K.McMurray, TrackingSharks.com   \n",
       "2          Unknown  Undetermined  K.McMurray, TrackingSharks.com   \n",
       "3        2 m shark  Undetermined                  B. Myatt, GSAF   \n",
       "4  Tiger shark, 3m         Tiger                       A .Kipper   \n",
       "\n",
       "                        pdf  \\\n",
       "0      2018.06.25-Wolfe.pdf   \n",
       "1    2018.06.18-McNeely.pdf   \n",
       "2     2018.06.09-Denges.pdf   \n",
       "3  2018.06.08-Arrawarra.pdf   \n",
       "4      2018.06.04-Ramos.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...    2018.06.25   \n",
       "1  http://sharkattackfile.net/spreadsheets/pdf_di...    2018.06.18   \n",
       "2  http://sharkattackfile.net/spreadsheets/pdf_di...    2018.06.09   \n",
       "3  http://sharkattackfile.net/spreadsheets/pdf_di...    2018.06.08   \n",
       "4  http://sharkattackfile.net/spreadsheets/pdf_di...    2018.06.04   \n",
       "\n",
       "  Case_Number_2 Original_order Unnamed23  \n",
       "0    2018.06.25         6303.0         0  \n",
       "1    2018.06.18         6302.0         0  \n",
       "2    2018.06.09         6301.0         0  \n",
       "3    2018.06.08         6300.0         0  \n",
       "4    2018.06.04         6299.0         0  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "334d1487",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"/home/jbloch3/Documents/Proyecto_TL/clean_attacks.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3dddd753",
   "metadata": {},
   "source": [
    "**Metemos los datos en la tabla previamente creada en workbench**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "4870dea3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('information_schema',)\n",
      "('mysql',)\n",
      "('performance_schema',)\n",
      "('publications',)\n",
      "('sys',)\n"
     ]
    }
   ],
   "source": [
    "connection = conn.connect(host='localhost', user='root', password='culoculete', database='sys')\n",
    "cursor=connection.cursor()\n",
    "cursor.execute('show databases')\n",
    "for x in cursor:\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8d3b63d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "570b7615",
   "metadata": {},
   "outputs": [],
   "source": [
    "str_conn='mysql+pymysql://root:culoculete@localhost:3306/sys'\n",
    "motor=create_engine(str_conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "8033afbb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_sql(name='sharks', con=motor,\n",
    "         if_exists= 'append', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "d4f1584b",
   "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>Shark_info</th>\n",
       "      <th>Sark_species</th>\n",
       "      <th>Investigator_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>Unnamed23</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ND.0001</td>\n",
       "      <td>2003-01-01 00:00:00</td>\n",
       "      <td>2003</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>CEYLON</td>\n",
       "      <td>Eastern Province</td>\n",
       "      <td>Below the English fort, Trincomalee</td>\n",
       "      <td>Swimming</td>\n",
       "      <td>mal</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Undetermined</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>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>ND.0001</td>\n",
       "      <td>ND.0001</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ND.0002</td>\n",
       "      <td>2003-01-01 00:00:00</td>\n",
       "      <td>2003</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>PANAMA</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Panama Bay 8�N, 79�W</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Jules Patterson</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Undetermined</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>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>ND.0002</td>\n",
       "      <td>ND.0002</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ND.0003</td>\n",
       "      <td>2003-01-01 00:00:00</td>\n",
       "      <td>2003</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>...</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Undetermined</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>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>ND.0003</td>\n",
       "      <td>ND.0003</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ND.0004</td>\n",
       "      <td>2003-01-01 00:00:00</td>\n",
       "      <td>2003</td>\n",
       "      <td>Unprovoked</td>\n",
       "      <td>AUSTRALIA</td>\n",
       "      <td>Western Australia</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Pearl diving</td>\n",
       "      <td>Ahmun</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Undetermined</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>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>ND.0004</td>\n",
       "      <td>ND.0004</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ND.0005</td>\n",
       "      <td>2003-01-01 00:00:00</td>\n",
       "      <td>2003</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>mal</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Undetermined</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>http://sharkattackfile.net/spreadsheets/pdf_di...</td>\n",
       "      <td>ND.0005</td>\n",
       "      <td>ND.0005</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Case_Number                 Date  Year        Type    Country  \\\n",
       "0     ND.0001  2003-01-01 00:00:00  2003  Unprovoked     CEYLON   \n",
       "1     ND.0002  2003-01-01 00:00:00  2003  Unprovoked     PANAMA   \n",
       "2     ND.0003  2003-01-01 00:00:00  2003  Unprovoked        USA   \n",
       "3     ND.0004  2003-01-01 00:00:00  2003  Unprovoked  AUSTRALIA   \n",
       "4     ND.0005  2003-01-01 00:00:00  2003  Unprovoked  AUSTRALIA   \n",
       "\n",
       "                Area                             Location      Activity  \\\n",
       "0   Eastern Province  Below the English fort, Trincomalee      Swimming   \n",
       "1            Unknown                 Panama Bay 8�N, 79�W       Unknown   \n",
       "2     North Carolina                       Ocracoke Inlet      Swimming   \n",
       "3  Western Australia                              Unknown  Pearl diving   \n",
       "4  Western Australia                          Roebuck Bay        Diving   \n",
       "\n",
       "                    Name Sex  ... Shark_info  Sark_species  \\\n",
       "0                    mal   M  ...    Unknown  Undetermined   \n",
       "1        Jules Patterson   M  ...    Unknown  Undetermined   \n",
       "2  Coast Guard personnel   M  ...    Unknown  Undetermined   \n",
       "3                  Ahmun   M  ...    Unknown  Undetermined   \n",
       "4                    mal   M  ...    Unknown  Undetermined   \n",
       "\n",
       "                     Investigator_Source                             pdf  \\\n",
       "0                             S.W. Baker              ND-0001-Ceylon.pdf   \n",
       "1                    The Sun, 10/20/1938      ND-0002-JulesPatterson.pdf   \n",
       "2   F. Schwartz, p.23; C. Creswell, GSAF  ND-0003-Ocracoke_1900-1905.pdf   \n",
       "3  H. Taunton; N. Bartlett,  pp. 233-234               ND-0004-Ahmun.pdf   \n",
       "4       H. Taunton; N. Bartlett,  p. 234          ND-0005-RoebuckBay.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...       ND.0001   \n",
       "1  http://sharkattackfile.net/spreadsheets/pdf_di...       ND.0002   \n",
       "2  http://sharkattackfile.net/spreadsheets/pdf_di...       ND.0003   \n",
       "3  http://sharkattackfile.net/spreadsheets/pdf_di...       ND.0004   \n",
       "4  http://sharkattackfile.net/spreadsheets/pdf_di...       ND.0005   \n",
       "\n",
       "  Case_Number_2 Original_order Unnamed23  \n",
       "0       ND.0001              2         0  \n",
       "1       ND.0002              3         0  \n",
       "2       ND.0003              4         0  \n",
       "3       ND.0004              5         0  \n",
       "4       ND.0005              6         0  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data=pd.read_sql('select * from sharks', motor)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b891715",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}