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

UF Investimento
SP171740111838.0
RJ49473735257.08
MG41298926989.44
PR28724574100.32
RS27157499074.68
BA24401562534.44
DF16320196665.42
GO15073583842.54
PE13666131507.18
SC13633033294.1
CE13033312986.18
PA12473700610.37
MA9472695230.34002
AM8926094516.68
ES7630901437.12
MT6906802672.1
RN6465137159.22
MS6189088362.3
SE5934839614.14
PB5509364605.14001

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;

Órgão Investimento Total Investimento Médio
Ministério das Cidades490643623250.72139456664.5155385
Fundação Nacional da Saúde7962934548.78002510509.972354149
Ministério da Educação3594551103.33999305659.10742687
Ministério da Cultura775253231.561076740.59938889
Ministério da Saúde768838158.77999949058.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, 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;

UF Investimento
SP171158656371.58
RJ49089877209.84
MG40175377781.16
PR27928207915.2
RS26717924226.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úde7962934548.78002
Ministério da Saúde768838158.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
PA785712631.78
CE746274322.939999
MG722866871.88
PB618124738.899999
BA546496245.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

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

9 thoughts on “Utilizando SQL para pequenas análises de dados”

  1. Cara, Está bom demais, parabéns. Certamente será bastante útil tanto para muitos. Você foi modesto no título, pois o exemplo aplicado no Google Maps está sensacional.

    Novamente parabéns!

    ;)

  2. O colega João Batista Oliveira Neto me enviou este link para me ajudar, pois não sabia por onde começar a análise dos dados. Artigo muito bom. Parabéns!

Deixe uma resposta

O seu endereço de email não será publicado Campos obrigatórios são marcados *