reading_sociodata.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ccda0740-fa74-424b-9ad3-5f1d73cc516f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# importamos librerias\n",
    "\n",
    "import pandas as pd\n",
    "import re \n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f69d634c-3dc2-4d1d-859f-a7ffc3d66c9c",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "d:\\Respaldo toshiba\\projectos\\manifolds\\ciudades\\datos\\socioeconomicos\n"
     ]
    }
   ],
   "source": [
    "\n",
    "print(os.getcwd())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d753819",
   "metadata": {},
   "source": [
    "Cargamos datos socioecónomicos en formato excel a nivel de municipio descargado de ? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f6bb018b-b52f-4807-ad40-d5950eeeed43",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_municipios = pd.read_excel('DatosMunicipales2020.xlsx', sheet_name=0, dtype ={\"Código INE\":str})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c1f3c083",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Código INE</th>\n",
       "      <th>Municipio</th>\n",
       "      <th>Provincia</th>\n",
       "      <th>Comunidad Autónoma</th>\n",
       "      <th>Población Total</th>\n",
       "      <th>Población Hombres</th>\n",
       "      <th>Población Mujeres</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Conductores Hombres</th>\n",
       "      <th>Conductoras Mujeres</th>\n",
       "      <th>...</th>\n",
       "      <th>Alcohol (6 puntos)</th>\n",
       "      <th>Drogas (6 puntos)</th>\n",
       "      <th>Casco, Cinturón, SRI (3 puntos)</th>\n",
       "      <th>Móvil (3 puntos)</th>\n",
       "      <th>Semáforo (4 puntos)</th>\n",
       "      <th>Otra (3 puntos)</th>\n",
       "      <th>Otra (4 puntos)</th>\n",
       "      <th>Otra (6 puntos)</th>\n",
       "      <th>Unnamed: 95</th>\n",
       "      <th>Campañas</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01001</td>\n",
       "      <td>Alava ( municipio sin especificar)</td>\n",
       "      <td>Araba/Álava</td>\n",
       "      <td>País Vasco</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01002</td>\n",
       "      <td>Alegría-Dulantzi</td>\n",
       "      <td>Araba/Álava</td>\n",
       "      <td>País Vasco</td>\n",
       "      <td>2935.0</td>\n",
       "      <td>1531.0</td>\n",
       "      <td>1404.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1038.0</td>\n",
       "      <td>818.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01003</td>\n",
       "      <td>Amurrio</td>\n",
       "      <td>Araba/Álava</td>\n",
       "      <td>País Vasco</td>\n",
       "      <td>10264.0</td>\n",
       "      <td>5044.0</td>\n",
       "      <td>5220.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3664.0</td>\n",
       "      <td>2660.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01004</td>\n",
       "      <td>Aramaio</td>\n",
       "      <td>Araba/Álava</td>\n",
       "      <td>País Vasco</td>\n",
       "      <td>1442.0</td>\n",
       "      <td>748.0</td>\n",
       "      <td>694.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>566.0</td>\n",
       "      <td>425.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01006</td>\n",
       "      <td>Artziniega</td>\n",
       "      <td>Araba/Álava</td>\n",
       "      <td>País Vasco</td>\n",
       "      <td>1800.0</td>\n",
       "      <td>892.0</td>\n",
       "      <td>908.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>630.0</td>\n",
       "      <td>490.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 97 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Código INE                           Municipio    Provincia  \\\n",
       "0      01001  Alava ( municipio sin especificar)  Araba/Álava   \n",
       "1      01002                    Alegría-Dulantzi  Araba/Álava   \n",
       "2      01003                             Amurrio  Araba/Álava   \n",
       "3      01004                             Aramaio  Araba/Álava   \n",
       "4      01006                          Artziniega  Araba/Álava   \n",
       "\n",
       "  Comunidad Autónoma  Población Total  Población Hombres  Población Mujeres  \\\n",
       "0         País Vasco              NaN                NaN                NaN   \n",
       "1         País Vasco           2935.0             1531.0             1404.0   \n",
       "2         País Vasco          10264.0             5044.0             5220.0   \n",
       "3         País Vasco           1442.0              748.0              694.0   \n",
       "4         País Vasco           1800.0              892.0              908.0   \n",
       "\n",
       "   Unnamed: 7  Conductores Hombres  Conductoras Mujeres  ...  \\\n",
       "0         NaN                  1.0                  0.0  ...   \n",
       "1         NaN               1038.0                818.0  ...   \n",
       "2         NaN               3664.0               2660.0  ...   \n",
       "3         NaN                566.0                425.0  ...   \n",
       "4         NaN                630.0                490.0  ...   \n",
       "\n",
       "   Alcohol (6 puntos)  Drogas (6 puntos)  Casco, Cinturón, SRI (3 puntos)  \\\n",
       "0                 NaN                NaN                              NaN   \n",
       "1                 NaN                NaN                              NaN   \n",
       "2                 NaN                NaN                              NaN   \n",
       "3                 NaN                NaN                              NaN   \n",
       "4                 NaN                NaN                              NaN   \n",
       "\n",
       "   Móvil (3 puntos)  Semáforo (4 puntos)  Otra (3 puntos)  Otra (4 puntos)  \\\n",
       "0               NaN                  NaN              NaN              NaN   \n",
       "1               NaN                  NaN              NaN              NaN   \n",
       "2               NaN                  NaN              NaN              NaN   \n",
       "3               NaN                  NaN              NaN              NaN   \n",
       "4               NaN                  NaN              NaN              NaN   \n",
       "\n",
       "   Otra (6 puntos)  Unnamed: 95  Campañas  \n",
       "0              NaN          NaN       0.0  \n",
       "1              NaN          NaN       0.0  \n",
       "2              NaN          NaN       0.0  \n",
       "3              NaN          NaN       0.0  \n",
       "4              NaN          NaN       0.0  \n",
       "\n",
       "[5 rows x 97 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_municipios.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8c846ff",
   "metadata": {},
   "source": [
    "Por el momento nos interesa guardar el Municipio y su código INE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "63147540-8616-4689-bc85-cd100a193882",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_municipios_small = df_municipios.iloc[:,0:2].reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "eee85155-d64f-46d7-984f-4f41b3e1184f",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>Código INE</th>\n",
       "      <th>Municipio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>01001</td>\n",
       "      <td>Alava ( municipio sin especificar)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>01002</td>\n",
       "      <td>Alegría-Dulantzi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>01003</td>\n",
       "      <td>Amurrio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>01004</td>\n",
       "      <td>Aramaio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>01006</td>\n",
       "      <td>Artziniega</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8191</th>\n",
       "      <td>8191</td>\n",
       "      <td>50901</td>\n",
       "      <td>Biel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8192</th>\n",
       "      <td>8192</td>\n",
       "      <td>50902</td>\n",
       "      <td>Marracos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8193</th>\n",
       "      <td>8193</td>\n",
       "      <td>50903</td>\n",
       "      <td>Villamayor de Gállego</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8194</th>\n",
       "      <td>8194</td>\n",
       "      <td>51001</td>\n",
       "      <td>Ceuta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8195</th>\n",
       "      <td>8195</td>\n",
       "      <td>52001</td>\n",
       "      <td>Melilla</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8196 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      index Código INE                           Municipio\n",
       "0         0      01001  Alava ( municipio sin especificar)\n",
       "1         1      01002                    Alegría-Dulantzi\n",
       "2         2      01003                             Amurrio\n",
       "3         3      01004                             Aramaio\n",
       "4         4      01006                          Artziniega\n",
       "...     ...        ...                                 ...\n",
       "8191   8191      50901                                Biel\n",
       "8192   8192      50902                            Marracos\n",
       "8193   8193      50903               Villamayor de Gállego\n",
       "8194   8194      51001                               Ceuta\n",
       "8195   8195      52001                             Melilla\n",
       "\n",
       "[8196 rows x 3 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_municipios_small"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e9b358d",
   "metadata": {},
   "source": [
    "Paralelamente tenemos los datos en csv de las métricas calculadas usando datos vectoriales con el software ZZZ. Los resultados fueron guardados en ficheros csv, uno para cada dimensión (diversidad, división, forma, área)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "258f2453-96d6-43ef-bbaa-2ddeb9352949",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_ciudades = pd.read_csv(r\"D:\\Respaldo toshiba\\projectos\\manifolds\\ciudades\\datos\\metricas\\ciudades_diversidad.csv\", index_col = [0])\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "c41eef39",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PR</th>\n",
       "      <th>PRD</th>\n",
       "      <th>SHDI</th>\n",
       "      <th>SIDI</th>\n",
       "      <th>MSIDI</th>\n",
       "      <th>SHEI</th>\n",
       "      <th>SIEI</th>\n",
       "      <th>MSIEI</th>\n",
       "      <th>IEI</th>\n",
       "      <th>index</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8</td>\n",
       "      <td>0.498351</td>\n",
       "      <td>0.723601</td>\n",
       "      <td>0.755052</td>\n",
       "      <td>1.406710</td>\n",
       "      <td>0.745706</td>\n",
       "      <td>0.862917</td>\n",
       "      <td>0.676484</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Albacete.txt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8</td>\n",
       "      <td>0.495263</td>\n",
       "      <td>0.769670</td>\n",
       "      <td>0.746116</td>\n",
       "      <td>1.370878</td>\n",
       "      <td>0.771443</td>\n",
       "      <td>0.852704</td>\n",
       "      <td>0.659253</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Alcala.txt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>0.495263</td>\n",
       "      <td>0.769670</td>\n",
       "      <td>0.746116</td>\n",
       "      <td>1.370878</td>\n",
       "      <td>0.771443</td>\n",
       "      <td>0.852704</td>\n",
       "      <td>0.659253</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Alcobendas.txt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8</td>\n",
       "      <td>1.016461</td>\n",
       "      <td>0.714969</td>\n",
       "      <td>0.757634</td>\n",
       "      <td>1.417307</td>\n",
       "      <td>0.751111</td>\n",
       "      <td>0.865868</td>\n",
       "      <td>0.681581</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Alcorcon.txt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>0.752361</td>\n",
       "      <td>1.209382</td>\n",
       "      <td>0.625019</td>\n",
       "      <td>0.980880</td>\n",
       "      <td>0.626374</td>\n",
       "      <td>0.714308</td>\n",
       "      <td>0.471704</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Algeciras.txt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>8</td>\n",
       "      <td>0.302543</td>\n",
       "      <td>0.758450</td>\n",
       "      <td>0.742545</td>\n",
       "      <td>1.356912</td>\n",
       "      <td>0.741693</td>\n",
       "      <td>0.848623</td>\n",
       "      <td>0.652537</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Alicante.txt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>8</td>\n",
       "      <td>0.655187</td>\n",
       "      <td>0.713636</td>\n",
       "      <td>0.759126</td>\n",
       "      <td>1.423480</td>\n",
       "      <td>0.762750</td>\n",
       "      <td>0.867572</td>\n",
       "      <td>0.684549</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Almeria.txt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>9</td>\n",
       "      <td>0.747920</td>\n",
       "      <td>7.128600</td>\n",
       "      <td>0.216069</td>\n",
       "      <td>0.243434</td>\n",
       "      <td>0.236178</td>\n",
       "      <td>0.243077</td>\n",
       "      <td>0.110792</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Avila.txt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>0.325322</td>\n",
       "      <td>1.675347</td>\n",
       "      <td>0.541120</td>\n",
       "      <td>0.778966</td>\n",
       "      <td>0.551175</td>\n",
       "      <td>0.618423</td>\n",
       "      <td>0.374603</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Badajoz.txt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>8</td>\n",
       "      <td>1.174438</td>\n",
       "      <td>0.667509</td>\n",
       "      <td>0.779475</td>\n",
       "      <td>1.511744</td>\n",
       "      <td>0.777582</td>\n",
       "      <td>0.890829</td>\n",
       "      <td>0.726995</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Badalona.txt</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PR       PRD      SHDI      SIDI     MSIDI      SHEI      SIEI     MSIEI  \\\n",
       "0   8  0.498351  0.723601  0.755052  1.406710  0.745706  0.862917  0.676484   \n",
       "1   8  0.495263  0.769670  0.746116  1.370878  0.771443  0.852704  0.659253   \n",
       "2   8  0.495263  0.769670  0.746116  1.370878  0.771443  0.852704  0.659253   \n",
       "3   8  1.016461  0.714969  0.757634  1.417307  0.751111  0.865868  0.681581   \n",
       "4   8  0.752361  1.209382  0.625019  0.980880  0.626374  0.714308  0.471704   \n",
       "5   8  0.302543  0.758450  0.742545  1.356912  0.741693  0.848623  0.652537   \n",
       "6   8  0.655187  0.713636  0.759126  1.423480  0.762750  0.867572  0.684549   \n",
       "7   9  0.747920  7.128600  0.216069  0.243434  0.236178  0.243077  0.110792   \n",
       "8   8  0.325322  1.675347  0.541120  0.778966  0.551175  0.618423  0.374603   \n",
       "9   8  1.174438  0.667509  0.779475  1.511744  0.777582  0.890829  0.726995   \n",
       "\n",
       "   IEI           index  \n",
       "0  0.0    Albacete.txt  \n",
       "1  0.0      Alcala.txt  \n",
       "2  0.0  Alcobendas.txt  \n",
       "3  0.0    Alcorcon.txt  \n",
       "4  0.0   Algeciras.txt  \n",
       "5  0.0    Alicante.txt  \n",
       "6  0.0     Almeria.txt  \n",
       "7  0.0       Avila.txt  \n",
       "8  0.0     Badajoz.txt  \n",
       "9  0.0    Badalona.txt  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ciudades.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67d17b17",
   "metadata": {},
   "source": [
    "Vamos a copiar la columna index a una columna llamada ciudades."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f25a526f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ciudades['ciudades'] = df_ciudades['index'].str.replace(r'\\.txt$', '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "aa8134dd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PR</th>\n",
       "      <th>PRD</th>\n",
       "      <th>SHDI</th>\n",
       "      <th>SIDI</th>\n",
       "      <th>MSIDI</th>\n",
       "      <th>SHEI</th>\n",
       "      <th>SIEI</th>\n",
       "      <th>MSIEI</th>\n",
       "      <th>IEI</th>\n",
       "      <th>index</th>\n",
       "      <th>ciudades</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8</td>\n",
       "      <td>0.498351</td>\n",
       "      <td>0.723601</td>\n",
       "      <td>0.755052</td>\n",
       "      <td>1.406710</td>\n",
       "      <td>0.745706</td>\n",
       "      <td>0.862917</td>\n",
       "      <td>0.676484</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Albacete.txt</td>\n",
       "      <td>Albacete.txt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8</td>\n",
       "      <td>0.495263</td>\n",
       "      <td>0.769670</td>\n",
       "      <td>0.746116</td>\n",
       "      <td>1.370878</td>\n",
       "      <td>0.771443</td>\n",
       "      <td>0.852704</td>\n",
       "      <td>0.659253</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Alcala.txt</td>\n",
       "      <td>Alcala.txt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>0.495263</td>\n",
       "      <td>0.769670</td>\n",
       "      <td>0.746116</td>\n",
       "      <td>1.370878</td>\n",
       "      <td>0.771443</td>\n",
       "      <td>0.852704</td>\n",
       "      <td>0.659253</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Alcobendas.txt</td>\n",
       "      <td>Alcobendas.txt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8</td>\n",
       "      <td>1.016461</td>\n",
       "      <td>0.714969</td>\n",
       "      <td>0.757634</td>\n",
       "      <td>1.417307</td>\n",
       "      <td>0.751111</td>\n",
       "      <td>0.865868</td>\n",
       "      <td>0.681581</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Alcorcon.txt</td>\n",
       "      <td>Alcorcon.txt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>0.752361</td>\n",
       "      <td>1.209382</td>\n",
       "      <td>0.625019</td>\n",
       "      <td>0.980880</td>\n",
       "      <td>0.626374</td>\n",
       "      <td>0.714308</td>\n",
       "      <td>0.471704</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Algeciras.txt</td>\n",
       "      <td>Algeciras.txt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>8</td>\n",
       "      <td>0.369649</td>\n",
       "      <td>0.871006</td>\n",
       "      <td>0.710258</td>\n",
       "      <td>1.238763</td>\n",
       "      <td>0.723575</td>\n",
       "      <td>0.811723</td>\n",
       "      <td>0.595719</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Valencia.txt</td>\n",
       "      <td>Valencia.txt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>8</td>\n",
       "      <td>0.289363</td>\n",
       "      <td>0.708775</td>\n",
       "      <td>0.762939</td>\n",
       "      <td>1.439438</td>\n",
       "      <td>0.757313</td>\n",
       "      <td>0.871930</td>\n",
       "      <td>0.692223</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Valladolid.txt</td>\n",
       "      <td>Valladolid.txt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>8</td>\n",
       "      <td>0.299214</td>\n",
       "      <td>0.924350</td>\n",
       "      <td>0.691404</td>\n",
       "      <td>1.175722</td>\n",
       "      <td>0.674770</td>\n",
       "      <td>0.790176</td>\n",
       "      <td>0.565403</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Vigo.txt</td>\n",
       "      <td>Vigo.txt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>8</td>\n",
       "      <td>1.449856</td>\n",
       "      <td>0.673657</td>\n",
       "      <td>0.774217</td>\n",
       "      <td>1.488181</td>\n",
       "      <td>0.758289</td>\n",
       "      <td>0.884820</td>\n",
       "      <td>0.715664</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Zamora.txt</td>\n",
       "      <td>Zamora.txt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>8</td>\n",
       "      <td>0.190993</td>\n",
       "      <td>0.765839</td>\n",
       "      <td>0.742138</td>\n",
       "      <td>1.355330</td>\n",
       "      <td>0.731288</td>\n",
       "      <td>0.848158</td>\n",
       "      <td>0.651776</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Zaragoza.txt</td>\n",
       "      <td>Zaragoza.txt</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>72 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    PR       PRD      SHDI      SIDI     MSIDI      SHEI      SIEI     MSIEI  \\\n",
       "0    8  0.498351  0.723601  0.755052  1.406710  0.745706  0.862917  0.676484   \n",
       "1    8  0.495263  0.769670  0.746116  1.370878  0.771443  0.852704  0.659253   \n",
       "2    8  0.495263  0.769670  0.746116  1.370878  0.771443  0.852704  0.659253   \n",
       "3    8  1.016461  0.714969  0.757634  1.417307  0.751111  0.865868  0.681581   \n",
       "4    8  0.752361  1.209382  0.625019  0.980880  0.626374  0.714308  0.471704   \n",
       "..  ..       ...       ...       ...       ...       ...       ...       ...   \n",
       "67   8  0.369649  0.871006  0.710258  1.238763  0.723575  0.811723  0.595719   \n",
       "68   8  0.289363  0.708775  0.762939  1.439438  0.757313  0.871930  0.692223   \n",
       "69   8  0.299214  0.924350  0.691404  1.175722  0.674770  0.790176  0.565403   \n",
       "70   8  1.449856  0.673657  0.774217  1.488181  0.758289  0.884820  0.715664   \n",
       "71   8  0.190993  0.765839  0.742138  1.355330  0.731288  0.848158  0.651776   \n",
       "\n",
       "    IEI           index        ciudades  \n",
       "0   0.0    Albacete.txt    Albacete.txt  \n",
       "1   0.0      Alcala.txt      Alcala.txt  \n",
       "2   0.0  Alcobendas.txt  Alcobendas.txt  \n",
       "3   0.0    Alcorcon.txt    Alcorcon.txt  \n",
       "4   0.0   Algeciras.txt   Algeciras.txt  \n",
       "..  ...             ...             ...  \n",
       "67  0.0    Valencia.txt    Valencia.txt  \n",
       "68  0.0  Valladolid.txt  Valladolid.txt  \n",
       "69  0.0        Vigo.txt        Vigo.txt  \n",
       "70  0.0      Zamora.txt      Zamora.txt  \n",
       "71  0.0    Zaragoza.txt    Zaragoza.txt  \n",
       "\n",
       "[72 rows x 11 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ciudades"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b15e422f",
   "metadata": {},
   "source": [
    "reemplazamos el caracter _ por un espacio en el nombre de las ciudades"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "be6864f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ciudades['ciudades'] = df_ciudades['ciudades'].str.replace('_', ' ')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a2d9ae4",
   "metadata": {},
   "source": [
    "A continuación eliminamos .txt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "af0f0bc5-d281-4009-a45c-a225325c4e23",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "\n",
    "df_ciudades['ciudades'] = df_ciudades['ciudades'].str.replace('.txt', '')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "399eee04",
   "metadata": {},
   "source": [
    "Finalmente, capitalizamos el nombre de cada ciudad "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "66052412-33f4-4786-b175-8f1a2b6af801",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_ciudades['ciudades'] = df_ciudades['ciudades'].apply(lambda x: x.capitalize())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "389d43e9",
   "metadata": {},
   "source": [
    "Creamos un diccionario con clave igual al nombre incorrecto que puede encontrarse en el archivo csv,  que deriva del nombre del fichero original. El valor del diccionario es el nombre correcto, para poder reemplazar esto en cada columna."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "4105bb2a-4f31-419e-8c29-b060601b65f9",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "rep = {'Avila':'Ávila','Alicante':'Alicante/Alacant','Alcorcon':'Alcorcón', 'Almeria':'Almería','Alcala':'Alcalá de Henares', 'Cadiz':'Cádiz','Caceres':'Cáceres', 'Cordoba':'Córdoba','Castello': 'Castelló de la Plana', 'Ciudad real':'Ciudad Real','Bodajoz':'Badajoz','Dos hermanas':'Dos Hermanas','Fuentelabra':'Fuenlabrada',\n",
    "'Leganes':'Leganés','Logronio':'Logroño','Mataro':'Mataró','Mostoles':'Móstoles','Santa coloma':'Santa Coloma de Gramenet','Santa cruz tenerife':'Santa Cruz de Tenerife','Jaen':'Jaén','Jerez':'Jerez de la Frontera','Leon':'León','Malaga':'Málaga',\n",
    "'San cristobal':'San Cristóbal de La Laguna','Terrasa':'Terrassa','Terragona':'Tarragona','Torrejon':'Torrejón de Ardoz','Corunia':'Coruña, A','Gijon':'Gijón',\n",
    "'Palma':'Palma de Mallorca','Las palma':'Palmas de Gran Canaria, Las', 'Elche': 'Elche/Elx', 'Hospitalet':\"Hospitalet de Llobregat, L'\"} "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39765230",
   "metadata": {},
   "source": [
    "Se define la función replacement_cities para reemplazar de forma iterativa el nombre incorrecto por el nombre correcto definido en el diccionario"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "ae5bde5d-d6d9-41f0-a6ac-8b9a9f5526e1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def replace_cities(df, replacements):\n",
    "    for city, replacement in replacements.items():\n",
    "        df['ciudades'] = df['ciudades'].str.replace(city, replacement)\n",
    "    return df\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0cacbddd",
   "metadata": {},
   "source": [
    "Se define un nuevo dataframe con los valores en la columna ciudades reemplazados, se conserva el antiguo como columna index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "f78c26ea-a1f0-4d8f-82e3-b04f739059c7",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df = replace_cities(df_ciudades, rep)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "c73a7f52",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PR</th>\n",
       "      <th>PRD</th>\n",
       "      <th>SHDI</th>\n",
       "      <th>SIDI</th>\n",
       "      <th>MSIDI</th>\n",
       "      <th>SHEI</th>\n",
       "      <th>SIEI</th>\n",
       "      <th>MSIEI</th>\n",
       "      <th>IEI</th>\n",
       "      <th>index</th>\n",
       "      <th>ciudades</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8</td>\n",
       "      <td>0.498351</td>\n",
       "      <td>0.723601</td>\n",
       "      <td>0.755052</td>\n",
       "      <td>1.406710</td>\n",
       "      <td>0.745706</td>\n",
       "      <td>0.862917</td>\n",
       "      <td>0.676484</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Albacete.txt</td>\n",
       "      <td>Albacete</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8</td>\n",
       "      <td>0.495263</td>\n",
       "      <td>0.769670</td>\n",
       "      <td>0.746116</td>\n",
       "      <td>1.370878</td>\n",
       "      <td>0.771443</td>\n",
       "      <td>0.852704</td>\n",
       "      <td>0.659253</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Alcala.txt</td>\n",
       "      <td>Alcalá de Henares</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>0.495263</td>\n",
       "      <td>0.769670</td>\n",
       "      <td>0.746116</td>\n",
       "      <td>1.370878</td>\n",
       "      <td>0.771443</td>\n",
       "      <td>0.852704</td>\n",
       "      <td>0.659253</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Alcobendas.txt</td>\n",
       "      <td>Alcobendas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8</td>\n",
       "      <td>1.016461</td>\n",
       "      <td>0.714969</td>\n",
       "      <td>0.757634</td>\n",
       "      <td>1.417307</td>\n",
       "      <td>0.751111</td>\n",
       "      <td>0.865868</td>\n",
       "      <td>0.681581</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Alcorcon.txt</td>\n",
       "      <td>Alcorcón</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>0.752361</td>\n",
       "      <td>1.209382</td>\n",
       "      <td>0.625019</td>\n",
       "      <td>0.980880</td>\n",
       "      <td>0.626374</td>\n",
       "      <td>0.714308</td>\n",
       "      <td>0.471704</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Algeciras.txt</td>\n",
       "      <td>Algeciras</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>8</td>\n",
       "      <td>0.369649</td>\n",
       "      <td>0.871006</td>\n",
       "      <td>0.710258</td>\n",
       "      <td>1.238763</td>\n",
       "      <td>0.723575</td>\n",
       "      <td>0.811723</td>\n",
       "      <td>0.595719</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Valencia.txt</td>\n",
       "      <td>Valencia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>8</td>\n",
       "      <td>0.289363</td>\n",
       "      <td>0.708775</td>\n",
       "      <td>0.762939</td>\n",
       "      <td>1.439438</td>\n",
       "      <td>0.757313</td>\n",
       "      <td>0.871930</td>\n",
       "      <td>0.692223</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Valladolid.txt</td>\n",
       "      <td>Valladolid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>8</td>\n",
       "      <td>0.299214</td>\n",
       "      <td>0.924350</td>\n",
       "      <td>0.691404</td>\n",
       "      <td>1.175722</td>\n",
       "      <td>0.674770</td>\n",
       "      <td>0.790176</td>\n",
       "      <td>0.565403</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Vigo.txt</td>\n",
       "      <td>Vigo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>8</td>\n",
       "      <td>1.449856</td>\n",
       "      <td>0.673657</td>\n",
       "      <td>0.774217</td>\n",
       "      <td>1.488181</td>\n",
       "      <td>0.758289</td>\n",
       "      <td>0.884820</td>\n",
       "      <td>0.715664</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Zamora.txt</td>\n",
       "      <td>Zamora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>8</td>\n",
       "      <td>0.190993</td>\n",
       "      <td>0.765839</td>\n",
       "      <td>0.742138</td>\n",
       "      <td>1.355330</td>\n",
       "      <td>0.731288</td>\n",
       "      <td>0.848158</td>\n",
       "      <td>0.651776</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Zaragoza.txt</td>\n",
       "      <td>Zaragoza</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>72 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    PR       PRD      SHDI      SIDI     MSIDI      SHEI      SIEI     MSIEI  \\\n",
       "0    8  0.498351  0.723601  0.755052  1.406710  0.745706  0.862917  0.676484   \n",
       "1    8  0.495263  0.769670  0.746116  1.370878  0.771443  0.852704  0.659253   \n",
       "2    8  0.495263  0.769670  0.746116  1.370878  0.771443  0.852704  0.659253   \n",
       "3    8  1.016461  0.714969  0.757634  1.417307  0.751111  0.865868  0.681581   \n",
       "4    8  0.752361  1.209382  0.625019  0.980880  0.626374  0.714308  0.471704   \n",
       "..  ..       ...       ...       ...       ...       ...       ...       ...   \n",
       "67   8  0.369649  0.871006  0.710258  1.238763  0.723575  0.811723  0.595719   \n",
       "68   8  0.289363  0.708775  0.762939  1.439438  0.757313  0.871930  0.692223   \n",
       "69   8  0.299214  0.924350  0.691404  1.175722  0.674770  0.790176  0.565403   \n",
       "70   8  1.449856  0.673657  0.774217  1.488181  0.758289  0.884820  0.715664   \n",
       "71   8  0.190993  0.765839  0.742138  1.355330  0.731288  0.848158  0.651776   \n",
       "\n",
       "    IEI           index           ciudades  \n",
       "0   0.0    Albacete.txt           Albacete  \n",
       "1   0.0      Alcala.txt  Alcalá de Henares  \n",
       "2   0.0  Alcobendas.txt         Alcobendas  \n",
       "3   0.0    Alcorcon.txt           Alcorcón  \n",
       "4   0.0   Algeciras.txt          Algeciras  \n",
       "..  ...             ...                ...  \n",
       "67  0.0    Valencia.txt           Valencia  \n",
       "68  0.0  Valladolid.txt         Valladolid  \n",
       "69  0.0        Vigo.txt               Vigo  \n",
       "70  0.0      Zamora.txt             Zamora  \n",
       "71  0.0    Zaragoza.txt           Zaragoza  \n",
       "\n",
       "[72 rows x 11 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a857811",
   "metadata": {},
   "source": [
    "Hacemos cambios adicionales p"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "5ef14515-0f90-47dd-9484-fc4ea0d3670d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_ciudades['ciudades'] = df['ciudades'].str.replace('Palmas de Gran Canaria, Lass', 'Palmas de Gran Canaria, Las')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d589caa",
   "metadata": {},
   "source": [
    "Creamos una serie con la columna nombre ciudades"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "fb8dee55-41b5-4a53-9b28-c5c0f9680abd",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "nombre_ciudades = df_ciudades.ciudades"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "7e1f0be9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0             Albacete\n",
       "1    Alcalá de Henares\n",
       "2           Alcobendas\n",
       "3             Alcorcón\n",
       "4            Algeciras\n",
       "Name: ciudades, dtype: object"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nombre_ciudades.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ebb080a",
   "metadata": {},
   "source": [
    "Verificamos que el nombre de cada ciudad corresponde al nombre oficial"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "6dd5b52e-fdb8-4e5c-aea7-8dd006505e62",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "72\n",
      "Albacete\n",
      "Alcalá de Henares\n",
      "Alcobendas\n",
      "Alcorcón\n",
      "Algeciras\n",
      "Alicante/Alacant\n",
      "Almería\n",
      "Ávila\n",
      "Badajoz\n",
      "Badalona\n",
      "Barcelona\n",
      "Burgos\n",
      "Cáceres\n",
      "Cádiz\n",
      "Cartagena\n",
      "Castella\n",
      "Ceuta\n",
      "Ciudad Real\n",
      "Córdoba\n",
      "Coruña, A\n",
      "Cuenca\n",
      "Dos Hermanas\n",
      "Elche/Elx\n",
      "Fuenlabrada\n",
      "Getafe\n",
      "Gijón\n",
      "Girona\n",
      "Granada\n",
      "Guadalajara\n",
      "Hospitalet de Llobregat, L'\n",
      "Huelva\n",
      "Huesca\n",
      "Jaén\n",
      "Jerez de la Frontera\n",
      "Laganes\n",
      "Palmas de Gran Canaria, Las\n",
      "León\n",
      "Lleida\n",
      "Logroño\n",
      "Lugo\n",
      "Madrid\n",
      "Málaga\n",
      "Marbella\n",
      "Mataró\n",
      "Melilla\n",
      "Móstoles\n",
      "Murcia\n",
      "Ourense\n",
      "Oviedo\n",
      "Palencia\n",
      "Palma de Mallorca\n",
      "Parla\n",
      "Pontevedra\n",
      "Sabadell\n",
      "Salamanca\n",
      "Santander\n",
      "Santa Coloma de Gramenet\n",
      "Santa cruz\n",
      "San Cristóbal de La Laguna\n",
      "Segovia\n",
      "Sevilla\n",
      "Soria\n",
      "Tarragona\n",
      "Terrassa\n",
      "Teruel\n",
      "Toledo\n",
      "Torrejón de Ardoz\n",
      "Valencia\n",
      "Valladolid\n",
      "Vigo\n",
      "Zamora\n",
      "Zaragoza\n"
     ]
    }
   ],
   "source": [
    "print(len(nombre_ciudades))\n",
    "for ciudades in nombre_ciudades:\n",
    "    print(ciudades)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "8dcb92bc-3f8e-409e-98c5-778858e980aa",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "ename": "IndentationError",
     "evalue": "unindent does not match any outer indentation level (<string>, line 14)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;36m  File \u001b[1;32m<string>:14\u001b[1;36m\u001b[0m\n\u001b[1;33m    else:\u001b[0m\n\u001b[1;37m         ^\u001b[0m\n\u001b[1;31mIndentationError\u001b[0m\u001b[1;31m:\u001b[0m unindent does not match any outer indentation level\n"
     ]
    }
   ],
   "source": [
    "# buscar los nombres de ciudades en la columna \"municipios\"\n",
    "nombre_dic = {}\n",
    "saltar =  'municipio sin especificar'\n",
    "for ciudad in nombre_ciudades:\n",
    "   filtro_exacto = df_municipios['Municipio'] == ciudad\n",
    "   filtro_relativo = df_municipios['Municipio'].str.contains(ciudad, regex=True)\n",
    "    mun = df_municipios.loc[filtro, 'Municipio'].iloc[0]\n",
    "   if filtro_exacto.any():\n",
    "        temp = df_municipios.loc[filtro_exacto, 'Municipio'].iloc[0]\n",
    "        if temp == None:\n",
    "            nombre_dic[ciudad] = df_municipios.loc[filtro_relativo, 'Municipio'].iloc[0]\n",
    "        else: \n",
    "            nombre_dic[ciudad] = temp\n",
    "    else:\n",
    "        # si no se encuentra ninguna coincidencia, agregar un valor nulo\n",
    "        nombre_dic[ciudad] = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "9d8f63bc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Código INE</th>\n",
       "      <th>Municipio</th>\n",
       "      <th>Provincia</th>\n",
       "      <th>Comunidad Autónoma</th>\n",
       "      <th>Población Total</th>\n",
       "      <th>Población Hombres</th>\n",
       "      <th>Población Mujeres</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Conductores Hombres</th>\n",
       "      <th>Conductoras Mujeres</th>\n",
       "      <th>...</th>\n",
       "      <th>Alcohol (6 puntos)</th>\n",
       "      <th>Drogas (6 puntos)</th>\n",
       "      <th>Casco, Cinturón, SRI (3 puntos)</th>\n",
       "      <th>Móvil (3 puntos)</th>\n",
       "      <th>Semáforo (4 puntos)</th>\n",
       "      <th>Otra (3 puntos)</th>\n",
       "      <th>Otra (4 puntos)</th>\n",
       "      <th>Otra (6 puntos)</th>\n",
       "      <th>Unnamed: 95</th>\n",
       "      <th>Campañas</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01001</td>\n",
       "      <td>Alava ( municipio sin especificar)</td>\n",
       "      <td>Araba/Álava</td>\n",
       "      <td>País Vasco</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01002</td>\n",
       "      <td>Alegría-Dulantzi</td>\n",
       "      <td>Araba/Álava</td>\n",
       "      <td>País Vasco</td>\n",
       "      <td>2935.0</td>\n",
       "      <td>1531.0</td>\n",
       "      <td>1404.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1038.0</td>\n",
       "      <td>818.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01003</td>\n",
       "      <td>Amurrio</td>\n",
       "      <td>Araba/Álava</td>\n",
       "      <td>País Vasco</td>\n",
       "      <td>10264.0</td>\n",
       "      <td>5044.0</td>\n",
       "      <td>5220.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3664.0</td>\n",
       "      <td>2660.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01004</td>\n",
       "      <td>Aramaio</td>\n",
       "      <td>Araba/Álava</td>\n",
       "      <td>País Vasco</td>\n",
       "      <td>1442.0</td>\n",
       "      <td>748.0</td>\n",
       "      <td>694.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>566.0</td>\n",
       "      <td>425.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01006</td>\n",
       "      <td>Artziniega</td>\n",
       "      <td>Araba/Álava</td>\n",
       "      <td>País Vasco</td>\n",
       "      <td>1800.0</td>\n",
       "      <td>892.0</td>\n",
       "      <td>908.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>630.0</td>\n",
       "      <td>490.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8191</th>\n",
       "      <td>50901</td>\n",
       "      <td>Biel</td>\n",
       "      <td>Zaragoza</td>\n",
       "      <td>Aragón</td>\n",
       "      <td>155.0</td>\n",
       "      <td>97.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>39.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8192</th>\n",
       "      <td>50902</td>\n",
       "      <td>Marracos</td>\n",
       "      <td>Zaragoza</td>\n",
       "      <td>Aragón</td>\n",
       "      <td>84.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8193</th>\n",
       "      <td>50903</td>\n",
       "      <td>Villamayor de Gállego</td>\n",
       "      <td>Zaragoza</td>\n",
       "      <td>Aragón</td>\n",
       "      <td>2745.0</td>\n",
       "      <td>1375.0</td>\n",
       "      <td>1370.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>627.0</td>\n",
       "      <td>430.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8194</th>\n",
       "      <td>51001</td>\n",
       "      <td>Ceuta</td>\n",
       "      <td>Ceuta</td>\n",
       "      <td>Ceuta</td>\n",
       "      <td>84202.0</td>\n",
       "      <td>42542.0</td>\n",
       "      <td>41660.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25274.0</td>\n",
       "      <td>16376.0</td>\n",
       "      <td>...</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>118.0</td>\n",
       "      <td>378.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8195</th>\n",
       "      <td>52001</td>\n",
       "      <td>Melilla</td>\n",
       "      <td>Melilla</td>\n",
       "      <td>Melilla</td>\n",
       "      <td>87076.0</td>\n",
       "      <td>44162.0</td>\n",
       "      <td>42914.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26521.0</td>\n",
       "      <td>18053.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>221.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66.0</td>\n",
       "      <td>843.0</td>\n",
       "      <td>149.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8196 rows × 97 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Código INE                           Municipio    Provincia  \\\n",
       "0         01001  Alava ( municipio sin especificar)  Araba/Álava   \n",
       "1         01002                    Alegría-Dulantzi  Araba/Álava   \n",
       "2         01003                             Amurrio  Araba/Álava   \n",
       "3         01004                             Aramaio  Araba/Álava   \n",
       "4         01006                          Artziniega  Araba/Álava   \n",
       "...         ...                                 ...          ...   \n",
       "8191      50901                                Biel     Zaragoza   \n",
       "8192      50902                            Marracos     Zaragoza   \n",
       "8193      50903               Villamayor de Gállego     Zaragoza   \n",
       "8194      51001                               Ceuta        Ceuta   \n",
       "8195      52001                             Melilla      Melilla   \n",
       "\n",
       "     Comunidad Autónoma  Población Total  Población Hombres  \\\n",
       "0            País Vasco              NaN                NaN   \n",
       "1            País Vasco           2935.0             1531.0   \n",
       "2            País Vasco          10264.0             5044.0   \n",
       "3            País Vasco           1442.0              748.0   \n",
       "4            País Vasco           1800.0              892.0   \n",
       "...                 ...              ...                ...   \n",
       "8191             Aragón            155.0               97.0   \n",
       "8192             Aragón             84.0               46.0   \n",
       "8193             Aragón           2745.0             1375.0   \n",
       "8194              Ceuta          84202.0            42542.0   \n",
       "8195            Melilla          87076.0            44162.0   \n",
       "\n",
       "      Población Mujeres  Unnamed: 7  Conductores Hombres  Conductoras Mujeres  \\\n",
       "0                   NaN         NaN                  1.0                  0.0   \n",
       "1                1404.0         NaN               1038.0                818.0   \n",
       "2                5220.0         NaN               3664.0               2660.0   \n",
       "3                 694.0         NaN                566.0                425.0   \n",
       "4                 908.0         NaN                630.0                490.0   \n",
       "...                 ...         ...                  ...                  ...   \n",
       "8191               58.0         NaN                 39.0                 15.0   \n",
       "8192               38.0         NaN                 20.0                  7.0   \n",
       "8193             1370.0         NaN                627.0                430.0   \n",
       "8194            41660.0         NaN              25274.0              16376.0   \n",
       "8195            42914.0         NaN              26521.0              18053.0   \n",
       "\n",
       "      ...  Alcohol (6 puntos)  Drogas (6 puntos)  \\\n",
       "0     ...                 NaN                NaN   \n",
       "1     ...                 NaN                NaN   \n",
       "2     ...                 NaN                NaN   \n",
       "3     ...                 NaN                NaN   \n",
       "4     ...                 NaN                NaN   \n",
       "...   ...                 ...                ...   \n",
       "8191  ...                 NaN                NaN   \n",
       "8192  ...                 NaN                NaN   \n",
       "8193  ...                 NaN                NaN   \n",
       "8194  ...                10.0                NaN   \n",
       "8195  ...                 3.0                NaN   \n",
       "\n",
       "      Casco, Cinturón, SRI (3 puntos)  Móvil (3 puntos)  Semáforo (4 puntos)  \\\n",
       "0                                 NaN               NaN                  NaN   \n",
       "1                                 NaN               NaN                  NaN   \n",
       "2                                 NaN               NaN                  NaN   \n",
       "3                                 NaN               NaN                  NaN   \n",
       "4                                 NaN               NaN                  NaN   \n",
       "...                               ...               ...                  ...   \n",
       "8191                              NaN               NaN                  NaN   \n",
       "8192                              NaN               NaN                  NaN   \n",
       "8193                              NaN               NaN                  NaN   \n",
       "8194                            118.0             378.0                 34.0   \n",
       "8195                            221.0               NaN                 66.0   \n",
       "\n",
       "      Otra (3 puntos)  Otra (4 puntos)  Otra (6 puntos)  Unnamed: 95  Campañas  \n",
       "0                 NaN              NaN              NaN          NaN       0.0  \n",
       "1                 NaN              NaN              NaN          NaN       0.0  \n",
       "2                 NaN              NaN              NaN          NaN       0.0  \n",
       "3                 NaN              NaN              NaN          NaN       0.0  \n",
       "4                 NaN              NaN              NaN          NaN       0.0  \n",
       "...               ...              ...              ...          ...       ...  \n",
       "8191              NaN              NaN              NaN          NaN       NaN  \n",
       "8192              NaN              NaN              NaN          NaN       NaN  \n",
       "8193              NaN              NaN              NaN          NaN       NaN  \n",
       "8194             51.0             88.0             51.0          NaN       NaN  \n",
       "8195            843.0            149.0             82.0          NaN       NaN  \n",
       "\n",
       "[8196 rows x 97 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_municipios"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "81a335fb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    Alava ( municipio sin especificar)\n",
       "1                      Alegría-Dulantzi\n",
       "2                               Amurrio\n",
       "3                               Aramaio\n",
       "4                            Artziniega\n",
       "Name: Municipio, dtype: object"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_municipios.Municipio.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "08a35bf0-daa4-4206-a19b-2475246b92f9",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "ename": "IndexError",
     "evalue": "single positional indexer is out-of-bounds",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mIndexError\u001b[0m                                Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[29], line 8\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m----> 8\u001b[0m     valor \u001b[38;5;241m=\u001b[39m df_municipios\u001b[38;5;241m.\u001b[39mloc[filtro_exacto, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mMunicipio\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m.\u001b[39miloc[\u001b[38;5;241m0\u001b[39m]\n\u001b[0;32m      9\u001b[0m     nombre_dic[ciudad] \u001b[38;5;241m=\u001b[39m valor\n",
      "File \u001b[1;32mc:\\anaconda_instalation\\envs\\spatial_data\\Lib\\site-packages\\pandas\\core\\indexing.py:1191\u001b[0m, in \u001b[0;36m_LocationIndexer.__getitem__\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   1190\u001b[0m maybe_callable \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_check_deprecated_callable_usage(key, maybe_callable)\n\u001b[1;32m-> 1191\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_getitem_axis(maybe_callable, axis\u001b[38;5;241m=\u001b[39maxis)\n",
      "File \u001b[1;32mc:\\anaconda_instalation\\envs\\spatial_data\\Lib\\site-packages\\pandas\\core\\indexing.py:1752\u001b[0m, in \u001b[0;36m_iLocIndexer._getitem_axis\u001b[1;34m(self, key, axis)\u001b[0m\n\u001b[0;32m   1751\u001b[0m \u001b[38;5;66;03m# validate the location\u001b[39;00m\n\u001b[1;32m-> 1752\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_validate_integer(key, axis)\n\u001b[0;32m   1754\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mobj\u001b[38;5;241m.\u001b[39m_ixs(key, axis\u001b[38;5;241m=\u001b[39maxis)\n",
      "File \u001b[1;32mc:\\anaconda_instalation\\envs\\spatial_data\\Lib\\site-packages\\pandas\\core\\indexing.py:1685\u001b[0m, in \u001b[0;36m_iLocIndexer._validate_integer\u001b[1;34m(self, key, axis)\u001b[0m\n\u001b[0;32m   1684\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m key \u001b[38;5;241m>\u001b[39m\u001b[38;5;241m=\u001b[39m len_axis \u001b[38;5;129;01mor\u001b[39;00m key \u001b[38;5;241m<\u001b[39m \u001b[38;5;241m-\u001b[39mlen_axis:\n\u001b[1;32m-> 1685\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mIndexError\u001b[39;00m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124msingle positional indexer is out-of-bounds\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "\u001b[1;31mIndexError\u001b[0m: single positional indexer is out-of-bounds",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[1;31mIndexError\u001b[0m                                Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[29], line 11\u001b[0m\n\u001b[0;32m      9\u001b[0m     nombre_dic[ciudad] \u001b[38;5;241m=\u001b[39m valor\n\u001b[0;32m     10\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mIndexError\u001b[39;00m:\n\u001b[1;32m---> 11\u001b[0m     mun \u001b[38;5;241m=\u001b[39m df_municipios\u001b[38;5;241m.\u001b[39mloc[filtro_relativo, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mMunicipio\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m.\u001b[39miloc[\u001b[38;5;241m0\u001b[39m]\n\u001b[0;32m     12\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m saltar \u001b[38;5;129;01min\u001b[39;00m mun:\n\u001b[0;32m     13\u001b[0m         \u001b[38;5;28;01mpass\u001b[39;00m \u001b[38;5;66;03m# En este caso tendria que ser una funcion recursiva.\u001b[39;00m\n",
      "File \u001b[1;32mc:\\anaconda_instalation\\envs\\spatial_data\\Lib\\site-packages\\pandas\\core\\indexing.py:1191\u001b[0m, in \u001b[0;36m_LocationIndexer.__getitem__\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   1189\u001b[0m maybe_callable \u001b[38;5;241m=\u001b[39m com\u001b[38;5;241m.\u001b[39mapply_if_callable(key, \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mobj)\n\u001b[0;32m   1190\u001b[0m maybe_callable \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_check_deprecated_callable_usage(key, maybe_callable)\n\u001b[1;32m-> 1191\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_getitem_axis(maybe_callable, axis\u001b[38;5;241m=\u001b[39maxis)\n",
      "File \u001b[1;32mc:\\anaconda_instalation\\envs\\spatial_data\\Lib\\site-packages\\pandas\\core\\indexing.py:1752\u001b[0m, in \u001b[0;36m_iLocIndexer._getitem_axis\u001b[1;34m(self, key, axis)\u001b[0m\n\u001b[0;32m   1749\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mTypeError\u001b[39;00m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mCannot index by location index with a non-integer key\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m   1751\u001b[0m \u001b[38;5;66;03m# validate the location\u001b[39;00m\n\u001b[1;32m-> 1752\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_validate_integer(key, axis)\n\u001b[0;32m   1754\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mobj\u001b[38;5;241m.\u001b[39m_ixs(key, axis\u001b[38;5;241m=\u001b[39maxis)\n",
      "File \u001b[1;32mc:\\anaconda_instalation\\envs\\spatial_data\\Lib\\site-packages\\pandas\\core\\indexing.py:1685\u001b[0m, in \u001b[0;36m_iLocIndexer._validate_integer\u001b[1;34m(self, key, axis)\u001b[0m\n\u001b[0;32m   1683\u001b[0m len_axis \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mlen\u001b[39m(\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mobj\u001b[38;5;241m.\u001b[39m_get_axis(axis))\n\u001b[0;32m   1684\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m key \u001b[38;5;241m>\u001b[39m\u001b[38;5;241m=\u001b[39m len_axis \u001b[38;5;129;01mor\u001b[39;00m key \u001b[38;5;241m<\u001b[39m \u001b[38;5;241m-\u001b[39mlen_axis:\n\u001b[1;32m-> 1685\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mIndexError\u001b[39;00m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124msingle positional indexer is out-of-bounds\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "\u001b[1;31mIndexError\u001b[0m: single positional indexer is out-of-bounds"
     ]
    }
   ],
   "source": [
    "## Buscar los nombres de ciudades en la columna \"Municipio\"\n",
    "nombre_dic = {}\n",
    "saltar =  'municipio sin especificar'\n",
    "for ciudad in nombre_ciudades:\n",
    "    filtro_exacto = df_municipios['Municipio'] == ciudad\n",
    "    filtro_relativo = df_municipios['Municipio'].str.contains(ciudad, regex=True)\n",
    "    try:\n",
    "        valor = df_municipios.loc[filtro_exacto, 'Municipio'].iloc[0]\n",
    "        nombre_dic[ciudad] = valor\n",
    "    except IndexError:\n",
    "        mun = df_municipios.loc[filtro_relativo, 'Municipio'].iloc[0]\n",
    "        if saltar in mun:\n",
    "            pass # En este caso tendria que ser una funcion recursiva.\n",
    "        else:\n",
    "            nombre_dic[ciudad] = mun\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "f24879a0-ebdd-4f53-a4b7-d230de0a6092",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'nombre_dic' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[33], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m \u001b[38;5;28mlen\u001b[39m(nombre_dic)\n",
      "\u001b[1;31mNameError\u001b[0m: name 'nombre_dic' is not defined"
     ]
    }
   ],
   "source": [
    "len(nombre_dic)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "e3b83a50-f825-4fbb-8c57-b5637a115e95",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Albacete': 'Albacete',\n",
       " 'Alcalá de Henares': 'Alcalá de Henares',\n",
       " 'Alcobendas': 'Alcobendas',\n",
       " 'Alcorcón': 'Alcorcón',\n",
       " 'Algeciras': 'Algeciras',\n",
       " 'Alicante/Alacant': 'Alicante/Alacant',\n",
       " 'Almería': 'Almería',\n",
       " 'Ávila': 'Ávila',\n",
       " 'Badajoz': 'Badajoz',\n",
       " 'Badalona': 'Badalona',\n",
       " 'Barcelona': 'Barcelona',\n",
       " 'Burgos': 'Burgos',\n",
       " 'Cáceres': 'Cáceres',\n",
       " 'Cádiz': 'Cádiz',\n",
       " 'Cartagena': 'Cartagena',\n",
       " 'Castella': 'Castellanos de Zapardiel',\n",
       " 'Ceuta': 'Ceuta',\n",
       " 'Ciudad Real': 'Ciudad Real',\n",
       " 'Córdoba': 'Córdoba',\n",
       " 'Coruña, A': 'Coruña, A',\n",
       " 'Cuenca': 'Cuenca',\n",
       " 'Dos Hermanas': 'Dos Hermanas',\n",
       " 'Elche/Elx': 'Elche/Elx',\n",
       " 'Fuenlabrada': 'Fuenlabrada',\n",
       " 'Getafe': 'Getafe',\n",
       " 'Gijón': 'Gijón',\n",
       " 'Girona': 'Girona',\n",
       " 'Granada': 'Granada',\n",
       " 'Guadalajara': 'Guadalajara',\n",
       " \"Hospitalet de Llobregat, L'\": \"Hospitalet de Llobregat, L'\",\n",
       " 'Huelva': 'Huelva',\n",
       " 'Huesca': 'Huesca',\n",
       " 'Jaén': 'Jaén',\n",
       " 'Jerez de la Frontera': 'Jerez de la Frontera'}"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nombre_dic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "a6c81341",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "72"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(nombre_ciudades)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "9a5ed345-454d-4cef-927e-8a24febcee29",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "intentamos con Albacete\n",
      "Asignaremos el codigo de Albacete\n",
      "intentamos con Alcalá de Henares\n",
      "Asignaremos el codigo de Alcalá de Henares\n",
      "intentamos con Alcobendas\n",
      "Asignaremos el codigo de Alcobendas\n",
      "intentamos con Alcorcón\n",
      "Asignaremos el codigo de Alcorcón\n",
      "intentamos con Algeciras\n",
      "Asignaremos el codigo de Algeciras\n",
      "intentamos con Alicante/Alacant\n",
      "Asignaremos el codigo de Alicante/Alacant\n",
      "intentamos con Almería\n",
      "Asignaremos el codigo de Almería\n",
      "intentamos con Ávila\n",
      "Asignaremos el codigo de Ávila\n",
      "intentamos con Badajoz\n",
      "Asignaremos el codigo de Badajoz\n",
      "intentamos con Badalona\n",
      "Asignaremos el codigo de Badalona\n",
      "intentamos con Barcelona\n",
      "Asignaremos el codigo de Barcelona\n",
      "intentamos con Burgos\n",
      "Asignaremos el codigo de Burgos\n",
      "intentamos con Cáceres\n",
      "Asignaremos el codigo de Cáceres\n",
      "intentamos con Cádiz\n",
      "Asignaremos el codigo de Cádiz\n",
      "intentamos con Cartagena\n",
      "Asignaremos el codigo de Cartagena\n",
      "intentamos con Castella\n",
      "asigneremos 05061 que corresponde a Castellanos de Zapardiel, porque ha fallado Castella en el try\n",
      "intentamos con Ceuta\n",
      "Asignaremos el codigo de Ceuta\n",
      "intentamos con Ciudad Real\n",
      "Asignaremos el codigo de Ciudad Real\n",
      "intentamos con Córdoba\n",
      "Asignaremos el codigo de Córdoba\n",
      "intentamos con Coruña, A\n",
      "Asignaremos el codigo de Coruña, A\n",
      "intentamos con Cuenca\n",
      "Asignaremos el codigo de Cuenca\n",
      "intentamos con Dos Hermanas\n",
      "Asignaremos el codigo de Dos Hermanas\n",
      "intentamos con Elche/Elx\n",
      "Asignaremos el codigo de Elche/Elx\n",
      "intentamos con Fuenlabrada\n",
      "Asignaremos el codigo de Fuenlabrada\n",
      "intentamos con Getafe\n",
      "Asignaremos el codigo de Getafe\n",
      "intentamos con Gijón\n",
      "Asignaremos el codigo de Gijón\n",
      "intentamos con Girona\n",
      "Asignaremos el codigo de Girona\n",
      "intentamos con Granada\n",
      "Asignaremos el codigo de Granada\n",
      "intentamos con Guadalajara\n",
      "Asignaremos el codigo de Guadalajara\n",
      "intentamos con Hospitalet de Llobregat, L'\n",
      "Asignaremos el codigo de Hospitalet de Llobregat, L'\n",
      "intentamos con Huelva\n",
      "Asignaremos el codigo de Huelva\n",
      "intentamos con Huesca\n",
      "Asignaremos el codigo de Huesca\n",
      "intentamos con Jaén\n",
      "Asignaremos el codigo de Jaén\n",
      "intentamos con Jerez de la Frontera\n",
      "Asignaremos el codigo de Jerez de la Frontera\n",
      "intentamos con Laganes\n"
     ]
    },
    {
     "ename": "IndexError",
     "evalue": "single positional indexer is out-of-bounds",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mIndexError\u001b[0m                                Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[38], line 9\u001b[0m\n\u001b[0;32m      8\u001b[0m \u001b[38;5;28mprint\u001b[39m(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mintentamos con \u001b[39m\u001b[38;5;132;01m{\u001b[39;00mciudad\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m\"\u001b[39m)\n\u001b[1;32m----> 9\u001b[0m valor \u001b[38;5;241m=\u001b[39m df_municipios\u001b[38;5;241m.\u001b[39mloc[filtro_exacto, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mMunicipio\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m.\u001b[39miloc[\u001b[38;5;241m0\u001b[39m]\n\u001b[0;32m     10\u001b[0m \u001b[38;5;28mprint\u001b[39m(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mAsignaremos el codigo de \u001b[39m\u001b[38;5;132;01m{\u001b[39;00mvalor\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m\"\u001b[39m)\n",
      "File \u001b[1;32mc:\\anaconda_instalation\\envs\\spatial_data\\Lib\\site-packages\\pandas\\core\\indexing.py:1191\u001b[0m, in \u001b[0;36m_LocationIndexer.__getitem__\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   1190\u001b[0m maybe_callable \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_check_deprecated_callable_usage(key, maybe_callable)\n\u001b[1;32m-> 1191\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_getitem_axis(maybe_callable, axis\u001b[38;5;241m=\u001b[39maxis)\n",
      "File \u001b[1;32mc:\\anaconda_instalation\\envs\\spatial_data\\Lib\\site-packages\\pandas\\core\\indexing.py:1752\u001b[0m, in \u001b[0;36m_iLocIndexer._getitem_axis\u001b[1;34m(self, key, axis)\u001b[0m\n\u001b[0;32m   1751\u001b[0m \u001b[38;5;66;03m# validate the location\u001b[39;00m\n\u001b[1;32m-> 1752\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_validate_integer(key, axis)\n\u001b[0;32m   1754\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mobj\u001b[38;5;241m.\u001b[39m_ixs(key, axis\u001b[38;5;241m=\u001b[39maxis)\n",
      "File \u001b[1;32mc:\\anaconda_instalation\\envs\\spatial_data\\Lib\\site-packages\\pandas\\core\\indexing.py:1685\u001b[0m, in \u001b[0;36m_iLocIndexer._validate_integer\u001b[1;34m(self, key, axis)\u001b[0m\n\u001b[0;32m   1684\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m key \u001b[38;5;241m>\u001b[39m\u001b[38;5;241m=\u001b[39m len_axis \u001b[38;5;129;01mor\u001b[39;00m key \u001b[38;5;241m<\u001b[39m \u001b[38;5;241m-\u001b[39mlen_axis:\n\u001b[1;32m-> 1685\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mIndexError\u001b[39;00m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124msingle positional indexer is out-of-bounds\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "\u001b[1;31mIndexError\u001b[0m: single positional indexer is out-of-bounds",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[1;31mIndexError\u001b[0m                                Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[38], line 13\u001b[0m\n\u001b[0;32m     11\u001b[0m     codigos_dic[ciudad] \u001b[38;5;241m=\u001b[39m df_municipios\u001b[38;5;241m.\u001b[39mloc[filtro_exacto, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mCódigo INE\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m.\u001b[39miloc[\u001b[38;5;241m0\u001b[39m]\n\u001b[0;32m     12\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mIndexError\u001b[39;00m:\n\u001b[1;32m---> 13\u001b[0m     mun \u001b[38;5;241m=\u001b[39m df_municipios\u001b[38;5;241m.\u001b[39mloc[filtro_relativo, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mCódigo INE\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m.\u001b[39miloc[\u001b[38;5;241m0\u001b[39m]\n\u001b[0;32m     14\u001b[0m     nombre \u001b[38;5;241m=\u001b[39m df_municipios\u001b[38;5;241m.\u001b[39mloc[filtro_relativo, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mMunicipio\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m.\u001b[39miloc[\u001b[38;5;241m0\u001b[39m]\n\u001b[0;32m     15\u001b[0m     codigos_dic[ciudad] \u001b[38;5;241m=\u001b[39m mun\n",
      "File \u001b[1;32mc:\\anaconda_instalation\\envs\\spatial_data\\Lib\\site-packages\\pandas\\core\\indexing.py:1191\u001b[0m, in \u001b[0;36m_LocationIndexer.__getitem__\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   1189\u001b[0m maybe_callable \u001b[38;5;241m=\u001b[39m com\u001b[38;5;241m.\u001b[39mapply_if_callable(key, \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mobj)\n\u001b[0;32m   1190\u001b[0m maybe_callable \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_check_deprecated_callable_usage(key, maybe_callable)\n\u001b[1;32m-> 1191\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_getitem_axis(maybe_callable, axis\u001b[38;5;241m=\u001b[39maxis)\n",
      "File \u001b[1;32mc:\\anaconda_instalation\\envs\\spatial_data\\Lib\\site-packages\\pandas\\core\\indexing.py:1752\u001b[0m, in \u001b[0;36m_iLocIndexer._getitem_axis\u001b[1;34m(self, key, axis)\u001b[0m\n\u001b[0;32m   1749\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mTypeError\u001b[39;00m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mCannot index by location index with a non-integer key\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m   1751\u001b[0m \u001b[38;5;66;03m# validate the location\u001b[39;00m\n\u001b[1;32m-> 1752\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_validate_integer(key, axis)\n\u001b[0;32m   1754\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mobj\u001b[38;5;241m.\u001b[39m_ixs(key, axis\u001b[38;5;241m=\u001b[39maxis)\n",
      "File \u001b[1;32mc:\\anaconda_instalation\\envs\\spatial_data\\Lib\\site-packages\\pandas\\core\\indexing.py:1685\u001b[0m, in \u001b[0;36m_iLocIndexer._validate_integer\u001b[1;34m(self, key, axis)\u001b[0m\n\u001b[0;32m   1683\u001b[0m len_axis \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mlen\u001b[39m(\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mobj\u001b[38;5;241m.\u001b[39m_get_axis(axis))\n\u001b[0;32m   1684\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m key \u001b[38;5;241m>\u001b[39m\u001b[38;5;241m=\u001b[39m len_axis \u001b[38;5;129;01mor\u001b[39;00m key \u001b[38;5;241m<\u001b[39m \u001b[38;5;241m-\u001b[39mlen_axis:\n\u001b[1;32m-> 1685\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mIndexError\u001b[39;00m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124msingle positional indexer is out-of-bounds\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "\u001b[1;31mIndexError\u001b[0m: single positional indexer is out-of-bounds"
     ]
    }
   ],
   "source": [
    "## Buscar los nombres de ciudades en la columna \"municipios\"\n",
    "codigos_dic = {}\n",
    "saltar =  'municipio sin especificar'\n",
    "for ciudad in nombre_ciudades:\n",
    "    filtro_exacto = df_municipios['Municipio'] == ciudad\n",
    "    filtro_relativo = df_municipios['Municipio'].str.contains(ciudad, regex=True)\n",
    "    try:\n",
    "        print(f\"intentamos con {ciudad}\")\n",
    "        valor = df_municipios.loc[filtro_exacto, 'Municipio'].iloc[0]\n",
    "        print(f\"Asignaremos el codigo de {valor}\")\n",
    "        codigos_dic[ciudad] = df_municipios.loc[filtro_exacto, 'Código INE'].iloc[0]\n",
    "    except IndexError:\n",
    "        mun = df_municipios.loc[filtro_relativo, 'Código INE'].iloc[0]\n",
    "        nombre = df_municipios.loc[filtro_relativo, 'Municipio'].iloc[0]\n",
    "        codigos_dic[ciudad] = mun\n",
    "        print(f\"asigneremos {mun} que corresponde a {nombre}, porque ha fallado {ciudad} en el try\")\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "3bdfd7aa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "intentamos con Albacete\n",
      "Asignaremos el codigo de Albacete\n",
      "intentamos con Alcalá de Henares\n",
      "Asignaremos el codigo de Alcalá de Henares\n",
      "intentamos con Alcobendas\n",
      "Asignaremos el codigo de Alcobendas\n",
      "intentamos con Alcorcón\n",
      "Asignaremos el codigo de Alcorcón\n",
      "intentamos con Algeciras\n",
      "Asignaremos el codigo de Algeciras\n",
      "intentamos con Alicante/Alacant\n",
      "Asignaremos el codigo de Alicante/Alacant\n",
      "intentamos con Almería\n",
      "Asignaremos el codigo de Almería\n",
      "intentamos con Ávila\n",
      "Asignaremos el codigo de Ávila\n",
      "intentamos con Badajoz\n",
      "Asignaremos el codigo de Badajoz\n",
      "intentamos con Badalona\n",
      "Asignaremos el codigo de Badalona\n",
      "intentamos con Barcelona\n",
      "Asignaremos el codigo de Barcelona\n",
      "intentamos con Burgos\n",
      "Asignaremos el codigo de Burgos\n",
      "intentamos con Cáceres\n",
      "Asignaremos el codigo de Cáceres\n",
      "intentamos con Cádiz\n",
      "Asignaremos el codigo de Cádiz\n",
      "intentamos con Cartagena\n",
      "Asignaremos el codigo de Cartagena\n",
      "intentamos con Castella\n",
      "asigneremos 05061 que corresponde a Castellanos de Zapardiel, porque ha fallado Castella en el try\n",
      "intentamos con Ceuta\n",
      "Asignaremos el codigo de Ceuta\n",
      "intentamos con Ciudad Real\n",
      "Asignaremos el codigo de Ciudad Real\n",
      "intentamos con Córdoba\n",
      "Asignaremos el codigo de Córdoba\n",
      "intentamos con Coruña, A\n",
      "Asignaremos el codigo de Coruña, A\n",
      "intentamos con Cuenca\n",
      "Asignaremos el codigo de Cuenca\n",
      "intentamos con Dos Hermanas\n",
      "Asignaremos el codigo de Dos Hermanas\n",
      "intentamos con Elche/Elx\n",
      "Asignaremos el codigo de Elche/Elx\n",
      "intentamos con Fuenlabrada\n",
      "Asignaremos el codigo de Fuenlabrada\n",
      "intentamos con Getafe\n",
      "Asignaremos el codigo de Getafe\n",
      "intentamos con Gijón\n",
      "Asignaremos el codigo de Gijón\n",
      "intentamos con Girona\n",
      "Asignaremos el codigo de Girona\n",
      "intentamos con Granada\n",
      "Asignaremos el codigo de Granada\n",
      "intentamos con Guadalajara\n",
      "Asignaremos el codigo de Guadalajara\n",
      "intentamos con Hospitalet de Llobregat, L'\n",
      "Asignaremos el codigo de Hospitalet de Llobregat, L'\n",
      "intentamos con Huelva\n",
      "Asignaremos el codigo de Huelva\n",
      "intentamos con Huesca\n",
      "Asignaremos el codigo de Huesca\n",
      "intentamos con Jaén\n",
      "Asignaremos el codigo de Jaén\n",
      "intentamos con Jerez de la Frontera\n",
      "Asignaremos el codigo de Jerez de la Frontera\n",
      "intentamos con Laganes\n",
      "No se encontró una coincidencia para Laganes ni exacta ni relativa\n",
      "intentamos con Palmas de Gran Canaria, Las\n",
      "Asignaremos el codigo de Palmas de Gran Canaria, Las\n",
      "intentamos con León\n",
      "Asignaremos el codigo de León\n",
      "intentamos con Lleida\n",
      "Asignaremos el codigo de Lleida\n",
      "intentamos con Logroño\n",
      "Asignaremos el codigo de Logroño\n",
      "intentamos con Lugo\n",
      "Asignaremos el codigo de Lugo\n",
      "intentamos con Madrid\n",
      "Asignaremos el codigo de Madrid\n",
      "intentamos con Málaga\n",
      "Asignaremos el codigo de Málaga\n",
      "intentamos con Marbella\n",
      "Asignaremos el codigo de Marbella\n",
      "intentamos con Mataró\n",
      "Asignaremos el codigo de Mataró\n",
      "intentamos con Melilla\n",
      "Asignaremos el codigo de Melilla\n",
      "intentamos con Móstoles\n",
      "Asignaremos el codigo de Móstoles\n",
      "intentamos con Murcia\n",
      "Asignaremos el codigo de Murcia\n",
      "intentamos con Ourense\n",
      "Asignaremos el codigo de Ourense\n",
      "intentamos con Oviedo\n",
      "Asignaremos el codigo de Oviedo\n",
      "intentamos con Palencia\n",
      "Asignaremos el codigo de Palencia\n",
      "intentamos con Palma de Mallorca\n",
      "Asignaremos el codigo de Palma de Mallorca\n",
      "intentamos con Parla\n",
      "Asignaremos el codigo de Parla\n",
      "intentamos con Pontevedra\n",
      "Asignaremos el codigo de Pontevedra\n",
      "intentamos con Sabadell\n",
      "Asignaremos el codigo de Sabadell\n",
      "intentamos con Salamanca\n",
      "Asignaremos el codigo de Salamanca\n",
      "intentamos con Santander\n",
      "Asignaremos el codigo de Santander\n",
      "intentamos con Santa Coloma de Gramenet\n",
      "Asignaremos el codigo de Santa Coloma de Gramenet\n",
      "intentamos con Santa cruz\n",
      "No se encontró una coincidencia para Santa cruz ni exacta ni relativa\n",
      "intentamos con San Cristóbal de La Laguna\n",
      "Asignaremos el codigo de San Cristóbal de La Laguna\n",
      "intentamos con Segovia\n",
      "Asignaremos el codigo de Segovia\n",
      "intentamos con Sevilla\n",
      "Asignaremos el codigo de Sevilla\n",
      "intentamos con Soria\n",
      "Asignaremos el codigo de Soria\n",
      "intentamos con Tarragona\n",
      "Asignaremos el codigo de Tarragona\n",
      "intentamos con Terrassa\n",
      "Asignaremos el codigo de Terrassa\n",
      "intentamos con Teruel\n",
      "Asignaremos el codigo de Teruel\n",
      "intentamos con Toledo\n",
      "Asignaremos el codigo de Toledo\n",
      "intentamos con Torrejón de Ardoz\n",
      "Asignaremos el codigo de Torrejón de Ardoz\n",
      "intentamos con Valencia\n",
      "Asignaremos el codigo de Valencia\n",
      "intentamos con Valladolid\n",
      "Asignaremos el codigo de Valladolid\n",
      "intentamos con Vigo\n",
      "Asignaremos el codigo de Vigo\n",
      "intentamos con Zamora\n",
      "Asignaremos el codigo de Zamora\n",
      "intentamos con Zaragoza\n",
      "Asignaremos el codigo de Zaragoza\n"
     ]
    }
   ],
   "source": [
    "## Buscar los nombres de ciudades en la columna \"municipios\"\n",
    "codigos_dic = {}\n",
    "saltar =  'municipio sin especificar'\n",
    "\n",
    "for ciudad in nombre_ciudades:\n",
    "\n",
    "\n",
    "    if ciudad == saltar:\n",
    "        continue\n",
    "\n",
    "    # filtro exacto\n",
    "\n",
    "    filtro_exacto = df_municipios['Municipio'] == ciudad\n",
    "\n",
    "    filtro_relativo = df_municipios['Municipio'].str.contains(ciudad, regex=True)\n",
    "\n",
    "    try:\n",
    "        print(f\"intentamos con {ciudad}\")\n",
    "\n",
    "     # Buscamos coincidencia exacta\n",
    "\n",
    "        valor = df_municipios.loc[filtro_exacto, 'Municipio'].iloc[0]\n",
    "     \n",
    "        print(f\"Asignaremos el codigo de {valor}\")\n",
    "\n",
    "     # Asignamos el código INE a la ciudad\n",
    "\n",
    "        codigos_dic[ciudad] = df_municipios.loc[filtro_exacto, 'Código INE'].iloc[0]\n",
    "\n",
    "\n",
    "    except IndexError:\n",
    "    # Si no se encuentra una coincidencia exacta, intentamos una coincidencia parcial\n",
    "    \n",
    "        try:\n",
    "            mun = df_municipios.loc[filtro_relativo, 'Código INE'].iloc[0]\n",
    "            nombre = df_municipios.loc[filtro_relativo, 'Municipio'].iloc[0]\n",
    "            codigos_dic[ciudad] = mun\n",
    "            print(f\"asigneremos {mun} que corresponde a {nombre}, porque ha fallado {ciudad} en el try\")\n",
    "\n",
    "        except IndexError:\n",
    "            # Si tampoco encontramos coincidencia parcial, mostramos un mensaje de error\n",
    "            print(f\"No se encontró una coincidencia para {ciudad} ni exacta ni relativa\")\n",
    "            codigos_dic[ciudad] = None  # O decide qué hacer si no se encuentra\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "52e4996f-dcde-4e20-80cc-820f3be4b7ab",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "72"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(codigos_dic)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "5641ae0b-2048-43be-b1f3-0c0ef6c9594d",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Albacete': '02005',\n",
       " 'Alcalá de Henares': '28039',\n",
       " 'Alcobendas': '28040',\n",
       " 'Alcorcón': '28041',\n",
       " 'Algeciras': '11021',\n",
       " 'Alicante/Alacant': '03017',\n",
       " 'Almería': '04017',\n",
       " 'Ávila': '05025',\n",
       " 'Badajoz': '06021',\n",
       " 'Badalona': '08029',\n",
       " 'Barcelona': '08033',\n",
       " 'Burgos': '09075',\n",
       " 'Cáceres': '10053',\n",
       " 'Cádiz': '11029',\n",
       " 'Cartagena': '31007',\n",
       " 'Castella': '05061',\n",
       " 'Ceuta': '51001',\n",
       " 'Ciudad Real': '13053',\n",
       " 'Córdoba': '14041',\n",
       " 'Coruña, A': '15051',\n",
       " 'Cuenca': '16102',\n",
       " 'Dos Hermanas': '41094',\n",
       " 'Elche/Elx': '03068',\n",
       " 'Fuenlabrada': '28094',\n",
       " 'Getafe': '28102',\n",
       " 'Gijón': '33063',\n",
       " 'Girona': '17102',\n",
       " 'Granada': '18117',\n",
       " 'Guadalajara': '19162',\n",
       " \"Hospitalet de Llobregat, L'\": '08115',\n",
       " 'Huelva': '21068',\n",
       " 'Huesca': '22167',\n",
       " 'Jaén': '23081',\n",
       " 'Jerez de la Frontera': '11037',\n",
       " 'Laganes': None,\n",
       " 'Palmas de Gran Canaria, Las': '36025',\n",
       " 'León': '24120',\n",
       " 'Lleida': '25153',\n",
       " 'Logroño': '26124',\n",
       " 'Lugo': '27062',\n",
       " 'Madrid': '28117',\n",
       " 'Málaga': '29903',\n",
       " 'Marbella': '30001',\n",
       " 'Mataró': '08135',\n",
       " 'Melilla': '52001',\n",
       " 'Móstoles': '28129',\n",
       " 'Murcia': '31021',\n",
       " 'Ourense': '32092',\n",
       " 'Oviedo': '34006',\n",
       " 'Palencia': '34171',\n",
       " 'Palma de Mallorca': '07047',\n",
       " 'Parla': '28141',\n",
       " 'Pontevedra': '37022',\n",
       " 'Sabadell': '08201',\n",
       " 'Salamanca': '37324',\n",
       " 'Santander': '40030',\n",
       " 'Santa Coloma de Gramenet': '08259',\n",
       " 'Santa cruz': None,\n",
       " 'San Cristóbal de La Laguna': '39018',\n",
       " 'Segovia': '41015',\n",
       " 'Sevilla': '42044',\n",
       " 'Soria': '43018',\n",
       " 'Tarragona': '44022',\n",
       " 'Terrassa': '08293',\n",
       " 'Teruel': '45014',\n",
       " 'Toledo': '46023',\n",
       " 'Torrejón de Ardoz': '28182',\n",
       " 'Valencia': '47045',\n",
       " 'Valladolid': '48018',\n",
       " 'Vigo': '37041',\n",
       " 'Zamora': '50065',\n",
       " 'Zaragoza': '50297'}"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "codigos_dic"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6ccc1c6",
   "metadata": {},
   "source": [
    "Transformamos el diccionario a un dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "e712398d-31b6-4ece-a69b-a04f2047ecee",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "\n",
    "codigos_INE = pd.DataFrame(codigos_dic, index = [0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "d2d3033f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Albacete</th>\n",
       "      <th>Alcalá de Henares</th>\n",
       "      <th>Alcobendas</th>\n",
       "      <th>Alcorcón</th>\n",
       "      <th>Algeciras</th>\n",
       "      <th>Alicante/Alacant</th>\n",
       "      <th>Almería</th>\n",
       "      <th>Ávila</th>\n",
       "      <th>Badajoz</th>\n",
       "      <th>Badalona</th>\n",
       "      <th>...</th>\n",
       "      <th>Tarragona</th>\n",
       "      <th>Terrassa</th>\n",
       "      <th>Teruel</th>\n",
       "      <th>Toledo</th>\n",
       "      <th>Torrejón de Ardoz</th>\n",
       "      <th>Valencia</th>\n",
       "      <th>Valladolid</th>\n",
       "      <th>Vigo</th>\n",
       "      <th>Zamora</th>\n",
       "      <th>Zaragoza</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02005</td>\n",
       "      <td>28039</td>\n",
       "      <td>28040</td>\n",
       "      <td>28041</td>\n",
       "      <td>11021</td>\n",
       "      <td>03017</td>\n",
       "      <td>04017</td>\n",
       "      <td>05025</td>\n",
       "      <td>06021</td>\n",
       "      <td>08029</td>\n",
       "      <td>...</td>\n",
       "      <td>44022</td>\n",
       "      <td>08293</td>\n",
       "      <td>45014</td>\n",
       "      <td>46023</td>\n",
       "      <td>28182</td>\n",
       "      <td>47045</td>\n",
       "      <td>48018</td>\n",
       "      <td>37041</td>\n",
       "      <td>50065</td>\n",
       "      <td>50297</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 72 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Albacete Alcalá de Henares Alcobendas Alcorcón Algeciras Alicante/Alacant  \\\n",
       "0    02005             28039      28040    28041     11021            03017   \n",
       "\n",
       "  Almería  Ávila Badajoz Badalona  ... Tarragona Terrassa Teruel Toledo  \\\n",
       "0   04017  05025   06021    08029  ...     44022    08293  45014  46023   \n",
       "\n",
       "  Torrejón de Ardoz Valencia Valladolid   Vigo Zamora Zaragoza  \n",
       "0             28182    47045      48018  37041  50065    50297  \n",
       "\n",
       "[1 rows x 72 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "codigos_INE"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a5b9eca",
   "metadata": {},
   "source": [
    "Exportamos los datos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "a7057d0b-bab0-4170-8c47-2f95e4670dfc",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "codigos_INE.to_csv('codigos_INE.csv', encoding = \"ISO-8859-1\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8d175f3",
   "metadata": {},
   "source": [
    "Se transforman los datos de formato wide a long usando melt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "5c1c7546-941d-4390-8a8b-83af492ef065",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "codigos_INE_long = codigos_INE.melt(value_vars= codigos_INE.columns, var_name = \"Ciudad\", value_name = 'COD')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "4327d0a7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Ciudad</th>\n",
       "      <th>COD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albacete</td>\n",
       "      <td>02005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alcalá de Henares</td>\n",
       "      <td>28039</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alcobendas</td>\n",
       "      <td>28040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alcorcón</td>\n",
       "      <td>28041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Algeciras</td>\n",
       "      <td>11021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Ciudad    COD\n",
       "0           Albacete  02005\n",
       "1  Alcalá de Henares  28039\n",
       "2         Alcobendas  28040\n",
       "3           Alcorcón  28041\n",
       "4          Algeciras  11021"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "codigos_INE_long.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25419fe3",
   "metadata": {},
   "source": [
    "Exportamos el último dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "17db15de-d0e7-414d-a58d-a728c57dcb10",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "codigos_INE_long.to_csv('codigos_INE_long_final.csv', encoding = 'ISO-8859-1')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a01c8d3f",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "49b42faa-05f9-4721-9690-c964b14456b5",
   "metadata": {},
   "source": [
    "## PARTE II: Vinculamos el código INE de las ciudades utilizadas para seleecionar desde la información a nivel de municipio."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3f7c2e7-d825-4a34-9689-a5c10a2b65c2",
   "metadata": {},
   "source": [
    "Se cargan los código INE desde el archivo generado\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "b778566a-3316-4c52-905d-af857b3bf779",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "codigos_INE_long = pd.read_csv(\"codigos_INE_long_final.csv\", encoding = 'ISO-8859-1', dtype ={\"COD\":str})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "02323f91-482e-42e2-947e-bc3cc2e0bf79",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Albacete</th>\n",
       "      <th>Alcalá de Henares</th>\n",
       "      <th>Alcobendas</th>\n",
       "      <th>Alcorcón</th>\n",
       "      <th>Algeciras</th>\n",
       "      <th>Alicante/Alacant</th>\n",
       "      <th>Almería</th>\n",
       "      <th>Ávila</th>\n",
       "      <th>Badajoz</th>\n",
       "      <th>Badalona</th>\n",
       "      <th>...</th>\n",
       "      <th>Tarragona</th>\n",
       "      <th>Terrassa</th>\n",
       "      <th>Teruel</th>\n",
       "      <th>Toledo</th>\n",
       "      <th>Torrejón de Ardoz</th>\n",
       "      <th>Valencia</th>\n",
       "      <th>Valladolid</th>\n",
       "      <th>Vigo</th>\n",
       "      <th>Zamora</th>\n",
       "      <th>Zaragoza</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02005</td>\n",
       "      <td>28039</td>\n",
       "      <td>28040</td>\n",
       "      <td>28041</td>\n",
       "      <td>11021</td>\n",
       "      <td>03017</td>\n",
       "      <td>04017</td>\n",
       "      <td>05025</td>\n",
       "      <td>06021</td>\n",
       "      <td>08029</td>\n",
       "      <td>...</td>\n",
       "      <td>44022</td>\n",
       "      <td>08293</td>\n",
       "      <td>45014</td>\n",
       "      <td>46023</td>\n",
       "      <td>28182</td>\n",
       "      <td>47045</td>\n",
       "      <td>48018</td>\n",
       "      <td>37041</td>\n",
       "      <td>50065</td>\n",
       "      <td>50297</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 72 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Albacete Alcalá de Henares Alcobendas Alcorcón Algeciras Alicante/Alacant  \\\n",
       "0    02005             28039      28040    28041     11021            03017   \n",
       "\n",
       "  Almería  Ávila Badajoz Badalona  ... Tarragona Terrassa Teruel Toledo  \\\n",
       "0   04017  05025   06021    08029  ...     44022    08293  45014  46023   \n",
       "\n",
       "  Torrejón de Ardoz Valencia Valladolid   Vigo Zamora Zaragoza  \n",
       "0             28182    47045      48018  37041  50065    50297  \n",
       "\n",
       "[1 rows x 72 columns]"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "codigos_INE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "126ef2ed-9825-4cbf-82e8-50998f0648e6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Unnamed: 0     int64\n",
       "Ciudad        object\n",
       "COD           object\n",
       "dtype: object"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "codigos_INE_long.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b93edb7-a26f-447f-b576-49b0e7f7b38d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "aebfe441-c3e7-4c6e-9cc4-8fc5deef590b",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "72"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows_to_filter = codigos_INE_long.COD\n",
    "len(rows_to_filter)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "1f474308-d999-401e-82d4-41d5bdd2ee1d",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0              Albacete\n",
       "1     Alcalá de Henares\n",
       "2            Alcobendas\n",
       "3              Alcorcón\n",
       "4             Algeciras\n",
       "            ...        \n",
       "67             Valencia\n",
       "68           Valladolid\n",
       "69                 Vigo\n",
       "70               Zamora\n",
       "71             Zaragoza\n",
       "Name: Ciudad, Length: 72, dtype: object"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows_to_filter1 = codigos_INE_long.Ciudad\n",
    "rows_to_filter1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "aa8cf9f2-84e8-4a62-95ee-d87315bbb452",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "\"None of ['Código INE'] are in the columns\"",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp\\ipykernel_22352\\633580669.py\u001b[0m in \u001b[0;36m?\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdf_municipios\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf_municipios\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mset_index\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'Código INE'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32mc:\\anaconda_instalation\\envs\\spatial_data\\Lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36m?\u001b[1;34m(self, keys, drop, append, inplace, verify_integrity)\u001b[0m\n\u001b[0;32m   6118\u001b[0m                     \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mfound\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   6119\u001b[0m                         \u001b[0mmissing\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcol\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   6120\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   6121\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mmissing\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 6122\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33mf\"\u001b[0m\u001b[1;33mNone of \u001b[0m\u001b[1;33m{\u001b[0m\u001b[0mmissing\u001b[0m\u001b[1;33m}\u001b[0m\u001b[1;33m are in the columns\u001b[0m\u001b[1;33m\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   6123\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   6124\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0minplace\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   6125\u001b[0m             \u001b[0mframe\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: \"None of ['Código INE'] are in the columns\""
     ]
    }
   ],
   "source": [
    "df_municipios = df_municipios.set_index('Código INE')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "30c14619",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Municipio</th>\n",
       "      <th>Provincia</th>\n",
       "      <th>Comunidad Autónoma</th>\n",
       "      <th>Población Total</th>\n",
       "      <th>Población Hombres</th>\n",
       "      <th>Población Mujeres</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Conductores Hombres</th>\n",
       "      <th>Conductoras Mujeres</th>\n",
       "      <th>Censo Conductores</th>\n",
       "      <th>...</th>\n",
       "      <th>Alcohol (6 puntos)</th>\n",
       "      <th>Drogas (6 puntos)</th>\n",
       "      <th>Casco, Cinturón, SRI (3 puntos)</th>\n",
       "      <th>Móvil (3 puntos)</th>\n",
       "      <th>Semáforo (4 puntos)</th>\n",
       "      <th>Otra (3 puntos)</th>\n",
       "      <th>Otra (4 puntos)</th>\n",
       "      <th>Otra (6 puntos)</th>\n",
       "      <th>Unnamed: 95</th>\n",
       "      <th>Campañas</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Código INE</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>01001</th>\n",
       "      <td>Alava ( municipio sin especificar)</td>\n",
       "      <td>Araba/Álava</td>\n",
       "      <td>País Vasco</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01002</th>\n",
       "      <td>Alegría-Dulantzi</td>\n",
       "      <td>Araba/Álava</td>\n",
       "      <td>País Vasco</td>\n",
       "      <td>2935.0</td>\n",
       "      <td>1531.0</td>\n",
       "      <td>1404.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1038.0</td>\n",
       "      <td>818.0</td>\n",
       "      <td>1856.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01003</th>\n",
       "      <td>Amurrio</td>\n",
       "      <td>Araba/Álava</td>\n",
       "      <td>País Vasco</td>\n",
       "      <td>10264.0</td>\n",
       "      <td>5044.0</td>\n",
       "      <td>5220.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3664.0</td>\n",
       "      <td>2660.0</td>\n",
       "      <td>6324.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01004</th>\n",
       "      <td>Aramaio</td>\n",
       "      <td>Araba/Álava</td>\n",
       "      <td>País Vasco</td>\n",
       "      <td>1442.0</td>\n",
       "      <td>748.0</td>\n",
       "      <td>694.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>566.0</td>\n",
       "      <td>425.0</td>\n",
       "      <td>991.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01006</th>\n",
       "      <td>Artziniega</td>\n",
       "      <td>Araba/Álava</td>\n",
       "      <td>País Vasco</td>\n",
       "      <td>1800.0</td>\n",
       "      <td>892.0</td>\n",
       "      <td>908.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>630.0</td>\n",
       "      <td>490.0</td>\n",
       "      <td>1120.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50901</th>\n",
       "      <td>Biel</td>\n",
       "      <td>Zaragoza</td>\n",
       "      <td>Aragón</td>\n",
       "      <td>155.0</td>\n",
       "      <td>97.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>39.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50902</th>\n",
       "      <td>Marracos</td>\n",
       "      <td>Zaragoza</td>\n",
       "      <td>Aragón</td>\n",
       "      <td>84.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50903</th>\n",
       "      <td>Villamayor de Gállego</td>\n",
       "      <td>Zaragoza</td>\n",
       "      <td>Aragón</td>\n",
       "      <td>2745.0</td>\n",
       "      <td>1375.0</td>\n",
       "      <td>1370.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>627.0</td>\n",
       "      <td>430.0</td>\n",
       "      <td>1057.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51001</th>\n",
       "      <td>Ceuta</td>\n",
       "      <td>Ceuta</td>\n",
       "      <td>Ceuta</td>\n",
       "      <td>84202.0</td>\n",
       "      <td>42542.0</td>\n",
       "      <td>41660.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25274.0</td>\n",
       "      <td>16376.0</td>\n",
       "      <td>41650.0</td>\n",
       "      <td>...</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>118.0</td>\n",
       "      <td>378.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52001</th>\n",
       "      <td>Melilla</td>\n",
       "      <td>Melilla</td>\n",
       "      <td>Melilla</td>\n",
       "      <td>87076.0</td>\n",
       "      <td>44162.0</td>\n",
       "      <td>42914.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26521.0</td>\n",
       "      <td>18053.0</td>\n",
       "      <td>44574.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>221.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66.0</td>\n",
       "      <td>843.0</td>\n",
       "      <td>149.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8196 rows × 96 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     Municipio    Provincia  \\\n",
       "Código INE                                                    \n",
       "01001       Alava ( municipio sin especificar)  Araba/Álava   \n",
       "01002                         Alegría-Dulantzi  Araba/Álava   \n",
       "01003                                  Amurrio  Araba/Álava   \n",
       "01004                                  Aramaio  Araba/Álava   \n",
       "01006                               Artziniega  Araba/Álava   \n",
       "...                                        ...          ...   \n",
       "50901                                     Biel     Zaragoza   \n",
       "50902                                 Marracos     Zaragoza   \n",
       "50903                    Villamayor de Gállego     Zaragoza   \n",
       "51001                                    Ceuta        Ceuta   \n",
       "52001                                  Melilla      Melilla   \n",
       "\n",
       "           Comunidad Autónoma  Población Total  Población Hombres  \\\n",
       "Código INE                                                          \n",
       "01001              País Vasco              NaN                NaN   \n",
       "01002              País Vasco           2935.0             1531.0   \n",
       "01003              País Vasco          10264.0             5044.0   \n",
       "01004              País Vasco           1442.0              748.0   \n",
       "01006              País Vasco           1800.0              892.0   \n",
       "...                       ...              ...                ...   \n",
       "50901                  Aragón            155.0               97.0   \n",
       "50902                  Aragón             84.0               46.0   \n",
       "50903                  Aragón           2745.0             1375.0   \n",
       "51001                   Ceuta          84202.0            42542.0   \n",
       "52001                 Melilla          87076.0            44162.0   \n",
       "\n",
       "            Población Mujeres  Unnamed: 7  Conductores Hombres  \\\n",
       "Código INE                                                       \n",
       "01001                     NaN         NaN                  1.0   \n",
       "01002                  1404.0         NaN               1038.0   \n",
       "01003                  5220.0         NaN               3664.0   \n",
       "01004                   694.0         NaN                566.0   \n",
       "01006                   908.0         NaN                630.0   \n",
       "...                       ...         ...                  ...   \n",
       "50901                    58.0         NaN                 39.0   \n",
       "50902                    38.0         NaN                 20.0   \n",
       "50903                  1370.0         NaN                627.0   \n",
       "51001                 41660.0         NaN              25274.0   \n",
       "52001                 42914.0         NaN              26521.0   \n",
       "\n",
       "            Conductoras Mujeres  Censo Conductores  ...  Alcohol (6 puntos)  \\\n",
       "Código INE                                          ...                       \n",
       "01001                       0.0                1.0  ...                 NaN   \n",
       "01002                     818.0             1856.0  ...                 NaN   \n",
       "01003                    2660.0             6324.0  ...                 NaN   \n",
       "01004                     425.0              991.0  ...                 NaN   \n",
       "01006                     490.0             1120.0  ...                 NaN   \n",
       "...                         ...                ...  ...                 ...   \n",
       "50901                      15.0               54.0  ...                 NaN   \n",
       "50902                       7.0               27.0  ...                 NaN   \n",
       "50903                     430.0             1057.0  ...                 NaN   \n",
       "51001                   16376.0            41650.0  ...                10.0   \n",
       "52001                   18053.0            44574.0  ...                 3.0   \n",
       "\n",
       "            Drogas (6 puntos)  Casco, Cinturón, SRI (3 puntos)  \\\n",
       "Código INE                                                       \n",
       "01001                     NaN                              NaN   \n",
       "01002                     NaN                              NaN   \n",
       "01003                     NaN                              NaN   \n",
       "01004                     NaN                              NaN   \n",
       "01006                     NaN                              NaN   \n",
       "...                       ...                              ...   \n",
       "50901                     NaN                              NaN   \n",
       "50902                     NaN                              NaN   \n",
       "50903                     NaN                              NaN   \n",
       "51001                     NaN                            118.0   \n",
       "52001                     NaN                            221.0   \n",
       "\n",
       "            Móvil (3 puntos)  Semáforo (4 puntos)  Otra (3 puntos)  \\\n",
       "Código INE                                                           \n",
       "01001                    NaN                  NaN              NaN   \n",
       "01002                    NaN                  NaN              NaN   \n",
       "01003                    NaN                  NaN              NaN   \n",
       "01004                    NaN                  NaN              NaN   \n",
       "01006                    NaN                  NaN              NaN   \n",
       "...                      ...                  ...              ...   \n",
       "50901                    NaN                  NaN              NaN   \n",
       "50902                    NaN                  NaN              NaN   \n",
       "50903                    NaN                  NaN              NaN   \n",
       "51001                  378.0                 34.0             51.0   \n",
       "52001                    NaN                 66.0            843.0   \n",
       "\n",
       "            Otra (4 puntos)  Otra (6 puntos)  Unnamed: 95  Campañas  \n",
       "Código INE                                                           \n",
       "01001                   NaN              NaN          NaN       0.0  \n",
       "01002                   NaN              NaN          NaN       0.0  \n",
       "01003                   NaN              NaN          NaN       0.0  \n",
       "01004                   NaN              NaN          NaN       0.0  \n",
       "01006                   NaN              NaN          NaN       0.0  \n",
       "...                     ...              ...          ...       ...  \n",
       "50901                   NaN              NaN          NaN       NaN  \n",
       "50902                   NaN              NaN          NaN       NaN  \n",
       "50903                   NaN              NaN          NaN       NaN  \n",
       "51001                  88.0             51.0          NaN       NaN  \n",
       "52001                 149.0             82.0          NaN       NaN  \n",
       "\n",
       "[8196 rows x 96 columns]"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_municipios"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "616e8aba",
   "metadata": {},
   "source": [
    "Vamos a usar la serie rows_to_filter para filtrar el dataframe usando los índices\n",
    "que contienen el código INE."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "c31a5272",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Municipio</th>\n",
       "      <th>Provincia</th>\n",
       "      <th>Comunidad Autónoma</th>\n",
       "      <th>Población Total</th>\n",
       "      <th>Población Hombres</th>\n",
       "      <th>Población Mujeres</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Conductores Hombres</th>\n",
       "      <th>Conductoras Mujeres</th>\n",
       "      <th>Censo Conductores</th>\n",
       "      <th>...</th>\n",
       "      <th>Alcohol (6 puntos)</th>\n",
       "      <th>Drogas (6 puntos)</th>\n",
       "      <th>Casco, Cinturón, SRI (3 puntos)</th>\n",
       "      <th>Móvil (3 puntos)</th>\n",
       "      <th>Semáforo (4 puntos)</th>\n",
       "      <th>Otra (3 puntos)</th>\n",
       "      <th>Otra (4 puntos)</th>\n",
       "      <th>Otra (6 puntos)</th>\n",
       "      <th>Unnamed: 95</th>\n",
       "      <th>Campañas</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Código INE</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>01001</th>\n",
       "      <td>Alava ( municipio sin especificar)</td>\n",
       "      <td>Araba/Álava</td>\n",
       "      <td>País Vasco</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01002</th>\n",
       "      <td>Alegría-Dulantzi</td>\n",
       "      <td>Araba/Álava</td>\n",
       "      <td>País Vasco</td>\n",
       "      <td>2935.0</td>\n",
       "      <td>1531.0</td>\n",
       "      <td>1404.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1038.0</td>\n",
       "      <td>818.0</td>\n",
       "      <td>1856.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01003</th>\n",
       "      <td>Amurrio</td>\n",
       "      <td>Araba/Álava</td>\n",
       "      <td>País Vasco</td>\n",
       "      <td>10264.0</td>\n",
       "      <td>5044.0</td>\n",
       "      <td>5220.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3664.0</td>\n",
       "      <td>2660.0</td>\n",
       "      <td>6324.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 96 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     Municipio    Provincia  \\\n",
       "Código INE                                                    \n",
       "01001       Alava ( municipio sin especificar)  Araba/Álava   \n",
       "01002                         Alegría-Dulantzi  Araba/Álava   \n",
       "01003                                  Amurrio  Araba/Álava   \n",
       "\n",
       "           Comunidad Autónoma  Población Total  Población Hombres  \\\n",
       "Código INE                                                          \n",
       "01001              País Vasco              NaN                NaN   \n",
       "01002              País Vasco           2935.0             1531.0   \n",
       "01003              País Vasco          10264.0             5044.0   \n",
       "\n",
       "            Población Mujeres  Unnamed: 7  Conductores Hombres  \\\n",
       "Código INE                                                       \n",
       "01001                     NaN         NaN                  1.0   \n",
       "01002                  1404.0         NaN               1038.0   \n",
       "01003                  5220.0         NaN               3664.0   \n",
       "\n",
       "            Conductoras Mujeres  Censo Conductores  ...  Alcohol (6 puntos)  \\\n",
       "Código INE                                          ...                       \n",
       "01001                       0.0                1.0  ...                 NaN   \n",
       "01002                     818.0             1856.0  ...                 NaN   \n",
       "01003                    2660.0             6324.0  ...                 NaN   \n",
       "\n",
       "            Drogas (6 puntos)  Casco, Cinturón, SRI (3 puntos)  \\\n",
       "Código INE                                                       \n",
       "01001                     NaN                              NaN   \n",
       "01002                     NaN                              NaN   \n",
       "01003                     NaN                              NaN   \n",
       "\n",
       "            Móvil (3 puntos)  Semáforo (4 puntos)  Otra (3 puntos)  \\\n",
       "Código INE                                                           \n",
       "01001                    NaN                  NaN              NaN   \n",
       "01002                    NaN                  NaN              NaN   \n",
       "01003                    NaN                  NaN              NaN   \n",
       "\n",
       "            Otra (4 puntos)  Otra (6 puntos)  Unnamed: 95  Campañas  \n",
       "Código INE                                                           \n",
       "01001                   NaN              NaN          NaN       0.0  \n",
       "01002                   NaN              NaN          NaN       0.0  \n",
       "01003                   NaN              NaN          NaN       0.0  \n",
       "\n",
       "[3 rows x 96 columns]"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_municipios.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "39386236-adaa-4a66-b99c-f72f56ebe6b2",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "filtered_df = df_municipios.loc[df_municipios.index.isin(rows_to_filter)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "6853a414-4a67-49c6-bff8-58432ea0a0f4",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "73"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(filtered_df.Municipio)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "282e32df",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Municipio</th>\n",
       "      <th>Provincia</th>\n",
       "      <th>Comunidad Autónoma</th>\n",
       "      <th>Población Total</th>\n",
       "      <th>Población Hombres</th>\n",
       "      <th>Población Mujeres</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Conductores Hombres</th>\n",
       "      <th>Conductoras Mujeres</th>\n",
       "      <th>Censo Conductores</th>\n",
       "      <th>...</th>\n",
       "      <th>Alcohol (6 puntos)</th>\n",
       "      <th>Drogas (6 puntos)</th>\n",
       "      <th>Casco, Cinturón, SRI (3 puntos)</th>\n",
       "      <th>Móvil (3 puntos)</th>\n",
       "      <th>Semáforo (4 puntos)</th>\n",
       "      <th>Otra (3 puntos)</th>\n",
       "      <th>Otra (4 puntos)</th>\n",
       "      <th>Otra (6 puntos)</th>\n",
       "      <th>Unnamed: 95</th>\n",
       "      <th>Campañas</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Código INE</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>02005</th>\n",
       "      <td>Albacete</td>\n",
       "      <td>Albacete</td>\n",
       "      <td>Castilla-La Mancha</td>\n",
       "      <td>174336.0</td>\n",
       "      <td>85214.0</td>\n",
       "      <td>89122.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59160.0</td>\n",
       "      <td>45441.0</td>\n",
       "      <td>104601.0</td>\n",
       "      <td>...</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>302.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>94.0</td>\n",
       "      <td>337.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>03017</th>\n",
       "      <td>Alicante/Alacant</td>\n",
       "      <td>Alicante/Alacant</td>\n",
       "      <td>Comunitat Valenciana</td>\n",
       "      <td>337482.0</td>\n",
       "      <td>163385.0</td>\n",
       "      <td>174097.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104997.0</td>\n",
       "      <td>80448.0</td>\n",
       "      <td>185445.0</td>\n",
       "      <td>...</td>\n",
       "      <td>109.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>295.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>254.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>03068</th>\n",
       "      <td>Elche/Elx</td>\n",
       "      <td>Alicante/Alacant</td>\n",
       "      <td>Comunitat Valenciana</td>\n",
       "      <td>234765.0</td>\n",
       "      <td>116386.0</td>\n",
       "      <td>118379.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>79269.0</td>\n",
       "      <td>60315.0</td>\n",
       "      <td>139584.0</td>\n",
       "      <td>...</td>\n",
       "      <td>113.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>199.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>295.0</td>\n",
       "      <td>386.0</td>\n",
       "      <td>134.0</td>\n",
       "      <td>345.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>04017</th>\n",
       "      <td>Almería</td>\n",
       "      <td>Almería</td>\n",
       "      <td>Andalucía</td>\n",
       "      <td>201322.0</td>\n",
       "      <td>98531.0</td>\n",
       "      <td>102791.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>65771.0</td>\n",
       "      <td>48869.0</td>\n",
       "      <td>114640.0</td>\n",
       "      <td>...</td>\n",
       "      <td>17.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>147.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>201.0</td>\n",
       "      <td>384.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05025</th>\n",
       "      <td>Ávila</td>\n",
       "      <td>Ávila</td>\n",
       "      <td>Castilla y León</td>\n",
       "      <td>58369.0</td>\n",
       "      <td>27784.0</td>\n",
       "      <td>30585.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20703.0</td>\n",
       "      <td>16973.0</td>\n",
       "      <td>37676.0</td>\n",
       "      <td>...</td>\n",
       "      <td>17.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>159.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>242.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51001</th>\n",
       "      <td>Santa Cruz de Moncayo</td>\n",
       "      <td>Zaragoza</td>\n",
       "      <td>Aragón</td>\n",
       "      <td>123.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52001</th>\n",
       "      <td>Santa Eulalia de Gállego</td>\n",
       "      <td>Zaragoza</td>\n",
       "      <td>Aragón</td>\n",
       "      <td>97.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50297</th>\n",
       "      <td>Zaragoza</td>\n",
       "      <td>Zaragoza</td>\n",
       "      <td>Aragón</td>\n",
       "      <td>681877.0</td>\n",
       "      <td>326103.0</td>\n",
       "      <td>355774.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>217389.0</td>\n",
       "      <td>148265.0</td>\n",
       "      <td>365654.0</td>\n",
       "      <td>...</td>\n",
       "      <td>146.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>189.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>908.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>196.0</td>\n",
       "      <td>125.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51001</th>\n",
       "      <td>Ceuta</td>\n",
       "      <td>Ceuta</td>\n",
       "      <td>Ceuta</td>\n",
       "      <td>84202.0</td>\n",
       "      <td>42542.0</td>\n",
       "      <td>41660.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25274.0</td>\n",
       "      <td>16376.0</td>\n",
       "      <td>41650.0</td>\n",
       "      <td>...</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>118.0</td>\n",
       "      <td>378.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52001</th>\n",
       "      <td>Melilla</td>\n",
       "      <td>Melilla</td>\n",
       "      <td>Melilla</td>\n",
       "      <td>87076.0</td>\n",
       "      <td>44162.0</td>\n",
       "      <td>42914.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26521.0</td>\n",
       "      <td>18053.0</td>\n",
       "      <td>44574.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>221.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66.0</td>\n",
       "      <td>843.0</td>\n",
       "      <td>149.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>73 rows × 96 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           Municipio         Provincia    Comunidad Autónoma  \\\n",
       "Código INE                                                                     \n",
       "02005                       Albacete          Albacete    Castilla-La Mancha   \n",
       "03017               Alicante/Alacant  Alicante/Alacant  Comunitat Valenciana   \n",
       "03068                      Elche/Elx  Alicante/Alacant  Comunitat Valenciana   \n",
       "04017                        Almería           Almería             Andalucía   \n",
       "05025                          Ávila             Ávila       Castilla y León   \n",
       "...                              ...               ...                   ...   \n",
       "51001          Santa Cruz de Moncayo          Zaragoza                Aragón   \n",
       "52001       Santa Eulalia de Gállego          Zaragoza                Aragón   \n",
       "50297                       Zaragoza          Zaragoza                Aragón   \n",
       "51001                          Ceuta             Ceuta                 Ceuta   \n",
       "52001                        Melilla           Melilla               Melilla   \n",
       "\n",
       "            Población Total  Población Hombres  Población Mujeres  Unnamed: 7  \\\n",
       "Código INE                                                                      \n",
       "02005              174336.0            85214.0            89122.0         NaN   \n",
       "03017              337482.0           163385.0           174097.0         NaN   \n",
       "03068              234765.0           116386.0           118379.0         NaN   \n",
       "04017              201322.0            98531.0           102791.0         NaN   \n",
       "05025               58369.0            27784.0            30585.0         NaN   \n",
       "...                     ...                ...                ...         ...   \n",
       "51001                 123.0               67.0               56.0         NaN   \n",
       "52001                  97.0               55.0               42.0         NaN   \n",
       "50297              681877.0           326103.0           355774.0         NaN   \n",
       "51001               84202.0            42542.0            41660.0         NaN   \n",
       "52001               87076.0            44162.0            42914.0         NaN   \n",
       "\n",
       "            Conductores Hombres  Conductoras Mujeres  Censo Conductores  ...  \\\n",
       "Código INE                                                               ...   \n",
       "02005                   59160.0              45441.0           104601.0  ...   \n",
       "03017                  104997.0              80448.0           185445.0  ...   \n",
       "03068                   79269.0              60315.0           139584.0  ...   \n",
       "04017                   65771.0              48869.0           114640.0  ...   \n",
       "05025                   20703.0              16973.0            37676.0  ...   \n",
       "...                         ...                  ...                ...  ...   \n",
       "51001                      44.0                 30.0               74.0  ...   \n",
       "52001                      37.0                 16.0               53.0  ...   \n",
       "50297                  217389.0             148265.0           365654.0  ...   \n",
       "51001                   25274.0              16376.0            41650.0  ...   \n",
       "52001                   26521.0              18053.0            44574.0  ...   \n",
       "\n",
       "            Alcohol (6 puntos)  Drogas (6 puntos)  \\\n",
       "Código INE                                          \n",
       "02005                     58.0                NaN   \n",
       "03017                    109.0                NaN   \n",
       "03068                    113.0                NaN   \n",
       "04017                     17.0                NaN   \n",
       "05025                     17.0                NaN   \n",
       "...                        ...                ...   \n",
       "51001                      NaN                NaN   \n",
       "52001                      NaN                NaN   \n",
       "50297                    146.0                NaN   \n",
       "51001                     10.0                NaN   \n",
       "52001                      3.0                NaN   \n",
       "\n",
       "            Casco, Cinturón, SRI (3 puntos)  Móvil (3 puntos)  \\\n",
       "Código INE                                                      \n",
       "02005                                 302.0               NaN   \n",
       "03017                                 295.0               1.0   \n",
       "03068                                 199.0               NaN   \n",
       "04017                                 147.0               NaN   \n",
       "05025                                 159.0               NaN   \n",
       "...                                     ...               ...   \n",
       "51001                                   NaN               NaN   \n",
       "52001                                   NaN               NaN   \n",
       "50297                                 189.0               NaN   \n",
       "51001                                 118.0             378.0   \n",
       "52001                                 221.0               NaN   \n",
       "\n",
       "            Semáforo (4 puntos)  Otra (3 puntos)  Otra (4 puntos)  \\\n",
       "Código INE                                                          \n",
       "02005                      94.0            337.0             46.0   \n",
       "03017                     254.0             27.0            120.0   \n",
       "03068                     295.0            386.0            134.0   \n",
       "04017                     201.0            384.0             76.0   \n",
       "05025                       5.0            242.0             39.0   \n",
       "...                         ...              ...              ...   \n",
       "51001                       NaN              NaN              NaN   \n",
       "52001                       NaN              NaN              NaN   \n",
       "50297                     908.0           1059.0            196.0   \n",
       "51001                      34.0             51.0             88.0   \n",
       "52001                      66.0            843.0            149.0   \n",
       "\n",
       "            Otra (6 puntos)  Unnamed: 95  Campañas  \n",
       "Código INE                                          \n",
       "02005                  33.0          NaN       0.0  \n",
       "03017                 180.0          NaN       0.0  \n",
       "03068                 345.0          NaN       0.0  \n",
       "04017                  26.0          NaN       0.0  \n",
       "05025                  23.0          NaN       0.0  \n",
       "...                     ...          ...       ...  \n",
       "51001                   NaN          NaN       5.0  \n",
       "52001                   NaN          NaN       3.0  \n",
       "50297                 125.0          NaN       NaN  \n",
       "51001                  51.0          NaN       NaN  \n",
       "52001                  82.0          NaN       NaN  \n",
       "\n",
       "[73 rows x 96 columns]"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4906ee1",
   "metadata": {},
   "source": [
    "Exportamos los datos socioecómicos para los municipios en estudio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "34aa5673-9bce-4b58-a661-78fe68a1ee8e",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "filtered_df.to_csv('datos_municipios_filtered_Cod.csv', encoding = 'ISO-8859-1')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16631aa6",
   "metadata": {},
   "source": [
    "A Continuación filtramos por los nombres de la ciudades"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "ed06a8ad-5694-4014-bfcf-6f85997457cf",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "filter_municipios = df_municipios.loc[df_municipios.Municipio.isin(rows_to_filter1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "770e4ca6-2fd3-4819-90be-fd2e030794c5",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Municipio</th>\n",
       "      <th>Provincia</th>\n",
       "      <th>Comunidad Autónoma</th>\n",
       "      <th>Población Total</th>\n",
       "      <th>Población Hombres</th>\n",
       "      <th>Población Mujeres</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Conductores Hombres</th>\n",
       "      <th>Conductoras Mujeres</th>\n",
       "      <th>Censo Conductores</th>\n",
       "      <th>...</th>\n",
       "      <th>Alcohol (6 puntos)</th>\n",
       "      <th>Drogas (6 puntos)</th>\n",
       "      <th>Casco, Cinturón, SRI (3 puntos)</th>\n",
       "      <th>Móvil (3 puntos)</th>\n",
       "      <th>Semáforo (4 puntos)</th>\n",
       "      <th>Otra (3 puntos)</th>\n",
       "      <th>Otra (4 puntos)</th>\n",
       "      <th>Otra (6 puntos)</th>\n",
       "      <th>Unnamed: 95</th>\n",
       "      <th>Campañas</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Código INE</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>02005</th>\n",
       "      <td>Albacete</td>\n",
       "      <td>Albacete</td>\n",
       "      <td>Castilla-La Mancha</td>\n",
       "      <td>174336.0</td>\n",
       "      <td>85214.0</td>\n",
       "      <td>89122.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59160.0</td>\n",
       "      <td>45441.0</td>\n",
       "      <td>104601.0</td>\n",
       "      <td>...</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>302.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>94.0</td>\n",
       "      <td>337.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>03017</th>\n",
       "      <td>Alicante/Alacant</td>\n",
       "      <td>Alicante/Alacant</td>\n",
       "      <td>Comunitat Valenciana</td>\n",
       "      <td>337482.0</td>\n",
       "      <td>163385.0</td>\n",
       "      <td>174097.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104997.0</td>\n",
       "      <td>80448.0</td>\n",
       "      <td>185445.0</td>\n",
       "      <td>...</td>\n",
       "      <td>109.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>295.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>254.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 96 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Municipio         Provincia    Comunidad Autónoma  \\\n",
       "Código INE                                                             \n",
       "02005               Albacete          Albacete    Castilla-La Mancha   \n",
       "03017       Alicante/Alacant  Alicante/Alacant  Comunitat Valenciana   \n",
       "\n",
       "            Población Total  Población Hombres  Población Mujeres  Unnamed: 7  \\\n",
       "Código INE                                                                      \n",
       "02005              174336.0            85214.0            89122.0         NaN   \n",
       "03017              337482.0           163385.0           174097.0         NaN   \n",
       "\n",
       "            Conductores Hombres  Conductoras Mujeres  Censo Conductores  ...  \\\n",
       "Código INE                                                               ...   \n",
       "02005                   59160.0              45441.0           104601.0  ...   \n",
       "03017                  104997.0              80448.0           185445.0  ...   \n",
       "\n",
       "            Alcohol (6 puntos)  Drogas (6 puntos)  \\\n",
       "Código INE                                          \n",
       "02005                     58.0                NaN   \n",
       "03017                    109.0                NaN   \n",
       "\n",
       "            Casco, Cinturón, SRI (3 puntos)  Móvil (3 puntos)  \\\n",
       "Código INE                                                      \n",
       "02005                                 302.0               NaN   \n",
       "03017                                 295.0               1.0   \n",
       "\n",
       "            Semáforo (4 puntos)  Otra (3 puntos)  Otra (4 puntos)  \\\n",
       "Código INE                                                          \n",
       "02005                      94.0            337.0             46.0   \n",
       "03017                     254.0             27.0            120.0   \n",
       "\n",
       "            Otra (6 puntos)  Unnamed: 95  Campañas  \n",
       "Código INE                                          \n",
       "02005                  33.0          NaN       0.0  \n",
       "03017                 180.0          NaN       0.0  \n",
       "\n",
       "[2 rows x 96 columns]"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filter_municipios.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ded87594",
   "metadata": {},
   "source": [
    "Verificamos cuantos casos hubo coincidencia"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "1cd0ea1a-786c-4218-8fa1-83bb4b026891",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "69"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(filter_municipios.Municipio) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ce1c777",
   "metadata": {},
   "source": [
    "Se exporta el dataframe filtrado por los nombres, tres ciudades no han sido identificadas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "e2adeab9-8816-4f67-8aaf-68724433beff",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "filter_municipios.to_csv('datos_municipios_filtered_ciudad.csv', encoding = 'ISO-8859-1')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54705bf0-5349-4bc4-bebe-97266a370e88",
   "metadata": {
    "tags": []
   },
   "source": [
    "### PARTE III. Vinculamos datos de población y vivienda desde el fichero 10mund00.xls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "7c3f5cc9-b469-46b4-8e4c-621aee681524",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "codigos_INE_long = pd.read_csv(\"codigos_INE_long_final.csv\", encoding = 'ISO-8859-1', dtype ={\"COD\":str})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "b5783898-72cd-48f2-abb7-b46adebd0375",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Unnamed: 0     int64\n",
       "Ciudad        object\n",
       "COD           object\n",
       "dtype: object"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "codigos_INE_long.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "30f66f37-090e-4063-b9b2-7422e79bad0f",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     02005\n",
       "1     28039\n",
       "2     28040\n",
       "3     28041\n",
       "4     11021\n",
       "      ...  \n",
       "67    47045\n",
       "68    48018\n",
       "69    37041\n",
       "70    50065\n",
       "71    50297\n",
       "Name: COD, Length: 72, dtype: object"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows_to_filter = codigos_INE_long.COD\n",
    "rows_to_filter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "b6c012b7-e92b-4103-b626-11cd23426ae0",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0              Albacete\n",
       "1     Alcalá de Henares\n",
       "2            Alcobendas\n",
       "3              Alcorcón\n",
       "4             Algeciras\n",
       "            ...        \n",
       "67             Valencia\n",
       "68           Valladolid\n",
       "69                 Vigo\n",
       "70               Zamora\n",
       "71             Zaragoza\n",
       "Name: Ciudad, Length: 72, dtype: object"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows_to_filter1 = codigos_INE_long.Ciudad\n",
    "rows_to_filter1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "032cb2b5-f87a-479b-970e-42eddada6d64",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "population_csv = pd.read_csv(\"10mun00_nuevo.csv\", encoding = 'utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "64b72ad9-612e-4564-972c-a7bbc2f44835",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>1 Total viviendas (2+3)</th>\n",
       "      <th>2.1 Total viviendas principales (2.11+2.12)</th>\n",
       "      <th>2.21 Viviendas secundarias</th>\n",
       "      <th>2.22 Viviendas vacias</th>\n",
       "      <th>3 Total viviendas colectivas</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>27001  Abadín</td>\n",
       "      <td>1,655</td>\n",
       "      <td>984</td>\n",
       "      <td>294</td>\n",
       "      <td>377</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>48001  Abadiño</td>\n",
       "      <td>2,979</td>\n",
       "      <td>2,695</td>\n",
       "      <td>137</td>\n",
       "      <td>146</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>30001  Abanilla</td>\n",
       "      <td>4,341</td>\n",
       "      <td>2,519</td>\n",
       "      <td>576</td>\n",
       "      <td>1,243</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>48002  Abanto y Ciérvana-Abanto Zierbena</td>\n",
       "      <td>4,439</td>\n",
       "      <td>3,932</td>\n",
       "      <td>143</td>\n",
       "      <td>363</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30002  Abarán</td>\n",
       "      <td>6,619</td>\n",
       "      <td>4,654</td>\n",
       "      <td>744</td>\n",
       "      <td>1,220</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2303</th>\n",
       "      <td>01063  Zuia</td>\n",
       "      <td>1,152</td>\n",
       "      <td>899</td>\n",
       "      <td>143</td>\n",
       "      <td>108</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2304</th>\n",
       "      <td>18194  Zújar</td>\n",
       "      <td>1,663</td>\n",
       "      <td>1,097</td>\n",
       "      <td>379</td>\n",
       "      <td>187</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2305</th>\n",
       "      <td>20081  Zumaia</td>\n",
       "      <td>4,449</td>\n",
       "      <td>3,550</td>\n",
       "      <td>466</td>\n",
       "      <td>430</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2306</th>\n",
       "      <td>20080  Zumarraga</td>\n",
       "      <td>4,570</td>\n",
       "      <td>4,295</td>\n",
       "      <td>36</td>\n",
       "      <td>234</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2307</th>\n",
       "      <td>04103  Zurgena</td>\n",
       "      <td>1,917</td>\n",
       "      <td>1,273</td>\n",
       "      <td>344</td>\n",
       "      <td>300</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2308 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               1 Total viviendas (2+3)  \\\n",
       "0                                27001  Abadín                   1,655   \n",
       "1                               48001  Abadiño                   2,979   \n",
       "2                              30001  Abanilla                   4,341   \n",
       "3     48002  Abanto y Ciérvana-Abanto Zierbena                   4,439   \n",
       "4                                30002  Abarán                   6,619   \n",
       "...                                        ...                     ...   \n",
       "2303                               01063  Zuia                   1,152   \n",
       "2304                              18194  Zújar                   1,663   \n",
       "2305                             20081  Zumaia                   4,449   \n",
       "2306                          20080  Zumarraga                   4,570   \n",
       "2307                            04103  Zurgena                   1,917   \n",
       "\n",
       "     2.1 Total viviendas principales (2.11+2.12) 2.21 Viviendas secundarias  \\\n",
       "0                                            984                        294   \n",
       "1                                          2,695                        137   \n",
       "2                                          2,519                        576   \n",
       "3                                          3,932                        143   \n",
       "4                                          4,654                        744   \n",
       "...                                          ...                        ...   \n",
       "2303                                         899                        143   \n",
       "2304                                       1,097                        379   \n",
       "2305                                       3,550                        466   \n",
       "2306                                       4,295                         36   \n",
       "2307                                       1,273                        344   \n",
       "\n",
       "     2.22 Viviendas vacias  3 Total viviendas colectivas  \n",
       "0                      377                             0  \n",
       "1                      146                             1  \n",
       "2                    1,243                             3  \n",
       "3                      363                             1  \n",
       "4                    1,220                             1  \n",
       "...                    ...                           ...  \n",
       "2303                   108                             2  \n",
       "2304                   187                             0  \n",
       "2305                   430                             2  \n",
       "2306                   234                             6  \n",
       "2307                   300                             0  \n",
       "\n",
       "[2308 rows x 6 columns]"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population_csv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d019b46a",
   "metadata": {},
   "source": [
    "Cambiamos el nombre de las columnas del dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "5adf1165-a0d2-49eb-9e07-79998b946ce9",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "population_csv.columns = ['COD_CIUDAD', 'Total viviendas', 'Total viviendas principales', 'Viviendas secundarias', 'Viviendas vacias', 'Total viviendas colectivas']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "49847fe7-f193-4a34-9283-adfd34680be2",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['COD_CIUDAD', 'Total viviendas', 'Total viviendas principales',\n",
       "       'Viviendas secundarias', 'Viviendas vacias',\n",
       "       'Total viviendas colectivas'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population_csv.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "a1901cf6-8072-46f8-acff-8e7a560173e3",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>COD_CIUDAD</th>\n",
       "      <th>Total viviendas</th>\n",
       "      <th>Total viviendas principales</th>\n",
       "      <th>Viviendas secundarias</th>\n",
       "      <th>Viviendas vacias</th>\n",
       "      <th>Total viviendas colectivas</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>27001  Abadín</td>\n",
       "      <td>1,655</td>\n",
       "      <td>984</td>\n",
       "      <td>294</td>\n",
       "      <td>377</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>48001  Abadiño</td>\n",
       "      <td>2,979</td>\n",
       "      <td>2,695</td>\n",
       "      <td>137</td>\n",
       "      <td>146</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>30001  Abanilla</td>\n",
       "      <td>4,341</td>\n",
       "      <td>2,519</td>\n",
       "      <td>576</td>\n",
       "      <td>1,243</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>48002  Abanto y Ciérvana-Abanto Zierbena</td>\n",
       "      <td>4,439</td>\n",
       "      <td>3,932</td>\n",
       "      <td>143</td>\n",
       "      <td>363</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 COD_CIUDAD Total viviendas  \\\n",
       "0                             27001  Abadín           1,655   \n",
       "1                            48001  Abadiño           2,979   \n",
       "2                           30001  Abanilla           4,341   \n",
       "3  48002  Abanto y Ciérvana-Abanto Zierbena           4,439   \n",
       "\n",
       "  Total viviendas principales Viviendas secundarias Viviendas vacias  \\\n",
       "0                         984                   294              377   \n",
       "1                       2,695                   137              146   \n",
       "2                       2,519                   576            1,243   \n",
       "3                       3,932                   143              363   \n",
       "\n",
       "   Total viviendas colectivas  \n",
       "0                           0  \n",
       "1                           1  \n",
       "2                           3  \n",
       "3                           1  "
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population_csv.head(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc6f69dd",
   "metadata": {},
   "source": [
    "Vamoa dividir el valor de codigo INE y ciudad que estan fusionados en la misma columna en el dataframe original."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c35b3cf",
   "metadata": {},
   "source": [
    " Para que funcione el método string tiene que estar aplicado sobre la columan que es \n",
    " string y no sobre una que es objeto, sin embargo, las transformaciones  string no han funcionado por lo tanto tendremos que realizar un split con un procedimiento más complicado usando la función split de python."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "4be6aaa0",
   "metadata": {},
   "outputs": [],
   "source": [
    "population_csv['COD_CIUDAD'] = population_csv['COD_CIUDAD'].astype('string')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "d7d8f1c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "# Utilizamos el método general split para dividir la columna\n",
    "population_csv['COD'], population_csv['Ciudad'] = zip(*population_csv['COD_CIUDAD'].apply(lambda x: x.split(\" \", 1)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "2eae00ec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>COD_CIUDAD</th>\n",
       "      <th>Total viviendas</th>\n",
       "      <th>Total viviendas principales</th>\n",
       "      <th>Viviendas secundarias</th>\n",
       "      <th>Viviendas vacias</th>\n",
       "      <th>Total viviendas colectivas</th>\n",
       "      <th>COD</th>\n",
       "      <th>Ciudad</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>27001  Abadín</td>\n",
       "      <td>1,655</td>\n",
       "      <td>984</td>\n",
       "      <td>294</td>\n",
       "      <td>377</td>\n",
       "      <td>0</td>\n",
       "      <td>27001</td>\n",
       "      <td>Abadín</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>48001  Abadiño</td>\n",
       "      <td>2,979</td>\n",
       "      <td>2,695</td>\n",
       "      <td>137</td>\n",
       "      <td>146</td>\n",
       "      <td>1</td>\n",
       "      <td>48001</td>\n",
       "      <td>Abadiño</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>30001  Abanilla</td>\n",
       "      <td>4,341</td>\n",
       "      <td>2,519</td>\n",
       "      <td>576</td>\n",
       "      <td>1,243</td>\n",
       "      <td>3</td>\n",
       "      <td>30001</td>\n",
       "      <td>Abanilla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>48002  Abanto y Ciérvana-Abanto Zierbena</td>\n",
       "      <td>4,439</td>\n",
       "      <td>3,932</td>\n",
       "      <td>143</td>\n",
       "      <td>363</td>\n",
       "      <td>1</td>\n",
       "      <td>48002</td>\n",
       "      <td>Abanto y Ciérvana-Abanto Zierbena</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30002  Abarán</td>\n",
       "      <td>6,619</td>\n",
       "      <td>4,654</td>\n",
       "      <td>744</td>\n",
       "      <td>1,220</td>\n",
       "      <td>1</td>\n",
       "      <td>30002</td>\n",
       "      <td>Abarán</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2303</th>\n",
       "      <td>01063  Zuia</td>\n",
       "      <td>1,152</td>\n",
       "      <td>899</td>\n",
       "      <td>143</td>\n",
       "      <td>108</td>\n",
       "      <td>2</td>\n",
       "      <td>01063</td>\n",
       "      <td>Zuia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2304</th>\n",
       "      <td>18194  Zújar</td>\n",
       "      <td>1,663</td>\n",
       "      <td>1,097</td>\n",
       "      <td>379</td>\n",
       "      <td>187</td>\n",
       "      <td>0</td>\n",
       "      <td>18194</td>\n",
       "      <td>Zújar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2305</th>\n",
       "      <td>20081  Zumaia</td>\n",
       "      <td>4,449</td>\n",
       "      <td>3,550</td>\n",
       "      <td>466</td>\n",
       "      <td>430</td>\n",
       "      <td>2</td>\n",
       "      <td>20081</td>\n",
       "      <td>Zumaia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2306</th>\n",
       "      <td>20080  Zumarraga</td>\n",
       "      <td>4,570</td>\n",
       "      <td>4,295</td>\n",
       "      <td>36</td>\n",
       "      <td>234</td>\n",
       "      <td>6</td>\n",
       "      <td>20080</td>\n",
       "      <td>Zumarraga</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2307</th>\n",
       "      <td>04103  Zurgena</td>\n",
       "      <td>1,917</td>\n",
       "      <td>1,273</td>\n",
       "      <td>344</td>\n",
       "      <td>300</td>\n",
       "      <td>0</td>\n",
       "      <td>04103</td>\n",
       "      <td>Zurgena</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2308 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    COD_CIUDAD Total viviendas  \\\n",
       "0                                27001  Abadín           1,655   \n",
       "1                               48001  Abadiño           2,979   \n",
       "2                              30001  Abanilla           4,341   \n",
       "3     48002  Abanto y Ciérvana-Abanto Zierbena           4,439   \n",
       "4                                30002  Abarán           6,619   \n",
       "...                                        ...             ...   \n",
       "2303                               01063  Zuia           1,152   \n",
       "2304                              18194  Zújar           1,663   \n",
       "2305                             20081  Zumaia           4,449   \n",
       "2306                          20080  Zumarraga           4,570   \n",
       "2307                            04103  Zurgena           1,917   \n",
       "\n",
       "     Total viviendas principales Viviendas secundarias Viviendas vacias  \\\n",
       "0                            984                   294              377   \n",
       "1                          2,695                   137              146   \n",
       "2                          2,519                   576            1,243   \n",
       "3                          3,932                   143              363   \n",
       "4                          4,654                   744            1,220   \n",
       "...                          ...                   ...              ...   \n",
       "2303                         899                   143              108   \n",
       "2304                       1,097                   379              187   \n",
       "2305                       3,550                   466              430   \n",
       "2306                       4,295                    36              234   \n",
       "2307                       1,273                   344              300   \n",
       "\n",
       "      Total viviendas colectivas    COD                              Ciudad  \n",
       "0                              0  27001                              Abadín  \n",
       "1                              1  48001                             Abadiño  \n",
       "2                              3  30001                            Abanilla  \n",
       "3                              1  48002   Abanto y Ciérvana-Abanto Zierbena  \n",
       "4                              1  30002                              Abarán  \n",
       "...                          ...    ...                                 ...  \n",
       "2303                           2  01063                                Zuia  \n",
       "2304                           0  18194                               Zújar  \n",
       "2305                           2  20081                              Zumaia  \n",
       "2306                           6  20080                           Zumarraga  \n",
       "2307                           0  04103                             Zurgena  \n",
       "\n",
       "[2308 rows x 8 columns]"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population_csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "e3ea824a-300d-4e2e-a9ac-f4ada25d20c8",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# population_csv[['COD','Ciudad']] = population_csv['COD_CIUDAD'].str.split(\" \",1, expand=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "4bde176c-aab2-4248-acf5-ceae81418852",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "population_csv = population_csv.drop(columns = [\"COD_CIUDAD\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "id": "72f81225-1375-40ee-ab27-2f7adde31b6c",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     02005\n",
       "1     28039\n",
       "2     28040\n",
       "3     28041\n",
       "4     11021\n",
       "      ...  \n",
       "67    47045\n",
       "68    48018\n",
       "69    37041\n",
       "70    50065\n",
       "71    50297\n",
       "Name: COD, Length: 72, dtype: object"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows_to_filter"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e7e4f75",
   "metadata": {},
   "source": [
    "Colocamos el cod como índice"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "abc2896a-c834-4839-9b31-e860305cabc0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "population_indcod = population_csv.set_index('COD')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "id": "0c1deb23-627d-499b-bfbd-bb033f9365a5",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "filtered_population_indcod = population_indcod.loc[population_indcod.index.isin(rows_to_filter)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ade2b40d",
   "metadata": {},
   "source": [
    "Si contamos el número de coincidencias, solo han habido 26"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "id": "444b3ee1-df86-4566-b2c8-fc3b3156d6e6",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "26"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(filtered_population_indcod.Ciudad)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0889025d",
   "metadata": {},
   "source": [
    "Probaremos hacer una selección en base a los nombres, colocamos el nombre de la ciudad como índice"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "id": "857e8679-b344-4972-97cb-8c6a7a215a3a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "population_ind = population_csv.set_index('Ciudad')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "id": "61faf760-dfd6-46dc-8a9e-7a3778d2727c",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Total viviendas</th>\n",
       "      <th>Total viviendas principales</th>\n",
       "      <th>Viviendas secundarias</th>\n",
       "      <th>Viviendas vacias</th>\n",
       "      <th>Total viviendas colectivas</th>\n",
       "      <th>COD</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ciudad</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Abadín</th>\n",
       "      <td>1,655</td>\n",
       "      <td>984</td>\n",
       "      <td>294</td>\n",
       "      <td>377</td>\n",
       "      <td>0</td>\n",
       "      <td>27001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Abadiño</th>\n",
       "      <td>2,979</td>\n",
       "      <td>2,695</td>\n",
       "      <td>137</td>\n",
       "      <td>146</td>\n",
       "      <td>1</td>\n",
       "      <td>48001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Abanilla</th>\n",
       "      <td>4,341</td>\n",
       "      <td>2,519</td>\n",
       "      <td>576</td>\n",
       "      <td>1,243</td>\n",
       "      <td>3</td>\n",
       "      <td>30001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Abanto y Ciérvana-Abanto Zierbena</th>\n",
       "      <td>4,439</td>\n",
       "      <td>3,932</td>\n",
       "      <td>143</td>\n",
       "      <td>363</td>\n",
       "      <td>1</td>\n",
       "      <td>48002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Abarán</th>\n",
       "      <td>6,619</td>\n",
       "      <td>4,654</td>\n",
       "      <td>744</td>\n",
       "      <td>1,220</td>\n",
       "      <td>1</td>\n",
       "      <td>30002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zuia</th>\n",
       "      <td>1,152</td>\n",
       "      <td>899</td>\n",
       "      <td>143</td>\n",
       "      <td>108</td>\n",
       "      <td>2</td>\n",
       "      <td>01063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zújar</th>\n",
       "      <td>1,663</td>\n",
       "      <td>1,097</td>\n",
       "      <td>379</td>\n",
       "      <td>187</td>\n",
       "      <td>0</td>\n",
       "      <td>18194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zumaia</th>\n",
       "      <td>4,449</td>\n",
       "      <td>3,550</td>\n",
       "      <td>466</td>\n",
       "      <td>430</td>\n",
       "      <td>2</td>\n",
       "      <td>20081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zumarraga</th>\n",
       "      <td>4,570</td>\n",
       "      <td>4,295</td>\n",
       "      <td>36</td>\n",
       "      <td>234</td>\n",
       "      <td>6</td>\n",
       "      <td>20080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zurgena</th>\n",
       "      <td>1,917</td>\n",
       "      <td>1,273</td>\n",
       "      <td>344</td>\n",
       "      <td>300</td>\n",
       "      <td>0</td>\n",
       "      <td>04103</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2308 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  Total viviendas Total viviendas principales  \\\n",
       "Ciudad                                                                          \n",
       "Abadín                                      1,655                         984   \n",
       "Abadiño                                     2,979                       2,695   \n",
       "Abanilla                                    4,341                       2,519   \n",
       "Abanto y Ciérvana-Abanto Zierbena           4,439                       3,932   \n",
       "Abarán                                      6,619                       4,654   \n",
       "...                                           ...                         ...   \n",
       "Zuia                                        1,152                         899   \n",
       "Zújar                                       1,663                       1,097   \n",
       "Zumaia                                      4,449                       3,550   \n",
       "Zumarraga                                   4,570                       4,295   \n",
       "Zurgena                                     1,917                       1,273   \n",
       "\n",
       "                                  Viviendas secundarias Viviendas vacias  \\\n",
       "Ciudad                                                                     \n",
       "Abadín                                              294              377   \n",
       "Abadiño                                             137              146   \n",
       "Abanilla                                            576            1,243   \n",
       "Abanto y Ciérvana-Abanto Zierbena                   143              363   \n",
       "Abarán                                              744            1,220   \n",
       "...                                                 ...              ...   \n",
       "Zuia                                                143              108   \n",
       "Zújar                                               379              187   \n",
       "Zumaia                                              466              430   \n",
       "Zumarraga                                            36              234   \n",
       "Zurgena                                             344              300   \n",
       "\n",
       "                                   Total viviendas colectivas    COD  \n",
       "Ciudad                                                                \n",
       "Abadín                                                      0  27001  \n",
       "Abadiño                                                     1  48001  \n",
       "Abanilla                                                    3  30001  \n",
       "Abanto y Ciérvana-Abanto Zierbena                           1  48002  \n",
       "Abarán                                                      1  30002  \n",
       "...                                                       ...    ...  \n",
       "Zuia                                                        2  01063  \n",
       "Zújar                                                       0  18194  \n",
       "Zumaia                                                      2  20081  \n",
       "Zumarraga                                                   6  20080  \n",
       "Zurgena                                                     0  04103  \n",
       "\n",
       "[2308 rows x 6 columns]"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population_ind"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "027f1f4e",
   "metadata": {},
   "source": [
    "Filtramos utilizando los nombres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "id": "fb7d23d3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0              Albacete\n",
       "1     Alcalá de Henares\n",
       "2            Alcobendas\n",
       "3              Alcorcón\n",
       "4             Algeciras\n",
       "            ...        \n",
       "67             Valencia\n",
       "68           Valladolid\n",
       "69                 Vigo\n",
       "70               Zamora\n",
       "71             Zaragoza\n",
       "Name: Ciudad, Length: 72, dtype: object"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows_to_filter1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "c3f85dec-fa8d-4004-a8b0-ed272f2a79ce",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "pop_filtered_city = population_ind.loc[population_ind.index.isin(rows_to_filter1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "6161d0f7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Total viviendas</th>\n",
       "      <th>Total viviendas principales</th>\n",
       "      <th>Viviendas secundarias</th>\n",
       "      <th>Viviendas vacias</th>\n",
       "      <th>Total viviendas colectivas</th>\n",
       "      <th>COD</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ciudad</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Total viviendas, Total viviendas principales, Viviendas secundarias, Viviendas vacias, Total viviendas colectivas, COD]\n",
       "Index: []"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop_filtered_city "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "id": "382b0006-a729-40d0-9232-9f8117fd8c8e",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "mask = population_csv['Ciudad'].str.contains('|'.join(rows_to_filter1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "c039f5fb-eead-4674-bb86-28567e27dfaf",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Filtrar el DataFrame original usando la Serie booleana como índice\n",
    "filtered_pop_mask = population_csv[mask]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22908203",
   "metadata": {},
   "source": [
    "Contaremos el número de coincidencias utilizando el método anterior"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "id": "8996bb69",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "102"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(filtered_pop_mask)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57fc0cf9",
   "metadata": {},
   "source": [
    "Al ser más flexible las codiciones del método anterior hemos seleccionado más ciudades que eliminaremos manualmente una vez exportado el archivo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "id": "f31b128c-a14a-433f-84ca-b0789e4ddc4a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "filtered_pop_mask.to_csv('fil_poblacion_censo_2011_ciudad_iso.csv', encoding = 'ISO-8859-1')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8f3830a-19c8-4da1-bb1f-9ce558777008",
   "metadata": {},
   "source": [
    "# Parte IV: Datos GDP"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dde86dce",
   "metadata": {},
   "source": [
    "Se descargaron ficheros xls uno para cada provincia. El fichero tiene el nombre con el código del municipio. Los ficheros contienen los datos del municipio, como también de áreas más desagregadas. Hay dos grupos de ficheros que tienen un formato levemente diferente por lo que el proceso para cada uno de estos grupos es levemente diferente."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18fc6625",
   "metadata": {},
   "source": [
    "Probamos uno de los ficheros con formato distinto"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "id": "8668bfe3-5f1a-4827-acfd-dda30cfc2d9b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df = pd.read_excel('./renta provincias/formato distinto/30656.xls', sheet_name=0, skiprows = 7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "id": "7e54a35a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>2020</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02001 Abengibre</td>\n",
       "      <td>11675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0200101 Abengibre distrito 01</td>\n",
       "      <td>11675</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   2020\n",
       "0                02001 Abengibre  11675\n",
       "1  0200101 Abengibre distrito 01  11675"
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60d9b474",
   "metadata": {},
   "source": [
    "Se cambia el nombre de las columnas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "id": "8a76eb13-b8c1-4550-ad23-87c5160d1a95",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df.columns = (\"COD Ciudad\",\"Renta neta media por persona\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "id": "32a3c88a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>COD Ciudad</th>\n",
       "      <th>Renta neta media por persona</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02001 Abengibre</td>\n",
       "      <td>11675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0200101 Abengibre distrito 01</td>\n",
       "      <td>11675</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      COD Ciudad Renta neta media por persona\n",
       "0                02001 Abengibre                        11675\n",
       "1  0200101 Abengibre distrito 01                        11675"
      ]
     },
     "execution_count": 186,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16f7b60c",
   "metadata": {},
   "source": [
    "Es necesario seperar la columna cod ciudad en el codigo y el nombre "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "id": "45d226b0-e5ca-4ec2-825f-6b50a91cb6d2",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "codigos_INE_long = pd.read_csv(\"codigos_INE_long_final.csv\", encoding = 'ISO-8859-1', dtype ={\"COD\":str})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "id": "62b0fd54-a52c-4e61-80fc-79cc69929354",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     02005\n",
       "1     28039\n",
       "2     28040\n",
       "3     28041\n",
       "4     11021\n",
       "      ...  \n",
       "67    47045\n",
       "68    48018\n",
       "69    37041\n",
       "70    50065\n",
       "71    50297\n",
       "Name: COD, Length: 72, dtype: object"
      ]
     },
     "execution_count": 188,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows_to_filter = codigos_INE_long.COD\n",
    "rows_to_filter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "id": "01b757f3-92ba-4f60-a805-399357874f9d",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0              Albacete\n",
       "1     Alcalá de Henares\n",
       "2            Alcobendas\n",
       "3              Alcorcón\n",
       "4             Algeciras\n",
       "            ...        \n",
       "67             Valencia\n",
       "68           Valladolid\n",
       "69                 Vigo\n",
       "70               Zamora\n",
       "71             Zaragoza\n",
       "Name: Ciudad, Length: 72, dtype: object"
      ]
     },
     "execution_count": 189,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows_to_filter1 = codigos_INE_long.Ciudad\n",
    "rows_to_filter1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c88ac418",
   "metadata": {},
   "source": [
    "Se construyeron dos expresiones regulares para separar el nombre de los códigos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "id": "95ced4ff-0798-40f5-b87c-e82a3abef07f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "patron = r'^(\\d+)([^\\d]+)(\\d+)$'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "id": "373fdb10-1d05-42ef-8902-e13c3fabe763",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "patron1 = r'^(\\d+)([^\\d]+)'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "bac1c177-f622-466f-abfd-4e3b452be9c8",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df1 = df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "id": "dba28c75-2bd8-4fd9-b8d2-3682b678405a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Aplicar la función extract para crear las nuevas columnas\n",
    "df[[\"COD\",\"CIUDAD\"]] = df['COD Ciudad'].str.extract(patron1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "id": "8fa96915-7b7e-4340-a056-f420a95fd0cb",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>COD Ciudad</th>\n",
       "      <th>Renta neta media por persona</th>\n",
       "      <th>COD</th>\n",
       "      <th>CIUDAD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02001 Abengibre</td>\n",
       "      <td>11675</td>\n",
       "      <td>02001</td>\n",
       "      <td>Abengibre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0200101 Abengibre distrito 01</td>\n",
       "      <td>11675</td>\n",
       "      <td>0200101</td>\n",
       "      <td>Abengibre distrito</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0200101001 Abengibre sección 01001</td>\n",
       "      <td>11675</td>\n",
       "      <td>0200101001</td>\n",
       "      <td>Abengibre sección</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>02002 Alatoz</td>\n",
       "      <td>9866</td>\n",
       "      <td>02002</td>\n",
       "      <td>Alatoz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0200201 Alatoz distrito 01</td>\n",
       "      <td>9866</td>\n",
       "      <td>0200201</td>\n",
       "      <td>Alatoz distrito</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0200201001 Alatoz sección 01001</td>\n",
       "      <td>9866</td>\n",
       "      <td>0200201001</td>\n",
       "      <td>Alatoz sección</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>02003 Albacete</td>\n",
       "      <td>12529</td>\n",
       "      <td>02003</td>\n",
       "      <td>Albacete</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0200301 Albacete distrito 01</td>\n",
       "      <td>14260</td>\n",
       "      <td>0200301</td>\n",
       "      <td>Albacete distrito</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0200301001 Albacete sección 01001</td>\n",
       "      <td>18111</td>\n",
       "      <td>0200301001</td>\n",
       "      <td>Albacete sección</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0200301002 Albacete sección 01002</td>\n",
       "      <td>16631</td>\n",
       "      <td>0200301002</td>\n",
       "      <td>Albacete sección</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           COD Ciudad Renta neta media por persona  \\\n",
       "0                     02001 Abengibre                        11675   \n",
       "1       0200101 Abengibre distrito 01                        11675   \n",
       "2  0200101001 Abengibre sección 01001                        11675   \n",
       "3                        02002 Alatoz                         9866   \n",
       "4          0200201 Alatoz distrito 01                         9866   \n",
       "5     0200201001 Alatoz sección 01001                         9866   \n",
       "6                      02003 Albacete                        12529   \n",
       "7        0200301 Albacete distrito 01                        14260   \n",
       "8   0200301001 Albacete sección 01001                        18111   \n",
       "9   0200301002 Albacete sección 01002                        16631   \n",
       "\n",
       "          COD                CIUDAD  \n",
       "0       02001             Abengibre  \n",
       "1     0200101   Abengibre distrito   \n",
       "2  0200101001    Abengibre sección   \n",
       "3       02002                Alatoz  \n",
       "4     0200201      Alatoz distrito   \n",
       "5  0200201001       Alatoz sección   \n",
       "6       02003              Albacete  \n",
       "7     0200301    Albacete distrito   \n",
       "8  0200301001     Albacete sección   \n",
       "9  0200301002     Albacete sección   "
      ]
     },
     "execution_count": 194,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8de608cc",
   "metadata": {},
   "source": [
    "A continuación se hace un prueba con el formato más común"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "id": "2daf5388-4b5a-492b-91d7-8cf26e3ead45",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "inv = pd.read_excel('./renta provincias/30953.xls', sheet_name=0, skiprows = 7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "id": "4804340d-fbf3-4f05-8dc9-074f1237f476",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "inv.columns = (\"COD Ciudad\",\"Renta neta media por persona 2020\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "id": "19e990e5-09c3-4428-ab59-54eca7b3531e",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "inv[[\"COD\",\"CIUDAD\"]] = inv['COD Ciudad'].str.extract(patron1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "id": "78b6fd66-53ed-43d1-adbe-072ce8b1da57",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>COD Ciudad</th>\n",
       "      <th>Renta neta media por persona 2020</th>\n",
       "      <th>COD</th>\n",
       "      <th>CIUDAD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39001 Alfoz de Lloredo</td>\n",
       "      <td>11589</td>\n",
       "      <td>39001</td>\n",
       "      <td>Alfoz de Lloredo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>39002 Ampuero</td>\n",
       "      <td>11220</td>\n",
       "      <td>39002</td>\n",
       "      <td>Ampuero</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               COD Ciudad Renta neta media por persona 2020    COD  \\\n",
       "0  39001 Alfoz de Lloredo                             11589  39001   \n",
       "1           39002 Ampuero                             11220  39002   \n",
       "\n",
       "              CIUDAD  \n",
       "0   Alfoz de Lloredo  \n",
       "1            Ampuero  "
      ]
     },
     "execution_count": 202,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inv.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e13c26d-86ca-4f2c-b950-ee47f12c6662",
   "metadata": {
    "tags": []
   },
   "source": [
    "A continuación definimos la columna COD como indice del df "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "id": "bcd38f26-5a28-42ad-852e-41a938742420",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "inv_index = inv.set_index(\"COD\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "id": "70d24c65-dc64-4846-b5de-765a99b11323",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     02005\n",
       "1     28039\n",
       "2     28040\n",
       "3     28041\n",
       "4     11021\n",
       "      ...  \n",
       "67    47045\n",
       "68    48018\n",
       "69    37041\n",
       "70    50065\n",
       "71    50297\n",
       "Name: COD, Length: 72, dtype: object"
      ]
     },
     "execution_count": 204,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows_to_filter"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eaf132ed",
   "metadata": {},
   "source": [
    "Filtramos por medio de los índices utilizando el código del municipio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "id": "73f00a2a-ea74-4f35-98eb-b42b2957b691",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>COD Ciudad</th>\n",
       "      <th>Renta neta media por persona 2020</th>\n",
       "      <th>CIUDAD</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>COD</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>39018</th>\n",
       "      <td>39018 Cartes</td>\n",
       "      <td>10998</td>\n",
       "      <td>Cartes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NaN</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NaN</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NaN</th>\n",
       "      <td>Notas:</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NaN</th>\n",
       "      <td>.Dato protegido por secreto estadístico</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NaN</th>\n",
       "      <td>Estadística experimental</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NaN</th>\n",
       "      <td>La renta por unidad de consumo se obtiene, par...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NaN</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NaN</th>\n",
       "      <td>Fuente:</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NaN</th>\n",
       "      <td>Instituto Nacional de Estadística</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              COD Ciudad  \\\n",
       "COD                                                        \n",
       "39018                                       39018 Cartes   \n",
       "NaN                                                  NaN   \n",
       "NaN                                                  NaN   \n",
       "NaN                                               Notas:   \n",
       "NaN              .Dato protegido por secreto estadístico   \n",
       "NaN                             Estadística experimental   \n",
       "NaN    La renta por unidad de consumo se obtiene, par...   \n",
       "NaN                                                  NaN   \n",
       "NaN                                             Fuente:    \n",
       "NaN                    Instituto Nacional de Estadística   \n",
       "\n",
       "      Renta neta media por persona 2020   CIUDAD  \n",
       "COD                                               \n",
       "39018                             10998   Cartes  \n",
       "NaN                                 NaN      NaN  \n",
       "NaN                                 NaN      NaN  \n",
       "NaN                                 NaN      NaN  \n",
       "NaN                                 NaN      NaN  \n",
       "NaN                                 NaN      NaN  \n",
       "NaN                                 NaN      NaN  \n",
       "NaN                                 NaN      NaN  \n",
       "NaN                                 NaN      NaN  \n",
       "NaN                                 NaN      NaN  "
      ]
     },
     "execution_count": 205,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inv_index.loc[inv_index.index.isin(rows_to_filter)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a9ead61",
   "metadata": {},
   "source": [
    "Realizamos una busqueda más flexible"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "id": "8257feee-1061-4746-bfa8-cbcceb752acc",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "mask_inv = inv['CIUDAD'].str.contains('|'.join(rows_to_filter1)).dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92738378",
   "metadata": {},
   "source": [
    "Se eliminaron los na"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "id": "8c498faf-a644-4678-9c06-5a2756203415",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 207,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mask_inv.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc8c6e96",
   "metadata": {},
   "source": [
    "Por precaución eliminamos los NA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 213,
   "id": "f33cc85e-43b0-4691-9d65-d53060128015",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "inv = inv.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2153acf8",
   "metadata": {},
   "source": [
    "A continuación verificamos que hemos podido seleccionar uno de los municipios por el código"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "id": "057d5ff3-c494-4b28-ad34-715fe5e5eab0",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>COD Ciudad</th>\n",
       "      <th>Renta neta media por persona 2020</th>\n",
       "      <th>COD</th>\n",
       "      <th>CIUDAD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>39075 Santander</td>\n",
       "      <td>14202</td>\n",
       "      <td>39075</td>\n",
       "      <td>Santander</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         COD Ciudad Renta neta media por persona 2020    COD      CIUDAD\n",
       "74  39075 Santander                             14202  39075   Santander"
      ]
     },
     "execution_count": 214,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inv[mask_inv]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3cd28b21-5de7-45c9-a8a8-6494695206cd",
   "metadata": {},
   "source": [
    "El procedimiento anterior debe ser realizado de forma iterativa para cada fichero xls, y el resultado debe ser añadido a un nuevo dataframe. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "id": "b2d53084-8137-4281-85b1-605056d2f8bb",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import os\n",
    "import glob\n",
    "import os\n",
    "import string"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9938110",
   "metadata": {},
   "source": [
    "Se define el directorio de trabajo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 248,
   "id": "ba224b4a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'d:\\\\Respaldo toshiba\\\\projectos\\\\manifolds\\\\ciudades\\\\datos\\\\socioeconomicos\\\\renta provincias'"
      ]
     },
     "execution_count": 248,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 247,
   "id": "7c635948-cf05-4433-968e-21c028d079c5",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[WinError 2] The system cannot find the file specified: './renta provincias'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[247], line 3\u001b[0m\n\u001b[0;32m      1\u001b[0m path \u001b[38;5;241m=\u001b[39m(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m./renta provincias\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[0;32m      2\u001b[0m \u001b[38;5;66;03m# Change the directory\u001b[39;00m\n\u001b[1;32m----> 3\u001b[0m os\u001b[38;5;241m.\u001b[39mchdir(path)\n",
      "\u001b[1;31mFileNotFoundError\u001b[0m: [WinError 2] The system cannot find the file specified: './renta provincias'"
     ]
    }
   ],
   "source": [
    "path =('./renta provincias')\n",
    "# Change the directory\n",
    "os.chdir(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 244,
   "id": "3746d5c9",
   "metadata": {},
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[WinError 2] The system cannot find the file specified: './renta provincias'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[244], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m os\u001b[38;5;241m.\u001b[39mchdir(path)\n",
      "\u001b[1;31mFileNotFoundError\u001b[0m: [WinError 2] The system cannot find the file specified: './renta provincias'"
     ]
    }
   ],
   "source": [
    "os.chdir(path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5461e35",
   "metadata": {},
   "source": [
    "Realizaremos el procedimiento probado anteriormente de forma iterativa para cada fichero. Guardaremos los resultados en un diccionario con el dataframe para cada municipio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 218,
   "id": "4417a720-29e0-4df9-b4f3-e2c673ba4853",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "dict_excel = {}\n",
    "\n",
    "\n",
    "for file in os.listdir():\n",
    "    if file.endswith('.xls'):\n",
    "            temp = pd.read_excel(file, sheet_name = 0)\n",
    "            temp = temp.iloc[:,0:2].dropna()\n",
    "            #nom_columnas = (temp.columns)\n",
    "            temp.columns = (\"COD Ciudad\",\"Renta neta media por persona 2020\")\n",
    "            patron1 = r'^(\\d+)([^\\d]+)'\n",
    "            temp[[\"COD\",\"CIUDAD\"]] = temp['COD Ciudad'].str.extract(patron1)\n",
    "            temp = temp.dropna()\n",
    "            df_temp = pd.DataFrame(temp)\n",
    "            dict_excel[file] = df_temp\n",
    "            \n",
    "   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "id": "53af2217",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "36"
      ]
     },
     "execution_count": 230,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(dict_excel)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8abd3e74",
   "metadata": {},
   "source": [
    "A continuación vamos aplicar el proceso de selección por nombre de la ciudad de forma flexible\n",
    "para seleccionar el municipio de interés. Esto se guarda en formato en un diccionario de dataframes  (dict_exacto), adicionalmente creamos un dataframe donde se van concatenando los resultdos (df_temp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "id": "16bf28b5-f106-4b6d-8f22-a7773b299310",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "dict_exacto = {}\n",
    "df_temp = pd.DataFrame()\n",
    "for key, value in dict_excel.items():\n",
    "    t = dict_excel[key]\n",
    "    #t1 = t.set_index('CIUDAD')\n",
    "    #t2 = t1.loc[t1.index.isin(rows_to_filter1)]\n",
    "    t1 =  t['CIUDAD'].str.contains('|'.join(rows_to_filter1)).dropna()\n",
    "    #t1 =  t['CIUDAD'].str.match('|'.join(rows_to_filter1)).dropna()\n",
    "    final = t[t1]\n",
    "    df_final = pd.DataFrame(final)\n",
    "    df_temp = pd.concat([df_temp,df_final], axis = 0)\n",
    "    dict_exacto[key] = df_final\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "id": "da83200b-4ce0-4e9d-9e6e-1b90e44e2ea0",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>COD Ciudad</th>\n",
       "      <th>Renta neta media por persona 2020</th>\n",
       "      <th>COD</th>\n",
       "      <th>CIUDAD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>39075 Santander</td>\n",
       "      <td>14202</td>\n",
       "      <td>39075</td>\n",
       "      <td>Santander</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>13033 Castellar de Santiago</td>\n",
       "      <td>8549</td>\n",
       "      <td>13033</td>\n",
       "      <td>Castellar de Santiago</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>13034 Ciudad Real</td>\n",
       "      <td>13580</td>\n",
       "      <td>13034</td>\n",
       "      <td>Ciudad Real</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>14021 Córdoba</td>\n",
       "      <td>11791</td>\n",
       "      <td>14021</td>\n",
       "      <td>Córdoba</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>14040 Montalbán de Córdoba</td>\n",
       "      <td>9408</td>\n",
       "      <td>14040</td>\n",
       "      <td>Montalbán de Córdoba</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>47186 Valladolid</td>\n",
       "      <td>14247</td>\n",
       "      <td>47186</td>\n",
       "      <td>Valladolid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>49103 Madridanos</td>\n",
       "      <td>10446</td>\n",
       "      <td>49103</td>\n",
       "      <td>Madridanos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254</th>\n",
       "      <td>49275 Zamora</td>\n",
       "      <td>12884</td>\n",
       "      <td>49275</td>\n",
       "      <td>Zamora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>51001 Ceuta</td>\n",
       "      <td>12358</td>\n",
       "      <td>51001</td>\n",
       "      <td>Ceuta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>52001 Melilla</td>\n",
       "      <td>11665</td>\n",
       "      <td>52001</td>\n",
       "      <td>Melilla</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3688 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      COD Ciudad Renta neta media por persona 2020    COD  \\\n",
       "81               39075 Santander                             14202  39075   \n",
       "39   13033 Castellar de Santiago                              8549  13033   \n",
       "40             13034 Ciudad Real                             13580  13034   \n",
       "27                 14021 Córdoba                             11791  14021   \n",
       "46    14040 Montalbán de Córdoba                              9408  14040   \n",
       "..                           ...                               ...    ...   \n",
       "187             47186 Valladolid                             14247  47186   \n",
       "99              49103 Madridanos                             10446  49103   \n",
       "254                 49275 Zamora                             12884  49275   \n",
       "7                    51001 Ceuta                             12358  51001   \n",
       "7                  52001 Melilla                             11665  52001   \n",
       "\n",
       "                     CIUDAD  \n",
       "81                Santander  \n",
       "39    Castellar de Santiago  \n",
       "40              Ciudad Real  \n",
       "27                  Córdoba  \n",
       "46     Montalbán de Córdoba  \n",
       "..                      ...  \n",
       "187              Valladolid  \n",
       "99               Madridanos  \n",
       "254                  Zamora  \n",
       "7                     Ceuta  \n",
       "7                   Melilla  \n",
       "\n",
       "[3688 rows x 4 columns]"
      ]
     },
     "execution_count": 236,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_temp"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0eea9f8",
   "metadata": {},
   "source": [
    "Se realiza un expresion regular para  seleccionar coincidencias tanto por seccion como por distrito sobre dataframe concatenado df_temp "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "id": "ebe6e943-8d43-4ba1-94aa-3402995422f4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "mask = df_temp['CIUDAD'].str.contains('sección|distrito', regex=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "351b0c6b",
   "metadata": {},
   "source": [
    "Esta mascara boleana es utilizada para seleccionar los elementos coincidentes dentro del dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "id": "e6583585",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df_temp[~mask]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "id": "c33aed50",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "106"
      ]
     },
     "execution_count": 241,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 292,
   "id": "751ee8ae-bf20-446f-9342-0f9641ecdc93",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df.to_csv('GDP_final.csv', encoding = \"ISO-8859-1\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f68e4974",
   "metadata": {},
   "source": [
    "Repetimos el proceso para los ficheros que tienen un formato distinto. Definmos como el directorio donde estan los ficheros de formato distinto utilizando una ruta relativa consideranco que hemos cambiado el directorio raíz a ./renta provincias/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f428861",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "id": "646d6069-3383-4581-832c-1ca1e869f916",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "path1 =('./formato distinto')\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e708512e",
   "metadata": {},
   "source": [
    "Asignamos el nuevo directorio de trabajo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "id": "3909f50d",
   "metadata": {},
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[WinError 2] The system cannot find the file specified: './formato distinto'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[253], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m os\u001b[38;5;241m.\u001b[39mchdir(path1)\n",
      "\u001b[1;31mFileNotFoundError\u001b[0m: [WinError 2] The system cannot find the file specified: './formato distinto'"
     ]
    }
   ],
   "source": [
    "os.chdir(path1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6958d811",
   "metadata": {},
   "source": [
    "Ejecutamos el proceso de forma iterativa para cada uno de los ficheros de formato distinto, los guardamos en un diccionario de dataframes (dict_excel1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "id": "212611ad-25dc-4c2e-9bd2-b0d8cb33a53e",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "dict_excel1 = {}\n",
    "\n",
    "# Iterate over all the files in the directory\n",
    "for file in os.listdir():\n",
    "    if file.endswith('.xls'):\n",
    "            temp = pd.read_excel(file, sheet_name = 0)\n",
    "            temp = temp.iloc[:,0:2].dropna()\n",
    "            #nom_columnas = (temp.columns)\n",
    "            temp.columns = (\"COD Ciudad\",\"Renta neta media por persona 2020\")\n",
    "            patron1 = r'^(\\d+)([^\\d]+)'\n",
    "            temp[[\"COD\",\"CIUDAD\"]] = temp['COD Ciudad'].str.extract(patron1)\n",
    "            temp = temp.dropna()\n",
    "            df_temp = pd.DataFrame(temp)\n",
    "            dict_excel1[file] = df_temp"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d5c6227",
   "metadata": {},
   "source": [
    "Seleccionamos con una selección amplia las coincidencias entre nombres de ciudad y dataframe de datos de ingresos, igual que anteriormente, guardamos esta información en un dataframe concatenando paso a paso (df_temp1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "id": "e28260f5-5bea-4ab4-94c4-83ec0467af86",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "dict_exacto1 = {}\n",
    "df_temp1 = pd.DataFrame()\n",
    "for key, value in dict_excel1.items():\n",
    "    t = dict_excel1[key]\n",
    "    #t1 = t.set_index('CIUDAD')\n",
    "    #t2 = t1.loc[t1.index.isin(rows_to_filter1)]\n",
    "    t1 =  t['CIUDAD'].str.contains('|'.join(rows_to_filter1)).dropna()\n",
    "    #t1 =  t['CIUDAD'].str.match('|'.join(rows_to_filter1)).dropna()\n",
    "    final1 = t[t1]\n",
    "    df_final1 = pd.DataFrame(final1)\n",
    "    df_temp1 = pd.concat([df_temp1,df_final1], axis = 0)\n",
    "    dict_exacto1[key] = df_final1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01038111",
   "metadata": {},
   "source": [
    "Con una expresión regular identificamos coincidencias en sección o distrito"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "id": "a4a7dda1-186c-4ce4-9445-0c45e23baac0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "mask1 = df_temp1['CIUDAD'].str.contains('sección|distrito', regex=True)\n",
    "df1 = df_temp1[~mask1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "id": "8d1cfdaa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "47"
      ]
     },
     "execution_count": 259,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "id": "b67a4366-7f51-4042-bde7-a4ba22a75c96",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df1.to_csv('GDP_formato_distinto.csv', encoding = 'ISO-8859-1')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "214f771a-35bd-4370-a08d-bc22ac0ecde1",
   "metadata": {},
   "source": [
    "# FIN"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}