Arquivo da tag: LEFT JOIN

Usando SQL Lite para encontrar os desertos de notícias no Brasil

Planilhas eletrônicas resolvem 90% dos casos de análise de dados numa redação jornalística, mas algumas vezes nos deparamos com desafios que, embora pouco complexos, carecem de soluções simples nestes programas.

A comparação entre duas listas de variáveis é um desses casos. Comandos como VLOOKUP, INDEX e MATCH podem ajudar, em especial quando o objetivo é encontrar duplicatas, mas o processo pode ficar complicado se quisermos, por exemplo, descobrir quais ítens de uma lista A estão ausentes de uma lista B.

Usando um programa como DB Browser e a linguagem SQL Lite, porém, é relativamente simples fazer esse tipo de análise.

Digamos, por exemplo, que seja necessário criar uma lista contendo os desertos de notícias do Brasil, a partir do banco de dados do Atlas da Notícia. Embora o projeto gentilmente ofereça uma planilha com os município onde há um jornal impresso ou digital, entre outros dados úteis, não há uma lista de todos os municípios sem nenhuma ocorrência. Como uma das pautas mais interessantes a serem realizadas em cima do Atlas da Notícia é justamente sobre as localidades carentes de imprensa, é útil comparar os municípios com veículos e os sem veículos.

LEFT JOIN

Esse tipo de comparação pode ser realizada com o comando LEFT JOIN na linguagem SQL Lite, desenvolvida especificamente para análise de bancos de dados.

Para isso, você vai precisar do banco de dados do Atlas da Notícia e de uma lista de todos os municípios brasileiros, como esta fornecida pelo IBGE. Vai precisar, também, é claro, de um programa como o DB Browser.

Antes de começar, porém, é preciso verificar se os dados na planilha do IBGE e nas tabelas do Atlas são compatíveis. Abrindo o arquivo “atlas.db” no DB Browser e a tabela “atlas”, que contém os dados completos, podemos verificar a existência de um campo “cidade”. Estes são os municípios nos quais foi informada a existência de pelo menos um jornal impresso ou ciberjornal. No arquivo ZIP do IBGE do ano de 2015, o mais atual, há uma planilha chamada “RELATORIO_DTB_BRASIL_MUNICIPIO”, contendo uma lista de todos os municípios brasileiros na coluna I. Observando ambos os arquivos, podemos perceber um problema: a lista do Atlas registra os nomes dos municípios em caixa alta, enquanto a do IBGE usa nomes capitulados.

Se tentarmos um LEFT JOIN dessa forma, nada será encontrado, porque o SQL Lite é sensível à caixa das letras — ou, pelo menos, não encontrei uma forma de tangenciar essa disparidade. Portanto, o primeiro passo é transformar toda coluna I para caixa alta no Google Spreadsheets, Excel ou algum outro software de sua preferência.

No Google Spreadsheets, basta digitar, na célula J2, a seguinte fórmula:

=UPPER(I:I)

Este comando manda reproduzir, em caixa alta, o que estiver na coluna I. Ao clicar “enter”, o nome da cidade da coluna I deve aparecer em caixa alta na coluna J. Agora, como fazer com o restante dos mais de 5 mil municípios da lista? Basta clicar duas vezes no quadradinho azul que surge ao selecionar a célula J2:

Google Sheets - Comando Upper case

Após verificar se o comando funcionou, baixe a planilha no formato CSV, mude para o DB Browser, vá em Arquivo>Importar>Importar tabela a partir de CSV. Aqui há uma pegadinha: por padrão, o DB Browser buscar arquivos em formato TXT. Portanto, na tela de busca, mude para “todos os arquivos”. Ache o seu CSV e clique em abrir, após o que você será apresentado a uma pré-visualização da tabela. Verifique se os dados estão íntegros — por exemplo, se os acentos estão sendo corretamente interpretados — e, caso não estejam, tente mudar a codificação, que normalmente deveria ser UTF-8.

Agora, com poucas linhas de comando, podemos comparar os municípios nas tabelas “atlas” e “municipiosBR”, para extrair os nomes de todas as localidades brasileiras ausentes da primeira. A imagem abaixo mostra os tipos de JOIN possíveis na linguagem SQL:

Tipos de JOIN SQL

No caso em discussão, queremos um JOIN dos conjuntos A (“municipiosBR”) e B (“atlas”) sem a intersecção de A+B. Noutras palavras, queremos descobrir quais cidades pertencem apenas ao conjunto A (municípios sem veículos de imprensa reportados ao Atlas da Notícia), mas não ao B (municípios com veículos de imprensa reportados ao Atlas da Notícia).

Para realizar esta operação, basta abrir a aba “executar SQL” no DB Browser e digitar a fórmula abaixo:

SELECT atlas.cidade, municipiosBR.field11
 FROM municipiosBR
 LEFT JOIN atlas
 ON cidade = field11
 WHERE atlas.cidade IS NULL;

O comando verifica quais municípios brasileiros segundo a lista do IBGE podem ser encontrados na base de dados do Atlas da Notícia. Os valores não encontrados são classificados como nulos (NULL), então o último comando (WHERE) serve para retornar uma lista só dos valores de A nulos em B. O resultado deve se parecer com a imagem abaixo:

DB Browser LEFT JOIN

Finalmente, basta clicar no ícone azulado logo abaixo da tabela, que parece ser uma página com um disquete sobreposto, para exportar o resultado em formato CSV, o qual pode ser tratado em planilhas eletrônicas. A planilha resultante deste exercício está aqui:

Desertos de notícias no Brasil

O problema, como se pode perceber ao olhar para a planilha, é a ausência de uma informação crucial: a UF de cada município. Uma abordagem possível seria, ao limpar a lista do IBGE, filtrar as localidades pela UF ou região de interesse, salvar uma planilha específica para ela e, então, proceder ao LEFT JOIN. Provavelmente é possível fazer este filtro extra usando comandos SQL, mas para noobs como eu pode ser mais simples fazer assim. Além disso, o formato das colunas referentes à UF é diferente nas duas tabelas, então seria necessário transformar uma delas para as tornar compatíveis.