DEV Community: Base dos Dados

Entenda como nossa base de Diretórios Brasileiros facilita sua vida

Crislane A. Souza — Mon, 16 Aug 2021 14:14:34 +0000

TL;DR

Neste artigo vamos apresentar nossa base de Diretórios Brasileiros, que está disponível no nosso datalake público (a BD+), onde disponibilizamos diversas bases de dados públicas já tratadas, organizadas e integradas para análise. Também vamos demonstrar como essa base facilita o cruzamento entre tabelas de diferentes conjuntos de dados e como você pode aplicá-la em sua análise.

A Base de Diretórios Brasileiros

Essa base é uma referência de centralização de informações de unidades básicas para análises e funciona como um perfil completo de entidades como município, escola, UF, setores censitários e mais. São tabelas que ligam diversos códigos institucionais e informações de diferentes entidades brasileiras.

Isso é importante porque resolve o problema de não existir um identificador único para municípios entre as instituições brasileiras, resolve a mudança de IDs e nomes com typos entre anos e instituições, além de IDs novos de municípios que são criados ao longo do tempo.

Por exemplo, para municípios, essa base liga conjuntos de organizações como IBGE, Receita Federal, Tribunal Superior Eleitoral (TSE), Banco Central do Brasil, comarcas, região de saúde, etc.

Cada tabela dessa base representa uma entidade do nosso datalake público, como UF, municipio, escola, distrito, setor_censitario, categorias CID-10 e CID-9, CBO-2002 e CBO-1992, dentre outras.

Os diretórios criam naturalmente relações entre as diferentes entidades. Por exemplo, a tabela município possui uma coluna sigla_uf, ou seja, nela identificamos a qual UF o município pertence. O mesmo vale para escola, podemos identificar em qual município determinada escola está localizada.

Para exemplificar como a base de Diretórios Brasileiros facilita o cruzamento de diferentes conjuntos, preparamos os seguintes exemplos de aplicações que demonstram como você pode lançar mão dessa ferramenta em suas análises, apenas com uma query de SQL no BigQuery.

Exemplo de aplicação

Indicadores de Mobilidade e Transportes

No primeiro exemplo, cruzamos a tabela municipio da base de Diretórios Brasileiros com a tabela tempo_deslocamento_casa_trabalho, da base de Indicadores de Mobilidade e Transporte da Mobilidados, que contém dados sobre tempo médio de deslocamento casa-trabalho, assim como de percentual de pessoas que gastam mais de uma hora neste deslocamento no ano de 2010.

Para isso, usamos como chave primária a coluna id_municipio. O objetivo é adicionar as colunas regiao e municipio no novo conjunto de dados. Assim, além de saber o nome dos municípios, também é possível fazer o agrupamento por cidade, grande região ou por estado e ver qual destes têm o tempo médio de deslocamento maior ou menor.

Confira abaixo a Query utilizada:

WITH deslocamento_casa_trabalho as
(SELECT ano, id_municipio, tempo_medio_deslocamento
FROM `basedosdados.br_mobilidados_indicadores.tempo_deslocamento_casa_trabalho`)
SELECT t1.id_municipio, regiao, nome as municipio, sigla_uf, tempo_medio_deslocamento
FROM deslocamento_casa_trabalho t1
JOIN `basedosdados.br_bd_diretorios_brasil.municipio` t2
ON t1.id_municipio = t2.id_municipio

Dados de importações e exportações brasileiras

No segundo exemplo, usamos a tabela pais da base de Diretórios, com a tabela municipio_importacao da base Comex Stat, que contém dados detalhados das exportações e importações brasileiras, extraídas do SISCOMEX. Esta tabela específica aborda dados de importação, detalhados por município e empresa importadora.

Neste exemplo, usamos como chave primária a coluna id_pais para extrair da base de Diretórios o nome do país.
Ao rodar query, teremos como resultado, além do ID do País e Valor da Importação, o nome do país, ou seja, os destinos das importações do Brasil no ano de 2020.

Você pode utilizar a query abaixo para acessar o destino das importações do Brasil no ano de 2020:

WITH importacao as 
(SELECT ano, id_pais, sigla_uf, SUM(valor_fob_dolar) AS importacao
FROM `basedosdados-dev.br_me_comex_stat.municipio_importacao` 
WHERE ano = 2020
GROUP BY ano, sigla_uf, id_pais
ORDER BY importacao DESC)
SELECT t1.ano, t1.id_pais, nome, importacao, sigla_uf
FROM importacao t1
JOIN `basedosdados-dev.br_bd_diretorios_brasil.pais` t2 
ON t1.id_pais = t2.id_pais
GROUP BY ano, sigla_uf, nome, id_pais, importacao
ORDER BY importacao DESC

