03. Data Adicional.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 03. Data Complementaria al Proyecto\n",
    "### Breve descripción del notebook\n",
    "En este notebook vas a encontrar todo el desarrollo de la carga y manipulación de los dataset obtenidos de la página de Estadísticas y Censos de la Cuidad de Buenos aires.<br>\n",
    "El propósito de es notebook es limpiar y corregir y transformar la información disponible para ser luego utilizada en el Dashboard del proyecto para el cálculo de KPIS y métricas que nos permitan usar esa información en el análisis y desarrollo del proyecto \n",
    "<br>\n",
    "Comenzamos:\n",
    "\n",
    "# 3.1. Cargamos las librerías necesarias"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import scipy\n",
    "\n",
    "%load_ext autoreload\n",
    "%autoreload 2\n",
    "import funciones\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3.2 Cargamos los datasets disponibles a varios dataframes de pandas para luego limpiarlos, y combinarlos para obtener las tablas que nos serán útiles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cargamos el dataset con sus diferentes páginas a pandas\n",
    "sheets_to_load = [\"2025\", \"2024\",\"2023\", \"2022\",\"2021\", \"2020\",\"2019\", \"2018\",\"2017\", \"2016\"]\n",
    "# Cargamos las diferentes hojas del excel a un diccionario \n",
    "sheets_data = pd.read_excel(r'data\\PBP_CO_T_1025.xls ', sheet_name=sheets_to_load)\n",
    "\n",
    "# Accedemos individualmente a los DataFrames usando el nombre de la pagina como fuente\n",
    "d_25 = sheets_data[\"2025\"]\n",
    "d_24 = sheets_data[\"2024\"]\n",
    "d_23 = sheets_data[\"2023\"]\n",
    "d_22 = sheets_data[\"2022\"]\n",
    "d_21 = sheets_data[\"2021\"]\n",
    "d_20 = sheets_data[\"2020\"]\n",
    "d_19 = sheets_data[\"2019\"]\n",
    "d_18 = sheets_data[\"2018\"]\n",
    "d_17 = sheets_data[\"2017\"]\n",
    "d_16 = sheets_data[\"2016\"]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Año</th>\n",
       "      <th>Edad</th>\n",
       "      <th>Total</th>\n",
       "      <th>Varones</th>\n",
       "      <th>Mujeres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2025</td>\n",
       "      <td>0</td>\n",
       "      <td>38465.3904</td>\n",
       "      <td>19809.808</td>\n",
       "      <td>18655.5824</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2025</td>\n",
       "      <td>1</td>\n",
       "      <td>38729.7200</td>\n",
       "      <td>19942.096</td>\n",
       "      <td>18787.6240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2025</td>\n",
       "      <td>2</td>\n",
       "      <td>38931.4240</td>\n",
       "      <td>20042.472</td>\n",
       "      <td>18888.9520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2025</td>\n",
       "      <td>3</td>\n",
       "      <td>39077.5040</td>\n",
       "      <td>20114.376</td>\n",
       "      <td>18963.1280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2025</td>\n",
       "      <td>4</td>\n",
       "      <td>39174.9616</td>\n",
       "      <td>20161.248</td>\n",
       "      <td>19013.7136</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Año Edad       Total    Varones     Mujeres\n",
       "0  2025    0  38465.3904  19809.808  18655.5824\n",
       "1  2025    1  38729.7200  19942.096  18787.6240\n",
       "2  2025    2  38931.4240  20042.472  18888.9520\n",
       "3  2025    3  39077.5040  20114.376  18963.1280\n",
       "4  2025    4  39174.9616  20161.248  19013.7136"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d_25.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Año</th>\n",
       "      <th>Edad</th>\n",
       "      <th>Total</th>\n",
       "      <th>Varones</th>\n",
       "      <th>Mujeres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021</td>\n",
       "      <td>0</td>\n",
       "      <td>40225.4512</td>\n",
       "      <td>20736.6400</td>\n",
       "      <td>19488.8112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021</td>\n",
       "      <td>1</td>\n",
       "      <td>40404.0720</td>\n",
       "      <td>20808.0480</td>\n",
       "      <td>19596.0240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021</td>\n",
       "      <td>2</td>\n",
       "      <td>40515.3360</td>\n",
       "      <td>20851.0160</td>\n",
       "      <td>19664.3200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021</td>\n",
       "      <td>3</td>\n",
       "      <td>40566.4640</td>\n",
       "      <td>20868.1680</td>\n",
       "      <td>19698.2960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021</td>\n",
       "      <td>4</td>\n",
       "      <td>40564.6768</td>\n",
       "      <td>20862.1280</td>\n",
       "      <td>19702.5488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>705</th>\n",
       "      <td>2020</td>\n",
       "      <td>66</td>\n",
       "      <td>28358.2384</td>\n",
       "      <td>12014.9056</td>\n",
       "      <td>16343.3328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>706</th>\n",
       "      <td>2020</td>\n",
       "      <td>67</td>\n",
       "      <td>27675.8784</td>\n",
       "      <td>11622.7536</td>\n",
       "      <td>16053.1248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>707</th>\n",
       "      <td>2020</td>\n",
       "      <td>68</td>\n",
       "      <td>27086.8064</td>\n",
       "      <td>11270.2736</td>\n",
       "      <td>15816.5328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>708</th>\n",
       "      <td>2020</td>\n",
       "      <td>69</td>\n",
       "      <td>26537.7376</td>\n",
       "      <td>10935.6048</td>\n",
       "      <td>15602.1328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709</th>\n",
       "      <td>2020</td>\n",
       "      <td>70 y más</td>\n",
       "      <td>366700.0000</td>\n",
       "      <td>130039.0000</td>\n",
       "      <td>236661.0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>710 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Año      Edad        Total      Varones      Mujeres\n",
       "0    2021         0   40225.4512   20736.6400   19488.8112\n",
       "1    2021         1   40404.0720   20808.0480   19596.0240\n",
       "2    2021         2   40515.3360   20851.0160   19664.3200\n",
       "3    2021         3   40566.4640   20868.1680   19698.2960\n",
       "4    2021         4   40564.6768   20862.1280   19702.5488\n",
       "..    ...       ...          ...          ...          ...\n",
       "705  2020        66   28358.2384   12014.9056   16343.3328\n",
       "706  2020        67   27675.8784   11622.7536   16053.1248\n",
       "707  2020        68   27086.8064   11270.2736   15816.5328\n",
       "708  2020        69   26537.7376   10935.6048   15602.1328\n",
       "709  2020  70 y más  366700.0000  130039.0000  236661.0000\n",
       "\n",
       "[710 rows x 5 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Voy a uir todos los  DataFrames en uno solo llamado poblacion_total\n",
    "ds = [d_21, d_22, d_23, d_24, d_25, d_16, d_17,d_18,d_19, d_20]\n",
    "\n",
    "# Concatenar todos los DataFrames \n",
    "poblacion_total = pd.concat(ds, ignore_index=True)\n",
    "\n",
    "poblacion_total"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 710 entries, 0 to 709\n",
      "Data columns (total 5 columns):\n",
      " #   Column   Non-Null Count  Dtype  \n",
      "---  ------   --------------  -----  \n",
      " 0   Año      710 non-null    int64  \n",
      " 1   Edad     710 non-null    object \n",
      " 2   Total    710 non-null    float64\n",
      " 3   Varones  710 non-null    float64\n",
      " 4   Mujeres  710 non-null    float64\n",
      "dtypes: float64(3), int64(1), object(1)\n",
      "memory usage: 27.9+ KB\n"
     ]
    }
   ],
   "source": [
    "poblacion_total.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Año</th>\n",
       "      <th>Edad</th>\n",
       "      <th>Total</th>\n",
       "      <th>Varones</th>\n",
       "      <th>Mujeres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021</td>\n",
       "      <td>0</td>\n",
       "      <td>40225</td>\n",
       "      <td>20736</td>\n",
       "      <td>19488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021</td>\n",
       "      <td>1</td>\n",
       "      <td>40404</td>\n",
       "      <td>20808</td>\n",
       "      <td>19596</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021</td>\n",
       "      <td>2</td>\n",
       "      <td>40515</td>\n",
       "      <td>20851</td>\n",
       "      <td>19664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021</td>\n",
       "      <td>3</td>\n",
       "      <td>40566</td>\n",
       "      <td>20868</td>\n",
       "      <td>19698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021</td>\n",
       "      <td>4</td>\n",
       "      <td>40564</td>\n",
       "      <td>20862</td>\n",
       "      <td>19702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>705</th>\n",
       "      <td>2020</td>\n",
       "      <td>66</td>\n",
       "      <td>28358</td>\n",
       "      <td>12014</td>\n",
       "      <td>16343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>706</th>\n",
       "      <td>2020</td>\n",
       "      <td>67</td>\n",
       "      <td>27675</td>\n",
       "      <td>11622</td>\n",
       "      <td>16053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>707</th>\n",
       "      <td>2020</td>\n",
       "      <td>68</td>\n",
       "      <td>27086</td>\n",
       "      <td>11270</td>\n",
       "      <td>15816</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>708</th>\n",
       "      <td>2020</td>\n",
       "      <td>69</td>\n",
       "      <td>26537</td>\n",
       "      <td>10935</td>\n",
       "      <td>15602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709</th>\n",
       "      <td>2020</td>\n",
       "      <td>70</td>\n",
       "      <td>366700</td>\n",
       "      <td>130039</td>\n",
       "      <td>236661</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>710 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Año  Edad   Total  Varones  Mujeres\n",
       "0    2021     0   40225    20736    19488\n",
       "1    2021     1   40404    20808    19596\n",
       "2    2021     2   40515    20851    19664\n",
       "3    2021     3   40566    20868    19698\n",
       "4    2021     4   40564    20862    19702\n",
       "..    ...   ...     ...      ...      ...\n",
       "705  2020    66   28358    12014    16343\n",
       "706  2020    67   27675    11622    16053\n",
       "707  2020    68   27086    11270    15816\n",
       "708  2020    69   26537    10935    15602\n",
       "709  2020    70  366700   130039   236661\n",
       "\n",
       "[710 rows x 5 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reemplazamos  '70 y más' en edad por 70 para que nos quede como int\n",
    "poblacion_total['Edad'] = poblacion_total['Edad'].replace('70 y más', 70)\n",
    "# Convertir todas las columnas a  integer datatype\n",
    "poblacion_total = poblacion_total.astype(int)\n",
    "poblacion_total"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Año', 'Edad', 'Total', 'Varones', 'Mujeres'], dtype='object')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "poblacion_total.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Vamos a transformar el dataframe para que nos sea util cuando lo importemos a Power BI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Año</th>\n",
       "      <th>Edad</th>\n",
       "      <th>Total</th>\n",
       "      <th>Total_Parcial</th>\n",
       "      <th>genero</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016</td>\n",
       "      <td>17</td>\n",
       "      <td>36705</td>\n",
       "      <td>18224</td>\n",
       "      <td>mujeres</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016</td>\n",
       "      <td>31</td>\n",
       "      <td>47487</td>\n",
       "      <td>24349</td>\n",
       "      <td>mujeres</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016</td>\n",
       "      <td>30</td>\n",
       "      <td>47316</td>\n",
       "      <td>24263</td>\n",
       "      <td>mujeres</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016</td>\n",
       "      <td>29</td>\n",
       "      <td>47061</td>\n",
       "      <td>24133</td>\n",
       "      <td>mujeres</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016</td>\n",
       "      <td>28</td>\n",
       "      <td>46768</td>\n",
       "      <td>23984</td>\n",
       "      <td>mujeres</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1415</th>\n",
       "      <td>2025</td>\n",
       "      <td>49</td>\n",
       "      <td>42339</td>\n",
       "      <td>20255</td>\n",
       "      <td>varones</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1416</th>\n",
       "      <td>2025</td>\n",
       "      <td>50</td>\n",
       "      <td>41592</td>\n",
       "      <td>19825</td>\n",
       "      <td>varones</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1417</th>\n",
       "      <td>2025</td>\n",
       "      <td>51</td>\n",
       "      <td>40863</td>\n",
       "      <td>19399</td>\n",
       "      <td>varones</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1418</th>\n",
       "      <td>2025</td>\n",
       "      <td>45</td>\n",
       "      <td>43952</td>\n",
       "      <td>21226</td>\n",
       "      <td>varones</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1419</th>\n",
       "      <td>2025</td>\n",
       "      <td>53</td>\n",
       "      <td>38394</td>\n",
       "      <td>20321</td>\n",
       "      <td>mujeres</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1420 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Año  Edad  Total  Total_Parcial   genero\n",
       "0     2016    17  36705          18224  mujeres\n",
       "1     2016    31  47487          24349  mujeres\n",
       "2     2016    30  47316          24263  mujeres\n",
       "3     2016    29  47061          24133  mujeres\n",
       "4     2016    28  46768          23984  mujeres\n",
       "...    ...   ...    ...            ...      ...\n",
       "1415  2025    49  42339          20255  varones\n",
       "1416  2025    50  41592          19825  varones\n",
       "1417  2025    51  40863          19399  varones\n",
       "1418  2025    45  43952          21226  varones\n",
       "1419  2025    53  38394          20321  mujeres\n",
       "\n",
       "[1420 rows x 5 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Separamos el dtaframe en dos DataFrame por genero\n",
    "male_df = poblacion_total[['Año', 'Edad', 'Total', 'Varones']].copy()\n",
    "female_df = poblacion_total[['Año', 'Edad', 'Total', 'Mujeres']].copy()\n",
    "\n",
    "# Agregamos a cada uno una columna genero\n",
    "male_df['genero'] = 'varones'\n",
    "female_df['genero'] = 'mujeres'\n",
    "\n",
    "# Renombramos las columns\n",
    "male_df.rename(columns={'Varones': 'Total_Parcial'}, inplace=True)\n",
    "female_df.rename(columns={'Mujeres': 'Total_Parcial'}, inplace=True)\n",
    "\n",
    "# Unimos de nuevo ambos DataFrames\n",
    "poblacion_total = pd.concat([male_df, female_df])\n",
    "\n",
    "# Hacemos un Sort del DataFrame por 'Año'\n",
    "poblacion_total.sort_values(by='Año', inplace=True)\n",
    "\n",
    "poblacion_total.reset_index(drop=True, inplace=True)\n",
    "poblacion_total"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Año', 'Edad', 'Total', 'Total_Parcial', 'genero'], dtype='object')"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "poblacion_total.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3.3 Creamos nuestra primer tabla: poblacion_total_anio\n",
    "Esta tabla contiene los datos de la cantidad de población de la Cuidad de Buenos Aires total por año ."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Año</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016</td>\n",
       "      <td>3059052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>3063657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018</td>\n",
       "      <td>3067971</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019</td>\n",
       "      <td>3071960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020</td>\n",
       "      <td>3075573</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2021</td>\n",
       "      <td>3078774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2022</td>\n",
       "      <td>3081483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2023</td>\n",
       "      <td>3083694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2024</td>\n",
       "      <td>3085411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2025</td>\n",
       "      <td>3086609</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Año    Total\n",
       "0  2016  3059052\n",
       "1  2017  3063657\n",
       "2  2018  3067971\n",
       "3  2019  3071960\n",
       "4  2020  3075573\n",
       "5  2021  3078774\n",
       "6  2022  3081483\n",
       "7  2023  3083694\n",
       "8  2024  3085411\n",
       "9  2025  3086609"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "poblacion_total_anio = poblacion_total[['Año','Edad','Total_Parcial']]\n",
    "# Creamos una table pivot para el total de la poblacion por año\n",
    "poblacion_total_anio = poblacion_total_anio.pivot_table(index='Año',aggfunc='sum')\n",
    "poblacion_total_anio.drop(['Edad'], axis=1,inplace=True)\n",
    "# Reseteamos el index del dataframe agrupado para que 'Año' sea una columna regular\n",
    "poblacion_total_anio.reset_index(inplace=True)\n",
    "poblacion_total_anio.rename(columns={'Total_Parcial': 'Total'}, inplace=True)\n",
    "poblacion_total_anio"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3.4 Creamos nuestra primer tabla: poblacion_total_anio_genero\n",
    "Esta tabla contiene los datos de la cantidad de población de la Cuidad de Buenos Aires por año y distinguiendo por género y luego el total."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Edad</th>\n",
       "      <th>Año</th>\n",
       "      <th>genero</th>\n",
       "      <th>Total_Parcial</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016</td>\n",
       "      <td>mujeres</td>\n",
       "      <td>1628557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016</td>\n",
       "      <td>varones</td>\n",
       "      <td>1430495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>mujeres</td>\n",
       "      <td>1629370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>varones</td>\n",
       "      <td>1434287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018</td>\n",
       "      <td>mujeres</td>\n",
       "      <td>1630070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018</td>\n",
       "      <td>varones</td>\n",
       "      <td>1437901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019</td>\n",
       "      <td>mujeres</td>\n",
       "      <td>1630643</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019</td>\n",
       "      <td>varones</td>\n",
       "      <td>1441317</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2020</td>\n",
       "      <td>mujeres</td>\n",
       "      <td>1631063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2020</td>\n",
       "      <td>varones</td>\n",
       "      <td>1444510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2021</td>\n",
       "      <td>mujeres</td>\n",
       "      <td>1631308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2021</td>\n",
       "      <td>varones</td>\n",
       "      <td>1447466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2022</td>\n",
       "      <td>mujeres</td>\n",
       "      <td>1631339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2022</td>\n",
       "      <td>varones</td>\n",
       "      <td>1450144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2023</td>\n",
       "      <td>mujeres</td>\n",
       "      <td>1631144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2023</td>\n",
       "      <td>varones</td>\n",
       "      <td>1452550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2024</td>\n",
       "      <td>mujeres</td>\n",
       "      <td>1630731</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2024</td>\n",
       "      <td>varones</td>\n",
       "      <td>1454680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2025</td>\n",
       "      <td>mujeres</td>\n",
       "      <td>1630087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2025</td>\n",
       "      <td>varones</td>\n",
       "      <td>1456522</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Edad   Año   genero  Total_Parcial\n",
       "0     2016  mujeres        1628557\n",
       "1     2016  varones        1430495\n",
       "2     2017  mujeres        1629370\n",
       "3     2017  varones        1434287\n",
       "4     2018  mujeres        1630070\n",
       "5     2018  varones        1437901\n",
       "6     2019  mujeres        1630643\n",
       "7     2019  varones        1441317\n",
       "8     2020  mujeres        1631063\n",
       "9     2020  varones        1444510\n",
       "10    2021  mujeres        1631308\n",
       "11    2021  varones        1447466\n",
       "12    2022  mujeres        1631339\n",
       "13    2022  varones        1450144\n",
       "14    2023  mujeres        1631144\n",
       "15    2023  varones        1452550\n",
       "16    2024  mujeres        1630731\n",
       "17    2024  varones        1454680\n",
       "18    2025  mujeres        1630087\n",
       "19    2025  varones        1456522"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Agrupamos por 'Año', 'Edad', y  'genero' y luego sumamos en 'Total_Parcial'\n",
    "poblacion_total_anio_genero = poblacion_total.groupby(['Año', 'Edad', 'genero']).agg({'Total_Parcial': 'sum'}).reset_index()\n",
    "\n",
    "# Creamos una tabla Pivot del DataFrame para que 'Año' sea el index, 'Edad' columna, y la suma en 'Total_Parcial' para cada 'genero'  los valores\n",
    "poblacion_total_anio_genero = poblacion_total_anio_genero.pivot_table(index=['Año', 'genero'], columns='Edad', values='Total_Parcial', aggfunc='sum').reset_index()\n",
    "\n",
    "# Sumamos por 'Edad'  para obtener  'Total_Parcial'\n",
    "poblacion_total_anio_genero['Total_Parcial'] = poblacion_total_anio_genero.iloc[:, 2:].sum(axis=1)\n",
    "\n",
    "# Eliminamos las columnas de Edad \n",
    "poblacion_total_anio_genero.drop(columns=range(0, 71), inplace=True)\n",
    "\n",
    "poblacion_total_anio_genero"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Año', 'genero', 'Total_Parcial'], dtype='object', name='Edad')"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "poblacion_total_anio_genero.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3.5 Creamos una nueva tabla por Rango Etario. \n",
    "Vamos a crear una segunda tabla con la información de la cantidad de población por rango etario<br>\n",
    "Siendo los rangos:\n",
    "- 0 a 17 años\n",
    "- 18 a 29 años\n",
    "- 30 a 49 años\n",
    "- 50 a 64 años\n",
    "- mas de 65 años"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Age_range</th>\n",
       "      <th>Año</th>\n",
       "      <th>Total_Parcial</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0-17</td>\n",
       "      <td>2016</td>\n",
       "      <td>685901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0-17</td>\n",
       "      <td>2017</td>\n",
       "      <td>686612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0-17</td>\n",
       "      <td>2018</td>\n",
       "      <td>686201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0-17</td>\n",
       "      <td>2019</td>\n",
       "      <td>684787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0-17</td>\n",
       "      <td>2020</td>\n",
       "      <td>682620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0-17</td>\n",
       "      <td>2021</td>\n",
       "      <td>679982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0-17</td>\n",
       "      <td>2022</td>\n",
       "      <td>676974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0-17</td>\n",
       "      <td>2023</td>\n",
       "      <td>673208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0-17</td>\n",
       "      <td>2024</td>\n",
       "      <td>668873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0-17</td>\n",
       "      <td>2025</td>\n",
       "      <td>664194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>18-29</td>\n",
       "      <td>2016</td>\n",
       "      <td>461384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>18-29</td>\n",
       "      <td>2017</td>\n",
       "      <td>453082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>18-29</td>\n",
       "      <td>2018</td>\n",
       "      <td>445569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>18-29</td>\n",
       "      <td>2019</td>\n",
       "      <td>439979</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>18-29</td>\n",
       "      <td>2020</td>\n",
       "      <td>437027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>18-29</td>\n",
       "      <td>2021</td>\n",
       "      <td>434559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>18-29</td>\n",
       "      <td>2022</td>\n",
       "      <td>432700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18-29</td>\n",
       "      <td>2023</td>\n",
       "      <td>432185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>18-29</td>\n",
       "      <td>2024</td>\n",
       "      <td>432592</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>18-29</td>\n",
       "      <td>2025</td>\n",
       "      <td>433067</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>30-49</td>\n",
       "      <td>2016</td>\n",
       "      <td>829382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>30-49</td>\n",
       "      <td>2017</td>\n",
       "      <td>838183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>30-49</td>\n",
       "      <td>2018</td>\n",
       "      <td>845599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>30-49</td>\n",
       "      <td>2019</td>\n",
       "      <td>850006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>30-49</td>\n",
       "      <td>2020</td>\n",
       "      <td>850295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>30-49</td>\n",
       "      <td>2021</td>\n",
       "      <td>848797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>30-49</td>\n",
       "      <td>2022</td>\n",
       "      <td>845484</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>30-49</td>\n",
       "      <td>2023</td>\n",
       "      <td>840627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>30-49</td>\n",
       "      <td>2024</td>\n",
       "      <td>834658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>30-49</td>\n",
       "      <td>2025</td>\n",
       "      <td>828168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>50-65</td>\n",
       "      <td>2016</td>\n",
       "      <td>475621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>50-65</td>\n",
       "      <td>2017</td>\n",
       "      <td>475500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>50-65</td>\n",
       "      <td>2018</td>\n",
       "      <td>475849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>50-65</td>\n",
       "      <td>2019</td>\n",
       "      <td>477114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>50-65</td>\n",
       "      <td>2020</td>\n",
       "      <td>479620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>50-65</td>\n",
       "      <td>2021</td>\n",
       "      <td>483392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>50-65</td>\n",
       "      <td>2022</td>\n",
       "      <td>488285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>50-65</td>\n",
       "      <td>2023</td>\n",
       "      <td>494266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>50-65</td>\n",
       "      <td>2024</td>\n",
       "      <td>501204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>50-65</td>\n",
       "      <td>2025</td>\n",
       "      <td>508966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>66+</td>\n",
       "      <td>2016</td>\n",
       "      <td>606764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>66+</td>\n",
       "      <td>2017</td>\n",
       "      <td>610280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>66+</td>\n",
       "      <td>2018</td>\n",
       "      <td>614753</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>66+</td>\n",
       "      <td>2019</td>\n",
       "      <td>620074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>66+</td>\n",
       "      <td>2020</td>\n",
       "      <td>626011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>66+</td>\n",
       "      <td>2021</td>\n",
       "      <td>632044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>66+</td>\n",
       "      <td>2022</td>\n",
       "      <td>638040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>66+</td>\n",
       "      <td>2023</td>\n",
       "      <td>643408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>66+</td>\n",
       "      <td>2024</td>\n",
       "      <td>648084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>66+</td>\n",
       "      <td>2025</td>\n",
       "      <td>652214</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Age_range   Año  Total_Parcial\n",
       "0       0-17  2016         685901\n",
       "1       0-17  2017         686612\n",
       "2       0-17  2018         686201\n",
       "3       0-17  2019         684787\n",
       "4       0-17  2020         682620\n",
       "5       0-17  2021         679982\n",
       "6       0-17  2022         676974\n",
       "7       0-17  2023         673208\n",
       "8       0-17  2024         668873\n",
       "9       0-17  2025         664194\n",
       "10     18-29  2016         461384\n",
       "11     18-29  2017         453082\n",
       "12     18-29  2018         445569\n",
       "13     18-29  2019         439979\n",
       "14     18-29  2020         437027\n",
       "15     18-29  2021         434559\n",
       "16     18-29  2022         432700\n",
       "17     18-29  2023         432185\n",
       "18     18-29  2024         432592\n",
       "19     18-29  2025         433067\n",
       "20     30-49  2016         829382\n",
       "21     30-49  2017         838183\n",
       "22     30-49  2018         845599\n",
       "23     30-49  2019         850006\n",
       "24     30-49  2020         850295\n",
       "25     30-49  2021         848797\n",
       "26     30-49  2022         845484\n",
       "27     30-49  2023         840627\n",
       "28     30-49  2024         834658\n",
       "29     30-49  2025         828168\n",
       "30     50-65  2016         475621\n",
       "31     50-65  2017         475500\n",
       "32     50-65  2018         475849\n",
       "33     50-65  2019         477114\n",
       "34     50-65  2020         479620\n",
       "35     50-65  2021         483392\n",
       "36     50-65  2022         488285\n",
       "37     50-65  2023         494266\n",
       "38     50-65  2024         501204\n",
       "39     50-65  2025         508966\n",
       "40       66+  2016         606764\n",
       "41       66+  2017         610280\n",
       "42       66+  2018         614753\n",
       "43       66+  2019         620074\n",
       "44       66+  2020         626011\n",
       "45       66+  2021         632044\n",
       "46       66+  2022         638040\n",
       "47       66+  2023         643408\n",
       "48       66+  2024         648084\n",
       "49       66+  2025         652214"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%run -i funciones.py\n",
    "# Defino los rangos de edad\n",
    "rango_edad = {\n",
    "    '0-17': range(0, 17),\n",
    "    '18-29': range(18, 29),\n",
    "    '30-49': range(30, 49),\n",
    "    '50-65': range(50, 65),\n",
    "    '66+': [66]\n",
    "}\n",
    "# Applicamos la funcion assign_age_group para crear la columna  'Age_range'\n",
    "poblacion_total['Age_range'] = poblacion_total['Edad'].apply(assign_age_group)\n",
    "\n",
    "# Agrupamos por 'Age_range', 'Año', y sumamos 'Total_Parcial'\n",
    "poblacion_total_rango_edad = poblacion_total.groupby(['Age_range', 'Año'])['Total_Parcial'].sum().reset_index()\n",
    "\n",
    "poblacion_total_rango_edad\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Age_range', 'Año', 'Total_Parcial'], dtype='object')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "poblacion_total_rango_edad.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Año</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016</td>\n",
       "      <td>3059052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>3063657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018</td>\n",
       "      <td>3067971</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019</td>\n",
       "      <td>3071960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020</td>\n",
       "      <td>3075573</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2021</td>\n",
       "      <td>3078774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2022</td>\n",
       "      <td>3081483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2023</td>\n",
       "      <td>3083694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2024</td>\n",
       "      <td>3085411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2025</td>\n",
       "      <td>3086609</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Año    Total\n",
       "0  2016  3059052\n",
       "1  2017  3063657\n",
       "2  2018  3067971\n",
       "3  2019  3071960\n",
       "4  2020  3075573\n",
       "5  2021  3078774\n",
       "6  2022  3081483\n",
       "7  2023  3083694\n",
       "8  2024  3085411\n",
       "9  2025  3086609"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "poblacion_total_anio"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3.6 Guardamos estas tablas que serán luego utilizadas en Power Bi para la creación del dashboard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "El poblacion_total_rango_edad fue guardado en data\\poblacion_total_rango_edad.csv\n",
      "El poblacion_total_anio fue guardado en data\\poblacion_total_anio.csv\n",
      "El poblacion_total_anio_genero fue guardado en data\\poblacion_total_anio_genero.csv\n"
     ]
    }
   ],
   "source": [
    "# Ruta donde guardamos el file y el name_file\n",
    "pobl_total_rango_edad = 'data\\\\poblacion_total_rango_edad.csv'\n",
    "pobl_total_anio = 'data\\\\poblacion_total_anio.csv'\n",
    "pobl_total_anio_genero =  'data\\\\poblacion_total_anio_genero.csv'\n",
    "\n",
    "# Guardamos el dataframe en un archivo .csv\n",
    "poblacion_total_rango_edad.to_csv(pobl_total_rango_edad, index=False)\n",
    "poblacion_total_anio.to_csv(pobl_total_anio,index= False)\n",
    "poblacion_total_anio_genero.to_csv(pobl_total_anio_genero, index= False)\n",
    "\n",
    "# Mensaje de confirmación de escritura\n",
    "print(f'El poblacion_total_rango_edad fue guardado en {pobl_total_rango_edad}')\n",
    "print(f'El poblacion_total_anio fue guardado en {pobl_total_anio}')\n",
    "print(f'El poblacion_total_anio_genero fue guardado en {pobl_total_anio_genero}')\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}