Linhas repetidas no BD das receitas

[andresmrm]

O Everton achou umas linhas repetidas na base da receita. Fui ver, gerador de relatórios está mesmo exportando mais de uma linha para o mesmo (código, data). E pior, indo para os anos mais recentes, há mais de uma descrição para o mesmo código: 1.2.1.0.29.07 - CONTRIBUIÇÃO DE SERVIDOR ATIVO CIVIL 1.2.1.0.29.07 - CONTRIBUIÇÃO DO SERVIDOR ATIVO CIVIL PARA O REGIME

Que estão respectivamente, dentro de: 1.2.1.0.29 - CONTRIBUIÇÕES PREVIDENCIÁRIAS DO REGIME PRÓPRIO 1.2.1.0.29 - CONTRIBUIÇÕES PARA O REGIME PRÓPRIO DE PREVIDÊNCIA

Ambos dentro de: 1.2.1.0 - CONTRIBUIÇÕES SOCIAIS

Logo a repetição, nesse caso, não é nem só na Sub-Alínea, mas na Alínea também.

fod**.

verificar se endpoint de códigos retorna mais de um código nesses casos: EDIT: Não, pois endpoint está pegando códigos do TXT referente à 2013. E, pelo menos para o código 1.2.1.0.29.07, não há duplicação nesse ano. Saídas possíveis: ou pegar descrições dos próprios dados, ou pegar TXTs de todos os anos. A segunda opção parece mais trabalhosa, mas na primeira opção, algumas descrições estão cortadas. Tentando a segunda via, mas não estou achado uma tabela que sirva. A tabela que temos é a de 2013 do TCM de GO. Mas a de 2011 deles não bate com nossos dados, pelo menos não para o código 1.2.1.0.29.07: http://www.tcm.go.gov.br/portal/arquivos-tcm/tabela-codificacao/codificacaoReceitas2011.pdf

EDIT2: Estou vendo os códigos que mapeiam para mais de uma descrição. Aparentemente a grande maioria são apenas descrições "iguais só que diferentes". Algo como:

1.3.2.5.01.01 - RECEITA DE REMUNERAÇÃO DE DEPÓSITOS BANCÁRIOS DE R
1.3.2.5.01.01 - RECEITA DE REMUNERAÇÃO DE DEPÓSITOS BANCÁRIOS DE RECURSOS VINCULADOS -

Ou:

9.9.1.1.40.01 - DEDUÇÕES DAS MULTAS E JUROS DE MORA DO IMPOSTO S/
9.9.1.1.40.01 - DEDUÇÕES DAS MULTAS E JUROS DE MORA DO ISS

Mas existem algums que aparentemente são diferentes mesmo:

1.3.2.9.01.23 - PMSP-SEMDET/PARQUE TECNOLÓGICO 1.3.2.9.01.23 - CONVÊNIO Nº 804142/03 - FNDE/MEC - ENSINO FUNDAME

Ou:

1.7.2.1.33.11 - MÉDIA E ALTA COMPLEXIDADE VIGILÂNCIA SANITÁRIA - M
1.7.2.1.33.11 - ATENÇÃO BÁSICA - FMS

Fiz um script para analisar as linhas/códigos repetidos: https://github.com/okfn-brasil/gastos_abertos_dados/blob/master/utils/show_replications.py Gerei então um arquivo e o estava limpando dos casos em que parece que as descrições querem dizer o mesmo, mas parei pois cansei e não sei se vamos mesmo usar. Fica ai caso venha a ser util.: https://github.com/okfn-brasil/gastos_abertos_dados/blob/master/utils/codes_replicated__semi-limpo__.csv

[andresmrm]

Quoting Everton Zanella Alvarenga (2015-02-13 18:34:51)

Andres, todos,

eu tirei a linha dos IDs gerados pelo MySQL na hora que os dados foram exportados. Isso faria com que cada linha dos dados fossem únicas, mas tenho dúvidas se essa será a solução para esses problemas nos dados.

Vou inserir uma coluna com o ID de cada linha para resolver o problema da chave única das dimensões no Open Spending (OS), mas talvez precisaremos entender essas linhas.

Até,

Tom

[andresmrm]

