SOF_Execucao_Orcamentaria_SMESP.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Explorando as despesas da educação na cidade de São Paulo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Um tutorial de primeiros passos para acessar a execução orçamentária da Secretaria de Educação do município usando Python e a biblioteca de análise de dados Pandas* *"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Passo 1. Cadastro na API e token de acesso"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Acessar a Vitrine de APIs da Prodam:https://api.prodam.sp.gov.br/store/\n",
    "* Selecione a API do SOF\n",
    "* Clique em \"Inscrever-se\"\n",
    "* Acesse o menu \"Minhas assinaturas\"\n",
    "* Gere uma chave de acesso de produção; coloque um valor de validade negativo, para evitar que expire\n",
    "* Copie o Token de Acesso"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Passo 2. Teste na API Console"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A API Console (entre na aba com esse nome neste link [aqui](https://api.prodam.sp.gov.br/store/apis/info?name=SOF&version=v2.1.0&provider=admin)) é uma interface que permite testar as diferentes consultas e obter a URL com os parâmetros desejados. Por exemplo, se deseja obter todos os contratos da Secretaria de Educação em 2017, basta entrar no item **/consultaContrato** e informar \"2017\" no campo **anoContrato** e \"16\" (código da Educação) no campo **codOrgao**. A URL resultante dessa consulta e que você vai usar nos passos seguintes é https://gatewayapi.prodam.sp.gov.br:443/financas/orcamento/sof/v2.1.0/consultaContrato?anoContrato=2017&codOrgao=16  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Passo 3. Mãos ao Pandas!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Este é o script que consulta a API (para qualquer URL gerada acima) e transforma o arquivo obtido em formato json para um Data Frame do Pandas, a partir do qual será possível fazer as análises. Substitua a constante TOKEN pelo seu código de assinatura!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "import json\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "TOKEN = '198f959a5f39a1c441c7c863423264'\n",
    "\n",
    "base_url = \"https://gatewayapi.prodam.sp.gov.br:443/financas/orcamento/sof/v2.1.0\"\n",
    "\n",
    "headers={'Authorization' : str('Bearer ' + TOKEN)}\n",
    "\n",
    "anos = [2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018]\n",
    "\n",
    "#Anos desejados na consulta; é possível consultar informações a partir de 2003"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Orçamento \n",
    "\n",
    "Primeiro, vamos ter uma visão geral do que foi orçado para a Secretaria Municipal de Educação desde 2011 até o ano corrente, bem como os valores congelados e já executados. Isso é possível com a consulta \"Despesas\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_lista = []\n",
    "a = 0 \n",
    "\n",
    "for ano in anos:\n",
    "    \"\"\"consulta todos os anos da lista acima\"\"\"\n",
    "    \n",
    "    url_orcado = '{base_url}/consultarDespesas?anoDotacao={ano}&mesDotacao=12&codOrgao=16'.format(base_url=base_url, ano=ano)\n",
    "    request_orcado = requests.get(url_orcado,\n",
    "                                headers=headers,\n",
    "                                verify=True).json()\n",
    "    \n",
    "    df_orcado = pd.DataFrame(request_orcado['lstDespesas'])\n",
    "    df_lista.append(df_orcado)\n",
    "    df_lista[a].loc[:,'anoExercicio'] = str(ano)\n",
    "    #adiciona uma coluna com o ano de exercício, já que esta informação não vem na API\n",
    "    a += 1\n",
    "\n",
    "df_total = pd.concat(df_lista, axis=0, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>valAnuladoEmpenho</th>\n",
       "      <th>valCanceladoReserva</th>\n",
       "      <th>valCongelado</th>\n",
       "      <th>valDescongelado</th>\n",
       "      <th>valDisponivel</th>\n",
       "      <th>valEmpenhadoLiquido</th>\n",
       "      <th>valLiquidado</th>\n",
       "      <th>valOrcadoAtualizado</th>\n",
       "      <th>valOrcadoInicial</th>\n",
       "      <th>valPagoExercicio</th>\n",
       "      <th>valPagoRestos</th>\n",
       "      <th>valReduzido</th>\n",
       "      <th>valReservado</th>\n",
       "      <th>valReservadoLiquido</th>\n",
       "      <th>valSuplementado</th>\n",
       "      <th>valTotalEmpenhado</th>\n",
       "      <th>anoExercicio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4.414630e+08</td>\n",
       "      <td>1.247965e+09</td>\n",
       "      <td>1.937554e+09</td>\n",
       "      <td>1.715061e+09</td>\n",
       "      <td>6.984083e+09</td>\n",
       "      <td>6.826925e+09</td>\n",
       "      <td>6.210634e+09</td>\n",
       "      <td>7.206576e+09</td>\n",
       "      <td>6978389021</td>\n",
       "      <td>6.169191e+09</td>\n",
       "      <td>5.588442e+08</td>\n",
       "      <td>1.220404e+09</td>\n",
       "      <td>8.074884e+09</td>\n",
       "      <td>6.826919e+09</td>\n",
       "      <td>1.448590e+09</td>\n",
       "      <td>7.268388e+09</td>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.916890e+08</td>\n",
       "      <td>1.256692e+09</td>\n",
       "      <td>1.850602e+09</td>\n",
       "      <td>1.694680e+09</td>\n",
       "      <td>7.520123e+09</td>\n",
       "      <td>7.406758e+09</td>\n",
       "      <td>6.997171e+09</td>\n",
       "      <td>7.676046e+09</td>\n",
       "      <td>7307184447</td>\n",
       "      <td>6.948630e+09</td>\n",
       "      <td>3.427918e+08</td>\n",
       "      <td>9.094071e+08</td>\n",
       "      <td>8.663449e+09</td>\n",
       "      <td>7.406758e+09</td>\n",
       "      <td>1.278269e+09</td>\n",
       "      <td>7.798447e+09</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7.262321e+08</td>\n",
       "      <td>1.310143e+09</td>\n",
       "      <td>1.066800e+07</td>\n",
       "      <td>1.423000e+06</td>\n",
       "      <td>8.280234e+09</td>\n",
       "      <td>7.939771e+09</td>\n",
       "      <td>7.595840e+09</td>\n",
       "      <td>8.289479e+09</td>\n",
       "      <td>7914456075</td>\n",
       "      <td>7.568847e+09</td>\n",
       "      <td>2.841143e+08</td>\n",
       "      <td>5.991833e+08</td>\n",
       "      <td>9.249913e+09</td>\n",
       "      <td>7.939771e+09</td>\n",
       "      <td>9.742067e+08</td>\n",
       "      <td>8.666003e+09</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.201673e+08</td>\n",
       "      <td>1.252968e+09</td>\n",
       "      <td>1.244632e+09</td>\n",
       "      <td>8.673022e+08</td>\n",
       "      <td>8.983922e+09</td>\n",
       "      <td>8.799911e+09</td>\n",
       "      <td>8.417624e+09</td>\n",
       "      <td>9.361252e+09</td>\n",
       "      <td>9022636700</td>\n",
       "      <td>8.399880e+09</td>\n",
       "      <td>2.467169e+08</td>\n",
       "      <td>1.158271e+09</td>\n",
       "      <td>1.005287e+10</td>\n",
       "      <td>8.799905e+09</td>\n",
       "      <td>1.496886e+09</td>\n",
       "      <td>9.220079e+09</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6.408370e+08</td>\n",
       "      <td>1.428246e+09</td>\n",
       "      <td>1.114940e+09</td>\n",
       "      <td>9.182910e+08</td>\n",
       "      <td>9.802778e+09</td>\n",
       "      <td>9.709032e+09</td>\n",
       "      <td>9.207102e+09</td>\n",
       "      <td>9.999427e+09</td>\n",
       "      <td>9792348985</td>\n",
       "      <td>9.178274e+09</td>\n",
       "      <td>3.829664e+08</td>\n",
       "      <td>1.240442e+09</td>\n",
       "      <td>1.113728e+10</td>\n",
       "      <td>9.709032e+09</td>\n",
       "      <td>1.447520e+09</td>\n",
       "      <td>1.034987e+10</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7.566030e+08</td>\n",
       "      <td>1.246995e+09</td>\n",
       "      <td>1.745198e+09</td>\n",
       "      <td>1.051613e+09</td>\n",
       "      <td>1.045215e+10</td>\n",
       "      <td>1.024287e+10</td>\n",
       "      <td>9.890627e+09</td>\n",
       "      <td>1.114573e+10</td>\n",
       "      <td>11096151037</td>\n",
       "      <td>9.865022e+09</td>\n",
       "      <td>3.022515e+08</td>\n",
       "      <td>5.583677e+08</td>\n",
       "      <td>1.148986e+10</td>\n",
       "      <td>1.024287e+10</td>\n",
       "      <td>6.079500e+08</td>\n",
       "      <td>1.099947e+10</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2.272048e+09</td>\n",
       "      <td>3.460240e+09</td>\n",
       "      <td>3.720131e+09</td>\n",
       "      <td>3.558954e+09</td>\n",
       "      <td>1.104829e+10</td>\n",
       "      <td>1.069421e+10</td>\n",
       "      <td>1.023613e+10</td>\n",
       "      <td>1.120947e+10</td>\n",
       "      <td>10985422304</td>\n",
       "      <td>1.019775e+10</td>\n",
       "      <td>2.811021e+08</td>\n",
       "      <td>8.743850e+08</td>\n",
       "      <td>1.415445e+10</td>\n",
       "      <td>1.069421e+10</td>\n",
       "      <td>1.098430e+09</td>\n",
       "      <td>1.296626e+10</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3.921755e+08</td>\n",
       "      <td>6.206129e+08</td>\n",
       "      <td>1.383712e+09</td>\n",
       "      <td>2.125405e+08</td>\n",
       "      <td>1.062097e+10</td>\n",
       "      <td>3.779975e+09</td>\n",
       "      <td>2.206465e+09</td>\n",
       "      <td>1.179214e+10</td>\n",
       "      <td>11774780638</td>\n",
       "      <td>2.129934e+09</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>5.848946e+07</td>\n",
       "      <td>5.012304e+09</td>\n",
       "      <td>4.391691e+09</td>\n",
       "      <td>7.584808e+07</td>\n",
       "      <td>4.172151e+09</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   valAnuladoEmpenho  valCanceladoReserva  valCongelado  valDescongelado  \\\n",
       "0       4.414630e+08         1.247965e+09  1.937554e+09     1.715061e+09   \n",
       "1       3.916890e+08         1.256692e+09  1.850602e+09     1.694680e+09   \n",
       "2       7.262321e+08         1.310143e+09  1.066800e+07     1.423000e+06   \n",
       "3       4.201673e+08         1.252968e+09  1.244632e+09     8.673022e+08   \n",
       "4       6.408370e+08         1.428246e+09  1.114940e+09     9.182910e+08   \n",
       "5       7.566030e+08         1.246995e+09  1.745198e+09     1.051613e+09   \n",
       "6       2.272048e+09         3.460240e+09  3.720131e+09     3.558954e+09   \n",
       "7       3.921755e+08         6.206129e+08  1.383712e+09     2.125405e+08   \n",
       "\n",
       "   valDisponivel  valEmpenhadoLiquido  valLiquidado  valOrcadoAtualizado  \\\n",
       "0   6.984083e+09         6.826925e+09  6.210634e+09         7.206576e+09   \n",
       "1   7.520123e+09         7.406758e+09  6.997171e+09         7.676046e+09   \n",
       "2   8.280234e+09         7.939771e+09  7.595840e+09         8.289479e+09   \n",
       "3   8.983922e+09         8.799911e+09  8.417624e+09         9.361252e+09   \n",
       "4   9.802778e+09         9.709032e+09  9.207102e+09         9.999427e+09   \n",
       "5   1.045215e+10         1.024287e+10  9.890627e+09         1.114573e+10   \n",
       "6   1.104829e+10         1.069421e+10  1.023613e+10         1.120947e+10   \n",
       "7   1.062097e+10         3.779975e+09  2.206465e+09         1.179214e+10   \n",
       "\n",
       "   valOrcadoInicial  valPagoExercicio  valPagoRestos   valReduzido  \\\n",
       "0        6978389021      6.169191e+09   5.588442e+08  1.220404e+09   \n",
       "1        7307184447      6.948630e+09   3.427918e+08  9.094071e+08   \n",
       "2        7914456075      7.568847e+09   2.841143e+08  5.991833e+08   \n",
       "3        9022636700      8.399880e+09   2.467169e+08  1.158271e+09   \n",
       "4        9792348985      9.178274e+09   3.829664e+08  1.240442e+09   \n",
       "5       11096151037      9.865022e+09   3.022515e+08  5.583677e+08   \n",
       "6       10985422304      1.019775e+10   2.811021e+08  8.743850e+08   \n",
       "7       11774780638      2.129934e+09   0.000000e+00  5.848946e+07   \n",
       "\n",
       "   valReservado  valReservadoLiquido  valSuplementado  valTotalEmpenhado  \\\n",
       "0  8.074884e+09         6.826919e+09     1.448590e+09       7.268388e+09   \n",
       "1  8.663449e+09         7.406758e+09     1.278269e+09       7.798447e+09   \n",
       "2  9.249913e+09         7.939771e+09     9.742067e+08       8.666003e+09   \n",
       "3  1.005287e+10         8.799905e+09     1.496886e+09       9.220079e+09   \n",
       "4  1.113728e+10         9.709032e+09     1.447520e+09       1.034987e+10   \n",
       "5  1.148986e+10         1.024287e+10     6.079500e+08       1.099947e+10   \n",
       "6  1.415445e+10         1.069421e+10     1.098430e+09       1.296626e+10   \n",
       "7  5.012304e+09         4.391691e+09     7.584808e+07       4.172151e+09   \n",
       "\n",
       "  anoExercicio  \n",
       "0         2011  \n",
       "1         2012  \n",
       "2         2013  \n",
       "3         2014  \n",
       "4         2015  \n",
       "5         2016  \n",
       "6         2017  \n",
       "7         2018  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_total"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Uma visão dos valores orçados (valor atualizado no início do ano, após projeção mais adequada das receitas) e o liquidado, nos últimos meses do ano:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "series = df_total[['anoExercicio', 'valOrcadoAtualizado','valLiquidado']].set_index('anoExercicio')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "series = series[['valOrcadoAtualizado', 'valLiquidado']].divide(1000000000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAA34AAAHRCAYAAAA1/GsGAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMS4wLCBo\ndHRwOi8vbWF0cGxvdGxpYi5vcmcvpW3flQAAIABJREFUeJzt3Xu8nfOZ///XVQkRiQSJjmO2tgiy\nCQ1K1aFUD0oHaRtVhygmVW2//eq0ptOa0Gl/OmNqqk5VRSlBU5ofejBGFKUdiTqlyFBBHOMUSZNd\nxPX9Y629rexk7+zIOtz7zuv5eKxH1n3fa933tdaV03t/Pvd9R2YiSZIkSSqvd7S6AEmSJElSYxn8\nJEmSJKnkDH6SJEmSVHIGP0mSJEkqOYOfJEmSJJWcwU+SJEmSSs7gJ0kqpYjYOyLmtvD4m0fEwohY\now77+kZEXNjL9jkRsd/b3PctEXHs269OktQfGPwkSQ0TEUdHxP0RsSgino2I8yJieKvrqqeeglNm\nPpGZQzJzyaoeIzO/m5mGM0nS22bwkyQ1REScBHwP+EdgGPA+YBTwXxGxZg/vGdC8CiVJWn0Y/CRJ\ndRcR6wKnAl/MzN9k5uuZOQf4FJXw99nq6yZHxNSI+FlEvAocHRFrVKc2PhoRCyJiZkRsVn39DyLi\nyYh4tbr+AzXHXDsiLomIlyPiz8DO3Wrapjo690pEzIqIg3qoff2ImBsRB1aXh0TEIxFx5Ep+B20R\nkZ1hNiK2iIjfVT/Tf0XE2RHxs+q2Zaal1k7frH5PP6vZdkREPB4RL0bEP3d73y4RcWf1cz5TPc6a\nNds/FBEPRcT8iDgbiJpt74iIb1b3/XxEXBoRw1bmc0uSisngJ0lqhN2BQcA1tSszcyHwa+BDNas/\nAUwFhgOXA/8XOAz4GLAucAywqPrau4CxwPrAFcDPI2JQddu/AO+uPj4MHNV5gIgYCFwH3AhsCHwR\nuDwitu5eeGa+VD3mjyNiQ+BM4J7MvPRtfA+1rgBmAiOAb9fWtzIiYlvgPOAIYGNgA2DTmpcsAb5S\nPc5uwL7ACdX3jgB+AXyzuv1R4P017z26+tgHeBcwBDj77dQpSSoWg58kqRFGAC9k5hvL2fZMdXun\nOzPzl5n5ZmYuBo4FvpmZD2fFvZn5IkBm/iwzX8zMNzLzP4C1gM7w9ingO5n5UmY+CZxVc4z3UQkx\np2fma5l5M3A9lYC5jMy8Efg58N/AAcA/vL2voSIiNqcyAvmtzPxbZt5KJYi+HeOB6zPz1sz8G/At\n4M2a2mdm5h+q39Ec4EfAXtXNHwP+nJlTM/N14D+BZ2v2fTjw/cz8SzWk/xMwwSm4ktT/GfwkSY3w\nAjCih8CwUXV7pye7bd+MykjUMiLipIh4sDpN8RUq5w52hsiNu+3r8ZrnGwNPZuab3bZv0stnuAAY\nA1zcGTxXwcbAy5n51x7qW9l9dX3O6j676ouIrSLi+urFdF4FvksP31FmJkt/Zxt3q+txYADwzrdZ\nqySpIAx+kqRGuBP4G3BI7cqIWAf4KJWRtE7Z7b1PUpmuuZTq+XxfpzKyt15mDgfm89Y5as9QCY2d\nNq95/jSwWUS8o9v2p5ZXfPUWDD8CLgU+HxHvWd7rVsIzwHrVz7+8+v4KDO52/JG97GuzmtcOpjLd\ns9N5wEPAlpm5LvANeviOIiJY+jt7mso5mLU1vgE818tnkyT1AwY/SVLdZeZ8Khd3+WFEfCQiBkZE\nG5Xpk3OBy3p5+4XAtyNiy6jYPiI2AIZSCSHzgAERcQqVcwA7XQ38U0SsFxGbUjmPr9MfqYSrr1Vr\n2Rs4ELiyhxq+Uf31GOAM4NLo/X58AyJiUM1jYLfv43FgBnBqRKwZEXtUj99pNjAoIg6ovvebVKax\nLs9U4OMRsUf1oi2nsfS/50OBV4GFETEa+HzNthuA7SLikOpo7JeAv6vZPgX4SvVCNEOojBZe1cOU\nXUlSP2LwkyQ1RGb+G5UAdQaVIPJHKqN5+1bPTevJ96mEuBupnLv2E2Bt4LdULgwzm8oUxA6WnqZ4\nanX9Y9X3doXLzHwNOIjKaOMLwLnAkZn5UPeDR8R7qVxg5sjqPfi+R2VU8uReaj4PWFzzuHg5r/kM\nsCvwEpUL0XRdLKYalE+gEnqfohJSl3vz+cycBXyBysVingFe7vbar1aPtQD4MXBVzXtfAD4JnE5l\neuiWwO9r3nsRle/tVirfYwdLB2hJUj8Vlen9kiQVT0RcCxyTmS+3upZ6i4jJwHsy87OtrkWSVH6O\n+EmSCqc6HXMt4BXgva2uR5Kk/s7gJ0kqovWB54E9gPtaXIskSf2eUz0lSZIkqeQc8ZMkSZKkkjP4\nSZIkSVLJDWh1AW/XiBEjsq2trdVlSJIkSVJLzJw584XMHNmX1/bb4NfW1saMGTNaXYYkSZIktURE\nPN7X1zrVU5IkSZJKzuAnSZIkSSVn8JMkSZKkkuu35/gtz+uvv87cuXPp6OhodSkquEGDBrHpppsy\ncODAVpciSZIkNVypgt/cuXMZOnQobW1tRESry1FBZSYvvvgic+fOZYsttmh1OZIkSVLDlWqqZ0dH\nBxtssIGhT72KCDbYYANHhiVJkrTaaFrwi4gTI2JGRPwtIi6pWf++iPiviHgpIuZFxM8jYqNVOE5d\n6lW5+ftEkiRJq5Nmjvg9DfwrcFG39esBFwBtwChgAXBxE+tqqSFDhnQ9nzVrFh/84AfZaqut2HLL\nLfn2t79NZtb9mHPmzGHMmDF9eu2Xv/xlNtlkE958882udbfccgt33HHHKtXQ+bmffvppxo8fv0r7\nApg8eTJnnHHGKu9HkiRJKqOmneOXmdcARMQ4YNOa9b+ufV1EnA38rh7HbDv5hnrspsuc0w+o6/5q\nLV68mIMOOojzzjuP/fffn0WLFnHooYdy7rnn8oUvfGGp177xxhsMGND41r355ptce+21bLbZZtx6\n663svffeQCX4DRkyhN13332Vj7HxxhszderUVd6PJEmSpJ4V8Ry/PYFZrS7i7fr617/Oueee27U8\nefJkTj31VPbdd1922mkn2tvbmTZt2jLvu+KKK3j/+9/P/vvvD8DgwYM5++yzOf3007v2c/zxx7P/\n/vtz5JFHsmTJEr761a/S3t7O9ttvzw9/+EMATjvtNHbeeWfGjBnD8ccf3zViOHPmTHbYYQd22203\nzjnnnK7jdnR0MHHiRNrb29lxxx2ZPn1617bp06czZswYPv/5zzNlyhSgMlp4/vnnc+aZZzJ27Fhu\nu+02jj766KXCW+do3sKFC1f4uWtHH4899ljGjh3L2LFjGTlyJKeeemqv+/jOd77D1ltvzX777cfD\nDz/ctf6ee+7hfe97H9tvvz0HH3wwL7/8cp96J0mSJJVVoYJfRGwPnAL8Yw/bj6+eJzhj3rx5zS2u\njyZMmMBVV13VtXz11VczceJErr32Wu6++26mT5/OSSedtMwUzlmzZvHe9753qXXvfve7WbhwIa++\n+ipQCW/Tpk3jiiuu4IILLuCxxx7jT3/6E/fddx+HH344ACeeeCJ33XUXDzzwAIsXL+b6668HYOLE\niZx11lnceeedSx2jMwTef//9TJkyhaOOOqrroidTpkzhsMMO4+CDD+b666/n9ddfp62tjUmTJvGV\nr3yFe+65hw984AM9fheDBg1a4eeudeGFF3LPPfcwbdo0NthgA44++uge9zFz5kyuvPJK/vSnP3HN\nNddw1113de3nyCOP5Hvf+x733Xcf7e3tnHrqqT0eU5IkSVodFCb4RcR7gF8DX87M25b3msy8IDPH\nZea4kSNHNrfAPtpxxx15/vnnefrpp7n33ntZb7312GijjfjGN77B9ttvz3777cdTTz3Fc889t9T7\nMrPHC450rj/ooINYe+21AbjpppuYNGlS15TP9ddfH6iM0u266660t7dz8803M2vWLObPn88rr7zC\nXnvtBcARRxzRte/bb7+9a3n06NGMGjWK2bNn89prr/GrX/2Kv//7v2fddddl11135cYbb1yp7yIz\nV/i5u+vo6OCTn/wkZ599NqNGjepxH7fddhsHH3wwgwcPZt111+Wggw4CWOazHnXUUdx6660rVbck\nSZJUNoW4j19EjAJuAr6dmZe1up5VNX78eKZOncqzzz7LhAkTuPzyy5k3bx4zZ85k4MCBtLW1LXMr\nge22226ZgPKXv/yFIUOGMHToUADWWWedrm3LC4odHR2ccMIJzJgxg80224zJkyfT0dHRa6jsaQTu\nN7/5DfPnz6e9vR2ARYsWMXjwYA44YNnzHAcMGNB18ZfM5LXXXgPo0+fubtKkSRxyyCHst99+K9yH\nV+aUJEmS+qaZt3MYEBGDgDWANSJiUHXdJsDNwDmZeX6z6mmkCRMmcOWVVzJ16lTGjx/P/Pnz2XDD\nDRk4cCDTp0/n8ccfX+Y9hx9+OLfffjs33XQTULnYy5e+9CW+9rWvLfcY+++/P+effz5vvPEGAC+9\n9FJXIBoxYgQLFy7sOu9u+PDhDBs2jNtvvx2ohKlOe+65Z9fy7NmzeeKJJ9h6662ZMmUKF154IXPm\nzGHOnDk89thj3HjjjSxatIihQ4eyYMGCrn20tbUxc+ZMAKZNm8brr78O0KfPXeucc85hwYIFnHzy\nyV3retrHnnvuybXXXsvixYtZsGAB1113HQDDhg1jvfXW47bbKoPGl112WdfonyRJkrS6auaI3zeB\nf6lZ/ixwKpDAu4B/iYiu7Zk5hH5qu+22Y8GCBWyyySZstNFGHH744Rx44IGMGzeOsWPHMnr06GXe\ns/baazNt2jS++MUv8oUvfIElS5ZwxBFHcOKJJy73GMceeyyzZ89m++23Z+DAgRx33HGceOKJHHfc\ncbS3t9PW1sbOO+/c9fqLL76YY445hsGDB/PhD3+4a/0JJ5zApEmTaG9vZ8CAAVxyySUsWbKE3/72\nt/zoRz/qet0666zDHnvswXXXXceBBx7I+PHjmTZtGj/84Q857rjj+MQnPsEuu+zCvvvu2zUy2ZfP\nXeuMM85g4MCBjB07FqiM/vW0j5122olPf/rTjB07llGjRi11ruFPf/pTJk2axKJFi3jXu97FxRev\nNncHkSRJkpYrGnGfuGYYN25czpgxY6l1Dz74INtss02LKlJ/4+8XSZIk9WcRMTMzx/XltYU4x0+S\nJEnS6qve99/uSSPvy110hbmqpyRJkiSpMQx+kiRJklRyBj9JkiRJKjmDnyRJkiSVnMFPkiRJkkrO\n4NdiQ4ZUblc4Z84cxowZs8z2U045peum7m/H7rvvvtz1Rx99dNcN3vuip/okSZIkFV+5b+cweVid\n9ze/vvvrg9NOO22V3n/HHXfUqRJJkiRJ/ZUjfnX29a9/nXPPPbdrefLkyZx66qnsu+++7LTTTrS3\ntzNt2rQ+7692ZO43v/kNo0ePZo899uBLX/oSH//4x7uOccYZZ3S9Z8yYMcyZMwd4a0QxMznxxBPZ\ndtttOeCAA3j++ee7Xn/aaaex8847M2bMGI4//ngyE4CZM2eyww47sNtuu3HOOed0vb6jo4OJEyfS\n3t7OjjvuyPTp01fyW5IkSZLUTAa/OpswYQJXXXVV1/LVV1/NxIkTufbaa7n77ruZPn06J510Ule4\n6quOjg6OO+44rrvuOm677TaeffbZlXr/tddey8MPP8z999/Pj3/846VGAk888UTuuusuHnjgARYv\nXsz1118PwMSJEznrrLO48847l9pXZwi8//77mTJlCkcddRQdHR0rVY8kSZKk5jH41dmOO+7I888/\nz9NPP829997Leuutx0YbbcQ3vvENtt9+e/bbbz+eeuopnnvuuZXa70MPPcQWW2zBlltuSUTw2c9+\ndqXef+utt3LYYYexxhprsPHGG/PBD36wa9v06dPZddddaW9v5+abb2bWrFnMnz+fV155hb322guA\nI444ouv1t99+e9fy6NGjGTVqFLNnz16peiRJkiQ1T7nP8WuR8ePHM3XqVJ599lkmTJjA5Zdfzrx5\n85g5cyYDBw6kra3tbY2QRcRy1w8YMIA333yza7mnfS/v/R0dHZxwwgnMmDGDzTbbjMmTJ9PR0UFm\n9ni8lR2tlCRJktRajvg1wIQJE7jyyiuZOnUq48ePZ/78+Wy44YYMHDiQ6dOn8/jjj6/0PkePHs1j\njz3Go48+CsCUKVO6trW1tXH33XcDcPfdd/PYY48t8/4999yTK6+8kiVLlvDMM890nZfXGRJHjBjB\nwoULu84nHD58OMOGDeP2228H4PLLL19qX53Ls2fP5oknnmDrrbde6c8kSZIkqTkc8WuA7bbbjgUL\nFrDJJpuw0UYbcfjhh3PggQcybtw4xo4dy+jRo5f7vocffphNN920a/nMM8/sej5o0CAuuOACDjjg\nAEaMGMEee+zBAw88AMChhx7KpZdeytixY9l5553Zaqutltn3wQcfzM0330x7eztbbbVV1xTO4cOH\nc9xxx9He3k5bWxs777xz13suvvhijjnmGAYPHsyHP/zhrvUnnHACkyZNor29nQEDBnDJJZew1lpr\nrdqXJkmSJKlhor9O2xs3blzOmDFjqXUPPvgg22yzTYsqaq5bbrmFM844o+tCLFp5q9PvF0mSpCJr\nO/mGphxnzukHNOU4zRIRMzNzXF9e61RPSZIkSSo5p3r2U3vvvTd77713q8uQJEmS1A844idJkiRJ\nJVe6Eb/ebkMgdeqv57ZKkqSV16zzx6B855CpPEo14jdo0CBefPFF/1OvXmUmL774IoMGDWp1KZIk\nSVJTlGrEb9NNN2Xu3LnMmzev1aWo4AYNGrTUrTMkSVoVXpFQUtGVKvgNHDiQLbbYotVlSJIkSVKh\nlGqqpyRJkiRpWQY/SZIkSSo5g58kSZIklZzBT5IkSZJKzuAnSZIkSSVn8JMkSZKkkjP4SZIkSVLJ\nGfwkSZIkqeQMfpIkSZJUcgY/SZIkSSo5g58kSZIklZzBT5IkSZJKzuAnSZIkSSVn8JMkSZKkkjP4\nSZIkSVLJGfwkSZIkqeQMfpIkSZJUcgY/SZIkSSo5g58kSZIklZzBT5IkSZJKzuAnSZIkSSVn8JMk\nSZKkkjP4SZIkSVLJGfwkSZIkqeQMfpIkSZJUcgY/SZIkSSq5Aa0uQJIkSSqNycOadJz5zTmOSsMR\nP0mSJEkqOYOfJEmSJJVc04JfRJwYETMi4m8RcUm3bftGxEMRsSgipkfEqGbVJUmSJEll18wRv6eB\nfwUuql0ZESOAa4BvAesDM4CrmliXJEmSJJVa0y7ukpnXAETEOGDTmk2HALMy8+fV7ZOBFyJidGY+\n1Kz6JEmSJKmsinCO33bAvZ0LmflX4NHqekmSJEnSKirC7RyGAPO6rZsPDO3+wog4HjgeYPPNN298\nZZIkFUDbyTc05ThzTj+gKceRJDVfEUb8FgLrdlu3LrCg+wsz84LMHJeZ40aOHNmU4iRJkiSpvytC\n8JsF7NC5EBHrAO+urpckSZIkraKmTfWMiAHV460BrBERg4A3gGuBf4+IQ4EbgFOA+7ywiyRJUjeT\nhzXpOPObcxxJTdPMEb9vAouBk4HPVp9/MzPnAYcC3wFeBnYFJjSxLkmSJEkqtWbezmEyMLmHbTcB\no5tViyRJkiStTopwjp8kSZIkqYEMfpIkSZJUcgY/SZIkSSo5g58kSZIklZzBT5IkSZJKzuAnSZIk\nSSVn8JMkSZKkkjP4SZIkSVLJGfwkSZIkqeQMfpIkSZJUcgY/SZIkSSo5g58kSZIklZzBT5IkSZJK\nzuAnSZIkSSU3oNUFSJJaq+3kG5p2rDmnH9C0Y0mSpLc44idJkiRJJWfwkyRJkqSSM/hJkiRJUsl5\njp8kSaqYPKxJx5nfnONIkro44idJkiRJJWfwkyRJkqSSM/hJkiRJUskZ/CRJkiSp5Ax+kiRJklRy\nBj9JkiRJKjmDnyRJkiSVnMFPkiRJkkrO4CdJkiRJJWfwkyRJkqSSM/hJkiRJUskZ/CRJkiSp5Ax+\nkiRJklRyBj9JkiRJKjmDnyRJkiSVnMFPkiRJkkrO4CdJkiRJJWfwkyRJkqSSM/hJkiRJUskZ/CRJ\nkiSp5Ax+kiRJklRyBj9JkiRJKjmDnyRJkiSVnMFPkiRJkkpuQKsLkCStRiYPa9Jx5jfnOJIk9ROO\n+EmSJElSyRn8JEmSJKnkDH6SJEmSVHKe4yep4dpOvqEpx5lz+gFNOY4kSVJ/44ifJEmSJJWcwU+S\nJEmSSs7gJ0mSJEklZ/CTJEmSpJIrTPCLiLaI+FVEvBwRz0bE2RHhxWckSZIkaRUVJvgB5wLPAxsB\nY4G9gBNaWpEkSZIklUCRgt8WwNWZ2ZGZzwK/AbZrcU2SJEmS1O8VKfj9AJgQEYMjYhPgo1TCX5eI\nOD4iZkTEjHnz5rWkSEmSJEnqb4oU/H5HZYTvVWAuMAP4Ze0LMvOCzByXmeNGjhzZghIlSZIkqf8p\nRPCLiHcAvwWuAdYBRgDrAd9rZV2SJEmSVAaFCH7A+sBmwNmZ+bfMfBG4GPhYa8uSJEmSpP6vEMEv\nM18AHgM+HxEDImI4cBRwb2srkyRJkqT+rxDBr+oQ4CPAPOAR4A3gKy2tSJIkSZJKYIU3SI+IUcCi\nzJxXXT4QOA54DvhW9dYLqywz7wH2rse+JEmSJElv6cuI38+p3GOvMwT+DLgDWBO4pGGVSZIkSZLq\noscRv4jYEwhgK2BodflA4G4qwe9/gKur68nMWxtfriRJkiRpZfU21XOL6q8BtFE5524f4I/V5QDe\nrHlu8JMkSZKkAuox+GXmTwEi4khgDHAXlTB4UGY+HRFDqJzjd2lTKpV60HbyDU05zpzTD2jKcSRJ\nkqR668s5fp8HtgG+CvyfzHy6uv4Q4KpGFSZJkiRJqo8VXtUzM2dTuc1C9/WO9EmSJElSP7DC4NeT\niNgQ+MfM/Mc61iNJb9/kYU06zvzmHEeSJKlOep3qGRWfi4gfRsSXI2JgRAyPiDOBx4Btm1OmJEmS\nJOntWtGI3xnAp4HfA4cCuwC7An8Ads3MBxpbniRJkiRpVa0o+H0K2DMz/xIRo4E/A5/KzKmNL02S\nJEmSVA8ruqrnsMz8C0BmPgQsMvRJkiRJUv+yohG/iIgtqNygHeCNbst0BkNJkiRJUjGtKPitAzxC\nTdADHq15nsAa9S5KkiRJklQ/vQa/zOzLDd4lSZIkSQVmsJMkSZKkkjP4SZIkSVLJGfwkSZIkqeQM\nfpIkSZJUciu6qmeXiBgIvA/YODOvioh1ADLzr40qrijaTr6hKceZc/oBTTmOJEmSpNVLn0b8IqId\nmA38GPhJdfVewEUNqkuSJEmSVCd9nep5HnBKZo4GXq+u+x2wR0OqkiRJkiTVTV+D33bAz6rPE7qm\neK7diKIkSZIkSfXT1+A3B3hv7YqI2AV4pN4FSZIkSZLqq68Xd/kWcENEnA+sGRH/BEwCjmtYZZIk\nSZKkuuhT8MvM6yPio8CxVM7tGwUckpkzG1mcVCiThzXxWPObdyxJkiSVXp9v55CZdwMnNLAWSZIk\nSVID9Bj8IuK0vuwgM0+pXzmSJEmSpHrrbcRvs5rng4BDgbuAx4HNgV2AXzSuNEmSJElSPfQY/DJz\nYufziLgSOCwzf1Gz7hDgk40tT5IkSZK0qvp6O4ePAr/stm4a8LH6liNJkiRJqre+Br9HgC90W3cC\n8Gh9y5EkSZIk1Vtfr+p5LHBtRHwNeArYBHgDOKRRhUmSJEmS6qOv9/H7U0RsCbwP2Bh4BrgzM19v\nZHGSJEmSpFW3Mvfxex24rYG1SJIkSZIaoK/n+EmSJEmS+imDnyRJkiSVnMFPkiRJkkquT8EvIkZG\nxJDq8zUiYmJEHBkRBkdJkiRJKri+BrfrgS2rz78DfBX4v8B/NKIoSZIkSVL99PWqnlsB91SffxbY\nHVgIzAK+0oC6JEmSJEl10tfgtwRYMyK2AuZn5hPVaZ5DGleaJEmSJKke+hr8fg1cDWwAXFldty3w\nVCOKkiRJkiTVT1+D37HAUcDrwGXVdSOAyQ2oSZIkSZJUR30Kfpn5N+CC6vTOdwLPZOYtjSxMkiRJ\nklQffb2dw/CIuALoAB6prjsoIv61kcVJkiRJklZdX2/ncD4wHxgFvFZddyfw6UYUJUmSJEmqn76e\n47cvsHFmvh4RCZCZ8yJiw8aVJkmSJEmqh76O+M2ncjGXLhGxOfBM3SuSJEmSJNVVX0f8LgR+ERH/\nDLwjInYDvktlCqjqZfKwJh1nfnOOI0mSJKkQ+hr8vkflwi7nAAOBi4AfAT9oUF2SJEmSpDrp6+0c\nEvjP6kOSJEmS1I/09Rw/ImKfiLgoIn5b/fWD9S4mIiZExIMR8deIeDQiPlDvY0iSJEnS6qbH4BcR\nP42IfarPjwWuAp4FrqFyUZcrIuK4ehUSER+iMqV0IjAU2BP4S732L0mSJEmrq96mep4GXAnsDHwN\n+FBm3tu5MSKuAn4B/LhOtZwKnJaZf6guP1Wn/UqSJEnSaq23qZ7nAZOrzzcA/txt+8PA+vUoIiLW\nAMYBIyPikYiYGxFnR8Ta9di/JEmSJK3Oegt+6wAfqj7/PfD9iBgMEBHrAP8O3FGnOt5J5Wqh44EP\nAGOBHYFv1r4oIo6PiBkRMWPevHl1OrQkSZIklVuPwS8z3w+cUV38B2B7YH5EPAe8AuxQXV8Pi6u/\n/jAzn8nMF4DvAx/rVtMFmTkuM8eNHDmyToeWJEmSpHLr9XYOmTm3+uszwF4RsSmwMfB057Z6yMyX\nI2IukPXapyRJkiSpos+3c6h6DXgBWDMi3hUR76pjLRcDX4yIDSNiPeD/ANfXcf+SJEmStFrq0w3c\nI+IjwE+AjbptSmCNOtXybWAEMBvoAK4GvlOnfUuSJEnSaquvI37nUAlm62TmO2oe9Qp9ZObrmXlC\nZg7PzL/LzC9lZke99i9JkiRJq6s+jfgB6wE/ykzPwZMkSZKkfqavI34/ASY2shBJkiRJUmP0OOIX\nEbfx1lU2A/hyRJwMPFv7uszcs3HlSZIkSZJWVW9TPS9cwbIkSZIkqR/oMfhl5k+bWYgkSZIkqTF6\nm+p5TF92kJkX1a8cSZIkSVK99TbV84g+vD8Bg58kSZIkFVhvUz33aWYhkiRJkqTG6G2qZ3Tety8i\nerztQ2a+2YjCJEmSJEn10dsKEGDsAAASwUlEQVRUz/nAutXnb/DWrR06RXXdGg2oS5IkSZJUJ70F\nv+1qnm/R6EIkSZIkSY3R2zl+T9Y8fxwq0z+BEcALndNAJUmSJEnF1uO5e7UiYnhEXAZ0AM8BiyPi\nsohYv6HVSZIkSZJWWZ+CH3AxsDYwFhgC7AishbdykCRJkqTC6+0cv1r7ABtl5uLq8oMRcTTwdEOq\nkiRJkiTVTV9H/B4G2rqt27y6XpIkSZJUYL3dx++YmsX/Bm6snuf3JLAZ8FngssaWJ0mSJElaVb1N\n9Tyi2/IjwG7VB8CjNc8lSZIkSQXV2+0c9mlmIZIkSZKkxujrOX6SJEmSpH7K4CdJkiRJJWfwkyRJ\nkqSSM/hJkiRJUsn19QbuRMRg4D3AkNr1mXlHvYuSJEmSJNVPn4JfRBwJnA28Biyu2ZRUbuQuSZIk\nSSqovo74/RtwaGb+VyOLkSRJkiTVX1/P8XsNuKWBdUiSJEmSGqSvwe9bwPcjYkQji5EkSZIk1V9f\ng99s4CDguYhYUn28GRFLGlibJEmSJKkO+nqO32XApcBVLH1xF0mSJElSwfU1+G0AnJKZ2chiJEmS\nJEn119epnhcDRzSyEEmSJElSY/R1xG8X4MSI+GfgudoNmbln3auSJEmSJNVNX4Pfj6sPSZIkSVI/\n06fgl5k/bXQhkiRJkqTG6PUcv4g4q9vy57ot/6IRRUmSJEmS6mdFF3c5utvyv3db/lD9SpEkSZIk\nNcKKgl+sYFmSJEmSVHArCn7d79vnffwkSZIkqZ9Z0cVdBkTEPrw10td9eY2GVSZJkiRJqosVBb/n\ngYtqll/stvx83SuSJEmSJNVVr8EvM9uaVIckSZIkqUFWdI6fJEmSJKmfM/hJkiRJUskZ/CRJkiSp\n5Ax+kiRJklRyBj9JkiRJKjmDnyRJkiSVnMFPkiRJkkrO4CdJkiRJJWfwkyRJkqSSM/hJkiRJUskV\nLvhFxJYR0RERP2t1LZIkSZJUBoULfsA5wF2tLkKSJEmSyqJQwS8iJgCvAP/d6lokSZIkqSwKE/wi\nYl3gNOCkXl5zfETMiIgZ8+bNa15xkiRJktSPFSb4Ad8GfpKZT/b0gsy8IDPHZea4kSNHNrE0SZIk\nSeq/BrS6AICIGAvsB+zY6lokSZIkqWwKEfyAvYE24ImIABgCrBER22bmTi2sS5IkSZL6vaIEvwuA\nK2uWv0olCH6+JdVIkiRJUokUIvhl5iJgUedyRCwEOjLTK7hIkiRJ0ioqRPDrLjMnt7oGSZIkSSqL\nIl3VU5IkSZLUAAY/SZIkSSo5g58kSZIklZzBT5IkSZJKzuAnSZIkSSVn8JMkSZKkkjP4SZIkSVLJ\nGfwkSZIkqeQMfpIkSZJUcgY/SZIkSSo5g58kSZIklZzBT5IkSZJKzuAnSZIkSSVn8JMkSZKkkjP4\nSZIkSVLJGfwkSZIkqeQMfpIkSZJUcgY/SZIkSSo5g58kSZIklZzBT5IkSZJKzuAnSZIkSSVn8JMk\nSZKkkjP4SZIkSVLJGfwkSZIkqeQMfpIkSZJUcgY/SZIkSSo5g58kSZIklZzBT5IkSZJKzuAnSZIk\nSSVn8JMkSZKkkjP4SZIkSVLJGfwkSZIkqeQMfpIkSZJUcgY/SZIkSSo5g58kSZIklZzBT5IkSZJK\nzuAnSZIkSSVn8JMkSZKkkjP4SZIkSVLJGfwkSZIkqeQMfpIkSZJUcgY/SZIkSSo5g58kSZIklZzB\nT5IkSZJKzuAnSZIkSSVn8JMkSZKkkjP4SZIkSVLJGfwkSZIkqeQGtLoASZIkSWqKycOadJz5zTnO\nSnDET5IkSZJKzuAnSZIkSSVXiOAXEWtFxE8i4vGIWBARf4qIj7a6LkmSJEkqg0IEPyrnGj4J7AUM\nA74FXB0RbS2sSZIkSZJKoRAXd8nMvwKTa1ZdHxGPAe8F5rSiJkmSJEkqi6KM+C0lIt4JbAXManUt\nkiRJktTfFS74RcRA4HLgp5n5ULdtx0fEjIiYMW/evNYUKEmSJEn9TKGCX0S8A7gMeA04sfv2zLwg\nM8dl5riRI0c2vT5JkiRJ6o8KcY4fQEQE8BPgncDHMvP1FpckSZIkSaVQmOAHnAdsA+yXmYtbXYwk\nSZIklUUhpnpGxCjgH4CxwLMRsbD6OLzFpUmSJElSv1eIEb/MfByIVtchSZIkSWVUiBE/SZIkSVLj\nGPwkSZIkqeQMfpIkSZJUcgY/SZIkSSo5g58kSZIklZzBT5IkSZJKzuAnSZIkSSVn8JMkSZKkkjP4\nSZIkSVLJGfwkSZIkqeQMfpIkSZJUcgY/SZIkSSo5g58kSZIklZzBT5IkSZJKzuAnSZIkSSVn8JMk\nSZKkkjP4SZIkSVLJGfwkSZIkqeQMfpIkSZJUcgY/SZIkSSo5g58kSZIklZzBT5IkSZJKzuAnSZIk\nSSVn8JMkSZKkkjP4SZIkSVLJGfwkSZIkqeQMfpIkSZJUcgY/SZIkSSo5g58kSZIklZzBT5IkSZJK\nzuAnSZIkSSVn8JMkSZKkkjP4SZIkSVLJGfwkSZIkqeQMfpIkSZJUcgY/SZIkSSo5g58kSZIklZzB\nT5IkSZJKzuAnSZIkSSVn8JMkSZKkkjP4SZIkSVLJGfwkSZIkqeQMfpIkSZJUcgY/SZIkSSo5g58k\nSZIklZzBT5IkSZJKzuAnSZIkSSVn8JMkSZKkkjP4SZIkSVLJGfwkSZIkqeQMfpIkSZJUcgY/SZIk\nSSq5wgS/iFg/Iq6NiL9GxOMR8ZlW1yRJkiRJZTCg1QXUOAd4DXgnMBa4ISLuzcxZrS1LkiRJkvq3\nQoz4RcQ6wKHAtzJzYWbeDvz/wBGtrUySJEmS+r/IzFbXQETsCNyRmWvXrPsqsFdmHliz7njg+Ori\n1sDDTS208UYAL7S6CPXI/hSfPSo+e1Rs9qf47FHx2aNiK1t/RmXmyL68sChTPYcA87utmw8MrV2R\nmRcAFzSrqGaLiBmZOa7VdWj57E/x2aPis0fFZn+Kzx4Vnz0qttW5P4WY6gksBNbttm5dYEELapEk\nSZKkUilK8JsNDIiILWvW7QB4YRdJkiRJWkWFCH6Z+VfgGuC0iFgnIt4PfAK4rLWVNV1pp7GWhP0p\nPntUfPao2OxP8dmj4rNHxbba9qcQF3eByn38gIuADwEvAidn5hWtrUqSJEmS+r/CBD9JkiRJUmMU\nYqqnJEmSJKlxDH6SJEmSVHIGP0mSJEkqOYNfwUTFnq2uQxURsV4P6zdtdi1aWkS8IyJG1Cy3R8Qn\nIuLvWlmXehcRZ0XE0FbXoWVV/0xtFxFjImKNVtezuouIdSNizZrlfSPiP6qPvVtYmqoi4uCIWLvV\ndah31f8ffDoiNqkuf7T6b9Exra6t2Qx+xbMmML3VRazuImKriHgQeDEinoqIT3V7yZ9bUZcqImJ3\nYB7wXEScHxFHAFcBZwAPV28JoxaKiGOW9wCOAD63Ov6DWzQRcUHN81HAvcAfgTuBByLi3a2qTUDl\n/wLvAYiIE4ApwMDq46qIOLaFtaniF8CzEfGTiPhAq4vRsiLiOOAO4CRgZkR8DvgBsAT4p4j4bivr\nazav6tkCEXFkL5vXBH6Umf60tYUi4tfAXcCZwF7A2cDZmXl6dfuCzHTUokUi4vfAvwFvAr8EPpOZ\nV1W3fR34SGbu08ISV3sR8SbwAJXb89TaHZgB/C0zP9j0wtQlIl7NzHWrz6cCzwBfBgL4D2DzzDyk\nhSWu1rr15yHgk5l5f3V5G+C6zHxPK2tc3UXEAuDjwNHAoVR+IHkpcGlmPtbC0lQVEY8AEzJzRkTs\nBvwO2C4z/7f6w63pmbl5a6tsHoNfC0TEEmAm8LflbQZ2M/i1VkS8ALwzM5dUlzcFfgv8MjP/2eDX\nWhHxUmauHxEBdABDM/O16rZBwNzMHNHrTtRQEXEUcDLwg8w8v2b9M8AOmfl8y4oTsPQPsCLiaWB0\nZr5aXR4CPJKZTp1ukYh4Ctg1M+dW/9yMqvl7bk3gRf8daq1u4XxtYDxwJLA38Hvgksy8pGUFioiY\nn5nDqs/fQeWHjgOXt3114FTP1vhf4OuZ+YHuD2A/KuFPrfUm0PUPambOpfIX+Ucj4qxWFaUubwJk\n5SdXD3X+Z6hqCZWpUGqhzPwpsBuwQ0T8T0Ts0uqatKyI2KL6U+83gUU1mxYB67SmKlVdDFwSEVtQ\nmX1yTkRsWv1B5A+A21tanZaSmYsz87LM/BCwBXAj8LUWlyV4JCKOrf5QeBKVU3j2A4iIfYAnWlpd\nkxn8WuN3wOgeti2pbldr3QEcXLsiM+cBHwTeBwxuRVHq8lBEbAmQmTt027YL4BSbAsjMVzLz88CJ\nVP7TeiGV6ewqhnWAR6j8MHJjKn+3dWoHnmpFUepyCvAH4D4qo+efAx6vPrYBPE+29Zb7g/rMnJuZ\n383MbZtdkJbxj1TO//8rlVMNjgWmRcRMYBpwagtrazqnekrLUQ0VwzPzruVsGwocnJmXNr8yAUTE\nBsDCzFxmunRE7AqslZm3Nr8y9aQ6xeYLVH54clTnlEIVU0RsS2W6uxcba7GIGE4liG8KLAbuy8y/\ntLYqAUTEHpnpyGvBRcQAYP3OUwwiYmtgDPCn1e3PksFPkiRJkkrOqZ4FExEDIuKiVtehntmjYrM/\nxWePis8eFZv9KT57VHyrY48c8SuYiFgLWORVPYvLHhWb/Sk+e1R89qjY7E/x2aPiWx17NKDVBayO\nIuLmXjavNr/5isweFZv9KT57VHz2qNjsT/HZo+KzR0sz+LXGrsD/R+Vmud0NBPZobjlaDntUbPan\n+OxR8dmjYrM/xWePis8e1XCqZwtExO+BMzNz6nK2rQUszkzPv2whe1Rs9qf47FHx2aNisz/FZ4+K\nzx4tbbX5oAXzn8BLPWx7HZjYxFq0fPao2OxP8dmj4rNHxWZ/is8eFZ89quGInyRJkiSVnOf4tVBE\nbAVsBwwFFgCzMnN2a6tSLXtUbPan+OxR8dmjYrM/xWePis8eVTji1wIRsTlwFbAD8CgwH1gXeDdw\nLzAhM59oXYWyR8Vmf4rPHhWfPSo2+1N89qj47NHSPMevNS4GbgNGZGZ7Zu6RmdsDG1bXX9LK4gTY\no6KzP8Vnj4rPHhWb/Sk+e1R89qiGI34tEBELgfUz87XlbFsLeCkz12l+Zepkj4rN/hSfPSo+e1Rs\n9qf47FHx2aOlOeLXGk8CH+9h28eA1WbIucDsUbHZn+KzR8Vnj4rN/hSfPSo+e1TDEb8WiIh9gV8A\nD1CZX9w533gslRNPD83Mm1tXoexRsdmf4rNHxWePis3+FJ89Kj57tDSDX4tExAbAIVR+0w0BFgKz\ngGsz84VW1qYKe1Rs9qf47FHx2aNisz/FZ4+Kzx69xeDXItWrDL2X5VxONiIOy8wpralMnexRsdmf\n4rNHxWePis3+FJ89Kj579BaDXwtExEeAq4HHgC2pXFHoi5m5pLr91cxct3UVyh4Vm/0pPntUfPao\n2OxP8dmj4rNHS/PiLq3xHeCwzNwB2ILKb8RpEbFmdXu0rDJ1skfFZn+Kzx4Vnz0qNvtTfPao+OxR\nDUf8WiAi5mfmsJrlAcDPgBHAQcBzmTm0VfXJHhWd/Sk+e1R89qjY7E/x2aPis0dLc8SvNV6OiM06\nFzLzDeAwKpeUvQlYo1WFqYs9Kjb7U3z2qPjsUbHZn+KzR8Vnj2oY/FrjJmBi7YqsOAa4DxjUkqpU\nyx4Vm/0pPntUfPao2OxP8dmj4rNHNZzq2QLVecUDMnNRD9s3z8zV6oaSRWOPis3+FJ89Kj57VGz2\np/jsUfHZo6UZ/CRJkiSp5JzqKUmSJEklZ/CTJEmSpJIz+EmSJElSyRn8JElajoi4JSJejoi1Wl2L\nJEmryuAnSVI3EdEGfABIKjf5lSSpXzP4SZK0rCOBPwCXAEd1royISyLinIi4ISIWRMQfI+LdNdt3\nj4i7ImJ+9dfdm1+6JEnLMvhJkrSsI4HLq48PR8Q7a7YdBpwKrAc8AnwHICLWB24AzgI2AL4P3BAR\nGzSxbkmSlsvgJ0lSjYjYAxgFXJ2ZM4FHgc/UvOSazPyfzHyDSjAcW11/APC/mXlZZr6RmVOAh4AD\nm1i+JEnLZfCTJGlpRwE3ZuYL1eUrqJnuCTxb83wRMKT6fGPg8W77ehzYpBFFSpK0Mga0ugBJkooi\nItYGPgWsERGdAW8tYHhE7LCCtz9NZaSw1ubAb+pbpSRJK88RP0mS3vL3wBJgWypTOMcC2wC3UTnv\nrze/AraKiM9ExICI+HR1P9c3sF5JkvrE4CdJ0luOAi7OzCcy89nOB3A2cDi9zJTJzBeBjwMnAS8C\nXwM+XjNlVJKklonMbHUNkiRJkqQGcsRPkiRJkkrO4CdJkiRJJWfwkyRJkqSSM/hJkiRJUskZ/CRJ\nkiSp5Ax+kiRJklRyBj9JkiRJKjmDnyRJkiSVnMFPkiRJkkru/wE71+2zbTDPiwAAAABJRU5ErkJg\ngg==\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x2aacc27d630>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "grafico1 = series[['valOrcadoAtualizado','valLiquidado']].plot(kind='bar', title =\"Orçado x Liquidado\", figsize=(15, 7), legend=True, fontsize=12)\n",
    "\n",
    "grafico1.set_xlabel(\"Ano\", fontsize=12)\n",
    "\n",
    "grafico1.set_ylabel(\"Em bilhões de R$\", fontsize=12)\n",
    "\n",
    "plt.show(grafico1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Empenhos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Empenho é o ato em que autoridade verifica a existência do crédito orçamentário e autoriza a execução da despesa (por exemplo, para realizar uma licitação). A partir daí, os valores vão sendo liquidados e pagos conforme a execução de um contrato. \n",
    "\n",
    "A API fornece apenas uma página na consulta. O script abaixo checa a quantidade de páginas nos metadados da consulta e itera o número de vezes necessário para obter todas as páginas."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Opção 1. Um ano inteiro"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Vamos ver quanto a Secretaria Municipal de Educação empenhou de seu orçamento em 2017."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pagination = '&numPagina={PAGE}'\n",
    "ano_empenho = 2017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "request_empenhos = requests.get('{base_url}/consultaEmpenhos?anoEmpenho={ano}&mesEmpenho=12&codOrgao=16'.format(base_url=base_url, ano=ano_empenho), \n",
    "                                            headers=headers, verify=True).json()\n",
    "    \n",
    "    number_of_pages = request_empenhos['metadados']['qtdPaginas']\n",
    "\n",
    "    todos_empenhos = []\n",
    "    \n",
    "    todos_empenhos = todos_empenhos + request_empenhos['lstEmpenhos']\n",
    "    \n",
    "    if number_of_pages>1:\n",
    "        \n",
    "        for p in range(2, number_of_pages+1):\n",
    "            \n",
    "            request_empenhos = requests.get('{base_url}/consultaEmpenhos?anoEmpenho={ano}&mesEmpenho=12&codOrgao=16'.format(base_url=base_url, ano=ano_empenho) + pagination.format(PAGE=p), \n",
    "                                            headers=headers, verify=True).json()\n",
    "            \n",
    "            todos_empenhos = todos_empenhos + request_empenhos['lstEmpenhos']\n",
    "            \n",
    "df_empenhos = pd.DataFrame(todos_empenhos)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Opção 2. Série Histórica"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pagination = '&numPagina={PAGE}'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Atenção: as consultas podem demorar horas, a depender da quantidade de anos requerida; verifique se o número de anos acima é realmente necessário; faça apenas isso uma vez, e guarde a base para análises futuras **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_empenhos_lista = []\n",
    "\n",
    "for ano in anos:\n",
    "\n",
    "    request_empenhos = requests.get('{base_url}/consultaEmpenhos?anoEmpenho={ano}&mesEmpenho=12&codOrgao=16'.format(base_url=base_url, ano=ano), \n",
    "                                            headers=headers, verify=True).json()\n",
    "    \n",
    "    number_of_pages = request_empenhos['metadados']['qtdPaginas']\n",
    "\n",
    "    todos_empenhos = []\n",
    "    \n",
    "    todos_empenhos = todos_empenhos + request_empenhos['lstEmpenhos']\n",
    "    \n",
    "    if number_of_pages>1:\n",
    "        \n",
    "        for p in range(2, number_of_pages+1):\n",
    "            \n",
    "            request_empenhos = requests.get('{base_url}/consultaEmpenhos?anoEmpenho={ano}&mesEmpenho=12&codOrgao=16'.format(base_url=base_url, ano=ano) + pagination.format(PAGE=p), \n",
    "                                            headers=headers, verify=True).json()\n",
    "            \n",
    "            todos_empenhos = todos_empenhos + request_empenhos['lstEmpenhos']\n",
    "            \n",
    "    df_empenhos = pd.DataFrame(todos_empenhos)\n",
    "    \n",
    "    df_empenhos_lista.append(df_empenhos)\n",
    "\n",
    "df_empenhos_serie = pd.concat(df_empenhos_lista, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['anoEmpenho', 'codCategoria', 'codElemento', 'codEmpenho', 'codEmpresa',\n",
       "       'codFonteRecurso', 'codFuncao', 'codGrupo', 'codItemDespesa',\n",
       "       'codModalidade', 'codOrgao', 'codPrograma', 'codProjetoAtividade',\n",
       "       'codSubElemento', 'codSubFuncao', 'codUnidade', 'datEmpenho',\n",
       "       'mesEmpenho', 'nomEmpresa', 'numCpfCnpj', 'numReserva',\n",
       "       'txtCategoriaEconomica', 'txtDescricaoElemento',\n",
       "       'txtDescricaoFonteRecurso', 'txtDescricaoFuncao',\n",
       "       'txtDescricaoItemDespesa', 'txtDescricaoOrgao', 'txtDescricaoPrograma',\n",
       "       'txtDescricaoProjetoAtividade', 'txtDescricaoSubElemento',\n",
       "       'txtDescricaoSubFuncao', 'txtDescricaoUnidade', 'txtGrupoDespesa',\n",
       "       'txtModalidadeAplicacao', 'txtRazaoSocial', 'valAnuladoEmpenho',\n",
       "       'valEmpenhadoLiquido', 'valLiquidado', 'valPagoExercicio',\n",
       "       'valPagoRestos', 'valTotalEmpenhado'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_empenhos_serie.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Com os passos acima, fizemos a requisição de todas as páginas e convertemos o arquivo formato json em um DataFrame. Agora podemos trabalhar com a análise desses dado no Pandas. Para checar quantos registros existentes, vamos ver o final da lista (aqui havia apenas 2016-2017):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>anoEmpenho</th>\n",
       "      <th>codCategoria</th>\n",
       "      <th>codElemento</th>\n",
       "      <th>codEmpenho</th>\n",
       "      <th>codEmpresa</th>\n",
       "      <th>codFonteRecurso</th>\n",
       "      <th>codFuncao</th>\n",
       "      <th>codGrupo</th>\n",
       "      <th>codItemDespesa</th>\n",
       "      <th>codModalidade</th>\n",
       "      <th>...</th>\n",
       "      <th>txtDescricaoUnidade</th>\n",
       "      <th>txtGrupoDespesa</th>\n",
       "      <th>txtModalidadeAplicacao</th>\n",
       "      <th>txtRazaoSocial</th>\n",
       "      <th>valAnuladoEmpenho</th>\n",
       "      <th>valEmpenhadoLiquido</th>\n",
       "      <th>valLiquidado</th>\n",
       "      <th>valPagoExercicio</th>\n",
       "      <th>valPagoRestos</th>\n",
       "      <th>valTotalEmpenhado</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>115242</th>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>39</td>\n",
       "      <td>65602</td>\n",
       "      <td>01</td>\n",
       "      <td>00</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>99</td>\n",
       "      <td>90</td>\n",
       "      <td>...</td>\n",
       "      <td>Departamento de Alimentação Escolar</td>\n",
       "      <td>Outras Despesas Correntes</td>\n",
       "      <td>Aplicações Diretas</td>\n",
       "      <td>BICIEXTIL EXTINTORES EIRELI EPP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>548.00</td>\n",
       "      <td>548.00</td>\n",
       "      <td>548.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>548.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115243</th>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>39</td>\n",
       "      <td>9857</td>\n",
       "      <td>01</td>\n",
       "      <td>00</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>99</td>\n",
       "      <td>90</td>\n",
       "      <td>...</td>\n",
       "      <td>Departamento de Alimentação Escolar</td>\n",
       "      <td>Outras Despesas Correntes</td>\n",
       "      <td>Aplicações Diretas</td>\n",
       "      <td>BG COMERCIO DE BALANÇAS LTDA - EPP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2954.68</td>\n",
       "      <td>2954.68</td>\n",
       "      <td>2954.68</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2954.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115244</th>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>39</td>\n",
       "      <td>9863</td>\n",
       "      <td>01</td>\n",
       "      <td>00</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>99</td>\n",
       "      <td>90</td>\n",
       "      <td>...</td>\n",
       "      <td>Departamento de Alimentação Escolar</td>\n",
       "      <td>Outras Despesas Correntes</td>\n",
       "      <td>Aplicações Diretas</td>\n",
       "      <td>BG COMERCIO DE BALANÇAS LTDA - EPP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>229.68</td>\n",
       "      <td>229.68</td>\n",
       "      <td>229.68</td>\n",
       "      <td>0.0</td>\n",
       "      <td>229.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115245</th>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>39</td>\n",
       "      <td>13108</td>\n",
       "      <td>01</td>\n",
       "      <td>00</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>99</td>\n",
       "      <td>90</td>\n",
       "      <td>...</td>\n",
       "      <td>Departamento de Alimentação Escolar</td>\n",
       "      <td>Outras Despesas Correntes</td>\n",
       "      <td>Aplicações Diretas</td>\n",
       "      <td>DIGIDOK3 DIGITALIZAÇÃO E SERVIÇOS LTDA - EPP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8610.86</td>\n",
       "      <td>8610.86</td>\n",
       "      <td>8610.86</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8610.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115246</th>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>39</td>\n",
       "      <td>13096</td>\n",
       "      <td>01</td>\n",
       "      <td>00</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>99</td>\n",
       "      <td>90</td>\n",
       "      <td>...</td>\n",
       "      <td>Departamento de Alimentação Escolar</td>\n",
       "      <td>Outras Despesas Correntes</td>\n",
       "      <td>Aplicações Diretas</td>\n",
       "      <td>DIGIDOK3 DIGITALIZAÇÃO E SERVIÇOS LTDA - EPP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>86282.43</td>\n",
       "      <td>86282.43</td>\n",
       "      <td>86282.43</td>\n",
       "      <td>0.0</td>\n",
       "      <td>86282.43</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        anoEmpenho  codCategoria codElemento  codEmpenho codEmpresa  \\\n",
       "115242        2017             3          39       65602         01   \n",
       "115243        2017             3          39        9857         01   \n",
       "115244        2017             3          39        9863         01   \n",
       "115245        2017             3          39       13108         01   \n",
       "115246        2017             3          39       13096         01   \n",
       "\n",
       "       codFonteRecurso codFuncao  codGrupo codItemDespesa  codModalidade  \\\n",
       "115242              00        12         3             99             90   \n",
       "115243              00        12         3             99             90   \n",
       "115244              00        12         3             99             90   \n",
       "115245              00        12         3             99             90   \n",
       "115246              00        12         3             99             90   \n",
       "\n",
       "              ...                         txtDescricaoUnidade  \\\n",
       "115242        ...         Departamento de Alimentação Escolar   \n",
       "115243        ...         Departamento de Alimentação Escolar   \n",
       "115244        ...         Departamento de Alimentação Escolar   \n",
       "115245        ...         Departamento de Alimentação Escolar   \n",
       "115246        ...         Departamento de Alimentação Escolar   \n",
       "\n",
       "                  txtGrupoDespesa txtModalidadeAplicacao  \\\n",
       "115242  Outras Despesas Correntes     Aplicações Diretas   \n",
       "115243  Outras Despesas Correntes     Aplicações Diretas   \n",
       "115244  Outras Despesas Correntes     Aplicações Diretas   \n",
       "115245  Outras Despesas Correntes     Aplicações Diretas   \n",
       "115246  Outras Despesas Correntes     Aplicações Diretas   \n",
       "\n",
       "                                      txtRazaoSocial valAnuladoEmpenho  \\\n",
       "115242               BICIEXTIL EXTINTORES EIRELI EPP               0.0   \n",
       "115243            BG COMERCIO DE BALANÇAS LTDA - EPP               0.0   \n",
       "115244            BG COMERCIO DE BALANÇAS LTDA - EPP               0.0   \n",
       "115245  DIGIDOK3 DIGITALIZAÇÃO E SERVIÇOS LTDA - EPP               0.0   \n",
       "115246  DIGIDOK3 DIGITALIZAÇÃO E SERVIÇOS LTDA - EPP               0.0   \n",
       "\n",
       "       valEmpenhadoLiquido valLiquidado  valPagoExercicio valPagoRestos  \\\n",
       "115242              548.00       548.00            548.00           0.0   \n",
       "115243             2954.68      2954.68           2954.68           0.0   \n",
       "115244              229.68       229.68            229.68           0.0   \n",
       "115245             8610.86      8610.86           8610.86           0.0   \n",
       "115246            86282.43     86282.43          86282.43           0.0   \n",
       "\n",
       "       valTotalEmpenhado  \n",
       "115242            548.00  \n",
       "115243           2954.68  \n",
       "115244            229.68  \n",
       "115245           8610.86  \n",
       "115246          86282.43  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_empenhos_serie.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Modalidades de Aplicação\n",
    "\n",
    "Aqui vemos a quantidade de recursos aplicados na Saúde, a título de exemplo, por Modalidade -- se é aplicação na rede direta ou repasse a organizações sociais. Note que o mesmo poderia ser feito para qualquer órgão, ou mesmo para a Prefeitura como um todo:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "modalidades = df_empenhos_serie.groupby('txtModalidadeAplicacao')['valTotalEmpenhado', 'valLiquidado'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>valTotalEmpenhado</th>\n",
       "      <th>valLiquidado</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>txtModalidadeAplicacao</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Apl.Dir.Dec.Op. entre Órgãos,Fundos e Ent.Int.Orç.Fiscal e Seg.Social</th>\n",
       "      <td>3.171919e+05</td>\n",
       "      <td>2.664116e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Aplicações Diretas</th>\n",
       "      <td>1.936534e+10</td>\n",
       "      <td>1.613209e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Execução Orçamentária Delegada à União</th>\n",
       "      <td>1.175625e+06</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Transferências a Instituições Privadas com Fins Lucrativos</th>\n",
       "      <td>2.451820e+07</td>\n",
       "      <td>2.077852e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Transferências a Instituições Privadas sem Fins Lucrativos</th>\n",
       "      <td>4.567242e+09</td>\n",
       "      <td>3.971606e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Transferências ao Exterior</th>\n",
       "      <td>2.015202e+06</td>\n",
       "      <td>2.013540e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Transferências à União</th>\n",
       "      <td>5.129619e+06</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    valTotalEmpenhado  \\\n",
       "txtModalidadeAplicacao                                                  \n",
       "Apl.Dir.Dec.Op. entre Órgãos,Fundos e Ent.Int.O...       3.171919e+05   \n",
       "Aplicações Diretas                                       1.936534e+10   \n",
       "Execução Orçamentária Delegada à União                   1.175625e+06   \n",
       "Transferências a Instituições Privadas com Fins...       2.451820e+07   \n",
       "Transferências a Instituições Privadas sem Fins...       4.567242e+09   \n",
       "Transferências ao Exterior                               2.015202e+06   \n",
       "Transferências à União                                   5.129619e+06   \n",
       "\n",
       "                                                    valLiquidado  \n",
       "txtModalidadeAplicacao                                            \n",
       "Apl.Dir.Dec.Op. entre Órgãos,Fundos e Ent.Int.O...  2.664116e+05  \n",
       "Aplicações Diretas                                  1.613209e+10  \n",
       "Execução Orçamentária Delegada à União              0.000000e+00  \n",
       "Transferências a Instituições Privadas com Fins...  2.077852e+07  \n",
       "Transferências a Instituições Privadas sem Fins...  3.971606e+09  \n",
       "Transferências ao Exterior                          2.013540e+06  \n",
       "Transferências à União                              0.000000e+00  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "modalidades"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Outra maneira de fazer a mesma operação:\n",
    "#pd.pivot_table(df_empenhos, values='valTotalEmpenhado', index=['txtModalidadeAplicacao'], aggfunc=np.sum)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Maiores despesas\n",
    "\n",
    "Aqui vamos produzir a lista das 15 maiores despesas da Educação neste período:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "despesas = pd.pivot_table(df_empenhos_serie, \n",
    "                              values=['valLiquidado', 'valPagoExercicio'], \n",
    "                              index=['numCpfCnpj', 'txtRazaoSocial', 'txtDescricaoProjetoAtividade'], \n",
    "                              aggfunc=np.sum).sort_values('valPagoExercicio', axis=0, ascending=False, inplace=False, kind='quicksort', na_position='last')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>valLiquidado</th>\n",
       "      <th>valPagoExercicio</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>numCpfCnpj</th>\n",
       "      <th>txtRazaoSocial</th>\n",
       "      <th>txtDescricaoProjetoAtividade</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">00000000999962</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">FOLHA DE PAGAMENTO _ EXECUTIVO</th>\n",
       "      <th>Remuneração dos Profissionais do Magistério</th>\n",
       "      <td>7.336578e+09</td>\n",
       "      <td>7.336578e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Operação e Manutenção do Sistema Municipal de Ensino</th>\n",
       "      <td>2.963915e+09</td>\n",
       "      <td>2.963915e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00051412000000</th>\n",
       "      <th>SERV.SEC.MUN. DE EDUCAÇÃO - AUX. REFEIÇÃO</th>\n",
       "      <th>Operação e Manutenção do Sistema Municipal de Ensino</th>\n",
       "      <td>8.030312e+08</td>\n",
       "      <td>8.030312e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00000000999962</th>\n",
       "      <th>FOLHA DE PAGAMENTO _ EXECUTIVO</th>\n",
       "      <th>Administração da Unidade</th>\n",
       "      <td>6.537685e+08</td>\n",
       "      <td>6.537685e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61980272000190</th>\n",
       "      <th>SHA COMERCIO DE ALIMENTOS LTDA</th>\n",
       "      <th>Alimentação Escolar</th>\n",
       "      <td>2.152894e+08</td>\n",
       "      <td>2.125028e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00000000999962</th>\n",
       "      <th>FOLHA DE PAGAMENTO _ EXECUTIVO</th>\n",
       "      <th>Operação e Manutenção dos Centros Educacionais Unificados</th>\n",
       "      <td>1.689856e+08</td>\n",
       "      <td>1.689856e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60166832000104</th>\n",
       "      <th>APETECE SISTEMAS DE ALIMENTACAO S/A</th>\n",
       "      <th>Alimentação Escolar</th>\n",
       "      <td>1.661347e+08</td>\n",
       "      <td>1.661347e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39787056000173</th>\n",
       "      <th>TANGARÁ IMP.E EXP.S/A</th>\n",
       "      <th>Leve-Leite</th>\n",
       "      <td>1.607405e+08</td>\n",
       "      <td>1.607405e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05951758000129</th>\n",
       "      <th>DENJUD REFEIÇÕES COLETIVAS ADMINISTRAÇÃO E SERVIÇOS LTDA</th>\n",
       "      <th>Alimentação Escolar</th>\n",
       "      <td>1.539616e+08</td>\n",
       "      <td>1.539616e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43776517000180</th>\n",
       "      <th>CIA DE SANEAMENTO BÁSICO DO ESTADO DE SÃO PAULO SABESP</th>\n",
       "      <th>Operação e Manutenção do Sistema Municipal de Ensino</th>\n",
       "      <td>1.266086e+08</td>\n",
       "      <td>1.260563e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01920177000764</th>\n",
       "      <th>COMERCIAL MILANO BRASIL LTDA</th>\n",
       "      <th>Alimentação Escolar</th>\n",
       "      <td>1.267848e+08</td>\n",
       "      <td>1.240951e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00000000999962</th>\n",
       "      <th>FOLHA DE PAGAMENTO _ EXECUTIVO</th>\n",
       "      <th>Ações Complementares do Sistema Municipal de Ensino</th>\n",
       "      <td>1.073501e+08</td>\n",
       "      <td>1.073501e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>02183750000171</th>\n",
       "      <th>BASE SISTEMA SERVIÇOS DE ADMINISTRAÇÃO E COMERCIO LTDA</th>\n",
       "      <th>Alimentação Escolar</th>\n",
       "      <td>7.729509e+07</td>\n",
       "      <td>7.729509e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61695227000193</th>\n",
       "      <th>ELETROPAULO METROPOLITANA ELETRICIDADE DE SÃO PAULO S.A.</th>\n",
       "      <th>Operação e Manutenção do Sistema Municipal de Ensino</th>\n",
       "      <td>6.987073e+07</td>\n",
       "      <td>6.987071e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53818191000160</th>\n",
       "      <th>SOCIEDADE BENEFICIENTE EQUILIBRIO DE INTERLAGOS</th>\n",
       "      <th>Operação e Manutenção de CEIs e Creches da rede conveniada e outras mo</th>\n",
       "      <td>6.799869e+07</td>\n",
       "      <td>6.798499e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                                                      valLiquidado  \\\n",
       "numCpfCnpj     txtRazaoSocial                                     txtDescricaoProjetoAtividade                                       \n",
       "00000000999962 FOLHA DE PAGAMENTO _ EXECUTIVO                     Remuneração dos Profissionais do Magistério         7.336578e+09   \n",
       "                                                                  Operação e Manutenção do Sistema Municipal de E...  2.963915e+09   \n",
       "00051412000000 SERV.SEC.MUN. DE EDUCAÇÃO - AUX. REFEIÇÃO          Operação e Manutenção do Sistema Municipal de E...  8.030312e+08   \n",
       "00000000999962 FOLHA DE PAGAMENTO _ EXECUTIVO                     Administração da Unidade                            6.537685e+08   \n",
       "61980272000190 SHA COMERCIO DE ALIMENTOS LTDA                     Alimentação Escolar                                 2.152894e+08   \n",
       "00000000999962 FOLHA DE PAGAMENTO _ EXECUTIVO                     Operação e Manutenção dos Centros Educacionais ...  1.689856e+08   \n",
       "60166832000104 APETECE SISTEMAS DE ALIMENTACAO S/A                Alimentação Escolar                                 1.661347e+08   \n",
       "39787056000173 TANGARÁ IMP.E EXP.S/A                              Leve-Leite                                          1.607405e+08   \n",
       "05951758000129 DENJUD REFEIÇÕES COLETIVAS ADMINISTRAÇÃO E SERV... Alimentação Escolar                                 1.539616e+08   \n",
       "43776517000180 CIA DE SANEAMENTO BÁSICO DO ESTADO DE SÃO PAULO... Operação e Manutenção do Sistema Municipal de E...  1.266086e+08   \n",
       "01920177000764 COMERCIAL MILANO BRASIL LTDA                       Alimentação Escolar                                 1.267848e+08   \n",
       "00000000999962 FOLHA DE PAGAMENTO _ EXECUTIVO                     Ações Complementares do Sistema Municipal de En...  1.073501e+08   \n",
       "02183750000171 BASE SISTEMA SERVIÇOS DE ADMINISTRAÇÃO E COMERC... Alimentação Escolar                                 7.729509e+07   \n",
       "61695227000193 ELETROPAULO METROPOLITANA ELETRICIDADE DE SÃO P... Operação e Manutenção do Sistema Municipal de E...  6.987073e+07   \n",
       "53818191000160 SOCIEDADE BENEFICIENTE EQUILIBRIO DE INTERLAGOS    Operação e Manutenção de CEIs e Creches da rede...  6.799869e+07   \n",
       "\n",
       "                                                                                                                      valPagoExercicio  \n",
       "numCpfCnpj     txtRazaoSocial                                     txtDescricaoProjetoAtividade                                          \n",
       "00000000999962 FOLHA DE PAGAMENTO _ EXECUTIVO                     Remuneração dos Profissionais do Magistério             7.336578e+09  \n",
       "                                                                  Operação e Manutenção do Sistema Municipal de E...      2.963915e+09  \n",
       "00051412000000 SERV.SEC.MUN. DE EDUCAÇÃO - AUX. REFEIÇÃO          Operação e Manutenção do Sistema Municipal de E...      8.030312e+08  \n",
       "00000000999962 FOLHA DE PAGAMENTO _ EXECUTIVO                     Administração da Unidade                                6.537685e+08  \n",
       "61980272000190 SHA COMERCIO DE ALIMENTOS LTDA                     Alimentação Escolar                                     2.125028e+08  \n",
       "00000000999962 FOLHA DE PAGAMENTO _ EXECUTIVO                     Operação e Manutenção dos Centros Educacionais ...      1.689856e+08  \n",
       "60166832000104 APETECE SISTEMAS DE ALIMENTACAO S/A                Alimentação Escolar                                     1.661347e+08  \n",
       "39787056000173 TANGARÁ IMP.E EXP.S/A                              Leve-Leite                                              1.607405e+08  \n",
       "05951758000129 DENJUD REFEIÇÕES COLETIVAS ADMINISTRAÇÃO E SERV... Alimentação Escolar                                     1.539616e+08  \n",
       "43776517000180 CIA DE SANEAMENTO BÁSICO DO ESTADO DE SÃO PAULO... Operação e Manutenção do Sistema Municipal de E...      1.260563e+08  \n",
       "01920177000764 COMERCIAL MILANO BRASIL LTDA                       Alimentação Escolar                                     1.240951e+08  \n",
       "00000000999962 FOLHA DE PAGAMENTO _ EXECUTIVO                     Ações Complementares do Sistema Municipal de En...      1.073501e+08  \n",
       "02183750000171 BASE SISTEMA SERVIÇOS DE ADMINISTRAÇÃO E COMERC... Alimentação Escolar                                     7.729509e+07  \n",
       "61695227000193 ELETROPAULO METROPOLITANA ELETRICIDADE DE SÃO P... Operação e Manutenção do Sistema Municipal de E...      6.987071e+07  \n",
       "53818191000160 SOCIEDADE BENEFICIENTE EQUILIBRIO DE INTERLAGOS    Operação e Manutenção de CEIs e Creches da rede...      6.798499e+07  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "despesas.head(15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Fontes de recursos\n",
    "\n",
    "Agrupamento dos empenhos por fonte de recursos:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "fonte = pd.pivot_table(df_empenhos_serie, \n",
    "                       values=['valLiquidado', 'valPagoExercicio'], \n",
    "                       index=['txtDescricaoFonteRecurso'], \n",
    "                       aggfunc=np.sum).sort_values('valPagoExercicio', axis=0, ascending=False, inplace=False, kind='quicksort', na_position='last')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>valLiquidado</th>\n",
       "      <th>valPagoExercicio</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>txtDescricaoFonteRecurso</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Tesouro Municipal</th>\n",
       "      <td>1.153491e+10</td>\n",
       "      <td>1.148541e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fundo Constitucional da Educação</th>\n",
       "      <td>7.336578e+09</td>\n",
       "      <td>7.336578e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Transferências Federais</th>\n",
       "      <td>1.215773e+09</td>\n",
       "      <td>1.201293e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Transferências Estaduais</th>\n",
       "      <td>2.292476e+07</td>\n",
       "      <td>2.292476e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Outras Fontes</th>\n",
       "      <td>1.656644e+07</td>\n",
       "      <td>1.656644e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  valLiquidado  valPagoExercicio\n",
       "txtDescricaoFonteRecurso                                        \n",
       "Tesouro Municipal                 1.153491e+10      1.148541e+10\n",
       "Fundo Constitucional da Educação  7.336578e+09      7.336578e+09\n",
       "Transferências Federais           1.215773e+09      1.201293e+09\n",
       "Transferências Estaduais          2.292476e+07      2.292476e+07\n",
       "Outras Fontes                     1.656644e+07      1.656644e+07"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fonte"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Passo 4. Quer salvar um csv?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "O objetivo deste tutorial não era fazer uma análise exaustiva da base, mas apenas mostrar o que é possível a partir do consumo da API. Você também pode salvar toda a base de empenhos num arquivo .csv e trabalhar no seu Excel (super te entendo). O Pandas também ajuda nisso! Assim:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_empenhos_serie.to_csv('serie_empenhos.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pronto, seu arquivo está lá salvo no mesmo diretório deste jupyter notebook! "
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}