ProyectoFinal.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Proyecto: Siniestro viales"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La empresa bogotana de seguros SSeguro esta buscando obtener informacion sobre los datos obtenidos en siniestros viales. Se busca lograr esto cargando la informacion a una base de datos de `sqlite` y haciendo consultas `SQL` para obtener la mayor cantidad de informacion sobre los datos."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Metas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "En general, se busca crear una base de datos local para poder ejecutar consultas de `SQL`. Ademas se busca generar consultas que extraigan informacion que permitan identificar caracteristicas de la muestra, es decir, obtener conteos y promedios de la muestra con respecto a diferentes caracteristicas. En mas detalle, se espera obtener informacion como:\n",
    "\n",
    "* El promedio de edad por sexo, estado y condicion.\n",
    "* La cantidad de personas por sexo y la cantidad de sevicios que entran en fuga."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Planeacion"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La empresa nos dio acceso a una base de datos de github en formato csv, el primer paso a seguir es importar los datos, es decir, hacer uso de pandas para descargar los datos en un DataFrame. Con esto se procede a exportar los datos a una base de datos local en un archivo `.db`. Finalmente, se hacen las consultas *SQL* necesarias para obtener la informacion de los datos."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importacion de paquetes y carga de datos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inicialmente se buscaba usar la funcion de pandas `to_sql` con conexion a un *engine* de `SQLAlchemy`, pero los intentos no fueron eficientes en velocidad, por lo tanto, se decidio usar un metodo local y manual. Esto quiere decir que se uso el paquete `sqlite3` y se implemento una carga de datos usando un bucle **for** insertando individualmente cada fila de los datos. Se intento una insercion multiple pero no hubo mayor eficiencia, por lo tanto se conservo insercion individual por menor cantidad de codigo necesitado. Teniendo en cuenta esto, podemos empezar con la importacion de los paquetes necesarios:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3 as db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahora, creamos una conexion a la base de datos, conectando a una existente o creandose automaticamente un archivo `.db` si no existe previamente:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "con = db.connect(\"SiniestrosViales.db\")\n",
    "cur = con.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Bajo esta conexion creamos la tabla `Siniestros`, la cual consta de las siguientes 16 columnas:\n",
    "* fecha\n",
    "* gravedad\n",
    "* clase_siniestro\n",
    "* choque\n",
    "* objeto_fijo\n",
    "* localidad\n",
    "* lugar\n",
    "* causa\n",
    "* vehiculo\n",
    "* servicio\n",
    "* modalidad\n",
    "* fuga\n",
    "* condicion\n",
    "* estado\n",
    "* edad\n",
    "* sexo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x2a7d8be57c0>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute(\"\"\"CREATE TABLE Siniestros (\n",
    "            fecha,\n",
    "            gravedad INTEGER,\n",
    "            clase_siniestro INTEGER,\n",
    "            choque INTEGER,\n",
    "            objeto_fijo,\n",
    "            localidad INTEGER,\n",
    "            lugar INTEGER,\n",
    "            causa INTEGER,\n",
    "            vehiculo INTEGER,\n",
    "            servicio INTEGER,\n",
    "            modalidad INTEGER,\n",
    "            fuga,\n",
    "            condicion,\n",
    "            estado,\n",
    "            edad INTEGER,\n",
    "            sexo)\n",
    "            \"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "y definimos las siguientes funciones encargadas de insertar una columna y todo el dataframe respectivamente:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def insert_row(row):\n",
    "    row = ' '.join([\"'\"+str(elem)+\"', \" for elem in row])\n",
    "    query = f\"\"\"\n",
    "    INSERT INTO Siniestros(fecha, gravedad, clase_siniestro, choque, objeto_fijo, localidad, lugar, causa, vehiculo, servicio, modalidad, fuga, condicion, estado, edad, sexo)\n",
    "    VALUES\n",
    "\t    ({row[:-2]});\n",
    "    \"\"\"\n",
    "    cur.execute(query)\n",
    "    con.commit()\n",
    "\n",
    "def insert_df(df):\n",
    "    for i in range(len(df)):\n",
    "        insert_row(df.iloc[i].array)\n",
    "    print(\"Ejecucion exitosa\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finalmente, procedemos a importar los datos en el dataframe `df` y ejecutamos la insercion de los datos:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(r\"https://raw.githubusercontent.com/JuanAGuzman/Siniestros-Viales/main/siniestros_viales.csv\")\n",
    "df.drop(['Unnamed: 0', 'CODIGO_ACCIDENTE', 'DIRECCION', 'CODIGO_ACCIDENTADO', 'VEHICULO'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Ejecucion exitosa\n"
     ]
    }
   ],
   "source": [
    "insert_df(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se uso la funcion `print` para hacer seguimiento cada 10000 datos insertados."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploracion de los datos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Habiendo creado la base de datos, podemos empezar con la consultas para extraer informacion de los datos. Para facilitar las consultas se va a trabajar con funciones que retornen los `query`, los cuales pueden ser evaluados directamente de la siguiente forma:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def distintos_valores(variable):\n",
    "    query = f\"\"\"\n",
    "    SELECT DISTINCT\n",
    "        {variable},\n",
    "        COUNT(*) as conteo\n",
    "    FROM\n",
    "        Siniestros\n",
    "    GROUP BY\n",
    "        {variable}\n",
    "    \"\"\"\n",
    "    return query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 8646), (2, 191111), (3, 302210)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res = cur.execute(distintos_valores('gravedad'))\n",
    "res.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pero con pandas podemos obtener una visualizacion mas agradable y comprensible de la siguiente forma:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>sexo</th>\n",
       "      <th>conteo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FEMENINO</td>\n",
       "      <td>75936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MASCULINO</td>\n",
       "      <td>414323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SIN INFORMACION</td>\n",
       "      <td>11708</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              sexo  conteo\n",
       "0         FEMENINO   75936\n",
       "1        MASCULINO  414323\n",
       "2  SIN INFORMACION   11708"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(distintos_valores('sexo'), con)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Por lo tanto, se va a usar pandas para ejecutar dichas consultas."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Con la funcion `distintos_valores` definida, podemos identificar los diferentes valores que tomas las variables y la cantidad de cada valor:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>condicion</th>\n",
       "      <th>conteo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CICLISTA</td>\n",
       "      <td>16915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CONDUCTOR</td>\n",
       "      <td>361433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MOTOCICLISTA</td>\n",
       "      <td>66131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PASAJERO/ACOMPAÑANTE</td>\n",
       "      <td>34041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PEATON</td>\n",
       "      <td>23447</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              condicion  conteo\n",
       "0              CICLISTA   16915\n",
       "1             CONDUCTOR  361433\n",
       "2          MOTOCICLISTA   66131\n",
       "3  PASAJERO/ACOMPAÑANTE   34041\n",
       "4                PEATON   23447"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(distintos_valores('condicion'), con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>estado</th>\n",
       "      <th>conteo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>HERIDO</td>\n",
       "      <td>114199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ILESO</td>\n",
       "      <td>384213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MUERTO</td>\n",
       "      <td>3555</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   estado  conteo\n",
       "0  HERIDO  114199\n",
       "1   ILESO  384213\n",
       "2  MUERTO    3555"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(distintos_valores('estado'), con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>servicio</th>\n",
       "      <th>conteo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>48055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>9513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>188444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>255859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   servicio  conteo\n",
       "0         0   48055\n",
       "1         1    9513\n",
       "2         2  188444\n",
       "3         3  255859\n",
       "4         4      96"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(distintos_valores('servicio'), con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fuga</th>\n",
       "      <th>conteo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>N</td>\n",
       "      <td>493610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S</td>\n",
       "      <td>8357</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  fuga  conteo\n",
       "0    N  493610\n",
       "1    S    8357"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(distintos_valores('fuga'), con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>edad</th>\n",
       "      <th>conteo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>352</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>117</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>118</td>\n",
       "      <td>328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>119</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>120</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>SIN INFORMACION</td>\n",
       "      <td>9653</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>114 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                edad  conteo\n",
       "0                  0     324\n",
       "1                  1     352\n",
       "2                  2     366\n",
       "3                  3     348\n",
       "4                  4     379\n",
       "..               ...     ...\n",
       "109              117      13\n",
       "110              118     328\n",
       "111              119       1\n",
       "112              120       6\n",
       "113  SIN INFORMACION    9653\n",
       "\n",
       "[114 rows x 2 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(distintos_valores('edad'), con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>localidad</th>\n",
       "      <th>conteo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>47826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>28071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>14365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>14426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>10559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>14712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>25426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>58140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>38953</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>50294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>45861</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>25942</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>26188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>17183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>10699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>35710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17</td>\n",
       "      <td>2532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18</td>\n",
       "      <td>13693</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19</td>\n",
       "      <td>21370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    localidad  conteo\n",
       "0           1   47826\n",
       "1           2   28071\n",
       "2           3   14365\n",
       "3           4   14426\n",
       "4           5   10559\n",
       "5           6   14712\n",
       "6           7   25426\n",
       "7           8   58140\n",
       "8           9   38953\n",
       "9          10   50294\n",
       "10         11   45861\n",
       "11         12   25942\n",
       "12         13   26188\n",
       "13         14   17183\n",
       "14         15   10699\n",
       "15         16   35710\n",
       "16         17    2532\n",
       "17         18   13693\n",
       "18         19   21370\n",
       "19         20      17"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(distintos_valores('localidad'), con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>lugar</th>\n",
       "      <th>conteo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>378602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>106047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>3266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>739</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>5238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>4498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>856</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    lugar  conteo\n",
       "0       1  378602\n",
       "1       2  106047\n",
       "2       3     205\n",
       "3       4    3266\n",
       "4       5    1474\n",
       "5       6     739\n",
       "6       7    5238\n",
       "7       8     940\n",
       "8       9       2\n",
       "9      10    4498\n",
       "10     11     856\n",
       "11     12      33\n",
       "12     13      67"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(distintos_valores('lugar'), con)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Esto es de gran utilidad debido a que nos da una idea del contenido de los datos y abre camino a realizar nuevas distintas consultas."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Resultados"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Podemos empezar nuestras consultas mas avanzadas definiendo las siguientes funciones query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "def implicados_edad_sexo(inf, sup):\n",
    "    query = f\"\"\"\n",
    "    SELECT\n",
    "        sexo,\n",
    "        COUNT(*) AS conteo\n",
    "    FROM\n",
    "        Siniestros\n",
    "    WHERE\n",
    "        edad BETWEEN {inf} AND {sup}\n",
    "    GROUP BY\n",
    "        sexo\n",
    "    \"\"\"\n",
    "    return query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "def promedio_edad(variable):\n",
    "    query = f\"\"\"\n",
    "    SELECT\n",
    "        {variable},\n",
    "        AVG(edad) AS edad_promedio\n",
    "    FROM\n",
    "        Siniestros\n",
    "    GROUP BY\n",
    "        {variable}\n",
    "    \"\"\"\n",
    "    return query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "def estado_sexo():\n",
    "    query = \"\"\"\n",
    "    SELECT\n",
    "        sexo,\n",
    "        estado,\n",
    "        COUNT(*) AS conteo\n",
    "    FROM\n",
    "        Siniestros\n",
    "    GROUP BY\n",
    "        sexo,\n",
    "        estado\n",
    "    \"\"\"\n",
    "    return query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "def enfuga_sexo():\n",
    "    query = \"\"\"\n",
    "    SELECT\n",
    "        sexo,\n",
    "        fuga,\n",
    "        COUNT(*) AS conteo\n",
    "    FROM\n",
    "        Siniestros\n",
    "    GROUP BY\n",
    "        sexo,\n",
    "        fuga\n",
    "    \"\"\"\n",
    "    return query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "def enfuga_servicio():\n",
    "    query = \"\"\"\n",
    "    SELECT\n",
    "        servicio,\n",
    "        fuga,\n",
    "        COUNT(*) AS conteo\n",
    "    FROM\n",
    "        Siniestros\n",
    "    GROUP BY\n",
    "        servicio,\n",
    "        fuga\n",
    "    \"\"\"\n",
    "    return query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A partir de estas podemos obtener los diferentes valores de consulta, repasemos una por una:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Con la funcion `implicados_edad_sexo` podemos obtener la cantidad de personas involucradas en siniestros por sexo en un rango de edad:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>sexo</th>\n",
       "      <th>conteo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FEMENINO</td>\n",
       "      <td>34410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MASCULINO</td>\n",
       "      <td>199387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SIN INFORMACION</td>\n",
       "      <td>1001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              sexo  conteo\n",
       "0         FEMENINO   34410\n",
       "1        MASCULINO  199387\n",
       "2  SIN INFORMACION    1001"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(implicados_edad_sexo(18, 35), con)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Por otro lado, la funcion `promedio_edad` permite obtener la edad promedio para la variable indicada, veamos unos ejemplos:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>sexo</th>\n",
       "      <th>edad_promedio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FEMENINO</td>\n",
       "      <td>37.015658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MASCULINO</td>\n",
       "      <td>37.672031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SIN INFORMACION</td>\n",
       "      <td>7.160147</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              sexo  edad_promedio\n",
       "0         FEMENINO      37.015658\n",
       "1        MASCULINO      37.672031\n",
       "2  SIN INFORMACION       7.160147"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(promedio_edad('sexo'), con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>condicion</th>\n",
       "      <th>edad_promedio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CICLISTA</td>\n",
       "      <td>31.937984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CONDUCTOR</td>\n",
       "      <td>38.657367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MOTOCICLISTA</td>\n",
       "      <td>29.344422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PASAJERO/ACOMPAÑANTE</td>\n",
       "      <td>33.613466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PEATON</td>\n",
       "      <td>38.638205</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              condicion  edad_promedio\n",
       "0              CICLISTA      31.937984\n",
       "1             CONDUCTOR      38.657367\n",
       "2          MOTOCICLISTA      29.344422\n",
       "3  PASAJERO/ACOMPAÑANTE      33.613466\n",
       "4                PEATON      38.638205"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(promedio_edad('condicion'), con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>estado</th>\n",
       "      <th>edad_promedio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>HERIDO</td>\n",
       "      <td>33.255808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ILESO</td>\n",
       "      <td>37.919248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MUERTO</td>\n",
       "      <td>38.310267</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   estado  edad_promedio\n",
       "0  HERIDO      33.255808\n",
       "1   ILESO      37.919248\n",
       "2  MUERTO      38.310267"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(promedio_edad('estado'), con)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Observamos que el promedio oscila entre los 29 y los 39 años, esto nos indica una mayor poblacion joven al volante."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Del estado tambien podemos identificar la cantidad de personas por sexo en cada estado usando la funcion `estado_sexo`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>sexo</th>\n",
       "      <th>estado</th>\n",
       "      <th>conteo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FEMENINO</td>\n",
       "      <td>HERIDO</td>\n",
       "      <td>39123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FEMENINO</td>\n",
       "      <td>ILESO</td>\n",
       "      <td>36147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FEMENINO</td>\n",
       "      <td>MUERTO</td>\n",
       "      <td>666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MASCULINO</td>\n",
       "      <td>HERIDO</td>\n",
       "      <td>74126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MASCULINO</td>\n",
       "      <td>ILESO</td>\n",
       "      <td>337536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MASCULINO</td>\n",
       "      <td>MUERTO</td>\n",
       "      <td>2661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SIN INFORMACION</td>\n",
       "      <td>HERIDO</td>\n",
       "      <td>950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SIN INFORMACION</td>\n",
       "      <td>ILESO</td>\n",
       "      <td>10530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>SIN INFORMACION</td>\n",
       "      <td>MUERTO</td>\n",
       "      <td>228</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              sexo  estado  conteo\n",
       "0         FEMENINO  HERIDO   39123\n",
       "1         FEMENINO   ILESO   36147\n",
       "2         FEMENINO  MUERTO     666\n",
       "3        MASCULINO  HERIDO   74126\n",
       "4        MASCULINO   ILESO  337536\n",
       "5        MASCULINO  MUERTO    2661\n",
       "6  SIN INFORMACION  HERIDO     950\n",
       "7  SIN INFORMACION   ILESO   10530\n",
       "8  SIN INFORMACION  MUERTO     228"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(estado_sexo(), con)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Observamos como es menor la proporcion de heridos y muertos con respecto a los ilesos para los hombres que para las mujeres."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finalmente, podemos identificar la catidad de personas que entran en fuga para cada sexo y tipo de servicio:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>sexo</th>\n",
       "      <th>fuga</th>\n",
       "      <th>conteo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FEMENINO</td>\n",
       "      <td>N</td>\n",
       "      <td>75192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FEMENINO</td>\n",
       "      <td>S</td>\n",
       "      <td>744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MASCULINO</td>\n",
       "      <td>N</td>\n",
       "      <td>413339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MASCULINO</td>\n",
       "      <td>S</td>\n",
       "      <td>984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SIN INFORMACION</td>\n",
       "      <td>N</td>\n",
       "      <td>5079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>SIN INFORMACION</td>\n",
       "      <td>S</td>\n",
       "      <td>6629</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              sexo fuga  conteo\n",
       "0         FEMENINO    N   75192\n",
       "1         FEMENINO    S     744\n",
       "2        MASCULINO    N  413339\n",
       "3        MASCULINO    S     984\n",
       "4  SIN INFORMACION    N    5079\n",
       "5  SIN INFORMACION    S    6629"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(enfuga_sexo(), con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>servicio</th>\n",
       "      <th>fuga</th>\n",
       "      <th>conteo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>39698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>S</td>\n",
       "      <td>8357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>9513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>N</td>\n",
       "      <td>188444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>N</td>\n",
       "      <td>255859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4</td>\n",
       "      <td>N</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   servicio fuga  conteo\n",
       "0         0    N   39698\n",
       "1         0    S    8357\n",
       "2         1    N    9513\n",
       "3         2    N  188444\n",
       "4         3    N  255859\n",
       "5         4    N      96"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(enfuga_servicio(), con)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notamos que hay una mayor cantidad de hombres que entran en fuga, pero la proporcion es mauor para las mujeres, de igual forma identificamos que unicamente se registran fugas de servicios del tipo 1."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finalmente, despues de realizar todas las consultas podemos cerrar la conexion con la base de datos."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conclusiones"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Toda la informacion obtenida es de gran utilidad debido a que esta puede ser usada para determinar el publico objetivo de la empresa y en como se van a destinar los recursos, por ejemplo identificando que los promedios de edad podemos identificar que tipo de medios y compañas utilizar para publicitar la empresa.\n",
    "Esto nos indica la importancia de trabajar con bases de datos, dado que usando el lenguaje de `SQL` podemos generar consultas de manera simple que extraigan informacion valiosa de los datos."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Realizado por Juan Andres Guzman para el Modulo 3 'Big Data' del diplomado modular 'Machine learning and Data science' ofrecido por la universidad Nacional de Colombia."
   ]
  }
 ],
 "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}