Tesouro Direto – Do Excel para o R

Trabalhando com o Excel

Introdução

Este post é uma continuação de:
https://dadosdadosdados.wordpress.com/2015/06/28/tesouro-direto-download-de-dados-com-o-r-2/

A análise de dados utilizando o R começa com a entrada de dados. Esses podem partir de várias fontes e formatos e quase sempre os dados crus precisam ser tratados antes de serem trabalhados.

Uma das formas mais comuns de entrada de dados no R é através de planilhas do Excel, porém as versões antigas dos pacotes de leitura de Excel para o R não eram tão confiáveis, por isso é comum encontrar diversos tutoriais na web dizendo que a melhor forma de se importar uma planilha de Excel no R é salvando-a em *.csv.

Atualmente diversos aprimoramentos foram feitos nos pacotes do R que melhoram muito o controle que temos das planilhas importadas no sistema. Um desses pacotes é o 'XLConnect'. O pacote XLConnect possui várias facilidades para trabalharmos com arquivos de Excel no R, tudo o que precisamos fazer é carregar a biblioteca e usar o comando 'loadWorkbook' que carrega o Excel como um objeto no R, o qual podemos acessar com outros métodos como getSheets().

Nesse post vamos dar alguns exemplos de uso desse pacote, mas antes precisamos dos dados. Você deve se lembrar que no post anterior criamos um data.frame com os nomes dos títulos, anos e links. Vamos continuar incrementando nossa base a partir dele.

Como escrevemos os nossos posts de forma totalmente desorganizada usando o Knitr que compila o código direto para o WordPress. Ou replicamos todo aquele código aqui (mesmo que oculto) ou exportamos os objetos de interesse daquele post para este.

Para exportar um objeto do R e utiliza-lo depois utilizamos os comandos 'dump' e 'source', por exemplo:

Utilizamos este código no primeiro post, ele faz com que o objeto 'baseEnd' seja salvo na minha pasta TD com o nome de 'baseEnd.R':

dump('baseEnd','.\\TD\\baseEnd.R')

Depois é só carrega-lo em outro momento com:

source('.\\TD\\baseEnd.R')

Outra maneira (talvez menos elegante) seria simplesmente exportar um csv e depois carregar esse csv aqui.

Carregando os arquivos

Primeiro vamos gerar uma lista do que temos:

arquivos <- list.files('.\\TD', '*.xls')

Vamos aproveitar esse blog para criar uma lista de 'regras' do trabalho com dados:

Regra #1 – Se existem duas fontes diferentes para o mesmo dado, elas não são compatíveis.

Sabendo disso, vamos carregar todos os arquivos na memória, em uma lista (o objeto R) e depois decidimos o que fazer com eles.

Apesar do título do post ser 'Do Excel para o R', a parte mais fácil é ler o Excel, simplesmente utilizamos o pacote 'XLConnect' e o comando 'loadWorkbook'

library(XLConnect)
#i <- 1
lista <- list()
for (i in seq_along(arquivos)){

        lista[i] <- loadWorkbook(paste('.\\TD\\',arquivos[i],sep=''))

}

Os arquivos já estão carregados na memória.

Lendo as planilhas

As planilhas estão na memória do R, só que agora vem o truque:

Temos que mesclar todas as planilhas e todas as abas em uma base só que obedeça aos critérios de uma base organizada:

1. Cada variável forma uma coluna
2. Cada observação forma uma linha
3. Cada unidade observacional forma uma tabela

Para mais detalhes leia os links abaixo:

https://dadosdadosdados.wordpress.com/2015/06/19/organizando-os-dados/

https://dadosdadosdados.wordpress.com/2015/06/19/tutorial-r-organizando-dados-com-dplyr-e-tidyr/

O que são as variáveis nesse caso:

