A documentação a seguir apresenta o processo ETL (extract, transform, load) aplicado aos dados do Sistema de Informações sobre Mortalidade (SIM) do DATASUS para indexação na Plataforma de Ciência de Dados aplicada à Saúde (PCDaS). O processo ETL aplicado consiste basicamente em:
Extract:
- Acessar o servidor FTP do DATASUS e baixar os arquivos com extensão .dbc contendo os microdados anônimos do SIM para cada estado e para cada ano disponível/desejado;
- Converter os arquivos baixados para a extensão .dbf;
- Ler e unir os arquivos em um único conjunto de dados para posterior tratamento.
Transform:
- Tratar o conjunto de dados adquirido de maneira a:
- Eliminar valores inválidos;
- Decodificar e enriquecer os dados de acordo com dicionário de dados do SIM;
- Enriquecer os dados relacionados a município;
- Enriquecer os dados relacionados a UF;
- Enriquecer os dados relacionados a Classificação Estatística Internacional de Doenças e Problemas Relacionados com a Saúde (CID10);
- Enriquecer os dados relacionados a data e idade;
- Enriquecer os dados relacionados a coordenadas.
Load:
- Indexar dados tratados do SIM em banco de dados NoSQL Elasticsearch localizado em infraestrutura do PCDaS no Laboratório Nacional de Computação Científica (LNCC);
- Coletar log de indexação.
A implementação de todo o fluxo ETL descrito foi feita no ambiente da ferramenta Dataiku Data Science Studio (DSS). Este processo ETL é reexecutado para cada ano selecionado disponível no servidor FTP do DATASUS. Uma visão geral do fluxo ETL é apresentada no diagrama a seguir:
Detalhes de código/dados da implementação do fluxo ETL são apresentados a seguir.
1: Acessando o servidor FTP do Datasus e baixando os arquivos com extensão .dbc contendo os dados do SIM para cada estado e para cada ano disponível/desejado;
download.dbc <- function(ftp_path,
dest_files_path = getwd(),
estados = NULL, anos = NULL){
require(RCurl)
require(tidyverse)
url <- ftp_path
#userpwd <- "yourUser:yourPass"
#recupera o nome dos arquivos em ftp_path
filenames <- getURL(url, userpwd = NULL,
ftp.use.epsv = FALSE,dirlistonly = TRUE)
filenames <- unlist(strsplit(filenames,"\n"))
filenames <- data.frame(filename=filenames)
filenames <- data.frame(lapply(filenames, as.character), stringsAsFactors=FALSE)
#filtra os estados desejados se estes forem especificados
if(!is.null(estados)){
filenames <- filenames %>%
filter(str_detect(filename, paste(estados,sep="",collapse = '|')))
}
#filtra os anos desejados se estes forem especificados
if(!is.null(anos)){
filenames <- filenames %>%
filter(str_detect(filename, paste(anos,sep="",collapse = '|')))
}
#faz download dos arquivos selecionados e salva os arquivos em dest_files_path
for (filename in filenames$filename) {
download.file(paste(url, filename, sep = ""), paste(dest_files_path, "/", filename, sep = ""))
}
#retorna um data.frame com os nomes e caminho dos arquivos
return(data.frame(file_name=filenames$filename,file_path=dest_files_path))
}#============[INTERAÇÂO DSS]============
library(dataiku)
library(dplyr)
#===========================================
#Caminho para função "download.dbc.sim"
path.script = "https://github.com/bigdata-icict/ETL-Dataiku-DSS/download.dbc.r"
#Caminho FTP Datasus
ftp_path = "ftp://ftp.datasus.gov.br/dissemin/publicos/SIM/CID10/DORES/"
#============[EDITAR]============
#Local para salvamento dos arquivos .dbc
path.data = "path_dbc_files"
#================================
#============[OPCIONAL]============
#filtros de estado e/ou anos
estados = c("RJ") #exemplo
anos = 2000:2014 #exemplo
#==================================
#Carrega função "download.dbc.sim"
if(!exists("download.dbc.sim", mode="function")) source(path.script)
# Computa retorno da receita: nome dos arquivos .dbc baixados
datasus_files_list <- download.dbc.sim(ftp_path = ftp_path,
dest_files_path = path.data,
estados = estados,
anos = anos)
#============[INTERAÇÂO DSS]============ -> Alternativa: salvar data.frame em arquivo .csv
# Retornando resultados para dataset do DSS
dkuWriteDataset(datasus_files_list,"datasus",schema = TRUE)
#===========================================
2: Convertendo os arquivos baixados para a extensão .dbf e então lendo e unindo os arquivos em um único conjunto de dados no DSS para posterior tratamento.
load.dbc.sim <- function(files){
require(read.dbc)
do_from_DBC = data.frame()
for(f in files){
bind_rows( do_from_DBC, read.dbc(f) ) -> do_from_DBC
}
return(do_from_DBC)
}Código para converter, ler e unir arquivos de dados do SIM e escrever conjunto de dados resultantes no DSS:
#============[INTERAÇÂO DSS]============
library(dataiku)
library(dplyr)
#===========================================
#Instala o pacote "read.dbc"
#install.packages("read.dbc")
#Caminho para função "load.dbc.sim"
path.script = "https://github.com/bigdata-icict/ETL-Dataiku-DSS/SIM/load.dbc.sim.r"
#Carrega função "load.dbc.sim"
if(!exists("load.dbc.sim", mode="function")) source(path.script)
#============[INTERAÇÂO DSS]============ -> Alternativa: leitura de data.frame a partir de arquivo .csv
#Acessa lista de arquivos baixados para posterior conversão e leitura
datasus_files_list <- dkuReadDataset("datasus")
#===========================================
#Concatena caminhos e nomes dos arquivos
datasus_files_list <- paste(datasus_files_list$file_path,datasus_files_list$file_name,sep="")
# Computa retorno da receita: dados do SIM advindos de todos os arquivos .dbc baixados
do_from_DBC <- load.dbc.sim(datasus_files_list)
#============[INTERAÇÂO DSS]============ -> Alternativa: salvar data.frame em arquivo .csv
# Retornando resultados para dataset do DSS
dkuWriteDataset(do_from_DBC, "DORES",schema = TRUE)
#===========================================
A transformação/preparação dos dados foi feita utilizando-se funcionalidades (processors) disponíveis no ambiente da ferramenta Dataiku Data Science Studio (DSS). A receita ("recipe") utilizada está disponível para download/visualização através dos arquivos json:
prepare_DORES.json e prepare_DORES.shaker
Alguns detalhes do tratamento dos dados são apresentados a seguir, onde colunas com nomes em maiúsculo representam dados originais advindos do DATASUS e colunas com nomes em minúsculo representam dados resultantes de tratamento:
| Coluna | Valores aceitos | Valores inválidos são substituídos por |
|---|---|---|
| TIPOBITO | [1,2] | [9] |
| SEXO | [0,1,2,'M','F'] | [0] |
| RACACOR | [1,2,3,4,5] | [9] |
| ESTCIV | [1,2,3,4,9] | [9] |
| ESC | [1,2,3,4,5,9] | [9] |
| ESCMAE | [1,2,3,4,5,9] | [9] |
| LOCOCOR | [1,2,3,4,5,9] | [9] |
| CIRCOBITO | [1,2,3,4,9] | [9] |
| GESTACAO | [1,2,3,4,5,6,9] | [9] |
| IDADEMAE | números inteiros | [] |
| NATURAL | números inteiros | [] |
| OCUP | números inteiros | [] |
| OCUPMAE | números inteiros | [] |
| PESO | números inteiros | [] |
| QTDFILVIVO | números inteiros | [] |
| QTDFILMORT | números inteiros | [] |
| Coluna | Códigos | Coluna resultante |
|---|---|---|
| TIPOBITO | {1:'óbito fetal', 2:'óbito não fetal'} | def_tipo_obito |
| SEXO | {'M':1, 'F':2} | SEXO |
| SEXO | {0:"Ignorado",1:"Masculino", 2:"Feminino"} | def_sexo |
| RACACOR | {1:"Branca",2:"Preta",3:"Amarela",4:"Parda",5:"Indígena", 9:"Ignorado"} | def_raca_cor |
| ESTCIV | {1:"Solteiro", 2:"Casado", 3:"Viúvo",4:"Separado Judic./Divorciado",9:"Ignorado"} | def_est_civil |
| ESC | {1:"Nenhuma",2:"1 a 3 anos",3:"4 a 7 anos",4:"8 a 11 anos",5:"12 e mais",9:"Ignorado"} | def_escol |
| LOCOCOR | {9:"Ignorado",1:"Hospital",2:"Outro Estab. Saúde", 3:"Domicílio", 4:"Via Pública", 5:"Outros"} | def_loc_ocor |
| ESCMAE | {1:"Nenhuma",2:"1 a 3 anos",3:"4 a 7 anos",4:"8 a 11 anos",5:"12 e mais",9:"Ignorado"} | def_escol_mae |
| GRAVIDEZ | {9:'Ignorado', 1:'Única',2:'Dupla',3:'Tripla ou mais'} | def_gravidez |
| GESTACAO | {1:'Menos de 22 semanas', 2:'22 a 27 semanas', 3:'28 a 31 semanas', 4:'32 a 36 semanas', 5:'37 a 41 semanas', 6:'42 semanas e mais', 9:'Ignorado'} | def_gestacao |
| PARTO | {9:"Ignorado",1:"Vaginal",2:"Cesáreo"} | def_parto |
| OBITOPARTO | {9:'Ignorado', 1:'Antes',2:'Durante',3:'Depois'} | def_obito_parto |
| OBITOGRAV | {9:'Ignorado', 1:'Sim',2:'Não'} | def_obito_grav |
| OBITOPUERP | {9:'Ignorado', 1:'Sim, até 42 dias',2:'sim, de 43 dias a 01 ano',3:'Não'} | def_obito_puerp |
| ASSISTMED | {9:'Ignorado', 1:'Com assitência',2:'Sem assistência'} | def_assist_med |
| EXAME | {9:'Ignorado', 1:'Sim',2:'Não'} | def_exame |
| CIRURGIA | {9:'Ignorado', 1:'Sim',2:'Não'} | def_cirurgia |
| NECROPSIA | {9:'Ignorado', 1:'Sim',2:'Não'} | def_necropsia |
| CIRCOBITO | {9:'Ignorado', 1:'Acidente',2:'Suicídio', 3:'Homicídio',4:'Outros'} | def_circ_obito |
| ACIDTRAB | {9:'Ignorado', 1:'Sim',2:'Não'} | def_acid_trab |
| FONTE | {9:'Ignorado', 1:'Boletim de ocorrência',2:'Hospital',3:'Família',4:'Outra'} | def_fonte |
O enriquecimento dos dados do SIM relacionados a município foram baseados nos dados disponíveis no seguinte arquivo:
| Coluna base | Operação | Colunas resultantes |
|---|---|---|
| CODMUNRES | Truncar para um máximo de 6 caracteres | cod_mun_res |
| cod_mun_res | Fazer junção ("left join") com base na coluna 'MUNCOD' em 'municipios' | ["res_MUNCOD", "res_MUNNOME", "res_MUNNOMEX", "res_AMAZONIA", "res_FRONTEIRA", "res_CAPITAL", "res_UFCOD", "res_MSAUDCOD", "res_RSAUDCOD", "res_CSAUDCOD", "res_LATITUDE", "res_LONGITUDE", "res_ALTITUDE", "res_AREA", "res_codigo_adotado"] |
| CODMUNOCOR | Truncar para um máximo de 6 caracteres | cod_mun_ocor |
| cod_mun_ocor | Fazer junção ("left join") com base na coluna 'MUNCOD' em 'municipios' | ["ocor_MUNCOD", "ocor_MUNNOME", "ocor_MUNNOMEX", "ocor_AMAZONIA", "ocor_FRONTEIRA", "ocor_CAPITAL", "ocor_UFCOD", "ocor_MSAUDCOD", "ocor_RSAUDCOD", "ocor_CSAUDCOD", "ocor_LATITUDE", "ocor_LONGITUDE", "ocor_ALTITUDE", "ocor_AREA", "ocor_codigo_adotado"] |
O enriquecimento dos dados do SIM relacionados a UF foram baseados nos dados disponíveis no seguinte arquivo:
| Coluna base | Operação | Colunas resultantes |
|---|---|---|
| res_UFCOD | Fazer junção ("left join") com base na coluna 'CODIGO' em 'ufs' | ["res_SIGLA_UF", "res_CODIGO_UF", "res_NOME_UF"] |
| res_CODIGO_UF | Definir a região da UF de acordo com o primeiro dígito de seu código {1:'Norte', 2:'Nordeste', 3:'Sudeste', 4:'Sul', 5:'Centro-Oeste'} | res_REGIAO |
| ocor_UFCOD | Fazer junção ("left join") com base na coluna 'CODIGO' em 'ufs' | ["ocor_SIGLA_UF", "ocor_CODIGO_UF", "ocor_NOME_UF"] |
| ocor_CODIGO_UF | Definir a região da UF de acordo com o primeiro dígito de seu código {1:'Norte', 2:'Nordeste', 3:'Sudeste', 4:'Sul', 5:'Centro-Oeste'} | ocor_REGIAO |
O enriquecimento dos dados do SIM relacionados a CID10 foram baseados nos dados disponíveis nos seguintes arquivos:
CID10 - Capítulos, CID10 - Grupos, CID10 - Categorias, CID10 - Subcategorias
| Coluna base | Operação | Colunas resultantes |
|---|---|---|
| CAUSABAS | Truncar para um máximo de 3 caracteres | CAUSABAS_tmp_1 |
| CAUSABAS | Truncar para um máximo de 4 caracteres | CAUSABAS_tmp_2 |
| CAUSABAS_tmp_1 | Fazer junção ("left join") com base na coluna 'codigo' em 'cid10_capitulos' e extração da coluna 'descricao_breve' | causabas_capitulo |
| CAUSABAS_tmp_1 | Fazer junção ("left join") com base na coluna 'codigo' em 'cid10_grupos' e extração da coluna 'descricao_breve' | causabas_grupo |
| CAUSABAS_tmp_1 | Fazer junção ("left join") com base na coluna 'CAT' em 'cid10_categorias' e extração da coluna 'DESCRABREV' | causabas_categoria |
| CAUSABAS_tmp_2 | Fazer junção ("left join") com base na coluna 'SUBCAT' em 'cid10_subcategorias' e extração da coluna 'DESCRABREV' | causabas_subcategoria |
| CAUSABAS_tmp_1 e CAUSABAS_tmp_2 | Remoção de colunas | [] |
| Coluna base | Operação | Coluna resultante |
|---|---|---|
| DTOBITO | Converter para tipo data | data_obito |
| data_obito | Extrair ano (se 1996 <= ano <= 2016) | ano_obito |
| data_obito | Extrair dia da semana | dia_semana_obito |
| DTNASC | Converter para tipo data | data_nasc |
| data_nasc | Extrair ano (se ano < ano_obito e se (ano_obito - ano) <= 150) | ano_nasc |
| data_nasc | Extrair dia da semana | dia_semana_nasc |
| IDADE | Executar a fórmula (if(IDADE_1>=0 && IDADE_1<4,0,if(IDADE_1>=4 && IDADE_1<=5,(IDADE_1-4)*100+IDADE_2,null)) onde IDADE_1 equivale ao primeiro dígito da coluna IDADE e IDADE_2 equivale ao restante dos dígitos da coluna |
idade_obito_anos |
| IDADE | Executar a fórmula (if(IDADE_1>=0 && IDADE_1<3,0,if(IDADE_1==3,IDADE_2,null)) onde IDADE_1 equivale ao primeiro dígito da coluna IDADE e IDADE_2 equivale ao restante dos dígitos da coluna |
idade_obito_meses |
| IDADE | Executar a fórmula (if(IDADE_1>=0 && IDADE_1<2,0,if(IDADE_1==2,IDADE_2,null)) onde IDADE_1 equivale ao primeiro dígito da coluna IDADE e IDADE_2 equivale ao restante dos dígitos da coluna |
idade_obito_dias |
| IDADE | Executar a fórmula (if(IDADE_1>=0 && IDADE_1<1,0,if(IDADE_1==1,IDADE_2,null)) onde IDADE_1 equivale ao primeiro dígito da coluna IDADE e IDADE_2 equivale ao restante dos dígitos da coluna |
idade_obito_horas |
| IDADE | Executar a fórmula (if(IDADE_1>0,null,if(IDADE_1==0,IDADE_2,null)) onde IDADE_1 equivale ao primeiro dígito da coluna IDADE e IDADE_2 equivale ao restante dos dígitos da coluna |
idade_obito_mins |
| data_nasc e data_obito | Computar diferença entre datas (em ano) | idade_obito_calculado |
| Coluna base | Operação | Coluna resultante |
|---|---|---|
| res_LATITUDE e res_LONGITUDE | Concatenar coordenadas | res_coordenadas |
| ocor_LATITUDE e ocor_LONGITUDE | Concatenar coordenadas | ocor_coordenadas |
1: Indexando dados tratados do SIM em banco de dados NoSQL Elasticsearch:
Uma vez finalizada a trasformação/preparação dos dados, estes são indexados em um servidor de banco de dados NoSQL Elasticsearch localizado em infraestrutura da PCDaS no LNCC.
O código utilizado para indexação está disponível em formato Jupyter Notebook: Notebook Python
O download e a prévia do conjunto de dados resultante de todo o tratamento realizado e indexado no Elasticsearch estão disponíveis na PCDaS, assim como o dicionário de variáveis:
Dados e dicionário de variáveis - SIM
Os dados SIM indexados no Elasticsearch possibilitam o desenvovimento de dashboards Kibana para a visualização simples, rápida e dinâmica de grandes quantidades de dados. Os dashboards desenvolvidos estão disponíveis para visualização na PCDaS: