Na primeira parte deste tutorial, procurei mostrar como:
Neste segundo tutorial, vamos focar em outra consulta disponível na mesma API: os contratos.
A Prefeitura de São Paulo ainda não dispõe de um sistema centralizado para gestão de contratos (existe um em implementação).
A boa notícia é que, com relação a contratos, se pode considerar uma das cidades mais transparentes do Brasil por ser uma das poucas a disponibilizar os termos de seus contratos e convênios na íntegra, desde 2014, nesta página do Portal da Transparência. Com o Marco Regulatório da Sociedade Civil, vigente a partir deste ano, também começam a aparecer por lá os Termos de Cooperação (sem transferência de recursos) que antes não eram publicados.Também as doações começaram a aparecer aí.
A má notícia é que nessa base do Portal você consegue extrair as informações publicadas em Diário Oficial, mas pode haver inconsistências (geradas por erros de publicação no D.O.). Às vezes há duplicidade, republicação, valores equivocados (pois são digitados manualmente na hora de publicar).
Por isso, podemos considerar que o registro mais confiável que há sobre a execução dos contratos é o do próprio sistema de Execução Orçamentária (o SOF), uma vez que é necessário cadastrar os contratos para realizar os pagamentos. E é daí a relevância desta API.
Importante!
Não tenho certeza se os contratos são imediatamente cadastrados no SOF ou se passam a existir no sistema apenas quando é gerado empenho vinculado a ele. Aqui neste exemplo eu encontrei empenhos zerados em alguns contratos, o que indica que não. Ainda assim, se quiser tratar no universo inteiro de contratos e não apenas da execução orçamentária, recomendo o 'double check' com a base do Portal da Transparência -- explico mais no último passo deste tutorial.
Seguiremos os passos detalhados no tutorial anterior para acessar essa consulta na API e utilizar o Pandas para montar um DataFrame.
In [1]:
import pandas as pd
import requests
import json
import numpy as np
In [2]:
TOKEN = '198f959a5f39a1c441c7c863423264'
base_url = "https://gatewayapi.prodam.sp.gov.br:443/financas/orcamento/sof/v2.1.0"
headers={'Authorization' : str('Bearer ' + TOKEN)}
Na consulta anterior, eu indiquei que o Código da Educação no Orçamento, por exemplo, é 16; o Fundo Municipal de Saúde, 84. Quem mexe com o orçamento, acaba se acostumando com os números. Mas como saber o código de todas as secretarias e empresas municipais? Existe uma consulta específica para isso na API, que você pode fazer lá no próprio console. Mas vamos trazer essa tabela para cá, para facilitar:
In [3]:
url_orgaos = '{base_url}/consultarOrgaos?anoExercicio=2017'.format(base_url=base_url)
In [4]:
request_orgaos = requests.get(url_orgaos,
headers=headers,
verify=True).json()
In [5]:
df_orgaos = pd.DataFrame(request_orgaos['lstOrgaos'])
In [15]:
df_orgaos
Out[15]:
Atenção para uma distinção conceitual importante!
Os campos na consulta da API são diferentes. Então se você inserir o Código da Prodam (uma empresa pública) no Campo Órgão, por exemplo, a consulta vai retornar 0 Registros.
Seguindo, vamos fazer a consulta dos contratos da Secretaria do Verde e Meio Ambiente -- Órgão 27, conforme a lista acima.
In [7]:
url_contratos = '{base_url}/consultaContrato?anoContrato=2017&codOrgao=27'.format(base_url=base_url)
In [8]:
request_contratos = requests.get(url_contratos,
headers=headers,
verify=True).json()
In [9]:
number_of_pages = request_contratos['metadados']['qtdPaginas']
todos_contratos = []
todos_contratos = todos_contratos + request_contratos['lstContratos']
if number_of_pages>1:
for p in range(2, number_of_pages+1):
request_contratos = requests.get(url_contratos + pagination.format(PAGE=p), headers=headers, verify=True).json()
todos_contratos = todos_contratos + request_contratos['lstContratos']
In [10]:
df_contratos = pd.DataFrame(todos_contratos)
Vamos checar como ficou nossa base de dados de contratos, mostrando os últimos 5 registros:
In [11]:
df_contratos.tail()
Out[11]:
São muitas as colunas dessa consulta (a visualização acima omite algumas). Vamos checar quais são:
In [12]:
list(df_contratos)
Out[12]:
In [16]:
df_contratos.to_excel('contratos.xls')
In [17]:
df_contratos.groupby(['txtDescricaoModalidade'])['valEmpenhadoLiquido', 'valPago'].sum().sort_values(['valEmpenhadoLiquido'], ascending=False)
Out[17]:
Vamos remover o txtRazaoSocial, para não nos atrapalhar mais tarde (vamos obter o dado de outro local). Esta consulta não retorna o nome das empresas contratadas, mas "PREFEITURA DE SÃO PAULO" para todos os valores -- provavelmente um erro da API.
In [18]:
df_contratos.drop('txtRazaoSocial', axis=1, inplace=True)
Infelizmente, a consulta não vem "pronta". Vamos precisar cruzar com a base de empenhos do primeiro tutorial para conseguir, por exemplo, saber quais são os credores POR CONTRATO. A imagem abaixo foi exibida durante o lançamento da API, no Café Hacker da Controladoria Geral do Município e da Secretaria Municipal da Fazenda, e explica como as consultas se relacionam (o 'Simplificada' é por conta deles, hehe):
O Código de Contrato é um parâmetro facultativo da consulta de empenhos, mas não vem no retorno de dados (outro possível furo da API, que vamos alertar aos desenvolvedores para tentar corrigir). O que isso significa? Que precisaríamos consultar um a um para obter todos os contratos na lista de empenhos! Felizmente, a programação tá aí pra isso. Vamos criar uma estrutura de repetição semelhante à que fizemos para a paginação:
In [19]:
url_empenho = '{base_url}/consultaEmpenhos?anoEmpenho=2017&mesEmpenho=08&codOrgao=27'.format(base_url=base_url)
num_contrato = '&codContrato={CONTRATO}'
In [20]:
lista_contratos = list(df_contratos['codContrato'])
len(lista_contratos)
Out[20]:
In [21]:
request_empenhos = requests.get(url_empenho,
headers=headers,
verify=True).json()
In [22]:
def add_codigo_contrato(empenhos, cod_contrato):
"""""
Adiciona Código de Contrato no dict de cada empenho consultado.
"""""
for item in empenhos:
item.update({'codContrato': cod_contrato})
return empenhos
In [28]:
todos_empenhos = []
todos_empenhos = todos_empenhos + request_empenhos['lstEmpenhos']
for n in lista_contratos:
response = requests.get(url_empenho + num_contrato.format(CONTRATO=n), headers=headers, verify=True).json()
empenhos_c_cod = add_codigo_contrato(response['lstEmpenhos'], n)
todos_empenhos = todos_empenhos + empenhos_c_cod
In [30]:
df_empenhos_c_contratos = pd.DataFrame(todos_empenhos)
Agora já temos o DataFrame que junta todos Empenhos do Verde de 2017 com as informações de Contrato, deixando a base mais rica -- e corrigindo essa falha da falta de Razão Social e CNPJ na consulta de contratos!
In [33]:
df_empenhos_c_contratos.head()
Out[33]:
A tabela acima vai ter muitos valores "Nan" para codContrato -- pois há 493 empenhos e apenas 80 contratos. Como o interesse agora é só nos contratos, vamos retirar esses casos e montar um novo DataFrame que contém apenas contratos com algum empenho relacionado:
In [34]:
df_empenhos_c_contratos = df_empenhos_c_contratos.dropna(axis=0).reset_index(drop=True)
Mudando o formato de número de decimal (tirar aquele .0 dali) para integer:
In [35]:
df_empenhos_c_contratos['codContrato'] = df_empenhos_c_contratos.loc[:,'codContrato'].astype(int)
Agora temos duas bases de dados para trabalhar:
Ambas têm várias colunas em comum. Então, antes de juntá-las, vamos apenas retirar as colunas únicas nas duas, com o método "difference":
In [36]:
cols_to_use = df_empenhos_c_contratos.columns.difference(df_contratos.columns)
In [37]:
df_contratos_empenhados = df_contratos.merge(df_empenhos_c_contratos[cols_to_use], left_index=True, right_index=True, how='outer')
Agora com a lista de todos os contratos do ano, vamos montar uma tabela e ordenar os dados pelo Valor Principal do Contrato. No Manual da API, aprende-se que esse campo 'valPrincipal'significa o "Valor do contrato sem ocorrência de reajustamentos, ou aditamentos".
In [76]:
top10 = df_contratos_empenhados[['txtDescricaoModalidade',
'txtObjetoContrato',
'txtRazaoSocial',
'numCpfCnpj',
'valPrincipal']].sort_values(['valPrincipal'], ascending=False)[:10]
In [77]:
top10
Out[77]:
In [81]:
df_contratos_empenhados.to_excel('exemplos/contratos_empenhados.xls')
In [82]:
df_contratos_empenhados.to_csv('exemplos/contratos_empenhados.csv')
Como mencionei lá em cima, o Portal da Transparência de São Paulo tem algo muito importante que é publicar os contratos na íntegra nesta página. Não vou entrar nos detalhes dos poréns que surgem aqui -- mas saiba que a ação de publicar o contrato depende de as pessoas subirem o "anexo" certo na hora de enviar o extrato para o Diário Oficial; o que acaba acontecendo é que são cerca de 700 usuários que fazem isso em toda a prefeitura e os erros são frequentes (ex.: subir arquivo de extrato no local de íntegra; indicar modalidade errada; digitar metadado do valor errado etc etc).
Eu já baixei (e arrumei alguns campos) um arquivo de lá com o mesmo exemplo da SVMA. Vamos comparar com o que vem na API do SOF:
In [61]:
df_contratos_portal = pd.read_excel('exemplos/contratos_portal.xls')
In [90]:
df_contratos_portal.sort_values('Valor (R$)', ascending=False).head()
Out[90]:
In [98]:
df_contratos_portal.groupby('Modalidade')['Valor (R$)'].sum()
Out[98]: