desviaciones_carga_docente.ipynb

{
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11+"
  },
  "name": "",
  "signature": "sha256:c258216c0cd83407597c6c90a67a82dd53ac2d905083c07e261c003cbc7cbb6c"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import matplotlib.pyplot as plt\n",
      "import numpy as np\n",
      "\n",
      "import openpyxl\n",
      "from openpyxl.cell import get_column_letter, column_index_from_string\n",
      "deptos_viejos = openpyxl.load_workbook('Datos 185 Departamentos 31-12-2015_corrected.xlsx', data_only=True)\n",
      "deptos_nuevos = openpyxl.load_workbook('Propuesta nuevos departamentos 17-5-2016_corrected.xlsx', data_only=True)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stderr",
       "text": [
        "/usr/lib/python2.7/dist-packages/openpyxl/workbook/names/named_range.py:124: UserWarning: Discarded range with reserved name\n",
        "  warnings.warn(\"Discarded range with reserved name\")\n"
       ]
      }
     ],
     "prompt_number": 1
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "deptos_viejos.get_sheet_names()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 2,
       "text": [
        "[u'Datos',\n",
        " u'Departamentos 31-12-2015',\n",
        " u'Factores de Ponderaci\\xf3n PTCE',\n",
        " u'Histograma_HID',\n",
        " u'Histograma1_HID',\n",
        " u'Uni_HID']"
       ]
      }
     ],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "dtos_viejos_data = deptos_viejos.get_sheet_by_name('Departamentos 31-12-2015')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import unicodedata\n",
      "def strip_accents(s):\n",
      "    s = unicode(s)\n",
      "    name = ''.join(c for c in unicodedata.normalize('NFD', s)\n",
      "                  if unicodedata.category(c) != 'Mn')\n",
      "    return name"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 4
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "new_data_interfac = deptos_nuevos.get_sheet_by_name('INTERFACULTATIVOS')\n",
      "new_data_intrafac = deptos_nuevos.get_sheet_by_name('INTRAFACULTATIVOS')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 5
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def clean_name(nombre_departamento):\n",
      "        nombre_departamento = strip_accents(nombre_departamento)\n",
      "        index = nombre_departamento.find('(')\n",
      "        if index != -1: return nombre_departamento[:index - 1]\n",
      "        return nombre_departamento"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 6
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "new_names_intra = [dept.value for dept in new_data_intrafac.columns[0]]\n",
      "new_names_intra = map(clean_name, new_names_intra[1:-3])\n",
      "new_names_inter = [dept.value for dept in new_data_interfac.columns[0]]\n",
      "new_names_inter = map(clean_name, new_names_inter[1:-1])\n",
      "\n",
      "class DepartamentoNuevo(object):\n",
      "    \n",
      "    def __init__(self, nombre_departamento):\n",
      "        self.name = clean_name(nombre_departamento)\n",
      "        self.row, self.nature = self.get_row_and_nature()\n",
      "        if self.nature == 'intra':\n",
      "            self.data = new_data_intrafac\n",
      "        else:\n",
      "            self.data = new_data_interfac\n",
      "        self.origin = self.get_origin()\n",
      "        self.ECTS = self.get_ECTS()\n",
      "        self.PTCE = self.get_PTCE()\n",
      "            \n",
      "    def __repr__(self):\n",
      "        return self.name\n",
      "        \n",
      "    def get_row_and_nature(self):\n",
      "        if self.name in new_names_intra:\n",
      "            nature = 'intra'\n",
      "            row = new_names_intra.index(self.name) + 2\n",
      "        else:\n",
      "            nature = 'inter'\n",
      "            row = new_names_inter.index(self.name) + 2\n",
      "        return (row, nature)\n",
      "            \n",
      "    def get_origin(self):\n",
      "        \n",
      "        origin = []\n",
      "        for i in range(21, 21 + 5):\n",
      "            old_origin = self.data.cell(row=self.row, column=i).value\n",
      "            #print old_origin\n",
      "            if old_origin is not None:\n",
      "                old_origin = clean_name(old_origin)\n",
      "                origin.append(DepartamentoViejo(old_origin)) \n",
      "        return origin\n",
      "    \n",
      "    def get_ECTS(self):\n",
      "        return self.data.cell(row=self.row, column=6).value\n",
      "    \n",
      "    def get_PTCE(self):\n",
      "        return self.data.cell(row=self.row, column=3).value\n",
      "    \n",
      "    def get_ECTS_over_PTCE(self):\n",
      "        return round(self.ECTS / self.PTCE, 2)\n",
      "    \n",
      "    def get_improvement(self):\n",
      "        old_depts = self.origin\n",
      "        new_load = self.get_ECTS_over_PTCE()\n",
      "        return [round(100 * (new_load - old_dept.get_ECTS_over_PTCE()) / old_dept.get_ECTS_over_PTCE(), 2) for old_dept in old_depts]      "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 7
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "old_names1 = [clean_name(dept.value) for dept in dtos_viejos_data.columns[2][1:]]\n",
      "old_names2 = [clean_name(dept.value) for dept in dtos_viejos_data.columns[3][1:]]\n",
      "old_names = zip(old_names1, old_names2)\n",
      "\n",
      "class DepartamentoViejo(object):\n",
      "    \n",
      "    def __init__(self, nombre_departamento):\n",
      "        self.name = clean_name(nombre_departamento)\n",
      "        self.row = self.get_row()\n",
      "        self.ECTS = self.get_ECTS()\n",
      "        self.PTCE = self.get_PTCE()\n",
      "        \n",
      "    def __repr__(self):\n",
      "        return self.name\n",
      "    \n",
      "    def get_row(self):\n",
      "        for row, names in enumerate(old_names):\n",
      "            if self.name in names:\n",
      "                return row + 2\n",
      "        raise ValueError('Name not found!', self.name) \n",
      "    \n",
      "    def get_ECTS_over_PTCE(self):\n",
      "        return round(dtos_viejos_data.cell(row=self.row, column=14).value, 2)\n",
      "    \n",
      "    def get_ECTS(self):\n",
      "        return dtos_viejos_data.cell(row=self.row, column=11).value\n",
      "    \n",
      "    def get_PTCE(self):\n",
      "        return dtos_viejos_data.cell(row=self.row, column=8).value"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 8
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "L_inter = [DepartamentoNuevo(name) for name in new_names_inter]\n",
      "L_intra = [DepartamentoNuevo(name) for name in new_names_intra]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 9
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "with open('cambios_intrafacultativos.txt', 'w') as f:\n",
      "\n",
      "    L_intra = [DepartamentoNuevo(name) for name in new_names_intra]\n",
      "    for n, new_dept in enumerate(L_intra):\n",
      "        changes = new_dept.get_improvement()\n",
      "        print >> f, new_dept\n",
      "        for old_dept, change in zip(new_dept.get_origin(), changes):\n",
      "            sign =('+' if change >0 else '-')\n",
      "            f.write('    {:<40} cambia su carga docente en un {}{}%\\n\\n'.format(old_dept, sign, abs(change)))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 10
    },
    {
     "cell_type": "code",
     "collapsed": true,
     "input": [
      "with open('cambios_interfacultativos.txt', 'w') as f:\n",
      "\n",
      "    L_inter = [DepartamentoNuevo(name) for name in new_names_inter]\n",
      "    for n, new_dept in enumerate(L_inter):\n",
      "        changes = new_dept.get_improvement()\n",
      "        print >> f, new_dept\n",
      "        for old_dept, change in zip(new_dept.get_origin(), changes):\n",
      "            sign =('+' if change >0 else '-')\n",
      "            f.write('    {:<40} cambia su carga docente en un {}{}%\\n\\n'.format(old_dept, sign, abs(change)))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 11
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "fig, axes = plt.subplots(2, 1)\n",
      "ax0, ax1 = axes\n",
      "ax0.set_xlim(-1, 43)\n",
      "ax1.set_xlim(-1, 45)\n",
      "N = len(L_intra)\n",
      "colors = ['pink', 'cyan', 'lightgreen', 'yellow', 'brown', 'grey']\n",
      "ind = 0\n",
      "width = 0.4\n",
      "end_first = 25\n",
      "for depto in L_intra[0:N/2]:\n",
      "    m = len(depto.origin)\n",
      "    ind +=width\n",
      "    ax0.text(ind, 50, depto, rotation='vertical', verticalalignment='bottom', size=8)\n",
      "    ind += 0.3\n",
      "    init_ind = ind\n",
      "    c = 0\n",
      "    for old_depto in depto.origin:\n",
      "        if len(depto.origin) == 1: c = 5\n",
      "        ax0.bar(ind, old_depto.get_ECTS_over_PTCE(), width, color=colors[c], alpha=0.7)\n",
      "        if len(depto.origin) > 1:\n",
      "            ax0.text(ind + 0.1, 50, old_depto, rotation='vertical', verticalalignment='bottom', size=8)\n",
      "        ind += width\n",
      "        end_ind = ind\n",
      "        c += 1\n",
      "    ax0.errorbar(init_ind + (end_ind - init_ind) / 2.0, depto.get_ECTS_over_PTCE(),\n",
      "                      xerr=(end_ind - init_ind) / 2.0 + 0.1, yerr=0, color='red')\n",
      "\n",
      "    \n",
      "        \n",
      "ind = 0        \n",
      "for depto in L_intra[N/2:]:\n",
      "    m = len(depto.origin)\n",
      "    ind +=width\n",
      "    ax1.text(ind, 50, depto, rotation='vertical', verticalalignment='bottom', size=8)\n",
      "    ind += 0.3\n",
      "    init_ind = ind\n",
      "    c = 0\n",
      "    for old_depto in depto.origin:\n",
      "        if len(depto.origin) == 1: c = 5\n",
      "        ax1.bar(ind, old_depto.get_ECTS_over_PTCE(), width, color=colors[c], alpha=0.7)\n",
      "        if len(depto.origin) > 1:\n",
      "            ax1.text(ind + 0.1, 50, old_depto, rotation='vertical', verticalalignment='bottom', size=8)\n",
      "        ind += width\n",
      "        end_ind = ind\n",
      "        c += 1\n",
      "    ax1.errorbar(init_ind + (end_ind - init_ind) / 2.0, depto.get_ECTS_over_PTCE(),\n",
      "                      xerr=(end_ind - init_ind) / 2.0 + 0.1, yerr=0, color='black')\n",
      "    \n",
      "ax0.tick_params(axis='x', which='both', bottom='off', top='off', labelbottom='off') \n",
      "ax1.tick_params(axis='x', which='both', bottom='off', top='off', labelbottom='off') "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 12
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Dibujos con base proporcional a PTCE\n",
      "fig, axes = plt.subplots(2, 1)\n",
      "ax0, ax1 = axes\n",
      "#ax0.set_xlim(-1, 43)\n",
      "#ax1.set_xlim(-1, 45)\n",
      "N = len(L_intra)\n",
      "colors = ['pink', 'cyan', 'lightgreen', 'yellow', 'brown', 'grey']\n",
      "ind = 0\n",
      "width = 1\n",
      "end_first = 25\n",
      "for depto in L_intra[0:N/2]:\n",
      "    m = len(depto.origin)\n",
      "    ind +=width\n",
      "    #ax0.text(ind, 50, depto, rotation='vertical', verticalalignment='bottom', size=8)\n",
      "    ind += 0.3\n",
      "    init_ind = ind\n",
      "    c = 0\n",
      "    for old_depto in depto.origin:\n",
      "        if len(depto.origin) == 1: c = 5\n",
      "        ax0.bar(ind, old_depto.get_ECTS_over_PTCE(), width*old_depto.PTCE, color=colors[c], alpha=0.7)\n",
      "        if len(depto.origin) > 1:\n",
      "            pass\n",
      "            #ax0.text(ind + 0.1, 50, old_depto, rotation='vertical', verticalalignment='bottom', size=8)\n",
      "        ind += width*old_depto.PTCE\n",
      "        end_ind = ind\n",
      "        c += 1\n",
      "    ax0.errorbar(init_ind + (end_ind - init_ind) / 2.0, depto.get_ECTS_over_PTCE(),\n",
      "                      xerr=(end_ind - init_ind) / 2.0 + 0.1, yerr=0, color='red')\n",
      "\n",
      "    \n",
      "        \n",
      "ind = 0        \n",
      "for depto in L_intra[N/2:]:\n",
      "    m = len(depto.origin)\n",
      "    ind +=width\n",
      "    #ax1.text(ind, 50, depto, rotation='vertical', verticalalignment='bottom', size=8)\n",
      "    ind += 0.3\n",
      "    init_ind = ind\n",
      "    c = 0\n",
      "    for old_depto in depto.origin:\n",
      "        if len(depto.origin) == 1: c = 5\n",
      "        ax1.bar(ind, old_depto.get_ECTS_over_PTCE(), width*old_depto.PTCE, color=colors[c], alpha=0.7)\n",
      "        if len(depto.origin) > 1:\n",
      "            pass\n",
      "            #ax0.text(ind + 0.1, 50, old_depto, rotation='vertical', verticalalignment='bottom', size=8)\n",
      "        ind += width*old_depto.PTCE\n",
      "        end_ind = ind\n",
      "        c += 1\n",
      "    ax1.errorbar(init_ind + (end_ind - init_ind) / 2.0, depto.get_ECTS_over_PTCE(),\n",
      "                      xerr=(end_ind - init_ind) / 2.0 + 0.1, yerr=0, color='red')\n",
      "    \n",
      "ax0.tick_params(axis='x', which='both', bottom='off', top='off', labelbottom='off') \n",
      "ax1.tick_params(axis='x', which='both', bottom='off', top='off', labelbottom='off') "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 13
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "fig, ax = plt.subplots()\n",
      "#ax.set_xlim(-1, 40)\n",
      "N = len(L_inter)\n",
      "colors = ['pink', 'cyan', 'lightgreen', 'yellow', 'brown', 'grey']\n",
      "ind = 0\n",
      "width = 0.4\n",
      "end_first = 25\n",
      "for depto in L_inter:\n",
      "    m = len(depto.origin)\n",
      "    ind +=width\n",
      "    ax.text(ind, 50, depto, rotation='vertical', verticalalignment='bottom', size=8)\n",
      "    ind += 0.3\n",
      "    init_ind = ind\n",
      "    c = 0\n",
      "    for old_depto in depto.origin:\n",
      "        if len(depto.origin) == 1: c = 5\n",
      "        ax.bar(ind, old_depto.get_ECTS_over_PTCE(), width, color=colors[c], alpha=0.7)\n",
      "        if len(depto.origin) > 1:\n",
      "            ax.text(ind + 0.1, 50, old_depto, rotation='vertical', verticalalignment='bottom', size=8)\n",
      "        ind += width\n",
      "        end_ind = ind\n",
      "        c += 1\n",
      "    ax.errorbar(init_ind + (end_ind - init_ind) / 2.0, depto.get_ECTS_over_PTCE(),\n",
      "                      xerr=(end_ind - init_ind) / 2.0 + 0.1, yerr=0, color='red')\n",
      "\n",
      "    \n",
      "    \n",
      "ax.tick_params(axis='x', which='both', bottom='off', top='off', labelbottom='off') "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 14
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "fig, ax = plt.subplots()\n",
      "#ax.set_xlim(-1, 40)\n",
      "N = len(L_inter)\n",
      "colors = ['pink', 'cyan', 'lightgreen', 'yellow', 'brown', 'grey']\n",
      "ind = 0\n",
      "width = 1\n",
      "end_first = 25\n",
      "for depto in L_inter:\n",
      "    m = len(depto.origin)\n",
      "    ind +=width\n",
      "    #ax.text(ind, 50, depto, rotation='vertical', verticalalignment='bottom', size=8)\n",
      "    ind += 0.3\n",
      "    init_ind = ind\n",
      "    c = 0\n",
      "    for old_depto in depto.origin:\n",
      "        if len(depto.origin) == 1: c = 5\n",
      "        ax.bar(ind, old_depto.get_ECTS_over_PTCE(), width*depto.PTCE, color=colors[c], alpha=0.7)\n",
      "        if len(depto.origin) > 1:\n",
      "            pass\n",
      "            #ax.text(ind + 0.1, 50, old_depto, rotation='vertical', verticalalignment='bottom', size=8)\n",
      "        ind += width*depto.PTCE\n",
      "        end_ind = ind\n",
      "        c += 1\n",
      "    ax.errorbar(init_ind + (end_ind - init_ind) / 2.0, depto.get_ECTS_over_PTCE(),\n",
      "                      xerr=(end_ind - init_ind) / 2.0 + 0.1, yerr=0, color='red')\n",
      "\n",
      "    \n",
      "    \n",
      "ax.tick_params(axis='x', which='both', bottom='off', top='off', labelbottom='off') "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 15
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def compute_gini_coefficient(dept_list):\n",
      "    '''Dada la lista de departamentos, calcula el coeficiente de Gini \n",
      "    respecto a la carga docente. \n",
      "    \n",
      "         sum_i sum_j |ECTS_i / PTCE_i - ECTS_j / PTCE_j| * PTCE_i * PTCE_j\n",
      "    G =  ----------------------------------------------------------------------------\n",
      "              2 * PTCE_total**2 * carga_media\n",
      "              \n",
      "        sum_i sum_j |ECTS_i * PTCE_j - ECTS_j * PTCE_i|\n",
      "      = -----------------------------------------------------------------------\n",
      "                         2 * PTCE_total * ECTS_total \n",
      "                         \n",
      "    Ref.: Dodge, Yadolah, \"The Concise Encyclopedia of Statistics\", p.232\n",
      "\n",
      "    '''\n",
      "    \n",
      "    ECTS_total = sum([dept.ECTS  for dept in dept_list])\n",
      "    PTCE_total = sum([dept.PTCE  for dept in dept_list])\n",
      "    \n",
      "    deptos_pairs = [(dep0, dep1) for dep0 in dept_list for dep1 in dept_list]\n",
      "    numer = sum(abs(dep0.ECTS * dep1.PTCE  - dep1.ECTS * dep0.PTCE) for (dep0, dep1) in deptos_pairs)\n",
      "    \n",
      "    return numer / (2 * ECTS_total * PTCE_total)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 16
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "new_depts = L_intra + L_inter\n",
      "compute_gini_coefficient(new_depts) #nuevo coeficiente de Gini"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 17,
       "text": [
        "0.22260298561903258"
       ]
      }
     ],
     "prompt_number": 17
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "origins = [dept.origin for dept in new_depts]\n",
      "old_depts = [dept for depts in origins for dept in depts]\n",
      "compute_gini_coefficient(old_depts) #viejo coeficiente de Gini"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 24,
       "text": [
        "0.2392313934693585"
       ]
      }
     ],
     "prompt_number": 24
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "(compute_gini_coefficient(old_depts) - compute_gini_coefficient(new_depts))*100"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 25,
       "text": [
        "1.6628407850325932"
       ]
      }
     ],
     "prompt_number": 25
    }
   ],
   "metadata": {}
  }
 ]
}