subvenciones.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The autoreload extension is already loaded. To reload it, use:\n",
      "  %reload_ext autoreload\n"
     ]
    }
   ],
   "source": [
    "from __future__ import division\n",
    "import csv,sys\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import re\n",
    "#import matplotlib.pyplot as plt\n",
    "from datetime import datetime, timedelta\n",
    "import time, locale\n",
    "#Plot.ly\n",
    "import plotly.plotly as py\n",
    "from plotly.graph_objs import *\n",
    "#from IPython import display as d\n",
    "#import nvd3\n",
    "from money import Money\n",
    "### Librerias Propias\n",
    "from load_data import obtener_provincia_cif\n",
    "from load_data import obtener_tipo_cif\n",
    "from load_data import obtener_tipo_cif_resumen\n",
    "from load_data import string_to_timestamp\n",
    "from load_data import listar_subvenciones_mes_grupo\n",
    "from load_data import filtra_importes_grupo\n",
    "from load_data import get_resumen_convocatoria\n",
    "from load_data import get_programa_partida_pres\n",
    "\n",
    "#from nvd3 import discreteBarChart\n",
    "%load_ext autoreload\n",
    "%autoreload 2\n",
    "\n",
    "#reload(sys)  # Reload does the trick!\n",
    "#sys.setdefaultencoding('UTF8')\n",
    "\n",
    "#nvd3.ipynb.initialize_javascript(use_remote=True)\n",
    "\n",
    "#locale.setlocale(locale.LC_ALL, 'es_ES')\n",
    "#locale.setlocale(locale.LC_ALL, 'C')\n",
    "#locale.format(\"%10.2f\", 1234.567, grouping=True) \n",
    "#pd.options.display.float_format = '{:,.2f}'.format\n",
    "\n",
    "# Static files\n",
    "FILE_SUBVENCIONES = 'data/datos_subvenciones_2.csv'\n",
    "FILE_AYUDAS = 'data/ayudas.json'\n",
    "FILE_ELECCIONES = 'data/04_201105_1.csv'\n",
    "FILE_MUNICIPIOS = 'data/smex99.csv'\n",
    "FILE_ENTES_LOCALES = 'data/entes_locales.csv'\n",
    "FILE_ENTES_JJAA = 'data/entes_jjaa.csv'\n",
    "FILE_FUNCIONAL_PRESUPUESTO = 'data/partidas_presupuesto_gasto.csv'\n",
    "FILE_CONVOCATORIAS_BOJA = \"data/datos_resoluciones_2.csv\"\n",
    "# Referencias de partidas del presupuesto: http://www.juntadeandalucia.es/haciendayadministracionpublica/planif_presup/proy_presupuesto2014/presentacion/presentacion-5.pdf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fuentes de Información\n",
    "----------------------\n",
    "\n",
    "El siguiente análisis está basado en las siguientes fuentes de datos:\n",
    "* Subvenciones. Portal de Datos Abiertos de la Junta de Andalucia\n",
    "* Ayudas. ????\n",
    "* Municipios. Instituto de Estadística y Cartografía de Andalucía\n",
    "* Elecciones. Instituto de Estadística y Cartografía de Andalucía\n",
    "* Entes Municipales. Ministerio de Hacienda y Administraciones Públicas / Secretaría de Estado de Administraciones Públicas. \n",
    "* Entes Autonómicos. Ministerio de Hacienda y Administraciones Públicas / Secretaría de Estado de Administraciones Públicas. (también disponible en Consejería de Hacienda y Administraciones Públicas de la Junta de Andalucía).\n",
    "* Anuario de la Caixa 2014.\n",
    "* Presupuesto 2015 de la Junta de Andalucía\n",
    "\n",
    "Se pueden descargar de:\n",
    "* Datos Abiertos / Secretaría de Estado de Administraciones Públicas. http://www.minhap.gob.es/es-ES/Datos%20Abiertos/Paginas/CatalogoRISPSecretariadeEstadodeAdministracionesPublicas.aspx\n",
    "* http://serviciostelematicos.minhap.gob.es/PubInvCCAA/secciones/FrmListadoEntes.aspx?cdcdad=AX9BV45F&tiporg2=\n",
    "* http://www.anuarieco.lacaixa.comunicacions.com/java/X?cgi=caixa.le_menuGeneral.pattern\n",
    "Referencias de partidas del presupuesto: http://www.juntadeandalucia.es/haciendayadministracionpublica/planif_presup/proy_presupuesto2014/presentacion/presentacion-5.pdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Read files\n",
    "ayudas = pd.read_json(FILE_AYUDAS)\n",
    "municipios = pd.read_csv(FILE_MUNICIPIOS, quotechar='\"', sep=';', decimal=',', thousands='.')\n",
    "elecciones = pd.read_csv(FILE_ELECCIONES, quotechar='\"', sep=';', decimal=',', thousands='.')\n",
    "entes_locales = pd.read_csv(FILE_ENTES_LOCALES, quotechar='\"', sep=',')\n",
    "entes_jjaa = pd.read_csv(FILE_ENTES_JJAA, quotechar='\"', sep=',')\n",
    "subvenciones = pd.read_csv(FILE_SUBVENCIONES, quotechar='\"', sep='|', decimal=',').fillna('')\n",
    "funcional = pd.read_csv(FILE_FUNCIONAL_PRESUPUESTO, quotechar='\"', sep='|', decimal=',').fillna('')\n",
    "convocatorias = pd.read_csv(FILE_CONVOCATORIAS_BOJA, sep='|', encoding='utf-8').fillna('')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "El importe total de las subvenciones includas en los datos abiertos es:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.038.724.228,81 €\n"
     ]
    }
   ],
   "source": [
    "total_subvenciones = subvenciones['importe'].sum() \n",
    "m = Money(amount = total_subvenciones, currency='EUR')\n",
    "print(m.format('es_ES'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Mejora de la información\n",
    "\n",
    "(Data wranling / Data Munging)\n",
    "\n",
    "A continuación se procede a la mejora de la información de base con otras fuentes de información (de las ya cargadas),\n",
    "o de la análisis de la ya disponible."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "subvenciones['mes_concesion_timestamp'] = subvenciones['fecha_concesion'].apply(lambda x: string_to_timestamp(x))\n",
    "subvenciones['fecha_concesion'] = subvenciones['fecha_concesion'].apply(lambda x: datetime.strptime(x, '%d/%m/%y'))\n",
    "subvenciones['mes_concesion'] = subvenciones['fecha_concesion'].apply(lambda x: x.strftime('%Y/%m'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "subvenciones['programa_de_partida'] = subvenciones.partida_pres.apply(get_programa_partida_pres)\n",
    "subvenciones['tipo_beneficiario'] = subvenciones['id_beneficiario'].apply(obtener_tipo_cif)\n",
    "subvenciones['tipo_beneficiario_res'] = subvenciones['id_beneficiario'].apply(obtener_tipo_cif_resumen)\n",
    "subvenciones['tipo_beneficiario_cod'] = subvenciones['id_beneficiario'].apply(lambda x: (\"%s\" % x)[0] if x != \"\" else \"\")\n",
    "subvenciones['provincia'] = subvenciones['id_beneficiario'].apply(obtener_provincia_cif)\n",
    "subvenciones['grupo_pres'] = subvenciones['programa'].apply(lambda x: (\"%s\" % x)[0] if x != \"\" else \"\")\n",
    "subvenciones['provincia_cod_cif'] = subvenciones['id_beneficiario'].apply(lambda x: (\"%s\" % x)[1:3])\n",
    "subvenciones['convocatoria_res'] = subvenciones.convocatoria.apply(get_resumen_convocatoria)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "subvenciones_por_fecha = subvenciones.groupby('fecha_concesion').sum()\n",
    "subvenciones_por_mes = subvenciones.groupby('mes_concesion_timestamp').sum()\n",
    "subvenciones_por_mes_grupo = subvenciones.groupby(['mes_concesion_timestamp','grupo_pres']).sum().reset_index()\n",
    "subvenciones_por_finalidad = subvenciones.groupby('finalidad').sum().sort('importe',ascending=0).reset_index()\n",
    "subvenciones_por_finalidad_beneficiario = subvenciones.groupby(['finalidad','tipo_beneficiario']).sum().sort('importe',ascending=0).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "elecciones['Nombre de Comunidad'] = elecciones['Nombre de Comunidad'].apply(lambda x: x.rstrip())\n",
    "filas_andalucia = elecciones['Nombre de Comunidad'] == 'Andaluc\\xc3\\xada' #Andalucía\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###¿Existen subvenciones no clasificadas?\n",
    "\n",
    "El presupuesto marca el objetivo que se busca a muy grandes rasgos con las subvenciones. Por lo que es importante conocer si la información está o no disponible en el conjunto de datos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6759"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum(subvenciones.programa==\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>convocatoria_res</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ORDEN DE 3 DE JULIO DE 2013</th>\n",
       "      <td>566</td>\n",
       "      <td>22088136.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ORDEN DE 10 DE MARZO DE 2006</th>\n",
       "      <td>112</td>\n",
       "      <td>13654423.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RESOL. 16 D FEBRERO D 2012</th>\n",
       "      <td>407</td>\n",
       "      <td>8347506.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DECRETO-LEY 8/2013</th>\n",
       "      <td>2274</td>\n",
       "      <td>6023391.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ORDEN DE 13 DE ABRIL DE 2010</th>\n",
       "      <td>126</td>\n",
       "      <td>4615182.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ORDEN DE 28 DE MAYO DE 1999</th>\n",
       "      <td>1834</td>\n",
       "      <td>4581545.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RESOL. DE 26 DE ABRIL DE 2013</th>\n",
       "      <td>252</td>\n",
       "      <td>3587719.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RESOL. 1 D AGOSTO D 2011</th>\n",
       "      <td>189</td>\n",
       "      <td>3543660.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RESOLUCION 10 DICIEMBRE 2013</th>\n",
       "      <td>100</td>\n",
       "      <td>2386186.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ORDEN DE 7 DE ENERO DE 2009</th>\n",
       "      <td>91</td>\n",
       "      <td>1981585.09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               count          sum\n",
       "convocatoria_res                                 \n",
       "ORDEN DE 3 DE JULIO DE 2013      566  22088136.05\n",
       "ORDEN DE 10 DE MARZO DE 2006     112  13654423.78\n",
       "RESOL. 16 D FEBRERO D 2012       407   8347506.43\n",
       "DECRETO-LEY 8/2013              2274   6023391.41\n",
       "ORDEN DE 13 DE ABRIL DE 2010     126   4615182.18\n",
       "ORDEN DE 28 DE MAYO DE 1999     1834   4581545.42\n",
       "RESOL. DE 26 DE ABRIL DE 2013    252   3587719.90\n",
       "RESOL. 1 D AGOSTO D 2011         189   3543660.60\n",
       "RESOLUCION 10 DICIEMBRE 2013     100   2386186.29\n",
       "ORDEN DE 7 DE ENERO DE 2009       91   1981585.09"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "subvenciones[subvenciones.programa==\"\"].groupby(['convocatoria_res'])\\\n",
    ".agg(['count','sum'])['importe']\\\n",
    ".sort(['sum','count'], ascending=[False,False]).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Existen por tanto subvenciones que de las que no contamos con la función del presupuesto que le aplica."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Ajustes manuales\n",
    "* La *Orden de 28 de mayo de 1999* tiene como objetivo el sacrificio de animales, como no es posible obtenerla directamente del BOJA se fija contra el programa \"71B\", aunque también está relacionado con el \"71C\"\n",
    "* La *Resolución del 16 de febrero de 2012* tiene como aplicación presupuestaria 01.12.00.01.00.773.48.610, por lo que debería ser 61O\n",
    "* La *Orden de 10 de Marzo de 2006* no indica aplicación presupuestaria, pero revisando los conceptos funcionales del presupuesto parece aplicarle a [43A](http://www.juntadeandalucia.es/economiayhacienda/planif_presup/genero/informe/informe2015/informe-5-b-9.pdf).\n",
    "* La *Resolución del 10 de diciembre de 2013* procederán de la partida 71P\n",
    "* La *Orden de 7 de enero de 2009* hace referencia a varios programas: 31P, 41A, 41C, 41D, 41H, 41J, 41K."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "programas_manuales = { 'ORDEN DE 28 DE MAYO DE 1999' : '71B',\n",
    "                       'RESOL. 16 D FEBRERO D 2012' : '61O',\n",
    "                       'ORDEN DE 10 DE MARZO DE 2006': '43A',\n",
    "                       'ORDEN DE 3 DE JULIO DE 2013' : '71F',\n",
    "                       'RESOLUCION 10 DICIEMBRE 2013' : '71P',\n",
    "                       'SUBV. NOM. PARQUE CIENCIAS SUBVENCION NOMINATIVA PARQUE DE LAS CIENCIAS DE GRANADA, ANO 2014' : '44D',\n",
    "                     }\n",
    "\n",
    "# Asignacion de subvenciones nominativas sin asignar.\n",
    "# 51B es donde el presupuesto reserva partidas para los metros\n",
    "subvenciones = subvenciones.set_value(8088,'programa','51B')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "convocatoria_partida = subvenciones[['convocatoria_res','programa_de_partida']]\n",
    "programas_de_partida_presupuesto = ((convocatoria_partida.drop_duplicates()[convocatoria_partida.programa_de_partida != \"\"])[convocatoria_partida.convocatoria_res != \"\"]).set_index('convocatoria_res')['programa_de_partida'].to_dict()\n",
    "programas_sin_convocatoria = ((convocatoria_partida[convocatoria_partida.programa_de_partida != \"\"])[convocatoria_partida.convocatoria_res == \"\"])['programa_de_partida'].to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "programas_extraidos_boja = dict(zip(convocatorias.resolucion,convocatorias.programas))\n",
    "\n",
    "indice_subvenciones_sin_programa = subvenciones[subvenciones.programa==\"\"].index\n",
    "\n",
    "programas_en_convocatoria = subvenciones['convocatoria'].str.contains('\\s\\d\\d\\D\\s')\n",
    "programas_en_convocatoria_res = subvenciones[programas_en_convocatoria]['convocatoria'].str.findall('\\s\\d\\d\\D\\s').apply(lambda l: l[0])\n",
    "\n",
    "programas_en_partida_pres = subvenciones['partida_pres'].str.contains('\\d\\d\\D')\n",
    "programas_en_partida_pres_res = subvenciones[programas_en_partida_pres]['partida_pres'].str.findall('\\d\\d\\D').apply(lambda l: l[0])\n",
    "\n",
    "for indice in indice_subvenciones_sin_programa:\n",
    "    # Rescatamos el programa de otra columna que incluye la informacion\n",
    "    if indice in programas_en_convocatoria_res:\n",
    "        subvenciones.set_value(indice,'programa',programas_en_convocatoria_res[indice])\n",
    "        continue\n",
    "    if indice in programas_sin_convocatoria:\n",
    "        subvenciones.set_value(indice,'programa',programas_sin_convocatoria[indice])\n",
    "        continue        \n",
    "    convocatoria_actual = subvenciones.iloc[indice].convocatoria_res\n",
    "    if convocatoria_actual in programas_manuales:\n",
    "        programa_manual = programas_manuales[convocatoria_actual]\n",
    "        subvenciones.set_value(indice,'programa',programa_manual)\n",
    "    elif convocatoria_actual in programas_de_partida_presupuesto:\n",
    "        programa_de_partida = programas_de_partida_presupuesto[convocatoria_actual]\n",
    "        subvenciones.set_value(indice,'programa',programa_de_partida)\n",
    "    elif convocatoria_actual in programas_extraidos_boja:\n",
    "        programa_boja = programas_extraidos_boja[convocatoria_actual]\n",
    "        if programa_boja != '':\n",
    "            #subvenciones.iloc[indice].programa = programas_boja\n",
    "            subvenciones.set_value(indice,'programa',programa_boja)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "808 programas de 71103 no han sido encontrados\n",
      "8.806.726,16 € de ayudas de 1.038.724.228,81 € no han sido clasificadas\n"
     ]
    }
   ],
   "source": [
    "print \"%d programas de %d no han sido encontrados\" % (sum(subvenciones.programa==\"\"), len(subvenciones))\n",
    "\n",
    "subvenciones_no_clasificadas = sum(subvenciones[subvenciones.programa==\"\"].importe)\n",
    "money_subvenciones_no_clasificadas = Money(amount = subvenciones_no_clasificadas, currency='EUR')\n",
    "print \"%s de ayudas de %s no han sido clasificadas\" % (money_subvenciones_no_clasificadas.format('es_ES'), m.format('es_ES'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>convocatoria_res</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ORDEN DE 7 DE ENERO DE 2009</th>\n",
       "      <td>91</td>\n",
       "      <td>1981585.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RESOL. DE 20 JUNIO 2013</th>\n",
       "      <td>6</td>\n",
       "      <td>884423.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RESOL D 14 D JULIO D 2010</th>\n",
       "      <td>64</td>\n",
       "      <td>659337.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RESOLUCION 25 NOVIEMBRE 2013</th>\n",
       "      <td>4</td>\n",
       "      <td>600000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ORDEN DE 19 DE ABRIL DE 2007</th>\n",
       "      <td>2</td>\n",
       "      <td>598790.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ORDEN DE 31 DE JULIO DE 2009</th>\n",
       "      <td>4</td>\n",
       "      <td>562422.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ORDEN D 26 D OCTUBRE D 2010</th>\n",
       "      <td>28</td>\n",
       "      <td>546030.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ORDEN DE 23 DE OCTUBRE D 2009</th>\n",
       "      <td>10</td>\n",
       "      <td>451409.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ORDEN D 12 D SEPTIEMBRE D 2008</th>\n",
       "      <td>3</td>\n",
       "      <td>310327.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ORDEN DE 26 DE ENERO DE 2010</th>\n",
       "      <td>1</td>\n",
       "      <td>247477.27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                count         sum\n",
       "convocatoria_res                                 \n",
       "ORDEN DE 7 DE ENERO DE 2009        91  1981585.09\n",
       "RESOL. DE 20 JUNIO 2013             6   884423.93\n",
       "RESOL D 14 D JULIO D 2010          64   659337.63\n",
       "RESOLUCION 25 NOVIEMBRE 2013        4   600000.00\n",
       "ORDEN DE 19 DE ABRIL DE 2007        2   598790.52\n",
       "ORDEN DE 31 DE JULIO DE 2009        4   562422.47\n",
       "ORDEN D 26 D OCTUBRE D 2010        28   546030.74\n",
       "ORDEN DE 23 DE OCTUBRE D 2009      10   451409.30\n",
       "ORDEN D 12 D SEPTIEMBRE D 2008      3   310327.85\n",
       "ORDEN DE 26 DE ENERO DE 2010        1   247477.27"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "subvenciones[subvenciones.programa==\"\"].groupby(['convocatoria_res'])\\\n",
    ".agg(['count','sum'])['importe']\\\n",
    ".sort(['sum','count'], ascending=[False,False]).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>base_reguladora</th>\n",
       "      <th>convocatoria</th>\n",
       "      <th>consejeria</th>\n",
       "      <th>organo_gestor</th>\n",
       "      <th>tipo_subvencion</th>\n",
       "      <th>programa</th>\n",
       "      <th>denominacion_programa</th>\n",
       "      <th>importe</th>\n",
       "      <th>finalidad</th>\n",
       "      <th>...</th>\n",
       "      <th>mes_concesion_timestamp</th>\n",
       "      <th>mes_concesion</th>\n",
       "      <th>programa_de_partida</th>\n",
       "      <th>grupo_pres</th>\n",
       "      <th>tipo_beneficiario</th>\n",
       "      <th>tipo_beneficiario_res</th>\n",
       "      <th>tipo_beneficiario_cod</th>\n",
       "      <th>provincia</th>\n",
       "      <th>provincia_cod_cif</th>\n",
       "      <th>convocatoria_res</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>36824</th>\n",
       "      <td>34247</td>\n",
       "      <td>CONVENIO SAS-FEAFES</td>\n",
       "      <td>SAS-FEAFES SUB.NOMINATIVA A FEAFES ANDALUCIA P...</td>\n",
       "      <td>SERVICIO ANDALUZ DE SALUD</td>\n",
       "      <td>SERVICIO DE APOYO AL S.A.S.</td>\n",
       "      <td>NOMINATIVA</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>150000</td>\n",
       "      <td>AYUDAS A FEDERACIONES DE APAS</td>\n",
       "      <td>...</td>\n",
       "      <td>1.409436e+12</td>\n",
       "      <td>2014/08</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Asociaciones</td>\n",
       "      <td>Personas</td>\n",
       "      <td>G</td>\n",
       "      <td>NA</td>\n",
       "      <td>41</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58605</th>\n",
       "      <td>12340</td>\n",
       "      <td>CONVENIO SAS-PAUSAME</td>\n",
       "      <td>CONVENIO SAS-PAUSAME CONCEDER SUBVENCION A LA ...</td>\n",
       "      <td>SERVICIO ANDALUZ DE SALUD</td>\n",
       "      <td>SERVICIO DE APOYO AL S.A.S.</td>\n",
       "      <td>NOMINATIVA</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>75000</td>\n",
       "      <td>AYUDAS A FEDERACIONES DE APAS</td>\n",
       "      <td>...</td>\n",
       "      <td>1.401487e+12</td>\n",
       "      <td>2014/05</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Asociaciones</td>\n",
       "      <td>Personas</td>\n",
       "      <td>G</td>\n",
       "      <td>NA</td>\n",
       "      <td>18</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id       base_reguladora  \\\n",
       "36824  34247   CONVENIO SAS-FEAFES   \n",
       "58605  12340  CONVENIO SAS-PAUSAME   \n",
       "\n",
       "                                            convocatoria  \\\n",
       "36824  SAS-FEAFES SUB.NOMINATIVA A FEAFES ANDALUCIA P...   \n",
       "58605  CONVENIO SAS-PAUSAME CONCEDER SUBVENCION A LA ...   \n",
       "\n",
       "                      consejeria                organo_gestor tipo_subvencion  \\\n",
       "36824  SERVICIO ANDALUZ DE SALUD  SERVICIO DE APOYO AL S.A.S.      NOMINATIVA   \n",
       "58605  SERVICIO ANDALUZ DE SALUD  SERVICIO DE APOYO AL S.A.S.      NOMINATIVA   \n",
       "\n",
       "      programa denominacion_programa  importe                      finalidad  \\\n",
       "36824                                  150000  AYUDAS A FEDERACIONES DE APAS   \n",
       "58605                                   75000  AYUDAS A FEDERACIONES DE APAS   \n",
       "\n",
       "            ...        mes_concesion_timestamp mes_concesion  \\\n",
       "36824       ...                   1.409436e+12       2014/08   \n",
       "58605       ...                   1.401487e+12       2014/05   \n",
       "\n",
       "      programa_de_partida grupo_pres  tipo_beneficiario tipo_beneficiario_res  \\\n",
       "36824                                      Asociaciones              Personas   \n",
       "58605                                      Asociaciones              Personas   \n",
       "\n",
       "      tipo_beneficiario_cod provincia provincia_cod_cif convocatoria_res  \n",
       "36824                     G        NA                41                   \n",
       "58605                     G        NA                18                   \n",
       "\n",
       "[2 rows x 24 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "subvenciones.query(\" convocatoria_res == '' and programa == ''\") #['convocatoria'])\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Una vez se ha mejorado el dato del programa presupuestario se obtienen las distintas agregaciones del presupuesto"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "subvenciones['grupo_pres'] = subvenciones['programa'].apply(lambda x: (\"%s\" % x)[0] if x != \"\" else \"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Análisis Exploratorio\n",
    "\n",
    "A continuación con la información ya revisada y ampliada se puede proceder a intentar responder a cuestiones sobre la información con que se cuenta.\n",
    "\n",
    "## ¿Cómo se distribuye el gasto a través del tiempo?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\"seamless=\"seamless\" src=\"https://plot.ly/~jdayllon/35.embed\" height=\"525\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lista_mes_concesion=list(subvenciones['mes_concesion'].drop_duplicates())\n",
    "lista_mes_concesion.reverse()\n",
    "lista_mes_concesion\n",
    "\n",
    "data = Data([\n",
    "    Bar(\n",
    "        x=lista_mes_concesion,\n",
    "        y=list(subvenciones_por_mes['importe']),\n",
    "        text=lista_mes_concesion,\n",
    "        marker=Marker(\n",
    "            color='rgb(142, 124, 195)'\n",
    "        )\n",
    "    )\n",
    "])\n",
    "\n",
    "layout = Layout(\n",
    "    title='Distribución del gasto en subvenciones',\n",
    "    font=Font(\n",
    "        family='Raleway, sans-serif'\n",
    "    ),\n",
    "    showlegend=False,\n",
    "    xaxis=XAxis(\n",
    "        tickangle=-45\n",
    "    ),\n",
    "    yaxis=YAxis(\n",
    "        zeroline=False,\n",
    "        gridwidth=2\n",
    "    ),\n",
    "    bargap=0.05\n",
    ")\n",
    "fig = Figure(data=data, layout=layout)\n",
    "#plot_url = py.plot(fig, filename='bar-with-hover-text')\n",
    "py.iplot(data, filename = 'evolucion-subveciones')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "lista_meses_timestamp = subvenciones_por_mes_grupo['mes_concesion_timestamp'].unique()\n",
    "lista_grupos = subvenciones_por_mes_grupo['grupo_pres'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def nueva_fila_mes_grupo(mes, grupo, importe = 0,id=0):\n",
    "    new_data = { 'mes_concesion_timestamp' : [mes],\n",
    "             'grupo_pres' : [grupo],\n",
    "             'importe': [0], \n",
    "             'id': [id]}\n",
    "    return pd.DataFrame(new_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "subvenciones_mes_grupo_trabajo = subvenciones_por_mes_grupo.copy(deep=True)\n",
    "contador_id = subvenciones_mes_grupo_trabajo['id'].argmax()\n",
    "\n",
    "for mes in lista_meses_timestamp:\n",
    "    for grupo in lista_grupos:\n",
    "        query_mes_grupo = \"mes_concesion_timestamp == %s & grupo_pres == '%s'\" % (mes, grupo)\n",
    "        resultados_query = subvenciones_mes_grupo_trabajo.query(query_mes_grupo)\n",
    "        if len(resultados_query) == 0:\n",
    "            contador_id += 1\n",
    "            subvenciones_mes_grupo_trabajo = pd.concat([subvenciones_mes_grupo_trabajo,nueva_fila_mes_grupo(mes,grupo,id=contador_id)])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Subvenciones por área del presupuesto\n",
    "---------------------\n",
    "\n",
    "Utilizando el identificador del presupuesto podemos agrupar las ayudas por el xxx del presupuesto de la Junta de Andalucía y su distribución a lo largo del tiempo.\n",
    "\n",
    "El presupuesto de las comunidades autónomas según su función se clasifican en:\n",
    "* Área,\n",
    "* Politica,\n",
    "* Función y\n",
    "* Programa\n",
    "\n",
    "Los datos de subvenciones tienen detallado hasta la función, no existe detalle del programa."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\"seamless=\"seamless\" src=\"https://plot.ly/~jdayllon/85.embed\" height=\"525\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "subvenciones_por_grupo = subvenciones_por_mes_grupo.groupby('grupo_pres').sum()\n",
    "areas_presupuesto = funcional.query(\"POLITICA=='' and  PROGRAMA ==''\").set_index('AREA')['DESCRIPCION'].to_dict()\n",
    "#total_subvenciones\n",
    "#print list(subvenciones_por_grupo['importe'])\n",
    "trace1 = Bar(\n",
    "    x= areas_presupuesto.values(),\n",
    "    y=list(subvenciones_por_grupo['importe']),\n",
    "    name='SF Zoo'\n",
    ")\n",
    "#trace2 = Bar(\n",
    "#    x=['giraffes', 'orangutans', 'monkeys'],\n",
    "#    y=[12, 18, 29],\n",
    "#    name='LA Zoo'\n",
    "#)\n",
    "data = Data([trace1])\n",
    "\n",
    "\n",
    "layout = Layout(\n",
    "    title='Subvenciones por área del presupuesto',\n",
    "    font=Font(\n",
    "        family='Raleway, sans-serif'\n",
    "    ),\n",
    "    showlegend=False,\n",
    "    xaxis=XAxis(\n",
    "        tickangle=-45\n",
    "    ),\n",
    "    yaxis=YAxis(\n",
    "        zeroline=False,\n",
    "        gridwidth=2\n",
    "    ),\n",
    "    bargap=0.05\n",
    ")\n",
    "\n",
    "fig = Figure(data=data, layout=layout)\n",
    "py.iplot(fig, filename='stacked-bar')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mes_concesion_timestamp</th>\n",
       "      <th>id</th>\n",
       "      <th>importe</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>grupo_pres</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>1.686547e+13</td>\n",
       "      <td>221923801</td>\n",
       "      <td>8.495133e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9.889398e+12</td>\n",
       "      <td>2675677</td>\n",
       "      <td>5.003047e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5.632132e+12</td>\n",
       "      <td>119297</td>\n",
       "      <td>1.229375e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2.114096e+13</td>\n",
       "      <td>371419320</td>\n",
       "      <td>3.965595e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.690210e+13</td>\n",
       "      <td>928131258</td>\n",
       "      <td>1.021258e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1.971321e+13</td>\n",
       "      <td>51749253</td>\n",
       "      <td>7.480284e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1.396217e+12</td>\n",
       "      <td>4672</td>\n",
       "      <td>3.500000e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1.971830e+13</td>\n",
       "      <td>921158832</td>\n",
       "      <td>2.883880e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1.408081e+13</td>\n",
       "      <td>30671746</td>\n",
       "      <td>8.642083e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            mes_concesion_timestamp         id       importe\n",
       "grupo_pres                                                  \n",
       "                       1.686547e+13  221923801  8.495133e+07\n",
       "1                      9.889398e+12    2675677  5.003047e+06\n",
       "2                      5.632132e+12     119297  1.229375e+05\n",
       "3                      2.114096e+13  371419320  3.965595e+08\n",
       "4                      1.690210e+13  928131258  1.021258e+08\n",
       "5                      1.971321e+13   51749253  7.480284e+07\n",
       "6                      1.396217e+12       4672  3.500000e+05\n",
       "7                      1.971830e+13  921158832  2.883880e+08\n",
       "8                      1.408081e+13   30671746  8.642083e+07"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#areas_presupuesto\n",
    "subvenciones_por_grupo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#subvenciones_por_finalidad_beneficiario['finalidad'].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<!DOCTYPE html>\n",
       "<html lang=\"en\">\n",
       "    <head>\n",
       "        <meta charset=\"utf-8\" />\n",
       "        <link href=\"https://cdnjs.cloudflare.com/ajax/libs/nvd3/1.7.0/nv.d3.min.css\" rel=\"stylesheet\" />\n",
       "        <script src=\"https://cdnjs.cloudflare.com/ajax/libs/d3/3.5.5/d3.min.js\"></script>\n",
       "        <script src=\"https://cdnjs.cloudflare.com/ajax/libs/nvd3/1.7.0/nv.d3.min.js\"></script>\n",
       "    </head>\n",
       "    <body>\n",
       "        \n",
       "    \n",
       "\n",
       "<h2>Subvenciones por area</h2>\n",
       "\n",
       "<div id=\"subvenciones_por_area\"><svg style=\"width:850px;height:450px;\"></svg></div>\n",
       "\n",
       "\n",
       "    <script>\n",
       "\n",
       "\n",
       "            data_subvenciones_por_area=[{\"values\": [{\"y\": 0.0, \"x\": 1391122800000.0}, {\"y\": 2364606.0, \"x\": 1393542000000.0}, {\"y\": 0.0, \"x\": 1396216800000.0}, {\"y\": 0.0, \"x\": 1398808800000.0}, {\"y\": 0.0, \"x\": 1401487200000.0}, {\"y\": 0.0, \"x\": 1404079200000.0}, {\"y\": 421448.0, \"x\": 1406757600000.0}, {\"y\": 0.0, \"x\": 1409436000000.0}, {\"y\": 50000.0, \"x\": 1412028000000.0}, {\"y\": 11616.57, \"x\": 1414710000000.0}, {\"y\": 1647968.0699999998, \"x\": 1417302000000.0}, {\"y\": 18000.0, \"x\": 1419980400000.0}, {\"y\": 0.0, \"x\": 1422658800000.0}, {\"y\": 489408.0, \"x\": 1425078000000.0}, {\"y\": 0.0, \"x\": 1427752800000.0}], \"key\": \"1\", \"yAxis\": \"1\"}, {\"values\": [{\"y\": 0.0, \"x\": 1391122800000.0}, {\"y\": 95000.0, \"x\": 1393542000000.0}, {\"y\": 0.0, \"x\": 1396216800000.0}, {\"y\": 0.0, \"x\": 1398808800000.0}, {\"y\": 0.0, \"x\": 1401487200000.0}, {\"y\": 9437.5, \"x\": 1404079200000.0}, {\"y\": 6000.0, \"x\": 1406757600000.0}, {\"y\": 0.0, \"x\": 1409436000000.0}, {\"y\": 0.0, \"x\": 1412028000000.0}, {\"y\": 0.0, \"x\": 1414710000000.0}, {\"y\": 0.0, \"x\": 1417302000000.0}, {\"y\": 0.0, \"x\": 1419980400000.0}, {\"y\": 0.0, \"x\": 1422658800000.0}, {\"y\": 0.0, \"x\": 1425078000000.0}, {\"y\": 12500.0, \"x\": 1427752800000.0}], \"key\": \"2\", \"yAxis\": \"1\"}, {\"values\": [{\"y\": 11146019.829999993, \"x\": 1391122800000.0}, {\"y\": 1492566.3900000001, \"x\": 1393542000000.0}, {\"y\": 3176851.8700000006, \"x\": 1396216800000.0}, {\"y\": 5480667.4799999986, \"x\": 1398808800000.0}, {\"y\": 3291973.5699999994, \"x\": 1401487200000.0}, {\"y\": 5533100.919999999, \"x\": 1404079200000.0}, {\"y\": 8842959.9299999997, \"x\": 1406757600000.0}, {\"y\": 4159566.5899999999, \"x\": 1409436000000.0}, {\"y\": 182916599.21999991, \"x\": 1412028000000.0}, {\"y\": 94617290.699999809, \"x\": 1414710000000.0}, {\"y\": 14292492.689999996, \"x\": 1417302000000.0}, {\"y\": 50871926.7000001, \"x\": 1419980400000.0}, {\"y\": 2603657.2400000002, \"x\": 1422658800000.0}, {\"y\": 159368.06, \"x\": 1425078000000.0}, {\"y\": 7974428.5799999973, \"x\": 1427752800000.0}], \"key\": \"3\", \"yAxis\": \"1\"}, {\"values\": [{\"y\": 0.0, \"x\": 1391122800000.0}, {\"y\": 189.13999999999999, \"x\": 1393542000000.0}, {\"y\": 5167464.7599999998, \"x\": 1396216800000.0}, {\"y\": 443889.47999999998, \"x\": 1398808800000.0}, {\"y\": 5509609.790000001, \"x\": 1401487200000.0}, {\"y\": 12113859.979999993, \"x\": 1404079200000.0}, {\"y\": 40008855.559999995, \"x\": 1406757600000.0}, {\"y\": 1076443.9499999993, \"x\": 1409436000000.0}, {\"y\": 5234775.5000000158, \"x\": 1412028000000.0}, {\"y\": 24092673.310000088, \"x\": 1414710000000.0}, {\"y\": 4971658.0000000009, \"x\": 1417302000000.0}, {\"y\": 3130033.0400000061, \"x\": 1419980400000.0}, {\"y\": 0.0, \"x\": 1422658800000.0}, {\"y\": 0.0, \"x\": 1425078000000.0}, {\"y\": 376364.34000000003, \"x\": 1427752800000.0}], \"key\": \"4\", \"yAxis\": \"1\"}, {\"values\": [{\"y\": 2003.52, \"x\": 1391122800000.0}, {\"y\": 10590151.619999999, \"x\": 1393542000000.0}, {\"y\": 303613.67999999999, \"x\": 1396216800000.0}, {\"y\": 250159.23000000001, \"x\": 1398808800000.0}, {\"y\": 545004.10000000021, \"x\": 1401487200000.0}, {\"y\": 7272535.9000000013, \"x\": 1404079200000.0}, {\"y\": 1203041.6500000001, \"x\": 1406757600000.0}, {\"y\": 7903038.6200000038, \"x\": 1409436000000.0}, {\"y\": 3008416.6400000001, \"x\": 1412028000000.0}, {\"y\": 7776686.580000001, \"x\": 1414710000000.0}, {\"y\": 1000.0, \"x\": 1417302000000.0}, {\"y\": 28680965.290000003, \"x\": 1419980400000.0}, {\"y\": 2530466.27, \"x\": 1422658800000.0}, {\"y\": 4735760.4900000012, \"x\": 1425078000000.0}, {\"y\": 0.0, \"x\": 1427752800000.0}], \"key\": \"5\", \"yAxis\": \"1\"}, {\"values\": [{\"y\": 0.0, \"x\": 1391122800000.0}, {\"y\": 0.0, \"x\": 1393542000000.0}, {\"y\": 350000.0, \"x\": 1396216800000.0}, {\"y\": 0.0, \"x\": 1398808800000.0}, {\"y\": 0.0, \"x\": 1401487200000.0}, {\"y\": 0.0, \"x\": 1404079200000.0}, {\"y\": 0.0, \"x\": 1406757600000.0}, {\"y\": 0.0, \"x\": 1409436000000.0}, {\"y\": 0.0, \"x\": 1412028000000.0}, {\"y\": 0.0, \"x\": 1414710000000.0}, {\"y\": 0.0, \"x\": 1417302000000.0}, {\"y\": 0.0, \"x\": 1419980400000.0}, {\"y\": 0.0, \"x\": 1422658800000.0}, {\"y\": 0.0, \"x\": 1425078000000.0}, {\"y\": 0.0, \"x\": 1427752800000.0}], \"key\": \"6\", \"yAxis\": \"1\"}, {\"values\": [{\"y\": 13555373.80000001, \"x\": 1391122800000.0}, {\"y\": 8168072.3000000007, \"x\": 1393542000000.0}, {\"y\": 27125700.680000599, \"x\": 1396216800000.0}, {\"y\": 44505775.7000001, \"x\": 1398808800000.0}, {\"y\": 21671308.549999975, \"x\": 1401487200000.0}, {\"y\": 29922474.620000008, \"x\": 1404079200000.0}, {\"y\": 8595435.3099999875, \"x\": 1406757600000.0}, {\"y\": 16609926.389999969, \"x\": 1409436000000.0}, {\"y\": 3553301.6299999994, \"x\": 1412028000000.0}, {\"y\": 48001925.680000074, \"x\": 1414710000000.0}, {\"y\": 25692143.139999986, \"x\": 1417302000000.0}, {\"y\": 37910611.020000003, \"x\": 1419980400000.0}, {\"y\": 0.0, \"x\": 1422658800000.0}, {\"y\": 2091674.0, \"x\": 1425078000000.0}, {\"y\": 984231.25999999838, \"x\": 1427752800000.0}], \"key\": \"7\", \"yAxis\": \"1\"}, {\"values\": [{\"y\": 0.0, \"x\": 1391122800000.0}, {\"y\": 0.0, \"x\": 1393542000000.0}, {\"y\": 398284.0, \"x\": 1396216800000.0}, {\"y\": 2393311.0, \"x\": 1398808800000.0}, {\"y\": 344928.0, \"x\": 1401487200000.0}, {\"y\": 44558872.32, \"x\": 1404079200000.0}, {\"y\": 299210.0, \"x\": 1406757600000.0}, {\"y\": 269999.15000000002, \"x\": 1409436000000.0}, {\"y\": 403585.44, \"x\": 1412028000000.0}, {\"y\": 4379692.4699999988, \"x\": 1414710000000.0}, {\"y\": 218188.0, \"x\": 1417302000000.0}, {\"y\": 33154755.489999995, \"x\": 1419980400000.0}, {\"y\": 0.0, \"x\": 1422658800000.0}, {\"y\": 0.0, \"x\": 1425078000000.0}, {\"y\": 0.0, \"x\": 1427752800000.0}], \"key\": \"8\", \"yAxis\": \"1\"}, {\"values\": [{\"y\": 275057.46999999997, \"x\": 1391122800000.0}, {\"y\": 5125164.7300000014, \"x\": 1393542000000.0}, {\"y\": 5357563.2800000031, \"x\": 1396216800000.0}, {\"y\": 5163649.4600000009, \"x\": 1398808800000.0}, {\"y\": 6104405.6600000057, \"x\": 1401487200000.0}, {\"y\": 3143724.6500000008, \"x\": 1404079200000.0}, {\"y\": 3599596.4500000011, \"x\": 1406757600000.0}, {\"y\": 1986140.7400000002, \"x\": 1409436000000.0}, {\"y\": 5219507.6400000015, \"x\": 1412028000000.0}, {\"y\": 38135783.939999983, \"x\": 1414710000000.0}, {\"y\": 6545984.549999998, \"x\": 1417302000000.0}, {\"y\": 4294755.9400000069, \"x\": 1419980400000.0}, {\"y\": 0.0, \"x\": 1422658800000.0}, {\"y\": 0.0, \"x\": 1425078000000.0}, {\"y\": 0.0, \"x\": 1427752800000.0}], \"key\": \"n\", \"yAxis\": \"1\"}];\n",
       "\n",
       "    nv.addGraph(function() {\n",
       "        var chart = nv.models.stackedAreaChart().useInteractiveGuideline(true);\n",
       "\n",
       "        chart.margin({top: 30, right: 60, bottom: 20, left: 100});\n",
       "\n",
       "        var datum = data_subvenciones_por_area;\n",
       "\n",
       "\n",
       "\n",
       "\n",
       "            chart.xAxis\n",
       "                .tickFormat(function(d) { return d3.time.format('%d %b %Y')(new Date(parseInt(d))) }\n",
       ");\n",
       "            chart.yAxis\n",
       "                .tickFormat(d3.format(',.2f'));\n",
       "\n",
       "                chart.tooltipContent(function(key, y, e, graph) {\n",
       "                    var x = d3.time.format(\"%d %b %Y\")(new Date(parseInt(graph.point.x)));\n",
       "                    var y = String(graph.point.y);\n",
       "                    var y = String(graph.point.y);\n",
       "\n",
       "                    tooltip_str = '<center><b>'+key+'</b></center>' + y + ' on ' + x;\n",
       "                    return tooltip_str;\n",
       "                });\n",
       "\n",
       "      chart.showLegend(true);\n",
       "\n",
       "\n",
       "\n",
       "        \n",
       "\n",
       "\n",
       "        d3.select('#subvenciones_por_area svg')\n",
       "            .datum(datum)\n",
       "            .transition().duration(500)\n",
       "            .attr('width', 850)\n",
       "            .attr('height', 450)\n",
       "            .call(chart);\n",
       "\n",
       "\n",
       "    });\n",
       "\n",
       "\n",
       "\n",
       "    </script>\n",
       "\n",
       "    </body>\n",
       "</html>"
      ],
      "text/plain": [
       "<nvd3.stackedAreaChart.stackedAreaChart at 0x115016210>"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chart4_name = \"Subvenciones por area\"\n",
    "chart4 = nvd3.stackedAreaChart(name=chart4_name,height=450,width=850,use_interactive_guideline=True, x_is_date=True)\n",
    "chart4.set_containerheader(\"\\n\\n<h2>\" + chart4_name + \"</h2>\\n\\n\")\n",
    "xdata = list(subvenciones_por_mes_grupo.mes_concesion_timestamp.drop_duplicates())\n",
    "\n",
    "for grupo in subvenciones_mes_grupo_trabajo.grupo_pres.order().drop_duplicates():\n",
    "    chart4.add_serie(name=grupo, y=listar_subvenciones_mes_grupo(subvenciones_mes_grupo_trabajo,grupo), x=xdata)\n",
    "\n",
    "chart4.margin_left = 100\n",
    "chart4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "Por Identificadores\n",
    "-------------------"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        G\n",
       "1        G\n",
       "2        Q\n",
       "3        G\n",
       "4        G\n",
       "5        G\n",
       "6        G\n",
       "7        G\n",
       "8        G\n",
       "9        G\n",
       "10       Q\n",
       "11       G\n",
       "12       G\n",
       "13       G\n",
       "14       G\n",
       "15       G\n",
       "16       Q\n",
       "17       G\n",
       "18       7\n",
       "19       P\n",
       "20       P\n",
       "21       P\n",
       "22       G\n",
       "23       P\n",
       "24       P\n",
       "25       P\n",
       "26       P\n",
       "27       P\n",
       "28       P\n",
       "29       P\n",
       "        ..\n",
       "71073    B\n",
       "71074    3\n",
       "71075    3\n",
       "71076    B\n",
       "71077    2\n",
       "71078    3\n",
       "71079    3\n",
       "71080    3\n",
       "71081    3\n",
       "71082    2\n",
       "71083    5\n",
       "71084    2\n",
       "71085    0\n",
       "71086    2\n",
       "71087    2\n",
       "71088    3\n",
       "71089    F\n",
       "71090    2\n",
       "71091    2\n",
       "71092    B\n",
       "71093    7\n",
       "71094    5\n",
       "71095    7\n",
       "71096    2\n",
       "71097    7\n",
       "71098    7\n",
       "71099    2\n",
       "71100    7\n",
       "71101    3\n",
       "71102    P\n",
       "Name: id_beneficiario, dtype: object"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "subvenciones['tipo_beneficiario'] = subvenciones['id_beneficiario'].apply(lambda x: (\"%s\" % x)[0])\n",
    "subvenciones['id_beneficiario'].apply(lambda x: (\"%s\" % x)[0])#.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                                             Asociaciones\n",
       "1                                             Asociaciones\n",
       "2        Organismos autónomos, estatales o no, y asimil...\n",
       "3                                             Asociaciones\n",
       "4                                             Asociaciones\n",
       "5                                             Asociaciones\n",
       "6                                             Asociaciones\n",
       "7                                             Asociaciones\n",
       "8                                             Asociaciones\n",
       "9                                             Asociaciones\n",
       "10       Organismos autónomos, estatales o no, y asimil...\n",
       "11                                            Asociaciones\n",
       "12                                            Asociaciones\n",
       "13                                            Asociaciones\n",
       "14                                            Asociaciones\n",
       "15                                            Asociaciones\n",
       "16       Organismos autónomos, estatales o no, y asimil...\n",
       "17                                            Asociaciones\n",
       "18                                                      NA\n",
       "19                                   Corporaciones locales\n",
       "20                                   Corporaciones locales\n",
       "21                                   Corporaciones locales\n",
       "22                                            Asociaciones\n",
       "23                                   Corporaciones locales\n",
       "24                                   Corporaciones locales\n",
       "25                                   Corporaciones locales\n",
       "26                                   Corporaciones locales\n",
       "27                                   Corporaciones locales\n",
       "28                                   Corporaciones locales\n",
       "29                                   Corporaciones locales\n",
       "                               ...                        \n",
       "71073               Sociedades de responsabilidad limitada\n",
       "71074                                                   NA\n",
       "71075                                                   NA\n",
       "71076               Sociedades de responsabilidad limitada\n",
       "71077                                                   NA\n",
       "71078                                                   NA\n",
       "71079                                                   NA\n",
       "71080                                                   NA\n",
       "71081                                                   NA\n",
       "71082                                                   NA\n",
       "71083                                                   NA\n",
       "71084                                                   NA\n",
       "71085                                                   NA\n",
       "71086                                                   NA\n",
       "71087                                                   NA\n",
       "71088                                                   NA\n",
       "71089                              Sociedades cooperativas\n",
       "71090                                                   NA\n",
       "71091                                                   NA\n",
       "71092               Sociedades de responsabilidad limitada\n",
       "71093                                                   NA\n",
       "71094                                                   NA\n",
       "71095                                                   NA\n",
       "71096                                                   NA\n",
       "71097                                                   NA\n",
       "71098                                                   NA\n",
       "71099                                                   NA\n",
       "71100                                                   NA\n",
       "71101                                                   NA\n",
       "71102                                Corporaciones locales\n",
       "Name: id_beneficiario, dtype: object"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#subvenciones['id_beneficiario'].apply(lambda x: (\"%s\" % x)[1:3])#.drop_duplicates()\n",
    "subvenciones['id_beneficiario'].apply(obtener_tipo_cif)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Series([], Name: id_beneficiario, dtype: object)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "subvenciones.query(\"tipo_beneficiario_cod =='P' and provincia == 'Madrid'\")['id_beneficiario']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>base_reguladora</th>\n",
       "      <th>convocatoria</th>\n",
       "      <th>consejeria</th>\n",
       "      <th>organo_gestor</th>\n",
       "      <th>tipo_subvencion</th>\n",
       "      <th>programa</th>\n",
       "      <th>denominacion_programa</th>\n",
       "      <th>importe</th>\n",
       "      <th>finalidad</th>\n",
       "      <th>id_beneficiario</th>\n",
       "      <th>nombre_beneficiario</th>\n",
       "      <th>fecha_concesion</th>\n",
       "      <th>partida_pres</th>\n",
       "      <th>mes_concesion</th>\n",
       "      <th>grupo_pres</th>\n",
       "      <th>tipo_beneficiario</th>\n",
       "      <th>tipo_beneficiario_cod</th>\n",
       "      <th>provincia_cod_cif</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>provincia</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>NA</th>\n",
       "      <td>71103</td>\n",
       "      <td>71103</td>\n",
       "      <td>71103</td>\n",
       "      <td>71103</td>\n",
       "      <td>71103</td>\n",
       "      <td>71103</td>\n",
       "      <td>64343</td>\n",
       "      <td>64343</td>\n",
       "      <td>71103</td>\n",
       "      <td>71103</td>\n",
       "      <td>71094</td>\n",
       "      <td>71103</td>\n",
       "      <td>71103</td>\n",
       "      <td>566</td>\n",
       "      <td>71103</td>\n",
       "      <td>71103</td>\n",
       "      <td>71103</td>\n",
       "      <td>71103</td>\n",
       "      <td>71103</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id  base_reguladora  convocatoria  consejeria  organo_gestor  \\\n",
       "provincia                                                                    \n",
       "NA         71103            71103         71103       71103          71103   \n",
       "\n",
       "           tipo_subvencion  programa  denominacion_programa  importe  \\\n",
       "provincia                                                              \n",
       "NA                   71103     64343                  64343    71103   \n",
       "\n",
       "           finalidad  id_beneficiario  nombre_beneficiario  fecha_concesion  \\\n",
       "provincia                                                                     \n",
       "NA             71103            71094                71103            71103   \n",
       "\n",
       "           partida_pres  mes_concesion  grupo_pres  tipo_beneficiario  \\\n",
       "provincia                                                               \n",
       "NA                  566          71103       71103              71103   \n",
       "\n",
       "           tipo_beneficiario_cod  provincia_cod_cif  \n",
       "provincia                                            \n",
       "NA                         71103              71103  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "subvenciones.groupby(['provincia']).count()\n",
    "#subvenciones.query(\"provincia =='Teruel'\")['id_beneficiario']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   2015-03-27\n",
       "1   2015-03-27\n",
       "2   2015-03-27\n",
       "3   2015-03-27\n",
       "4   2015-03-27\n",
       "Name: fecha_concesion, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "subvencione"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      31G\n",
       "1      31G\n",
       "2      31G\n",
       "3      31G\n",
       "4      31G\n",
       "5      31G\n",
       "6      31G\n",
       "7      31G\n",
       "8      31G\n",
       "9      31G\n",
       "10     31G\n",
       "11     31G\n",
       "12     31G\n",
       "13     31G\n",
       "14     31G\n",
       "15     31G\n",
       "16     31G\n",
       "17     31G\n",
       "18     32F\n",
       "19     43A\n",
       "20     43A\n",
       "21     43A\n",
       "22     71B\n",
       "23     31B\n",
       "24     31B\n",
       "25     31E\n",
       "26     31B\n",
       "27     31G\n",
       "28     31G\n",
       "29     31G\n",
       "      ... \n",
       "536    31B\n",
       "537    11A\n",
       "540    11A\n",
       "541    75D\n",
       "570    31G\n",
       "571    31G\n",
       "572    31G\n",
       "573    31G\n",
       "574    31G\n",
       "575    31G\n",
       "576    31G\n",
       "577    31G\n",
       "578    31G\n",
       "579    31G\n",
       "580    31G\n",
       "581    31G\n",
       "582    31G\n",
       "583    31G\n",
       "584    31G\n",
       "585    31G\n",
       "586    31G\n",
       "587    31G\n",
       "588    31G\n",
       "589    31G\n",
       "590    31G\n",
       "591    31G\n",
       "592    31G\n",
       "593    31G\n",
       "594    31G\n",
       "596    32D\n",
       "Name: partida_pres, dtype: object"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        3\n",
       "1        3\n",
       "2        3\n",
       "3        3\n",
       "4        3\n",
       "5        3\n",
       "6        3\n",
       "7        3\n",
       "8        3\n",
       "9        3\n",
       "10       3\n",
       "11       3\n",
       "12       3\n",
       "13       3\n",
       "14       3\n",
       "15       3\n",
       "16       3\n",
       "17       3\n",
       "18       3\n",
       "19       4\n",
       "20       4\n",
       "21       4\n",
       "22       7\n",
       "23       3\n",
       "24       3\n",
       "25       3\n",
       "26       3\n",
       "27       3\n",
       "28       3\n",
       "29       3\n",
       "        ..\n",
       "71073    7\n",
       "71074    7\n",
       "71075    7\n",
       "71076    7\n",
       "71077    7\n",
       "71078    7\n",
       "71079    7\n",
       "71080    7\n",
       "71081    7\n",
       "71082    7\n",
       "71083    7\n",
       "71084    7\n",
       "71085    7\n",
       "71086    7\n",
       "71087    7\n",
       "71088    7\n",
       "71089    7\n",
       "71090    7\n",
       "71091    7\n",
       "71092    7\n",
       "71093    7\n",
       "71094    7\n",
       "71095    7\n",
       "71096    7\n",
       "71097    7\n",
       "71098    7\n",
       "71099    7\n",
       "71100    7\n",
       "71101    7\n",
       "71102    3\n",
       "Name: programa, dtype: object"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        [31G]\n",
       "1        [31G]\n",
       "2        [31G]\n",
       "3        [31G]\n",
       "4        [31G]\n",
       "5        [31G]\n",
       "6        [31G]\n",
       "7        [31G]\n",
       "8        [31G]\n",
       "9        [31G]\n",
       "10       [31G]\n",
       "11       [31G]\n",
       "12       [31G]\n",
       "13       [31G]\n",
       "14       [31G]\n",
       "15       [31G]\n",
       "16       [31G]\n",
       "17       [31G]\n",
       "18       [32F]\n",
       "19       [43A]\n",
       "20       [43A]\n",
       "21       [43A]\n",
       "22       [71B]\n",
       "23       [31B]\n",
       "24       [31B]\n",
       "25       [31E]\n",
       "26       [31B]\n",
       "27       [31G]\n",
       "28       [31G]\n",
       "29       [31G]\n",
       "         ...  \n",
       "71073       []\n",
       "71074       []\n",
       "71075       []\n",
       "71076       []\n",
       "71077       []\n",
       "71078       []\n",
       "71079       []\n",
       "71080       []\n",
       "71081       []\n",
       "71082       []\n",
       "71083       []\n",
       "71084       []\n",
       "71085       []\n",
       "71086       []\n",
       "71087       []\n",
       "71088       []\n",
       "71089       []\n",
       "71090       []\n",
       "71091       []\n",
       "71092       []\n",
       "71093       []\n",
       "71094       []\n",
       "71095       []\n",
       "71096       []\n",
       "71097       []\n",
       "71098       []\n",
       "71099       []\n",
       "71100       []\n",
       "71101       []\n",
       "71102       []\n",
       "Name: partida_pres, dtype: object"
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "subvenciones['partida_pres'].apply(lambda x: re.findall(\"\\/(\\d\\d\\D)\\/\",x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'ORDEN 28 MAYO 2014': '31B',\n",
       " 'ORDEN 4 DE SEPTIEMBRE DE 2014': '31J',\n",
       " 'ORDEN DE 10 DE MARZO DE 2006': '43A',\n",
       " 'ORDEN DE 13 DE ABRIL DE 2010': '71B',\n",
       " 'ORDEN DE 15 DE OCTUBRE DE 2013': '32D',\n",
       " 'ORDEN DE 16 DE MAYO DE 2008': '71P',\n",
       " 'ORDEN DE 20 DE JUNIO DE 2005': '31E',\n",
       " 'ORDEN DE 21 DE OCTUBRE DE 2014': '71B',\n",
       " 'ORDEN DE 21 JULIO DE 2005': '31B',\n",
       " 'ORDEN DE 21 OCTUBRE DE 2014': '71B',\n",
       " 'ORDEN DE 24 DE JULIO 2014': '32F',\n",
       " 'ORDEN DE 24 JULIO DE 2014': '32F',\n",
       " 'ORDEN DE 25 DE ABRIL DE 2014': '32G',\n",
       " 'RESOLUCION DE 12 MARZO DE 2014': '41D',\n",
       " 'RESOLUCION DE 24 DE JUNIO 2014': '31P',\n",
       " 'RESOLUCI\\xc3\\x93N DE 19 DE DICIEMBRE DE 2008': '71P',\n",
       " 'SUBV. NOMINATIVA COL. OFICIAL PSICOLOGOS AND. OCCIDENTAL 2015': '22B',\n",
       " 'SUBVENCI\\xc3\\x93N NOMINATIVA GASTOS DE PERSONAL FREAAE 2015': '75D'}"
      ]
     },
     "execution_count": 197,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{216: '31P', 537: '11A', 540: '11A'}"
      ]
     },
     "execution_count": 221,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "convocatoria_partida = subvenciones[['convocatoria_res','programa_de_partida']]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}