import pandas as pd
import numpy as np
amendments = pd.read_csv('../data/2016-12-22-amendments.xz',
dtype={'proposal_id': np.str,
'amendment_beneficiary': np.str,
'amendment_program_code': np.str,
'amendment_proposal_tranfer_value': np.float,
'amendment_tranfer_value': np.float})
amendments.fillna('', inplace=True)
amendments.shape
(81169, 10)
agreements = pd.read_csv('../data/2016-12-22-agreements.xz',
usecols=(['agreement_number', 'proposal_id',
'agreement_end_date','date_signed', 'situation']),
dtype={'agreement_number': np.str,
'proposal_id': np.str,
'situation': np.str},
parse_dates=['agreement_end_date', 'date_signed'],
low_memory=False)
agreements.fillna('', inplace=True)
agreements.shape
(105276, 5)
agreements_with_amendments = agreements.merge(amendments, on='proposal_id')
agreements_with_amendments = agreements_with_amendments.filter(['amendment_number',
'congressperson_name',
'amendment_beneficiary',
'date_signed',
'agreement_end_date',
'agreement_number',
'situation'])
agreements_with_amendments.shape
(44072, 7)
This dataset gather Non-profit entities that are prevented from entering into agreements, onlending agreements or terms of partnership with the federal public administration.
Origin of the information: Controladoria-Geral da União - CGU (Comptroller General of the Union)
impeded_non_profit_entities = pd.read_csv('../data/2016-12-20-impeded-non-profit-entities.xz',
dtype={'company_cnpj': np.str,
'agreement_number': np.str})
impeded_non_profit_entities.shape
(4591, 5)
First we need to get the agreements in which entities were impeded:
impeded_entities_w_start_date = agreements_with_amendments.merge(
impeded_non_profit_entities,
left_on=(['amendment_beneficiary',
'agreement_number']),
right_on=(['company_cnpj',
'agreement_number']))
impeded_entities_w_start_date = impeded_entities_w_start_date.filter(['company_cnpj',
'compay_name',
'agreement_number',
'agreement_end_date',
'grating_body',
'impediment_reason'])
impeded_entities_w_start_date = impeded_entities_w_start_date.rename(columns =
{'agreement_end_date':'date_impended',
'agreement_number': 'impended_agreement'})
impeded_entities_w_start_date.iloc[0]
company_cnpj 08366579000112 compay_name INSTITUTO CULTURAL AFRO-BRASILEIRO OLUFON DEYI impended_agreement 748628 date_impended 2011-07-06 00:00:00 grating_body PRESIDENCIA DA REPUBLICA impediment_reason MOTIVO NÃO ESPECIFICADO Name: 0, dtype: object
Because the dataset doesn't gives the date when the entity becomes impended, we are using the end date of the agreement where the entity was impended as a minimum date called here as date_impended.
So date_impended means that we are concerned only with agreements signed after this date.
agreements_after_impended = agreements_with_amendments.merge(
impeded_entities_w_start_date,
left_on=(['amendment_beneficiary']),
right_on=(['company_cnpj']))
Below we have a list of agreements that are still in execution and are related to the amendments that have as beneficiaries non-profit entities that are impeded. In addition, the difference between the date of signature of the agreements in execution and the date of entities disability is less than 2 years.
agreements_after_impended = agreements_after_impended.query(
'situation == \'Em execução\' and \
date_impended < date_signed and \
date_signed.dt.year - date_impended.dt.year < 2')
agreements_after_impended.shape
(7, 13)
agreements_after_impended
amendment_number | congressperson_name | amendment_beneficiary | date_signed | agreement_end_date | agreement_number | situation | company_cnpj | compay_name | impended_agreement | date_impended | grating_body | impediment_reason | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
6 | 36500017 | OSMAR SERRAGLIO | 76575604000128 | 2011-12-29 | 2017-01-09 | 756696 | Em execução | 76575604000128 | SOCIEDADE EVANGELICA BENEFICENTE DE CURITIBA | 747398 | 2011-09-11 | MINISTERIO DO TURISMO | MOTIVO NÃO ESPECIFICADO |
7 | 33090023 | NELSON MEURER | 76575604000128 | 2011-12-29 | 2017-04-16 | 757799 | Em execução | 76575604000128 | SOCIEDADE EVANGELICA BENEFICENTE DE CURITIBA | 747398 | 2011-09-11 | MINISTERIO DO TURISMO | MOTIVO NÃO ESPECIFICADO |
8 | 24050011 | LUIZ CARLOS SETIM | 76575604000128 | 2011-12-29 | 2017-04-16 | 757828 | Em execução | 76575604000128 | SOCIEDADE EVANGELICA BENEFICENTE DE CURITIBA | 747398 | 2011-09-11 | MINISTERIO DO TURISMO | MOTIVO NÃO ESPECIFICADO |
9 | 10560010 | SERGIO SOUZA | 76575604000128 | 2011-12-29 | 2017-05-15 | 757915 | Em execução | 76575604000128 | SOCIEDADE EVANGELICA BENEFICENTE DE CURITIBA | 747398 | 2011-09-11 | MINISTERIO DO TURISMO | MOTIVO NÃO ESPECIFICADO |
10 | 33320012 | RICARDO BARROS | 76575604000128 | 2011-12-29 | 2017-05-20 | 758001 | Em execução | 76575604000128 | SOCIEDADE EVANGELICA BENEFICENTE DE CURITIBA | 747398 | 2011-09-11 | MINISTERIO DO TURISMO | MOTIVO NÃO ESPECIFICADO |
11 | 20380001 | FLAVIO JOSE ARNS | 76575604000128 | 2011-12-29 | 2017-04-16 | 758030 | Em execução | 76575604000128 | SOCIEDADE EVANGELICA BENEFICENTE DE CURITIBA | 747398 | 2011-09-11 | MINISTERIO DO TURISMO | MOTIVO NÃO ESPECIFICADO |
12 | 31760009 | DILCEU SPERAFICO | 76575604000128 | 2011-12-29 | 2017-03-31 | 760953 | Em execução | 76575604000128 | SOCIEDADE EVANGELICA BENEFICENTE DE CURITIBA | 747398 | 2011-09-11 | MINISTERIO DO TURISMO | MOTIVO NÃO ESPECIFICADO |
from py2neo import Node
from py2neo import Relationship
from py2neo import Graph
graph = Graph()
graph.delete_all()
congresspersons = [Node("Congressperson", name=congressperson)
for congressperson in
agreements_after_impended['congressperson_name'].unique()]
entities = [Node("Entity", name=beneficiary)
for beneficiary in
agreements_after_impended['compay_name'].unique()]
for congressperson in congresspersons:
graph.create(congressperson)
for entity in entities:
graph.create(entity)
for index, row in agreements_after_impended.iterrows():
congressperson = list(filter(lambda c: c['name'] == row['congressperson_name'],
congresspersons))[0]
entity = list(filter(lambda c: c['name'] == row['compay_name'],
entities))[0]
graph.create(Relationship(congressperson,
"benefited",
entity))
import neo4jupyter
neo4jupyter.init_notebook_mode()
from neo4jupyter import draw
options = {"Congressperson": "name", "Entity": "name"}
draw(graph, options)