```



Vale lembrar que o cruzamento entre bases não é realizável apenas porque a base de Diretórios funciona como uma espécie de dicionário. Além deste fator, existe o **padrão de qualidade da Base dos Dados**, ou seja, nós compatibilizamos todos os dados para que possam ser cruzados entre tabelas. A limpeza das bases disponíveis em nosso datalake público envolve um rigoroso processo de padronização e compatibilização de dados. 

Ficou com alguma dúvida sobre nossa base de Diretórios Brasileiros, ou como aplicá-la? Deixe seu comentário ou procure algum de nossos assistentes de dados no canal *#dados* em nossa [comunidade do Discord](https://discord.com/invite/huKWpsVYx4). 

A Base dos Dados é uma iniciativa sem fins lucrativos e nossa equipe trabalha sempre para facilitar seu trabalho ou pesquisa. Nós criamos nosso datalake público e padrão de dados para que *a distância entre você e sua análise seja apenas uma boa pergunta*. Que tal nos ajudar nessa empreitada? Você pode apoiar a BD mensalmente por [aqui](https://apoia.se/basedosdados) ou via PIX com nossa chave CNPJ 42494318000116. Seu apoio nos ajuda a chegar mais perto da nossa missão de universalizar o acesso a dados de qualidade no Brasil!

Tutorial Power BI

Victor Viana — Fri, 30 Jul 2021 00:14:45 +0000

TL;DR

O Power BI é uma das tecnologias mais populares para o desenvolvimento de dashboards com dados relacionais, e a Base dos Dados é um dos maiores data lakes públicos do Brasil. Essa combinação é o ambiente perfeito para sua análise e a visualização de dados. Neste artigo, vou te mostrar como é fácil ter acesso às bases de dados da BD para uso no PBI, além de explicar o passo a passo.

Conectar ao Google BigQuery

O Google Bigquery é um serviço de banco de dados em nuvem do Google, onde os conjuntos de dados da Base dos Dados estão armazenados dentro de um datalake público - chamado basedosdados. Para acessar os dados, necessário criar um projeto (gratuito) no BigQuery caso já tenha um projeto siga para o próximo passo, caso contrário, elaboramos dois tutoriais para te ajudar de forma simples e rápida:

Artigo
Video

Conectar dados no PowerBI (PBI)

Nesse projeto vamos mostrar como conectar os dados da evolução do PIB dos municípios (base fato) e as informações sobre os municípios (base dimensão) no Power BI para elaboração de análises. Essa base é usada de exemplo, mas o tutorial serve para qualquer outra base de interesse que esteja no datalake.

Buscando os dados no site

Para acessar os dados na interface do BigQuery utilizamos queries (consultas) em SQL, uma das linguagens de programação mais básicas e úteis para quem trabalha com dados. No site da Base dos Dados você pode procurar por qualquer base e copiar direto o código SQL, disponível na página da tabela selecionada em "Acesse os dados via BigQuery", para usar no editor de SQL do Google BigQuery, como mostra no exemplo abaixo. Para aprender mais sobre como usar a linguagem, recomendo o curso gratuito da Udacity, ou o próprio tutorial do BigQuery.

Selecionando os dados no BigQuery
Ainda no site, você pode clicar no botão "Consultar no BigQuery" para ser redirecionado ao datalake. A interface do BigQuery é diferente do site pois é um serviço mantido pelo próprio Google, explicamos mais sobre cada elemento dessa interface neste artigo.

Clicamos então em "Criar nova consulta" e no editor que aparece na tela basta colar o código copiado e rodar. Note que no código explicitamos LIMIT 100 para puxar somente as 100 primeiras linhas do dado, mas você pode mudar esse parâmetro (ou removê-lo) para puxar mais linhas - pedimos somente que tome cuidado com bases muito grandes (RAIS, Censo Populacional), pois puxar todos os dados de uma vez não só é demorado como também gasta bastante processamento, o que pode acarretar em custos.

SELECT 
    pib.id_municipio, --selecionar id do municipio
    pop.ano, -- população do muunicipio
    pib.PIB / pop.populacao AS pib_per_capita -- calculo do PIB per capita
FROM `basedosdados.br_ibge_pib.municipio` AS pib -- selecionar base de pib dos municipios
JOIN `basedosdados.br_ibge_populacao.municipio` AS pop -- join com a base de população
ON pib.id_municipio = pop.id_municipio AND pib.ano = pop.ano
LIMIT 100

Salvando os dados num projeto privado
Salve a tabela obtida clicando em "Salvar".
Você pode salvar a consulta ou a visualização

O BigQuery irá te pedir para criar um conjunto de dados onde você pode salvar essa tabela (caso não tenha um). Se já tem maior familiaridade com o PBI, é algo muito similar aos conjuntos de dados do PBI. Dê um nome intuitivo ao seu conjunto No caso da Base dos Dados, organizamos os nomes de conjuntos por abrangência geográfica, instituição e tema do dado, você pode ver mais sobre nossas regras de nomenclatura aqui. Esse conjunto é essencialmente uma "pasta"onde ficarão todas as tabelas do seu projeto. Neste exemplo, escolhemos o nome "tutorial" de forma mais genérica.

Em seguida, selecione o conjunto criado para salvar a base e escolha um nome para sua tabela e clique em Salvar. Simples assim 😊.

Agora seu projeto irá aparecer na barra lateral esquerda. Clique na setinha do lado do nome do seu projeto, lá vai aparecer seu conjunto de dados com a tabela que você salvou. Se não aparecer, atualize a página.

Para salvar outra tabela com as informações de municípios (nome, UF, etc), faça o mesmo processo com a query abaixo. Vamos chamar essa tabela de dMunicipio, que será salva no mesmo conjunto chamado tutorial.

SELECT 
    id_municipio,
    municipio,
    id_uf,
    uf,
    regiao,
FROM `basedosdados.br_bd_diretorios_brasil.municipio`

Importando os dados para o PBI

Abra o PBI
Vá em Obter Dados -> Mais

Procure pelo Google BigQuery -> Conectar
Entre com sua conta do Google
A mesma que fez as consultas no BigQuery. Caso você entre com outra conta não será possível conectar.

Permita o acesso ao seu PBI
Volte ao PBI e clique em conectar
Selecione a pasta com o nome do seu conjunto de dados
Selecione as tabelas
Clique em carregar
Selecione Importar
Para maioria dos casos não é necessário estar conectado diretamente, além disso não você não fica dependente da conexão com o banco.

Pronto, agora você tem acesso às suas bases da BD para criar seu dashboard. :)

Para qualquer sugestão ou dúvidas, fique a vontade para comentar, me procurar pelas redes ou na comunidade da Base dos Dados no Discord.

O Brasil nas Olimpíadas

lucasnascm — Fri, 23 Jul 2021 16:42:53 +0000

TL;DR

Hoje começa mais uma edição das Olimpíadas, mas você sabia que os jogos da Era Moderna tiveram sua primeira edição em 1896? Nesse texto vamos apresentar dados históricos dos Jogos Olímpicos, que já estão limpos, tratados e disponíveis no datalake público da Base dos Dados. Os microdados das Olimpíadas trazem informações sobre os jogos, cidade sede, as delegações, atletas e suas características, além dos esportes, suas diferentes modalidades e medalhistas também.

O script de análise foi executado em R usando nosso pacote de dados. A ideia aqui é mostrar um panorama da performance brasileira nas edições em que a delegação brasileira esteve presente. Vale lembrar que, com a BD+, você também pode acessar esses dados em Python ou diretamente pelo BigQuery.

library("basedosdados")
library("tidyverse")
library("gridExtra")
library("dplyr")

olimpiadas <- basedosdados::read_sql("SELECT * 
                                      FROM `basedosdados.mundo_kaggle_olimpiadas.microdados` 
                                      WHERE delegacao = 'BRA'", billing_project_id='input-dados')

Presença confirmada

O Brasil teve sua primeira participação em uma edição dos Jogos Olímpicos em 1900, na cidade de Paris. O único atleta da delegação era Adolphe Klingelhoeffer, que competia nas provas de atletismo. A próxima edição com representantes brasileiros foi em 1920 e, desde então, temos marcado presença em diferentes provas. Abaixo você pode conferir o panorama da participação brasileira ao longo das edições dos jogos, com número de atletas e modalidades, e veja também o código utilizado para produzir essa visualização.

counts <- olimpiadas %>% filter(edicao == "Summer") %>% 
  group_by(ano) %>%
  summarize(
    atletas = length(unique(id_atleta)),
    eventos = length(unique(evento))
  )

p1 <- ggplot(counts, aes(x=as.numeric(ano), y=as.numeric(atletas))) +
  geom_point() +
  scale_y_continuous(limits=c(0,470)) +
  labs(title="Participação brasileira nos Jogos Olímpicos", y="Total de atletas") +
  theme(plot.title = element_text(hjust = 0.5)) +
  geom_line() + xlab("")
p2 <- ggplot(counts, aes(x=as.numeric(ano), y=as.numeric(eventos))) +
  geom_point() +
  scale_y_continuous(limits=c(0,250)) +
  labs(x="Anos", y="Modalidades") +
  geom_line()
grid.arrange(p1, p2, ncol=1)

O recorde de participação brasileira foi em 2016, competindo em casa, com 462 atletas disputando em 222 provas distintas. As edições passadas foram bem diferentes, a média de participação das 5 edições anteriores a 2016 foi de 236 atletas. Esse ano contamos com 302 atletas em Tokyo, segundo dados do Comitê Olímpico Brasileiro.

Quadro de Medalhas

Nas próximas duas semanas, todos os jornais e canais esportivos se concentrarão nos melhores momentos da nossa delegação e o quadro geral de medalhas será um destaque nos noticiários. Analisando o desempenho dos atletas nos jogos em que participou, o Brasil acumula 30 medalhas de ouro, 36 de prata e 63 bronzes. O gráfico abaixo mostra os esportes, nas modalidades masculina e feminina somados, em que o Brasil divide suas conquistas.

No nosso podium, Judô, Vela e Atletismo são os recordistas no total de medalhas com 22, 18 e 16, respectivamente. Os dados permitem identificar quem são os atletas campeões e os eventos em que eles garantiram a vitória. Nas modalidades do Judô 🥋, as mulheres ganharam 3 medalhas de bronze e 2 de ouro, enquanto os homens trouxeram para casa 12 bronzes, 3 pratas e 2 ouros. Será que na edição Tokyo 2020 esse resultado se altera? O script para desenvolver o gráfico é:

medalha_counts <- olimpiadas %>% filter(!is.na(medalha))%>%
  group_by(ano, esporte, evento, medalha) %>% 
  summarize(Count=length(unique(medalha)))
#ordena a tabela
medalha_counts$medalha <- factor(medalha_counts$medalha, levels=c("Gold", "Silver", "Bronze"))

#total de medalhas por modalidade esportiva ao longo dos anos
lev <- medalha_counts %>%
  group_by(esporte) %>%
  summarize(Total=sum(Count)) %>%
  arrange(Total) %>%
  select(esporte)
medalha_counts$esporte <- factor(medalha_counts$esporte, levels=lev$esporte)

#criação do gráfico
ggplot(medalha_counts, aes(x=esporte, y=Count, fill=medalha)) +
  geom_col() +
  coord_flip() +
  scale_fill_manual(values=c("gold1", "gray70", "gold4")) +
  ggtitle("Total de medalhas brasileiras por esporte nos Jogos Olímpicos") +
  theme(plot.title = element_text(hjust = 0.5))

Mulheres nas Olimpíadas

A participação feminina brasileira nos jogos acontece pela primeira vez somente em 1932 - 36 anos após a primeira edição - e a lacuna entre homens e mulheres é notória. Antes dos anos 2000, a razão de homens para mulheres nos jogos era de 20% em média: ou seja, para cada mulher competindo na delegação, existiam outros 5 homens. Somente no novo milênio que essa desigualdade quase se anula, a nível Brasil. Em 2016, foram 207 mulheres e 255 homens competindo em 31 esportes diferentes.

O código de análise do total de participação por sexo ao longo dos anos é fácil.

#filtrando para edição de verão dos Jogos
sexo <- olimpiadas %>% filter(edicao == "Summer")

#série do total de atletas por sexo
counts_sex <- sexo %>% 
  group_by(ano, sexo) %>%
  summarize(atletas = length(unique(id_atleta)))
counts_sex$ano <- as.integer(counts_sex$ano)

#criação do gráfico
ggplot(counts_sex, aes(x=ano, y=atletas, group=sexo, color=sexo)) +
  geom_point(size=2) +
  geom_line()  +
  scale_color_manual(values=c("orange","darkgreen")) +
  labs(title = "Participação masculina e feminina nas Olimpíadas") +
  theme(plot.title = element_text(hjust = 0.5))

A Base dos Dados é uma iniciativa sem fins lucrativos, open source e que busca facilitar e fomentar a produção de conhecimento no Brasil. Nosso intuito aqui foi instigar a você analisar mais. Muitas perguntas podem ser respondidas (e outras tantas serem feitas) analisando essa história. Podemos continuar conversando sobre dados em nossa comunidade no Discord: https://discord.com/invite/huKWpsVYx4.

Que a força esteja com nossos atletas! Vai Brasil!

Queremos saber #OndeEstãoDadosLGBTQIA

Giovane Caruso — Fri, 25 Jun 2021 15:24:27 +0000

TL;DR

Nesse texto, vamos apresentar nossa campanha em busca de dados com informações sobre a comunidade LGBTQIA+ e explicar como é possível participar e nos ajudar a disponibilizá-los em nosso datalake público. Também vamos apresentar bases de dados que já mapeamos e que entrarão para o datalake, além de demonstrar como acessá-las.

Queremos saber #OndeEstãoDadosLGBTQIA

Sabemos que a riqueza de dados é fundamental para a elaboração de políticas públicas que ajudem a combater a violência e o preconceito contra a comunidade LGBTQIA+. No intuito de identificar e trazer acessibilidade para esses dados, a Base dos Dados está lançando esta campanha para encorajar todos que trabalham ou utilizam dados a nos ajudar a encontrar e catalogar essas informações.

Nosso objetivo é mapear o máximo possível de bases, para que nossa equipe trate, limpe e disponibilize esses dados em nosso datalake público, facilitando o acesso e análise dessas informações.

Até então, tivemos muito mais facilidade de encontrar bases estrangeiras, geralmente a nível de país, do que bases de níveis nacionais. Nossa equipe já entrou em contato com o Grupo Gay da Bahia, principal ONG que contabiliza mortes LGBTQIA+ no país; com a Ditch The Label, ONG estrangeira que contabiliza mortes, ofensas e abusos sofridos por pessoas trans; e com a própria Transrespect x Transphobia, ONG estrangeira que contabiliza assassinatos trans ao redor do mundo.

Infelizmente, só obtivemos uma resposta negativa da Ditch The Label quando requisitamos dados brutos para disponibilização. É importante ressaltar que é urgente a melhor disponibilização de dados da temática LGBTQIA+ e isso pode começar por você!

Como você pode ajudar?

Se você já conhece ou encontrou alguma base de dados interessante que aborda esse tema, você pode acessar nossa planilha de prioridade de bases, incluir uma linha por tabela do conjunto de dados e preencher os campos cuidadosamente, caso ela não esteja já listada.

Uma outra maneira de ajudar é divulgando esses dados em suas redes sociais e marcar a @basedosdados com a hashtag #OndeEstãoDadosLGBTQIA, para aumentar ainda mais o alcance da campanha.

Conjuntos de dados já mapeados pela Base dos Dados

Nossa iniciativa mapeou três bases de dados que serão disponibilizadas já limpas e tratadas em nosso data lake público. Você já pode acessar as tabelas através da biblioteca basedosdados no R , em Python ou diretamente pelo GoogleCloud, utilizando os códigos descritos neste texto. Confira a seguir os conjuntos de dados já tratados:

Relatório Transrespect versus Transphobia Worldwide

O relatório relata os números totais de homicídios de pessoas trans no mundo inteiro e contém tabelas a diferentes níveis de agregação, demonstrando mortes totais a nível de país, de local da ocorrência do crime e de como o crime ocorreu, a causa mortis do homicídio. O recorte aborda dados de 2008 até 2018, em países de todos os continentes.

Utilize as queries de SQL a seguir para acessar as tabelas:

#Assassinatos por país
SELECT * FROM `basedosdados.mundo_transrespect_transphobia.pais`

#Causa do óbito
SELECT * FROM `basedosdados.mundo_transrespect_transphobia.obito`

#Local de assassinato
SELECT * FROM `basedosdados.mundo_transrespect_transphobia.local`

Caso prefira pegar direto pelo Python:

import basedosdados as bd
bd.read_sql(query = 'SELECT *
FROM `basedosdados.mundo_transrespect_transphobia.pais`',
billing-project-id = 'seu-project-id')

Com esses dados criamos a seguinte visualização que compara o número de homicídios de pessoas trans em diferentes países ao longo dos anos:

Pesquisa Online da Agência dos Direitos Fundamentais da União Europeia (FRA):

A pesquisa foi realizada com pessoas LGBTQIA+, em 2012, na maioria dos países do bloco político-econômico. A pesquisa é dividida em um conjunto de tópicos que dão origem a diferentes tabelas. Os tópicos levam a perguntas sobre violência, sobre o cotidiano LGBTQIA+, direitos da comunidade, entre outras. Os tópicos são agrupados a nível de cada país e grupo LGBTQIA+.

Utilize as queries de SQL a seguir para acessar as tabelas da pesquisa:

 SELECT * FROM `basedosdados.eu_fra_lgbt.consciencia_direitos`
 SELECT * FROM `basedosdados.eu_fra_lgbt.cotidiano`
 SELECT * FROM `basedosdados.eu_fra_lgbt.discriminacao`
 SELECT * FROM `basedosdados.eu_fra_lgbt.transgenero_especifico`
 SELECT * FROM `basedosdados.eu_fra_lgbt.violencia_abuso`

Caso não queira nem sair do R, você pode sempre acessá-las direto do software:

library(basedosdados)
basedosdados::read_sql('SELECT *
FROM `basedosdados.eu_fra_lgbt.consciencia_direitos',
billing-project-id = 'seu-project-id')

