Preparacion.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "070aeb99-2957-4620-8c31-250bd11aef0d",
   "metadata": {},
   "source": [
    "# Importación de módulos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2d0fc378-d568-42a0-a98d-1aa9cfd24043",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d85a11c9-6ad2-4b30-96b6-39b6b2411d98",
   "metadata": {},
   "source": [
    "# Lectura de la Base de Datos\n",
    "Para descargar la base de datos original se necesita ir a la siguiente [ruta](https://sio.cnsf.gob.mx/)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cbb66aff-58a9-4ffe-b600-55fde63b23cc",
   "metadata": {},
   "source": [
    "## Definición de ruta de lectura"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "fffd728c-89c4-4505-a2c7-a5e25b30d3b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "read_dir = r'C:\\Users\\ed_22\\Documents\\SIO\\Bases Originales'\n",
    "read_file = 'ER_dic2023.xlsx'\n",
    "read_path = os.path.join(read_dir, read_file)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff931e27-a31c-4288-9a2c-659065fdf992",
   "metadata": {},
   "source": [
    "## Variables globales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8f024bfb-d32e-4e9c-b3ab-d5b634c27490",
   "metadata": {},
   "outputs": [],
   "source": [
    "FIELDS = {\n",
    "    'FECHA_CORTE':'corte',\n",
    "    'NOMBRE_CORTO':'nom_inst',\n",
    "    'DESCRIPCION':'desc',\n",
    "    'DESC_OPERACION':'desc_op',\n",
    "    'IMPORTE':'importe'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "097b6486-e112-48c1-bf11-036fc7a0028c",
   "metadata": {},
   "outputs": [],
   "source": [
    "ER = pd.read_excel(read_path, usecols=FIELDS.keys()).rename(columns=FIELDS)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13f686f8-0a05-4e49-8def-b58baa6dec96",
   "metadata": {},
   "source": [
    "# Análisis exploratorio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e1e1345e-4a0f-42c3-88e9-079f23125f33",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 536897 entries, 0 to 536896\n",
      "Data columns (total 5 columns):\n",
      " #   Column    Non-Null Count   Dtype         \n",
      "---  ------    --------------   -----         \n",
      " 0   corte     536897 non-null  datetime64[ns]\n",
      " 1   nom_inst  536897 non-null  object        \n",
      " 2   desc      536897 non-null  object        \n",
      " 3   desc_op   536897 non-null  object        \n",
      " 4   importe   536897 non-null  float64       \n",
      "dtypes: datetime64[ns](1), float64(1), object(3)\n",
      "memory usage: 20.5+ MB\n"
     ]
    }
   ],
   "source": [
    "ER.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5738719a-80f2-4582-bb3f-2a737b9b507d",
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Crédito Afianzador', 'Fianzas Guardiana Inbursa',\n",
       "       'Liberty Fianzas', 'Fianzas Asecam', 'Afianzadora CBL Fiducia',\n",
       "       'Mapfre Fianzas', 'CESCE Fianzas México', 'Zurich Fianzas México',\n",
       "       'MBIA México', 'Plan Seguro', 'Medi Access Seguros de Salud',\n",
       "       'BBVA Bancomer Seguros Salud', 'AXA Salud', 'General de Salud',\n",
       "       'Servicios Integrales de Salud Nova', 'Seguros Centauro',\n",
       "       'Dentegra Seguros Dentales', 'Odontored Seguros Dentales',\n",
       "       'Pensiones Sura', 'Pensiones Banorte', 'HSBC Pensiones',\n",
       "       'Pensiones BBVA Bancomer', 'Profuturo GNP Pensiones',\n",
       "       'Principal Pensiones', 'Pensiones Banamex',\n",
       "       'Metlife Pensiones México', 'Pensiones Inbursa', 'Seguros Banorte',\n",
       "       'Allianz México', 'Patrimonial Inbursa',\n",
       "       'Atradius Seguros de Crédito', 'Seguros El Potosí',\n",
       "       'General de Seguros', 'Seguros Sura', 'AIG Seguros México',\n",
       "       'La Latinoamericana Seguros', 'Seguros Ve por Más',\n",
       "       'Zurich Santander Seguros México', 'Seguros Inbursa',\n",
       "       'Seguros Atlas', 'Zurich', 'HDI Seguros', 'Metlife México',\n",
       "       'QBE de México Compa?ía de Seguros',\n",
       "       'Seguros Monterrey New York Life', 'Chubb Seguros México',\n",
       "       'Mapfre México', 'Grupo Nacional Provincial', 'Principal Seguros',\n",
       "       'Quálitas', 'Protección Agropecuaria', 'AXA Seguros',\n",
       "       'CitiBanamex Seguros', 'Metlife Más',\n",
       "       'Solunion México Seguros de Crédito', 'Seguros Argos',\n",
       "       'BUPA México', 'XL Seguros México', 'Assurant Da?os México',\n",
       "       'Assurant Vida México', 'Aseguradora Patrimonial Vida',\n",
       "       'Stewart Title Guaranty de México', 'Agroasemex',\n",
       "       'HDI Global Seguros', 'HSBC Seguros', 'Seguros BBVA Bancomer',\n",
       "       'Tokio Marine', 'El Águila', 'Zurich Vida', 'Old Mutual Life',\n",
       "       'A.N.A. Compa?ía de Seguros', 'HIR Compa?ía de Seguros',\n",
       "       'Grupo Mexicano de Seguros', 'Sompo Seguros México',\n",
       "       'Seguros Afirme', 'Seguros de Vida Sura México', 'CESCE México',\n",
       "       'Deco Seguros', 'Seguros Azteca', 'Aseguradora Patrimonial Da?os',\n",
       "       'COFACE Seguro de Crédito México', 'Seguros Azteca Da?os',\n",
       "       'Cardif México Seguros de Vida', 'Cardif México Seguros Generales',\n",
       "       'Prudential Seguros México', 'Primero Seguros',\n",
       "       'FM Global de México', ' Armour Secure Insurance', 'Insignia Life',\n",
       "       'Prevem Seguros', 'SPT', 'Aserta Seguros Vida',\n",
       "       'Pan-American México', 'Thona Seguros', 'Tláloc Seguros',\n",
       "       'Genworth Seguros de Crédito a la Vivienda',\n",
       "       'Seguros de Crédito a la Vivienda SHF',\n",
       "       'Umbrella Compa?ía de Seguros',\n",
       "       'Virginia Surety Seguros de México',\n",
       "       'Berkley Internacional Fianzas México',\n",
       "       'Berkley International Seguros México', 'Fianzas Avanza',\n",
       "       'Swiss Re Corporate Solutions México Seguros',\n",
       "       'Aseguradora Aserta', 'Aseguradora Insurgentes',\n",
       "       'Fianzas y Cauciones Atlas', 'Chubb Fianzas Monterrey',\n",
       "       'SPP Institución de Seguros', 'CRABI', 'Sofimex', 'Dorama',\n",
       "       'Tokio Marine HCC México Compa?ia Afianzadora',\n",
       "       'REASEGURADORA PATRIA', 'Der Neue Horizont Re'], dtype=object)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ER.nom_inst.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "245ed15a-452f-4b60-8d83-38709cc5f206",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Directo', 'Primas Cedidas', 'Primas De Retención',\n",
       "       'Primas de Retención Devengadas', 'Costo Neto de Adquisición',\n",
       "       'Comisiones a Agentes',\n",
       "       'Costo Neto de Siniestralidad, Reclamaciones y Otras Obligaciones Pendientes de Cumplir',\n",
       "       'Utilidad (Pérdida) Técnica', 'Gastos de Operación Netos',\n",
       "       'Resultado Integral de Financiamiento',\n",
       "       'Utilidad (Pérdida) antes de Impuestos a la Utilidad',\n",
       "       'Utilidad  (Pérdida) del Ejercicio',\n",
       "       'Utilidad (Pérdida) de la Operación', 'Tomado',\n",
       "       'Compensaciones Adicionales a Agentes'], dtype=object)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ER.desc.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3bcbf544-27f8-4278-b3c7-eb8ccc44c720",
   "metadata": {},
   "source": [
    "**Nota**: Hay doble espacio en el concepto `'Utilidad  (Pérdida) del Ejercicio'`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a098b10e-67cc-4d3b-8a0c-6fdfd9d401e9",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Total', '*Fianzas', '**Fidelidad', '***Individual',\n",
       "       '***Colectivo', '**Judicial o Penal', '***Penales',\n",
       "       '***No penales', '***Amparan conductores de automóviles',\n",
       "       '**Administrativo', '***Por Obra', '***Proveeduría', '***Fiscales',\n",
       "       '***Arrendamiento', '***Otras de Administrativo', '**De crédito',\n",
       "       '***Suministro', '***Compra Venta', '***Otras de Crédito',\n",
       "       '***Financiera', '*Da?os', '**Garantía financiera',\n",
       "       '*Accidentes y Enfermedades', '***Gastos Médicos', '***Salud',\n",
       "       '***Accidentes Personales', '*Pensiones',\n",
       "       '***IMSS Incapacidad Permanente RT',\n",
       "       '***IMSS Muerte (riesgos de trabajo)',\n",
       "       '***IMSS Invalidez (invalidez y vida)',\n",
       "       '***IMSS Muerte (invalidez y vida)',\n",
       "       '***IMSS Jubilación (retiro, cesantía y vejez)',\n",
       "       '***ISSSTE Incapacidad Permanente RT',\n",
       "       '***ISSSTE Muerte (riesgos de trabajo)',\n",
       "       '***ISSSTE Invalidez (invalidez y vida)',\n",
       "       '***ISSSTE Muerte (invalidez y vida)',\n",
       "       '***ISSSTE Jubilación (retiro, cesantía y vejez)', '*Vida',\n",
       "       '***Vida Individual', '***Vida Grupo', '***Vida Colectivo',\n",
       "       '**Responsabilidad Civil y Riesgos Profesionales', '***General',\n",
       "       '***Aviones y Barcos', '***Viajero', '**Marítimo y Transportes',\n",
       "       '***Carga', '***Cascos', '**Incendio', '**Riesgos Catastróficos',\n",
       "       '***Terremoto y Erupción Volcánica',\n",
       "       '***Huracán y Otros Riesgos Hidrometeorológicos',\n",
       "       '**Agrícola y de animales', '***Agrícola', '**Automóviles',\n",
       "       '***Automóviles Residentes', '***Camiones',\n",
       "       '***Automóviles Turistas', '**Diversos', '***Misceláneos',\n",
       "       '***Técnicos', '***Otros', '***Obligatorios', '**Crédito',\n",
       "       '***Pecuario', '**Reafianzamiento', '***Fidelidad',\n",
       "       '***Administrativo', '***Judicial o Penal', '***De crédito',\n",
       "       '**Crédito a la vivienda', '**Caución', '***De Crédito'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ER.desc_op.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ad3a7be-d2b3-4201-9d3a-6f5103c6ab78",
   "metadata": {},
   "source": [
    "**Notas**\n",
    "- Los caracteres `'ñ'` se ponen como `'?'`\n",
    "- Símbolos `'*'`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c68ba434-cb7e-42d5-a23e-f83fee57d01c",
   "metadata": {},
   "source": [
    "# Limpieza\n",
    "¡Buena práctica!\n",
    "\n",
    "Haz una copia de tu base de datos original."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "8613933f-af3f-443c-b3fa-44299a89c70a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = ER.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "606ada0a-1b00-484a-a477-3970adcaf095",
   "metadata": {},
   "source": [
    "## Reemplazos simples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "d99538b1-b4cd-4732-9527-b4d851e0e610",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['nom_inst'] = df.nom_inst.str.replace('?', 'ñ', regex=True)\n",
    "df['desc'] = df.desc.str.replace('?', 'ñ', regex=True)\n",
    "df['desc_op'] = df.desc_op.str.replace('*', '', regex=True)\n",
    "df['desc_op'] = df.desc_op.str.replace('?', 'ñ', regex=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09e9a385-4076-4b9d-b8c5-cd13938a1919",
   "metadata": {},
   "source": [
    "## Expresiones regulares"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a146ca9b-b8de-4fd5-be1a-f99e6f6ba115",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['desc'] = df.desc.str.replace(r'\\s+', ' ', regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "23b9583a-9978-4eaf-8c2e-17ff01af863c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Directo', 'Primas Cedidas', 'Primas De Retención',\n",
       "       'Primas de Retención Devengadas', 'Costo Neto de Adquisición',\n",
       "       'Comisiones a Agentes',\n",
       "       'Costo Neto de Siniestralidad, Reclamaciones y Otras Obligaciones Pendientes de Cumplir',\n",
       "       'Utilidad (Pérdida) Técnica', 'Gastos de Operación Netos',\n",
       "       'Resultado Integral de Financiamiento',\n",
       "       'Utilidad (Pérdida) antes de Impuestos a la Utilidad',\n",
       "       'Utilidad (Pérdida) del Ejercicio',\n",
       "       'Utilidad (Pérdida) de la Operación', 'Tomado',\n",
       "       'Compensaciones Adicionales a Agentes'], dtype=object)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.desc.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51c7e8cc-7891-4dce-90ac-1deccbd0bd36",
   "metadata": {},
   "source": [
    "# Reduciendo tamaño\n",
    "## Creando catálogos\n",
    "¡Buenas prácticas!\n",
    "\n",
    "- Usa el **tipado** de Python (ver [referencia](https://docs.python.org/3/library/typing.html))\n",
    "- Docstrings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "98562be0-0bd2-4d05-875e-150886a415c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_db(field: pd.Series, pk_name: str, field_name: str) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "    Create a DataFrame representing a database with unique identifiers based on a given field.\n",
    "\n",
    "    Parameters:\n",
    "    field (pandas.Series): The field from which unique identifiers will be generated.\n",
    "    pk (str): The name of the column for unique identifiers.\n",
    "    field_name (str): The name of the column for the original field values.\n",
    "\n",
    "    Returns:\n",
    "    pandas.DataFrame: A DataFrame representing the database with unique identifiers.\n",
    "\n",
    "    Example:\n",
    "    >>> import pandas as pd\n",
    "    >>> data = {'field': ['a', 'b', 'c', 'a', 'b']}\n",
    "    >>> df = pd.DataFrame(data)\n",
    "    >>> create_db(df['field'], 'ID', 'Field_Name')\n",
    "       ID Field_Name\n",
    "    0   1          a\n",
    "    1   2          b\n",
    "    2   3          c\n",
    "    \"\"\"\n",
    "    # Generate unique identifiers for each unique value in the field\n",
    "    unique_values = field.unique()\n",
    "    identifiers = range(1, len(unique_values) + 1)\n",
    "\n",
    "    # Create a DataFrame with the primary key and field name columns\n",
    "    db_df = pd.DataFrame({pk_name: identifiers, field_name: unique_values})\n",
    "    \n",
    "    return db_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd76c34c-f6d6-4b2b-9ba1-080f75bb4323",
   "metadata": {},
   "source": [
    "Testeando"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "4bab303b-b760-4fe6-8e3e-59df27c0f6f8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_desc</th>\n",
       "      <th>descripcion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Directo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Primas Cedidas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Primas De Retención</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Primas de Retención Devengadas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Costo Neto de Adquisición</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_desc                     descripcion\n",
       "0        1                         Directo\n",
       "1        2                  Primas Cedidas\n",
       "2        3             Primas De Retención\n",
       "3        4  Primas de Retención Devengadas\n",
       "4        5       Costo Neto de Adquisición"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_db(df.desc, 'id_desc', 'descripcion').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0459587-2875-4fed-8603-94493590028e",
   "metadata": {},
   "source": [
    "Ventajas del *docstring*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "5fcf8383",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on function create_db in module __main__:\n",
      "\n",
      "create_db(field: pandas.core.series.Series, pk_name: str, field_name: str) -> pandas.core.frame.DataFrame\n",
      "    Create a DataFrame representing a database with unique identifiers based on a given field.\n",
      "    \n",
      "    Parameters:\n",
      "    field (pandas.Series): The field from which unique identifiers will be generated.\n",
      "    pk (str): The name of the column for unique identifiers.\n",
      "    field_name (str): The name of the column for the original field values.\n",
      "    \n",
      "    Returns:\n",
      "    pandas.DataFrame: A DataFrame representing the database with unique identifiers.\n",
      "    \n",
      "    Example:\n",
      "    >>> import pandas as pd\n",
      "    >>> data = {'field': ['a', 'b', 'c', 'a', 'b']}\n",
      "    >>> df = pd.DataFrame(data)\n",
      "    >>> create_db(df['field'], 'ID', 'Field_Name')\n",
      "       ID Field_Name\n",
      "    0   1          a\n",
      "    1   2          b\n",
      "    2   3          c\n",
      "\n"
     ]
    }
   ],
   "source": [
    "help(create_db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "356c9cd8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "    Create a DataFrame representing a database with unique identifiers based on a given field.\n",
      "\n",
      "    Parameters:\n",
      "    field (pandas.Series): The field from which unique identifiers will be generated.\n",
      "    pk (str): The name of the column for unique identifiers.\n",
      "    field_name (str): The name of the column for the original field values.\n",
      "\n",
      "    Returns:\n",
      "    pandas.DataFrame: A DataFrame representing the database with unique identifiers.\n",
      "\n",
      "    Example:\n",
      "    >>> import pandas as pd\n",
      "    >>> data = {'field': ['a', 'b', 'c', 'a', 'b']}\n",
      "    >>> df = pd.DataFrame(data)\n",
      "    >>> create_db(df['field'], 'ID', 'Field_Name')\n",
      "       ID Field_Name\n",
      "    0   1          a\n",
      "    1   2          b\n",
      "    2   3          c\n",
      "    \n"
     ]
    }
   ],
   "source": [
    "print(create_db.__doc__)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0a36e77-3206-46a7-a4ed-04a8e021ff90",
   "metadata": {},
   "source": [
    "## Creamos tablas de catálogos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "146840e9-07a1-4ce0-94be-a491f2adb93b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# nombres de instituciones\n",
    "instituciones = create_db(df.nom_inst, pk_name = 'id_nom', field_name = 'nombre')\n",
    "# trimestres\n",
    "trimestres = create_db(df.corte, pk_name = 'id_trim', field_name = 'trimestre')\n",
    "# descripciones de cuentas\n",
    "cuentas = create_db(df.desc, pk_name = 'id_cuenta', field_name = 'cuenta')\n",
    "# descripciones de operaciones\n",
    "operaciones = create_db(df.desc_op, pk_name = 'id_op', field_name = 'operacion')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "f89b3cf7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Instituciones\n",
      "   id_nom                     nombre\n",
      "0       1         Crédito Afianzador\n",
      "1       2  Fianzas Guardiana Inbursa\n",
      "2       3            Liberty Fianzas\n",
      "3       4             Fianzas Asecam\n",
      "4       5    Afianzadora CBL Fiducia\n",
      "------------------------------------------------------------\n",
      "Trimestres\n",
      "   id_trim  trimestre\n",
      "0        1 2016-03-31\n",
      "1        2 2016-06-30\n",
      "2        3 2016-09-30\n",
      "3        4 2016-12-31\n",
      "4        5 2017-03-31\n",
      "------------------------------------------------------------\n",
      "Cuentas\n",
      "   id_cuenta                          cuenta\n",
      "0          1                         Directo\n",
      "1          2                  Primas Cedidas\n",
      "2          3             Primas De Retención\n",
      "3          4  Primas de Retención Devengadas\n",
      "4          5       Costo Neto de Adquisición\n",
      "------------------------------------------------------------\n",
      "Operaciones\n",
      "   id_op   operacion\n",
      "0      1       Total\n",
      "1      2     Fianzas\n",
      "2      3   Fidelidad\n",
      "3      4  Individual\n",
      "4      5   Colectivo\n"
     ]
    }
   ],
   "source": [
    "print('Instituciones', instituciones.head(), sep = '\\n')\n",
    "print(60*'-')\n",
    "print('Trimestres', trimestres.head(), sep = '\\n')\n",
    "print(60*'-')\n",
    "print('Cuentas', cuentas.head(), sep = '\\n')\n",
    "print(60*'-')\n",
    "print('Operaciones', operaciones.head(), sep = '\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51d2cca7-1e99-478c-a916-55d010a999c4",
   "metadata": {},
   "source": [
    "## Incorporación de los identificadores\n",
    "Los famosos join's"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "21b6a733-8000-4fd8-aa32-ab63d982a175",
   "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>corte</th>\n",
       "      <th>nom_inst</th>\n",
       "      <th>desc</th>\n",
       "      <th>desc_op</th>\n",
       "      <th>importe</th>\n",
       "      <th>id_nom</th>\n",
       "      <th>nombre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-03-31</td>\n",
       "      <td>Crédito Afianzador</td>\n",
       "      <td>Directo</td>\n",
       "      <td>Total</td>\n",
       "      <td>17.68</td>\n",
       "      <td>1</td>\n",
       "      <td>Crédito Afianzador</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-06-30</td>\n",
       "      <td>Crédito Afianzador</td>\n",
       "      <td>Directo</td>\n",
       "      <td>Total</td>\n",
       "      <td>19.30</td>\n",
       "      <td>1</td>\n",
       "      <td>Crédito Afianzador</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>Crédito Afianzador</td>\n",
       "      <td>Directo</td>\n",
       "      <td>Total</td>\n",
       "      <td>21.39</td>\n",
       "      <td>1</td>\n",
       "      <td>Crédito Afianzador</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>Crédito Afianzador</td>\n",
       "      <td>Directo</td>\n",
       "      <td>Total</td>\n",
       "      <td>24.54</td>\n",
       "      <td>1</td>\n",
       "      <td>Crédito Afianzador</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-03-31</td>\n",
       "      <td>Crédito Afianzador</td>\n",
       "      <td>Directo</td>\n",
       "      <td>Total</td>\n",
       "      <td>17.77</td>\n",
       "      <td>1</td>\n",
       "      <td>Crédito Afianzador</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536892</th>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>Der Neue Horizont Re</td>\n",
       "      <td>Utilidad (Pérdida) de la Operación</td>\n",
       "      <td>Agrícola</td>\n",
       "      <td>-30.30</td>\n",
       "      <td>114</td>\n",
       "      <td>Der Neue Horizont Re</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536893</th>\n",
       "      <td>2023-03-31</td>\n",
       "      <td>Der Neue Horizont Re</td>\n",
       "      <td>Utilidad (Pérdida) de la Operación</td>\n",
       "      <td>Agrícola</td>\n",
       "      <td>-6.13</td>\n",
       "      <td>114</td>\n",
       "      <td>Der Neue Horizont Re</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536894</th>\n",
       "      <td>2023-06-30</td>\n",
       "      <td>Der Neue Horizont Re</td>\n",
       "      <td>Utilidad (Pérdida) de la Operación</td>\n",
       "      <td>Agrícola</td>\n",
       "      <td>-20.35</td>\n",
       "      <td>114</td>\n",
       "      <td>Der Neue Horizont Re</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536895</th>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>Der Neue Horizont Re</td>\n",
       "      <td>Utilidad (Pérdida) de la Operación</td>\n",
       "      <td>Agrícola</td>\n",
       "      <td>-28.50</td>\n",
       "      <td>114</td>\n",
       "      <td>Der Neue Horizont Re</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536896</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>Der Neue Horizont Re</td>\n",
       "      <td>Utilidad (Pérdida) de la Operación</td>\n",
       "      <td>Agrícola</td>\n",
       "      <td>-38.28</td>\n",
       "      <td>114</td>\n",
       "      <td>Der Neue Horizont Re</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>536897 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            corte              nom_inst                                desc  \\\n",
       "0      2016-03-31    Crédito Afianzador                             Directo   \n",
       "1      2016-06-30    Crédito Afianzador                             Directo   \n",
       "2      2016-09-30    Crédito Afianzador                             Directo   \n",
       "3      2016-12-31    Crédito Afianzador                             Directo   \n",
       "4      2017-03-31    Crédito Afianzador                             Directo   \n",
       "...           ...                   ...                                 ...   \n",
       "536892 2022-12-31  Der Neue Horizont Re  Utilidad (Pérdida) de la Operación   \n",
       "536893 2023-03-31  Der Neue Horizont Re  Utilidad (Pérdida) de la Operación   \n",
       "536894 2023-06-30  Der Neue Horizont Re  Utilidad (Pérdida) de la Operación   \n",
       "536895 2023-09-30  Der Neue Horizont Re  Utilidad (Pérdida) de la Operación   \n",
       "536896 2023-12-31  Der Neue Horizont Re  Utilidad (Pérdida) de la Operación   \n",
       "\n",
       "         desc_op  importe  id_nom                nombre  \n",
       "0          Total    17.68       1    Crédito Afianzador  \n",
       "1          Total    19.30       1    Crédito Afianzador  \n",
       "2          Total    21.39       1    Crédito Afianzador  \n",
       "3          Total    24.54       1    Crédito Afianzador  \n",
       "4          Total    17.77       1    Crédito Afianzador  \n",
       "...          ...      ...     ...                   ...  \n",
       "536892  Agrícola   -30.30     114  Der Neue Horizont Re  \n",
       "536893  Agrícola    -6.13     114  Der Neue Horizont Re  \n",
       "536894  Agrícola   -20.35     114  Der Neue Horizont Re  \n",
       "536895  Agrícola   -28.50     114  Der Neue Horizont Re  \n",
       "536896  Agrícola   -38.28     114  Der Neue Horizont Re  \n",
       "\n",
       "[536897 rows x 7 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.merge(instituciones, left_on = 'nom_inst', right_on = 'nombre', how = 'inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "a5ec7c4d-6d37-45f3-a704-aa7b6fe2a8d7",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>corte</th>\n",
       "      <th>nom_inst</th>\n",
       "      <th>desc</th>\n",
       "      <th>desc_op</th>\n",
       "      <th>importe</th>\n",
       "      <th>id_nom</th>\n",
       "      <th>nombre</th>\n",
       "      <th>id_trim</th>\n",
       "      <th>trimestre</th>\n",
       "      <th>id_cuenta</th>\n",
       "      <th>cuenta</th>\n",
       "      <th>id_op</th>\n",
       "      <th>operacion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-03-31</td>\n",
       "      <td>Crédito Afianzador</td>\n",
       "      <td>Directo</td>\n",
       "      <td>Total</td>\n",
       "      <td>17.68</td>\n",
       "      <td>1</td>\n",
       "      <td>Crédito Afianzador</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-03-31</td>\n",
       "      <td>1</td>\n",
       "      <td>Directo</td>\n",
       "      <td>1</td>\n",
       "      <td>Total</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-03-31</td>\n",
       "      <td>Fianzas Guardiana Inbursa</td>\n",
       "      <td>Directo</td>\n",
       "      <td>Total</td>\n",
       "      <td>496.03</td>\n",
       "      <td>2</td>\n",
       "      <td>Fianzas Guardiana Inbursa</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-03-31</td>\n",
       "      <td>1</td>\n",
       "      <td>Directo</td>\n",
       "      <td>1</td>\n",
       "      <td>Total</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-03-31</td>\n",
       "      <td>Liberty Fianzas</td>\n",
       "      <td>Directo</td>\n",
       "      <td>Total</td>\n",
       "      <td>111.25</td>\n",
       "      <td>3</td>\n",
       "      <td>Liberty Fianzas</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-03-31</td>\n",
       "      <td>1</td>\n",
       "      <td>Directo</td>\n",
       "      <td>1</td>\n",
       "      <td>Total</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-03-31</td>\n",
       "      <td>Fianzas Asecam</td>\n",
       "      <td>Directo</td>\n",
       "      <td>Total</td>\n",
       "      <td>10.07</td>\n",
       "      <td>4</td>\n",
       "      <td>Fianzas Asecam</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-03-31</td>\n",
       "      <td>1</td>\n",
       "      <td>Directo</td>\n",
       "      <td>1</td>\n",
       "      <td>Total</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-03-31</td>\n",
       "      <td>Afianzadora CBL Fiducia</td>\n",
       "      <td>Directo</td>\n",
       "      <td>Total</td>\n",
       "      <td>11.29</td>\n",
       "      <td>5</td>\n",
       "      <td>Afianzadora CBL Fiducia</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-03-31</td>\n",
       "      <td>1</td>\n",
       "      <td>Directo</td>\n",
       "      <td>1</td>\n",
       "      <td>Total</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536892</th>\n",
       "      <td>2022-03-31</td>\n",
       "      <td>Chubb Fianzas Monterrey</td>\n",
       "      <td>Utilidad (Pérdida) de la Operación</td>\n",
       "      <td>De Crédito</td>\n",
       "      <td>-0.03</td>\n",
       "      <td>107</td>\n",
       "      <td>Chubb Fianzas Monterrey</td>\n",
       "      <td>25</td>\n",
       "      <td>2022-03-31</td>\n",
       "      <td>13</td>\n",
       "      <td>Utilidad (Pérdida) de la Operación</td>\n",
       "      <td>69</td>\n",
       "      <td>De Crédito</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536893</th>\n",
       "      <td>2022-06-30</td>\n",
       "      <td>Chubb Fianzas Monterrey</td>\n",
       "      <td>Utilidad (Pérdida) de la Operación</td>\n",
       "      <td>De Crédito</td>\n",
       "      <td>-0.04</td>\n",
       "      <td>107</td>\n",
       "      <td>Chubb Fianzas Monterrey</td>\n",
       "      <td>26</td>\n",
       "      <td>2022-06-30</td>\n",
       "      <td>13</td>\n",
       "      <td>Utilidad (Pérdida) de la Operación</td>\n",
       "      <td>69</td>\n",
       "      <td>De Crédito</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536894</th>\n",
       "      <td>2022-09-30</td>\n",
       "      <td>Chubb Fianzas Monterrey</td>\n",
       "      <td>Utilidad (Pérdida) de la Operación</td>\n",
       "      <td>De Crédito</td>\n",
       "      <td>-0.07</td>\n",
       "      <td>107</td>\n",
       "      <td>Chubb Fianzas Monterrey</td>\n",
       "      <td>27</td>\n",
       "      <td>2022-09-30</td>\n",
       "      <td>13</td>\n",
       "      <td>Utilidad (Pérdida) de la Operación</td>\n",
       "      <td>69</td>\n",
       "      <td>De Crédito</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536895</th>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>Chubb Fianzas Monterrey</td>\n",
       "      <td>Utilidad (Pérdida) de la Operación</td>\n",
       "      <td>De Crédito</td>\n",
       "      <td>-0.08</td>\n",
       "      <td>107</td>\n",
       "      <td>Chubb Fianzas Monterrey</td>\n",
       "      <td>28</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>13</td>\n",
       "      <td>Utilidad (Pérdida) de la Operación</td>\n",
       "      <td>69</td>\n",
       "      <td>De Crédito</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536896</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>Aseguradora Aserta</td>\n",
       "      <td>Utilidad (Pérdida) de la Operación</td>\n",
       "      <td>De Crédito</td>\n",
       "      <td>-0.44</td>\n",
       "      <td>104</td>\n",
       "      <td>Aseguradora Aserta</td>\n",
       "      <td>32</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>13</td>\n",
       "      <td>Utilidad (Pérdida) de la Operación</td>\n",
       "      <td>69</td>\n",
       "      <td>De Crédito</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>536897 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            corte                   nom_inst  \\\n",
       "0      2016-03-31         Crédito Afianzador   \n",
       "1      2016-03-31  Fianzas Guardiana Inbursa   \n",
       "2      2016-03-31            Liberty Fianzas   \n",
       "3      2016-03-31             Fianzas Asecam   \n",
       "4      2016-03-31    Afianzadora CBL Fiducia   \n",
       "...           ...                        ...   \n",
       "536892 2022-03-31    Chubb Fianzas Monterrey   \n",
       "536893 2022-06-30    Chubb Fianzas Monterrey   \n",
       "536894 2022-09-30    Chubb Fianzas Monterrey   \n",
       "536895 2022-12-31    Chubb Fianzas Monterrey   \n",
       "536896 2023-12-31         Aseguradora Aserta   \n",
       "\n",
       "                                      desc     desc_op  importe  id_nom  \\\n",
       "0                                  Directo       Total    17.68       1   \n",
       "1                                  Directo       Total   496.03       2   \n",
       "2                                  Directo       Total   111.25       3   \n",
       "3                                  Directo       Total    10.07       4   \n",
       "4                                  Directo       Total    11.29       5   \n",
       "...                                    ...         ...      ...     ...   \n",
       "536892  Utilidad (Pérdida) de la Operación  De Crédito    -0.03     107   \n",
       "536893  Utilidad (Pérdida) de la Operación  De Crédito    -0.04     107   \n",
       "536894  Utilidad (Pérdida) de la Operación  De Crédito    -0.07     107   \n",
       "536895  Utilidad (Pérdida) de la Operación  De Crédito    -0.08     107   \n",
       "536896  Utilidad (Pérdida) de la Operación  De Crédito    -0.44     104   \n",
       "\n",
       "                           nombre  id_trim  trimestre  id_cuenta  \\\n",
       "0              Crédito Afianzador        1 2016-03-31          1   \n",
       "1       Fianzas Guardiana Inbursa        1 2016-03-31          1   \n",
       "2                 Liberty Fianzas        1 2016-03-31          1   \n",
       "3                  Fianzas Asecam        1 2016-03-31          1   \n",
       "4         Afianzadora CBL Fiducia        1 2016-03-31          1   \n",
       "...                           ...      ...        ...        ...   \n",
       "536892    Chubb Fianzas Monterrey       25 2022-03-31         13   \n",
       "536893    Chubb Fianzas Monterrey       26 2022-06-30         13   \n",
       "536894    Chubb Fianzas Monterrey       27 2022-09-30         13   \n",
       "536895    Chubb Fianzas Monterrey       28 2022-12-31         13   \n",
       "536896         Aseguradora Aserta       32 2023-12-31         13   \n",
       "\n",
       "                                    cuenta  id_op   operacion  \n",
       "0                                  Directo      1       Total  \n",
       "1                                  Directo      1       Total  \n",
       "2                                  Directo      1       Total  \n",
       "3                                  Directo      1       Total  \n",
       "4                                  Directo      1       Total  \n",
       "...                                    ...    ...         ...  \n",
       "536892  Utilidad (Pérdida) de la Operación     69  De Crédito  \n",
       "536893  Utilidad (Pérdida) de la Operación     69  De Crédito  \n",
       "536894  Utilidad (Pérdida) de la Operación     69  De Crédito  \n",
       "536895  Utilidad (Pérdida) de la Operación     69  De Crédito  \n",
       "536896  Utilidad (Pérdida) de la Operación     69  De Crédito  \n",
       "\n",
       "[536897 rows x 13 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.\\\n",
    "    merge(instituciones, left_on = 'nom_inst', right_on = 'nombre', how = 'inner').\\\n",
    "    merge(trimestres, left_on = 'corte', right_on = 'trimestre', how = 'inner').\\\n",
    "    merge(cuentas, left_on = 'desc', right_on = 'cuenta', how = 'inner').\\\n",
    "    merge(operaciones, left_on = 'desc_op', right_on = 'operacion', how = 'inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "868fd957-86e3-4c61-ae06-03d06ccd8665",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_trim</th>\n",
       "      <th>id_nom</th>\n",
       "      <th>id_cuenta</th>\n",
       "      <th>id_op</th>\n",
       "      <th>importe</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>17.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>496.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>111.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>10.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>11.29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_trim  id_nom  id_cuenta  id_op  importe\n",
       "0        1       1          1      1    17.68\n",
       "1        1       2          1      1   496.03\n",
       "2        1       3          1      1   111.25\n",
       "3        1       4          1      1    10.07\n",
       "4        1       5          1      1    11.29"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.\\\n",
    "        merge(instituciones, left_on = 'nom_inst', right_on = 'nombre', how = 'inner').\\\n",
    "        merge(trimestres, left_on = 'corte', right_on = 'trimestre', how = 'inner').\\\n",
    "        merge(cuentas, left_on = 'desc', right_on = 'cuenta', how = 'inner').\\\n",
    "        merge(operaciones, left_on = 'desc_op', right_on = 'operacion', how = 'inner')\n",
    "df = df[['id_trim', 'id_nom', 'id_cuenta', 'id_op', 'importe']]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "0efe2d8b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 536897 entries, 0 to 536896\n",
      "Data columns (total 5 columns):\n",
      " #   Column     Non-Null Count   Dtype  \n",
      "---  ------     --------------   -----  \n",
      " 0   id_trim    536897 non-null  int64  \n",
      " 1   id_nom     536897 non-null  int64  \n",
      " 2   id_cuenta  536897 non-null  int64  \n",
      " 3   id_op      536897 non-null  int64  \n",
      " 4   importe    536897 non-null  float64\n",
      "dtypes: float64(1), int64(4)\n",
      "memory usage: 24.6 MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbb2a2c3-6fe7-4003-b6aa-0a187ca5af2e",
   "metadata": {},
   "source": [
    "¿Por qué esto nos reduce tamaño?\n",
    "\n",
    "¿Nos reduce tamaño?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f8558bd-ba4c-42aa-8965-a5b6834bcc77",
   "metadata": {},
   "source": [
    "## Definición de ruta de guardado"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "b9fbc12d-3e51-4d12-ae88-86b39d1a9bad",
   "metadata": {},
   "outputs": [],
   "source": [
    "save_dir = r'C:\\Users\\ed_22\\Documents\\SIO\\Procesados'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e30a955-ee8d-4795-a80d-4b1e04bebddf",
   "metadata": {},
   "source": [
    "# Exportamos bases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "d351cb26-cfa2-405e-a16e-356b14cad53a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# base completa\n",
    "save_path = os.path.join(save_dir, 'dic2023.xlsx')\n",
    "df.to_excel(save_path, index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "1f8a92e5-b911-4345-b9fb-c514381d8b7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# trimestre\n",
    "save_path = os.path.join(save_dir, 'trimestres.xlsx')\n",
    "trimestres.columns = ['id', 'nombre']\n",
    "trimestres.to_excel(save_path, index = False)\n",
    "# instituciones\n",
    "save_path = os.path.join(save_dir, 'instituciones.xlsx')\n",
    "instituciones.columns = ['id', 'nombre']\n",
    "instituciones.to_excel(save_path, index = False)\n",
    "# cuentas\n",
    "save_path = os.path.join(save_dir, 'cuentas.xlsx')\n",
    "cuentas.columns = ['id', 'nombre']\n",
    "cuentas.to_excel(save_path, index = False)\n",
    "# operaciones\n",
    "save_path = os.path.join(save_dir, 'operaciones.xlsx')\n",
    "operaciones.columns = ['id', 'nombre']\n",
    "operaciones.to_excel(save_path, index = False)"
   ]
  }
 ],
 "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}