DEV Community

Matheus Valentim for Base dos Dados

Posted on • Edited on

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

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)
Enter fullscreen mode Exit fullscreen mode

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') 
Enter fullscreen mode Exit fullscreen mode

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")
Enter fullscreen mode Exit fullscreen mode

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")
Enter fullscreen mode Exit fullscreen mode

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` ')
Enter fullscreen mode Exit fullscreen mode

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.

image

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).

Alt Text

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` ')
Enter fullscreen mode Exit fullscreen mode

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

Alt Text

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 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` ')
Enter fullscreen mode Exit fullscreen mode

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") ')
Enter fullscreen mode Exit fullscreen mode

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 ' )
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

Alt Text

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')
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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")')
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

Alt Text

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:

GitHub logo 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 💚.

Top comments (1)

Collapse
 
luigicaloi profile image
luigicaloi

Oi, muito obrigado pelo ótimo texto! Sou grande fã de vcs.

Só queria apontar que no primeiro código tem um erro de digitação. Deveria ser:

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

e nao

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

Ou seja, sem o "s" em "municipios". Gastei um tempo até entender pq eu estava recebendo um erro, espero que ajude outras pessoas!

Abs