• Datas
• O nome da aba (nome do título – LFT 010116, NTN-B Principal 150515 etc)
• O tipo de Preço ou taxa – Essa parte é discutível se estamos falando de observações na mesma data ou variáveis, mas colocar isso como variável vai facilitar muito mais para a frente

O que são as observações:

• Os valores (claro!)

Em suma, queremos transformar esse monte de arquivos e abas em:

Data / Nome do Título / Tipo de Observação / Valor

Tudo em um bloco só…para começar a brincar

Acessando listas

Uma forma de pensar em listas é imagina-las como aquelas bonecas Russas que tem várias outras umas dentro das outras, só que em um universo paralelo onde dentro de uma boneca menor mora uma boneca maior…

…Não ajudou, então vamos dar uma olhada na prática…

Dada nossa lista de planilhas

head(lista)

## [[1]]
## [1] ".\\TD\\LFT_2002.xls"
## 
## [[2]]
## [1] ".\\TD\\LFT_2003.xls"
## 
## [[3]]
## [1] ".\\TD\\LFT_2004.xls"
## 
## [[4]]
## [1] ".\\TD\\LFT_2005.xls"
## 
## [[5]]
## [1] ".\\TD\\LFT_2006.xls"
## 
## [[6]]
## [1] ".\\TD\\LFT_2007.xls"

Podemos acessar seu primeiro elemento e aqui já começa o rolo, se utilizarmos um colchete '[]' o que é retornado é outra lista.

lista[1]

## [[1]]
## [1] ".\\TD\\LFT_2002.xls"

class(lista[1])

## [1] "list"

Nós não queremos isso! Queremos acessar o objeto correto que está guardado na lista, ou seja, a planilha, para isso utilizamos 2 colchetes '[[]]'.

lista[[1]]

## [1] ".\\TD\\LFT_2002.xls"

class(lista[[1]])

## [1] "workbook"
## attr(,"package")
## [1] "XLConnect"

Ótimo, agora já podemos brincar com o que foi retornado como se fosse uma planilha, por exemplo descobrindo quantas abas estão dentro dela.

getSheets(lista[[1]])

## [1] "LFT 210104" "LFT 160205" "LFT 180106" "LFT 170107"

E podemos acessar o conteúdo das abas usando mais colchetes

kable(head(lista[[1]][1]))

Vencimento	X2004.01.21.00.00.00	Col3	Col4	Col5
Dia	Taxa Compra 9:00	Taxa Venda 9:00	PU Compra 9:00	PU Venda 9:00
2002-01-02 00:00:00	NA	NA	NA	NA
2002-01-03 00:00:00	NA	NA	NA	NA
2002-01-04 00:00:00	NA	NA	NA	NA
2002-01-07 00:00:00	0,24%	0,26%	1.263,01	1.262,50
2002-01-08 00:00:00	0,24%	0,26%	1.263,90	1.263,39

Voltando a Regra #1, vamos dar uma olhada em uma planilha mais recente (o índice 1 é de uma planilha de 2002, o índice 78 de uma de 2015).

kable(head(lista[[78]][1]))

Vencimento	X01.01.2017	Col3	Col4	Col5	Col6
Dia	Taxa Compra Manhã	Taxa Venda Manhã	PU Compra Manhã	PU Venda Manhã	PU Base Manhã
02/01/2015	12,99%	13,05%	953,13	952,20	951,74
05/01/2015	12,98%	13,04%	953,75	952,82	952,35
06/01/2015	12,90%	12,96%	955,45	954,52	954,06
07/01/2015	12,74%	12,80%	958,40	957,47	957,01
08/01/2015	12,69%	12,75%	959,64	958,70	958,25

O que temos de errado:

1. A primeira linha é irrelevante
2. A segunda linha, que era para ser o nome das colunas tem tamanhos e nomes diferentes entre os arquivos
3. Datas
4. As observações estão em colunas
5. Pontos e vírgulas

Tratando as planilhas

