UNAL.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "private_outputs": true,
      "provenance": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/amalvarezme/AnaliticaDatos/blob/master/UNAL.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Análisis impacto Decreto 1279 - UNAL\n",
        "\n"
      ],
      "metadata": {
        "id": "vqAt-y93yaOd"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "EKJEsin_yY-x"
      },
      "outputs": [],
      "source": [
        "#Lectura datos\n",
        "FILEID = \"1zgDdYXuqGJXAPTw3QJUMUvpFWpo-HOgZ\"\n",
        "\n",
        "!wget --load-cookies /tmp/cookies.txt \"https://docs.google.com/uc?export=download&confirm=$(wget --quiet --save-cookies /tmp/cookies.txt --keep-session-cookies --no-check-certificate 'https://docs.google.com/uc?export=download&id='$FILEID -O- | sed -rn 's/.*confirm=([0-9A-Za-z_]+).*/\\1\\n/p')&id=\"$FILEID -O costo_personal_unal.xlsx && rm -rf /tmp/cookies.txt\n",
        "\n",
        "\n",
        "FILEID = \"1X5gy9CmyUmB5c6l5VUwmGx4Q-AcAxtos\"\n",
        "\n",
        "!wget --load-cookies /tmp/cookies.txt \"https://docs.google.com/uc?export=download&confirm=$(wget --quiet --save-cookies /tmp/cookies.txt --keep-session-cookies --no-check-certificate 'https://docs.google.com/uc?export=download&id='$FILEID -O- | sed -rn 's/.*confirm=([0-9A-Za-z_]+).*/\\1\\n/p')&id=\"$FILEID -O historico_puntos_unal.xlsx && rm -rf /tmp/cookies.txt\n",
        "\n",
        "!dir"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install pandas openpyxl"
      ],
      "metadata": {
        "id": "ETiVOkzb1HSz"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "import numpy as np\n",
        "import matplotlib.pyplot as plt\n",
        "import os\n",
        "\n",
        "# Define the folder path\n",
        "path_ = \"resultadosUNAL\"\n",
        "\n",
        "# Create the folder\n",
        "try:\n",
        "    os.makedirs(path_, exist_ok=True)  # `exist_ok=True` avoids error if the folder already exists\n",
        "    print(f\"Folder '{path_}' created successfully!\")\n",
        "except Exception as e:\n",
        "    print(f\"Error creating folder: {e}\")\n",
        "\n",
        "#ocasionales\n",
        "file_path = 'costo_personal_unal.xlsx'\n",
        "start_row = 2  # 0-indexed, this means row 6 in Excel\n",
        "end_row = 13   #\n",
        "use_columns = ['Año', 'Total general']  # Columns by Excel labels\n",
        "\n",
        "costos_ocasionales_UN = pd.read_excel(\n",
        "    file_path,\n",
        "    usecols=use_columns,       # Specify columns to read\n",
        "    skiprows=start_row,        # Skip rows before start_row\n",
        "    nrows=end_row - start_row,  # Number of rows to read\n",
        "    sheet_name='Hoja2'\n",
        ")\n",
        "\n",
        "costos_ocasionales_UN.head()\n",
        "\n",
        "#planta recursos nacion\n",
        "file_path = 'costo_personal_unal.xlsx'\n",
        "start_row = 20  # 0-indexed, this means row 6 in Excel\n",
        "end_row = 31   #\n",
        "use_columns = ['Año', 'Gastos de Personal RN', 'Gastos de Personal - Docentes', '% Participación linea Docentes']  # Columns by Excel labels\n",
        "\n",
        "costos_docentes_nacion_UN = pd.read_excel(\n",
        "    file_path,\n",
        "    usecols=use_columns,       # Specify columns to read\n",
        "    skiprows=start_row,        # Skip rows before start_row\n",
        "    nrows=end_row - start_row,  # Number of rows to read\n",
        "    sheet_name='Hoja2'\n",
        ")\n",
        "\n",
        "costos_ocasionales_UN.head(20)"
      ],
      "metadata": {
        "id": "6b_Gmekpzt8i"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Costo ocasionales"
      ],
      "metadata": {
        "id": "58HerIwnJGjP"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from matplotlib.ticker import FuncFormatter\n",
        "\n",
        "# Calculate percentage increase between years\n",
        "costos_ocasionales_UN['% Incremento Anual'] = costos_ocasionales_UN['Total general'].pct_change() * 100\n",
        "\n",
        "# Formatter function for y-axis in thousands\n",
        "def thousands_formatter(x, pos):\n",
        "    return f'${int(x/1e6)}M'\n",
        "\n",
        "# Plotting\n",
        "fig, ax1 = plt.subplots(figsize=(10, 6))\n",
        "\n",
        "# Bar plot for values\n",
        "ax1.bar(costos_ocasionales_UN['Año'], costos_ocasionales_UN['Total general'], color='skyblue', label='Costo ocasionales',alpha=0.5)\n",
        "ax1.set_xlabel('Año')\n",
        "ax1.set_ylabel('Valor $', color='blue')\n",
        "ax1.tick_params(axis='y', labelcolor='blue')\n",
        "ax1.set_title('Costo Ocasionales UNAL 2013 - 2023')\n",
        "ax1.set_xticks(costos_ocasionales_UN['Año'])\n",
        "# Adjust y-axis ticks to thousands\n",
        "ax1.yaxis.set_major_formatter(FuncFormatter(thousands_formatter))\n",
        "\n",
        "# Create a second y-axis for percentage increase\n",
        "ax2 = ax1.twinx()\n",
        "ax2.plot(costos_ocasionales_UN['Año'], costos_ocasionales_UN['% Incremento Anual'], color='red', marker='o', label='% Incremento Anual')\n",
        "ax2.set_ylabel('% Incremento Anual', color='red')\n",
        "ax2.tick_params(axis='y', labelcolor='red')\n",
        "\n",
        "\n",
        "# Calculate average percentage increase and standard deviation\n",
        "avg_increase = costos_ocasionales_UN['% Incremento Anual'].mean()\n",
        "med_increase = costos_ocasionales_UN['% Incremento Anual'].median()\n",
        "std_increase = costos_ocasionales_UN['% Incremento Anual'].std()\n",
        "\n",
        "# Add fill-between for ±2 std dev of % increase\n",
        "ax2.plot(costos_ocasionales_UN['Año'],avg_increase*np.ones((costos_ocasionales_UN.shape[0],1)),'y--',label='Media Inc.: '+str(round(avg_increase,2))+'%')\n",
        "\n",
        "ax2.plot(costos_ocasionales_UN['Año'],med_increase*np.ones((costos_ocasionales_UN.shape[0],1)),'g-.',label='Mediana Inc.: '+str(round(med_increase,2))+'%')\n",
        "\n",
        "\n",
        "#ax2.fill_between(\n",
        " #   costos_ocasionales_UN['Año'],\n",
        "  #  avg_increase - 1 * std_increase,\n",
        "  #  avg_increase + 1 * std_increase,\n",
        "  #  color='orange',\n",
        "  #  alpha=0.3,\n",
        "  #  label='± Std Dev'\n",
        "#)\n",
        "\n",
        "\n",
        "# Add legends\n",
        "fig.legend(loc=\"upper left\", bbox_to_anchor=(0.1, 0.9), bbox_transform=ax1.transAxes)\n",
        "\n",
        "# Show plot\n",
        "plt.tight_layout()\n",
        "plt.grid()\n",
        "plt.savefig(path_+'/costo_ocasionales_UNAL.png',dpi = 300)\n",
        "plt.show()\n",
        "\n"
      ],
      "metadata": {
        "id": "tl0RrUrj0y8x"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Gastos Personal con Recursos Nación"
      ],
      "metadata": {
        "id": "wIdeh__yN0TH"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "costos_docentes_nacion_UN.columns"
      ],
      "metadata": {
        "id": "hsG2uHgbObhl"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#costos_docentes_nacion_UN\n",
        "\n",
        "\n",
        "fig, ax1 = plt.subplots(figsize=(10, 6))\n",
        "\n",
        "# Width of a bar\n",
        "width = 0.4\n",
        "\n",
        "# Plotting\n",
        "ax1.bar(costos_docentes_nacion_UN['Año']-width/2, costos_docentes_nacion_UN['Gastos de Personal RN'], width, label='Gastos Personal RN',alpha=0.5)\n",
        "ax1.bar(costos_docentes_nacion_UN['Año']+width/2, costos_docentes_nacion_UN['Gastos de Personal - Docentes'], width, label='Gastos de Personal - Docentes',alpha=0.5)\n",
        "ax1.set_xlabel('Año')\n",
        "ax1.set_ylabel('Valor $', color='blue')\n",
        "ax1.tick_params(axis='y', labelcolor='blue')\n",
        "ax1.set_title('% Gastos de Personal Recursos Nación (RN) UNAL 2013-2023')\n",
        "ax1.set_xticks(costos_docentes_nacion_UN['Año'])\n",
        "# Adjust y-axis ticks to thousands\n",
        "ax1.yaxis.set_major_formatter(FuncFormatter(thousands_formatter))\n",
        "\n",
        "# Create a second y-axis for percentage increase\n",
        "# Calculate percentage increase between years\n",
        "costos_docentes_nacion_UN['% Incremento Anual GP RN'] = costos_docentes_nacion_UN['Gastos de Personal RN'].pct_change() * 100\n",
        "\n",
        "costos_docentes_nacion_UN['% Incremento Anual GP'] = costos_docentes_nacion_UN['Gastos de Personal - Docentes'].pct_change() * 100\n",
        "\n",
        "\n",
        "\n",
        "ax2 = ax1.twinx()\n",
        "\n",
        "#ax2.plot(costos_docentes_nacion_UN['Año'], costos_docentes_nacion_UN['% Incremento Anual GP RN'], color='red', marker='o', label='% Incremento Anual GP RN')\n",
        "#ax2.set_ylabel('% Incremento Anual', color='red')\n",
        "#ax2.tick_params(axis='y', labelcolor='red')\n",
        "\n",
        "ax2.plot(costos_docentes_nacion_UN['Año'], costos_docentes_nacion_UN['% Incremento Anual GP'], color='red', marker='o', label='% Incremento Anual GP')\n",
        "ax2.set_ylabel('% Incremento Anual GP', color='red')\n",
        "ax2.tick_params(axis='y', labelcolor='red')\n",
        "\n",
        "\n",
        "# Calculate average percentage increase and standard deviation\n",
        "avg_increase = costos_docentes_nacion_UN['% Incremento Anual GP'].mean()\n",
        "med_increase = costos_docentes_nacion_UN['% Incremento Anual GP'].median()\n",
        "std_increase = costos_docentes_nacion_UN['% Incremento Anual GP'].std()\n",
        "\n",
        "# Add fill-between for ±2 std dev of % increase\n",
        "ax2.plot(costos_docentes_nacion_UN['Año'],avg_increase*np.ones((costos_docentes_nacion_UN.shape[0],1)),'y--',label='Media Inc.: '+str(round(avg_increase,2))+'%')\n",
        "\n",
        "ax2.plot(costos_docentes_nacion_UN['Año'],med_increase*np.ones((costos_docentes_nacion_UN.shape[0],1)),'g-.',label='Mediana Inc.: '+str(round(med_increase,2))+'%')\n",
        "\n",
        "# Add legends\n",
        "fig.legend(loc='upper left', bbox_to_anchor=(0.005, 1.015), bbox_transform=ax1.transAxes)\n",
        "\n",
        "\n",
        "plt.grid()\n",
        "plt.savefig(path_+'/gastos_personal_nacion_UNAL.png',dpi = 300)\n",
        "plt.show()\n"
      ],
      "metadata": {
        "id": "sZT2UvRG21xo"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Aumento Salario Puntos 1279"
      ],
      "metadata": {
        "id": "sdp8Uka0A_xF"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "file_path = 'historico_puntos_unal.xlsx'\n",
        "\n",
        "start_row = 0  # 0-indexed, this means row 6 in Excel\n",
        "end_row = 21   #\n",
        "use_columns = ['AÑO ACTA', 'PUNTOS ASIGNADOS']  # Columns by Excel labels\n",
        "\n",
        "\n",
        "puntos_asignados_UN = pd.read_excel(\n",
        "    file_path,\n",
        "    usecols=use_columns,       # Specify columns to read\n",
        "    skiprows=start_row,        # Skip rows before start_row\n",
        "    nrows=end_row - start_row,  # Number of rows to read\n",
        "    sheet_name='10. PUNTOS PROD'\n",
        ")\n",
        "\n"
      ],
      "metadata": {
        "id": "l_iw_iVUBC-g"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "fig, ax1 = plt.subplots(figsize=(10, 6))\n",
        "\n",
        "\n",
        "# Plotting\n",
        "ax1.bar(puntos_asignados_UN['AÑO ACTA'], puntos_asignados_UN['PUNTOS ASIGNADOS'], label='PUNTOS ASIGNADOS UNAL',alpha=0.5)\n",
        "\n",
        "ax1.set_xlabel('Año')\n",
        "ax1.set_ylabel('#TOTAL PUNTOS ASIGNADOS', color='blue')\n",
        "ax1.tick_params(axis='y', labelcolor='blue')\n",
        "ax1.set_title('PUNTOS ASIGNADOS UNAL 2003-2023')\n",
        "ax1.set_xticks(puntos_asignados_UN['AÑO ACTA'])  # Ensure correct positioning for labels\n",
        "ax1.set_xticklabels(list(puntos_asignados_UN['AÑO ACTA']),rotation=45)  # Rotate labels by 45 degrees\n",
        "\n",
        "# Create a second y-axis for percentage increase\n",
        "# Calculate percentage increase between years\n",
        "puntos_asignados_UN['% Incremento Anual Ptos. Asignados'] = puntos_asignados_UN['PUNTOS ASIGNADOS'].pct_change() * 100\n",
        "\n",
        "\n",
        "ax2 = ax1.twinx()\n",
        "ax2.plot(puntos_asignados_UN['AÑO ACTA'], puntos_asignados_UN['% Incremento Anual Ptos. Asignados'], color='red', marker='o', label='% Incremento Anual Ptos. Asignados')\n",
        "ax2.set_ylabel('% Incremento Anual Ptos. Asignados', color='red')\n",
        "ax2.tick_params(axis='y', labelcolor='red')\n",
        "\n",
        "\n",
        "# Calculate average percentage increase and standard deviation\n",
        "avg_increase = puntos_asignados_UN['% Incremento Anual Ptos. Asignados'].mean()\n",
        "med_increase = puntos_asignados_UN['% Incremento Anual Ptos. Asignados'].median()\n",
        "std_increase = puntos_asignados_UN['% Incremento Anual Ptos. Asignados'].std()\n",
        "\n",
        "# Add fill-between for ±2 std dev of % increase\n",
        "ax2.plot(puntos_asignados_UN['AÑO ACTA'],avg_increase*np.ones((puntos_asignados_UN.shape[0],1)),'y--',label='Media Inc.: '+str(round(avg_increase,2))+'%')\n",
        "\n",
        "ax2.plot(puntos_asignados_UN['AÑO ACTA'],med_increase*np.ones((puntos_asignados_UN.shape[0],1)),'y--',label='Mediana Inc.: '+str(round(med_increase,2))+'%')\n",
        "\n",
        "# Add legends\n",
        "fig.legend(loc='upper left', bbox_to_anchor=(0.005, 1.015), bbox_transform=ax1.transAxes)\n",
        "\n",
        "\n",
        "plt.grid()\n",
        "plt.savefig(path_+'/puntos_asignados_UNAL.png',dpi = 300)\n",
        "plt.show()\n"
      ],
      "metadata": {
        "id": "wUlPDulfJSSQ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "\n",
        "\n",
        "# Plotting\n",
        "fig, ax1 = plt.subplots(figsize=(10, 6))\n",
        "\n",
        "\n",
        "# Plotting\n",
        "ax1.bar(puntos_asignados_UN.iloc[10:,0], puntos_asignados_UN.iloc[10:,1], label='PUNTOS ASIGNADOS UNAL',alpha=0.5)\n",
        "\n",
        "ax1.set_xlabel('Año')\n",
        "ax1.set_ylabel('#TOTAL PUNTOS ASIGNADOS', color='blue')\n",
        "ax1.tick_params(axis='y', labelcolor='blue')\n",
        "ax1.set_title('PUNTOS ASIGNADOS UNAL 2013-2023')\n",
        "ax1.set_xticks(puntos_asignados_UN.iloc[10:,0])  # Ensure correct positioning for labels\n",
        "ax1.set_xticklabels(list(puntos_asignados_UN.iloc[10:,0]),rotation=45)  # Rotate labels by 45 degrees\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "ax2 = ax1.twinx()\n",
        "ax2.plot(puntos_asignados_UN.iloc[10:,0], puntos_asignados_UN.iloc[10:,-1], color='red', marker='o', label='% Incremento Anual Ptos. Asignados')\n",
        "ax2.set_ylabel('% Incremento Anual Ptos. Asignados', color='red')\n",
        "ax2.tick_params(axis='y', labelcolor='red')\n",
        "\n",
        "\n",
        "# Calculate average percentage increase and standard deviation\n",
        "avg_increase = np.mean(puntos_asignados_UN.iloc[10:,-1])\n",
        "med_increase = np.median(puntos_asignados_UN.iloc[10:,-1])\n",
        "\n",
        "\n",
        "# Add fill-between for ±2 std dev of % increase\n",
        "ax2.plot(puntos_asignados_UN.iloc[10:,0],avg_increase*np.ones((puntos_asignados_UN.iloc[10:,0].shape[0],1)),'r--',label='Media Inc.: '+str(round(avg_increase,2))+'%')\n",
        "\n",
        "ax2.plot(puntos_asignados_UN.iloc[10:,0],med_increase*np.ones((puntos_asignados_UN.iloc[10:,0].shape[0],1)),'y--',label='Mediana Inc.: '+str(round(med_increase,2))+'%')\n",
        "\n",
        "# Add legends\n",
        "fig.legend(loc='upper right', bbox_to_anchor=(0.95, 0.975), bbox_transform=ax1.transAxes)\n",
        "\n",
        "\n",
        "plt.grid()\n",
        "plt.savefig(path_+'/puntos_asignados_UNAL_10.png',dpi = 300)\n",
        "plt.show()"
      ],
      "metadata": {
        "id": "cPpklnLeNEwC"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "file_path = 'historico_puntos_unal.xlsx'\n",
        "\n",
        "start_row = 0  # 0-indexed, this means row 6 in Excel\n",
        "end_row = 62   #\n",
        "use_columns = ['SUELDO MIN', 'SUELDO MAX','2023_']  # Columns by Excel labels\n",
        "\n",
        "\n",
        "sueldos2023_UN = pd.read_excel(\n",
        "    file_path,\n",
        "    usecols=use_columns,       # Specify columns to read\n",
        "    skiprows=start_row,        # Skip rows before start_row\n",
        "    nrows=end_row - start_row,  # Number of rows to read\n",
        "    sheet_name='5. SUELDOS PLANTA'\n",
        ")\n",
        "print(sueldos2023_UN['2023_'].sum())\n",
        "\n",
        "sueldos2023_UN.fillna(0,inplace=True) #fill NAN\n",
        "\n"
      ],
      "metadata": {
        "id": "9cwfPOK8MAES"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#range\n",
        "for i in range(sueldos2023_UN.shape[0]):\n",
        "   sueldos2023_UN.loc[[i],['RANGE']] = str((sueldos2023_UN.loc[[i],['SUELDO MIN']]/1e6).to_numpy(dtype=int)[0][0]) + '-' + str((sueldos2023_UN.loc[[i],['SUELDO MAX']]/1e6).to_numpy(dtype=int)[0][0])+ 'M'\n",
        "\n",
        "sueldos2023_UN['RANGE']"
      ],
      "metadata": {
        "id": "fiZfTGGsIwlR"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#medium salary\n",
        "#percentage\n",
        "\n",
        "sueldos2023_UN['SUELDO APROX.'] = (sueldos2023_UN['SUELDO MIN']+sueldos2023_UN['SUELDO MAX'])/2\n",
        "\n",
        "sueldos2023_UN['PORCENTAGE PROFESORES'] = (sueldos2023_UN['2023_']/sueldos2023_UN['2023_'].sum())*100\n",
        "\n",
        "sueldos2023_UN['PERCENTILES'] = np.cumsum(sueldos2023_UN['PORCENTAGE PROFESORES'])\n",
        "\n",
        "Nprofes = sueldos2023_UN['2023_'].sum()\n",
        "print(Nprofes)\n"
      ],
      "metadata": {
        "id": "voYd3z07bYiD"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "from scipy.spatial.distance import cdist\n",
        "\n",
        "percentiles_ = np.array([25,50,75,99])\n",
        "\n",
        "dis_ = cdist(sueldos2023_UN['PERCENTILES'].to_numpy().reshape(-1,1),percentiles_.reshape(-1,1))\n",
        "\n",
        "ind_ = np.argmin(dis_,axis=0)\n",
        "\n",
        "print(dis_.shape,ind_.shape)\n",
        "sueldos2023_UN.loc[ind_,['PERCENTILES','SUELDO APROX.']]\n"
      ],
      "metadata": {
        "id": "TqaDQw1IeZHW"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "sueldos2023_UN.loc[ind_[1]+2,['PERCENTILES','SUELDO APROX.']]"
      ],
      "metadata": {
        "id": "vPIpw7YF8FYt"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "sueldos2023_UN.plot(x='SUELDO APROX.',y='PERCENTILES',marker='o')"
      ],
      "metadata": {
        "id": "iEKdY3HZdihp"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "avg_salary = sueldos2023_UN['SUELDO APROX.'].to_numpy().dot(sueldos2023_UN['PORCENTAGE PROFESORES'].to_numpy()/100)\n",
        "\n",
        "med_salary = sueldos2023_UN.loc[ind_[1]+2,'SUELDO APROX.']\n",
        "\n",
        "print(avg_salary, med_salary)\n",
        "\n",
        "ind_avg = np.argmin(cdist(sueldos2023_UN['SUELDO APROX.'].to_numpy().reshape(-1,1),np.array([avg_salary]).reshape(-1,1)))\n",
        "\n"
      ],
      "metadata": {
        "id": "w5f4w-lycnOq"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "fig, ax1 = plt.subplots(figsize=(10, 6))\n",
        "\n",
        "# Bar plot for values\n",
        "ax1.bar(\n",
        "range(sueldos2023_UN.shape[0]),\n",
        "sueldos2023_UN['PORCENTAGE PROFESORES'], color='blue', label='SUELDOS PLANTA UNAL 2023',alpha=0.5)\n",
        "ax1.set_xlabel('RANGO')\n",
        "ax1.set_ylabel('% PROFESORES DE PLANTA', color='blue')\n",
        "ax1.tick_params(axis='y', labelcolor='blue')\n",
        "ax1.set_title(f'SUELDOS PROFESORES DE PLANTA UNAL 2023\\n TOTAL PROFESORES: {int(Nprofes)}')\n",
        "\n",
        "#average median salary\n",
        "\n",
        "ax1.plot(ind_avg*np.ones((20,1)),np.linspace(0,9.5,20),'r--',label='Media: $'+str(round(avg_salary/1e6,1))+'M',linewidth=3)\n",
        "\n",
        "ax1.plot((ind_[1]+2)*np.ones((20,1)),np.linspace(0,9.5,20),'y--',label='Mediana: $'+str(round(med_salary/1e6,1))+'M',linewidth=3)\n",
        "ax1.set_xticks(range(sueldos2023_UN.shape[0]))  # Ensure correct positioning for labels\n",
        "ax1.set_xticklabels(sueldos2023_UN['RANGE'],rotation=90)  #\n",
        "plt.grid()\n",
        "plt.legend()\n",
        "plt.tight_layout()\n",
        "plt.savefig(path_+'/sueldos_2023_UNAL.png',dpi = 300)\n",
        "plt.show()\n"
      ],
      "metadata": {
        "id": "AT0p5cnBFuy4"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Distribución Salarios Docentes de Planta\n"
      ],
      "metadata": {
        "id": "ZTsr14pxI_ea"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "sueldos2023_UN.head()"
      ],
      "metadata": {
        "id": "r-B35v8irV1P"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "percentiles_ = np.array([20,40,60,80,99])\n",
        "\n",
        "dis_ = cdist(sueldos2023_UN['PERCENTILES'].to_numpy().reshape(-1,1),percentiles_.reshape(-1,1))\n",
        "\n",
        "ind_ = np.argmin(dis_,axis=0)\n",
        "ind_ = np.append(ind_,sueldos2023_UN.shape[0]-1)\n",
        "print(sueldos2023_UN.loc[ind_,['PERCENTILES','SUELDO APROX.']])\n",
        "\n",
        "pi = -1\n",
        "bar_sueldos = []\n",
        "por_profes_q = []\n",
        "for i in ind_:#percentile  25, 50, 75, 99\n",
        "    xx = np.array([])\n",
        "    print(pi+1,i)\n",
        "    for j in range(pi+1,i+1):\n",
        "        xx = np.append(xx,sueldos2023_UN.loc[j,'SUELDO APROX.']*np.ones((1,sueldos2023_UN.loc[j,'2023_'].astype(int)))[0])\n",
        "    pi = i\n",
        "    bar_sueldos.append(xx)\n",
        "    por_profes_q.append(xx.shape[0]/Nprofes*100)\n",
        "    print(por_profes_q[-1].round(1))"
      ],
      "metadata": {
        "id": "LheWNK2IjbwH"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Define colors for each boxplot\n",
        "colors = ['skyblue', 'lightgreen', 'lightcoral', 'gold', 'plum', 'lightsalmon']\n",
        "\n",
        "# Create the figure and axis\n",
        "fig, ax = plt.subplots(figsize=(12, 6))\n",
        "\n",
        "# Plot the boxplots\n",
        "box = ax.boxplot(bar_sueldos, patch_artist=True, showmeans=True)\n",
        "\n",
        "# Apply colors to each boxplot\n",
        "for patch, color in zip(box['boxes'], colors):\n",
        "    patch.set_facecolor(color)\n",
        "\n",
        "\n",
        "ax.set_xticks(range(1, 7))\n",
        "ax.set_xticklabels(['0-20%', '20-40%', '40-60%', '60-80%','80-99%', 'TOP 1%'])\n",
        "\n",
        "ax.set_xlabel('QUANTILES SALARIOS UNAL 2023')\n",
        "ax.set_ylabel('Valor $')#, color='blue')\n",
        "#ax.tick_params(axis='y', labelcolor='blue')\n",
        "ax.set_title('DISTRIBUCIÓN POR QUANTILES SALARIOS PROFESORES DE PLANTA UNAL 2023')\n",
        "\n",
        "# Adjust y-axis ticks to thousands\n",
        "ax.yaxis.set_major_formatter(FuncFormatter(thousands_formatter))\n",
        "plt.grid()\n",
        "plt.savefig(path_+'/sueldos_quantiles_top_1_2023_UNAL.png',dpi = 300)\n",
        "plt.show()"
      ],
      "metadata": {
        "id": "wAMqEvK3wGp0"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "import shutil\n",
        "from google.colab import files\n",
        "\n",
        "# Define the folder to be zipped\n",
        "folder_to_zip = \"resultadosUNAL\"  # Replace with your folder name\n",
        "zip_output_file = \"resultadosUNAL.zip\"  # Name of the output zip file\n",
        "\n",
        "# Create a ZIP file\n",
        "try:\n",
        "    shutil.make_archive(zip_output_file.replace('.zip', ''), 'zip', folder_to_zip)\n",
        "    print(f\"Folder '{folder_to_zip}' has been successfully zipped as '{zip_output_file}'.\")\n",
        "except Exception as e:\n",
        "    print(f\"An error occurred while zipping the folder: {e}\")\n",
        "\n",
        "# Download the file\n",
        "files.download(zip_output_file)"
      ],
      "metadata": {
        "id": "1jlpX6CC_XQe"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "NQc3tI8tOI1q"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}