Data visualizations

[thaisvergani]

Hi! I am developing some charts using d3 js I want to help people to understand the composition of the reimbursements and I wish to know your thoughts about it :)

Chart	Description
Composition of the reimbursements classified by Category	A pizza chart that shows how much each category impacts on the budget
Usage of each Category	Bars chart showing the total (R$) allowed for that category and how much of it was used. Each bar could represent (1) the max value allowed by the quota in that state, (2) how much was used and (3) how much of it Serenata considered suspicious. There would be filters so the user could select the congressperson or the state.

OBS: The charts will consider the information in a period of selected year.
(if the total value isn't used in a month it will be cumulative to the next months)

[cuducos]

Sounds really interesting! Some comments:

1. Will the Composition of the reimbursements classified by Category charts be separated by state? I think this might be a good idea: since each state has a different allowance in the quota and a different number of deputies, this could be a possible bias in the visualization (São Paulo has more deputies and arguably a more expensive quota than half of the states, for example).
2. There's something we might struggle with in Usage of each Category: deputies can transfer part of their quota to the leadership and AFAIK this is not publicized in anyway by the Chamber of Deputies (and this could be another bias to take into account). Maybe pressure them to register that in a public medium?

[jedibruno]

It is indeed really interesting!

I have no coding skills to help with that, but maybe I can help locating more data about the quota transfers to the leadership. What do you think? Could it be useful?

[cuducos]

I can help locating more data about the quota transfers to the leadership. What do you think? Could it be useful?

Sure thing, that'd pretty useful IMHO : )

[jedibruno]

In order to gather more information about this, I filled an official FOIA request to the Chamber of Deputies on their website. Here is the text (in Portuguese) in case anyone be curious:

Requisitamos acesso às informações listadas abaixo, referentes à utilização da Cota de Exercício da Atividade Parlamentar (CEAP). Para facilitar a compreensão das informações fornecidas, requisitamos que cada item seja respondido separadamente, indicando o número a que se refere. 1 – Existe alguma espécie de base de dados nos quais sejam registradas as transferências/cessões de quotas da CEAP para a Liderança, conforme previsto no art. 14, §§1º e 2º do Ato de Mesa 43/2009? 1.1 – Caso não: 1.1.1 - Por quais motivos, de fato e de direito, isso ocorre? 1.1.2 – De que maneira é realizado o controle destas transferências? 1.1 – Caso sim: 1.1.1 – Desde quando existe? 1.1.2 – Qual é o órgão responsável pela sua manutenção? 1.1.3 – Que tecnologia é utilizada para o armazenamento? 1.1.4 – Qual é o volume, em bytes, desta base de dados? 1.1.5 – Requisitamos acesso à URL (link) no qual seja possível obter acesso a estes dados mediante transparência ativa, nos termos do art. 8º, caput e §3º, II e III da LF 12.527/11; 1.1.5.1 – Caso não seja possível obter acesso nos termos do item 1.1.1: 1.1.5.1.1 – Por quais motivos, de fato e de direito, isso ocorre? 1.1.5.1.2 – Que medidas estão sendo tomadas para tornar estas informações disponíveis mediante transparência ativa? 1.1.5.1.3 – Nos termos do art. 8º, §3º, II e III da LF 12.527/11 e art. 24, VI da LF 12.965/14, requisitamos o fornecimento de arquivo, em formato *.csv (que é aberto e não proprietário), contendo todos os dados armazenados nessa base de dados.

Observações: caso não seja possível fornecer diretamente os dados por meio eletrônico, podemos eventualmente providenciar mídia eletrônica para armazenamento dos dados.

I'll publish the answer as soon as I receive it.

[gabrielfior]

I have a different suggestion regardiing data visualization. What about using a ready-to-use solution like Redash (https://redash.io/)? There you can just connect your database, write SQL queries and create the visulizations. Afterwards you can embed them in Websites or other medias.

PROS

• Out-of-the-box solution, quick to deploy (AWS Images available, as well as for other platforms)
• Easy to share and embed elsewhere
• Different visualizations possible (Charts, Pivot tables, heatmaps, etc)
• Very good coverage of data sources (Postgres, Google Analytics, Excel, JSON requests, etc)

CONS

• Redash and others must be deployed in a server, hence costs are to be expected (from personal experience, using in AWS costs ~ 10 USD/month).

It would be great to have your thoughts on it!

[cuducos]

Redash and others must be deployed in a server, hence costs are to be expected (from personal experience, using in AWS costs ~ 10 USD/month).

This is basically increasing 25% of our total costs with infra-structure. At this point of the project I'm afraid this is prohibitive : (

Also, our bottleneck already is the database, so I would prefer to not directly connect the visualization to PostgreSQL to avoid increasing costs in the database machine.

[thaisvergani]

Another chart that could be useful would be related to tweets. How the tweet impacts on reimbursements of a congressperson? I mean, does the reimbursements of a congressperson get any cheaper after he/she gets notified that Rosie considered a expense suspicious? If so, can i get the date of the tweet? Where is it stored?

[cuducos]

I mean, does the reimbursements of a congressperson get any cheaper

Not analyzed per congressperson or tweet, but we might assume yes, it does.

If so, can i get the date of the tweet? Where is it stored?

You need to use the Twitter API. We just store the tweet status number.

[jedibruno]

I received an answer from the Chamber of Deputies (CD) regarding the request I made about the leadership quotas. The answers (in Portuguese) are attached to this message.

I'm speacially troubled by this answer:

1 – Existe alguma espécie de base de dados nos quais sejam registradas as transferências/cessões de quotas da CEAP para a Liderança, conforme previsto no art. 14, §§1º e 2º do Ato de Mesa 43/2009? A cessão de cota prevista no art. 14 do Ato da Mesa 43/2009 é registrada no sistema de controle da Cota para o Exercício da Atividade Parlamentar (Ceap), denominado Cotasnet.

Maybe I should ask directly something like: "how can a citizen know if there are quota transfers between the deputy and the leadership?" What do you guys think about it?

Related topic:

It has been a while since I didn't access the CD website, but it seems like they made several improvements to their "transparency" section. Lots of dataviz where once there was no information avaiable. Have you guys checked it out?

Resposta Ditec..pdf Resposta Defin..pdf

[cuducos]

Maybe I should ask directly something like: "how can a citizen know if there are quota transfers between the deputy and the leadership?"

Sure thing. It worth nothing to ask about transparency if the answer direct us to a restricted software/dataset.

[jedibruno]

Alright. I have submitted another formal request to the Chamber of Deputies regarding the subject. Hopefully we can get a better picture this time. Their deadline is approximately on april 17th

Here is the text I have submitted (in Portuguese):

Tendo em vista que as informações fornecidas no Protocolo SIC 190214-000273 não satisfizeram a necessidade de informações sobre o assunto, requisitamos acesso às informações listadas abaixo, referentes as transferências/cessões de quotas da CEAP para as lideranças, conforme previsto no art. 14 do Ato de Mesa 43/2009. Para facilitar a compreensão das informações fornecidas, requisitamos que cada item seja respondido separadamente, indicando o número a que se refere: 1 – De que maneira o cidadão pode verificar se houve transferência de quotas entre algum deputado e sua liderança? 1.1 – Tendo em vista que a simples indicação da URL (https://www.camara.leg.br/cota-parlamentar/) não tornou clara a questão, utilizando como exemplo algum caso em que tenha ocorrido efetivamente transferência/cessão de quota, requisitamos o fornecimento de um guia passo-a-passo de como chegar a esta informação. 2 – Em que frequência os dados referentes as transferências/cessões de quotas para a liderança são atualizados?

[jedibruno]

Hello guys!

I have received another answer from the Chamber of Deputies about the quota transfers between representatives and their leadership.

I have attached their complete answer below, but in few words: the transfers "per se" are currently not avaiable for public scrutiny online.

What do you suggest to be our next move? Maybe we can try to request access to this dataset?

Resposta 190328-000193.pdf