script_merge.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pre - procesamiento de los datos con Python\n",
    "## Librerias y variables básicas\n",
    "Lista todos los archivos *.csv en `./data` y crea un `DataFrame` de la libreria pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 719,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "import numpy as np\n",
    "import glob\n",
    "import pandas as pd\n",
    "import math\n",
    "allFiles = glob.glob(\"./data/raw/Hurto*.csv\") #obtiene los archivos del directorio ./data que empiecen por Hurto\n",
    "frame = pd.DataFrame() #crea el DataFrame donde se almacenarán los archivos finales\n",
    "list_ = [] # crea una lista vacía donde agregará un lector csv por cada archivo *.csv en ./data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imprimir lista de archivos que empiezan con 'Hurto'  en la carpeta `./data`\n",
    "\n",
    "Imprime todos los archivos *.csv en ./data\n",
    "<br>\n",
    "<strong>Nota:</strong> todos los archivos que se van a concatenar tienen el mismo número de columnas, por lo tanto no es necesario hacer pre-procesamiento de las mismas por el momento. Además, se debe verificar que los archivos en allFiles sean los que deseamos concatenar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 720,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['./data/raw/Hurto_de_Automotores_2016.csv',\n",
       " './data/raw/Hurto_de_motocicletas_2016.csv']"
      ]
     },
     "execution_count": 720,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allFiles"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creando el `DataFrame` con todos los *.csv \n",
    "Se itera sobre todos los archivos *.csv en ./data luego, agrega a la lista creada previamente un  lectores de csv de panda por cada archivo *.csv \n",
    "<br>\n",
    "Finalmente, en el `DataFrame` guarda la concatenación de todos los elementos contenidos en la lista."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 721,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for file_ in allFiles:\n",
    "    df = pd.read_csv(file_,index_col=None, header=0)\n",
    "    list_.append(df)\n",
    "frame = pd.concat(list_)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Verificando el   `DataFrame`\n",
    "### Se verifica el contenido\n",
    "Asegúrese de que el `DataFrame` contiene la información adecuada de los archivos *.csv en ./data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 722,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Fecha</th>\n",
       "      <th>Departamento</th>\n",
       "      <th>Municipio</th>\n",
       "      <th>Día</th>\n",
       "      <th>Hora</th>\n",
       "      <th>Barrio</th>\n",
       "      <th>Zona</th>\n",
       "      <th>Clase de sitio</th>\n",
       "      <th>Arma empleada</th>\n",
       "      <th>Móvil Agresor</th>\n",
       "      <th>...</th>\n",
       "      <th>Clase de empleado</th>\n",
       "      <th>Profesión</th>\n",
       "      <th>Escolaridad</th>\n",
       "      <th>Código DANE</th>\n",
       "      <th>CLASE</th>\n",
       "      <th>MARCA</th>\n",
       "      <th>LINEA</th>\n",
       "      <th>MODELO</th>\n",
       "      <th>COLOR</th>\n",
       "      <th>Cantidad</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01/01/2016 12:00:00 AM</td>\n",
       "      <td>ANTIOQUIA</td>\n",
       "      <td>ENVIGADO</td>\n",
       "      <td>Viernes</td>\n",
       "      <td>12/31/1899 01:30:00 AM</td>\n",
       "      <td>MESA</td>\n",
       "      <td>URBANA</td>\n",
       "      <td>FRENTE A EDIFICIO - VIA PUBLICA</td>\n",
       "      <td>LLAVE MAESTRA</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>...</td>\n",
       "      <td>NO REPORTA</td>\n",
       "      <td>ADMINISTRADOR DE EMPRESAS</td>\n",
       "      <td>SUPERIOR</td>\n",
       "      <td>5266000</td>\n",
       "      <td>AUTOMOVIL</td>\n",
       "      <td>CHEVROLET</td>\n",
       "      <td>CRUZE</td>\n",
       "      <td>2013</td>\n",
       "      <td>NEGRO</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01/01/2016 12:00:00 AM</td>\n",
       "      <td>ATLÁNTICO</td>\n",
       "      <td>BARRANQUILLA (CT)</td>\n",
       "      <td>Viernes</td>\n",
       "      <td>12/31/1899 04:00:00 PM</td>\n",
       "      <td>ALTOS DEL LIMON</td>\n",
       "      <td>URBANA</td>\n",
       "      <td>CONJUNTO RESIDENCIAL</td>\n",
       "      <td>LLAVE MAESTRA</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>...</td>\n",
       "      <td>EMPLEADO PARTICULAR</td>\n",
       "      <td>PSICOLOGO</td>\n",
       "      <td>SUPERIOR</td>\n",
       "      <td>8001000</td>\n",
       "      <td>AUTOMOVIL</td>\n",
       "      <td>NISSAN</td>\n",
       "      <td>VERSA</td>\n",
       "      <td>2013</td>\n",
       "      <td>GRIS</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Fecha Departamento          Municipio      Día  \\\n",
       "0  01/01/2016 12:00:00 AM    ANTIOQUIA           ENVIGADO  Viernes   \n",
       "1  01/01/2016 12:00:00 AM    ATLÁNTICO  BARRANQUILLA (CT)  Viernes   \n",
       "\n",
       "                     Hora           Barrio    Zona  \\\n",
       "0  12/31/1899 01:30:00 AM             MESA  URBANA   \n",
       "1  12/31/1899 04:00:00 PM  ALTOS DEL LIMON  URBANA   \n",
       "\n",
       "                    Clase de sitio  Arma empleada Móvil Agresor   ...     \\\n",
       "0  FRENTE A EDIFICIO - VIA PUBLICA  LLAVE MAESTRA         A PIE   ...      \n",
       "1             CONJUNTO RESIDENCIAL  LLAVE MAESTRA         A PIE   ...      \n",
       "\n",
       "     Clase de empleado                  Profesión Escolaridad Código DANE  \\\n",
       "0           NO REPORTA  ADMINISTRADOR DE EMPRESAS    SUPERIOR     5266000   \n",
       "1  EMPLEADO PARTICULAR                  PSICOLOGO    SUPERIOR     8001000   \n",
       "\n",
       "       CLASE      MARCA  LINEA MODELO  COLOR Cantidad  \n",
       "0  AUTOMOVIL  CHEVROLET  CRUZE   2013  NEGRO        1  \n",
       "1  AUTOMOVIL     NISSAN  VERSA   2013   GRIS        1  \n",
       "\n",
       "[2 rows x 25 columns]"
      ]
     },
     "execution_count": 722,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.head(0)\n",
    "frame.head(1)\n",
    "frame.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Se verifica las filas y columnas \n",
    "En este caso el conjunto de datos tiene 39367 filas y 25 columnas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 723,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(39367, 25)"
      ]
     },
     "execution_count": 723,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Verificar atributos\n",
    "Se verifican los atributos, es decir las columnas del dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 724,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Fecha</th>\n",
       "      <th>Departamento</th>\n",
       "      <th>Municipio</th>\n",
       "      <th>Día</th>\n",
       "      <th>Hora</th>\n",
       "      <th>Barrio</th>\n",
       "      <th>Zona</th>\n",
       "      <th>Clase de sitio</th>\n",
       "      <th>Arma empleada</th>\n",
       "      <th>Móvil Agresor</th>\n",
       "      <th>...</th>\n",
       "      <th>Clase de empleado</th>\n",
       "      <th>Profesión</th>\n",
       "      <th>Escolaridad</th>\n",
       "      <th>Código DANE</th>\n",
       "      <th>CLASE</th>\n",
       "      <th>MARCA</th>\n",
       "      <th>LINEA</th>\n",
       "      <th>MODELO</th>\n",
       "      <th>COLOR</th>\n",
       "      <th>Cantidad</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01/01/2016 12:00:00 AM</td>\n",
       "      <td>ANTIOQUIA</td>\n",
       "      <td>ENVIGADO</td>\n",
       "      <td>Viernes</td>\n",
       "      <td>12/31/1899 01:30:00 AM</td>\n",
       "      <td>MESA</td>\n",
       "      <td>URBANA</td>\n",
       "      <td>FRENTE A EDIFICIO - VIA PUBLICA</td>\n",
       "      <td>LLAVE MAESTRA</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>...</td>\n",
       "      <td>NO REPORTA</td>\n",
       "      <td>ADMINISTRADOR DE EMPRESAS</td>\n",
       "      <td>SUPERIOR</td>\n",
       "      <td>5266000</td>\n",
       "      <td>AUTOMOVIL</td>\n",
       "      <td>CHEVROLET</td>\n",
       "      <td>CRUZE</td>\n",
       "      <td>2013</td>\n",
       "      <td>NEGRO</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Fecha Departamento Municipio      Día  \\\n",
       "0  01/01/2016 12:00:00 AM    ANTIOQUIA  ENVIGADO  Viernes   \n",
       "\n",
       "                     Hora Barrio    Zona                   Clase de sitio  \\\n",
       "0  12/31/1899 01:30:00 AM   MESA  URBANA  FRENTE A EDIFICIO - VIA PUBLICA   \n",
       "\n",
       "   Arma empleada Móvil Agresor   ...    Clase de empleado  \\\n",
       "0  LLAVE MAESTRA         A PIE   ...           NO REPORTA   \n",
       "\n",
       "                   Profesión Escolaridad Código DANE      CLASE      MARCA  \\\n",
       "0  ADMINISTRADOR DE EMPRESAS    SUPERIOR     5266000  AUTOMOVIL  CHEVROLET   \n",
       "\n",
       "   LINEA MODELO  COLOR Cantidad  \n",
       "0  CRUZE   2013  NEGRO        1  \n",
       "\n",
       "[1 rows x 25 columns]"
      ]
     },
     "execution_count": 724,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.head(0)\n",
    "frame.head(1)\n",
    "frame.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cambios sobre los atributos\n",
    "Se identifica que se deben hacer cambios en varios de los atributos para el análisis correcto del conjunto de datos con Weka. Dichos cambios estan especificados en el documento.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Eliminar columnas\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 725,
   "metadata": {},
   "outputs": [],
   "source": [
    "del frame['Código DANE']\n",
    "del frame['Estado civil']\n",
    "del frame['Cantidad']\n",
    "del frame['País de nacimiento']\n",
    "del frame['Clase de empleado']\n",
    "del frame['Profesión']\n",
    "del frame['Escolaridad']\n",
    "del frame['LINEA']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 726,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Fecha', 'Departamento', 'Municipio', 'Día', 'Hora', 'Barrio', 'Zona',\n",
       "       'Clase de sitio', 'Arma empleada', 'Móvil Agresor', 'Móvil Victima',\n",
       "       'Edad', 'Sexo', 'CLASE', 'MARCA', 'MODELO', 'COLOR'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 726,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 727,
   "metadata": {},
   "outputs": [],
   "source": [
    "column_names = ['FECHA','DEPARTAMENTO', 'MUNICIPIO','DIA','HORA','BARRIO','ZONA','SITIO','ARMA','MOVIL_AGRESOR',\n",
    "                 'MOVIL_VICTIMA','EDAD','SEXO','CLASE','MARCA','MODELO','COLOR']\n",
    "\n",
    "#frame[columnas_str] = df[columnas_str].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 728,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['FECHA', 'DEPARTAMENTO', 'MUNICIPIO', 'DIA', 'HORA', 'BARRIO', 'ZONA',\n",
       "       'SITIO', 'ARMA', 'MOVIL_AGRESOR', 'MOVIL_VICTIMA', 'EDAD', 'SEXO',\n",
       "       'CLASE', 'MARCA', 'MODELO', 'COLOR'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 728,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.columns = column_names\n",
    "frame.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Manejo de las fechas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Limpiando fecha  y hora\n",
    "Dado que las fechas tienen hora pero todos tienen la hora a 00:00 y la columna de hora tiene fecha pero del 01/01/1899"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 729,
   "metadata": {},
   "outputs": [],
   "source": [
    "col_loc_fecha = frame.columns.get_loc('FECHA')\n",
    "col_loc_hora = frame.columns.get_loc('HORA')\n",
    " \n",
    "for i in range(len(frame)):\n",
    "    fecha_antes = frame.iloc[i, col_loc_fecha]\n",
    "    fecha_despues = fecha_antes.split(' ')[0]\n",
    "    frame.iloc[i, col_loc_fecha] = fecha_despues\n",
    "   \n",
    "    hora_antes = frame.iloc[i, col_loc_hora]\n",
    "    hora_despues = hora_antes.split(' ', 1)[-1]\n",
    "    frame.iloc[i, col_loc_hora] = hora_despues"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 730,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FECHA</th>\n",
       "      <th>DEPARTAMENTO</th>\n",
       "      <th>MUNICIPIO</th>\n",
       "      <th>DIA</th>\n",
       "      <th>HORA</th>\n",
       "      <th>BARRIO</th>\n",
       "      <th>ZONA</th>\n",
       "      <th>SITIO</th>\n",
       "      <th>ARMA</th>\n",
       "      <th>MOVIL_AGRESOR</th>\n",
       "      <th>MOVIL_VICTIMA</th>\n",
       "      <th>EDAD</th>\n",
       "      <th>SEXO</th>\n",
       "      <th>CLASE</th>\n",
       "      <th>MARCA</th>\n",
       "      <th>MODELO</th>\n",
       "      <th>COLOR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01/01/2016</td>\n",
       "      <td>ANTIOQUIA</td>\n",
       "      <td>ENVIGADO</td>\n",
       "      <td>Viernes</td>\n",
       "      <td>01:30:00 AM</td>\n",
       "      <td>MESA</td>\n",
       "      <td>URBANA</td>\n",
       "      <td>FRENTE A EDIFICIO - VIA PUBLICA</td>\n",
       "      <td>LLAVE MAESTRA</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>53</td>\n",
       "      <td>FEMENINO</td>\n",
       "      <td>AUTOMOVIL</td>\n",
       "      <td>CHEVROLET</td>\n",
       "      <td>2013</td>\n",
       "      <td>NEGRO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01/01/2016</td>\n",
       "      <td>ATLÁNTICO</td>\n",
       "      <td>BARRANQUILLA (CT)</td>\n",
       "      <td>Viernes</td>\n",
       "      <td>04:00:00 PM</td>\n",
       "      <td>ALTOS DEL LIMON</td>\n",
       "      <td>URBANA</td>\n",
       "      <td>CONJUNTO RESIDENCIAL</td>\n",
       "      <td>LLAVE MAESTRA</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>33</td>\n",
       "      <td>MASCULINO</td>\n",
       "      <td>AUTOMOVIL</td>\n",
       "      <td>NISSAN</td>\n",
       "      <td>2013</td>\n",
       "      <td>GRIS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        FECHA DEPARTAMENTO          MUNICIPIO      DIA         HORA  \\\n",
       "0  01/01/2016    ANTIOQUIA           ENVIGADO  Viernes  01:30:00 AM   \n",
       "1  01/01/2016    ATLÁNTICO  BARRANQUILLA (CT)  Viernes  04:00:00 PM   \n",
       "\n",
       "            BARRIO    ZONA                            SITIO           ARMA  \\\n",
       "0             MESA  URBANA  FRENTE A EDIFICIO - VIA PUBLICA  LLAVE MAESTRA   \n",
       "1  ALTOS DEL LIMON  URBANA             CONJUNTO RESIDENCIAL  LLAVE MAESTRA   \n",
       "\n",
       "  MOVIL_AGRESOR MOVIL_VICTIMA EDAD       SEXO      CLASE      MARCA MODELO  \\\n",
       "0         A PIE         A PIE   53   FEMENINO  AUTOMOVIL  CHEVROLET   2013   \n",
       "1         A PIE         A PIE   33  MASCULINO  AUTOMOVIL     NISSAN   2013   \n",
       "\n",
       "   COLOR  \n",
       "0  NEGRO  \n",
       "1   GRIS  "
      ]
     },
     "execution_count": 730,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.head(0)\n",
    "frame.head(1)\n",
    "frame.head(2)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 731,
   "metadata": {},
   "outputs": [],
   "source": [
    "formato_fecha = '%m/%d/%Y'\n",
    "meses = {\n",
    "    1 : 'ENERO',\n",
    "    2 : 'FEBRERO',\n",
    "    3 : 'MARZO',\n",
    "    4 : 'ABRIL',\n",
    "    5 : 'MAYO',\n",
    "    6 : 'JUNIO',\n",
    "    7 : 'JULIO',\n",
    "    8 : 'AGOSTO',\n",
    "    9 : 'SEPTIEMBRE',\n",
    "    10 : 'OCTUBRE',\n",
    "    11 : 'NOVIEMBRE',\n",
    "    12 : 'DICIEMBRE'  \n",
    "}\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Agrupando horas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 732,
   "metadata": {},
   "outputs": [],
   "source": [
    "formato_hora = '%I:%M:%S %p'\n",
    "horas_limite = {\n",
    "    'madrugada_inicio': '1900-01-01 12:01 AM',\n",
    "    'madrugada_final' : '1900-01-01 06:00 AM',\n",
    "    'mov_matutina_inicio': '1900-01-01 06:01 AM',\n",
    "    'mov_matutina_inicio': '1900-01-01 08:30 AM',\n",
    "    'manhana_inicio': '1900-01-01 08:31 AM',\n",
    "    'manhana_final': '1900-01-01 12:00 PM',\n",
    "    'almuerzo_inicio': '1900-01-01 12:01 PM',\n",
    "    'almuerzo_final': '1900-01-01 02:00 PM',\n",
    "    'tarde_inicio': '1900-01-01 02:01 PM',\n",
    "    'tarde_final': '1900-01-01 05:00 PM',\n",
    "    'mov_vespertina_inicio': '1900-01-01 05:01 PM',\n",
    "    'mov_vespertina_final': '1900-01-01 08:00 PM',\n",
    "    'noche_inicio': '1900-01-01 08:01 PM',\n",
    "    'noche_final': '1900-01-02 12:00 AM' # notese que aqui cambie al siguiente dia\n",
    "}\n",
    "def category_by_hour(hora):\n",
    "    '''Da la categoría (de hora) del día dependiendo de la hora que mandemos como parámetro'''\n",
    "    formato_hora_limite = '%Y-%m-%d %I:%M %p'    \n",
    "    madrugada = datetime.strptime( horas_limite['madrugada_inicio'], formato_hora_limite)\n",
    "    mov_matutina = datetime.strptime( horas_limite['mov_matutina_inicio'], formato_hora_limite)\n",
    "    manhana = datetime.strptime( horas_limite['manhana_inicio'], formato_hora_limite)\n",
    "    almuerzo = datetime.strptime( horas_limite['almuerzo_inicio'], formato_hora_limite)\n",
    "    tarde = datetime.strptime( horas_limite['tarde_inicio'], formato_hora_limite)\n",
    "    mov_vespertina = datetime.strptime( horas_limite['mov_vespertina_inicio'], formato_hora_limite)\n",
    "    noche = datetime.strptime( horas_limite['noche_inicio'], formato_hora_limite)\n",
    "   \n",
    "    categoria = 'no-definida'\n",
    "    if hora >= madrugada and hora < mov_matutina:\n",
    "        categoria = 'MADRUGADA'\n",
    "    elif hora >= mov_matutina and hora < manhana:\n",
    "        categoria = 'MOVILIZACIONMATUTINA'\n",
    "    elif hora >= manhana and hora < almuerzo:\n",
    "        categoria = 'MANHANA'\n",
    "    elif hora >= almuerzo and hora < tarde:\n",
    "        categoria = 'ALMUERZO'\n",
    "    elif hora >= tarde and hora < mov_vespertina:\n",
    "        categoria = 'TARDE'\n",
    "    elif hora >= mov_vespertina and hora < noche:\n",
    "        categoria = 'MOVILIZACIONVESPERTINA'\n",
    "    else:\n",
    "        categoria  = 'NOCHE'\n",
    "   \n",
    "    return categoria\n",
    "\n",
    "col_loc_fecha = frame.columns.get_loc('FECHA')\n",
    "col_loc_hora = frame.columns.get_loc('HORA')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 733,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(frame)):\n",
    "    fecha_string = frame.iloc[i, col_loc_fecha]\n",
    "    fecha = datetime.strptime(fecha_string, formato_fecha)\n",
    "    frame.iloc[i, col_loc_fecha] = meses[fecha.month]\n",
    "   \n",
    "    hora_string = frame.iloc[i, col_loc_hora]\n",
    "\n",
    "    hora  = datetime.strptime(hora_string, formato_hora)\n",
    "    frame.iloc[i, col_loc_hora] = category_by_hour(hora)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 734,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FECHA</th>\n",
       "      <th>DEPARTAMENTO</th>\n",
       "      <th>MUNICIPIO</th>\n",
       "      <th>DIA</th>\n",
       "      <th>HORA</th>\n",
       "      <th>BARRIO</th>\n",
       "      <th>ZONA</th>\n",
       "      <th>SITIO</th>\n",
       "      <th>ARMA</th>\n",
       "      <th>MOVIL_AGRESOR</th>\n",
       "      <th>MOVIL_VICTIMA</th>\n",
       "      <th>EDAD</th>\n",
       "      <th>SEXO</th>\n",
       "      <th>CLASE</th>\n",
       "      <th>MARCA</th>\n",
       "      <th>MODELO</th>\n",
       "      <th>COLOR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ENERO</td>\n",
       "      <td>ANTIOQUIA</td>\n",
       "      <td>ENVIGADO</td>\n",
       "      <td>Viernes</td>\n",
       "      <td>MADRUGADA</td>\n",
       "      <td>MESA</td>\n",
       "      <td>URBANA</td>\n",
       "      <td>FRENTE A EDIFICIO - VIA PUBLICA</td>\n",
       "      <td>LLAVE MAESTRA</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>53</td>\n",
       "      <td>FEMENINO</td>\n",
       "      <td>AUTOMOVIL</td>\n",
       "      <td>CHEVROLET</td>\n",
       "      <td>2013</td>\n",
       "      <td>NEGRO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ENERO</td>\n",
       "      <td>ATLÁNTICO</td>\n",
       "      <td>BARRANQUILLA (CT)</td>\n",
       "      <td>Viernes</td>\n",
       "      <td>TARDE</td>\n",
       "      <td>ALTOS DEL LIMON</td>\n",
       "      <td>URBANA</td>\n",
       "      <td>CONJUNTO RESIDENCIAL</td>\n",
       "      <td>LLAVE MAESTRA</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>33</td>\n",
       "      <td>MASCULINO</td>\n",
       "      <td>AUTOMOVIL</td>\n",
       "      <td>NISSAN</td>\n",
       "      <td>2013</td>\n",
       "      <td>GRIS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   FECHA DEPARTAMENTO          MUNICIPIO      DIA       HORA           BARRIO  \\\n",
       "0  ENERO    ANTIOQUIA           ENVIGADO  Viernes  MADRUGADA             MESA   \n",
       "1  ENERO    ATLÁNTICO  BARRANQUILLA (CT)  Viernes      TARDE  ALTOS DEL LIMON   \n",
       "\n",
       "     ZONA                            SITIO           ARMA MOVIL_AGRESOR  \\\n",
       "0  URBANA  FRENTE A EDIFICIO - VIA PUBLICA  LLAVE MAESTRA         A PIE   \n",
       "1  URBANA             CONJUNTO RESIDENCIAL  LLAVE MAESTRA         A PIE   \n",
       "\n",
       "  MOVIL_VICTIMA EDAD       SEXO      CLASE      MARCA MODELO  COLOR  \n",
       "0         A PIE   53   FEMENINO  AUTOMOVIL  CHEVROLET   2013  NEGRO  \n",
       "1         A PIE   33  MASCULINO  AUTOMOVIL     NISSAN   2013   GRIS  "
      ]
     },
     "execution_count": 734,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.head(0)\n",
    "frame.head(1)\n",
    "frame.head(2)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Agrupando clase de sitio por popularidad"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 735,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "VIAS PUBLICAS                              28722\n",
       "FRENTE A RESIDENCIAS - VIA PUBLICA          2903\n",
       "CARRETERA                                   1360\n",
       "PARQUEADERO                                 1173\n",
       "FINCAS Y SIMILARES                           693\n",
       "CASAS DE HABITACION                          671\n",
       "SOBRE ANDEN - VIA PUBLICA                    357\n",
       "FRENTE CONJUNTO - VIA PUBLICA                290\n",
       "TRAMO DE VIA                                 229\n",
       "BAHIA                                        212\n",
       "DENTRO DE LA VIVIENDA                        166\n",
       "FRENTE CLINICA U HOSPITAL - VIA PUBLICA      143\n",
       "TROCHA                                       116\n",
       "CONJUNTO RESIDENCIAL                         115\n",
       "FRENTE EMPRESA - VIA PUBLICA                 113\n",
       "SEMÁFORO                                     112\n",
       "FRENTE A DISCOTECAS                          105\n",
       "FRENTE A COLEGIO - VIA PUBLICA                99\n",
       "FRENTE A ALMACEN - VIA PUBLICA                92\n",
       "GARAJE                                        87\n",
       "PARQUES                                       87\n",
       "FRENTE A EDIFICIO - VIA PUBLICA               81\n",
       "FRENTE IGLESIA - VIA PUBLICA                  64\n",
       "CALLEJÓN                                      52\n",
       "LOCAL COMERCIAL                               49\n",
       "RODANDO SOBRE VIA - VIA PUBLICA               48\n",
       "PLAZAS DE MERCADO                             46\n",
       "CANCHA DE FUTBOL                              44\n",
       "PERIMETRO URBANO                              44\n",
       "BARES, CANTINAS Y SIMILARES                   43\n",
       "                                           ...  \n",
       "ISLA                                           2\n",
       "LINEA FERREA                                   2\n",
       "PARADERO DE BUSES                              2\n",
       "BAÑOS                                          1\n",
       "FONDAS                                         1\n",
       "ESTABLECIMIENTOS SELLADOS                      1\n",
       "MATADEROS, CARNICERIA Y SIMILARES              1\n",
       "PISTAS CLANDESTINAS                            1\n",
       "CANALIZACION                                   1\n",
       "ESTACION M.I.O.                                1\n",
       "SECTOR INDUSTRIA                               1\n",
       "EMISORAS                                       1\n",
       "CASAS DE APUESTAS                              1\n",
       "CORREDORES FLUVIALES                           1\n",
       "BANCOS                                         1\n",
       "CANCHAS DE TEJO                                1\n",
       "HOGAR DE PASO                                  1\n",
       "SEDES SOCIALES, CLUBES O SIMILARES             1\n",
       "FERIA GANADO                                   1\n",
       "TERMINAL MARITIMA                              1\n",
       "BOSQUES                                        1\n",
       "POZO                                           1\n",
       "LAGUNAS                                        1\n",
       "CAÑADUZAL                                      1\n",
       "PUENTE PEATONAL                                1\n",
       "ESTACION DEL METRO                             1\n",
       "PLAZA DE TOROS                                 1\n",
       "LICORERA/ESTANCO 24 HORAS                      1\n",
       "ZONAS AZULES - VIA PUBLICA                     1\n",
       "INTERIOR VEHICULO PARTICULAR                   1\n",
       "Name: SITIO, Length: 131, dtype: int64"
      ]
     },
     "execution_count": 735,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame['SITIO'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 736,
   "metadata": {},
   "outputs": [],
   "source": [
    "col_loc_sitio = frame.columns.get_loc('SITIO')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 737,
   "metadata": {},
   "outputs": [],
   "source": [
    "#indices_via_publica = np.where('VIA PUBLICA' in frame['Clase de sitio'])[0]\n",
    "#indices_via_publica\n",
    "count= 0\n",
    "for i in range(len(frame['SITIO'])):\n",
    "    cell_value = frame.iloc[i, col_loc_sitio]\n",
    "    if 'VIA PUBLICA' in cell_value :\n",
    "        frame.iloc[i, col_loc_sitio] = 'VIAS PUBLICAS'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 738,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['PARQUES', 'GARAJE', 'CALLEJÓN', 'LOCAL COMERCIAL', 'PLAZAS DE MERCADO', 'CANCHA DE FUTBOL', 'PERIMETRO URBANO', 'BARES, CANTINAS Y SIMILARES', 'CENTRO COMERCIAL', 'FRENTE RESTAURANTE', 'APARTAMENTO', 'COLEGIOS, ESCUELAS', 'FRENTE CENTRO COMERCIAL', 'TALLERES', 'ESTADERO', 'PUENTE', 'UNIVERSIDADES', 'BODEGAS Y SIMILARES', 'BOMBA DE GASOLINA', 'RIOS', 'SECTOR RESIDENCIAS', 'HOTELES, RESIDENCIAS, Y SIMILARES.', 'LAVA-AUTOS', 'TALLERES MECANICA', 'GALLERA', 'LOTE BALDIO', 'ALMACENES', 'GLORIETA', 'CLINICAS Y SIMILARES', 'EMPRESA', 'VEHICULO', 'CEMENTERIO', 'BILLARES', 'ESTACIONES DE SERVICIO', 'TIENDA', 'RESTAURANTES', 'FRENTE OFICINA', 'CONSTRUCCION', 'HOSPITALES', 'RANCHERIA', 'DISCOTECAS', 'IGLESIA', 'SUPERMERCADOS', 'TERMINAL DE TRANSPORTES', 'COMPRAVENTA', 'HACIENDA', 'COLISEO', 'CENTRO RECREACIONAL', 'INSTALACIONES DE LA POLICIA', 'CASA EN CONJUNTO CERRADO', 'ESCENARIOS DEPORTIVOS', 'ESTADIO', 'PELUQUERIA Y SIMILARES', 'OFICINAS', 'MINA', 'APARTAMENTO EN CONJUNTO CERRADO', 'NO DEFINIDO', 'CABAÑA', 'AEROPUERTO', 'DROGUERIAS, FARMACIAS', 'ZONA SELVÁTICA', 'RIVERAS', 'PLAYA', 'SALON COMUNAL', 'CASETA COMUNAL', 'PEAJE', 'POTREROS', 'QUEBRADA', 'TALLERES LATONERIA', 'INTERIOR VEHICULO SERVICIO PUBLICO', 'ISLA', 'LINEA FERREA', 'PANADERIAS', 'PUESTO DE CONTROL', 'MONTALLANTAS', 'SECTOR COMERCIO', 'INSTALACIONES GUBERNAMENTALES', 'GRANEROS', 'PUERTO', 'RESGUARDO INDÍGENA', 'PARADERO DE BUSES', 'CANALIZACION', 'PISTAS CLANDESTINAS', 'SECTOR INDUSTRIA', 'ESTACION M.I.O.', 'MATADEROS, CARNICERIA Y SIMILARES', 'FONDAS', 'EMISORAS', 'ESTABLECIMIENTOS SELLADOS', 'BAÑOS', 'CASAS DE APUESTAS', 'PLAZA DE TOROS', 'CANCHAS DE TEJO', 'HOGAR DE PASO', 'CAÑADUZAL', 'CORREDORES FLUVIALES', 'SEDES SOCIALES, CLUBES O SIMILARES', 'BANCOS', 'TERMINAL MARITIMA', 'FERIA GANADO', 'POZO', 'LICORERA/ESTANCO 24 HORAS', 'BOSQUES', 'PUENTE PEATONAL', 'ESTACION DEL METRO', 'LAGUNAS', 'INTERIOR VEHICULO PARTICULAR']\n"
     ]
    }
   ],
   "source": [
    "arr = []\n",
    "ite = 0\n",
    "for i in frame['SITIO'].value_counts():\n",
    "    if i < 101:\n",
    "        arr.append(frame['SITIO'].value_counts().keys()[ite])\n",
    "    ite = ite+1\n",
    "print(arr)\n",
    "for i in range(len(frame['SITIO'])):\n",
    "    cell_value = frame.iloc[i,col_loc_sitio]\n",
    "    if cell_value in arr:\n",
    "        frame.iloc[i,col_loc_sitio] = 'OTRO'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 739,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "VIAS PUBLICAS            32924\n",
       "OTRO                      1491\n",
       "CARRETERA                 1360\n",
       "PARQUEADERO               1173\n",
       "FINCAS Y SIMILARES         693\n",
       "CASAS DE HABITACION        671\n",
       "TRAMO DE VIA               229\n",
       "BAHIA                      212\n",
       "DENTRO DE LA VIVIENDA      166\n",
       "TROCHA                     116\n",
       "CONJUNTO RESIDENCIAL       115\n",
       "SEMÁFORO                   112\n",
       "FRENTE A DISCOTECAS        105\n",
       "Name: SITIO, dtype: int64"
      ]
     },
     "execution_count": 739,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame['SITIO'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Agrupando arma empleada por popularidad"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 740,
   "metadata": {},
   "outputs": [],
   "source": [
    "frame['ARMA'].value_counts()\n",
    "\n",
    "col_loc_arma = frame.columns.get_loc('ARMA')\n",
    "\n",
    "arma_jeringa = np.where(frame['ARMA'] == 'JERINGA')[0]\n",
    "arma_cortante = np.where(frame['ARMA'] == 'CORTANTES')[0]\n",
    "arma_cortopunzante = np.where(frame['ARMA'] == 'CORTOPUNZANTES')[0]\n",
    "arma_punzante = np.where(frame['ARMA'] == 'PUNZANTES')[0]\n",
    "arma_blanca = np.where(frame['ARMA'] == 'ARMAS BLANCAS')[0]\n",
    "\n",
    "armas_cortopunzantes = np.concatenate((arma_jeringa, arma_cortante,arma_cortopunzante,arma_punzante,arma_blanca))\n",
    "\n",
    "for arma in armas_cortopunzantes:\n",
    "    frame.iloc[arma, col_loc_arma] = 'ARMA BLANCA / CORTOPUNZANTE'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 741,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "LLAVE MAESTRA                  19382\n",
       "ARMA DE FUEGO                  11780\n",
       "SIN EMPLEO DE ARMAS             5280\n",
       "CONTUNDENTES                    1529\n",
       "ARMA BLANCA / CORTOPUNZANTE     1200\n",
       "ESCOPOLAMINA                     195\n",
       "-                                  1\n",
       "Name: ARMA, dtype: int64"
      ]
     },
     "execution_count": 741,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame['ARMA'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Agrupando Móvil agresor y victima por popularidad"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 742,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "A PIE                    29293\n",
       "PASAJERO MOTOCICLETA      6453\n",
       "CONDUCTOR MOTOCICLETA     2431\n",
       "CONDUCTOR VEHICULO         671\n",
       "-                          137\n",
       "PASAJERO TAXI              136\n",
       "PASAJERO VEHICULO          116\n",
       "CONDUCTOR TAXI              62\n",
       "BICICLETA                   52\n",
       "PASAJERO BUS                 7\n",
       "PASAJERO METRO               6\n",
       "CONDUCTOR BUS                2\n",
       "TRIPULANTE AERONAVE          1\n",
       "Name: MOVIL_AGRESOR, dtype: int64"
      ]
     },
     "execution_count": 742,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame['MOVIL_AGRESOR'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 743,
   "metadata": {},
   "outputs": [],
   "source": [
    "col_movil_agresor= frame.columns.get_loc('MOVIL_AGRESOR')\n",
    "for i in range(len(frame['MOVIL_AGRESOR'])):\n",
    "    cell_value = frame.iloc[i, col_movil_agresor]\n",
    "    if 'PASAJERO' in cell_value :\n",
    "        frame.iloc[i, col_movil_agresor] = 'PASAJERO'\n",
    "    elif 'CONDUCTOR' in cell_value :\n",
    "        frame.iloc[i, col_movil_agresor] = 'CONDUCTOR'\n",
    "    elif '-' in cell_value:\n",
    "        frame.iloc[i, col_movil_agresor] = 'OTRO'\n",
    "    elif 'TRIPULANTE AERONAVE' in cell_value:\n",
    "        frame.iloc[i, col_movil_agresor] = 'OTRO'\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 744,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "A PIE        29293\n",
       "PASAJERO      6718\n",
       "CONDUCTOR     3166\n",
       "OTRO           138\n",
       "BICICLETA       52\n",
       "Name: MOVIL_AGRESOR, dtype: int64"
      ]
     },
     "execution_count": 744,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame['MOVIL_AGRESOR'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 745,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "A PIE                    25180\n",
       "CONDUCTOR MOTOCICLETA    11512\n",
       "CONDUCTOR VEHICULO        2145\n",
       "PASAJERO MOTOCICLETA       191\n",
       "-                          161\n",
       "CONDUCTOR TAXI             143\n",
       "CONDUCTOR BUS               17\n",
       "BICICLETA                   11\n",
       "PASAJERO VEHICULO            6\n",
       "PASAJERO TAXI                1\n",
       "Name: MOVIL_VICTIMA, dtype: int64"
      ]
     },
     "execution_count": 745,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame['MOVIL_VICTIMA'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 746,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "col_movil_victima= frame.columns.get_loc('MOVIL_VICTIMA')\n",
    "for i in range(len(frame['MOVIL_AGRESOR'])):\n",
    "    cell_value = frame.iloc[i, col_movil_victima]\n",
    "    if 'PASAJERO' in cell_value :\n",
    "        frame.iloc[i, col_movil_victima] = 'PASAJERO'\n",
    "    elif 'CONDUCTOR' in cell_value :\n",
    "        frame.iloc[i, col_movil_victima] = 'CONDUCTOR'\n",
    "    elif '-' in cell_value:\n",
    "        frame.iloc[i, col_movil_victima] = 'OTRO'\n",
    "    elif 'TRIPULANTE AERONAVE' in cell_value:\n",
    "        frame.iloc[i, col_movil_victima] = 'OTRO'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 747,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "A PIE        25180\n",
       "CONDUCTOR    13817\n",
       "PASAJERO       198\n",
       "OTRO           161\n",
       "BICICLETA       11\n",
       "Name: MOVIL_VICTIMA, dtype: int64"
      ]
     },
     "execution_count": 747,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame['MOVIL_VICTIMA'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Verificando rango de edad"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 748,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "666 0-18\n",
      "16582 19-30\n",
      "10534 31-40\n",
      "10332 41-60\n",
      "1106 61\n",
      "147 Nulos\n"
     ]
    }
   ],
   "source": [
    "frame['EDAD'].value_counts()\n",
    "col_edad = frame.columns.get_loc('EDAD')\n",
    "count_16_18 = 0\n",
    "count_19_30 = 0\n",
    "count_31_40 = 0\n",
    "count_41_60 = 0\n",
    "count_61 = 0\n",
    "null=0\n",
    "for i in range(len(frame['EDAD'])):\n",
    "    cell_value = (frame.iloc[i, col_edad])\n",
    "    if '-' not in cell_value:\n",
    "        cell_value = int(cell_value)\n",
    "        if cell_value < 19:\n",
    "            #if int(cell_value) <19:\n",
    "            count_16_18 = count_16_18+1\n",
    "        elif cell_value < 31:\n",
    "            count_19_30 = count_19_30+1\n",
    "        elif cell_value < 40:\n",
    "            count_31_40 = count_31_40+1\n",
    "        elif cell_value < 61:\n",
    "            count_41_60 = count_41_60+1\n",
    "        else:\n",
    "            count_61= count_61+1\n",
    "    else:\n",
    "        null=null+1\n",
    "            \n",
    "print(count_16_18, '0-18') #ADOLESCENTE\n",
    "print(count_19_30, '19-30') # JOVEN\n",
    "print(count_31_40, '31-40') #ADULTO_JOVEN\n",
    "print(count_41_60, '41-60') # ADULTO_MAYOR\n",
    "print(count_61, '61')   # ANCIANO\n",
    "print(null, \"Nulos\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Eliminar filas sin edad y con edad menor de 16\n",
    "Se elimina por lógica de negocio ya que legalmente un menor de 16 años no puede manejar\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 749,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "39367\n",
      "39135\n"
     ]
    }
   ],
   "source": [
    "print(len(frame))\n",
    "indices_edad = frame['EDAD'] == '-'\n",
    "frame = frame.drop(frame.index[indices_edad])\n",
    "print(len(frame))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 750,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#tipo de columna a numerica\n",
    "frame['EDAD']=frame['EDAD'].apply(pd.to_numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 751,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "39135\n",
      "39052\n"
     ]
    }
   ],
   "source": [
    "print(len(frame))\n",
    "indices_edad = frame['EDAD'] < 16\n",
    "frame = frame.drop(frame.index[indices_edad])\n",
    "print(len(frame))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 752,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for i in range(len(frame['EDAD'])):\n",
    "    cell_value = (frame.iloc[i, col_edad])\n",
    "    if cell_value < 19:\n",
    "        frame.iloc[i, col_edad] = 'ADOLESCENTE'\n",
    "    elif cell_value < 31:\n",
    "        frame.iloc[i, col_edad] = 'JOVEN'\n",
    "    elif cell_value < 40:\n",
    "        frame.iloc[i, col_edad] = 'ADULTO_JOVEN'\n",
    "    elif cell_value < 61:\n",
    "        frame.iloc[i, col_edad] = 'ADULTO_CONTEMPORANEO'\n",
    "    else:\n",
    "        frame.iloc[i, col_edad] = 'ADULTO_MAYOR'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 753,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "JOVEN                   16548\n",
       "ADULTO_JOVEN            10497\n",
       "ADULTO_CONTEMPORANEO    10296\n",
       "ADULTO_MAYOR             1102\n",
       "ADOLESCENTE               609\n",
       "Name: EDAD, dtype: int64"
      ]
     },
     "execution_count": 753,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame['EDAD'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Agrupando por Clase, Marca, modelo y color"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 754,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MOTOCICLETA              31057\n",
       "AUTOMOVIL                 4747\n",
       "CAMIONETA                 1814\n",
       "CAMPERO                    444\n",
       "CAMION                     376\n",
       "VEHICULO-PANEL             130\n",
       "TAXI                        98\n",
       "MOTO-CARRO                  78\n",
       "TRACTO_CAMION               63\n",
       "VOLQUETA                    61\n",
       "FURGON                      38\n",
       "MICROBUS                    32\n",
       "TRACTOMULA                  19\n",
       "BUSETA                      16\n",
       "TRACTOR                     15\n",
       "RETROEXCAVADORA             15\n",
       "BUS                         12\n",
       "TRAILER                      6\n",
       "GRUA                         6\n",
       "MINICARGADOR                 4\n",
       "CUATRIMOTO                   3\n",
       "CARROTANQUE                  3\n",
       "MONTACARGA                   2\n",
       "MOTONIVELADORA               2\n",
       "MEZCLADORA (VEHICULO)        2\n",
       "BULDOZER                     2\n",
       "COLECTIVO                    2\n",
       "NIVELADORA                   1\n",
       "BUS EJECUTIVO                1\n",
       "VOLCO                        1\n",
       "VOLTEO                       1\n",
       "AMBULANCIA                   1\n",
       "Name: CLASE, dtype: int64"
      ]
     },
     "execution_count": 754,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame['CLASE'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Categorización\n",
    "Se identifican varios conjuntos de automóviles. Entre estos:\n",
    "- Transporte público [TAXI, MICROBUS, BUSETA, BUS, COLECTIVO, BUS EJECUTIVO]\n",
    "- Transporte pesado [TRACTO_CAMION, VOLQUETA, FURGON, TRACTOMULA, TRACTOR, RETROEXCAVADORA, TRAILER, GRUA, CARROTANQUE, MONTACARGA, MEZCLADORA, BULDOZER, VOLTEO, NIVELADORA]\n",
    "- Transporte liviano [AUTOMOVIL, CAMIONETA, CAMPERO, VEHICULO-PANEL, AMBULANCIA, MINICARGADOR, CUATRIMOTO, MOTONIVELADORA ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 755,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "col_loc_clase = frame.columns.get_loc('CLASE')\n",
    "transporte_publico = ['TAXI', 'MICROBUS','BUSETA','BUS','COLECTIVO']\n",
    "transporte_pesado = ['TRACTO','VOLQUETA','FURGON','RETRO','GRUA','TRAILER','CARROTANQUE','MONTACARGA','MEZCLADORA','BULDOZER','VOLTEO','VOLCO','NIVELADORA','CAMION']\n",
    "transporte_liviano = ['AUTOMOVIL','CAMIONETA','CAMPERO','VEHICULO-PANEL','AMBULANCIA','MINICARGADOR','CUATRIMOTO','MOTONIVELADORA']\n",
    "motocicleta_moto_carro= ['MOTO']\n",
    "\n",
    "for i in range(len(frame['CLASE'])):\n",
    "    cell_value = frame.iloc[i, col_loc_clase]\n",
    "    for j in transporte_publico:\n",
    "        if j in cell_value:\n",
    "            frame.iloc[i,col_loc_clase] = 'TRANSPORTE_PUBLICO'\n",
    "    for j in transporte_pesado:\n",
    "        if j in cell_value:\n",
    "            frame.iloc[i,col_loc_clase] = 'TRANSPORTE_PESADO'\n",
    "    for j in transporte_liviano:\n",
    "        if j in cell_value:\n",
    "            frame.iloc[i,col_loc_clase] = 'TRANSPORTE_LIVIANO'\n",
    "    for j in motocicleta_moto_carro:\n",
    "        if j in cell_value:\n",
    "            frame.iloc[i,col_loc_clase] = 'TRANSPORTE_BIPERSONAL'\n",
    "            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 756,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "TRANSPORTE_BIPERSONAL    31140\n",
       "TRANSPORTE_LIVIANO        7140\n",
       "TRANSPORTE_PESADO          611\n",
       "TRANSPORTE_PUBLICO         161\n",
       "Name: CLASE, dtype: int64"
      ]
     },
     "execution_count": 756,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame['CLASE'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 757,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NEGRO                15617\n",
       "BLANCO                5245\n",
       "AZUL                  5115\n",
       "ROJO                  4529\n",
       "GRIS                  2194\n",
       "NEGRO GRAFITO         1095\n",
       "VERDE                  915\n",
       "PLATA                  693\n",
       "NEGRO Y BLANCO         542\n",
       "NARANJA                418\n",
       "AMARILLO               356\n",
       "PLATEADO               274\n",
       "ROJO - BLANCO          249\n",
       "BEIGE                  214\n",
       "AZUL BALTICO           189\n",
       "VERDE OSCURO           153\n",
       "PLATA NIQUEL           106\n",
       "VIOLETA                 83\n",
       "CHAMPAÑA                81\n",
       "MORADO CORDOBES         78\n",
       "BLANCO Y VERDE          68\n",
       "VINOTINTO               63\n",
       "AMARILLO Y NEGRO        60\n",
       "AZUL Y BLANCO           49\n",
       "MARRON                  48\n",
       "PERLA                   47\n",
       "CAFE                    46\n",
       "GRIS PLATINO            46\n",
       "NEGRO Y NIQUEL          46\n",
       "ESTRATO DE PERLA        41\n",
       "                     ...  \n",
       "CLASICO                  2\n",
       "ZAFIRO                   2\n",
       "FIRMAMENTO               2\n",
       "CLARO                    2\n",
       "LIMON                    2\n",
       "MENTA                    2\n",
       "COBRE                    2\n",
       "AMARILLO Y BLANCO        2\n",
       "IMPERIAL                 2\n",
       "AZUL BAHIA               2\n",
       "CEREZA                   1\n",
       "TERRANOVA                1\n",
       "AZUL LISBOA              1\n",
       "BOSQUE                   1\n",
       "PARDO LAGUNA             1\n",
       "AZUL LAGUNA              1\n",
       "SAFARI                   1\n",
       "BRUMA                    1\n",
       "ZAPOTE                   1\n",
       "CARAMELO                 1\n",
       "BAMBOO                   1\n",
       "PACIFICO                 1\n",
       "ACUARELA                 1\n",
       "COLONIAL                 1\n",
       "HINDU                    1\n",
       "DALI                     1\n",
       "TERRACOTA                1\n",
       "GRAFITO                  1\n",
       "LADRILLO                 1\n",
       "FIESTA                   1\n",
       "Name: COLOR, Length: 93, dtype: int64"
      ]
     },
     "execution_count": 757,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame['COLOR'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 758,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['ROSADO', 'VARIOS COLORES', 'DORADO', 'AMARILLO TRANSITO', 'STRATO PLATA', 'SILICE TORNADO', 'AZUL CIELO', 'HABANO', 'METALIZADO', 'MARRON Y NIQUEL', 'AZUL MEDIO', 'BRONCE', 'ROJO-MARRON-ROYAL', 'ORO', 'AZUL ZAFIRO', 'ACERO', 'CENIZA GRAFITO', 'PLATINO', 'ROJO-VERDE', 'CELESTE', 'ABANO', 'MARFIL', 'CREMA', 'CROMADO', 'QUARZO', 'LILA', 'TURQUEZA', 'MANDARINA', 'MOSTAZA', 'FUEGO', 'CINZA EJECUTIVO', 'ROBLE', '-', 'CLASICO', 'ZAFIRO', 'FIRMAMENTO', 'CLARO', 'LIMON', 'MENTA', 'COBRE', 'AMARILLO Y BLANCO', 'IMPERIAL', 'AZUL BAHIA', 'CEREZA', 'TERRANOVA', 'AZUL LISBOA', 'BOSQUE', 'PARDO LAGUNA', 'AZUL LAGUNA', 'SAFARI', 'BRUMA', 'ZAPOTE', 'CARAMELO', 'BAMBOO', 'PACIFICO', 'ACUARELA', 'COLONIAL', 'HINDU', 'DALI', 'TERRACOTA', 'GRAFITO', 'LADRILLO', 'FIESTA']\n"
     ]
    }
   ],
   "source": [
    "col_loc_color = frame.columns.get_loc('COLOR')\n",
    "arr = []\n",
    "ite = 0\n",
    "for i in frame['COLOR'].value_counts():\n",
    "    if i < 40:\n",
    "        arr.append(frame['COLOR'].value_counts().keys()[ite])\n",
    "    ite = ite+1\n",
    "print(arr)\n",
    "for i in range(len(frame['COLOR'])):\n",
    "    cell_value = frame.iloc[i,col_loc_color]\n",
    "    if cell_value in arr:\n",
    "        frame.iloc[i,col_loc_color] = 'OTRO'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 759,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NEGRO               15617\n",
       "BLANCO               5245\n",
       "AZUL                 5115\n",
       "ROJO                 4529\n",
       "GRIS                 2194\n",
       "NEGRO GRAFITO        1095\n",
       "VERDE                 915\n",
       "PLATA                 693\n",
       "NEGRO Y BLANCO        542\n",
       "NARANJA               418\n",
       "OTRO                  392\n",
       "AMARILLO              356\n",
       "PLATEADO              274\n",
       "ROJO - BLANCO         249\n",
       "BEIGE                 214\n",
       "AZUL BALTICO          189\n",
       "VERDE OSCURO          153\n",
       "PLATA NIQUEL          106\n",
       "VIOLETA                83\n",
       "CHAMPAÑA               81\n",
       "MORADO CORDOBES        78\n",
       "BLANCO Y VERDE         68\n",
       "VINOTINTO              63\n",
       "AMARILLO Y NEGRO       60\n",
       "AZUL Y BLANCO          49\n",
       "MARRON                 48\n",
       "PERLA                  47\n",
       "GRIS PLATINO           46\n",
       "CAFE                   46\n",
       "NEGRO Y NIQUEL         46\n",
       "ESTRATO DE PERLA       41\n",
       "Name: COLOR, dtype: int64"
      ]
     },
     "execution_count": 759,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame['COLOR'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 760,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2016        6560\n",
       "2015        5289\n",
       "2014        4441\n",
       "2013        3321\n",
       "2012        2789\n",
       "2017        1990\n",
       "2011        1898\n",
       "2008        1628\n",
       "2010        1440\n",
       "2007        1313\n",
       "2009        1259\n",
       "2006        1099\n",
       "2005         641\n",
       "2000         459\n",
       "1998         432\n",
       "1995         409\n",
       "2004         390\n",
       "1996         388\n",
       "1994         372\n",
       "1997         344\n",
       "2003         316\n",
       "1993         267\n",
       "2002         207\n",
       "1999         206\n",
       "2001         183\n",
       "1992         170\n",
       "1990         149\n",
       "1989         136\n",
       "1991         129\n",
       "1988         122\n",
       "            ... \n",
       "1967           4\n",
       "1959           3\n",
       "0              3\n",
       "1970           3\n",
       "1952           3\n",
       "1957           2\n",
       "1954           2\n",
       "1956           2\n",
       "1960           2\n",
       "1968           2\n",
       "1964           2\n",
       "1973           2\n",
       "1963           2\n",
       "1953           1\n",
       "2051           1\n",
       "1966           1\n",
       "20017          1\n",
       "11             1\n",
       "10             1\n",
       "1897           1\n",
       "20114          1\n",
       "20008          1\n",
       "90             1\n",
       "93             1\n",
       "96             1\n",
       "2102           1\n",
       "15             1\n",
       "20082008       1\n",
       "98             1\n",
       "95             1\n",
       "Name: MODELO, Length: 79, dtype: int64"
      ]
     },
     "execution_count": 760,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame['MODELO'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 761,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "99\n"
     ]
    }
   ],
   "source": [
    "col_loc_modelo = frame.columns.get_loc('MODELO')\n",
    "count_invalid = 0\n",
    "for i in range(len(frame['MODELO'])):\n",
    "    cell_value = frame.iloc[i, col_loc_modelo]\n",
    "    if cell_value == '-':\n",
    "        count_invalid = count_invalid+1\n",
    "    elif len(cell_value) != 4:\n",
    "        count_invalid = count_invalid+1\n",
    "    elif int(cell_value) > 2017:\n",
    "        count_invalid = count_invalid+1\n",
    "print(count_not_4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Eliminar todos los registros inválidos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 762,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "39052\n",
      "38944\n"
     ]
    }
   ],
   "source": [
    "#eliminar registros nulos\n",
    "print(len(frame))\n",
    "indices_modelo = frame['MODELO'] == '-'\n",
    "frame = frame.drop(frame.index[indices_modelo])\n",
    "print(len(frame))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 763,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[120, 3647, 3647, 3899, 4335, 5742, 6500, 8018, 8050, 10699, 19378, 23111, 23111, 27529, 30517, 30517, 30762, 32102, 32633, 32633, 38395]\n"
     ]
    }
   ],
   "source": [
    "arr_indexes = []\n",
    "for i in range(len(frame['MODELO'])):\n",
    "    cell = frame.iloc[i,col_loc_modelo]\n",
    "    if len(cell) != 4:\n",
    "        arr_indexes.append(i)\n",
    "    if int(cell) > 2017:\n",
    "        arr_indexes.append(i)\n",
    "print(arr_indexes)\n",
    "frame = frame.drop(frame.index[arr_indexes])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 764,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2016    6557\n",
       "2015    5287\n",
       "2014    4439\n",
       "2013    3318\n",
       "2012    2787\n",
       "2017    1990\n",
       "2011    1895\n",
       "2008    1626\n",
       "2010    1438\n",
       "2007    1313\n",
       "2009    1256\n",
       "2006    1099\n",
       "2005     640\n",
       "2000     459\n",
       "1998     431\n",
       "1995     409\n",
       "2004     390\n",
       "1996     386\n",
       "1994     372\n",
       "1997     343\n",
       "2003     316\n",
       "1993     265\n",
       "2002     207\n",
       "1999     205\n",
       "2001     183\n",
       "1992     170\n",
       "1990     149\n",
       "1989     136\n",
       "1991     129\n",
       "1988     121\n",
       "        ... \n",
       "1986      62\n",
       "1984      43\n",
       "1981      39\n",
       "1980      37\n",
       "1983      33\n",
       "1979      31\n",
       "1978      27\n",
       "1977      23\n",
       "1974      14\n",
       "1975      13\n",
       "1976       9\n",
       "1961       6\n",
       "1969       5\n",
       "1971       5\n",
       "1972       5\n",
       "1967       4\n",
       "1959       3\n",
       "1952       3\n",
       "1970       3\n",
       "1964       2\n",
       "1956       2\n",
       "1973       2\n",
       "1957       2\n",
       "1954       2\n",
       "1963       2\n",
       "1968       2\n",
       "1960       2\n",
       "1953       1\n",
       "1897       1\n",
       "1966       1\n",
       "Name: MODELO, Length: 63, dtype: int64"
      ]
     },
     "execution_count": 764,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame['MODELO'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Eliminando caracteres que pueden causar error\n",
    "-  =,',\",*,-,%\n",
    "- tildes\n",
    "- ñ's"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 765,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def replace_accent_marks(inp):\n",
    "    text = inp\n",
    "    text=text.replace('á','a')\n",
    "    text=text.replace('é','e')\n",
    "    text=text.replace('í','i')\n",
    "    text=text.replace('ó','o')\n",
    "    text=text.replace('ú','u')\n",
    "    text=text.replace('Á','A')\n",
    "    text=text.replace('É','E')\n",
    "    text=text.replace('Í','I')\n",
    "    text=text.replace('Ó','O')\n",
    "    text=text.replace('Ú','U')\n",
    "    text=text.replace('Ñ','N')\n",
    "    text=text.replace('ñ','n')\n",
    "    return text\n",
    "def replace_conflicting_characters(inp):\n",
    "    text = inp\n",
    "    text=text.replace(',',' ')\n",
    "    text=text.replace('=',' ')\n",
    "    text=text.replace(\"\\'\",' ')\n",
    "    text=text.replace('+',' ')\n",
    "    text=text.replace('*',' ')\n",
    "    text=text.replace('-',' ')\n",
    "    text=text.replace('%',' ')\n",
    "    text=text.replace('\\\"',' ')\n",
    "    return text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 766,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "col_locs = range(len(frame.columns))\n",
    "\n",
    "for i in range(len(frame['DEPARTAMENTO'])):\n",
    "    for j in col_locs:\n",
    "        actual = frame.iloc[i,j]\n",
    "        temp_str = replace_accent_marks(actual)\n",
    "        final = replace_conflicting_characters(temp_str)\n",
    "        #final = replace_conflicting_characters(actual)\n",
    "        frame.iloc[i,j] = final\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 767,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FECHA</th>\n",
       "      <th>DEPARTAMENTO</th>\n",
       "      <th>MUNICIPIO</th>\n",
       "      <th>DIA</th>\n",
       "      <th>HORA</th>\n",
       "      <th>BARRIO</th>\n",
       "      <th>ZONA</th>\n",
       "      <th>SITIO</th>\n",
       "      <th>ARMA</th>\n",
       "      <th>MOVIL_AGRESOR</th>\n",
       "      <th>MOVIL_VICTIMA</th>\n",
       "      <th>EDAD</th>\n",
       "      <th>SEXO</th>\n",
       "      <th>CLASE</th>\n",
       "      <th>MARCA</th>\n",
       "      <th>MODELO</th>\n",
       "      <th>COLOR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ENERO</td>\n",
       "      <td>ANTIOQUIA</td>\n",
       "      <td>ENVIGADO</td>\n",
       "      <td>Viernes</td>\n",
       "      <td>MADRUGADA</td>\n",
       "      <td>MESA</td>\n",
       "      <td>URBANA</td>\n",
       "      <td>VIAS PUBLICAS</td>\n",
       "      <td>LLAVE MAESTRA</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>ADULTO_CONTEMPORANEO</td>\n",
       "      <td>FEMENINO</td>\n",
       "      <td>TRANSPORTE_LIVIANO</td>\n",
       "      <td>CHEVROLET</td>\n",
       "      <td>2013</td>\n",
       "      <td>NEGRO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ENERO</td>\n",
       "      <td>ATLANTICO</td>\n",
       "      <td>BARRANQUILLA (CT)</td>\n",
       "      <td>Viernes</td>\n",
       "      <td>TARDE</td>\n",
       "      <td>ALTOS DEL LIMON</td>\n",
       "      <td>URBANA</td>\n",
       "      <td>CONJUNTO RESIDENCIAL</td>\n",
       "      <td>LLAVE MAESTRA</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>ADULTO_JOVEN</td>\n",
       "      <td>MASCULINO</td>\n",
       "      <td>TRANSPORTE_LIVIANO</td>\n",
       "      <td>NISSAN</td>\n",
       "      <td>2013</td>\n",
       "      <td>GRIS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   FECHA DEPARTAMENTO          MUNICIPIO      DIA       HORA           BARRIO  \\\n",
       "0  ENERO    ANTIOQUIA           ENVIGADO  Viernes  MADRUGADA             MESA   \n",
       "1  ENERO    ATLANTICO  BARRANQUILLA (CT)  Viernes      TARDE  ALTOS DEL LIMON   \n",
       "\n",
       "     ZONA                 SITIO           ARMA MOVIL_AGRESOR MOVIL_VICTIMA  \\\n",
       "0  URBANA         VIAS PUBLICAS  LLAVE MAESTRA         A PIE         A PIE   \n",
       "1  URBANA  CONJUNTO RESIDENCIAL  LLAVE MAESTRA         A PIE         A PIE   \n",
       "\n",
       "                   EDAD       SEXO               CLASE      MARCA MODELO  \\\n",
       "0  ADULTO_CONTEMPORANEO   FEMENINO  TRANSPORTE_LIVIANO  CHEVROLET   2013   \n",
       "1          ADULTO_JOVEN  MASCULINO  TRANSPORTE_LIVIANO     NISSAN   2013   \n",
       "\n",
       "   COLOR  \n",
       "0  NEGRO  \n",
       "1   GRIS  "
      ]
     },
     "execution_count": 767,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.head(0)\n",
    "frame.head(1)\n",
    "frame.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exportando trabajo temporal en un archivo *.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 768,
   "metadata": {},
   "outputs": [],
   "source": [
    "frame.to_csv('data/pre_processed/pre_processed_hurto_automoviles.csv', mode='a', header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 769,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ANTIOQUIA             8427\n",
       "VALLE                 6399\n",
       "CUNDINAMARCA          5799\n",
       "CAUCA                 2637\n",
       "ATLANTICO             1800\n",
       "NORTE DE SANTANDER    1504\n",
       "CESAR                 1497\n",
       "GUAJIRA               1305\n",
       "NARINO                1008\n",
       "SANTANDER              985\n",
       "META                   962\n",
       "HUILA                  921\n",
       "CORDOBA                690\n",
       "CASANARE               634\n",
       "CAQUETA                560\n",
       "MAGDALENA              532\n",
       "SUCRE                  523\n",
       "BOLIVAR                468\n",
       "TOLIMA                 434\n",
       "RISARALDA              344\n",
       "CHOCO                  314\n",
       "PUTUMAYO               255\n",
       "QUINDIO                228\n",
       "ARAUCA                 198\n",
       "CALDAS                 171\n",
       "BOYACA                 100\n",
       "SAN ANDRES              94\n",
       "AMAZONAS                50\n",
       "GUAVIARE                47\n",
       "VICHADA                 29\n",
       "GUAINIA                  2\n",
       "VAUPES                   1\n",
       "Name: DEPARTAMENTO, dtype: int64"
      ]
     },
     "execution_count": 769,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame['DEPARTAMENTO'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Leyendo fuentes de datos alternativas\n",
    "### Fiscalía\n",
    "Los datos de la Fiscalía se obtuvieron haciendo un scraping sobre los gráficos  <a src=\"http://www.fiscalia.gov.co/atlas2014/\">Fiscalía Atlas</a> de las gráficas de cobertura de fiscales por seccional y de cobertura población por seccional. A partir del archivo .xls generado se quitaron tildes y se exporto un archivo .csv para procesar acontinuación."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 770,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>seccional</th>\n",
       "      <th>fiscales</th>\n",
       "      <th>poblacion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Amazonas</td>\n",
       "      <td>4</td>\n",
       "      <td>75388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Antioquia</td>\n",
       "      <td>17</td>\n",
       "      <td>6266961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arauca</td>\n",
       "      <td>10</td>\n",
       "      <td>266165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Atlantico</td>\n",
       "      <td>18</td>\n",
       "      <td>60071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bolivar</td>\n",
       "      <td>5</td>\n",
       "      <td>1897567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Boyaca</td>\n",
       "      <td>9</td>\n",
       "      <td>1213015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Cordoba</td>\n",
       "      <td>4</td>\n",
       "      <td>1683792</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Caldas</td>\n",
       "      <td>6</td>\n",
       "      <td>1004428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Caqueta</td>\n",
       "      <td>8</td>\n",
       "      <td>471527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Casanare</td>\n",
       "      <td>9</td>\n",
       "      <td>350221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Cauca</td>\n",
       "      <td>7</td>\n",
       "      <td>1359634</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Cesar</td>\n",
       "      <td>5</td>\n",
       "      <td>800362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Choco</td>\n",
       "      <td>6</td>\n",
       "      <td>461595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Cundinamarca</td>\n",
       "      <td>25</td>\n",
       "      <td>10374839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Guainia</td>\n",
       "      <td>7</td>\n",
       "      <td>82939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Guaviare</td>\n",
       "      <td>6</td>\n",
       "      <td>130991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Huila</td>\n",
       "      <td>6</td>\n",
       "      <td>1140585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>La Guajira</td>\n",
       "      <td>3</td>\n",
       "      <td>930165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Magdalena</td>\n",
       "      <td>8</td>\n",
       "      <td>1286609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Meta</td>\n",
       "      <td>10</td>\n",
       "      <td>945361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Narino</td>\n",
       "      <td>5</td>\n",
       "      <td>1722999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Norte de Santander</td>\n",
       "      <td>6</td>\n",
       "      <td>1342477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Putumayo</td>\n",
       "      <td>5</td>\n",
       "      <td>348337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Quindio</td>\n",
       "      <td>7</td>\n",
       "      <td>562087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Risaralda</td>\n",
       "      <td>8</td>\n",
       "      <td>946626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>San Andres</td>\n",
       "      <td>11</td>\n",
       "      <td>75801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Santander</td>\n",
       "      <td>8</td>\n",
       "      <td>1742598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Sucre</td>\n",
       "      <td>5</td>\n",
       "      <td>843182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Tolima</td>\n",
       "      <td>9</td>\n",
       "      <td>1404268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Valle</td>\n",
       "      <td>14</td>\n",
       "      <td>4586350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Vichada</td>\n",
       "      <td>4</td>\n",
       "      <td>7026</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             seccional  fiscales  poblacion\n",
       "0             Amazonas         4      75388\n",
       "1            Antioquia        17    6266961\n",
       "2               Arauca        10     266165\n",
       "3            Atlantico        18      60071\n",
       "4              Bolivar         5    1897567\n",
       "5               Boyaca         9    1213015\n",
       "6              Cordoba         4    1683792\n",
       "7               Caldas         6    1004428\n",
       "8              Caqueta         8     471527\n",
       "9             Casanare         9     350221\n",
       "10               Cauca         7    1359634\n",
       "11               Cesar         5     800362\n",
       "12               Choco         6     461595\n",
       "13        Cundinamarca        25   10374839\n",
       "14             Guainia         7      82939\n",
       "15            Guaviare         6     130991\n",
       "16               Huila         6    1140585\n",
       "17          La Guajira         3     930165\n",
       "18           Magdalena         8    1286609\n",
       "19                Meta        10     945361\n",
       "20              Narino         5    1722999\n",
       "21  Norte de Santander         6    1342477\n",
       "22            Putumayo         5     348337\n",
       "23             Quindio         7     562087\n",
       "24           Risaralda         8     946626\n",
       "25          San Andres        11      75801\n",
       "26           Santander         8    1742598\n",
       "27               Sucre         5     843182\n",
       "28              Tolima         9    1404268\n",
       "29               Valle        14    4586350\n",
       "30             Vichada         4       7026"
      ]
     },
     "execution_count": 770,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file_fiscalia ='./data/raw/datos_fiscalia.csv'\n",
    "df_fiscalia = pd.read_csv(file_fiscalia,index_col=None, header=0)\n",
    "#help(df_fiscalia)\n",
    "df_fiscalia\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Limpieza de datos\n",
    "A continuación se debe hacer la limpieza de datos sobre las columnas. Tener en cuenta que la columna de `fiscales` contiene el número de fiscales de una sección por cada cien mil habitantes mientras que la columna de `poblacion` contiene la población de dicha sección. Acontinuación se procede a eliminar los puntos de la columna de `poblacion` "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 771,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# num_fiscales_seccion = fiscales*(poblacion/100000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 772,
   "metadata": {},
   "outputs": [],
   "source": [
    "col_loc_fiscales = df_fiscalia.columns.get_loc('fiscales')\n",
    "col_loc_poblacion = df_fiscalia.columns.get_loc('poblacion')\n",
    "col_loc_seccion = df_fiscalia.columns.get_loc('seccional')\n",
    "for i in range(len(df_fiscalia['fiscales'])):\n",
    "    cell = df_fiscalia.iloc[i,col_loc_fiscales]\n",
    "    pob = int(df_fiscalia.iloc[i,col_loc_poblacion])/100000\n",
    "    df_fiscalia.iloc[i,col_loc_fiscales] = math.floor(int(cell)*pob)\n",
    "    df_fiscalia.iloc[i,col_loc_seccion] = df_fiscalia.iloc[i,col_loc_seccion].upper()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 773,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>seccional</th>\n",
       "      <th>fiscales</th>\n",
       "      <th>poblacion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AMAZONAS</td>\n",
       "      <td>3</td>\n",
       "      <td>75388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ANTIOQUIA</td>\n",
       "      <td>1065</td>\n",
       "      <td>6266961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ARAUCA</td>\n",
       "      <td>26</td>\n",
       "      <td>266165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ATLANTICO</td>\n",
       "      <td>10</td>\n",
       "      <td>60071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BOLIVAR</td>\n",
       "      <td>94</td>\n",
       "      <td>1897567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>BOYACA</td>\n",
       "      <td>109</td>\n",
       "      <td>1213015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CORDOBA</td>\n",
       "      <td>67</td>\n",
       "      <td>1683792</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>CALDAS</td>\n",
       "      <td>60</td>\n",
       "      <td>1004428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>CAQUETA</td>\n",
       "      <td>37</td>\n",
       "      <td>471527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>CASANARE</td>\n",
       "      <td>31</td>\n",
       "      <td>350221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>CAUCA</td>\n",
       "      <td>95</td>\n",
       "      <td>1359634</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>CESAR</td>\n",
       "      <td>40</td>\n",
       "      <td>800362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>CHOCO</td>\n",
       "      <td>27</td>\n",
       "      <td>461595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>CUNDINAMARCA</td>\n",
       "      <td>2593</td>\n",
       "      <td>10374839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>GUAINIA</td>\n",
       "      <td>5</td>\n",
       "      <td>82939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>GUAVIARE</td>\n",
       "      <td>7</td>\n",
       "      <td>130991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>HUILA</td>\n",
       "      <td>68</td>\n",
       "      <td>1140585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>LA GUAJIRA</td>\n",
       "      <td>27</td>\n",
       "      <td>930165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>MAGDALENA</td>\n",
       "      <td>102</td>\n",
       "      <td>1286609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>META</td>\n",
       "      <td>94</td>\n",
       "      <td>945361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>NARINO</td>\n",
       "      <td>86</td>\n",
       "      <td>1722999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>NORTE DE SANTANDER</td>\n",
       "      <td>80</td>\n",
       "      <td>1342477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>PUTUMAYO</td>\n",
       "      <td>17</td>\n",
       "      <td>348337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>QUINDIO</td>\n",
       "      <td>39</td>\n",
       "      <td>562087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>RISARALDA</td>\n",
       "      <td>75</td>\n",
       "      <td>946626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>SAN ANDRES</td>\n",
       "      <td>8</td>\n",
       "      <td>75801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>SANTANDER</td>\n",
       "      <td>139</td>\n",
       "      <td>1742598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>SUCRE</td>\n",
       "      <td>42</td>\n",
       "      <td>843182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>TOLIMA</td>\n",
       "      <td>126</td>\n",
       "      <td>1404268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>VALLE</td>\n",
       "      <td>642</td>\n",
       "      <td>4586350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>VICHADA</td>\n",
       "      <td>0</td>\n",
       "      <td>7026</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             seccional  fiscales  poblacion\n",
       "0             AMAZONAS         3      75388\n",
       "1            ANTIOQUIA      1065    6266961\n",
       "2               ARAUCA        26     266165\n",
       "3            ATLANTICO        10      60071\n",
       "4              BOLIVAR        94    1897567\n",
       "5               BOYACA       109    1213015\n",
       "6              CORDOBA        67    1683792\n",
       "7               CALDAS        60    1004428\n",
       "8              CAQUETA        37     471527\n",
       "9             CASANARE        31     350221\n",
       "10               CAUCA        95    1359634\n",
       "11               CESAR        40     800362\n",
       "12               CHOCO        27     461595\n",
       "13        CUNDINAMARCA      2593   10374839\n",
       "14             GUAINIA         5      82939\n",
       "15            GUAVIARE         7     130991\n",
       "16               HUILA        68    1140585\n",
       "17          LA GUAJIRA        27     930165\n",
       "18           MAGDALENA       102    1286609\n",
       "19                META        94     945361\n",
       "20              NARINO        86    1722999\n",
       "21  NORTE DE SANTANDER        80    1342477\n",
       "22            PUTUMAYO        17     348337\n",
       "23             QUINDIO        39     562087\n",
       "24           RISARALDA        75     946626\n",
       "25          SAN ANDRES         8      75801\n",
       "26           SANTANDER       139    1742598\n",
       "27               SUCRE        42     843182\n",
       "28              TOLIMA       126    1404268\n",
       "29               VALLE       642    4586350\n",
       "30             VICHADA         0       7026"
      ]
     },
     "execution_count": 773,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fiscalia"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exportar trabajo temporal en un *.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 774,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_fiscalia.to_csv('data/pre_processed/pre_processed_fiscalia.csv', mode='a', header=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DANE\n",
    "Para los datos del DANE se hizo un join de los atributos del 2016 para diferentes medidas relacionadas con la pobreza"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 775,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_dane ='./data/raw/datos_dane.csv'\n",
    "df_dane = pd.read_csv(file_dane,index_col=None, header=0)\n",
    "#Reemplazar los acentos\n",
    "\n",
    "\n",
    "#for i in df_dane.columns:\n",
    "#    df_dane[i] = df_dane[i].str.replace(',','')\n",
    "df_dane\n",
    "\n",
    "col_loc_seccion = df_dane.columns.get_loc('Departamento')\n",
    "for i in range(len(df_dane['Departamento'])):\n",
    "    df_dane.iloc[i,col_loc_seccion] = df_fiscalia.iloc[i,col_loc_seccion].upper()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 776,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Departamento</th>\n",
       "      <th>pobreza_monetaria</th>\n",
       "      <th>pobreza_monetaria_extrema</th>\n",
       "      <th>promedio_ingreso_per_capita</th>\n",
       "      <th>linea_pobreza</th>\n",
       "      <th>linea_pobreza_extrema</th>\n",
       "      <th>brecha_pobreza_monetaria</th>\n",
       "      <th>severidad_pobreza_monetaria</th>\n",
       "      <th>brecha_pobreza_monetaria_extrema</th>\n",
       "      <th>severidad_pobreza_monetaria_extrema</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AMAZONAS</td>\n",
       "      <td>21.9</td>\n",
       "      <td>6.6</td>\n",
       "      <td>701607</td>\n",
       "      <td>246012</td>\n",
       "      <td>113694</td>\n",
       "      <td>8.4</td>\n",
       "      <td>4.7</td>\n",
       "      <td>2.6</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ANTIOQUIA</td>\n",
       "      <td>25.0</td>\n",
       "      <td>3.1</td>\n",
       "      <td>583239</td>\n",
       "      <td>264814</td>\n",
       "      <td>121699</td>\n",
       "      <td>6.9</td>\n",
       "      <td>2.9</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ARAUCA</td>\n",
       "      <td>11.6</td>\n",
       "      <td>2.3</td>\n",
       "      <td>1052592</td>\n",
       "      <td>260109</td>\n",
       "      <td>119134</td>\n",
       "      <td>3.9</td>\n",
       "      <td>2.1</td>\n",
       "      <td>1.1</td>\n",
       "      <td>0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ATLANTICO</td>\n",
       "      <td>41.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>444962</td>\n",
       "      <td>246135</td>\n",
       "      <td>115722</td>\n",
       "      <td>14.7</td>\n",
       "      <td>7.4</td>\n",
       "      <td>3.3</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BOLIVAR</td>\n",
       "      <td>32.0</td>\n",
       "      <td>10.6</td>\n",
       "      <td>506044</td>\n",
       "      <td>219977</td>\n",
       "      <td>110332</td>\n",
       "      <td>12.1</td>\n",
       "      <td>6.5</td>\n",
       "      <td>4.2</td>\n",
       "      <td>2.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>BOYACA</td>\n",
       "      <td>27.6</td>\n",
       "      <td>7.2</td>\n",
       "      <td>567695</td>\n",
       "      <td>236394</td>\n",
       "      <td>112019</td>\n",
       "      <td>9.7</td>\n",
       "      <td>5.3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CORDOBA</td>\n",
       "      <td>35.8</td>\n",
       "      <td>8.7</td>\n",
       "      <td>411837</td>\n",
       "      <td>222269</td>\n",
       "      <td>111309</td>\n",
       "      <td>12.1</td>\n",
       "      <td>5.9</td>\n",
       "      <td>2.5</td>\n",
       "      <td>1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>CALDAS</td>\n",
       "      <td>50.7</td>\n",
       "      <td>22.3</td>\n",
       "      <td>313872</td>\n",
       "      <td>201270</td>\n",
       "      <td>106301</td>\n",
       "      <td>20.2</td>\n",
       "      <td>10.7</td>\n",
       "      <td>7.1</td>\n",
       "      <td>3.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>CAQUETA</td>\n",
       "      <td>41.9</td>\n",
       "      <td>12.1</td>\n",
       "      <td>408188</td>\n",
       "      <td>241510</td>\n",
       "      <td>114640</td>\n",
       "      <td>15.9</td>\n",
       "      <td>8.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>CASANARE</td>\n",
       "      <td>59.8</td>\n",
       "      <td>34.7</td>\n",
       "      <td>293004</td>\n",
       "      <td>210045</td>\n",
       "      <td>107899</td>\n",
       "      <td>31.4</td>\n",
       "      <td>21.0</td>\n",
       "      <td>17.1</td>\n",
       "      <td>11.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>CAUCA</td>\n",
       "      <td>44.8</td>\n",
       "      <td>10.8</td>\n",
       "      <td>342657</td>\n",
       "      <td>216076</td>\n",
       "      <td>108666</td>\n",
       "      <td>14.8</td>\n",
       "      <td>6.6</td>\n",
       "      <td>2.7</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>CESAR</td>\n",
       "      <td>17.3</td>\n",
       "      <td>5.3</td>\n",
       "      <td>627817</td>\n",
       "      <td>231463</td>\n",
       "      <td>111651</td>\n",
       "      <td>6.1</td>\n",
       "      <td>3.3</td>\n",
       "      <td>2.1</td>\n",
       "      <td>1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>CHOCO</td>\n",
       "      <td>45.9</td>\n",
       "      <td>20.0</td>\n",
       "      <td>408498</td>\n",
       "      <td>227029</td>\n",
       "      <td>112738</td>\n",
       "      <td>19.5</td>\n",
       "      <td>11.1</td>\n",
       "      <td>7.8</td>\n",
       "      <td>4.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>CUNDINAMARCA</td>\n",
       "      <td>52.5</td>\n",
       "      <td>25.3</td>\n",
       "      <td>351400</td>\n",
       "      <td>217679</td>\n",
       "      <td>110039</td>\n",
       "      <td>23.7</td>\n",
       "      <td>14.0</td>\n",
       "      <td>10.2</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>GUAINIA</td>\n",
       "      <td>50.0</td>\n",
       "      <td>18.2</td>\n",
       "      <td>355476</td>\n",
       "      <td>236632</td>\n",
       "      <td>113497</td>\n",
       "      <td>21.0</td>\n",
       "      <td>11.6</td>\n",
       "      <td>6.3</td>\n",
       "      <td>3.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>GUAVIARE</td>\n",
       "      <td>24.5</td>\n",
       "      <td>7.5</td>\n",
       "      <td>577676</td>\n",
       "      <td>240048</td>\n",
       "      <td>113942</td>\n",
       "      <td>9.3</td>\n",
       "      <td>5.4</td>\n",
       "      <td>3.5</td>\n",
       "      <td>2.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>HUILA</td>\n",
       "      <td>45.7</td>\n",
       "      <td>16.2</td>\n",
       "      <td>374468</td>\n",
       "      <td>211656</td>\n",
       "      <td>108843</td>\n",
       "      <td>16.9</td>\n",
       "      <td>8.5</td>\n",
       "      <td>4.8</td>\n",
       "      <td>2.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>LA GUAJIRA</td>\n",
       "      <td>40.4</td>\n",
       "      <td>12.4</td>\n",
       "      <td>417301</td>\n",
       "      <td>244930</td>\n",
       "      <td>113552</td>\n",
       "      <td>15.5</td>\n",
       "      <td>8.4</td>\n",
       "      <td>4.7</td>\n",
       "      <td>2.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>MAGDALENA</td>\n",
       "      <td>30.3</td>\n",
       "      <td>7.4</td>\n",
       "      <td>541712</td>\n",
       "      <td>249514</td>\n",
       "      <td>115105</td>\n",
       "      <td>10.9</td>\n",
       "      <td>5.7</td>\n",
       "      <td>2.8</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>META</td>\n",
       "      <td>19.6</td>\n",
       "      <td>3.4</td>\n",
       "      <td>592378</td>\n",
       "      <td>242251</td>\n",
       "      <td>112560</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2.7</td>\n",
       "      <td>1.2</td>\n",
       "      <td>0.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>NARINO</td>\n",
       "      <td>18.0</td>\n",
       "      <td>4.7</td>\n",
       "      <td>650360</td>\n",
       "      <td>248387</td>\n",
       "      <td>117377</td>\n",
       "      <td>5.6</td>\n",
       "      <td>2.7</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>NORTE DE SANTANDER</td>\n",
       "      <td>46.7</td>\n",
       "      <td>12.0</td>\n",
       "      <td>367584</td>\n",
       "      <td>232555</td>\n",
       "      <td>113972</td>\n",
       "      <td>16.1</td>\n",
       "      <td>7.6</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>PUTUMAYO</td>\n",
       "      <td>31.4</td>\n",
       "      <td>9.5</td>\n",
       "      <td>504385</td>\n",
       "      <td>233983</td>\n",
       "      <td>113262</td>\n",
       "      <td>10.8</td>\n",
       "      <td>5.4</td>\n",
       "      <td>2.9</td>\n",
       "      <td>1.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>QUINDIO</td>\n",
       "      <td>22.6</td>\n",
       "      <td>6.2</td>\n",
       "      <td>677104</td>\n",
       "      <td>250531</td>\n",
       "      <td>117876</td>\n",
       "      <td>8.2</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2.6</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>RISARALDA</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.5</td>\n",
       "      <td>624796</td>\n",
       "      <td>241673</td>\n",
       "      <td>114692</td>\n",
       "      <td>10.3</td>\n",
       "      <td>5.5</td>\n",
       "      <td>3.1</td>\n",
       "      <td>1.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Departamento  pobreza_monetaria  pobreza_monetaria_extrema  \\\n",
       "0             AMAZONAS               21.9                        6.6   \n",
       "1            ANTIOQUIA               25.0                        3.1   \n",
       "2               ARAUCA               11.6                        2.3   \n",
       "3            ATLANTICO               41.0                       11.0   \n",
       "4              BOLIVAR               32.0                       10.6   \n",
       "5               BOYACA               27.6                        7.2   \n",
       "6              CORDOBA               35.8                        8.7   \n",
       "7               CALDAS               50.7                       22.3   \n",
       "8              CAQUETA               41.9                       12.1   \n",
       "9             CASANARE               59.8                       34.7   \n",
       "10               CAUCA               44.8                       10.8   \n",
       "11               CESAR               17.3                        5.3   \n",
       "12               CHOCO               45.9                       20.0   \n",
       "13        CUNDINAMARCA               52.5                       25.3   \n",
       "14             GUAINIA               50.0                       18.2   \n",
       "15            GUAVIARE               24.5                        7.5   \n",
       "16               HUILA               45.7                       16.2   \n",
       "17          LA GUAJIRA               40.4                       12.4   \n",
       "18           MAGDALENA               30.3                        7.4   \n",
       "19                META               19.6                        3.4   \n",
       "20              NARINO               18.0                        4.7   \n",
       "21  NORTE DE SANTANDER               46.7                       12.0   \n",
       "22            PUTUMAYO               31.4                        9.5   \n",
       "23             QUINDIO               22.6                        6.2   \n",
       "24           RISARALDA               28.0                        8.5   \n",
       "\n",
       "    promedio_ingreso_per_capita  linea_pobreza  linea_pobreza_extrema  \\\n",
       "0                        701607         246012                 113694   \n",
       "1                        583239         264814                 121699   \n",
       "2                       1052592         260109                 119134   \n",
       "3                        444962         246135                 115722   \n",
       "4                        506044         219977                 110332   \n",
       "5                        567695         236394                 112019   \n",
       "6                        411837         222269                 111309   \n",
       "7                        313872         201270                 106301   \n",
       "8                        408188         241510                 114640   \n",
       "9                        293004         210045                 107899   \n",
       "10                       342657         216076                 108666   \n",
       "11                       627817         231463                 111651   \n",
       "12                       408498         227029                 112738   \n",
       "13                       351400         217679                 110039   \n",
       "14                       355476         236632                 113497   \n",
       "15                       577676         240048                 113942   \n",
       "16                       374468         211656                 108843   \n",
       "17                       417301         244930                 113552   \n",
       "18                       541712         249514                 115105   \n",
       "19                       592378         242251                 112560   \n",
       "20                       650360         248387                 117377   \n",
       "21                       367584         232555                 113972   \n",
       "22                       504385         233983                 113262   \n",
       "23                       677104         250531                 117876   \n",
       "24                       624796         241673                 114692   \n",
       "\n",
       "    brecha_pobreza_monetaria  severidad_pobreza_monetaria  \\\n",
       "0                        8.4                          4.7   \n",
       "1                        6.9                          2.9   \n",
       "2                        3.9                          2.1   \n",
       "3                       14.7                          7.4   \n",
       "4                       12.1                          6.5   \n",
       "5                        9.7                          5.3   \n",
       "6                       12.1                          5.9   \n",
       "7                       20.2                         10.7   \n",
       "8                       15.9                          8.5   \n",
       "9                       31.4                         21.0   \n",
       "10                      14.8                          6.6   \n",
       "11                       6.1                          3.3   \n",
       "12                      19.5                         11.1   \n",
       "13                      23.7                         14.0   \n",
       "14                      21.0                         11.6   \n",
       "15                       9.3                          5.4   \n",
       "16                      16.9                          8.5   \n",
       "17                      15.5                          8.4   \n",
       "18                      10.9                          5.7   \n",
       "19                       5.8                          2.7   \n",
       "20                       5.6                          2.7   \n",
       "21                      16.1                          7.6   \n",
       "22                      10.8                          5.4   \n",
       "23                       8.2                          4.5   \n",
       "24                      10.3                          5.5   \n",
       "\n",
       "    brecha_pobreza_monetaria_extrema  severidad_pobreza_monetaria_extrema  \n",
       "0                                2.6                                  1.6  \n",
       "1                                0.8                                  0.3  \n",
       "2                                1.1                                  0.8  \n",
       "3                                3.3                                  1.6  \n",
       "4                                4.2                                  2.4  \n",
       "5                                3.0                                  1.9  \n",
       "6                                2.5                                  1.2  \n",
       "7                                7.1                                  3.3  \n",
       "8                                4.5                                  2.6  \n",
       "9                               17.1                                 11.3  \n",
       "10                               2.7                                  1.0  \n",
       "11                               2.1                                  1.2  \n",
       "12                               7.8                                  4.4  \n",
       "13                              10.2                                  5.5  \n",
       "14                               6.3                                  3.3  \n",
       "15                               3.5                                  2.3  \n",
       "16                               4.8                                  2.1  \n",
       "17                               4.7                                  2.7  \n",
       "18                               2.8                                  1.6  \n",
       "19                               1.2                                  0.7  \n",
       "20                               1.5                                  0.8  \n",
       "21                               3.2                                  1.3  \n",
       "22                               2.9                                  1.3  \n",
       "23                               2.6                                  1.5  \n",
       "24                               3.1                                  1.7  "
      ]
     },
     "execution_count": 776,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dane"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 777,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_dane.to_csv('data/pre_processed/pre_processed_dane.csv', mode='a', header=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exportando el archivo final de CSV con el append de las columnas de los datos externos\n",
    "Write a csv file with the merge of the files in the main directory"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Exportando hurtos+datos de fiscalia"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 778,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FECHA</th>\n",
       "      <th>DEPARTAMENTO</th>\n",
       "      <th>MUNICIPIO</th>\n",
       "      <th>DIA</th>\n",
       "      <th>HORA</th>\n",
       "      <th>BARRIO</th>\n",
       "      <th>ZONA</th>\n",
       "      <th>SITIO</th>\n",
       "      <th>ARMA</th>\n",
       "      <th>MOVIL_AGRESOR</th>\n",
       "      <th>MOVIL_VICTIMA</th>\n",
       "      <th>EDAD</th>\n",
       "      <th>SEXO</th>\n",
       "      <th>CLASE</th>\n",
       "      <th>MARCA</th>\n",
       "      <th>MODELO</th>\n",
       "      <th>COLOR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ENERO</td>\n",
       "      <td>ANTIOQUIA</td>\n",
       "      <td>ENVIGADO</td>\n",
       "      <td>Viernes</td>\n",
       "      <td>MADRUGADA</td>\n",
       "      <td>MESA</td>\n",
       "      <td>URBANA</td>\n",
       "      <td>VIAS PUBLICAS</td>\n",
       "      <td>LLAVE MAESTRA</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>ADULTO_CONTEMPORANEO</td>\n",
       "      <td>FEMENINO</td>\n",
       "      <td>TRANSPORTE_LIVIANO</td>\n",
       "      <td>CHEVROLET</td>\n",
       "      <td>2013</td>\n",
       "      <td>NEGRO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ENERO</td>\n",
       "      <td>ATLANTICO</td>\n",
       "      <td>BARRANQUILLA (CT)</td>\n",
       "      <td>Viernes</td>\n",
       "      <td>TARDE</td>\n",
       "      <td>ALTOS DEL LIMON</td>\n",
       "      <td>URBANA</td>\n",
       "      <td>CONJUNTO RESIDENCIAL</td>\n",
       "      <td>LLAVE MAESTRA</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>ADULTO_JOVEN</td>\n",
       "      <td>MASCULINO</td>\n",
       "      <td>TRANSPORTE_LIVIANO</td>\n",
       "      <td>NISSAN</td>\n",
       "      <td>2013</td>\n",
       "      <td>GRIS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   FECHA DEPARTAMENTO          MUNICIPIO      DIA       HORA           BARRIO  \\\n",
       "0  ENERO    ANTIOQUIA           ENVIGADO  Viernes  MADRUGADA             MESA   \n",
       "1  ENERO    ATLANTICO  BARRANQUILLA (CT)  Viernes      TARDE  ALTOS DEL LIMON   \n",
       "\n",
       "     ZONA                 SITIO           ARMA MOVIL_AGRESOR MOVIL_VICTIMA  \\\n",
       "0  URBANA         VIAS PUBLICAS  LLAVE MAESTRA         A PIE         A PIE   \n",
       "1  URBANA  CONJUNTO RESIDENCIAL  LLAVE MAESTRA         A PIE         A PIE   \n",
       "\n",
       "                   EDAD       SEXO               CLASE      MARCA MODELO  \\\n",
       "0  ADULTO_CONTEMPORANEO   FEMENINO  TRANSPORTE_LIVIANO  CHEVROLET   2013   \n",
       "1          ADULTO_JOVEN  MASCULINO  TRANSPORTE_LIVIANO     NISSAN   2013   \n",
       "\n",
       "   COLOR  \n",
       "0  NEGRO  \n",
       "1   GRIS  "
      ]
     },
     "execution_count": 778,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.head(0)\n",
    "frame.head(1)\n",
    "frame.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 779,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#tres columnas del archivo de data_fiscalia\n",
    "col_loc_d = df_fiscalia.columns.get_loc('seccional')\n",
    "col_loc_fiscales = df_fiscalia.columns.get_loc('fiscales')\n",
    "col_loc_poblacion = df_fiscalia.columns.get_loc('poblacion')\n",
    "#columna dpto de hurtos\n",
    "col_loc_departamento = frame.columns.get_loc('DEPARTAMENTO')\n",
    "#dos arreglos para tener los atributos de cada registro de cada columna        \n",
    "arr_fiscales = []\n",
    "arr_poblacion = []\n",
    "for j in range(len(frame['DEPARTAMENTO'])):\n",
    "    cell_hurtos = frame.iloc[j,col_loc_departamento]\n",
    "    found = False\n",
    "    for i in range(len(df_fiscalia['seccional'])):\n",
    "        if found:\n",
    "            break\n",
    "        cell = df_fiscalia.iloc[i,col_loc_d]\n",
    "        if cell_hurtos in cell:\n",
    "            found = True\n",
    "            arr_fiscales.append(df_fiscalia.iloc[i,col_loc_fiscales])\n",
    "            arr_poblacion.append(df_fiscalia.iloc[i,col_loc_poblacion])\n",
    "    if not found:\n",
    "        arr_fiscales.append(0)\n",
    "        arr_poblacion.append(0)\n",
    "        \n",
    "\n",
    "\n",
    "df_fiscales_column = pd.DataFrame({'FISCALES': arr_fiscales})\n",
    "df_poblacion_column = pd.DataFrame({'POBLACION': arr_poblacion})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 780,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_new = df_fiscales_column.join(df_poblacion_column)\n",
    "frame = frame.join(df_new)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 781,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "frame.to_csv('data/pre_processed/pre_processed_hurtos_fiscalia.csv', mode='a', header=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Exportando hurtos+datos de fiscalia + datos DANE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 782,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Departamento</th>\n",
       "      <th>pobreza_monetaria</th>\n",
       "      <th>pobreza_monetaria_extrema</th>\n",
       "      <th>promedio_ingreso_per_capita</th>\n",
       "      <th>linea_pobreza</th>\n",
       "      <th>linea_pobreza_extrema</th>\n",
       "      <th>brecha_pobreza_monetaria</th>\n",
       "      <th>severidad_pobreza_monetaria</th>\n",
       "      <th>brecha_pobreza_monetaria_extrema</th>\n",
       "      <th>severidad_pobreza_monetaria_extrema</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AMAZONAS</td>\n",
       "      <td>21.9</td>\n",
       "      <td>6.6</td>\n",
       "      <td>701607</td>\n",
       "      <td>246012</td>\n",
       "      <td>113694</td>\n",
       "      <td>8.4</td>\n",
       "      <td>4.7</td>\n",
       "      <td>2.6</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ANTIOQUIA</td>\n",
       "      <td>25.0</td>\n",
       "      <td>3.1</td>\n",
       "      <td>583239</td>\n",
       "      <td>264814</td>\n",
       "      <td>121699</td>\n",
       "      <td>6.9</td>\n",
       "      <td>2.9</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ARAUCA</td>\n",
       "      <td>11.6</td>\n",
       "      <td>2.3</td>\n",
       "      <td>1052592</td>\n",
       "      <td>260109</td>\n",
       "      <td>119134</td>\n",
       "      <td>3.9</td>\n",
       "      <td>2.1</td>\n",
       "      <td>1.1</td>\n",
       "      <td>0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ATLANTICO</td>\n",
       "      <td>41.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>444962</td>\n",
       "      <td>246135</td>\n",
       "      <td>115722</td>\n",
       "      <td>14.7</td>\n",
       "      <td>7.4</td>\n",
       "      <td>3.3</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BOLIVAR</td>\n",
       "      <td>32.0</td>\n",
       "      <td>10.6</td>\n",
       "      <td>506044</td>\n",
       "      <td>219977</td>\n",
       "      <td>110332</td>\n",
       "      <td>12.1</td>\n",
       "      <td>6.5</td>\n",
       "      <td>4.2</td>\n",
       "      <td>2.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>BOYACA</td>\n",
       "      <td>27.6</td>\n",
       "      <td>7.2</td>\n",
       "      <td>567695</td>\n",
       "      <td>236394</td>\n",
       "      <td>112019</td>\n",
       "      <td>9.7</td>\n",
       "      <td>5.3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CORDOBA</td>\n",
       "      <td>35.8</td>\n",
       "      <td>8.7</td>\n",
       "      <td>411837</td>\n",
       "      <td>222269</td>\n",
       "      <td>111309</td>\n",
       "      <td>12.1</td>\n",
       "      <td>5.9</td>\n",
       "      <td>2.5</td>\n",
       "      <td>1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>CALDAS</td>\n",
       "      <td>50.7</td>\n",
       "      <td>22.3</td>\n",
       "      <td>313872</td>\n",
       "      <td>201270</td>\n",
       "      <td>106301</td>\n",
       "      <td>20.2</td>\n",
       "      <td>10.7</td>\n",
       "      <td>7.1</td>\n",
       "      <td>3.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>CAQUETA</td>\n",
       "      <td>41.9</td>\n",
       "      <td>12.1</td>\n",
       "      <td>408188</td>\n",
       "      <td>241510</td>\n",
       "      <td>114640</td>\n",
       "      <td>15.9</td>\n",
       "      <td>8.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>CASANARE</td>\n",
       "      <td>59.8</td>\n",
       "      <td>34.7</td>\n",
       "      <td>293004</td>\n",
       "      <td>210045</td>\n",
       "      <td>107899</td>\n",
       "      <td>31.4</td>\n",
       "      <td>21.0</td>\n",
       "      <td>17.1</td>\n",
       "      <td>11.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>CAUCA</td>\n",
       "      <td>44.8</td>\n",
       "      <td>10.8</td>\n",
       "      <td>342657</td>\n",
       "      <td>216076</td>\n",
       "      <td>108666</td>\n",
       "      <td>14.8</td>\n",
       "      <td>6.6</td>\n",
       "      <td>2.7</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>CESAR</td>\n",
       "      <td>17.3</td>\n",
       "      <td>5.3</td>\n",
       "      <td>627817</td>\n",
       "      <td>231463</td>\n",
       "      <td>111651</td>\n",
       "      <td>6.1</td>\n",
       "      <td>3.3</td>\n",
       "      <td>2.1</td>\n",
       "      <td>1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>CHOCO</td>\n",
       "      <td>45.9</td>\n",
       "      <td>20.0</td>\n",
       "      <td>408498</td>\n",
       "      <td>227029</td>\n",
       "      <td>112738</td>\n",
       "      <td>19.5</td>\n",
       "      <td>11.1</td>\n",
       "      <td>7.8</td>\n",
       "      <td>4.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>CUNDINAMARCA</td>\n",
       "      <td>52.5</td>\n",
       "      <td>25.3</td>\n",
       "      <td>351400</td>\n",
       "      <td>217679</td>\n",
       "      <td>110039</td>\n",
       "      <td>23.7</td>\n",
       "      <td>14.0</td>\n",
       "      <td>10.2</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>GUAINIA</td>\n",
       "      <td>50.0</td>\n",
       "      <td>18.2</td>\n",
       "      <td>355476</td>\n",
       "      <td>236632</td>\n",
       "      <td>113497</td>\n",
       "      <td>21.0</td>\n",
       "      <td>11.6</td>\n",
       "      <td>6.3</td>\n",
       "      <td>3.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>GUAVIARE</td>\n",
       "      <td>24.5</td>\n",
       "      <td>7.5</td>\n",
       "      <td>577676</td>\n",
       "      <td>240048</td>\n",
       "      <td>113942</td>\n",
       "      <td>9.3</td>\n",
       "      <td>5.4</td>\n",
       "      <td>3.5</td>\n",
       "      <td>2.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>HUILA</td>\n",
       "      <td>45.7</td>\n",
       "      <td>16.2</td>\n",
       "      <td>374468</td>\n",
       "      <td>211656</td>\n",
       "      <td>108843</td>\n",
       "      <td>16.9</td>\n",
       "      <td>8.5</td>\n",
       "      <td>4.8</td>\n",
       "      <td>2.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>LA GUAJIRA</td>\n",
       "      <td>40.4</td>\n",
       "      <td>12.4</td>\n",
       "      <td>417301</td>\n",
       "      <td>244930</td>\n",
       "      <td>113552</td>\n",
       "      <td>15.5</td>\n",
       "      <td>8.4</td>\n",
       "      <td>4.7</td>\n",
       "      <td>2.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>MAGDALENA</td>\n",
       "      <td>30.3</td>\n",
       "      <td>7.4</td>\n",
       "      <td>541712</td>\n",
       "      <td>249514</td>\n",
       "      <td>115105</td>\n",
       "      <td>10.9</td>\n",
       "      <td>5.7</td>\n",
       "      <td>2.8</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>META</td>\n",
       "      <td>19.6</td>\n",
       "      <td>3.4</td>\n",
       "      <td>592378</td>\n",
       "      <td>242251</td>\n",
       "      <td>112560</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2.7</td>\n",
       "      <td>1.2</td>\n",
       "      <td>0.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>NARINO</td>\n",
       "      <td>18.0</td>\n",
       "      <td>4.7</td>\n",
       "      <td>650360</td>\n",
       "      <td>248387</td>\n",
       "      <td>117377</td>\n",
       "      <td>5.6</td>\n",
       "      <td>2.7</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>NORTE DE SANTANDER</td>\n",
       "      <td>46.7</td>\n",
       "      <td>12.0</td>\n",
       "      <td>367584</td>\n",
       "      <td>232555</td>\n",
       "      <td>113972</td>\n",
       "      <td>16.1</td>\n",
       "      <td>7.6</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>PUTUMAYO</td>\n",
       "      <td>31.4</td>\n",
       "      <td>9.5</td>\n",
       "      <td>504385</td>\n",
       "      <td>233983</td>\n",
       "      <td>113262</td>\n",
       "      <td>10.8</td>\n",
       "      <td>5.4</td>\n",
       "      <td>2.9</td>\n",
       "      <td>1.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>QUINDIO</td>\n",
       "      <td>22.6</td>\n",
       "      <td>6.2</td>\n",
       "      <td>677104</td>\n",
       "      <td>250531</td>\n",
       "      <td>117876</td>\n",
       "      <td>8.2</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2.6</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>RISARALDA</td>\n",
       "      <td>28.0</td>\n",
       "      <td>8.5</td>\n",
       "      <td>624796</td>\n",
       "      <td>241673</td>\n",
       "      <td>114692</td>\n",
       "      <td>10.3</td>\n",
       "      <td>5.5</td>\n",
       "      <td>3.1</td>\n",
       "      <td>1.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Departamento  pobreza_monetaria  pobreza_monetaria_extrema  \\\n",
       "0             AMAZONAS               21.9                        6.6   \n",
       "1            ANTIOQUIA               25.0                        3.1   \n",
       "2               ARAUCA               11.6                        2.3   \n",
       "3            ATLANTICO               41.0                       11.0   \n",
       "4              BOLIVAR               32.0                       10.6   \n",
       "5               BOYACA               27.6                        7.2   \n",
       "6              CORDOBA               35.8                        8.7   \n",
       "7               CALDAS               50.7                       22.3   \n",
       "8              CAQUETA               41.9                       12.1   \n",
       "9             CASANARE               59.8                       34.7   \n",
       "10               CAUCA               44.8                       10.8   \n",
       "11               CESAR               17.3                        5.3   \n",
       "12               CHOCO               45.9                       20.0   \n",
       "13        CUNDINAMARCA               52.5                       25.3   \n",
       "14             GUAINIA               50.0                       18.2   \n",
       "15            GUAVIARE               24.5                        7.5   \n",
       "16               HUILA               45.7                       16.2   \n",
       "17          LA GUAJIRA               40.4                       12.4   \n",
       "18           MAGDALENA               30.3                        7.4   \n",
       "19                META               19.6                        3.4   \n",
       "20              NARINO               18.0                        4.7   \n",
       "21  NORTE DE SANTANDER               46.7                       12.0   \n",
       "22            PUTUMAYO               31.4                        9.5   \n",
       "23             QUINDIO               22.6                        6.2   \n",
       "24           RISARALDA               28.0                        8.5   \n",
       "\n",
       "    promedio_ingreso_per_capita  linea_pobreza  linea_pobreza_extrema  \\\n",
       "0                        701607         246012                 113694   \n",
       "1                        583239         264814                 121699   \n",
       "2                       1052592         260109                 119134   \n",
       "3                        444962         246135                 115722   \n",
       "4                        506044         219977                 110332   \n",
       "5                        567695         236394                 112019   \n",
       "6                        411837         222269                 111309   \n",
       "7                        313872         201270                 106301   \n",
       "8                        408188         241510                 114640   \n",
       "9                        293004         210045                 107899   \n",
       "10                       342657         216076                 108666   \n",
       "11                       627817         231463                 111651   \n",
       "12                       408498         227029                 112738   \n",
       "13                       351400         217679                 110039   \n",
       "14                       355476         236632                 113497   \n",
       "15                       577676         240048                 113942   \n",
       "16                       374468         211656                 108843   \n",
       "17                       417301         244930                 113552   \n",
       "18                       541712         249514                 115105   \n",
       "19                       592378         242251                 112560   \n",
       "20                       650360         248387                 117377   \n",
       "21                       367584         232555                 113972   \n",
       "22                       504385         233983                 113262   \n",
       "23                       677104         250531                 117876   \n",
       "24                       624796         241673                 114692   \n",
       "\n",
       "    brecha_pobreza_monetaria  severidad_pobreza_monetaria  \\\n",
       "0                        8.4                          4.7   \n",
       "1                        6.9                          2.9   \n",
       "2                        3.9                          2.1   \n",
       "3                       14.7                          7.4   \n",
       "4                       12.1                          6.5   \n",
       "5                        9.7                          5.3   \n",
       "6                       12.1                          5.9   \n",
       "7                       20.2                         10.7   \n",
       "8                       15.9                          8.5   \n",
       "9                       31.4                         21.0   \n",
       "10                      14.8                          6.6   \n",
       "11                       6.1                          3.3   \n",
       "12                      19.5                         11.1   \n",
       "13                      23.7                         14.0   \n",
       "14                      21.0                         11.6   \n",
       "15                       9.3                          5.4   \n",
       "16                      16.9                          8.5   \n",
       "17                      15.5                          8.4   \n",
       "18                      10.9                          5.7   \n",
       "19                       5.8                          2.7   \n",
       "20                       5.6                          2.7   \n",
       "21                      16.1                          7.6   \n",
       "22                      10.8                          5.4   \n",
       "23                       8.2                          4.5   \n",
       "24                      10.3                          5.5   \n",
       "\n",
       "    brecha_pobreza_monetaria_extrema  severidad_pobreza_monetaria_extrema  \n",
       "0                                2.6                                  1.6  \n",
       "1                                0.8                                  0.3  \n",
       "2                                1.1                                  0.8  \n",
       "3                                3.3                                  1.6  \n",
       "4                                4.2                                  2.4  \n",
       "5                                3.0                                  1.9  \n",
       "6                                2.5                                  1.2  \n",
       "7                                7.1                                  3.3  \n",
       "8                                4.5                                  2.6  \n",
       "9                               17.1                                 11.3  \n",
       "10                               2.7                                  1.0  \n",
       "11                               2.1                                  1.2  \n",
       "12                               7.8                                  4.4  \n",
       "13                              10.2                                  5.5  \n",
       "14                               6.3                                  3.3  \n",
       "15                               3.5                                  2.3  \n",
       "16                               4.8                                  2.1  \n",
       "17                               4.7                                  2.7  \n",
       "18                               2.8                                  1.6  \n",
       "19                               1.2                                  0.7  \n",
       "20                               1.5                                  0.8  \n",
       "21                               3.2                                  1.3  \n",
       "22                               2.9                                  1.3  \n",
       "23                               2.6                                  1.5  \n",
       "24                               3.1                                  1.7  "
      ]
     },
     "execution_count": 782,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dane"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 783,
   "metadata": {},
   "outputs": [],
   "source": [
    "#columnas del archivo data_dane\n",
    "cols = range(0,len(df_dane.columns))\n",
    "name_cols = [df_dane.columns[x].upper() for x in cols]\n",
    "#columna depto de hurtos\n",
    "col_loc_departamento = frame.columns.get_loc('DEPARTAMENTO')\n",
    "#arreglo de arreglos para los datos de cada uno de las columnas\n",
    "arr_attributes = []\n",
    "for i in cols:\n",
    "    arr_attributes.append([])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 784,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(frame['DEPARTAMENTO'])):\n",
    "    cell_hurtos = frame.iloc[i, col_loc_departamento]\n",
    "    found = False\n",
    "    for j in range(len(df_dane['Departamento'])):\n",
    "        if found:\n",
    "            break\n",
    "        cell = df_dane.iloc[j,cols[0]]\n",
    "        if cell_hurtos in cell:\n",
    "            found = True\n",
    "            for k in cols:\n",
    "                arr_attributes[k].append(df_dane.iloc[j,k])\n",
    "    if not found:\n",
    "        for k in cols:\n",
    "            arr_attributes[k].append(0)\n",
    "    #delete for testing\n",
    "    #if i>10:\n",
    "    #    break\n",
    "\n",
    "\n",
    "arr_df_columns = []\n",
    "for i in cols:\n",
    "    arr_df_columns.append([])\n",
    "for i in cols:\n",
    "    arr_df_columns[i] = pd.DataFrame({name_cols[i]:arr_attributes[i]})\n",
    "            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 785,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_total_col = pd.DataFrame()\n",
    "for i in cols:\n",
    "    df_total_col = arr_df_columns[i+1].join(df_total_col)\n",
    "    if i == 8:\n",
    "        break\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 786,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SEVERIDAD_POBREZA_MONETARIA_EXTREMA</th>\n",
       "      <th>BRECHA_POBREZA_MONETARIA_EXTREMA</th>\n",
       "      <th>SEVERIDAD_POBREZA_MONETARIA</th>\n",
       "      <th>BRECHA_POBREZA_MONETARIA</th>\n",
       "      <th>LINEA_POBREZA_EXTREMA</th>\n",
       "      <th>LINEA_POBREZA</th>\n",
       "      <th>PROMEDIO_INGRESO_PER_CAPITA</th>\n",
       "      <th>POBREZA_MONETARIA_EXTREMA</th>\n",
       "      <th>POBREZA_MONETARIA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.3</td>\n",
       "      <td>0.8</td>\n",
       "      <td>2.9</td>\n",
       "      <td>6.9</td>\n",
       "      <td>121699</td>\n",
       "      <td>264814</td>\n",
       "      <td>583239</td>\n",
       "      <td>3.1</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.3</td>\n",
       "      <td>0.8</td>\n",
       "      <td>2.9</td>\n",
       "      <td>6.9</td>\n",
       "      <td>121699</td>\n",
       "      <td>264814</td>\n",
       "      <td>583239</td>\n",
       "      <td>3.1</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SEVERIDAD_POBREZA_MONETARIA_EXTREMA  BRECHA_POBREZA_MONETARIA_EXTREMA  \\\n",
       "0                                  0.3                               0.8   \n",
       "1                                  0.3                               0.8   \n",
       "\n",
       "   SEVERIDAD_POBREZA_MONETARIA  BRECHA_POBREZA_MONETARIA  \\\n",
       "0                          2.9                       6.9   \n",
       "1                          2.9                       6.9   \n",
       "\n",
       "   LINEA_POBREZA_EXTREMA  LINEA_POBREZA  PROMEDIO_INGRESO_PER_CAPITA  \\\n",
       "0                 121699         264814                       583239   \n",
       "1                 121699         264814                       583239   \n",
       "\n",
       "   POBREZA_MONETARIA_EXTREMA  POBREZA_MONETARIA  \n",
       "0                        3.1               25.0  \n",
       "1                        3.1               25.0  "
      ]
     },
     "execution_count": 786,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_total_col.head(0)\n",
    "df_total_col.head(1)\n",
    "df_total_col.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 787,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "frame = frame.join(df_total_col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 788,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FECHA</th>\n",
       "      <th>DEPARTAMENTO</th>\n",
       "      <th>MUNICIPIO</th>\n",
       "      <th>DIA</th>\n",
       "      <th>HORA</th>\n",
       "      <th>BARRIO</th>\n",
       "      <th>ZONA</th>\n",
       "      <th>SITIO</th>\n",
       "      <th>ARMA</th>\n",
       "      <th>MOVIL_AGRESOR</th>\n",
       "      <th>...</th>\n",
       "      <th>POBLACION</th>\n",
       "      <th>SEVERIDAD_POBREZA_MONETARIA_EXTREMA</th>\n",
       "      <th>BRECHA_POBREZA_MONETARIA_EXTREMA</th>\n",
       "      <th>SEVERIDAD_POBREZA_MONETARIA</th>\n",
       "      <th>BRECHA_POBREZA_MONETARIA</th>\n",
       "      <th>LINEA_POBREZA_EXTREMA</th>\n",
       "      <th>LINEA_POBREZA</th>\n",
       "      <th>PROMEDIO_INGRESO_PER_CAPITA</th>\n",
       "      <th>POBREZA_MONETARIA_EXTREMA</th>\n",
       "      <th>POBREZA_MONETARIA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ENERO</td>\n",
       "      <td>ANTIOQUIA</td>\n",
       "      <td>ENVIGADO</td>\n",
       "      <td>Viernes</td>\n",
       "      <td>MADRUGADA</td>\n",
       "      <td>MESA</td>\n",
       "      <td>URBANA</td>\n",
       "      <td>VIAS PUBLICAS</td>\n",
       "      <td>LLAVE MAESTRA</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>...</td>\n",
       "      <td>6266961</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.8</td>\n",
       "      <td>2.9</td>\n",
       "      <td>6.9</td>\n",
       "      <td>121699</td>\n",
       "      <td>264814</td>\n",
       "      <td>583239</td>\n",
       "      <td>3.1</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ENERO</td>\n",
       "      <td>ANTIOQUIA</td>\n",
       "      <td>APARTADO</td>\n",
       "      <td>Viernes</td>\n",
       "      <td>MADRUGADA</td>\n",
       "      <td>OBRERO</td>\n",
       "      <td>URBANA</td>\n",
       "      <td>VIAS PUBLICAS</td>\n",
       "      <td>LLAVE MAESTRA</td>\n",
       "      <td>A PIE</td>\n",
       "      <td>...</td>\n",
       "      <td>6266961</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.8</td>\n",
       "      <td>2.9</td>\n",
       "      <td>6.9</td>\n",
       "      <td>121699</td>\n",
       "      <td>264814</td>\n",
       "      <td>583239</td>\n",
       "      <td>3.1</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   FECHA DEPARTAMENTO MUNICIPIO      DIA       HORA  BARRIO    ZONA  \\\n",
       "0  ENERO    ANTIOQUIA  ENVIGADO  Viernes  MADRUGADA    MESA  URBANA   \n",
       "0  ENERO    ANTIOQUIA  APARTADO  Viernes  MADRUGADA  OBRERO  URBANA   \n",
       "\n",
       "           SITIO           ARMA MOVIL_AGRESOR        ...         POBLACION  \\\n",
       "0  VIAS PUBLICAS  LLAVE MAESTRA         A PIE        ...           6266961   \n",
       "0  VIAS PUBLICAS  LLAVE MAESTRA         A PIE        ...           6266961   \n",
       "\n",
       "  SEVERIDAD_POBREZA_MONETARIA_EXTREMA BRECHA_POBREZA_MONETARIA_EXTREMA  \\\n",
       "0                                 0.3                              0.8   \n",
       "0                                 0.3                              0.8   \n",
       "\n",
       "  SEVERIDAD_POBREZA_MONETARIA BRECHA_POBREZA_MONETARIA LINEA_POBREZA_EXTREMA  \\\n",
       "0                         2.9                      6.9                121699   \n",
       "0                         2.9                      6.9                121699   \n",
       "\n",
       "  LINEA_POBREZA  PROMEDIO_INGRESO_PER_CAPITA  POBREZA_MONETARIA_EXTREMA  \\\n",
       "0        264814                       583239                        3.1   \n",
       "0        264814                       583239                        3.1   \n",
       "\n",
       "   POBREZA_MONETARIA  \n",
       "0               25.0  \n",
       "0               25.0  \n",
       "\n",
       "[2 rows x 28 columns]"
      ]
     },
     "execution_count": 788,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.head(0)\n",
    "frame.head(1)\n",
    "frame.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 789,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "frame.to_csv('data/pre_processed/pre_processed_compilated.csv', mode='a', header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 790,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['FECHA', 'DEPARTAMENTO', 'MUNICIPIO', 'DIA', 'HORA', 'BARRIO', 'ZONA',\n",
       "       'SITIO', 'ARMA', 'MOVIL_AGRESOR', 'MOVIL_VICTIMA', 'EDAD', 'SEXO',\n",
       "       'CLASE', 'MARCA', 'MODELO', 'COLOR', 'FISCALES', 'POBLACION',\n",
       "       'SEVERIDAD_POBREZA_MONETARIA_EXTREMA',\n",
       "       'BRECHA_POBREZA_MONETARIA_EXTREMA', 'SEVERIDAD_POBREZA_MONETARIA',\n",
       "       'BRECHA_POBREZA_MONETARIA', 'LINEA_POBREZA_EXTREMA', 'LINEA_POBREZA',\n",
       "       'PROMEDIO_INGRESO_PER_CAPITA', 'POBREZA_MONETARIA_EXTREMA',\n",
       "       'POBREZA_MONETARIA'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 790,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}