Bases de dados: unindo e processando

Notas

Texto com fundo branco indica comandos que devem ser digitados exatamente como aparecem.

O mesmo vale para comandos em mais de uma linha.
Neste caso, deve-se digitar o comando apresentado em cada linha, seguido da tecla Enter.

Baixando

Antes de baixar os dados, alguns testes devem ser feitos para termos certeza do que estamos baixando:

Outra coisa que devemos notar é o número de linhas (ou registros) em cada arquivo, a cada etapa do processamento. Aspas duplas, geralmente usadas para identificar campos de texto, podem ser um problema, pois aparecem também no meio do texto, gerando diversos tipos de problemas, como uma identificação incorreta do número de linhas/registros. Trataremos disso adiante.

GBIF

O portal do GBIF permite dois tipos de buscas: simples e avançada. Usamos a busca simples onde possível. Permite ainda a pesquisa por nome da localidade e por coordenadas, não fazendo distinção entre minúsculas e maiúsculas.

Assim, fizemos as seguintes buscas:

Cada busca dessas gerou um arquivo CSV, em seguida lido e processado (no software R). A busca por "Seridó" apresentou um problema, pois o GBIF trata "Seridó", "ESEC Seridó" e "ESEC do Seridó" como coisas distintas, e assim deveríamos buscar todas as combinações possíveis, o que não faz sentido. Mandamos um e-mail, e um funcionário fez a busca no sistema interno e nos enviou o CSV, com 5.821 registros.

spLink

O portal do speciesLink permite a busca por estado, município e/ou localidade.

Assim, fizemos as seguintes buscas:

Nossa primeira tentativa foi baixar os arquivos no formato CSV, mas problemas com as aspas nos levaram a optar pelo formato XLSX.

Jabot

O portal do Jabot permite a busca por estado e localidade (incluindo município). Distingue cedilha e acentuação, forçando-nos a fazer buscas separadas para "Seridó" e "Serido".

Assim, fizemos as seguintes buscas:

Fizemos ainda buscas pelos quatro municípios que compõem a ESEC do Seridó (com e sem acento):

E ampliando um pouco mais a busca (já que o município de Caicó sozinho é imenso), para sete municípios (novamente com e sem acento):

Finalmente, usamos o Maxent (com as camadas ambientais do WorldClim e uma lista prévia de coletas no interior da ESEC) para delimitarmos uma região ecologicamente similar à ESEC, chegando numa lista de 40 municípios (buscada apenas na versão sem acento):

SiBBr

O portal do SiBBr permite a busca apenas por shapefile (existe uma opção "Advanced search" que deveria permitir a busca por país/estado, mas aparentemente não está funcionando).

Este portal nos redireciona para o Portal Espacial do SiBBr, onde clicamos em Adicionar|Área, Importar|Import Shapefile, escolhemos um arquivo ZIP (contendo os arquivos do nosso shapefile), e clicamos na opção "Baixar todos os registros para ...". Na próxima tela, na opção "2. Select species", selecionamos "Todas as espécies" e, na próxima tela, clicamos no botão Download, informamos e-mail e razão do download, e mantivemos selecionada a opção "All Records".

Assim, fizemos as seguintes buscas:

O segundo shapefile tornou o primeiro obsoleto, de forma que usamos apenas o segundo.

Unindo

Daqui pra frente começamos a usar o R para unir todas as bases de dados.

Esse trabalho é cansativo e demorado, podendo levar dias. Entre uma seção e outra, podemos (e devemos) fechar o R (o RStudio, na verdade). Ele vai perguntar se queremos salvar as variáveis da memória (todas as tabelas e variáveis intermediárias criadas), ao que respondemos Não. Por duas razões: 1) não queremos todas essas variáveis intermediárias ocupando espaço na memória; e 2) preferimos rodar de novo o script, sempre que preciso (e nem sempre desde o início, embora isso também aconteça). Assim, rodando o mesmo script repetidas vezes, podemos notar erros e aperfeiçoar o script e o resultado final.

Unindo cada base

O primeiro passo é unir todas as bases baixadas de cada fonte. Por exemplo, para o Jabot:

j1 <- read.csv('Jabot_RN.csv',sep='|',stringsAsFactors=F) # 23548
j2 <- read.csv('Jabot_Seridó.csv',sep='|',stringsAsFactors=F) # 1356
j3 <- read.csv('Jabot_Serido.csv',sep='|',stringsAsFactors=F) # 7
j4 <- read.csv('Jabot_mun40.csv',sep='|',stringsAsFactors=F) # 7002

Aqui foram lidos 4 arquivos CSV, cada um guardado numa variável diferente (j1 a j4). O comentário na frente de cada linha indica quantas linhas foram lidas de cada arquivo. Comparando este número com o que vemos em outros programas (Excel, LibreOffice Calc, Notepad++ ou Geany, por exemplo), podemos descobrir problemas com aspas, entre outros. Repare que o separador de colunas do Jabot é o caracter |, enquanto outras bases usam caracteres diferentes, como , ou ;.

O próximo passo é unir essas 4 fontes numa só.

