02. ETL_victimas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ETL y EDA de victimas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Comenzamos importando las librerías necesarias"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import scipy\n",
    "\n",
    "%load_ext autoreload\n",
    "%autoreload 2\n",
    "import funciones\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.1 Cargamos el dataset victimas a un pandas dataframe para comenzar el ETL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cargamos el dataset con sus diferentes páginas a pandas\n",
    "sheets_to_load = [\"VICTIMAS\"]\n",
    "# Cargamos las diferentes hojas del excel a un diccionario \n",
    "sheets_data = pd.read_excel(r'data\\homicidios.xlsx ', sheet_name=sheets_to_load)\n",
    "\n",
    "# Accedemos individualmente a los DataFrames usando el nombre de la pagina como fuente\n",
    "victimas_df = sheets_data[\"VICTIMAS\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 717 entries, 0 to 716\n",
      "Data columns (total 10 columns):\n",
      " #   Column               Non-Null Count  Dtype         \n",
      "---  ------               --------------  -----         \n",
      " 0   ID_hecho             717 non-null    object        \n",
      " 1   FECHA                717 non-null    datetime64[ns]\n",
      " 2   AAAA                 717 non-null    int64         \n",
      " 3   MM                   717 non-null    int64         \n",
      " 4   DD                   717 non-null    int64         \n",
      " 5   ROL                  717 non-null    object        \n",
      " 6   VICTIMA              717 non-null    object        \n",
      " 7   SEXO                 717 non-null    object        \n",
      " 8   EDAD                 717 non-null    object        \n",
      " 9   FECHA_FALLECIMIENTO  717 non-null    object        \n",
      "dtypes: datetime64[ns](1), int64(3), object(6)\n",
      "memory usage: 56.1+ KB\n"
     ]
    }
   ],
   "source": [
    "victimas_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID_hecho</th>\n",
       "      <th>FECHA</th>\n",
       "      <th>AAAA</th>\n",
       "      <th>MM</th>\n",
       "      <th>DD</th>\n",
       "      <th>ROL</th>\n",
       "      <th>VICTIMA</th>\n",
       "      <th>SEXO</th>\n",
       "      <th>EDAD</th>\n",
       "      <th>FECHA_FALLECIMIENTO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-0001</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>CONDUCTOR</td>\n",
       "      <td>MOTO</td>\n",
       "      <td>MASCULINO</td>\n",
       "      <td>19</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-0002</td>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>CONDUCTOR</td>\n",
       "      <td>AUTO</td>\n",
       "      <td>MASCULINO</td>\n",
       "      <td>70</td>\n",
       "      <td>2016-01-02 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-0003</td>\n",
       "      <td>2016-01-03</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>CONDUCTOR</td>\n",
       "      <td>MOTO</td>\n",
       "      <td>MASCULINO</td>\n",
       "      <td>30</td>\n",
       "      <td>2016-01-03 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-0004</td>\n",
       "      <td>2016-01-10</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>CONDUCTOR</td>\n",
       "      <td>MOTO</td>\n",
       "      <td>MASCULINO</td>\n",
       "      <td>18</td>\n",
       "      <td>SD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-0005</td>\n",
       "      <td>2016-01-21</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>21</td>\n",
       "      <td>CONDUCTOR</td>\n",
       "      <td>MOTO</td>\n",
       "      <td>MASCULINO</td>\n",
       "      <td>29</td>\n",
       "      <td>2016-02-01 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID_hecho      FECHA  AAAA  MM  DD        ROL VICTIMA       SEXO EDAD  \\\n",
       "0  2016-0001 2016-01-01  2016   1   1  CONDUCTOR    MOTO  MASCULINO   19   \n",
       "1  2016-0002 2016-01-02  2016   1   2  CONDUCTOR    AUTO  MASCULINO   70   \n",
       "2  2016-0003 2016-01-03  2016   1   3  CONDUCTOR    MOTO  MASCULINO   30   \n",
       "3  2016-0004 2016-01-10  2016   1  10  CONDUCTOR    MOTO  MASCULINO   18   \n",
       "4  2016-0005 2016-01-21  2016   1  21  CONDUCTOR    MOTO  MASCULINO   29   \n",
       "\n",
       "   FECHA_FALLECIMIENTO  \n",
       "0  2016-01-01 00:00:00  \n",
       "1  2016-01-02 00:00:00  \n",
       "2  2016-01-03 00:00:00  \n",
       "3                   SD  \n",
       "4  2016-02-01 00:00:00  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "victimas_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ID_hecho', 'FECHA', 'AAAA', 'MM', 'DD', 'ROL', 'VICTIMA', 'SEXO',\n",
       "       'EDAD', 'FECHA_FALLECIMIENTO'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "victimas_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "| Nombre_Columna        | Rename                      | Descripcion del dato y caracteristicas                                                          | Datatype |\n",
    "| --------------------- | --------------------------- | ----------------------------------------------------------------------------------------------- | -------- |\n",
    "| ID_hecho                    | id_hecho                    | es el identificador único del evento (siniestro)                                                | str      |\n",
    "| FECHA                 | fecha | fecha en formato dd/mm/aaaa                                                                     | datetime |\n",
    "| AAAA                  | año                         | es el año de ocurrencia del siniestro                                                           | int      |\n",
    "| MM                    | mes                         | es el mes de ocurrencia del siniestro                                                           | int      |\n",
    "| DD                    | id_hecho                    | es nro del dia de ocurrencia del siniestro (siniestro)                                          | int      |\n",
    "| HORA                  | hora                        | es la hora de ocurrencia del siniestro (siniestro)                                              | HH:MM:SS |\n",
    "| ROL                    | rol                    | Posición relativa al vehículo que presentaba la víctima en el momento del siniestro                                       | int      |\n",
    "| VICTIMA      | victima                         | Vehículo que ocupaba quien haya fallecido a se haya lastimado a raíz del hecho, o bien peatón/a                                     | string   |\n",
    "| SEXO                 | sexo                       |Sexo informado por fuente policial de la víctima                              | string   |\n",
    "| EDAD                | edad                       | es nro de la calle, altura en que ocurrió el evento (siniestro)                                 | string   |\n",
    "| FECHA_FALLECIMIENTO                 | fecha_fallecimiento                       | Fecha de fallecimiento de la víctima                          | string   |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.2 Comenzamos el ETL con el primer dataframe hechos_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Renombro las columnas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "victimas_df.rename(columns={'ID_hecho': 'id', 'FECHA': 'fecha', 'AAAA': 'anio', 'MM': 'mes', 'DD':'dia', \n",
    "                            'HORA': 'hora', 'ROL': 'rol', 'VICTIMA': 'victima', 'SEXO':'sexo', 'EDAD':'edad', 'FECHA_FALLECIMIENTO':'fecha_fallecimiento'}, inplace= True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Recorremos todo el Dataframe iterando por las columnas para repasar si hay valores duplicados, si hay valores faltantes y valores erróneos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Resumen de las columnas del dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>Unique_Values</th>\n",
       "      <th>Missing_Values</th>\n",
       "      <th>Missing_Percentage</th>\n",
       "      <th>Min</th>\n",
       "      <th>Max</th>\n",
       "      <th>Most_Common</th>\n",
       "      <th>Has_Duplicates</th>\n",
       "      <th>Duplicate_Percentage</th>\n",
       "      <th>Data_Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <td>696</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>2.93</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fecha</th>\n",
       "      <td>598</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>16.6</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>anio</th>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>2021</td>\n",
       "      <td>2018</td>\n",
       "      <td>True</td>\n",
       "      <td>99.16</td>\n",
       "      <td>int64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mes</th>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>True</td>\n",
       "      <td>98.33</td>\n",
       "      <td>int64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dia</th>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>20</td>\n",
       "      <td>True</td>\n",
       "      <td>95.68</td>\n",
       "      <td>int64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rol</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>99.3</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>victima</th>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>98.88</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sexo</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>99.58</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>edad</th>\n",
       "      <td>86</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>88.01</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fecha_fallecimiento</th>\n",
       "      <td>563</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>21.48</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Unique_Values Missing_Values Missing_Percentage   Min  \\\n",
       "id                            696              0                0.0  None   \n",
       "fecha                         598              0                0.0  None   \n",
       "anio                            6              0                0.0  2016   \n",
       "mes                            12              0                0.0     1   \n",
       "dia                            31              0                0.0     1   \n",
       "rol                             5              0                0.0  None   \n",
       "victima                         8              0                0.0  None   \n",
       "sexo                            3              0                0.0  None   \n",
       "edad                           86              0                0.0  None   \n",
       "fecha_fallecimiento           563              0                0.0  None   \n",
       "\n",
       "                      Max Most_Common Has_Duplicates Duplicate_Percentage  \\\n",
       "id                   None        None           True                 2.93   \n",
       "fecha                None        None           True                 16.6   \n",
       "anio                 2021        2018           True                99.16   \n",
       "mes                    12          12           True                98.33   \n",
       "dia                    31          20           True                95.68   \n",
       "rol                  None        None           True                 99.3   \n",
       "victima              None        None           True                98.88   \n",
       "sexo                 None        None           True                99.58   \n",
       "edad                 None        None           True                88.01   \n",
       "fecha_fallecimiento  None        None           True                21.48   \n",
       "\n",
       "                          Data_Type  \n",
       "id                           object  \n",
       "fecha                datetime64[ns]  \n",
       "anio                          int64  \n",
       "mes                           int64  \n",
       "dia                           int64  \n",
       "rol                          object  \n",
       "victima                      object  \n",
       "sexo                         object  \n",
       "edad                         object  \n",
       "fecha_fallecimiento          object  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%run -i funciones.py\n",
    "calculate_column_stats(victimas_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i funciones.py\n",
    "columns = ['rol','victima','sexo']\n",
    "convert_to_sentence_case(victimas_df, columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Análisis de las columnas de victimas_df\n",
    "Podemos ver de la función anterior que:\n",
    "#### 1. **id** \n",
    "- hay 696 valores únicos, es decir que se registraron 696 siniestros\n",
    "- por como esta construido el 'id' corresponde al año y el número, cantidad de eventos en el año.\n",
    "- No hay valores faltantes pero si duplicados, ya que esta columna corresponde a una  **clave secundaria** que vincula el siniestro con las víctimas, y hay siniestros con hasta casi 3 víctimas<br>\n",
    "Este campo coincide exacto con el del campo id del Dataframe homocidios_df\n",
    "\n",
    "#### 2. **fecha**, **mes**, **dia**, **anio**\n",
    "- Son columnas numérica, y el rango va de 2016 a 2021. fecha es datatime y esta correcta \n",
    "- No hay valores faltantes.\n",
    "- El datatype es correcto"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "anio\n",
       "2018    149\n",
       "2016    146\n",
       "2017    140\n",
       "2019    104\n",
       "2021     97\n",
       "2020     81\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Repasamos los valores de cant_victimas\n",
    "victimas_df.anio.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3. **rol**:\n",
    "- Posición relativa al vehículo que presentaba la víctima en el momento del siniestro<br>\n",
    "- Hay 5 posibilidades (categorias): Conductor, Peaton, Pasajero_Acompañante, Ciclista, SD\n",
    "- Vemos que estan todas las categorias representadas y correctas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "rol\n",
       "Conductor               330\n",
       "Peaton                  267\n",
       "Pasajero_Acompañante     80\n",
       "Ciclista                 29\n",
       "Sd                       11\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Repasamos los valores de horas\n",
    "victimas_df.rol.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Revisamos el id del siniestro para repasar si se puede completar desde esa data este campo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>id</th>\n",
       "      <th>fecha</th>\n",
       "      <th>anio</th>\n",
       "      <th>mes</th>\n",
       "      <th>dia</th>\n",
       "      <th>rol</th>\n",
       "      <th>victima</th>\n",
       "      <th>sexo</th>\n",
       "      <th>edad</th>\n",
       "      <th>fecha_fallecimiento</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>2016-0049</td>\n",
       "      <td>2016-04-17</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>Sd</td>\n",
       "      <td>Sd</td>\n",
       "      <td>Sd</td>\n",
       "      <td>SD</td>\n",
       "      <td>SD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>2016-0052</td>\n",
       "      <td>2016-04-20</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>Sd</td>\n",
       "      <td>Moto</td>\n",
       "      <td>Sd</td>\n",
       "      <td>SD</td>\n",
       "      <td>SD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>2016-0085</td>\n",
       "      <td>2016-06-29</td>\n",
       "      <td>2016</td>\n",
       "      <td>6</td>\n",
       "      <td>29</td>\n",
       "      <td>Sd</td>\n",
       "      <td>Moto</td>\n",
       "      <td>Masculino</td>\n",
       "      <td>SD</td>\n",
       "      <td>SD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>2016-0101</td>\n",
       "      <td>2016-08-07</td>\n",
       "      <td>2016</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "      <td>Sd</td>\n",
       "      <td>Sd</td>\n",
       "      <td>Masculino</td>\n",
       "      <td>67</td>\n",
       "      <td>SD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>2016-0115</td>\n",
       "      <td>2016-09-02</td>\n",
       "      <td>2016</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>Sd</td>\n",
       "      <td>Sd</td>\n",
       "      <td>Masculino</td>\n",
       "      <td>SD</td>\n",
       "      <td>SD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>2016-0174</td>\n",
       "      <td>2016-12-27</td>\n",
       "      <td>2016</td>\n",
       "      <td>12</td>\n",
       "      <td>27</td>\n",
       "      <td>Sd</td>\n",
       "      <td>Sd</td>\n",
       "      <td>Sd</td>\n",
       "      <td>SD</td>\n",
       "      <td>SD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>2017-0029</td>\n",
       "      <td>2017-03-07</td>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>Sd</td>\n",
       "      <td>Sd</td>\n",
       "      <td>Masculino</td>\n",
       "      <td>34</td>\n",
       "      <td>2017-03-07 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>208</th>\n",
       "      <td>2017-0074</td>\n",
       "      <td>2017-06-04</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>Sd</td>\n",
       "      <td>Sd</td>\n",
       "      <td>Masculino</td>\n",
       "      <td>70</td>\n",
       "      <td>2017-06-04 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>2017-0089</td>\n",
       "      <td>2017-07-13</td>\n",
       "      <td>2017</td>\n",
       "      <td>7</td>\n",
       "      <td>13</td>\n",
       "      <td>Sd</td>\n",
       "      <td>Sd</td>\n",
       "      <td>Masculino</td>\n",
       "      <td>23</td>\n",
       "      <td>SD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280</th>\n",
       "      <td>2017-0155</td>\n",
       "      <td>2017-12-12</td>\n",
       "      <td>2017</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>Sd</td>\n",
       "      <td>Sd</td>\n",
       "      <td>Masculino</td>\n",
       "      <td>77</td>\n",
       "      <td>SD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>390</th>\n",
       "      <td>2018-0100</td>\n",
       "      <td>2018-09-20</td>\n",
       "      <td>2018</td>\n",
       "      <td>9</td>\n",
       "      <td>20</td>\n",
       "      <td>Sd</td>\n",
       "      <td>Moto</td>\n",
       "      <td>Femenino</td>\n",
       "      <td>33</td>\n",
       "      <td>2018-09-20 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            id      fecha  anio  mes  dia rol victima       sexo edad  \\\n",
       "36   2016-0049 2016-04-17  2016    4   17  Sd      Sd         Sd   SD   \n",
       "39   2016-0052 2016-04-20  2016    4   20  Sd    Moto         Sd   SD   \n",
       "63   2016-0085 2016-06-29  2016    6   29  Sd    Moto  Masculino   SD   \n",
       "77   2016-0101 2016-08-07  2016    8    7  Sd      Sd  Masculino   67   \n",
       "89   2016-0115 2016-09-02  2016    9    2  Sd      Sd  Masculino   SD   \n",
       "141  2016-0174 2016-12-27  2016   12   27  Sd      Sd         Sd   SD   \n",
       "167  2017-0029 2017-03-07  2017    3    7  Sd      Sd  Masculino   34   \n",
       "208  2017-0074 2017-06-04  2017    6    4  Sd      Sd  Masculino   70   \n",
       "221  2017-0089 2017-07-13  2017    7   13  Sd      Sd  Masculino   23   \n",
       "280  2017-0155 2017-12-12  2017   12   12  Sd      Sd  Masculino   77   \n",
       "390  2018-0100 2018-09-20  2018    9   20  Sd    Moto   Femenino   33   \n",
       "\n",
       "     fecha_fallecimiento  \n",
       "36                    SD  \n",
       "39                    SD  \n",
       "63                    SD  \n",
       "77                    SD  \n",
       "89                    SD  \n",
       "141                   SD  \n",
       "167  2017-03-07 00:00:00  \n",
       "208  2017-06-04 00:00:00  \n",
       "221                   SD  \n",
       "280                   SD  \n",
       "390  2018-09-20 00:00:00  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "victimas_df[victimas_df['rol'] == 'Sd']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4. **victima**\n",
    "Vehículo que ocupaba quien haya fallecido a se haya lastimado a raíz del hecho, o bien peatón/a. <br>\n",
    "Clasificación agregada del tipo de vehículos.<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "victima\n",
       "Moto         303\n",
       "Peaton       267\n",
       "Auto          94\n",
       "Bicicleta     29\n",
       "Sd             9\n",
       "Cargas         7\n",
       "Pasajeros      5\n",
       "Movil          3\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "victimas_df['victima'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 5. **sexo**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sexo\n",
       "Masculino    545\n",
       "Femenino     166\n",
       "Sd             6\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "victimas_df.sexo.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 6. **Edad**:\n",
    "Hay 53 valores sin datos. Vamos a decidir imputarlos segun el sexo, por el promedio de edad segun el sexo.<br>\n",
    "Esto es vamos a calcular para cada sexo, el promedio de edad de los valores que si existen y completaremos la columna edad por ese valor promedio.<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "edad\n",
       "SD    53\n",
       "30    28\n",
       "29    24\n",
       "23    24\n",
       "27    20\n",
       "      ..\n",
       "11     1\n",
       "13     1\n",
       "85     1\n",
       "7      1\n",
       "88     1\n",
       "Name: count, Length: 86, dtype: int64"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "victimas_df.edad.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "El promedio de edad de las victimas Masculinas es : 39.55\n",
      "El porcentage de victimas Masculinas es : 76.01 %\n",
      "El promedio de edad de las victimas Femeninas es : 50.76\n",
      "El porcentaje de victimas Femeninas es : 23.15 %\n"
     ]
    }
   ],
   "source": [
    "# Convert 'edad' column to numeric data type, coercing errors to NaN\n",
    "victimas_df['edad'] = pd.to_numeric(victimas_df['edad'], errors='coerce')\n",
    "\n",
    "victimas_male = victimas_df[victimas_df['sexo'] == 'MASCULINO']         # 545 registros\n",
    "victimas_female = victimas_df[victimas_df['sexo'] == 'FEMENINO']        # 166 registros\n",
    "\n",
    "# Calculamos el promedio de edad para cada sexo:\n",
    "prom_edad_victimas_male = round((victimas_df[victimas_df['sexo'] == 'MASCULINO']['edad'].mean()),2)        # 545 registros\n",
    "prom_edad_victimas_female = round((victimas_df[victimas_df['sexo'] == 'FEMENINO']['edad'].mean()),2)        # 166 registros\n",
    "\n",
    "porcentaje_masculinas = round((victimas_male.id.count()/victimas_df.id.count())*100,2)\n",
    "porcentaje_femeninas = round((victimas_female.id.count()/victimas_df.id.count())*100,2)\n",
    "\n",
    "print('El promedio de edad de las victimas Masculinas es :', prom_edad_victimas_male)\n",
    "print('El porcentage de victimas Masculinas es :',porcentaje_masculinas,'%')\n",
    "print('El promedio de edad de las victimas Femeninas es :',prom_edad_victimas_female)\n",
    "print('El porcentaje de victimas Femeninas es :',porcentaje_femeninas, '%')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Realizamos la imputacion de estos valores a victimas_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "victimas_df.loc[(victimas_df['edad'] == 'SD') & (victimas_df['sexo'] == 'Masculino'), 'edad'] = 40\n",
    "victimas_df.loc[(victimas_df['edad'] == 'SD') & (victimas_df['sexo'] == 'Femenino'), 'edad'] = 51"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "edad\n",
       "40    47\n",
       "30    28\n",
       "29    24\n",
       "23    24\n",
       "27    20\n",
       "      ..\n",
       "11     1\n",
       "13     1\n",
       "85     1\n",
       "7      1\n",
       "88     1\n",
       "Name: count, Length: 86, dtype: int64"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "victimas_df.edad.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 5. **fecha_fallecimiento**\n",
    "-  Hay 68 valores sin datos.\n",
    "Pero esta columna no la consideraremos ya que la fecha que nos importa es la relativa al siniestro, esta es la fecha real de la muerte, pero no es interés para nuestro análisis y contexto.\n",
    "Procedemos a eliminarla:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "victimas_df.drop(['fecha_fallecimiento'],axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Coincide que el value_counts llegue hasta 3 ya que los siniestros registran hasta 3 victimas por siniestro"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3 Guardamos el victimas_df para utilizarlo en el EDA integrador"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "El victimas_df fue guardado en data\\victimas_etl.csv\n"
     ]
    }
   ],
   "source": [
    "# Ruta donde guardamos el file y el name_file\n",
    "victimas = 'data\\\\victimas_etl.csv'\n",
    "# Guardamos el dataframe en un archivo .csv\n",
    "victimas_df.to_csv(victimas, index=False)\n",
    "# Mensaje de confirmación de escritura\n",
    "print(f'El victimas_df fue guardado en {victimas}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "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.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}