{
 "metadata": {
  "name": "",
  "signature": "sha256:3dac15dfe811bd1bc0efb973fb60c6a3a572af2008b5645bf14737bc61cfb9ce"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "from collections import defaultdict"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "An\u00e1lise dos dados de Planejamento"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Peguei as tr\u00eas planilhas relativas ao planejamento das p\u00e1ginas:\n",
      "\n",
      "* LOA (Or\u00e7amento Aprovado) - http://sempla.prefeitura.sp.gov.br/orcamento/loa.html\n",
      "* Proposta Or\u00e7ament\u00e1ria (Projeto de Lei e quadro detalhado) - http://sempla.prefeitura.sp.gov.br/orcamento/proposta.html\n"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!pip install xlrd"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Downloading/unpacking xlrd\r\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "  Downloading xlrd-0.9.3.tar.gz (178kB): \r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):   2%  4.1kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):   4%  8.2kB"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):   6%  12kB \r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):   9%  16kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  11%  20kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  13%  24kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  16%  28kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  18%  32kB"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  20%  36kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  22%  40kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  25%  45kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  27%  49kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  29%  53kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  32%  57kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  34%  61kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  36%  65kB"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  39%  69kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  41%  73kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  43%  77kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  45%  81kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  48%  86kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  50%  90kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  52%  94kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  55%  98kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  57%  102kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  59%  106kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  61%  110kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  64%  114kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  66%  118kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  68%  122kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  71%  126kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  73%  131kB"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  75%  135kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  78%  139kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  80%  143kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  82%  147kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  84%  151kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  87%  155kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  89%  159kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  91%  163kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  94%  167kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  96%  172kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):  98%  176kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB): 100%  178kB\r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB):            \r",
        "  Downloading xlrd-0.9.3.tar.gz (178kB): 178kB downloaded\r\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "  Running setup.py (path:/home/everton/.virtualenvs/ga/build/xlrd/setup.py) egg_info for package xlrd\r\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "    \r\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Installing collected packages: xlrd\r\n",
        "  Running setup.py install for xlrd\r\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "    changing mode of build/scripts-2.7/runxlrd.py from 664 to 775\r\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "    \r\n",
        "    changing mode of /home/everton/.virtualenvs/ga/bin/runxlrd.py to 775\r\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Successfully installed xlrd\r\n",
        "Cleaning up...\r\n"
       ]
      }
     ],
     "prompt_number": 4
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "projeto_de_lei = pd.read_excel('../Orcamento/2015/Proposta/PLOA467BaseDados.xls')\n",
      "quadro_detalhado = pd.read_excel('../Orcamento/2015/Proposta/PLOA467BaseDadosQuadroDetalhadoDaAcao.xls')\n",
      "loa = pd.read_excel('../Orcamento/2014/LOA/OrcamentoAprovado2014.xls')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 6
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## Comparando Projeto de Lei e Quadro Detalhado\n",
      "\n",
      "A primeira coisa que d\u00e1 pra notar \u00e9 que ambos possuem v\u00e1rias colunas diferentes:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "quadro_detalhado.columns - projeto_de_lei.columns"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stderr",
       "text": [
        "/home/everton/.virtualenvs/ga/local/lib/python2.7/site-packages/pandas-0.15.2-py2.7-linux-x86_64.egg/pandas/core/index.py:1172: FutureWarning: using '-' to provide set differences with Indexes is deprecated, use .difference()\n",
        "  \"use .difference()\",FutureWarning)\n"
       ]
      },
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 7,
       "text": [
        "Index([u'ANO_EX', u'COD_COMPLT_DA', u'COD_DA', u'COD_DISTRITO', u'COD_EMP', u'COD_META', u'COD_REGIAO', u'COD_SUBPREFEITURA', u'DESC_DA', u'DESC_DISTRITO', u'DESC_META', u'DESC_ORGAO', u'DESC_PA', u'DESC_REGIAO', u'DESC_SUBPREFEITURA', u'DESC_UNIDADE', u'NOME_EMP', u'ORGAO', u'UNIDADE', u'VALOR_DA'], dtype='object')"
       ]
      }
     ],
     "prompt_number": 7
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "projeto_de_lei.columns - quadro_detalhado.columns"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 8,
       "text": [
        "Index([u'Administracao', u'Categoria_Despesa', u'Cd_Despesa', u'Cd_Dotac_Id', u'Cd_Elemento', u'Cd_Exerc_Empresa_Id', u'Cd_Exercicio', u'Cd_Fonte', u'Cd_Funcao', u'Cd_Modalidade', u'Cd_Orgao', u'Cd_Programa', u'Cd_Projeto_Atividade', u'Cd_SubFuncao', u'Cd_Unidade', u'DataExtracao', u'Ds_Categoria', u'Ds_Despesa', u'Ds_Fonte', u'Ds_Funcao', u'Ds_Grupo', u'Ds_Modalidade', u'Ds_Orgao', u'Ds_Programa', u'Ds_Projeto_Atividade', u'Ds_SubFuncao', u'Ds_Unidade', u'Grupo_Despesa', u'PAPA', u'Poder', u'ProjetoAtividade', u'Sld_Orcado_Ano', u'Tp_Projeto_Atividade'], dtype='object')"
       ]
      }
     ],
     "prompt_number": 8
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Outra quest\u00e3o \u00e9 a diferen\u00e7a no n\u00famero de linhas de cada planilha:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print(\"PLOA467BaseDados.xls => {} linhas\".format(len(projeto_de_lei)))\n",
      "print(\"PLOA467BaseDadosQuadroDetalhadoDaAcao.xls => {} linhas\".format(len(quadro_detalhado)))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "PLOA467BaseDados.xls => 2799 linhas\n",
        "PLOA467BaseDadosQuadroDetalhadoDaAcao.xls => 1784 linhas\n"
       ]
      }
     ],
     "prompt_number": 9
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Apesar do quadro detalhado possuir mais de 1000 linhas a menos, ele possui informa\u00e7\u00f5es mais espec\u00edficas em termos regionais. \n",
      "Por exemplo, na planilha \"PLOA467BaseDados.xls\" existem apenas 4 linhas referentes a constru\u00e7\u00e3o de CEI's em 2015, mas o arquivo do quadro detalhado \"PLOA467BaseDadosQuadroDetalhadoDaAcao.xls\" possui 89.\n",
      "\n",
      "O melhor desse exemplo \u00e9 que no quadro detalhado possui o quanto est\u00e1 indo pra cada CEI em uma determinada subprefeitura. Ach\u00e1vamos antes que esse tipo de informa\u00e7\u00e3o n\u00e3o existia, mas pelo que vi agora talvez o problema n\u00e3o seja t\u00e3o grave.\n",
      "\n",
      "Mas ainda precisamos descobrir porque existem menos linhas no quadro detalhado."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "total_cei = projeto_de_lei[projeto_de_lei['ProjetoAtividade'] == 3359]['Sld_Orcado_Ano'].sum()\n",
      "print(\"R$ {:,.2f}\".format(total_cei))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "R$ 231,586,768.00\n"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "\"{} linhas sobre o CEI\".format(len(projeto_de_lei[projeto_de_lei['ProjetoAtividade'] == 3359]))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 60,
       "text": [
        "'4 linhas sobre o CEI'"
       ]
      }
     ],
     "prompt_number": 60
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "total_detalhado_cei = quadro_detalhado[quadro_detalhado['PA'] == 3359]['VALOR_DA'].sum()\n",
      "print(\"R$ {:,.2f}\".format(total_detalhado_cei))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "R$ 231,586,768.00\n"
       ]
      }
     ],
     "prompt_number": 12
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "projeto_de_lei[projeto_de_lei['ProjetoAtividade'] == 3359]['Ds_Projeto_Atividade']"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 69,
       "text": [
        "432    Constru\u00e7\u00e3o de Centros de Educa\u00e7\u00e3o Infantil - CEI\n",
        "433    Constru\u00e7\u00e3o de Centros de Educa\u00e7\u00e3o Infantil - CEI\n",
        "434    Constru\u00e7\u00e3o de Centros de Educa\u00e7\u00e3o Infantil - CEI\n",
        "435    Constru\u00e7\u00e3o de Centros de Educa\u00e7\u00e3o Infantil - CEI\n",
        "Name: Ds_Projeto_Atividade, dtype: object"
       ]
      }
     ],
     "prompt_number": 69
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "loa[loa['ProjetoAtividade'] == 3359].groupby('Ds_Orgao')['Sld_Orcado_Ano'].sum()\n",
      "loa.columns"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 94,
       "text": [
        "Index([u'Cd_Exercicio', u'Administracao', u'Cd_Orgao', u'Ds_Orgao', u'Cd_Unidade', u'Ds_Unidade', u'Cd_Funcao', u'Ds_Funcao', u'Cd_SubFuncao', u'Ds_SubFuncao', u'Cd_Programa', u'Ds_Programa', u'PAPA', u'ProjetoAtividade', u'Ds_Projeto_Atividade', u'Cd_Despesa', u'Ds_Despesa', u'Categoria_Despesa', u'Ds_Categoria', u'Grupo_Despesa', u'Ds_Grupo', u'Cd_Modalidade', u'Ds_Modalidade', u'Cd_Elemento', u'Cd_Fonte', u'Ds_Fonte', u'Sld_Orcado_Ano', u'DataExtracao'], dtype='object')"
       ]
      }
     ],
     "prompt_number": 94
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "quadro_detalhado[quadro_detalhado['PA'] == 3359].groupby('DESC_SUBPREFEITURA')['VALOR_DA'].sum()\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 93,
       "text": [
        "DESC_SUBPREFEITURA\n",
        "A Definir                                  60763344\n",
        "Subprefeitura Aricanduva/Formosa/Carr\u00e3o     2288784\n",
        "Subprefeitura Butant\u00e3                      14306272\n",
        "Subprefeitura Campo Limpo                  14599056\n",
        "Subprefeitura Capela do Socorro            27331760\n",
        "Subprefeitura Casa Verde/Cachoeirinha       1288784\n",
        "Subprefeitura Cidade Ademar                 3577568\n",
        "Subprefeitura Cidade Tiradentes             1288784\n",
        "Subprefeitura Ermelino Matarazzo            2288784\n",
        "Subprefeitura Freguesia/Brasil\u00e2ndia         2288784\n",
        "Subprefeitura Guaianases                    2288784\n",
        "Subprefeitura Ipiranga                      5866352\n",
        "Subprefeitura Itaim Paulista                9151136\n",
        "Subprefeitura Itaquera                     15021488\n",
        "Subprefeitura Ja\u00e7an\u00e3/Trememb\u00e9              12021488\n",
        "Subprefeitura Mo\u00f3ca                         2288784\n",
        "Subprefeitura M\u00b4Boi Mirim                  10021488\n",
        "Subprefeitura Parelheiros                   4577568\n",
        "Subprefeitura Penha                         1288784\n",
        "Subprefeitura Perus                         4577568\n",
        "Subprefeitura Pirituba/Jaragu\u00e1             11155136\n",
        "Subprefeitura S\u00e3o Mateus                   12443920\n",
        "Subprefeitura S\u00e3o Miguel                   10862352\n",
        "Name: VALOR_DA, dtype: float64"
       ]
      }
     ],
     "prompt_number": 93
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Histograma de atividades"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cod_atividade = set(projeto_de_lei['ProjetoAtividade'])\n",
      "cod_atividade_detalhado = set(quadro_detalhado['PA'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 14
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "O n\u00famero de atividade em cada planilha n\u00e3o difere em quantidade:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "len(cod_atividade - cod_atividade_detalhado)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 15,
       "text": [
        "0"
       ]
      }
     ],
     "prompt_number": 15
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "len(cod_atividade), len(cod_atividade_detalhado)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 16,
       "text": [
        "(313, 313)"
       ]
      }
     ],
     "prompt_number": 16
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "hist_atividades = defaultdict(int)\n",
      "\n",
      "for pa in cod_atividade:\n",
      "    for l in projeto_de_lei[projeto_de_lei['ProjetoAtividade'] == pa].iterrows():\n",
      "        hist_atividades[pa] += 1"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 17
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "hist_atividades_detalhado = defaultdict(int)\n",
      "\n",
      "for pa in cod_atividade_detalhado:\n",
      "    for l in quadro_detalhado[quadro_detalhado['PA'] == pa].iterrows():\n",
      "        hist_atividades_detalhado[pa] += 1"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 18
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "No entanto diferem em detalhamento. Como citei no caso do CEI acima, no quadro detalhado existe o detalhamento por subprefeitur, enquanto na planilha do projeto de lei exixte apenas entradas totalizadoras. No entanto se formos comparar os histogramas abaixo veremos que em certos casos as atividades est\u00e3o mais detalhas no arquivo do projeto de lei:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "diff_hists = {pa: hist_atividades_detalhado[pa] - hist_atividades[pa] for pa in cod_atividade}\n",
      "\n",
      "print(\"{} atividade mais detalhas em PLOA467BaseDados.xls\".format(len(diff_hists.values()) - len(filter(lambda x: x < 0, diff_hists.values()))))\n",
      "print(\"{} atividade com mesmo n\u00famero de linhas em ambas planilhas\".format(len(diff_hists.values()) - len(filter(lambda x: x == 0, diff_hists.values()))))\n",
      "print(\"{} atividade mais detalhas em PLOA467BaseDadosQuadroDetalhadoDaAcao.xls\".format(len(diff_hists.values()) - len(filter(lambda x: x > 0, diff_hists.values()))))\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "150 atividade mais detalhas em PLOA467BaseDados.xls\n",
        "209 atividade com mesmo n\u00famero de linhas em ambas planilhas\n",
        "267 atividade mais detalhas em PLOA467BaseDadosQuadroDetalhadoDaAcao.xls\n"
       ]
      }
     ],
     "prompt_number": 19
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Exemplo de atividad emais detalhada na planilha PLOA467BaseDados.xls"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "projeto_de_lei[projeto_de_lei['ProjetoAtividade'] == 4]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>Cd_Dotac_Id</th>\n",
        "      <th>Cd_Exerc_Empresa_Id</th>\n",
        "      <th>Cd_Exercicio</th>\n",
        "      <th>Poder</th>\n",
        "      <th>Administracao</th>\n",
        "      <th>Cd_Orgao</th>\n",
        "      <th>Ds_Orgao</th>\n",
        "      <th>Cd_Unidade</th>\n",
        "      <th>Ds_Unidade</th>\n",
        "      <th>Cd_Funcao</th>\n",
        "      <th>...</th>\n",
        "      <th>Ds_Categoria</th>\n",
        "      <th>Grupo_Despesa</th>\n",
        "      <th>Ds_Grupo</th>\n",
        "      <th>Cd_Modalidade</th>\n",
        "      <th>Ds_Modalidade</th>\n",
        "      <th>Cd_Elemento</th>\n",
        "      <th>Cd_Fonte</th>\n",
        "      <th>Ds_Fonte</th>\n",
        "      <th>Sld_Orcado_Ano</th>\n",
        "      <th>DataExtracao</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>1197</th>\n",
        "      <td> 101547</td>\n",
        "      <td> 333</td>\n",
        "      <td> 2015</td>\n",
        "      <td>NaN</td>\n",
        "      <td> Administra\u00e7\u00e3o Direta</td>\n",
        "      <td> 28</td>\n",
        "      <td> Encargos Gerais do Munic\u00edpio</td>\n",
        "      <td> 17</td>\n",
        "      <td> Recursos Superv. pela Secretaria Municipal de ...</td>\n",
        "      <td> 28</td>\n",
        "      <td>...</td>\n",
        "      <td>  Despesas Correntes</td>\n",
        "      <td> 2</td>\n",
        "      <td> JUROS E ENCARGOS DA DIVIDA</td>\n",
        "      <td> 90</td>\n",
        "      <td> Aplica\u00e7\u00f5es Diretas</td>\n",
        "      <td> 21</td>\n",
        "      <td> 0</td>\n",
        "      <td> Tesouro Municipal</td>\n",
        "      <td>  67868404</td>\n",
        "      <td>2014-09-29 18:02:01</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1198</th>\n",
        "      <td> 101548</td>\n",
        "      <td> 333</td>\n",
        "      <td> 2015</td>\n",
        "      <td>NaN</td>\n",
        "      <td> Administra\u00e7\u00e3o Direta</td>\n",
        "      <td> 28</td>\n",
        "      <td> Encargos Gerais do Munic\u00edpio</td>\n",
        "      <td> 17</td>\n",
        "      <td> Recursos Superv. pela Secretaria Municipal de ...</td>\n",
        "      <td> 28</td>\n",
        "      <td>...</td>\n",
        "      <td>  Despesas Correntes</td>\n",
        "      <td> 2</td>\n",
        "      <td> JUROS E ENCARGOS DA DIVIDA</td>\n",
        "      <td> 90</td>\n",
        "      <td> Aplica\u00e7\u00f5es Diretas</td>\n",
        "      <td> 22</td>\n",
        "      <td> 0</td>\n",
        "      <td> Tesouro Municipal</td>\n",
        "      <td>   2134045</td>\n",
        "      <td>2014-09-29 18:02:01</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1199</th>\n",
        "      <td> 101549</td>\n",
        "      <td> 333</td>\n",
        "      <td> 2015</td>\n",
        "      <td>NaN</td>\n",
        "      <td> Administra\u00e7\u00e3o Direta</td>\n",
        "      <td> 28</td>\n",
        "      <td> Encargos Gerais do Munic\u00edpio</td>\n",
        "      <td> 17</td>\n",
        "      <td> Recursos Superv. pela Secretaria Municipal de ...</td>\n",
        "      <td> 28</td>\n",
        "      <td>...</td>\n",
        "      <td> Despesas de Capital</td>\n",
        "      <td> 6</td>\n",
        "      <td>      AMORTIZACAO DA DIVIDA</td>\n",
        "      <td> 90</td>\n",
        "      <td> Aplica\u00e7\u00f5es Diretas</td>\n",
        "      <td> 71</td>\n",
        "      <td> 0</td>\n",
        "      <td> Tesouro Municipal</td>\n",
        "      <td> 280584141</td>\n",
        "      <td>2014-09-29 18:02:01</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>3 rows \u00d7 34 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 20,
       "text": [
        "      Cd_Dotac_Id  Cd_Exerc_Empresa_Id  Cd_Exercicio  Poder  \\\n",
        "1197       101547                  333          2015    NaN   \n",
        "1198       101548                  333          2015    NaN   \n",
        "1199       101549                  333          2015    NaN   \n",
        "\n",
        "             Administracao  Cd_Orgao                      Ds_Orgao  \\\n",
        "1197  Administra\u00e7\u00e3o Direta        28  Encargos Gerais do Munic\u00edpio   \n",
        "1198  Administra\u00e7\u00e3o Direta        28  Encargos Gerais do Munic\u00edpio   \n",
        "1199  Administra\u00e7\u00e3o Direta        28  Encargos Gerais do Munic\u00edpio   \n",
        "\n",
        "      Cd_Unidade                                         Ds_Unidade  \\\n",
        "1197          17  Recursos Superv. pela Secretaria Municipal de ...   \n",
        "1198          17  Recursos Superv. pela Secretaria Municipal de ...   \n",
        "1199          17  Recursos Superv. pela Secretaria Municipal de ...   \n",
        "\n",
        "      Cd_Funcao         ...                 Ds_Categoria  Grupo_Despesa  \\\n",
        "1197         28         ...           Despesas Correntes              2   \n",
        "1198         28         ...           Despesas Correntes              2   \n",
        "1199         28         ...          Despesas de Capital              6   \n",
        "\n",
        "                        Ds_Grupo  Cd_Modalidade       Ds_Modalidade  \\\n",
        "1197  JUROS E ENCARGOS DA DIVIDA             90  Aplica\u00e7\u00f5es Diretas   \n",
        "1198  JUROS E ENCARGOS DA DIVIDA             90  Aplica\u00e7\u00f5es Diretas   \n",
        "1199       AMORTIZACAO DA DIVIDA             90  Aplica\u00e7\u00f5es Diretas   \n",
        "\n",
        "      Cd_Elemento  Cd_Fonte           Ds_Fonte Sld_Orcado_Ano  \\\n",
        "1197           21         0  Tesouro Municipal       67868404   \n",
        "1198           22         0  Tesouro Municipal        2134045   \n",
        "1199           71         0  Tesouro Municipal      280584141   \n",
        "\n",
        "            DataExtracao  \n",
        "1197 2014-09-29 18:02:01  \n",
        "1198 2014-09-29 18:02:01  \n",
        "1199 2014-09-29 18:02:01  \n",
        "\n",
        "[3 rows x 34 columns]"
       ]
      }
     ],
     "prompt_number": 20
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "quadro_detalhado[quadro_detalhado['PA'] == 4]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>COD_COMPLT_DA</th>\n",
        "      <th>COD_META</th>\n",
        "      <th>DESC_META</th>\n",
        "      <th>COD_REGIAO</th>\n",
        "      <th>DESC_REGIAO</th>\n",
        "      <th>COD_SUBPREFEITURA</th>\n",
        "      <th>DESC_SUBPREFEITURA</th>\n",
        "      <th>COD_DISTRITO</th>\n",
        "      <th>DESC_DISTRITO</th>\n",
        "      <th>COD_DA</th>\n",
        "      <th>...</th>\n",
        "      <th>VALOR_DA</th>\n",
        "      <th>ORGAO</th>\n",
        "      <th>DESC_ORGAO</th>\n",
        "      <th>UNIDADE</th>\n",
        "      <th>DESC_UNIDADE</th>\n",
        "      <th>PA</th>\n",
        "      <th>DESC_PA</th>\n",
        "      <th>ANO_EX</th>\n",
        "      <th>COD_EMP</th>\n",
        "      <th>NOME_EMP</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>878</th>\n",
        "      <td> 201.00.00.00.001</td>\n",
        "      <td> 201</td>\n",
        "      <td> Servi\u00e7o da D\u00edvida P\u00fablica Interna</td>\n",
        "      <td> 0</td>\n",
        "      <td> Supra-Regional</td>\n",
        "      <td> 0</td>\n",
        "      <td> Supra-Regional</td>\n",
        "      <td> 0</td>\n",
        "      <td> Supra-Distrital</td>\n",
        "      <td> 1</td>\n",
        "      <td>...</td>\n",
        "      <td> 350586590</td>\n",
        "      <td> 28</td>\n",
        "      <td> Encargos Gerais do Munic\u00edpio</td>\n",
        "      <td> 17</td>\n",
        "      <td> Recursos Superv. pela Secretaria Municipal de ...</td>\n",
        "      <td> 4</td>\n",
        "      <td> Servi\u00e7o da D\u00edvida P\u00fablica Interna</td>\n",
        "      <td> 2015</td>\n",
        "      <td> 1</td>\n",
        "      <td> PREFEITURA DO MUNIC\u00cdPIO DE S\u00c3O PAULO</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>1 rows \u00d7 21 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 21,
       "text": [
        "        COD_COMPLT_DA  COD_META                          DESC_META  \\\n",
        "878  201.00.00.00.001       201  Servi\u00e7o da D\u00edvida P\u00fablica Interna   \n",
        "\n",
        "     COD_REGIAO     DESC_REGIAO  COD_SUBPREFEITURA DESC_SUBPREFEITURA  \\\n",
        "878           0  Supra-Regional                  0     Supra-Regional   \n",
        "\n",
        "     COD_DISTRITO    DESC_DISTRITO  COD_DA  \\\n",
        "878             0  Supra-Distrital       1   \n",
        "\n",
        "                     ...                    VALOR_DA  ORGAO  \\\n",
        "878                  ...                   350586590     28   \n",
        "\n",
        "                       DESC_ORGAO UNIDADE  \\\n",
        "878  Encargos Gerais do Munic\u00edpio      17   \n",
        "\n",
        "                                          DESC_UNIDADE PA  \\\n",
        "878  Recursos Superv. pela Secretaria Municipal de ...  4   \n",
        "\n",
        "                               DESC_PA ANO_EX  COD_EMP  \\\n",
        "878  Servi\u00e7o da D\u00edvida P\u00fablica Interna   2015        1   \n",
        "\n",
        "                                 NOME_EMP  \n",
        "878  PREFEITURA DO MUNIC\u00cdPIO DE S\u00c3O PAULO  \n",
        "\n",
        "[1 rows x 21 columns]"
       ]
      }
     ],
     "prompt_number": 21
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "E como vemos abaixo, ambos possuem um total de gastos igual:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "projeto_de_lei[projeto_de_lei['ProjetoAtividade'] == 4]['Sld_Orcado_Ano'].sum() == quadro_detalhado[quadro_detalhado['PA'] == 4]['VALOR_DA'].sum()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 22,
       "text": [
        "True"
       ]
      }
     ],
     "prompt_number": 22
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Diferen\u00e7a no total em Reais"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "A boa not\u00edcia \u00e9 que o valor total em 2015 de gastos nas duas planilhas, detalhada e n\u00e3o detalhada n\u00e3o diferem:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print(\"R$ {:,.2f}\".format(projeto_de_lei['Sld_Orcado_Ano'].sum()))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "R$ 51,332,748,121.00\n"
       ]
      }
     ],
     "prompt_number": 114
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "quadro_detalhado['VALOR_DA'].sum()\n",
      "print(\"R$ {:,.2f}\".format(quadro_detalhado['VALOR_DA'].sum()))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "R$ 51,332,748,121.00\n"
       ]
      }
     ],
     "prompt_number": 116
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print(\"R$ {:,.2f}\".format(loa['Sld_Orcado_Ano'].sum()))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "R$ 50,569,325,587.00\n"
       ]
      }
     ],
     "prompt_number": 7
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "len(loa)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 8,
       "text": [
        "3722"
       ]
      }
     ],
     "prompt_number": 8
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "len(projeto_de_lei)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 9,
       "text": [
        "2799"
       ]
      }
     ],
     "prompt_number": 9
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "loa[loa[('Ds_Funcao'] == u'Educa\u00e7\u00e3o')]['Sld_Orcado_Ano'].sum()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 31,
       "text": [
        "9150325600"
       ]
      }
     ],
     "prompt_number": 31
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "lsoma = loa.groupby('Ds_Funcao')['Sld_Orcado_Ano'].sum()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 35
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "lsoma.to_csv('soma_por_funcao.csv', header=True, encoding='utf8')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 50
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "lsoma.columns = ['funcao', 'orcado']"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 45
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!soffice soma_por_funcao.csv"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 51
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "? lsoma.to_csv"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 49
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "loa.Ds_Funcao.value_counts()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 59,
       "text": [
        "Urbanismo                  1010\n",
        "Educa\u00e7\u00e3o                    685\n",
        "Sa\u00fade                       452\n",
        "Administra\u00e7\u00e3o               241\n",
        "Desporto e Lazer            190\n",
        "Direitos da Cidadania       175\n",
        "Cultura                     175\n",
        "Assist\u00eancia Social          141\n",
        "Habita\u00e7\u00e3o                    97\n",
        "Saneamento                   87\n",
        "Gest\u00e3o Ambiental             85\n",
        "Transporte                   83\n",
        "Legislativa                  63\n",
        "Trabalho                     54\n",
        "Seguran\u00e7a P\u00fablica            46\n",
        "Previd\u00eancia Social           40\n",
        "Judici\u00e1ria                   32\n",
        "Encargos Especiais           19\n",
        "Com\u00e9rcio e Servi\u00e7os          15\n",
        "Agricultura                  14\n",
        "Comunica\u00e7\u00f5es                 11\n",
        "Rela\u00e7\u00f5es Exteriores           4\n",
        "Defesa Nacional               2\n",
        "Reserva de Conting\u00eancia       1\n",
        "dtype: int64"
       ]
      }
     ],
     "prompt_number": 59
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}