j <- rbind(j1,j2) # 24904
j <- rbind(j,j3) # 24911
j <- rbind(j,j4) # 31913

Antes de eliminarmos as linhas repetidas, devemos cuidar da diferença entre NA e ''. Isso ocorre porque, numa coluna qualquer, se houver algumas linhas com texto e outras sem, as linhas sem texto ficarão vazias (''). Se, em outro arquivo, a mesma coluna só tiver linhas vazias, esse vazio será representado de outra forma (NA). E quando pedirmos para o R comparar, ele dirá que NA e '' não são a mesma coisa (e de fato não são). Então, devemos transformar os dois símbolos numa coisa só, em cada coluna onde ambos ocorram. A função a seguir faz exatamente isso. Ela também converte todas as aspas duplas (") em um caractere chinês qualquer (escolhemos 的), pois caracteres chineses não aparecem nessas bases, ao contrário de qualquer outro caractere presente em nosso teclado. E devemos sumir com as aspas logo, antes que elas nos causem maiores problemas (afinal, precisaremos das aspas para identificar alguns textos, por exemplo, textos contendo quebras de linha). Ao final do processo transformaremos de volta esse caractere chinês em aspas duplas.

tiraNAesp <- function(tab) {
  for (i in 1:ncol(tab)) {
    # troca " por 的
    tab[,i] <- gsub('"','的',tab[,i])
    # se a coluna i tem ao menos 1 tab E ao menos um '' (texto em branco)
    if (anyNA(tab[,i]) & length(which(tab[,i] == '')) > 0) {
      print(paste(i,names(tab)[i],nrow(tab),sep=': ')) # qual coluna?
      flush.console()
      tab[is.na(tab[,i]),i] <- '' # transforma os NA daquela coluna em ''
    }
  }
  return(tab)
}

Agora executamos essa função na tabela final (j), e depois eliminamos as linhas repetidas usando a função unique.

jj <- tiraNAesp(j) # 31913
ju <- unique(jj) # 30300
write.table(ju,'jabot30300.csv',quote=T,sep='\t',na='',row.names=F)

Com isso temos um arquivo para cada base de dados, com o número de registros devidamente identificado no nome de cada arquivo.

Unindo as diferentes bases

Cada base traz as mesmas colunas com diferentes nomes e em ordens diferentes, além de colunas exclusivas de cada base, ou compartilhadas por algumas bases, mas não por todas.

Para organizar essa bagunça, fizemos uma planilha com 5 colunas. A primeira coluna contém os nomes que desejamos para as colunas da nossa tabela final (nomes curtos, com letras minúsculas e sem espaços, para facilitar o trabalho no R; por exemplo, fam para família, gen para gênero, sp para epíteto específico, coletor para nome do coletor e numcol para número do coletor). As outras 4 colunas têm os nomes dessas colunas em cada base de dados. Por exemplo, a primeira coluna traz, numa certa linha, o valor gensp, indicando o nome científico completo (gênero + epíteto específico). No GBIF, essa coluna se chama species, e no speciesLink se chama scientificname. Essa coluna não existe no Jabot nem no SiBBr (embora nesta última base exista uma coluna semelhante, Scientific.Name, mas que costuma trazer junto o nome da família – sim, parece um pesadelo, e de certa forma é).

Uma vez decididas quais colunas entrarão na tabela final, com quais nomes e em que ordem, passamos à próxima etapa: ler cada uma das quatro tabelas (uma de cada base de dados), e deixá-las na ordem correta para a colagem final.

Às vezes, por problemas relacionados às aspas ou alguma outra coisa, podemos ter dificuldade para ler uma tabela usando read.csv. Neste caso, podemos usar outros comandos.

library(data.table)
j <- fread('jabot30300.csv') # 30300
j <- as.data.frame(j)

Ao tentar ler a tabela acima com o comando tradicional, read.csv, observamos que ela não vinha com os necessários 30.300 registros. A função fread resolveu o problema, bastando ao final converter a tabela para o formato padrão, data.frame.

Processamentos que não foram feitos anteriormente também podem ser feitos aqui.

l <- read.csv('splink10808.csv',sep='\t',stringsAsFactors=F) # 10808
l <- l[which(l$kingdom == 'Plantae'),] # 5966

Não excluímos antes tudo que não é Plantae porque queríamos visualizar a planilha no LibreOffice primeiro. Afinal, poderia haver (e havia) plantas sem essa indicação na coluna kingdom.

Processamentos similares foram feitos para outras tabelas.

s <- read.csv('SiBBr_bbox40.csv',sep=',',stringsAsFactors=F) # 8795
s <- s[which(s$Kingdom == 'Plantae'),1:44] # 4002
table(s$Basis.Of.Record)
s <- s[which(s$Basis.Of.Record == 'PreservedSpecimen'),] # 3554
g <- read.csv('gbif9535.csv',sep='\t',stringsAsFactors=F) # 9535
table(g$basisOfRecord)
g <- g[which(g$basisOfRecord == 'PRESERVED_SPECIMEN'),] # 9370

Neste ponto, temos 4 variáveis, todas de apenas uma letra (para reduzir o trabalho de digitação). Mas atenção! As letras c e t têm significado especial no R.

Agora começamos a ordenar as colunas. Para cada variável/tabela/data.frame, vamos criar as colunas que faltam (com o valor nulo, NA, em todas elas), e depois colocar as colunas na ordem desejada.

Primeiro criamos as novas colunas.

g$source <- 'GBIF'
g$grp1 <- NA
g$grp2 <- NA
g$sp <- NA
g$mun <- NA
g$latMun <- NA
g$lonMun <- NA
g$minElev <- NA
g$maxElev <- NA
g$prepType <- NA
g$diaID <- NA
g$mesID <- NA
g$anoID <- NA
g$idQualif <- NA
g$occRemark <- NA
g$barcode <- NA
g$vern <- NA

Ao contrário da maioria das colunas, a coluna source traz o nome da tabela, ao invés de NA. Assim saberemos de onde cada registro veio, após a união final.

Depois criamos uma nova variável com as colunas na ordem desejada.

names(g)
g1 <- g[,c(51,1,2,3,52,53,4:9,54,10:16,18,55,17,22,23,56,57,24,
           26,58,59,27,30:33,36,60,37:42,61:64,45:46,50,65:67)]
names(g1)

Usamos o comando names antes e depois da mudança. Ajuda a nos orientar no meio de tantas colunas, e também para confirmarmos se a transição funcionou como esperávamos.

Ver a primeira linha de cada tabela (antes e depois da mudança) também pode ajudar a ver se está tudo certo.

g[1,]
g1[1,]

Repetimos o mesmo passo para as demais tabelas.

j$source <- 'Jabot'
j$id <- NA
j$datasetKey <- NA
j$grp2 <- NA
j$class <- NA
j$order <- NA
j$gensp <- NA
j$verbatimScName <- NA
j$latMun <- NA
j$lonMun <- NA
j$coordError <- NA
j$elev <- NA
j$elevError <- NA
j$basisOfRec <- NA
j$prepType <- NA
j$diaID <- NA
j$mesID <- NA
j$anoID <- NA
j$issue <- NA
j$barcode <- NA
j$vern <- NA
j1 <- j[,c(44:46,3,39,47,41,42,48,49,33:35,50,36,37,32,51,38,17:20,25,26,52:55,
           21,22,56,11,14,13,12,57,58,6:9,27,28,59:61,30,10,31,62,15,63,64)]

l$source <- 'spLink'
l$id <- NA
l$datasetKey <- NA
l$ocurID <- NA
l$grp1 <- NA
l$grp2 <- NA
l$taxonRank <- NA
l$scNameWAuth <- NA
l$verbatimScName <- NA
l$elev <- NA
l$elevError <- NA
l$dataCol <- NA
l$basisOfRec <- NA
l$dataID <- NA
l$idQualif <- NA
l$issue <- NA
l$vern <- NA
l1 <- l[,c(52:57,7:13,5,14,58:60,15,30:33,35,34,37,36,38,61,40,41,62,
           63,26:24,64,45,2:4,21,16,65,19:17,66,23,20,67,50,51,68)]

s$source <- 'SiBBr'
s$id <- NA
s$datasetKey <- NA
s$ocurID <- NA
s$grp1 <- NA
s$sp <- NA
s$gensp <- NA
s$ssp <- NA
s$scNameAuthor <- NA
s$mun <- NA
s$latMun <- NA
s$lonMun <- NA
s$elev <- NA
s$elevError <- NA
s$dataCol <- NA
s$diaCol <- NA
s$prepType <- NA
s$recNum <- NA
s$idBy <- NA
s$dataID <- NA
s$diaID <- NA
s$mesID <- NA
s$anoID <- NA
s$idQualif <- NA
s$coletor <- NA
s$typeStatus <- NA
s$issue <- NA
s$occRemark <- NA
s$barcode <- NA
s1 <- s[,c(45:49,13,16:21,50:52,14,22,11,53,32,33,54,25,29,30,55,
           56,31,57,34,35,58,59,60,40,39,41,61,23,24,9,62:73,15)]

Finalmente juntamos as tabelas, duas a duas. Primeiro vemos se os nomes estão perfeitamente alinhados.

cbind(names(g1),names(j1))
cbind(names(g1),names(l1))
cbind(names(l1),names(s1))

Se houver espaço no seu monitor, é possível comparar 3 ou até 4 colunas de uma vez só.

Para serem unidas com o comando rbind, as tabelas devem ter os mesmos nomes.

names(j1) <- names(g1)
gj <- rbind(g1,j1)

names(s1) <- names(l1)
sl <- rbind(l1,s1)

names(sl) <- names(gj)
a <- rbind(gj,sl)

Finalmente salvamos nossa última variável, a, num arquivo CSV separado por tabulações (logo tecnicamente um TSV, mas mantemos a extensão CSV por razões históricas de comodidade).

write.table(a,'junto.csv',sep='\t',na='',quote=T,row.names=F) # 33.309

Processando