Relatórios anuais de mortes LGBTI+ no Brasil, pelo Grupo Gay da Bahia

Relatórios que informam o total de mortes da comunidade no Brasil, agregados por ano, por grupo LGBTQIA+, por estado de ocorrência do homicídio, entre outros tópicos. Os dados foram coletados, em sua maioria, manualmente. Caso você tenha contato de alguém da iniciativa, nos informe, pois estamos procurando os dados em formato tabular para agilizar e automatizar a disponibilização dessas bases.

Utilize as queries em SQL a seguir para acessar as tabelas:

#Uma série por ano de todo o Brasil
SELECT * FROM `basedosdados.br_ggb_relatorio_lgbtqi.brasil`

#Por raça/cor
SELECT * FROM `basedosdados.br_ggb_relatorio_lgbtqi.raca_cor`

#Por grupo LGBTQIA+
SELECT * FROM `basedosdados.br_ggb_relatorio_lgbtqi.grupo_lgbtqia`

#Por causa do óbito
SELECT * FROM `basedosdados.br_ggb_relatorio_lgbtqi.brasil`

#Por local do assassinato
SELECT * FROM `basedosdados.br_ggb_relatorio_lgbtqi.local`

Como sempre, você pode acessar os dados direto do seu console de R. Para analisar os assassinatos a nível de raça/cor, você pode rodar:

basedosdados::read_sql('SELECT *
FROM `basedosdados.br_ggb_relatorio_lgbtqi.raca_cor`',
billing_project_id = "seu-project-id")

Com esses dados, criamos também este gráfico que demonstra o número de homicídios de pessoas LGBTQIA+ ao longo dos anos, separado por raça.

A Base dos Dados é uma iniciativa sem fins lucrativos, open source e que busca facilitar e fomentar a produção de conhecimento no Brasil. Acreditamos que dados públicos devem ser transparentes e acessíveis a toda a sociedade civil.

Se você tiver alguma dúvida sobre a campanha ou sobre como adicionar dados que você encontrou na planilha de prioridade de bases, entre em nossa comunidade no Discord e fale conosco: https://discord.com/invite/huKWpsVYx4

Esse texto foi produzido com ajuda do Matheus Valentim, da equipe de dados da BD, e da Fernanda Scovino, coordenadora de comunicação. 💚

Explorando o Censo Escolar com a Base dos Dados

Matheus Valentim — Fri, 04 Jun 2021 03:46:07 +0000

TL;DR

Nesse texto, vamos dissecar o novo conjunto de dados da Base dos Dados: o Censo Escolar. Primeiro, vamos introduzir as quatro diferentes tabelas da base, apresentando algumas de suas variáveis. Depois, vamos mostrar alguns pontos de contato interessantes com outras tabelas disponíveis em nosso datalake público e perguntas que podem surgir dessas interações.

O Censo - e suas dificuldades

O Censo Escolar é uma pesquisa anual, onde cada escola do país responde a diversas perguntas sobre a própria escola, sobre as turmas, os professores e os alunos que ali frequentam. O Censo tem microdados desde 1995 e em 2007 assumiu um novo formato digital. Há indícios de que a pesquisa ocorre desde a década de 30. Aqui na Base dos Dados, optamos por disponibilizar, inicialmente, valores desde 2009 até 2020. O período é o mais uniforme da pesquisa, e foi quando ela manteve o mesmo formato, o que facilita e corrobora com a compatibilização entre diferentes anos.

A disponibilização dos dados do Censo, assim como de outras bases de educação, vêm do Plano Inep Dados Abertos. Porém, utilizar as bases originais do Censo tem vários obstáculos:

-Você terá que baixar cada ano individualmente em um arquivo compactado, cada um com aproximadamente 10gb
-Cada base tem seu próprio conjunto de variáveis e você terá que consultar os vários dicionários, já que uma mesma variável recebe vários nomes diferentes ao longo do período.
-Os dicionários não são muito claros sobre a constância das variáveis: em muitos casos é necessário abrir a base para verificar a existência de uma dada variável.
-O período de 2009 até 2020 soma mais de 100gb se for aberto inteiro no seu computador.

Ao invés de enfrentar tudo isso, você pode usar nossa versão já limpa e harmonizada, baixando via BigQuery ou abrindo as tabelas direto no R ou no Python.

O Censo Escolar pela Base dos Dados

Com as suas 4 tabelas, o nosso dataset do Censo Escolar replica a arquitetura da base original, disponibilizando diferentes tabelas para diferentes níveis de agregação. Temos uma base a nível de escola, uma a nível de turma, e duas no nível individual: uma para docentes e outra para os alunos.

É importante ressaltar que todas as tabelas do dataset, além de terem identificação de suas observações, identificam sempre a escola a qual pertencem, sempre tendo a variável id_escola. Optamos por deixar essas variáveis para manter o paralelismo da base original e para que possíveis agregações e junções de tabelas fossem mais fáceis.

Além disso, cabe reforçar que não disponibilizamos todas as variáveis que aparecem no Censo. Selecionamos a maioria das variáveis, as que aparecem mais vezes. Pegamos as variáveis que aparecem 8 ou mais vezes ao longo dos 12 anos, além de outras variáveis que julgamos serem cruciais por serem identificadoras importantes. Isso significa que, se a variável só aparece em 2 anos, por exemplo, ela não foi incluída nas nossas tabelas.

A tabela `escola`