Vejamos, filtrando por essas duas chaves: "2008-01" "1.2.1.0.29.07 - CONTRIBUIÇÃO DE SERVIDOR ATIVO CIVIL" A base original exportada do site da prefeitura retorna 13 linhas:

    Valor_Previsto_Receita_Anual1   Valor_Realizado_Receita
    R$ 44.280.609,67    R$ 0,00
    R$ 0,00             R$ 31.227.162,22
    R$ 0,00             R$ 493.816,36
    R$ 0,00             R$ 465.329,02
    R$ 0,00             R$ 9.913,65
    R$ 0,00             R$ 181.138,86
    R$ 0,00             R$ 0,00
    R$ 0,00             R$ 35.032,91
    R$ 0,00             R$ 38.373,68
    R$ 0,00             R$ 32.272,20
    R$ 0,00             R$ 228.724,61
    R$ 0,00             R$ 45.584,78
    R$ 0,00             R$ 31.900,22

Somando todos os realizados (segunda coluna), temos o total de: R$ 32.789.248,51 Que é o que está na coluna de totalizações, que também vem na base original:

    TotalPrevistoMês        TotalRealizadoMês
    R$ 44.280.609,67        R$ 32.789.248,51
    R$ 44.280.609,67        R$ 32.789.248,51
    R$ 44.280.609,67        R$ 32.789.248,51
    R$ 44.280.609,67        R$ 32.789.248,51
    R$ 44.280.609,67        R$ 32.789.248,51
    R$ 44.280.609,67        R$ 32.789.248,51
    R$ 44.280.609,67        R$ 32.789.248,51
    R$ 44.280.609,67        R$ 32.789.248,51
    R$ 44.280.609,67        R$ 32.789.248,51
    R$ 44.280.609,67        R$ 32.789.248,51
    R$ 44.280.609,67        R$ 32.789.248,51
    R$ 44.280.609,67        R$ 32.789.248,51
    R$ 44.280.609,67        R$ 32.789.248,51

Logo, nesse caso, parece ser só somar todas as linhas de mesma data código e descrição. Agora, porque o Valor_Realizado veio dividido em 12 valores para cada mês do ano, e mais o Valor_Previsto isolado? Não faço a mínima ideia... Estou vendo os outros casos de repetição.

[andresmrm]

Para code=1.1.1.2.02.01 e ano=2011 nos dados que estamos importando, temos isso:

date        monthly_predicted   monthly_outcome
2011-01-31  363849720.33        102727192.89
2011-01-31  363849720.33        293725994.02
2011-02-28  0.0                 0.0
2011-02-28  0.0                 0.0
2011-03-31  363849720.33        1306138398.41
2011-03-31  363849720.33        314980755.3
2011-04-30  0.0                 0.0
2011-04-30  0.0                 0.0
2011-05-31  363849720.33        379401568.63
2011-05-31  363849720.33        300478159.98
2011-06-30  0.0                 0.0
2011-06-30  0.0                 0.0
2011-07-31  363849720.33        297207110.08
2011-07-31  363849720.33        298250991.07
2011-08-31  0.0                 0.0
2011-08-31  0.0                 0.0
2011-09-30  363849720.33        337338324.75
2011-09-30  363849720.33        294862477.86
2011-10-31  0.0                 0.0
2011-10-31  0.0                 0.0
2011-11-30  363849720.33        310068828.47
2011-11-30  363849720.33        91513590.82
2011-12-31  0.0                 0.0
2011-12-31  0.0                 0.0

Isso porque os dados que exportamos da prefeitura, quando pedimos TODOS os mêses são assim:

Valor_Previsto_Receita_Anual1   Valor_Realizado_Receita
R$ 363.849.720,33               R$ 102.727.192,89
R$ 0,00                         R$ 0,00
R$ 363.849.720,33               R$ 1.306.138.398,41
R$ 0,00                         R$ 0,00
R$ 363.849.720,33               R$ 379.401.568,63
R$ 0,00                         R$ 0,00
R$ 363.849.720,33               R$ 297.207.110,08
R$ 0,00                         R$ 0,00
R$ 363.849.720,33               R$ 337.338.324,75
R$ 0,00                         R$ 0,00
R$ 363.849.720,33               R$ 310.068.828,47
R$ 0,00                         R$ 0,00
R$ 363.849.720,33               R$ 293.725.994,02
R$ 0,00                         R$ 0,00
R$ 363.849.720,33               R$ 314.980.755,30
R$ 0,00                         R$ 0,00
R$ 363.849.720,33               R$ 300.478.159,98
R$ 0,00                         R$ 0,00
R$ 363.849.720,33               R$ 298.250.991,07
R$ 0,00                         R$ 0,00
R$ 363.849.720,33               R$ 294.862.477,86
R$ 0,00                         R$ 0,00
R$ 363.849.720,33               R$ 91.513.590,82
R$ 0,00                         R$ 0,00