Primeiro vamos dar uma olhada na primeira linha de cada planilha, pois vamos ter que compatibilizar isso. Vamos assumir que o cabeçalho das abas dentro de uma planilha de um ano é o mesmo, só para simplificar.

library(dplyr)
a <- NULL
b <- NULL

for (i in seq_along(lista)){

        a <- lista[[i]][1][1,]
        colnames(a) <- paste('col',seq(1:length(a)),sep='')
        b <- bind_rows(a,b)
}

c <- sapply(b, function(x) unique(x))
d <- do.call(cbind.data.frame, c)
d <- as.data.frame(as.matrix(d),stringsAsFactors=F)

kable(d[,1:6])

col1	col2	col3	col4	col5	col6
Dia	Taxa Compra Manhã	Taxa Venda Manhã	PU Compra Manhã	PU Venda Manhã	PU Base Manhã
Dia	Taxa Compra 9:00	Taxa Venda 9:00	PU Compra 9:00	PU Venda 9:00	PU Base 9:00
Dia	Taxa Compra Manhã	Taxa Venda Manhã	PU Compra Manhã	PU Venda Manhã	PU Extrato 9:00
Dia	Taxa Compra 9:00	Taxa Venda 9:00	PU Compra 9:00	PU Venda 9:00	NA

O código acima é onde a arte se encontra com a ciência…

1. Criamos um data.frame 'b' que vai agrupando as primeiras linhas das primeiras abas de todas as planilhas
   + O comando rbind() não funciona pois o número de colunas pode variar, por isso usamos o bind_rows da biblioteca dplyr

2. Apesar de não sermos muito fãs das funções apply (por elas serem menos intuitivas de usar), criamos uma lista 'c' que passa a função 'unique()' em cada coluna de b, retirando somente os valores únicos

3. Transformamos a lista 'c' em um data.frame juntando as colunas com um do.call, pois data.frames são mais fáceis de trabalhar.

Note que ainda assim, temos várias colunas vazias, isso pode ter ocorrido na leitura do Excel onde o arquivo 'pensa' que contém um range maior do que o que conseguimos enxergar. Uma maneira de testar isso é apertando CTRL+END em uma planilha bem velha (daquelas que todo mundo usa) e o Excel vai te jogar em uma área em branco, onde ele 'acha' que é o final da planilha…Bem, para nós isso é irrelevante, por isso não vamos tratar

O que importa aqui são os nomes únicos, pois precisamos de um só para todos…

Olhando para a tabela acima, aparentemente não temos nenhuma diferença significativa nos campos. 'PU X manhã' == 'PU X 9:00', a mesma coisa com Taxas. Acredito que o maior problema está no 'Base', onde aparentemente algumas planilhas não possuem esta coluna.

Finalizando, vamos usar como nomes de colunas os valores da primeira linha da tabela acima, pois são os nomes usados nas planilhas mais novas e nas planilhas que não possuem o valor 'Base', azar, ficamos com um buraco, não há muito o que fazer…Ainda não temos o poder de criar dados que não existem no R… ainda…

Juntando as peças

Agora é a hora de ler arquivo por arquivo, aba por aba remover a primeira linha, substituir o nome das colunas e juntar tudo em um único data.frame.

a <- NULL
b <- NULL
MASTER <- NULL
conteudo <- NULL
i <- 1
ii <- 1
for (i in seq_along(lista)){
        for (ii in 1:length(getSheets(lista[[i]]))){
                colunas <- min(length(lista[[i]][ii]),6)
                conteudo <- lista[[i]][ii][-1,1:colunas]
                colnames(conteudo) <- d[1,1:colunas]
                conteudo$Nome <- getSheets(lista[[i]])[ii]
        }
        MASTER <- bind_rows(MASTER,conteudo)
}

Feito…Os pontos 1 e 2 estão ok…Agora, precisamos arrumar as datas, deixar a base em um formato adequado e trocar vírgulas por pontos, mas isso fica para um próximo post.