A tabela escola tem a granularidade de cada escola por cada ano. A identificação básica de cada unidade é feita pelas variáveis id_escola e ano.

Na tabela, temos três grandes grupos de variáveis: variáveis com características gerais da escola, variáveis que qualificam a infraestrutura de cada instituição e variáveis que identificam quais tipos de aulas ocorrem.

Entre as características gerais, temos a rede a qual a escola está vinculada, o tipo de localização da escola, a dependência administrativa mantida com o poder público, a instituição mantenedora da escola e o seu local de funcionamento.

Nas variáveis de infraestrutura, temos uma ampla caracterização do acesso de cada instituição a serviços básicos, como água, energia e coleta/tratamento de lixo e esgoto. A infraestrutura de ensino também é avaliada: temos variáveis que medem a quantidade de equipamentos, desde equipamentos de som ou DVD até computadores e conexão com a internet, além das variáveis que informam o número de salas, de banheiros, de quadras, de laboratórios, áreas verdes e até de berçários.

Por fim, nas variáveis que identificam o ensino, temos em sua maioria variáveis binárias que identificam se a escola tem aulas para ensino regular, para EJA, em formato EaD, entre outras.

Com uma única entrada no R usando a biblioteca basedosdados, podemos descobrir, por exemplo, quais escolas não têm água potável em Minas Gerais no ano de 2020:

basedosdados::read_sql('SELECT ano,id_escola,agua_potavel 
FROM `basedosdados-dev.br_inep_censo_escolar.escola` 
WHERE agua_potavel = 0 and ano = 2020 and sigla_uf = "MG"')

Ou quais escolas funcionavam em unidades prisionais em 2009:

basedosdados::read_sql('SELECT id_escola,ano,local_func_unid_prisional 
FROM `basedosdados-dev.br_inep_censo_escolar.escola` 
WHERE ano = 2009 and local_func_unid_prisional = 1')

A tabela `turma`

A tabela turma tem a granularidade de cada turma por cada ano, isto é, cada observação é identificada por seu id_turmae pelo seu ano.

Na tabela, encontramos principalmente variáveis relacionadas a quais dias a turma tem aulas e de quais disciplinas são essas aulas. Em cada um dos casos, tem-se variáveis binárias identificando cada dia da semana e cada matéria.

Temos também variáveis que identificam atividades extracurriculares e quando elas são feitas por cada turma, além das já mencionadas identificações da escola a qual a turma está inserida.

Se quisermos abrir as turmas de 2010 para o estado da Bahia por exemplo, no Python:

import basedosdados as bd 
bd.read_sql(query = " SELECT * FROM `basedosdados.br_inep_censo_escolar.turma` 
WHERE ano = 2010 and sigla_uf = BA ", 
billing_project_id = "bd_projeto")

A tabela `docente`

A tabela docente tem a granularidade a nível dos profissionais escolares em sala de aula: professores, auxiliares, tradutores/intérpretes entre outros que atuam pedagogicamente na escola. Cada linha da tabela corresponde a um profissional, por cada ano. Cada um desses profissionais é identificado com um id_docente.

Na tabela temos as mais diversas características de cada docente, representadas por mais de 80 variáveis. Tem-se características pessoais de cada docente, como sua raça/cor, seu sexo, sua idade, sua nacionalidade e até o município de nascimento e o município onde o docente reside. Também temos variáveis que identificam a formação do docente, desde as disciplinas pelo qual ele é responsável, até a sua formação específica como professor.

Se quisermos só os docentes pretos da cidade de Rio Branco por exemplo, poderíamos rodar em Python:

import basedosdados as bd 
bd.read_sql(query = "SELECT * FROM `basedosdados.br_inep_censo_escolar.docente`
WHERE id_municipio = 1200401 and raca_cor = 2", billing_project_id = "bd_projeto")

A tabela `matricula`

Sem dúvida uma das maiores tabelas de todo nosso datalake, a tabela matricula identifica cada aluno brasileiro através da id_aluno. As observações estão no nível de cada aluno e de cada ano.

Entre as variáveis, temos as que caracterizam dados pessoais de cada aluno, paralelo ao feito com cada docente: temos sexo, cor_raca, id_municipio_nascimento, id_municipio_endereco, que, assim como na tabela de docentes, identificam sexo, cor/raça e o município onde nasceu e onde reside o aluno. Temos também variáveis que caracterizam deficiências dos alunos, físicas e intelectuais.

Para além desses casos, temos também um conjunto de dados que identificam relações do aluno com a escola: variáveis que identificam como os alunos se deslocam até a escola e variáveis que identificam como os alunos ingressaram na instituição.

A tabela matricula, especificamente, é muito grande (chega a mais de 90gb), por isso não recomendamos tentar baixá-la ou utilizá-la inteira: a tabela é particionada por ano e por uf de maneira que, ao filtrar por essas variáveis, o resultado é obtido mais rápido e o gasto é bem menor.

Como exemplo, podemos recortá-la para pegar somente os estudantes que vão à escola de transporte público em 2019, no estado de Roraima. No R ficaria assim:

basedosdados::read_sql("SELECT * FROM `basedosdados.br_inep_censo_escolar.matricula
WHERE sigla_uf = RR and transporte_publico = 1 
and ano = 2019")

Pontos de contato com outras bases

O censo escolar por si só já disponibiliza uma grande variedade de variáveis. Caso você queira obter ainda mais, com a BD+ é fácil: como as bases têm os mesmos nomes de variável, podemos rapidamente juntar diferentes informações. A seguir separamos algumas bases já disponíveis na BD+ que podem complementar bem as tabelas do Censo Escolar:

Os indicadores escolares do INEP: O dataset br_inep_indicadores_educacionais apresenta uma série de medições sobre a qualidade de ensino, em diferentes níveis de agregação. Usando a tabela escola como ponte, podemos juntar média de alunos por turma da escola (proveniente dessa tabela) com número de computadores da escola (vindo do Censo) e construir um mais amplo cenário socioeconômico da escola.

As notas do IDEB: O dataset br_inep_idebnos disponibiliza tabelas de diferentes que identificam as notas dos alunos em avaliações da qualidade do ensino (as provas do SAEB), as taxas de rendimento (aprovação, reprovação, abandono) e o desempenho no IDEB. Junto com as tabelas do Censo, pode-se avaliar, por exemplo, como escolas com mais professores com mestrado vão nessas provas em relação a escolas com professores que não tem pós graduação.

Diferentes tabelas da SEDUC: As tabelas da SEDUC disponibilizam informações sobre o nível educacional e socioeconômico das escolas estaduais de São Paulo. Com elas somadas ao censo, pode se avaliar por exemplo se escolas com melhor infraestrutura tem maior evasão no ensino médio.

As tabelas do Censo também tem identificação do município de cada uma das escolas, das turmas, dos docentes e dos alunos, permitindo vinculá-las com quase todas as tabelas a nível de município disponível no nosso datalake.

Chegamos ao fim da nossa breve apresentação! Os dados da mais importante pesquisa estatística educacional brasileira, o Censo Escolar (e todas suas nuances) agora fazem parte da Base dos Dados!

Nosso projeto já te ajudou de alguma forma? Saiba como nos ajudar:
-Apoie o projeto
-Seja um(a) colaborador(a) de dados na BD
-Colabore com nossos pacotes
-Compartilhe nas redes sociais!

Texto produzido por Matheus da equipe de Dados, com enorme apoio do pesquisador Adriano Senkevics, um dos especialistas em educação da nossa comunidade 💚

Como funciona o sistema de inserção de dados na BD

Vinicius Aguiar — Fri, 28 May 2021 15:32:31 +0000

TL;DR: Vamos conhecer um pouquinho sobre como funciona a infraestrutura de inserção de dados da Base dos Dados e como você pode melhorar seu portfólio de cientista de dados e/ou desenvolvedor contribuindo com a nossa missão de universalizar o acesso a dados.

A Infraestrutura

O time de infraestrutura da Base dos Dados é responsável pelas ferramentas de ingestão de dados, que englobam desde o upload de dados até a disponibilização de dados no ambiente de produção; pelo acesso de dados através de pacotes em Python e R; e pelo website. Neste cenário o time é atualmente dividido em várias frentes, tratando da renovação do site e da implementação de pesos e contrapesos automatizados.

Procuramos simplificar e automatizar todos os processos, começando com o upload de dados e inserção dos mesmos no Ambiente de Experimentação. Neste ponto o colaborador pode adicionar dados em sua nuvem do Google, limpar e tratar os dados, e então criar as tabelas locais com a interface de linha de comando desenvolvida pela infra. Por fim, o colaborador pode submeter a base de dados para revisão, criando um pull request no Github.

Após o pull request de revisão entra em ação o sistema de Pesos e Contrapesos, com o time de dados checando a qualidade dos dados e metadados. Esse ponto é crucial para manter a qualidade dos dados, um diferencial da BD. O time de infra atua procurando automatizar o máximo possível o processo de revisão dos dados, realizando a validação de metadados como descrições e nomes de colunas; e tipos de dados, como dados chaves primárias.

Após a checagem dos dados, o pull request de inserção de dados é aprovado e os dados entram no Ambiente de Produção. Logo podem ser acessados por uma de nossas ferramentas, como os pacotes em Python e R, ou diretamente pelo BigQuery.

Paralelamente ao processo de inserção de dados, o time de Infra também trabalha com a renovação do portal, visando oferecer uma interface moderna.

Contribuindo com dados

No caminho para se tornar um analista de dados ou desenvolvedor nos encontramos com certas dificuldades de entrada no mercado de trabalho. Por vezes não existe balanço entre estudo e aplicação prática, ou somente a análise de dados de brincadeirinha. Levante a mão quem não passou uma época paralisado em bases de dados como o Titanic ou Iris. E apesar dessas bases de dados serem uma boa alternativa para aprender novos métodos ou ferramentas, o conhecimento obtido trabalhando com as mesmas não é transferível para o mundo real.

Uma boa alternativa para lidar com dados reais e melhorar seu portfólio é ajudar a Base dos Dados com a sua ingestão de dados. No mínimo você irá lidar com a captura de dados, preferencialmente de forma automatizada, a arquitetura de dados, e a limpeza dos mesmos. Também vai interagir com ferramentas do dia a dia de um cientista de dados, como interfaces de linha de comando, YAML e BigQuery. A experiência conquistada pode ser crucial na entrada no mercado de trabalho.

Descrevemos em detalhes esse processo em Colaborando com dados na BD+. Em resumo o processo é dividido em quatro partes. Inicialmente você informa seu interesse para a BD. Então limpa e trata os dados. Em seguida realiza upload dos dados em seu BigQuery pessoal. E por fim envia os dados para revisão.

Contribuindo com a infra

Aliás, outra forma de contribuir e melhorar seu portfólio, mas agora de desenvolvedor, é colaborando com a infraestrutura da BD.

A colaboração começa conversando conosco, no bate papo da infra ou nas reuniões às 19h da segunda-feira, ambas nos canais da infra no Discord. Após este passo podemos escolher uma feature ou problema para desenvolvimento, isto é, caso ainda não tenha escolhido algum problema contido nas issues.

Como você pode colaborar? Aqui estão algumas ideias:

Adicionando novos conjuntos de dados
Fazendo a revisão de submissões de dados
Aprimorando e criando novas funcionalidades do pacote em Python
Aprimorando e criando novas funcionalidades do pacote em R
Criando um pacote em Stata
Adicionando checagens automáticas de dados
Adicionando checagens automáticas de metadados
Desenvolvendo novas features para o site

Nosso projeto já te ajudou de alguma forma? Saiba como nos ajudar:

Apoie o projeto
Seja um(a) colaborador(a) de dados na BD
Colabore com nossos pacotes
Compartilhe nas redes sociais!

Texto produzido por Vinicius e Fernanda, com apoio de Diego, João e Caio do time de Infra da Base dos Dados 💚

BigQuery 101

Fernanda Scovino — Wed, 26 May 2021 22:31:28 +0000

TL;DR

Neste artigo vamos demonstrar como consultar os conjuntos de dados disponíveis no nosso datalake público online através do Google BigQuery. Você irá aprender a avaliar os dados, cruzar tabelas de diferentes conjuntos de dados disponíveis e para isso iremos explicar algumas funções básicas de SQL e BigQuery - ao final do artigo temos inclusive uma super indicação de curso gratuito de SQL.

O texto tem como base o workshop feito por João Carabetta disponível em nosso canal no Youtube.

Introdução

As bases disponíveis para download e análise no nosso site possuem o ícone BD+. Isto significa que a base está disponível no nosso datalake público, ou seja, estes dados já foram tratados pela nossa equipe técnica e são atualizados frequentemente.

Ao longo do texto iremos explorar os dados da RAIS (Relação Anual de Informações Sociais) mas o processo é o mesmo para qualquer base que você desejar acessar. A RAIS é um relatório de informações socioeconômicas solicitado pela Secretaria de Trabalho do Ministério da Economia brasileiro às pessoas jurídicas e outros empregadores anualmente.

Através do site, após escolher o conjunto de dados de interesse, você pode visualizar Tabelas disponíveis neste conjunto. No caso da RAIS, temos disponíveis toda a série histórica de microdados de vínculos empregatícios no Brasil, e tabelas de agregações destes dados.

Para explorar uma tabela em específico, você deve clicar em Explorar dados. Em seguida, ao clicar no botão somos redirecionados ao Editor de consultas dentro do Google Cloud - essa é a ferramenta que iremos explorar hoje.

Antes de começar:

Se já tiver um projeto no Google Cloud, siga em frente. Caso contrário, após clicar no botão você verá a página abaixo - basta clicar em Criar um projeto (é gratuito) e já terá acesso às nossas bases.

Navegando pelo BigQuery

Abaixo vamos entender melhor como funciona a interface do BigQuery. Após criar o projeto ele vai aparecer para você no canto superior esquerdo (1). Logo abaixo terá uma lista de Projeto fixos do BigQuery, dentre esses o basedosdados (2) - o ícone de pino azul indica que o projeto está fixado e poderá ser acessado sempre que você abrir o BigQuery. A seta à esquerda do nome basedosdados nos permite expandir a lista de todas as bases disponíveis na BD+ logo abaixo.

Neste exemplo acessamos a página dos dados da RAIS. Sempre que abrirmos uma tabela no BigQuery teremos alguns itens que ficarão à mostra: a aba referente à tabela que selecionamos (3) que contém informações sobre a estrutura e descrição das colunas em Esquema (4) e também metadados da tabela em Detalhes (5).

Por fim, para visualizar os dados da tabela criamos uma nova Consulta (6) que irá abrir um novo Editor com a estrutura em SQL já com as informações da nossa tabela.

Explorando os metadados da RAIS

Na RAIS podemos ver entre as colunas disponíveis a sigla_uf, id_município (código IBGE), tipo de vínculo empregatício, tipo de admissão, mês de admissão, mês de desligamento, motivo de desligamento etc.
Todos os dados podem ser filtrados por região, período, nacionalidade, raça, salários etc.

Utilizando o Editor de consultas em SQL

Após selecionarmos em Consulta na página anterior, o BigQuery irá nos fornecer uma nova interface para escrita das instruções em SQL. A estrutura básica criada segue abaixo:

SELECT FROM 'basedosdados.br_me_rais.microdados_vinculos' LIMIT 1000

Essa estrutura mínima indica que iremos selecionar (SELECT) alguma(s) coluna(s) ou agregações (temos que indicar aqui quais serão) da tabela de microdados (FROM 'basedosdados.br_me_rais.microdados_vinculos') e queremos só visualizar as 1000 primeiras linhas (LIMIT 1000).

Atenção: Esse limite é importante para não processarmos a tabela inteira caso contrário seriam 250GB de dados! Conforme tornamos a query mais refinada, adicionando filtros para anos e locais específicos, por exemplo, esse tamanho de processamento se reduz bastante.

Para selecionar todas as colunas da tabela podemos usar o "*" ao invés de escrever uma por uma após o SELECT. Ao clicar em Executar (1) obtemos logo abaixo a tabela de resultado da nossa consulta. Acima da tabela irá aparecer o tamanho do processamento realizado (2) e você pode também salvar a tabela gerada em CSV no Drive ou no seu computador no botão de Salvar resultados (3) ou Explorar dados (4) para criar gráficos com o Google Data Studio - veja o exemplo com os dados da RAIS abaixo.

Vamos usar como exemplo mais completo uma consulta de vínculos empregatícios do estado do Acre e cruzar com os dados de população do IBGE de 1985 até 2019. Essa é uma consulta mais complexa, mas basta sabermos quais são as colunas de pareamento (neste caso, id_municipio e ano que fazem o JOIN) entre as tabelas para realizar o cruzamento. Abaixo em vermelho segue a explicação de cada linha da query, você pode acessar o código em SQL também em nosso Github.

Importante ressaltar:

Nossas bases sempre terão todos os nomes de colunas chave de identificação padronizados, por exemplo sigla_uf é sigla_uf em todas as tabelas, o mesmo para ano e id_municipio - isso facilita bastante os cruzamentos!

Esta consulta processou 571 MB de dados em apenas 4 segundos. A tabela completa da RAIS tem mais de 250 GB. Já imaginou executar no Excel?

Para visualizar o resultado ao longo do tempo podemos criar um gráfico através do botão Explorar dados que irá nos redirecionar para uma aba do Google Data Studio, uma ferramenta gratuita de BI do Google. Para saber mais sobre o Data Studio veja aqui.

Por que usar SQL?

SQL é uma das linguagens de programação mais simples e poderosas para quem quer mexer com dados. Saber criar consultas eficientes com filtros, agregações e cruzamentos em SQL salva bastante tempo que você gastaria no Python ou R, por exemplo - e acredite, nós também amamos essas linguagens.

Usar o SQL para pré-processamento de dados é uma ótima saída para quem trabalha com grandes volumes de dados, e construir estatísticas descritivas também é bastante simples. Uma vez com os dados prontos, você pode carregar os dados no Python ou R para rodar modelos e algoritmos mais robustos, usando inclusive nosso pacote já disponível em ambas as linguagens.

Quer aprender mais sobre SQL? Temos uma sugestão de um curso gratuito no Coursera em que você pode aprender praticando, e tem duração média de 8 horas.

Nosso projeto já te ajudou de alguma forma? Saiba como nos ajudar:

Apoie o projeto
Seja um(a) colaborador(a) de dados na BD
Colabore com nossos pacotes
Compartilhe nas redes sociais!

Texto produzido por Paolo e Fernanda, da Base dos Dados 💚

Como usar a biblioteca basedosdados no R - capítulo 1

Matheus Valentim — Wed, 12 May 2021 17:56:00 +0000

TL;DR

Neste texto vamos explicar como usar a biblioteca basedosdados no R e como exemplo iremos explorar a relação entre cobertura da coleta e tratamento de esgoto, e incidência de doenças causadas pela falta de saneamento básico. A biblioteca permite acessar, baixar e carregar centenas de bases do nosso datalake público direto na sua máquina. Serão apresentadas as funções presentes do pacote e como utilizá-las para realizar análises.

Como acessar nosso datalake público (BD+)

Organizamos neste datalake as principais bases públicas já tratadas e compatibilizadas, prontas para análise. O datalake é mantido no ambiente da Google (BigQuery) e o acesso às bases é gratuito, com um limite mensal de 1TB - acredite, nem a gente chega a tanto.

O pacote basedosdados te permite acessar esse banco através do R de um jeito rápido e fácil. Para isso, é necessário somente que você possua um projeto no Google Cloud, o que é gratuito. Veja aqui os 5 passos para criar seu projeto. Caso não tenha o projeto, ao utilizar a biblioteca pela primeira vez, esse mesmo passo a passo para criação é printado no seu console.

Conhecendo a biblioteca `basedosdados`

Como qualquer outra biblioteca no R, você deve instalá-la e carregar no seu ambiente:

# instalando a biblioteca
install.packages('basedosdados')

# carregando a biblioteca na sessão
library(basedosdados)

A biblioteca contém duas funções principais: read_sql() e a download(), descritas abaixo.

Função `download()`

A donwload() te permite baixar bases direto do nosso repositório em sua máquina, como um arquivo .CSV. Seus argumentos são:

query: query em SQL com qual tabela se quer baixar, com quais colunas e com qual agregação
path: um caminho para onde salvar o arquivo .csv. Um truque importante é usar projetos do R. Com eles, você não precisa especificar todo o caminho onde quer salvar as bases e o código pode ser mais facilmente reutilizado por outros usuários. Ao longo do texto vamos usar o caminho "/bases" como sendo uma pasta em um dado projeto R. Note que depois de "/bases" acrescentamos o nome do arquivo e o formato .csv.

Caso você queira baixar o Atlas Esgotos da ANA, base que vamos usar nas aplicações, rodaria:

download(query = 'SELECT * FROM `basedosdados.br_ana_atlas_esgotos.municipios` ', path = '/bases/base_ana.csv')

Função `read_sql()`

A read_sql() usa uma API para acessar o datalake e abrir uma base em formato tibble na sua sessão de R. O R se encarrega de entrar na nuvem, escolher a tabela e rodar a query para acessá-la, conforme os parâmetros passados na função:

query (string) : é a consulta que será feita ao banco em SQL. Aqui você pode especificar filtros, agrupamentos, e outras transformações usando SQL. Veja mais sobre a sintaxe aqui.
billing-project-id: é o identificador do seu projeto do Google. Ele é necessário para que o Google saiba quem está acessando a nuvem, e contabilizar o tamanho da requisição que você está fazendo. Não precisa ser especificado caso a set_billing_id seja usada.

Funções `set_billing_id()` e `get_billing_id()`

A set_billing_id() guarda o seu billing-project-id usado na função read_sql(). Em outras palavras, você pode definir uma única vez na sessão o seu identificador de projeto do Google, e ele não precisa mais ser um argumento para acessar tabelas. Para os mais experientes, um outro truque possível é guardar o seu id em algum objeto de um arquivo .env e depois usar a função Sys.getenv(objeto) para acessá-lo. Um arquivo .env pode ser gerado com a biblioteca dotenv.

A get_billing_id() printa o valor guardado em set_billing_id. Essa função não é diretamente usada: a função read_sql() automaticamente pega o identificador definido através da get_billing_id().

Dito isso, uma vez que definimos nosso billing-project-id:

set_billing_id("meu-projeto-3058")

Não precisamos mais usar a função read_sql() com dois argumentos:

read_sql(query = 'SELECT * FROM `basedosdados.br_ana_atlas_esgotos.municipios` ' ,
billing_project_id = "meu-projetoid-3058")

Podemos usá-la sem o segundo argumento (que é o que será feito ao longo do texto):

read_sql(query = 'SELECT * FROM `basedosdados.br_ana_atlas_esgotos.municipios` ')

Aplicações

Antes: Como descobrir os nomes de tabelas

Para exemplificar o uso da biblioteca, vamos estudar duas variáveis de bases diferentes que acreditamos estar correlacionadas: investimento em coleta de esgoto e mortes por doenças que se propagam na falta de saneamento básico.

Para começar essa análise, iremos acessar os dados do Atlas Esgotos da Agência Nacional das Águas. Ao acessar o link do conjunto de dados no nosso site (acima), você verá listado as "Tabelas disponíveis na BD+" - isso indica quais tabelas estão disponíveis no banco.

Neste caso temos somente a tabela municipios. Ao clicar na mesma, você irá para uma página que mostra como acessar essa tabela no próprio banco (via editor do BigQuery), em Python e em R (figura abaixo).

No código, vem descrito já o nome do conjunto e da tabela no banco - neste caso, basedodados é o banco, br_ana_atlas_esgotos o conjunto e municipios a tabela. Você pode copiar este código direto para o RStudio.

Diferentes maneiras de consultar uma tabela no R

Utilizando a função read_sql(), iremos carregar os dados do Atlas Esgostos da ANA no R e salvar esse tibble em um objeto chamado base.

base <- basedosdados::read_sql(query = 'SELECT * FROM `basedosdados.br_ana_atlas_esgotos.municipios` ')

Caso você clique para ver a base, vai se deparar com algo assim:

O Atlas contém mais de 30 variáveis sobre a condição da coleta e do tratamento de esgoto para cada município brasileiro. Entre elas, temos, por exemplo, a porcentagem de habitantes do município sem acesso a esgoto tratado, o nome do prestador do serviço do saneamento e o investimento feito pelo município em coleta e em tratamento de esgoto.

A query usada contém um * para indicar que estamos selecionando todas as colunas da tabela. Caso quiséssemos baixar só duas colunas, como o identificador do municipio (id_municipio) e o índice de pessoas que não recebe atendimento de tratamento de esgoto (indice_sem_atend) bastaria rodar algo como:

base_cobertura <- basedosdados::read_sql(query = 
'SELECT id_municipio, indice_sem_atend 
FROM `basedosdados.br_ana_atlas_esgotos.municipios` ')

Outra maneira de selecionar uma "sub-base" é filtrando as observações por alguma característica: se estivermos interessados somente no saneamento básico da região norte, não faz sentido pegarmos todas as 5570 linhas da base original. Podemos rodar uma query adicionando o verbo WHERE e indicar que só queremos estados do Norte:

base_norte <- read_sql('SELECT * 
FROM `basedosdados.br_ana_atlas_esgotos.municipios` 
WHERE sigla_uf in ("AM","AP","RO","RR","AC", "PA") ')

Além dessas possibilidades de seleção, podemos agregar essas tabela para o nível de estado, ao invés do nível de município, utilizando um agrupamento por sigla_uf. Ao agregarmos, precisamos também agregar as colunas, somando ou tirando uma média, por exemplo. Nesse exemplo, vamos pegar uma média da cobertura de esgoto por UF. O código fica assim:

base_uf <- basedosdados::read_sql(query = 
'SELECT sigla_uf, AVG(indice_sem_atend) as sem_esgoto
FROM `basedosdados.br_ana_atlas_esgotos.municipios` 
GROUP BY sigla_uf ' )

Visualizando o acesso a tratamento nos estados

Para finalizar, vamos construir um gráfico com essa última base criada para visualizar quais estados tem maior e a menor média de acesso a tratamento de esgoto nos seus municipios:

base_uf%>%
  ggplot(aes(y = sem_esgoto, x = reorder(sigla_uf, -sem_esgoto))) + geom_col(fill = '#7cb342') +
  labs(x = "Estado", y = "Porcentagem média sem saneamento", 
       title = "População sem saneamento básico",
       subtitle = "Média da porcentagem da população municipal sem saneamento, por UF") + theme_classic()

Parabéns! Concluímos a nossa primeira aplicação da basedosdados! Com ela, descobrimos que o estado que tem, em média, o pior acesso a tratamento e coleta de esgoto nas suas cidades é Rondônia, e o estado que tem a melhor é São Paulo.

Para usar esta mesma base em um outro software, pode-se usar a função download() para baixá-la em CSV direto na sua máquina:

download(query = 
'SELECT sigla_uf, AVG(indice_sem_atend) as sem_esgoto
FROM `basedosdados.br_ana_atlas_esgotos.municipios` 
GROUP BY sigla_uf ', path = '/bases/base_ana_uf.csv')

Cruzando diferentes bases

Outra aplicação importante do pacote é a possibilidade de juntar diferentes bases sem ter que abrí-las individualmente.

Para exemplificar, vamos comparar os dados que obtemos de saneamento com o nível de mortalidade por doenças relacionadas à falta de saneamento. Para explorar mortalidade precisamos de número de óbitos, que estão na tabela do Sistema de Mortalidade do Ministério da Saúde (SIM), e da população, na tabela de população do IBGE. Ambas as tabelas estão disponíveis na BD+ nos links acima!

Para cruzar as tabelas vamos filtrar ambas para o ano de 2013, referente ao Atlas Esgotos (tabela anterior), pela coluna ano presente em todas as tabelas. Além disso, vamos também escolher somente a mortalidade de causa_basica referente a doenças diarréicas, relacionadas à falta de saneamento básico. Os códigos de referência da coluna causa_basica na tabela SIM podem ser consultados aqui. A query abaixo faz esses filtros e seleciona as colunas tanto da base de população e quanto de mortalidade:

base_mortalidade <- read_sql('
SELECT sim.id_municipio, sim.numero_obitos , pop.populacao 
FROM `basedosdados.br_ms_sim.municipio_causa` as sim
FULL JOIN `basedosdados.br_ibge_populacao.municipios` as pop ON sim.id_municipio = pop.id_municipio
WHERE sim.ano = 2013 and pop.ano = 2013 and sim.causa_basica in ("A00", "A01", "A02", "A03", "A04", "A05", "A06","A07", "A08", "A09")')%>%
  mutate(mortalidade = (numero_obitos/populacao)*10000)%>%
  select(id_municipio,mortalidade)

Vamos então juntar essa base com a tabela de cobertura de saneamento e ver as possíveis correlações. Para isso, podemos juntar as bases abertas no R, a base_cobertura e a base_mortalidade, ou podemos rodar uma nova QUERY:

base_final <- read_sql(
'SELECT  sim.id_municipio, sim.numero_obitos , pop.populacao, ana.indice_sem_atend
FROM `basedosdados.br_ms_sim.municipio_causa` as sim
FULL JOIN `basedosdados.br_ibge_populacao.municipios` as pop ON sim.id_municipio = pop.id_municipio
FULL JOIN `basedosdados.br_ana_atlas_esgotos.municipios` as
ana ON sim.id_municipio = ana.id_municipio 
WHERE sim.ano = 2013 and pop.ano = 2013 and sim.causa_basica in ("A00", "A01", "A02", "A03", "A04", "A05", "A06","A07", "A08", "A09")')

Com a base em mãos, criamos um gráfico que relaciona a mortalidade por doenças diarreicas e a cobertura de saneamento básico:

base_final%>%
  mutate(mortalidade = (numero_obitos/populacao)*10000)%>%
  ggplot(aes(y = mortalidade, x =indice_sem_atend)) + geom_point(color = '#7cb342') +
  labs(x = "Porcentagem sem saneamento", y = "Mortalidade", 
       title = "Saneamento x Mortalidade",
       subtitle = "Os municípios que tem pior cobertura também tem mais mortes?") + theme_classic()

E com isso descobrimos que aparentemente não há uma forte correlação entre cobertura de saneamento e mortalidade por doenças quando analisamos todos municípios brasileiros que tiveram mortes por diarreia... Mas, conseguimos facilmente juntar 3 bases de diferentes organizações numa única entrada!

Muito obrigado por ler até aqui!

Confira o notebook com a análise completa no nosso repositório:

basedosdados / analises

📊 Repositório de códigos simples e replicáveis das análises publicadas.

A Base dos Dados é uma ONG com intuito de disponibilizar dados tratados e limpos para qualquer um acessar de um jeito rápido e fácil. Apoie a nossa iniciativa 💚.

Base dos Dados Python 102

Vinicius Aguiar — Sat, 08 May 2021 15:51:41 +0000

TL;DR: Neste artigo vamos demonstrar como acessar os dados do BD+ via consulta SQL pelo pacote da Base Dos Dados em Python. Em seguida vamos continuar as análises do post Base Dos Dados Python 101, sobre a popularidade de nomes nos estados brasileiros, com o conteúdo adaptado do Workshop "Brincando com dados da BD+ em Python".

import pandas as pd
import geopandas as gpd
import basedosdados as bd
import matplotlib.pyplot as plt

Consultas SQL na BD+

Já vimos em um artigo anterior que podemos acessar a BD+ pela função read_table, capaz de carregar uma tabela por vez. Hoje vamos acessar a BD+ por meio de consultas SQL, com a função read_sql. Recomendamos essa opção, por contar com toda a capacidade da linguagem SQL, acesso amplo ao acervo da BD+ e retorno em formato de dataframe. Além do mais, sua sintaxe é análoga a read_table. Isto é, precisamos apenas fornecer uma consulta válida e um id de projeto do Google Cloud.

A seguir ilustramos uma consulta de dados da tabela de nomes brasileiros do IBGE (2010) cruzada com diretórios brasileiros, com fim de obter o nome mais popular de cada estado. Observe que precisamos fornecer o nome do datalake (basedosdados), dataset (br_bd_diretorios_brasil) e tabela (municipio).

df = bd.read_sql(
    '''
    SELECT estado.uf AS state, estado.municipio AS town, nomes.nome AS name, nomes.qtde_nascimentos_ate_2010 AS freq
    FROM  basedosdados.br_ibge_nomes_brasil.quantidade_municipio_nome_2010 AS nomes
    INNER JOIN basedosdados.br_bd_diretorios_brasil.municipio AS estado ON nomes.id_municipio = estado.id_municipio
    ''',
    billing_project_id='basedosdados42'
)

Após lermos os dados realizamos um pré-processamento. Neste processo, eliminamos nomes únicos ou erros em municípios, como: Awo, Yorane e Zilneide. Por fim, também calculamos a frequência dos nomes nos estados por 100.000 pessoas.

df = df[df.duplicated('name', keep=False)] # seleciona nomes que aparecem mais de uma vez

total_by_state = df.groupby('state').freq.sum() # agrupa o total por estado
df = df.merge(total_by_state, on='state', suffixes=('', '_total_by_state')) # une os dados com total por estado

df['freq_per_100k'] = 100_000 * df.freq / df.freq_total_by_state # cria o total por estado por 100k pessoas

Em que estado Fred é mais famoso?

Nossa primeira questão de análise é descobrir como está distribuída a frequência de um nome nos estados brasileiros. Em particular gostaríamos de saber em qual estado Fred, nome de um de nossos desenvolvedores, é o nome mais famoso. Então vamos contar a frequência do nome Fred por 100.000 pessoas em cada estado, e em seguida representar este dado com um mapa com gradiente de cores (coroplético).

Para isso, previamente instalamos a biblioteca geopandas para auxiliar o desenho do mapa e manualmente criamos um mapeamento entre o nome de um estado e sua abreviação nos dados geográficos state2abbr.

state2abbr = {
    'Acre': 'BR.AC', 'Alagoas': 'BR.AL', 'Amapá': 'BR.AP',
    'Amazonas': 'BR.AM', 'Bahia': 'BR.BA', 'Ceará': 'BR.CE',
    'Distrito Federal': 'BR.DF', 'Espírito Santo': 'BR.ES',
    'Goiás': 'BR.GO', 'Maranhão': 'BR.MA', 'Mato Grosso': 'BR.MT',
    'Mato Grosso do Sul': 'BR.MS', 'Minas Gerais': 'BR.MG',
    'Pará': 'BR.PA', 'Paraíba': 'BR.PB', 'Paraná': 'BR.PR',
    'Pernambuco': 'BR.PE', 'Piauí': 'BR.PI', 'Rio de Janeiro': 'BR.RJ',
    'Rio Grande do Norte': 'BR.RN', 'Rio Grande do Sul': 'BR.RS',
    'Rondônia': 'BR.RO', 'Roraima': 'BR.RR', 'Santa Catarina': 'BR.SC',
    'São Paulo': 'BR.SP', 'Sergipe': 'BR.SE', 'Tocantins': 'BR.TO',
}

Em seguida criamos a função visualize_name que filtra os dados do nome, agrupa a frequência por estados e desenha o mapa.

def visualize_name(df, name):
    name_df = df.query(f'name == "{name}"') # filtra o nome escolhido
    name_df = name_df.groupby('state', as_index=False).sum() # agrupa pelos estados
    name_df.state = name_df.state.apply(lambda x: state2abbr[x]) # abrevia os estados
    name_df = name_df.rename(columns={'state': 'HASC_1'}) # renomeia a coluna

    fig, ax = plt.subplots(1, 1)
    br_map = gpd.read_file('./shapes/gadm36_BRA_1.shp') # lê o mapa
    br_map = br_map.merge(name_df, on='HASC_1', how='outer') # une o mapa com os dados
    br_map.plot(column='freq_per_100k', ax=ax, legend=True) # desenha o gráfico

visualize_name(df, 'Fred')

Interessante notar como Fred parece um nome comum em estados como Amazonas e Roraima, devido a população nestes estados ser menor quando comparada com São Paulo.

Quais são os nomes mais regionais?

Essa é uma boa pergunta a se fazer: qual nome é mais exclusivo de uma região? Ou seja, é mais provável de ser encontrado numa determinada região e não em outros estados.

Vamos solucionar esta dúvida agrupando a frequência de nomes dos municípios em estados. Em seguida excluímos os nomes que aparecem apenas em um estado. Para então calcular a mediana e desvio padrão das frequências de cada nome distinto, e por fim normalizamos as frequências de nomes.

state_df = df.groupby(['name', 'state'], as_index=False).sum() # soma as frequências de municípios
state_df = state_df[state_df.duplicated('name', keep=False)] # elimina nomes não duplicados

median = state_df.groupby('name', as_index=False).freq_per_100k.median()
state_df = state_df.merge(median, on='name', suffixes=('', '_median'))

std = state_df.groupby('name', as_index=False).freq_per_100k.std()
state_df = state_df.merge(std, on='name', suffixes=('', '_std'))

state_df['freq_per_100k_zscore'] = state_df.freq_per_100k
state_df['freq_per_100k_zscore'] -= state_df.freq_per_100k_median
state_df['freq_per_100k_zscore'] /= state_df.freq_per_100k_std

Podemos então visualizar quais são os nomes mais regionais, procurando pela máxima frequência normalizada de cada estado.

idxmax = state_df[['state', 'freq_per_100k_zscore']].groupby('state').idxmax()
idxmax = idxmax.values.ravel()

max_name_by_state = state_df.loc[idxmax][['state', 'name', 'freq_per_100k_zscore']]
max_name_by_state = max_name_by_state.sort_values(by='state').set_index('state')

Para aprender como funciona, nada melhor que brincar com o código. Agora é com você!! Como ficou seu nome no ranking? Em qual estado aparece com maior frequência? Fez uma análise da sua família e descobriu alguma coisa legal? Conta pra gente no twitter!

Confira o notebook com a análise completa no nosso repositório:

basedosdados / analises

📊 Repositório de códigos simples e replicáveis das análises publicadas.

Texto produzido por Vinicius, Fernanda e Paolo, da Base dos Dados 💚

Base dos Dados Python 101

Fernanda Scovino — Fri, 16 Apr 2021 15:30:39 +0000

TL;DR

Neste artigo vamos demonstrar como usar o pacote da Base dos Dados em Python para acessar e usar os dados disponíveis em nosso repositório público, com o conteúdo do Workshop "Brincando com dados da BD+ em Python".

O pacote tem diversas funções que permitem listar os conjuntos de dados disponíveis, obter informações sobre as tabelas, carregar os dados direto no pandas, dentre muitas outras. Como exemplo, exploramos os dados da tabela de nomes brasileiros do IBGE (2010) e mostramos como criar uma visualização de nuvem de palavras dos nomes mais frequentes.

Como acessar a BD+ em Python?

A Base dos Dados Mais (BD+) é o nosso datalake de dados públicos limpos, integrados e atualizados pela nossa equipe de dados - dados prontos para análise.

O datalake é mantido no Google BigQuery e tem custo praticamente zero para todos os usuários - você tem 1 TB disponível por mês para fazer consulta aos dados. Para facilitar ainda mais a vida de Pythonistas, criamos um pacote de acesso direto ao repositório via Python: basedosdados

!pip install basedosdados
import basedosdados as bd

Atenção! É necessário criar um projeto no Google Cloud e autenticar seu projeto para fazer consultas aos dados do repositório. Caso você rode qualquer função do pacote pela primeira vez, as instruções para essa configuração irão aparecer para você e basta seguir o passo a passo apresentado.

Existem diversas funções no pacote, tanto para acesso quanto para publicação de dados no nosso ou em qualquer projeto do Google Cloud - você pode usar o pacote para construir seu próprio repositório de dados também.

A lista completa dos módulos do pacote está na nossa documentação, e veja também como colaborar subindo dados no repositório.

Explorando as funções do pacote

Para ilustrar, podemos verificar todos os conjuntos de dados disponíveis no repositório usando a função list_datasets. Essa função retorna todos os conjuntos de dados no repositório e que podem ser filtrados por algum termo específico, com o parâmetro filter_by, como fazemos abaixo buscando dados do IBGE. O parâmetro with_description indica se queremos visualizar também a descrição de cada conjunto.

bd.list_datasets(filter_by='ibge', with_description=True)

Da mesma forma, podemos listar as tabelas de um conjunto de dados específico com a função list_dataset_tables. Além disso, podemos ter também uma visão completa das colunas e seus tipos com a função get_table_columns - tudo isso sem carregar ainda os dados no ambiente!

bd.list_dataset_tables(dataset_id='br_ibge_nomes_brasil', with_description=True)
bd.get_table_columns(
    dataset_id='br_ibge_nomes_brasil',
    table_id='quantidade_municipio_nome_2010'
)

Antes de carregar os dados, pode-se verificar inclusive seu tamanho total - existem tabelas muito grandes no repositório, então recomendamos fortemente fazer esse passo.

bd.get_table_size(
    dataset_id='br_ibge_nomes_brasil',
    table_id='quantidade_municipio_nome_2010',
    billing_project_id='basedosdados42'
)

Por fim, a função read_table faz o carregamento dos dados no ambiente Python. Caso a base em questão seja muito grande, você pode optar também em usar a função read_sql, que permite fazer uma query SQL e carregar no ambiente somente os dados requisitados. Para ambos é necessário que você explicite o seu billing_project_id, o projeto que foi habilitado lá no início e que será cobrado caso você exceda o limite.

df = bd.read_table(
    dataset_id='br_ibge_nomes_brasil',
    table_id='quantidade_municipio_nome_2010',
    billing_project_id='basedosdados42'
)

Neste exemplo vamos trabalhar com os dados de nomes brasileiros do Censo Demográfico 2010 do IBGE. De acordo com o Censo, existem ao todo cerca de 200 milhões de habitantes com mais de 130 mil nomes diferentes espalhados pelo Brasil. Curioso? Nós também!

Quais são os nomes mais famosos no Brasil?

Quem você diria que é mais famoso: Maria ou João? É isso o que queremos descobrir primeiro com os nossos dados.

Para respondermos a essa pergunta, nós vamos contar a frequência de cada nome no país e ordená-los, e em seguida criar uma nuvem de palavras para visualização dessas informações.

Criamos então a função generate_list_sorted_by_freq que faz a agregação dos nomes contando quantas vezes cada um aparece na nossa base, e ordena essa lista de acordo com os nomes mais frequentes. A função pode ser lida abaixo.

def generate_list_sorted_by_freq(df):
    name_freq = df.groupby('name').freq.sum() # agrupamento de nomes iguais
    name_freq = name_freq.sort_values(ascending=False) # ordenação dos nomes por frequência
    name_freq = name_freq.reset_index() # inicializa o index de um dataframe criando um se necessário
    return name_freq

df = df.rename(columns={'qtde_nascimentos_ate_2010': 'freq', 'nome': 'name'})
name_freq = generate_list_sorted_by_freq(df)

Para criar uma nuvem de palavras, vamos usar a biblioteca wordcloud junto ao matplotlib disponíveis para instalação via pip. A wordcloud nos possibilita gerar uma imagem com as palavras mais frequentes e o tamanho de cada palavra é determinado pela sua frequência, gerando um belo efeito visual para nosso ranking.

!pip install wordcloud # caso não tenha a biblioteca já instalada
!pip install matplotlib # caso não tenha a biblioteca já instalada

from wordcloud import WordCloud
import matplotlib.pyplot as plt

Abaixo você pode ver a nuvem gerada com a wordcloud, o código completo de como geramos a nuvem está no notebook ao final do texto.

E como resultado: Maria é a vencedora! O que achou dessa descoberta? No próximo texto vamos trazer uma análise regional construída pelo Fred também no Workshop, para acessar todo o conteúdo apresentado basta acessar a Base dos Dados no Youtube.

Confira o notebook com a análise completa no nosso repositório:

basedosdados / analises

📊 Repositório de códigos simples e replicáveis das análises publicadas.

Texto produzido por Vinicius e Fernanda, da Base dos Dados 💚

DEV Community: Base dos Dados

Entenda como nossa base de Diretórios Brasileiros facilita sua vida

TL;DR

A Base de Diretórios Brasileiros

Exemplo de aplicação

Tutorial Power BI

TL;DR

Conectar ao Google BigQuery

Conectar dados no PowerBI (PBI)

O Brasil nas Olimpíadas

TL;DR

Presença confirmada

Quadro de Medalhas

Mulheres nas Olimpíadas

Queremos saber #OndeEstãoDadosLGBTQIA

TL;DR

Queremos saber #OndeEstãoDadosLGBTQIA

Como você pode ajudar?

Conjuntos de dados já mapeados pela Base dos Dados

Explorando o Censo Escolar com a Base dos Dados

TL;DR

O Censo - e suas dificuldades

O Censo Escolar pela Base dos Dados

A tabela escola

A tabela turma

A tabela docente

A tabela matricula

Pontos de contato com outras bases

Como funciona o sistema de inserção de dados na BD

A Infraestrutura

Contribuindo com dados

Contribuindo com a infra

BigQuery 101

TL;DR

Introdução

Antes de começar:

Navegando pelo BigQuery

Explorando os metadados da RAIS

Utilizando o Editor de consultas em SQL

Importante ressaltar:

Por que usar SQL?

Como usar a biblioteca basedosdados no R - capítulo 1

TL;DR

Como acessar nosso datalake público (BD+)

Conhecendo a biblioteca basedosdados

Função download()

Função read_sql()

Funções set_billing_id() e get_billing_id()

Aplicações

Antes: Como descobrir os nomes de tabelas

Diferentes maneiras de consultar uma tabela no R

Visualizando o acesso a tratamento nos estados

Cruzando diferentes bases

basedosdados / analises

📊 Repositório de códigos simples e replicáveis das análises publicadas.

Base dos Dados Python 102

Consultas SQL na BD+

Em que estado Fred é mais famoso?

Quais são os nomes mais regionais?

basedosdados / analises

📊 Repositório de códigos simples e replicáveis das análises publicadas.

Base dos Dados Python 101

TL;DR

Como acessar a BD+ em Python?

Explorando as funções do pacote

Quais são os nomes mais famosos no Brasil?

Confira o notebook com a análise completa no nosso repositório:

basedosdados / analises

📊 Repositório de códigos simples e replicáveis das análises publicadas.

A tabela `escola`

A tabela `turma`

A tabela `docente`

A tabela `matricula`

Conhecendo a biblioteca `basedosdados`

Função `download()`

Função `read_sql()`

Funções `set_billing_id()` e `get_billing_id()`