Skip to content

Instantly share code, notes, and snippets.

@jazzido

jazzido/presu_bb.ipynb

Last active November 8, 2017 14:59
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jazzido/782b645b87b1876e48d61e8f4252ef8f to your computer and use it in GitHub Desktop.
Save jazzido/782b645b87b1876e48d61e8f4252ef8f to your computer and use it in GitHub Desktop.
Download ZIP
Análisis ejecución presupuesto Municipio de Bahía blanca
Display the source blob
Display the rendered blob
Raw
presu_bb.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Ejecución Presupuesto Bahía Blanca\n",
"\n",
"Análisis _quick and dirty_ del porcentaje de ejecución del presupuesto de gastos de la Municipalidad de Bahía Blanca\n",
"\n",
"Por [Manuel Aristarán](https://twitter.com/manuelaristaran). Datos obtenidos el 7/11/2017"
]
},
{
"cell_type": "code",
"execution_count": 52,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"import requests\n",
"import pandas as pd"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Obtener datos del [sitio de información presupuestaria](http://www.bahia.gob.ar/economia/presupuesto/). En dos pedidos separados, obtenemos las series de presupuesto vigente y de presupuesto ejecutado. El sitio de MBB provee esta información para la clasificación _administrativa_ (secretarías) y el primer nivel de la clasificación _objeto del gasto_."
]
},
{
"cell_type": "code",
"execution_count": 53,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"resp = requests.get(\"http://www.bahia.gob.ar/presupuesto/data/gestorDatos.php?anio=2017&apertura=Gastos&a=1\")\n",
"# El sitio de MBB declara content-type y encoding erróneos, tenemos que forzar el encoding de la respuesta\n",
"resp.encoding = 'utf-8'\n",
"\n",
"gasto = pd.DataFrame(resp.json())[['den', 'gasto', 'valor']]\n",
"\n",
"resp = requests.get(\"http://www.bahia.gob.ar/presupuesto/data/gestorDatos.php?anio=2017&apertura=Presupuestado&a=1\")\n",
"resp.encoding = 'utf-8'\n",
"\n",
"presupuesto = pd.DataFrame(resp.json())[['den', 'gasto', 'valor']]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Combinamos ambas series en una única tabla."
]
},
{
"cell_type": "code",
"execution_count": 54,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"df = gasto.merge(presupuesto, on=['den', 'gasto']).rename(columns={'valor_x': 'ejecutado', 'valor_y': 'presupuestado'})"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Agrupamos por la clasificación administrativa, y agregamos una columna `porcentaje_ejecutado`, definida como la razón entre el ejecutado y el vigente."
]
},
{
"cell_type": "code",
"execution_count": 55,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>ejecutado</th>\n",
" <th>presupuestado</th>\n",
" <th>porcentaje_ejecutado</th>\n",
" </tr>\n",
" <tr>\n",
" <th>den</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>Secretaría de Infraestructura</th>\n",
" <td>7.749446e+08</td>\n",
" <td>1.431288e+09</td>\n",
" <td>54.143157</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Secretaría de Hacienda y Desarrollo Económico</th>\n",
" <td>1.915754e+08</td>\n",
" <td>2.723665e+08</td>\n",
" <td>70.337373</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Intendencia</th>\n",
" <td>2.993534e+07</td>\n",
" <td>3.670817e+07</td>\n",
" <td>81.549529</td>\n",
" </tr>\n",
" <tr>\n",
" <th>H.C.D.</th>\n",
" <td>6.013235e+07</td>\n",
" <td>7.310287e+07</td>\n",
" <td>82.257173</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Secretaría de Salud</th>\n",
" <td>6.662521e+08</td>\n",
" <td>7.946992e+08</td>\n",
" <td>83.837016</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Secretaría de Políticas Sociales</th>\n",
" <td>3.124093e+08</td>\n",
" <td>3.715259e+08</td>\n",
" <td>84.088158</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Instituto Cultural</th>\n",
" <td>4.572383e+07</td>\n",
" <td>5.407428e+07</td>\n",
" <td>84.557445</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Secretaría de Modernización y Gobierno Abierto</th>\n",
" <td>1.988187e+07</td>\n",
" <td>2.234123e+07</td>\n",
" <td>88.991811</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Secretaría de Asesoría Letrada</th>\n",
" <td>1.278986e+07</td>\n",
" <td>1.435758e+07</td>\n",
" <td>89.080901</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Secretaría de Gestión Ambiental</th>\n",
" <td>4.213700e+08</td>\n",
" <td>4.600235e+08</td>\n",
" <td>91.597505</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Secretaría de Gobierno</th>\n",
" <td>2.470516e+08</td>\n",
" <td>2.658670e+08</td>\n",
" <td>92.923008</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Secretaría de Innov. Tecnológica Desar. Creativo</th>\n",
" <td>5.957617e+06</td>\n",
" <td>5.711603e+06</td>\n",
" <td>104.307260</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Secretaría de Seguridad y Protección Ciudadana</th>\n",
" <td>1.593196e+08</td>\n",
" <td>1.471805e+08</td>\n",
" <td>108.247747</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" ejecutado presupuestado \\\n",
"den \n",
"Secretaría de Infraestructura 7.749446e+08 1.431288e+09 \n",
"Secretaría de Hacienda y Desarrollo Económico 1.915754e+08 2.723665e+08 \n",
"Intendencia 2.993534e+07 3.670817e+07 \n",
"H.C.D. 6.013235e+07 7.310287e+07 \n",
"Secretaría de Salud 6.662521e+08 7.946992e+08 \n",
"Secretaría de Políticas Sociales 3.124093e+08 3.715259e+08 \n",
"Instituto Cultural 4.572383e+07 5.407428e+07 \n",
"Secretaría de Modernización y Gobierno Abierto 1.988187e+07 2.234123e+07 \n",
"Secretaría de Asesoría Letrada 1.278986e+07 1.435758e+07 \n",
"Secretaría de Gestión Ambiental 4.213700e+08 4.600235e+08 \n",
"Secretaría de Gobierno 2.470516e+08 2.658670e+08 \n",
"Secretaría de Innov. Tecnológica Desar. Creativo 5.957617e+06 5.711603e+06 \n",
"Secretaría de Seguridad y Protección Ciudadana 1.593196e+08 1.471805e+08 \n",
"\n",
" porcentaje_ejecutado \n",
"den \n",
"Secretaría de Infraestructura 54.143157 \n",
"Secretaría de Hacienda y Desarrollo Económico 70.337373 \n",
"Intendencia 81.549529 \n",
"H.C.D. 82.257173 \n",
"Secretaría de Salud 83.837016 \n",
"Secretaría de Políticas Sociales 84.088158 \n",
"Instituto Cultural 84.557445 \n",
"Secretaría de Modernización y Gobierno Abierto 88.991811 \n",
"Secretaría de Asesoría Letrada 89.080901 \n",
"Secretaría de Gestión Ambiental 91.597505 \n",
"Secretaría de Gobierno 92.923008 \n",
"Secretaría de Innov. Tecnológica Desar. Creativo 104.307260 \n",
"Secretaría de Seguridad y Protección Ciudadana 108.247747 "
]
},
"execution_count": 55,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"admin = df.groupby('den').sum()\n",
"admin['porcentaje_ejecutado'] = (admin['ejecutado'] / admin['presupuestado']) * 100\n",
"admin.sort_values('porcentaje_ejecutado')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Agrupamos por la clasificación por objeto del gasto, y agregamos una columna `porcentaje_ejecutado`, definida como la razón entre el ejecutado y el vigente."
]
},
{
"cell_type": "code",
"execution_count": 56,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>ejecutado</th>\n",
" <th>presupuestado</th>\n",
" <th>porcentaje_ejecutado</th>\n",
" </tr>\n",
" <tr>\n",
" <th>gasto</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>Bienes de uso</th>\n",
" <td>1.960297e+08</td>\n",
" <td>7.130901e+08</td>\n",
" <td>27.490176</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Servicio de la deuda y di</th>\n",
" <td>6.450522e+07</td>\n",
" <td>1.033388e+08</td>\n",
" <td>62.421100</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Bienes de consumo</th>\n",
" <td>8.801814e+07</td>\n",
" <td>1.312307e+08</td>\n",
" <td>67.071291</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Transferencias</th>\n",
" <td>2.475001e+08</td>\n",
" <td>3.133800e+08</td>\n",
" <td>78.977642</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Gastos figurativos</th>\n",
" <td>4.787162e+08</td>\n",
" <td>5.741258e+08</td>\n",
" <td>83.381764</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Activos financieros</th>\n",
" <td>1.759748e+07</td>\n",
" <td>2.078771e+07</td>\n",
" <td>84.653253</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Gastos en personal</th>\n",
" <td>1.051577e+09</td>\n",
" <td>1.193480e+09</td>\n",
" <td>88.110185</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Servicios no personales</th>\n",
" <td>8.033993e+08</td>\n",
" <td>8.998135e+08</td>\n",
" <td>89.285089</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" ejecutado presupuestado porcentaje_ejecutado\n",
"gasto \n",
"Bienes de uso 1.960297e+08 7.130901e+08 27.490176\n",
"Servicio de la deuda y di 6.450522e+07 1.033388e+08 62.421100\n",
"Bienes de consumo 8.801814e+07 1.312307e+08 67.071291\n",
"Transferencias 2.475001e+08 3.133800e+08 78.977642\n",
"Gastos figurativos 4.787162e+08 5.741258e+08 83.381764\n",
"Activos financieros 1.759748e+07 2.078771e+07 84.653253\n",
"Gastos en personal 1.051577e+09 1.193480e+09 88.110185\n",
"Servicios no personales 8.033993e+08 8.998135e+08 89.285089"
]
},
"execution_count": 56,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"objeto = df.groupby('gasto').sum()\n",
"objeto['porcentaje_ejecutado'] = (objeto['ejecutado'] / objeto['presupuestado']) * 100\n",
"objeto.sort_values('porcentaje_ejecutado')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Presupuesto de Ingresos\n",
"\n",
"Hacemos un análisis similar con el presupuesto de *ingresos*"
]
},
{
"cell_type": "code",
"execution_count": 76,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th>group</th>\n",
" <th>gasto</th>\n",
" <th>Calculado</th>\n",
" <th>Percibido</th>\n",
" <th>porcentaje_ejecutado</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>Origen Municipal</td>\n",
" <td>3.199096e+09</td>\n",
" <td>2.592313e+09</td>\n",
" <td>81.032685</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>Origen Nación</td>\n",
" <td>3.679187e+08</td>\n",
" <td>9.174897e+07</td>\n",
" <td>24.937291</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>Origen OTROS...</td>\n",
" <td>1.221781e+07</td>\n",
" <td>5.422722e+06</td>\n",
" <td>44.383747</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>Origen Provincia</td>\n",
" <td>3.716015e+08</td>\n",
" <td>2.076148e+08</td>\n",
" <td>55.870284</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
"group gasto Calculado Percibido porcentaje_ejecutado\n",
"0 Origen Municipal 3.199096e+09 2.592313e+09 81.032685\n",
"1 Origen Nación 3.679187e+08 9.174897e+07 24.937291\n",
"2 Origen OTROS... 1.221781e+07 5.422722e+06 44.383747\n",
"3 Origen Provincia 3.716015e+08 2.076148e+08 55.870284"
]
},
"execution_count": 76,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"resp = requests.get(\"http://www.bahia.gob.ar/presupuesto/data/gestorDatos.php?anio=2017&apertura=Percibido&a=1\")\n",
"# El sitio de MBB declara content-type y encoding erróneos, tenemos que forzar el encoding de la respuesta\n",
"resp.encoding = 'utf-8'\n",
"\n",
"\n",
"ingresos = pd.DataFrame(resp.json()).pivot(index='gasto', columns='group', values='valor').reset_index()\n",
"ingresos['porcentaje_ejecutado'] = (ingresos['Percibido'] / ingresos['Calculado']) * 100\n",
"ingresos"
]
},
{
"cell_type": "code",
"execution_count": 77,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"ingresos.to_csv('/tmp/bb_ingresos.csv', index=False)"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.6.1"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment