Banco de Dados

22 abr, 2013

Dados Abertos – Utilizando SQL para pequenas análises de dados

Publicidade

Mais do que uma tradução, este artigo se trata, na verdade, de uma adaptação. No artigo original, Using SQL for Lightweight Data Analysis, escrito por Rufus Pollock, foram utilizados dados sobre despesas de Londres em janeiro de 2013. Na tentativa de contextualizar o assunto, vou usar aqui dados disponibilizados pelo Governo Brasileiro sobre o PAC (Programa de Aceleração do Crescimento).

Este artigo introduz o uso da SQL para pequenas análises de dados, através da análise de pequenos conjuntos de dados abertos para responder a seguinte questão: quem foram os maiores beneficiados com as obras do PAC em 2012?

Durante o artigo, além da SQL (e SQLite), ilustraremos como localizar, filtrar e carregar dados tabulares em uma base de dados relacional, para que possam ser facilmente manipulados.

Localizando os dados

A primeira coisa que precisamos fazer é localizar os dados online. Vamos começar com uma busca, por exemplo: “dados obras do PAC“. Essa busca nos levará, rapidamente, ao dados.gov.br, mais especificamente, à página Obras do PAC – Programa de Aceleração do Crescimento / Obras do PAC em CSV referentes ao 6º balanço – 12/2012:

dados sobre as obras do PAC

Preparando os dados

No artigo original, o autor descreve os passos necessários para limpar o arquivo CSV, removendo linhas ou colunas vazias, linhas em branco no final do arquivo, etc.

No nosso conjunto de dados, esse tipo de problema não ocorre, porém, temos um problema de conjunto de caracteres. O arquivo CSV utiliza um formato ASCII estendido em vez de UTF-8. Para resolver esse problema, podemos utilizar o utilitário iconv para fazer a conversão:

iconv -f 857// -t UTF-8 pac_2012_12.csv >pac_2012_12.utf8.csv

A linha acima fará a leitura do arquivo pac_2012_12.csv, converterá o conjunto de caracteres e salvará o conteúdo novamente em pac_2012_12.csv.

Outro detalhe que precisamos ajustar, é a questão da data. No Brasil, utilizamos o formato dd/mm/yyyy. Já na base de dados, precisamos utilizar o formato yyyy-mm-dd. Isso pode ser facilmente resolvido com o seguinte:

sed -i "s_\(..\)/\(..\)/\(....\)_\3-\2-\1_g" pac_2012_12.utf8.csv

A linha acima fará a conversão de todas as ocorrências do padrão dd/mm/yyyy e converterá para yyyy-mm-dd. Caso você esteja trabalhando com algum conjunto de dados que, além dos pontos descritos aqui, ainda tenha linhas com metadados, linhas ou colunas em branco, não deixe de ler o artigo original para saber como lidar com eles.

Analizando dados em uma base de dados relacional

Nosso objetivo é trabalhar com os maiores beneficiados e quais são as áreas à que o dinheiro é destinado. Para um pequeno conjunto de dados, poderíamos utilizar uma planilha eletrônica. Entretanto, vamos seguir um caminho um pouco diferente e utilizar uma abordagem mais apropriada, com uma base de dados relacional.

Utilizaremos o SQLite, um banco de dados relacional, open-source, que apesar de leve, é bem completo para nosso propósito. Você precisará verificar se o tem instalado (digite sqlite ou sqlite3 no seu terminal – se você não o tiver instalado, basta fazer o download e instalar).

Carregando os dados no SQLite

Agora que já temos o conjunto de dados, precisamos carregá-lo no SQLite. Para isso, podemos utilizar um pequeno script python chamado csv2sqlite. Como o nome sugere, ele carrega o conteúdo de um arquivo CSV em uma base SQLite. O código completo está no apêndice abaixo, mas você pode fazer download a partir desse gist. Uma vez baixado, você pode utilizá-lo assim:

# isso carregará nosso arquivo CSV em uma nova tabela chamada "pac"
# dentro de uma nova base SQLite chamada pac.sqlite
csv2sqlite.py pac_2012_12.csv pac.sqlite pac

Nota: Caso você esteja trabalhando com Windows e as ferramentas head, tail, sed, iconv e o python, utilizadas neste artigo e no original não estejam disponíveis no seu ambiente, você poderá fazer o download da base de dados pac.sqlite.

Análise I

Vamos até o shell do sqlite executar algumas instruções SQL:

