API_Orcamento_Coda2019.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "API_Orcamento_Coda2019.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/campagnucci/dados-abertos-gov-br/blob/master/API_Orcamento_Coda2019.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RpIICuxKfuh_",
        "colab_type": "text"
      },
      "source": [
        "# Gastos públicos federais via API - Despesas e contratos \n",
        "\n",
        "\n",
        "\n",
        "Workshop no Coda.Br 2019\n",
        "\n",
        "*Fernanda Campagnucci*\n",
        "\n",
        "Diretora-Executiva da Open Knowledge Brasil "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-91nqaDrUaiT",
        "colab_type": "text"
      },
      "source": [
        "Duração: 1h15\n",
        "\n",
        "Tópicos:\n",
        "\n",
        "1. **API x Bases de Dados**\n",
        "- Quando usar?\n",
        "- Onde encontrar\n",
        "\n",
        "2. **Planejamento e Orçamento Público**\n",
        "- Princípios do orçamento \n",
        "- Classificações da Despesa\n",
        "- Execução Orçamentária e estágios da despesa \n",
        "\n",
        "3. **Acessando a API**\n",
        "\n",
        "- Despesas\n",
        "- Contratos\n",
        "\n",
        "4. **Mais referências**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lJstFeWdXIK_",
        "colab_type": "text"
      },
      "source": [
        "## 1. API x Bases de Dados\n",
        "---\n",
        "\n",
        "### Quando usar?\n",
        "Quem não tem fluência em programação (como eu!), pode precisar de uma certa curva de aprendizagem para usar a API. Em alguns casos, porém, você pode ganhar muito tempo se optar pela API, mesmo que demore mais para criar seu código pela primeira vez. *Atenção: essa comparação não é geral, se aplica especificamente ao Portal da Transparência do Governo Federal e à maneira como os dados estão disponibilizados hoje*. "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d2-tMprkXRbe",
        "colab_type": "text"
      },
      "source": [
        "| Situação                       |   API           | Base de dados  |\n",
        "| :-------------                 | :-------------  | :------------- |\n",
        "| Preciso de uma série histórica grande| +               | +++++          |\n",
        "| Não faço pauta desse tema com frequência | ++               | +++++|\n",
        "| Quero monitorar um assunto/cnpj/órgão etc | +++++               | +          |\n",
        "| Quero conectar em um site ou visualização | ++++               | ++      |\n",
        "| *Que outras situações ocorrem a vocês?* |               |      |\n",
        " \n",
        " \n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Pn9MOxZHfYPv",
        "colab_type": "text"
      },
      "source": [
        "### Onde encontrar?\n",
        "\n",
        "Para conhecer os dados disponíveis, sistema de origem, órgão responsável, periodicidade e data de atualização do Portal (download e API):\n",
        "\n",
        "http://transparencia.gov.br/origem-dos-dados\n",
        "\n",
        "- **API - Application Programming Interface**\n",
        "\n",
        "http://www.transparencia.gov.br/swagger-ui.html\n",
        "\n",
        "Swagger: Ferramenta que documenta e permite explorar o conteúdo \n",
        "\n",
        "- **Bases para download**\n",
        "\n",
        "http://transparencia.gov.br/download-de-dados\n",
        "\n",
        "https://brasil.io/dataset/gastos-diretos/gastos (coletado e tratado pelo @Turicas)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HYTBi12kg33E",
        "colab_type": "text"
      },
      "source": [
        "## 2. Planejamento e orçamento público \n",
        "---\n",
        "\n",
        "\n",
        "![planejamento e orçamento](http://umdadoamais.com/wp-content/uploads/2019/11/tutorial_orcamento1-e1574301837420.png)\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Q0ftyqomnhq3",
        "colab_type": "text"
      },
      "source": [
        "### Princípios do Orçamento\n",
        "\n",
        "O orçamento público deve observar um conjunto de princípios presentes na Constituição e nas leis que regem o processo orçamentário no Brasil, em especial a Lei nº 4.320/1964 e a Lei nº 101/2000:\n",
        "\n",
        "![alt text](http://umdadoamais.com/wp-content/uploads/2019/11/tutorial_orcamento2.png)\n",
        "\n",
        "O orçamento é *autorizativo*, não obrigatório"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "t9Xhdz-mwYOg",
        "colab_type": "text"
      },
      "source": [
        "### Classificações de Despesa\n",
        "\n",
        "![alt text](http://umdadoamais.com/wp-content/uploads/2019/11/tutorial_orcamento3.png)\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-yVFqa-EPDDl",
        "colab_type": "text"
      },
      "source": [
        "![alt text](http://umdadoamais.com/wp-content/uploads/2019/11/Screen-Shot-2019-11-23-at-00.59.24.png)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WII6A77Z2QMH",
        "colab_type": "text"
      },
      "source": [
        "Exercício no Swagger: http://www.transparencia.gov.br/swagger-ui.html#!/Despesas32P250blicas/despesasPorFuncaoUsingGET"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rU3zqhYscCtU",
        "colab_type": "text"
      },
      "source": [
        "![alt text](http://umdadoamais.com/wp-content/uploads/2019/11/tutorial_orcamento6.png)\n",
        "\n",
        "![alt text](http://umdadoamais.com/wp-content/uploads/2019/11/tutorial_orcamento7.png)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "H0q3h6Jac5-1",
        "colab_type": "text"
      },
      "source": [
        "E se fosse buscar esse dado pela base para download? \n",
        "- Baixar cada mês e juntar as bases\n",
        "- Filtrar o campo de ação\n",
        "- Somar registros de empenhado, liquidado, pago\n",
        "\n",
        "Vantagem: é desagregado\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Ytu-ELgX6RTY",
        "colab_type": "text"
      },
      "source": [
        "## 3. Acessando a API\n",
        "\n",
        "---\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aBAeTjUGtl8c",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import pandas as pd\n",
        "import requests\n",
        "import json\n",
        "import numpy as np"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2tFbhnNyd-V1",
        "colab_type": "text"
      },
      "source": [
        "### Despesas \n",
        "\n",
        "Exercício 1. Coletar uma série histórica de despesas do Ministério do Meio Ambiente "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5ia0z_yGiLnV",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "serie_orgao = []\n",
        "\n",
        "for ano in range(2014, 2020):\n",
        "\n",
        "    url = f'http://www.transparencia.gov.br/api-de-dados/despesas/por-orgao?ano={ano}&orgaoSuperior=44000&pagina=1'\n",
        "    # gerada a partir da consulta por órgão no swagger\n",
        "    \n",
        "    r_orgao = requests.get(url, verify=True).json()\n",
        "\n",
        "    serie_orgao = serie_orgao + r_orgao"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CTLgahsMkacV",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "4bb3bada-1b48-4886-a3e7-7ffc30a85d6c"
      },
      "source": [
        "df_serie_orgao = pd.DataFrame(serie_orgao)\n",
        "df_serie_orgao.tail(5)"
      ],
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>ano</th>\n",
              "      <th>orgao</th>\n",
              "      <th>codigoOrgao</th>\n",
              "      <th>orgaoSuperior</th>\n",
              "      <th>codigoOrgaoSuperior</th>\n",
              "      <th>empenhado</th>\n",
              "      <th>liquidado</th>\n",
              "      <th>pago</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>31</th>\n",
              "      <td>2019</td>\n",
              "      <td>Instituto Brasileiro do Meio Ambiente e dos Re...</td>\n",
              "      <td>20701</td>\n",
              "      <td>Ministério do Meio Ambiente</td>\n",
              "      <td>44000</td>\n",
              "      <td>1.419.953.161,70</td>\n",
              "      <td>1.120.111.516,08</td>\n",
              "      <td>1.111.155.041,62</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>32</th>\n",
              "      <td>2019</td>\n",
              "      <td>Fundo Nacional do Meio Ambiente</td>\n",
              "      <td>44204</td>\n",
              "      <td>Ministério do Meio Ambiente</td>\n",
              "      <td>44000</td>\n",
              "      <td>51.453,33</td>\n",
              "      <td>46.695,22</td>\n",
              "      <td>46.695,22</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>33</th>\n",
              "      <td>2019</td>\n",
              "      <td>Instituto Chico Mendes de Conservação da Biodi...</td>\n",
              "      <td>44207</td>\n",
              "      <td>Ministério do Meio Ambiente</td>\n",
              "      <td>44000</td>\n",
              "      <td>576.288.062,35</td>\n",
              "      <td>490.458.991,03</td>\n",
              "      <td>488.308.637,44</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>34</th>\n",
              "      <td>2019</td>\n",
              "      <td>Fundo Nacional sobre Mudança no Clima</td>\n",
              "      <td>44902</td>\n",
              "      <td>Ministério do Meio Ambiente</td>\n",
              "      <td>44000</td>\n",
              "      <td>350.719.518,00</td>\n",
              "      <td>0,00</td>\n",
              "      <td>0,00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>35</th>\n",
              "      <td>2019</td>\n",
              "      <td>Instituto de Pesquisas Jardim Botânico do Rio ...</td>\n",
              "      <td>44206</td>\n",
              "      <td>Ministério do Meio Ambiente</td>\n",
              "      <td>44000</td>\n",
              "      <td>74.466.616,95</td>\n",
              "      <td>54.759.381,21</td>\n",
              "      <td>54.712.683,17</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "     ano  ...              pago\n",
              "31  2019  ...  1.111.155.041,62\n",
              "32  2019  ...         46.695,22\n",
              "33  2019  ...    488.308.637,44\n",
              "34  2019  ...              0,00\n",
              "35  2019  ...     54.712.683,17\n",
              "\n",
              "[5 rows x 8 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 3
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5_7o6XnIt8Mz",
        "colab_type": "text"
      },
      "source": [
        "Exercicio 1.2 - Agrupar por orgao e comparar ano a ano"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QFOBarKypiJy",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 266
        },
        "outputId": "e59de555-e74d-447d-8ef0-6877c80f19f7"
      },
      "source": [
        "df_por_orgao = pd.pivot_table(df_serie_orgao, \n",
        "                              values='liquidado',\n",
        "                              index=['orgao'],\n",
        "                              columns='ano',\n",
        "                              aggfunc=np.sum).sort_values(by='orgao', ascending=False)\n",
        "                            \n",
        "df_por_orgao"
      ],
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>ano</th>\n",
              "      <th>2014</th>\n",
              "      <th>2015</th>\n",
              "      <th>2016</th>\n",
              "      <th>2017</th>\n",
              "      <th>2018</th>\n",
              "      <th>2019</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>orgao</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>Ministério do Meio Ambiente - Unidades com vínculo direto</th>\n",
              "      <td>290.361.669,73</td>\n",
              "      <td>320.092.573,08</td>\n",
              "      <td>292.733.441,47</td>\n",
              "      <td>291.294.400,47</td>\n",
              "      <td>229.293.617,08</td>\n",
              "      <td>143.621.004,33</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Instituto de Pesquisas Jardim Botânico do Rio de Janeiro</th>\n",
              "      <td>59.148.259,04</td>\n",
              "      <td>61.390.469,20</td>\n",
              "      <td>64.956.162,41</td>\n",
              "      <td>68.872.033,31</td>\n",
              "      <td>70.106.689,12</td>\n",
              "      <td>54.759.381,21</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Instituto Chico Mendes de Conservação da Biodiversidade</th>\n",
              "      <td>521.298.991,82</td>\n",
              "      <td>536.502.218,06</td>\n",
              "      <td>574.340.871,93</td>\n",
              "      <td>584.586.460,34</td>\n",
              "      <td>610.110.950,78</td>\n",
              "      <td>490.458.991,03</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Instituto Brasileiro do Meio Ambiente e dos Recursos Naturais Renováveis</th>\n",
              "      <td>1.206.848.908,65</td>\n",
              "      <td>1.271.750.234,93</td>\n",
              "      <td>1.322.018.555,52</td>\n",
              "      <td>1.372.364.904,86</td>\n",
              "      <td>1.406.268.441,60</td>\n",
              "      <td>1.120.111.516,08</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Fundo Nacional sobre Mudança no Clima</th>\n",
              "      <td>300.000,00</td>\n",
              "      <td>0,00</td>\n",
              "      <td>0,00</td>\n",
              "      <td>23.166.881,00</td>\n",
              "      <td>392.582.919,00</td>\n",
              "      <td>0,00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Fundo Nacional do Meio Ambiente</th>\n",
              "      <td>3.919.125,95</td>\n",
              "      <td>26.713,25</td>\n",
              "      <td>241.528,51</td>\n",
              "      <td>1.811.643,23</td>\n",
              "      <td>1.181.420,06</td>\n",
              "      <td>46.695,22</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "ano                                                             2014  ...              2019\n",
              "orgao                                                                 ...                  \n",
              "Ministério do Meio Ambiente - Unidades com vínc...    290.361.669,73  ...    143.621.004,33\n",
              "Instituto de Pesquisas Jardim Botânico do Rio d...     59.148.259,04  ...     54.759.381,21\n",
              "Instituto Chico Mendes de Conservação da Biodiv...    521.298.991,82  ...    490.458.991,03\n",
              "Instituto Brasileiro do Meio Ambiente e dos Rec...  1.206.848.908,65  ...  1.120.111.516,08\n",
              "Fundo Nacional sobre Mudança no Clima                     300.000,00  ...              0,00\n",
              "Fundo Nacional do Meio Ambiente                         3.919.125,95  ...         46.695,22\n",
              "\n",
              "[6 rows x 6 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 4
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kmoBbFWnuoeL",
        "colab_type": "text"
      },
      "source": [
        "Exercicio 2 - Coletar dados de execução orçamentária da subfunção controle ambiental (542)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RzfTwAcfzCX6",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "ano=2019 #mudar de acordo com a necessidade\n",
        "page=1 #requisito obrigatório da API\n",
        "subfuncao=542 #controle ambiental"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "coO76S2ztwH4",
        "colab_type": "code",
        "outputId": "e7a833a2-4314-4613-ef59-b4531c3585c0",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "url = f'http://www.transparencia.gov.br/api-de-dados/despesas/por-funcional-programatica?ano={ano}&subfuncao={subfuncao}'\n",
        "#gerada no Swagger na consulta funcional programática\n",
        "url"
      ],
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'http://www.transparencia.gov.br/api-de-dados/despesas/por-funcional-programatica?ano=2019&subfuncao=542'"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "E-Q2QKIBubNg",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "r_funcional = requests.get(url+f'&pagina={page}',\n",
        "                           verify=True).json()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6l4ozMaheo37",
        "colab_type": "text"
      },
      "source": [
        "Mas... Precisamos fazer uma iteração para coletar todas as páginas"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GK6t8QzKwIcJ",
        "colab_type": "code",
        "outputId": "21487a92-ff85-4e7c-c614-67d1a00ea1fd",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 119
        }
      },
      "source": [
        "page = 1\n",
        "todas_pag = []\n",
        "todas_pag = todas_pag + r_funcional\n",
        "tamanho = int(requests.get(url+f'&pagina={page}').headers['Content-Length'])\n",
        "#verifica tamanho da resposta; 22 é padrão da resposta vazia, ou seja, não há conteúdo nessa página\n",
        "\n",
        "while(tamanho>22):\n",
        "    page = page +1\n",
        "    print(page)  \n",
        "    r_funcional = requests.get(url+f'&pagina={page}').json() \n",
        "    print(url+f'&pagina={page}')\n",
        "    todas_pag = todas_pag + r_funcional\n",
        "    tamanho = int(requests.get(url+f'&pagina={page}').headers['Content-Length'])\n",
        "    print(tamanho)"
      ],
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "2\n",
            "http://www.transparencia.gov.br/api-de-dados/despesas/por-funcional-programatica?ano=2019&subfuncao=542&pagina=2\n",
            "722\n",
            "3\n",
            "http://www.transparencia.gov.br/api-de-dados/despesas/por-funcional-programatica?ano=2019&subfuncao=542&pagina=3\n",
            "22\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_8niXpCxvXRG",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df_funcional = pd.DataFrame(todas_pag)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TN47ThD7-5lm",
        "colab_type": "code",
        "outputId": "37ea2edf-e15a-4870-b658-19e0e93534ce",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 442
        }
      },
      "source": [
        "df_funcional.head()"
      ],
      "execution_count": 29,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>ano</th>\n",
              "      <th>funcao</th>\n",
              "      <th>codigoFuncao</th>\n",
              "      <th>subfuncao</th>\n",
              "      <th>codigoSubfuncao</th>\n",
              "      <th>programa</th>\n",
              "      <th>codigoPrograma</th>\n",
              "      <th>acao</th>\n",
              "      <th>codigoAcao</th>\n",
              "      <th>empenhado</th>\n",
              "      <th>liquidado</th>\n",
              "      <th>pago</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2019</td>\n",
              "      <td>Gestão ambiental</td>\n",
              "      <td>18</td>\n",
              "      <td>Controle ambiental</td>\n",
              "      <td>542</td>\n",
              "      <td>QUALIDADE AMBIENTAL</td>\n",
              "      <td>2083</td>\n",
              "      <td>LICENCIAMENTO AMBIENTAL FEDERAL</td>\n",
              "      <td>6925</td>\n",
              "      <td>4.604.677,75</td>\n",
              "      <td>2.373.900,40</td>\n",
              "      <td>2.326.754,32</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2019</td>\n",
              "      <td>Gestão ambiental</td>\n",
              "      <td>18</td>\n",
              "      <td>Controle ambiental</td>\n",
              "      <td>542</td>\n",
              "      <td>MUDANCA DO CLIMA</td>\n",
              "      <td>2050</td>\n",
              "      <td>PREVENCAO E CONTROLE DE INCENDIOS FLORESTAIS N...</td>\n",
              "      <td>214M</td>\n",
              "      <td>36.589.934,14</td>\n",
              "      <td>28.870.331,05</td>\n",
              "      <td>25.091.211,26</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2019</td>\n",
              "      <td>Defesa nacional</td>\n",
              "      <td>05</td>\n",
              "      <td>Controle ambiental</td>\n",
              "      <td>542</td>\n",
              "      <td>DEFESA NACIONAL</td>\n",
              "      <td>2058</td>\n",
              "      <td>IMPLANTACAO DO SISTEMA AMAZONIA - SAR</td>\n",
              "      <td>15P5</td>\n",
              "      <td>1.368.364,36</td>\n",
              "      <td>298.364,36</td>\n",
              "      <td>298.364,36</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2019</td>\n",
              "      <td>Gestão ambiental</td>\n",
              "      <td>18</td>\n",
              "      <td>Controle ambiental</td>\n",
              "      <td>542</td>\n",
              "      <td>FLORESTAS, PREVENCAO E CONTROLE DO DESMATAMENT...</td>\n",
              "      <td>2036</td>\n",
              "      <td>PREVENCAO E COMBATE A INCENDIOS FLORESTAIS EM ...</td>\n",
              "      <td>20MY</td>\n",
              "      <td>0,00</td>\n",
              "      <td>0,00</td>\n",
              "      <td>0,00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2019</td>\n",
              "      <td>Gestão ambiental</td>\n",
              "      <td>18</td>\n",
              "      <td>Controle ambiental</td>\n",
              "      <td>542</td>\n",
              "      <td>QUALIDADE AMBIENTAL</td>\n",
              "      <td>2083</td>\n",
              "      <td>AVALIACAO DE PERICULOSIDADE E CONTROLE DE PROD...</td>\n",
              "      <td>20WH</td>\n",
              "      <td>1.516.189,89</td>\n",
              "      <td>843.027,57</td>\n",
              "      <td>770.291,75</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "    ano            funcao  ...      liquidado           pago\n",
              "0  2019  Gestão ambiental  ...   2.373.900,40   2.326.754,32\n",
              "1  2019  Gestão ambiental  ...  28.870.331,05  25.091.211,26\n",
              "2  2019   Defesa nacional  ...     298.364,36     298.364,36\n",
              "3  2019  Gestão ambiental  ...           0,00           0,00\n",
              "4  2019  Gestão ambiental  ...     843.027,57     770.291,75\n",
              "\n",
              "[5 rows x 12 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 29
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "An8hdG961Cdd",
        "colab_type": "text"
      },
      "source": [
        "Exercicio 2.1 - Agrupar empenhos por programa e função"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zlZXegqar0uT",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 359
        },
        "outputId": "83427491-1975-47e0-b4b9-4daae03139b8"
      },
      "source": [
        "def limpa_empenho(linha):\n",
        "  \"\"\"retira os pontos e coloca vírgula no lugar de ponto para posterior conversão em número\"\"\"\n",
        "  valor = linha['empenhado']\n",
        "  valor = valor.replace('.', '')\n",
        "  valor = valor.replace(',', '.')\n",
        "  return valor\n",
        "\n",
        "df_funcional['valor_empenhado'] = df_funcional.apply(limpa_empenho, axis =1)\n",
        "\n",
        "df_funcional['valor_empenhado'] = df_funcional.valor_empenhado.astype('float64')  \n",
        "\n",
        "df_por_programa = pd.pivot_table(df_funcional, \n",
        "                              values='valor_empenhado',\n",
        "                              index=['programa'],\n",
        "                              columns='funcao',\n",
        "                              aggfunc=np.sum)\n",
        "                          \n",
        "df_por_programa"
      ],
      "execution_count": 46,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>funcao</th>\n",
              "      <th>Ciência e Tecnologia</th>\n",
              "      <th>Defesa nacional</th>\n",
              "      <th>Energia</th>\n",
              "      <th>Gestão ambiental</th>\n",
              "      <th>Transporte</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>programa</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>CONSERVACAO E USO SUSTENTAVEL DA BIODIVERSIDADE</th>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>14712569.18</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>DEFESA NACIONAL</th>\n",
              "      <td>NaN</td>\n",
              "      <td>62623616.9</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>FLORESTAS, PREVENCAO E CONTROLE DO DESMATAMENTO E DOS INCEND</th>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.00</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>MUDANCA DO CLIMA</th>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>73837753.71</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>PETROLEO E GAS</th>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1123316.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>POLITICA NUCLEAR</th>\n",
              "      <td>0.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>PROGRAMA DE GESTAO E MANUTENCAO DO MINISTERIO DA INFRAESTRUT</th>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>QUALIDADE AMBIENTAL</th>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>11141566.63</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>RESÍDUOS SÓLIDOS URBANOS</th>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.00</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "funcao                                              Ciência e Tecnologia  ...  Transporte\n",
              "programa                                                                  ...            \n",
              "CONSERVACAO E USO SUSTENTAVEL DA BIODIVERSIDADE                      NaN  ...         NaN\n",
              "DEFESA NACIONAL                                                      NaN  ...         NaN\n",
              "FLORESTAS, PREVENCAO E CONTROLE DO DESMATAMENTO...                   NaN  ...         NaN\n",
              "MUDANCA DO CLIMA                                                     NaN  ...         NaN\n",
              "PETROLEO E GAS                                                       NaN  ...         NaN\n",
              "POLITICA NUCLEAR                                                     0.0  ...         NaN\n",
              "PROGRAMA DE GESTAO E MANUTENCAO DO MINISTERIO D...                   NaN  ...         0.0\n",
              "QUALIDADE AMBIENTAL                                                  NaN  ...         NaN\n",
              "RESÍDUOS SÓLIDOS URBANOS                                             NaN  ...         NaN\n",
              "\n",
              "[9 rows x 5 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 46
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pj31wVwn2Lgw",
        "colab_type": "text"
      },
      "source": [
        "Exercicio 2.2 - Ranking de ações dentro de controle ambiental, empenhado x liquidado"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9PVrms1C2tIz",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 672
        },
        "outputId": "6005c8a2-4c9e-4c0a-c7f3-afe128b23189"
      },
      "source": [
        "def limpa_liquidado(linha):\n",
        "  \"\"\"retira os pontos e coloca vírgula no lugar de ponto para posterior conversão em número\"\"\"\n",
        "  valor = linha['liquidado']\n",
        "  valor = valor.replace('.', '')\n",
        "  valor = valor.replace(',', '.')\n",
        "  return valor\n",
        "\n",
        "df_funcional['valor_liquidado'] = df_funcional.apply(limpa_liquidado, axis =1)\n",
        "\n",
        "df_funcional['valor_liquidado'] = df_funcional.valor_liquidado.astype('float64') \n",
        "\n",
        "df_por_acao = pd.pivot_table(df_funcional, \n",
        "                              values=['valor_empenhado', 'valor_liquidado'],\n",
        "                              index=['acao'],\n",
        "                              aggfunc=np.sum).sort_values(by='valor_liquidado', ascending=False)\n",
        "                            \n",
        "df_por_acao"
      ],
      "execution_count": 48,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>valor_empenhado</th>\n",
              "      <th>valor_liquidado</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>acao</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>PREVENCAO E CONTROLE DE INCENDIOS FLORESTAIS NAS AREAS FEDERAIS PRIORITARIAS</th>\n",
              "      <td>73179868.28</td>\n",
              "      <td>57740662.10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>MANUTENCAO DO SISTEMA DE PROTECAO DA AMAZONIA - SIPAM</th>\n",
              "      <td>59886888.18</td>\n",
              "      <td>33693359.54</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>GESTAO DO USO SUSTENTAVEL DA BIODIVERSIDADE</th>\n",
              "      <td>14712569.18</td>\n",
              "      <td>8607894.55</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>LICENCIAMENTO AMBIENTAL FEDERAL</th>\n",
              "      <td>4604677.75</td>\n",
              "      <td>2373900.40</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>MONITORAMENTO AMBIENTAL E GESTAO DA INFORMACAO SOBRE O MEIO AMBIENTE E EDUCACAO AMBIENTAL</th>\n",
              "      <td>4326970.48</td>\n",
              "      <td>1957206.86</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>AVALIACAO DE PERICULOSIDADE E CONTROLE DE PRODUTOS, SUBSTANCIAS QUIMICAS E RESIDUOS PERIGOSOS</th>\n",
              "      <td>1516189.89</td>\n",
              "      <td>843027.57</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>IMPLANTACAO DO SISTEMA AMAZONIA - SAR</th>\n",
              "      <td>2736728.72</td>\n",
              "      <td>596728.72</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>ESTUDOS AMBIENTAIS DE AREAS SEDIMENTARES</th>\n",
              "      <td>1123316.00</td>\n",
              "      <td>547757.74</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>APOIO A IMPLEMENTACAO DE INSTRUMENTOS ESTRUTURANTES DA POLITICA NACIONAL DE RESIDUOS SOLIDOS</th>\n",
              "      <td>572723.67</td>\n",
              "      <td>239930.96</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>INICIATIVAS PARA IMPLEMENTACAO DA POLITICA NACIONAL SOBRE MUDANCA DO CLIMA</th>\n",
              "      <td>394686.07</td>\n",
              "      <td>120686.07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>FOMENTO PARA A MELHORIA DA QUALIDADE AMBIENTAL</th>\n",
              "      <td>87993.58</td>\n",
              "      <td>71197.21</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>POLITICAS E ESTRATEGIAS DE PREVENCAO E CONTROLE DO DESMATAMENTO E DE MANEJO E RECUPERACAO FLORESTAL NO AMBITO DA UNIAO, ESTADOS E MUNICIPIOS</th>\n",
              "      <td>263199.36</td>\n",
              "      <td>15431.34</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>GESTAO AMBIENTALMENTE ADEQUADA DE SUBSTANCIAS QUIMICAS</th>\n",
              "      <td>33011.26</td>\n",
              "      <td>7547.66</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>PREVENCAO E COMBATE A INCENDIOS FLORESTAIS EM UNIDADES DE CONSERVACAO FEDERAIS</th>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>IMPLEMENTAÇÃO DA POLÍTICA NACIONAL DE RESÍDUOS SÓLIDOS</th>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>ESTRUTURACAO DA GESTAO NACIONAL DOS RECURSOS FAUNISTICOS</th>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>ELABORAÇÃO E EXECUÇÃO DE ESTUDOS DE IMPACTO AMBIENTAL E PLANOS BÁSICOS AMBIENTAIS PARA PROJETOS DE INFRAESTRUTURA DE TRANSPORTES</th>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>PROTECAO RADIOLOGICA</th>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                                    valor_empenhado  valor_liquidado\n",
              "acao                                                                                \n",
              "PREVENCAO E CONTROLE DE INCENDIOS FLORESTAIS NA...      73179868.28      57740662.10\n",
              "MANUTENCAO DO SISTEMA DE PROTECAO DA AMAZONIA -...      59886888.18      33693359.54\n",
              "GESTAO DO USO SUSTENTAVEL DA BIODIVERSIDADE             14712569.18       8607894.55\n",
              "LICENCIAMENTO AMBIENTAL FEDERAL                          4604677.75       2373900.40\n",
              "MONITORAMENTO AMBIENTAL E GESTAO DA INFORMACAO ...       4326970.48       1957206.86\n",
              "AVALIACAO DE PERICULOSIDADE E CONTROLE DE PRODU...       1516189.89        843027.57\n",
              "IMPLANTACAO DO SISTEMA AMAZONIA - SAR                    2736728.72        596728.72\n",
              "ESTUDOS AMBIENTAIS DE AREAS SEDIMENTARES                 1123316.00        547757.74\n",
              "APOIO A IMPLEMENTACAO DE INSTRUMENTOS ESTRUTURA...        572723.67        239930.96\n",
              "INICIATIVAS PARA IMPLEMENTACAO DA POLITICA NACI...        394686.07        120686.07\n",
              "FOMENTO PARA A MELHORIA DA QUALIDADE AMBIENTAL             87993.58         71197.21\n",
              "POLITICAS E ESTRATEGIAS DE PREVENCAO E CONTROLE...        263199.36         15431.34\n",
              "GESTAO AMBIENTALMENTE ADEQUADA DE SUBSTANCIAS Q...         33011.26          7547.66\n",
              "PREVENCAO E COMBATE A INCENDIOS FLORESTAIS EM U...             0.00             0.00\n",
              "IMPLEMENTAÇÃO DA POLÍTICA NACIONAL DE RESÍDUOS ...             0.00             0.00\n",
              "ESTRUTURACAO DA GESTAO NACIONAL DOS RECURSOS FA...             0.00             0.00\n",
              "ELABORAÇÃO E EXECUÇÃO DE ESTUDOS DE IMPACTO AMB...             0.00             0.00\n",
              "PROTECAO RADIOLOGICA                                           0.00             0.00"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 48
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yWt10w_k4ETv",
        "colab_type": "text"
      },
      "source": [
        "### Contratos\n",
        "\n",
        "Exercicio 3. Verificar contratos do Ministério do Meio Ambiente\n",
        "\n",
        "Primeiro, explorar campos de contrato no Swagger e gerar URL:\n",
        "\n",
        "![alt text](http://umdadoamais.com/wp-content/uploads/2019/11/tutorial_orcamento8.png)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7tFlAfUr393N",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#parâmatros para a iteração\n",
        "dia_inicio = '01'\n",
        "mes_inicio = '01'\n",
        "dia_fim = '31'\n",
        "mes_fim = '12'\n",
        "ano = 2018\n",
        "page = 1"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yAzWc9hv6GMV",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 119
        },
        "outputId": "4dacdaad-c3e9-4c65-b42c-f70cddabbc53"
      },
      "source": [
        "url = f'http://www.transparencia.gov.br/api-de-dados/contratos?dataInicial={dia_inicio}%2F{mes_inicio}%2F{ano}&dataFinal={dia_fim}%2F{mes_fim}%2F2019&codigoOrgao=44000'\n",
        "\n",
        "r_contratos = requests.get(url+f'&pagina={page}',\n",
        "                           verify=True).json()\n",
        "\n",
        "page = 1\n",
        "todas_pag = []\n",
        "todas_pag = todas_pag + r_contratos\n",
        "tamanho = int(requests.get(url+f'&pagina={page}').headers['Content-Length'])\n",
        "#verifica tamanho da resposta; 22 é padrão da resposta vazia, ou seja, não há conteúdo nessa página\n",
        "\n",
        "while(tamanho>22):\n",
        "    page = page +1\n",
        "    print(page)  \n",
        "    r_contratos = requests.get(url+f'&pagina={page}').json() \n",
        "    print(url+f'&pagina={page}')\n",
        "    todas_pag = todas_pag + r_contratos\n",
        "    tamanho = int(requests.get(url+f'&pagina={page}').headers['Content-Length'])\n",
        "    print(tamanho)\n"
      ],
      "execution_count": 14,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "2\n",
            "http://www.transparencia.gov.br/api-de-dados/contratos?dataInicial=01%2F01%2F2018&dataFinal=31%2F12%2F2019&codigoOrgao=44000&pagina=2\n",
            "3265\n",
            "3\n",
            "http://www.transparencia.gov.br/api-de-dados/contratos?dataInicial=01%2F01%2F2018&dataFinal=31%2F12%2F2019&codigoOrgao=44000&pagina=3\n",
            "22\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1qxskuKLB5KS",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 564
        },
        "outputId": "47f24035-0421-4abb-b702-bb0ba7ccba60"
      },
      "source": [
        "df_contratos = pd.DataFrame(todas_pag)\n",
        "df_contratos.head()"
      ],
      "execution_count": 15,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>id</th>\n",
              "      <th>dimCompra</th>\n",
              "      <th>situacaoContrato</th>\n",
              "      <th>modalidadeCompra</th>\n",
              "      <th>unidadeGestora</th>\n",
              "      <th>unidadeGestoraCompras</th>\n",
              "      <th>dataAssinatura</th>\n",
              "      <th>dataPublicacaoDOU</th>\n",
              "      <th>dataInicioVigencia</th>\n",
              "      <th>dataFimVigencia</th>\n",
              "      <th>fornecedor</th>\n",
              "      <th>valorInicialCompra</th>\n",
              "      <th>valorFinalCompra</th>\n",
              "      <th>contrato</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>38464503</td>\n",
              "      <td>{'numero': '000112018', 'objeto': 'Objeto: Pre...</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>{'codigo': '440001', 'nome': 'SUBSECRET.DE PLA...</td>\n",
              "      <td>None</td>\n",
              "      <td>18/12/2018</td>\n",
              "      <td>20/12/2018</td>\n",
              "      <td>18/12/2018</td>\n",
              "      <td>17/12/2019</td>\n",
              "      <td>{'numeroInscricaoSocial': '', 'nome': 'DELL CO...</td>\n",
              "      <td>2496600.0</td>\n",
              "      <td>2496600.0</td>\n",
              "      <td>{'numero': '222018', 'objeto': 'Objeto: Aquisi...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>40464821</td>\n",
              "      <td>{'numero': '000022018', 'objeto': 'Objeto: Pre...</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>{'codigo': '440001', 'nome': 'SUBSECRET.DE PLA...</td>\n",
              "      <td>None</td>\n",
              "      <td>21/05/2018</td>\n",
              "      <td>22/05/2018</td>\n",
              "      <td>21/05/2018</td>\n",
              "      <td>20/05/2019</td>\n",
              "      <td>{'numeroInscricaoSocial': '', 'nome': 'GAP SER...</td>\n",
              "      <td>378945.0</td>\n",
              "      <td>378945.0</td>\n",
              "      <td>{'numero': '72018', 'objeto': 'Objeto: Contrat...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>40665800</td>\n",
              "      <td>{'numero': '000062018', 'objeto': 'Objeto: Pre...</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>{'codigo': '440001', 'nome': 'SUBSECRET.DE PLA...</td>\n",
              "      <td>None</td>\n",
              "      <td>17/12/2018</td>\n",
              "      <td>18/12/2018</td>\n",
              "      <td>17/12/2018</td>\n",
              "      <td>16/12/2019</td>\n",
              "      <td>{'numeroInscricaoSocial': '', 'nome': 'BLUE EY...</td>\n",
              "      <td>560195.0</td>\n",
              "      <td>560195.0</td>\n",
              "      <td>{'numero': '212018', 'objeto': 'Objeto: Serviç...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>40863029</td>\n",
              "      <td>{'numero': '000062018', 'objeto': 'Objeto: Pre...</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>{'codigo': '440001', 'nome': 'SUBSECRET.DE PLA...</td>\n",
              "      <td>None</td>\n",
              "      <td>12/12/2018</td>\n",
              "      <td>13/12/2018</td>\n",
              "      <td>12/12/2018</td>\n",
              "      <td>11/12/2019</td>\n",
              "      <td>{'numeroInscricaoSocial': '', 'nome': 'BLUE EY...</td>\n",
              "      <td>598965.0</td>\n",
              "      <td>598965.0</td>\n",
              "      <td>{'numero': '202018', 'objeto': 'Objeto: Serviç...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>40963966</td>\n",
              "      <td>{'numero': '000032017', 'objeto': 'Objeto: Pre...</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>{'codigo': '440001', 'nome': 'SUBSECRET.DE PLA...</td>\n",
              "      <td>None</td>\n",
              "      <td>21/05/2018</td>\n",
              "      <td>23/05/2018</td>\n",
              "      <td>21/05/2018</td>\n",
              "      <td>20/05/2019</td>\n",
              "      <td>{'numeroInscricaoSocial': '', 'nome': 'TECNO -...</td>\n",
              "      <td>393752.0</td>\n",
              "      <td>393752.0</td>\n",
              "      <td>{'numero': '82018', 'objeto': 'Objeto: Contrat...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "         id  ...                                           contrato\n",
              "0  38464503  ...  {'numero': '222018', 'objeto': 'Objeto: Aquisi...\n",
              "1  40464821  ...  {'numero': '72018', 'objeto': 'Objeto: Contrat...\n",
              "2  40665800  ...  {'numero': '212018', 'objeto': 'Objeto: Serviç...\n",
              "3  40863029  ...  {'numero': '202018', 'objeto': 'Objeto: Serviç...\n",
              "4  40963966  ...  {'numero': '82018', 'objeto': 'Objeto: Contrat...\n",
              "\n",
              "[5 rows x 14 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vWb-8Zm-RJ52",
        "colab_type": "text"
      },
      "source": [
        "Mas... o que é esse formato estranho? "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MxNsIqnrRXAF",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 105
        },
        "outputId": "74595196-8f8e-4502-a80c-d215e3893142"
      },
      "source": [
        "df_contratos.dimCompra[0]"
      ],
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'contatoResponsavel': 'PAULO SERGIO ALBERTO',\n",
              " 'numero': '000112018',\n",
              " 'numeroProcesso': '12804100067201812',\n",
              " 'objeto': 'Objeto: Pregão Eletrônico -  Aquisição de ESTAÇÕES DE TRABALHO (DESKTOPS), conforme condições, quantidades e exigências estabelecidas no Edital e seus anexos.'}"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 16
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NXHFRGT9RrUR",
        "colab_type": "text"
      },
      "source": [
        "Nosso tempo acabou... é assunto para outro capítulo!"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "H8LdBDSwivIU",
        "colab_type": "text"
      },
      "source": [
        "## 4. Mais referências\n",
        "\n",
        "* **Documentação técnica** e bastante detalhada sobre o orçamento federal é frequentemente atualizada pelo governo \n",
        " * Acesse o [Manual Técnico do Orçamento Federal](https://www1.siop.planejamento.gov.br/mto/lib/exe/fetch.php/mto2020:mto2020-versao5.pdf) - MTO 2020\n",
        "\n",
        "* **A Prefeitura de São Paulo** também tem uma API de Despesas e Contratos - e, neste caso, os dados não estão disponíveis para download. Para conhecer detalhes da execução orçamentária, a única maneira, por ora, é usar a API. \n",
        "\n",
        " * Acesse a [API do SOF](https://api.prodam.sp.gov.br/store/apis/info?name=sof&version=v2.1.0&provider=admin)\n",
        " * [Publicação da Secretaria de Inovação e Tecnologia da PMSP ](https://copicola.prefeitura.sp.gov.br/docs/8_guia_dadosabertos.pdf)que sistematiza a história de construção da API\n",
        " * [Tutorial que fiz ](https://github.com/campagnucci/api_sof) para download de dados na API do SOF. \n",
        "\n",
        "* **Material do INESC** que explica o orçamento e sua relação com direitos humanos\n",
        "\n",
        " * Faça download da [Cartilha Orçamento e Direitos](https://www.inesc.org.br/orcamento-direitos-2018-2/) (2018) "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "W33TEWfbozbu",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}