Não possuem marcação de mês. Logo assumimos que é um mês após o outro. Mas nesse caso, na linha 13, o script deve estar recomeçando a contagem em janeiro. Uma interpretação possível, já que há 2 linhas por mês. Nessa interpretação, os meses ímpares ficam com dois valores, e os pares ficam com duas linhas zeradas.

Porém, indo no site da prefeitura e exportando mês a mês, janeiro vem assim:

Valor_Previsto_Receita_Anual1   Valor_Realizado_Receita
R$ 363.849.720,33   R$ 102.727.192,89
R$ 0,00             R$ 0,00

E julho, só para ter "mais um ponto", está assim:

Valor_Previsto_Receita_Anual1   Valor_Realizado_Receita
R$ 363.849.720,33   R$ 293.725.994,02
R$ 0,00             R$ 0,00

Logo a interpretação correta é pegar duas linhas para cada mês na sequência, e não intercalando cada mês. Pelo menos quando tivermos 24 linhas...

[andresmrm]

Problema equivalente ao anterior ocorre para "2.5.9.0.01.21" em "2008". A mesma regra anterior se aplica. Lembrar de primeiro pegar os meses na ordem certa para depois somar o que está dentro de um mês.

[aivuk]

Descobri o problema e reimportei (parcialmente) os dados. Os novos dados encontram-se em:

https://github.com/okfn-brasil/gastos_abertos_dados/tree/master/Orcamento/receitas/via_site_prefeitura/prefeitura

Depois de ver o que andou fazendo acima e verificar que todos os dados que possuímos repetidos são múltiplos de 12, fui na página da Prefeitura e resolvi escolher um ano pra testa com um detalhe: ao invés de deixar Instituição como TODOS, deixei apenas Prefeitura.

Com isso todos os valores repetidos que conferi só aparecem agora com 12 linhas, umas pra cada mês de forma sequencial.

O que acontece quando colocar o campo TODOS em Instituição é que o gerador de relatórios gera N linhas por mês, sendo cada linha referente a uma instituição. O grande problema é que em nenhum local aparece a informação de qual instituição a linha se refere, então aparentemente teremos que ou achar outra fonte pros dados, ou fazer o que eu fiz pra Prefeitura, só que pras outras instituições.

[aivuk]

Esqueci de comentar acima como fiz pra verificar que as linhas duplicadas eram sempre múltiplas de 12:

http://nbviewer.ipython.org/github/aivuk/var/blob/master/gastos_abertos/Linhas%20duplicadas.ipynb

[aivuk]

Vendo esta página no site da prefeitura:

http://www.prefeitura.sp.gov.br/cidade/secretarias/financas/contaspublicas/index.php?p=3216

Os valores que baixei agora, ou seja, os que estão com a opção Instituição apenas Prefeitura, estão batendo com os da Prefeitura. Então talvez o correto seja fazer como fiz e utilizarmos apenas esses novos dados que baixei. Uma dúvida que fiquei é: por que no link acima existem balancetes da Receita desde 2005, e no gerador de relatórios somente desde 2008?

[aivuk]

Vou fechar a tarefa. Acredito que o mistério das linhas duplicadas está resolvido. Agora precisamos de novas tarefas: converter as planilhas novas importadas pro formato do receitas_min.csv e fazer um script do Selenium para importar as receitas das outras instituições.

[andresmrm]

Boa, @aivuk. Não tinha pensado em dividir por instituição. Mas é curioso que algumas sub-alíneas tenham mais de 12 linhas por anos e outras não. Só se ele só duplica aquilo que mais de uma instituição tem?