$ sqlite3 pac.sqlite
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Dependendo da versão do sqlite, é possível que você precise executar sqlite em vez de sqlite3. Agora que estamos dentro do shell do sqlite, vamos executar uma instrução simples:

sqlite> SELECT `sig_uf`,SUM(`investimento_total`)
...> FROM `pac`
...> GROUP BY `sig_uf`
...> ORDER BY SUM(`investimento_total`) DESC
...> LIMIT 20;

Como isso funciona? Bom, o ponto chave aqui é o GROUP BY. O que ele faz é agrupar todas as linhas com o mesmo valor na coluna sig_uf. Podemos utilizar o SELECT para dizer quais campos ou resultados de funções que queremos na listagem dos resultados. No caso acima, nós apenas selecionamos o sig_uf e a soma de todo o investimento_total das linhas que possuem o mesmo valor no sig_uf. Feito isso, limitamos o resultado em apenas vinte linhas e, finalmente, ordenamos do maior valor para o menor valor. O resultado dessa consulta segue abaixo:

UF Investimento
SP 171740111838.0
RJ 49473735257.08
MG 41298926989.44
PR 28724574100.32
RS 27157499074.68
BA 24401562534.44
DF 16320196665.42
GO 15073583842.54
PE 13666131507.18
SC 13633033294.1
CE 13033312986.18
PA 12473700610.37
MA 9472695230.34002
AM 8926094516.68
ES 7630901437.12
MT 6906802672.1
RN 6465137159.22
MS 6189088362.3
SE 5934839614.14
PB 5509364605.14001

Podemos utilizar outras funções também, por exemplo, quais são as cinco áreas mais beneficiadas e qual é o investimento médio feito nessas áreas?

sqlite> SELECT `dsc_orgao`,SUM(`investimento_total`),AVG(`investimento_total`)
...> FROM `pac`
...> GROUP BY `dsc_orgao`
...> ORDER BY SUM(`investimento_total`) DESC
...> LIMIT 5;

Órgão Investimento Total Investimento Médio
Ministério das Cidades 490643623250.721 39456664.5155385
Fundação Nacional da Saúde 7962934548.78002 510509.972354149
Ministério da Educação 3594551103.33999 305659.10742687
Ministério da Cultura 775253231.56 1076740.59938889
Ministério da Saúde 768838158.779999 49058.0754709035

Isso nos dá uma boa noção de como são distribuídos os recursos; se existem, por exemplo, vários itens recebendo pequenas quantidades, ou poucos itens recebendo grandes quantidades.

O que vimos aqui, é que a maior parte dos recursos são utilizados pelo Ministério das Cidades, cuja atribuição é a de “planejamento urbano, políticas fundiárias e imobiliárias, (que incluem zoneamento, regularização da posse ou propriedade, código de obras) requalificação de áreas centrais, prevenção a riscos de desmoronamento de encostas, recuperação de áreas ambientalmente degradadas são atribuições municipais“.

Análise II – Filtragem

Agora que sabemos que a maior parte dos recursos são utilizados pelo Ministério das Cidades, podemos ir um pouco mais a fundo: quais são os estados mais beneficiados pelo Ministério das Cidades?

sqlite> SELECT `sig_uf`,SUM(`investimento_total`)
...> FROM `pac`
...> WHERE `dsc_orgao` = "Ministério das Cidades"
...> GROUP BY `sig_uf`
...> ORDER BY SUM(`investimento_total`) DESC
...> LIMIT 5;

UF Investimento
SP 171158656371.58
RJ 49089877209.84
MG 40175377781.16
PR 27928207915.2
RS 26717924226.26

Ou, então, quanto dos recursos do PAC são gastos com saúde, seja através do Ministério da Saúde, ou através da Fundação Nacional da Saúde?

sqlite> SELECT `dsc_orgao`,SUM(`investimento_total`)
...> FROM `pac`
...> WHERE `dsc_orgao` LIKE "%Saúde"
...> GROUP BY `dsc_orgao`
...> ORDER BY SUM(`investimento_total`) DESC;

Órgão Investimento
Fundação Nacional da Saúde 7962934548.78002
Ministério da Saúde 768838158.779999

Aqui utilizamos o WHERE para restringir os resultados à apenas aos órgãos que possuem Saúde em seu nome, como é o caso da Fundação Nacional da Saúde (Funasa) e do Ministério da Saúde.

Podemos, ainda, saber quais são os estados mais beneficiados pelo Ministério da Saúde:

sqlite> SELECT `sig_uf`,SUM(`investimento_total`)
...> FROM `pac`
...> WHERE `dsc_orgao` LIKE "%Saúde"
...> GROUP BY `sig_uf`
...> ORDER BY SUM(`investimento_total`) DESC
...> LIMIT 5;

UF Investimento
PA 785712631.78
CE 746274322.939999
MG 722866871.88
PB 618124738.899999
BA 546496245.64

Visualização gráfica

Uma informação muito especial que temos nesse conjunto de dados do PAC, é a latitude e longitude de onde os recursos estão sendo empregados. Essa informação é especialmente interessante, quando utilizamos alguma ferramenta de plotagem de mapas, como é o caso do Google Maps Engine.

A primeira coisa que precisamos fazer, é transformar os dados em alguma informação que queremos no mapa. Por exemplo, quais são as 20 obras que mais receberam recursos?

SELECT
`sig_uf` AS `UF`,
`dsc_titulo` AS `Obra`,
"R$ " || MAX(`investimento_total`) AS `Investimento Total`,
(`val_lat` || "," || `val_long`) AS `Coordenadas`
FROM `pac`
WHERE `investimento_total` != "" AND `val_lat` != "" AND `val_long` != ""
GROUP BY `sig_uf`
ORDER BY `sig_uf`;

Após encontrar a informação que desejamos, precisaremos exportá-la para CSV. Isso pode ser feito através do próprio SQLite, por exemplo:

sqlite> .headers on
sqlite> .mode csv
sqlite> .output top20-obras.csv
sqlite> SELECT
...> `sig_uf` AS `UF`,
...> `dsc_titulo` AS `Obra`,
...> "R$ " || MAX(`investimento_total`) AS `Investimento Total`,
...> (`val_lat` || "," || `val_long`) AS `Coordenadas`
...> FROM `pac`
...> WHERE `investimento_total` != "" AND `val_lat` != "" AND `val_long` != ""
...> GROUP BY `sig_uf`
...> ORDER BY `sig_uf`;

  • A primeira linha, .headers on, diz para o SQLite que queremos os cabeçalhos no arquivo CSV;
  • A segunda linha, .mode csv, diz para o SQLite que queremos que o resultado seja no formato CSV;
  • A terceira linha, .output top20-obras.csv, diz para o SQLite que, em vez de mostrar os resultados na tela, ele deve salvar em disco, no arquivo top20-obras.csv;
  • A quarta linha é a informação que desejamos obter dos dados.

Com o arquivo top20-obras.csv, vamos até o Google Maps Engine e vamos fazer o upload do arquivo. Para isso, vamos clicar na pastinha que fica ao lado do botão Add Layer:

import

Após clicar no botão, uma tela será exibida para a escolha do arquivo. Assim que selecionamos o arquivo top20-obras.csv, a seguinte tela é exibida:

placemark

Nessa tela, devemos selecionar o item Coordenadas. É onde o Google Maps Engine irá colocar os pins. Após selecionar a coluna Coordenadas e clicar no botão Continue, uma nova tela será exibida:

title

Nessa tela devemos escolher a coluna Obra, que será utilizada para o título dos pins no mapa. Agora é só clicar no botão Finish e pronto, o resultado será semelhante ao abaixo:

result

Se clicarmos em algum dos pins, veremos algo semelhante ao abaixo:

result2

Veja os dados no Google Maps Engine

Resultados

Como podemos ver, obter informações de pequenos conjuntos de dados é, de certa forma, bastante simples. Tudo o que precisamos, são algumas perguntas. Sobre nossa pergunta inicial, descobrimos que o estado de São Paulo é o que recebeu a maior quantidade de recursos. Descobrimos também que a maior parte dos recursos do PAC são utilizados em obras do Ministério das Cidades. Além disso, descobrimos que os maiores investimentos na área da saúde são destinados ao estado do Pará. Por fim, conseguimos plotar um mapa com um conjunto de informações obtidas através dos dados, utilizando as coordenadas geográficas para identificar as obras.

Isso tudo foi feito utilizando um conjunto simples de dados, com apenas uma tabela. Alguns conjuntos de dados são mais complexos, muitas vezes distribuídos em várias tabelas. É claro que as consultas vão ficando mais complexas, conforme o volume de dados e a quantidade de tabelas, mas é possível extrair informação de várias tabelas ao mesmo tempo, apenas cruzando informações entre elas.

Apêndice

Saiba mais como lidar com dados: