Utilizando SQL para pequenas análises de dados

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

Mais do que uma tradução, este artigo trata-se, 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 artigo, serão utilizados dados disponibilizados pelo governo Brasileiro sobre o PAC (Programa de Aceleração do Crescimento).

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:

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 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:

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, utilizados neste artigo e no original, não estejam disponíveis no seu ambiente, você poderá fazer o download da base de dados pac.sqlite através desse link.

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 20 linhas e, finalmente, ordenamos do maior valor para o menor valor. O resultado dessa consulta segue abaixo:

Podemos utilizar outras funções também, por exemplo, quais são as 5 á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;

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, por exemplo, 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;

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;

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;

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:

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:

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:

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:

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

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

Para saber mais sobre como lidar com dados, veja os links a seguir:

• Mais sobre CSV – Comma Separated Variables.
• Extensão para o Google Chrome que permite a visualização de arquivos CSV.
• Você também pode brincar com SQL online, utilizando o SQLFiddle
  • Ou até uma implementação do SQLite em Javascript
• Existe diversos recursos online para aprender SQL, por exemplo (em inglês):
  • A Gentle Introduction to SQL using SQLite by Toby Thibodeaux
  • Introducing SQL for Lightweight Data Manipulation by Tim McNamara
  • Command Line Shell For SQLite by D. Richard Hipp et al.