Como baixar dados em planilha e arrumar para arquivo de texto

Suponha que você tenha feito um script que precisa de dados de entrada disponibilizados por um site. O problema é que esse site deixa disponível para download um arquivo de planilha Excel (xls), pdf ou documento de texto Word (doc), e ainda por cima compactado. Esses não são bons formatos para guardar muitos dados, já que são difíceis de manipular e ainda gravam um monte de informações a respeito de formatação (muitas vezes incompatíveis dependendo do sistema operacional ou versão do leitor), inúteis para quem vai trabalhar só com os valores e/ou texto puro (popularmente chamado de "ASCII").

Já passou por isso? Então seus problemas acabaram! Chegou o novo script "baixa_arruma_dados_tabajara.sh"... tá, esse script pode não resolver seus problemas mas já é um começo para automatizar a tarefa. Para resolver esse problema foram criadas duas rotinas (uma em shell script e outra em R):

- Script "baixa_arruma_dados.sh"

Tem a função de baixar o arquivo XLS de um link, converter para csv e selecionar linhas que interessam. Como exemplo, está um arquivo do site do Operador Nacional do Sistema Elétrico (ONS) - não garanto que estará sempre lá esse arquivo, mas lá tem algumas linhas com valores médios, e são essas que interessam. O nome desse arquivo deve entrar como argumento. O script também chama a segunda rotina (veja mais abaixo) e depois remove os arquivos temporários.

#!/bin/bash
# Script para baixar XLS, converter para ASCII e extrair dados
# Ficar atento para possíveis alterações na formatação!

DIR=$(readlink -f -- $0 | awk -F "/" 'sub(FS $NF,x)')/dados
file="Enas_REE_1931_2015.xlsx"

# Baixar arquivo XLS do site ONS
wget -nv -P $DIR http://www.ons.org.br/download/operacao/hidrologia/$file

# Converter XLS -> CSV (converter terminação de linha DOS para UNIX também)
filename=$(echo $file | cut -d'.' -f1)
xlsx2csv $DIR/$file $DIR/$filename.txt
dos2unix $DIR/$filename.txt
# Gravar arquivo temporário com linhas contendo "(", que possuem nome da bacia e as médias mensais
ano=$(echo $filename | cut -d'_' -f4)
# aproveitar pra substituir/retirar o que não interessa: inicio doido, descrições em parênteses, complementos de nomes; 
more $DIR/$filename.txt | grep "(" | sed "s/^,med(1931 - $ano),//" | sed "s/ [(][^)]*[)]//g" | sed "s/REE //g" | sed "s/ ARTIFICIAL//g" | sed "s/ //g" | sed "s/)//g" > $DIR/temp
# Montar cabeçalho: selecionar somente os nomes das bacias, passar para caixa baixa (exceto primeiro caractere), substituir caracteres especiais e espaços por _
cat $DIR/temp | sed 's/[0-9]*//g' | sed 's/,//g' | tr -d '.' | grep [A-Z] | sed 's/\b\([[:alpha:]]\)\([[:alpha:]]*\)\b/\u\1\L\2/g' | sed 'y/áÁàÀãÃâÂéÉêÊíÍóÓõÕôÔúÚçÇ/aAaAaAaAeEeEiIoOoOoOuUcC/' | sed 's/Telespires/TelesPires/g' | sed 's/Belomonte/BeloMonte/g' | sed ':a;N;$!ba;s/\n/,/g' > $DIR/cabecalho.csv
# Montar médias: pegar somente linhas e colunas com as médias
cat $DIR/temp | grep '^[0-9]' | awk -F',' '{print $1 "," $3 "," $5 "," $7 "," $9 "," $11 "," $13 "," $15 "," $17 "," $19 "," $21 "," $23}' > $DIR/medias.csv

# Inverter linhas e colunas, apagar espaços em branco e gravar arquivo final
Rscript $(readlink -f -- $0 | awk -F "/" 'sub(FS $NF,x)')/transpose.R $DIR/medias.csv $DIR/mediast.csv

# Juntar cabeçalho e medias
cat $DIR/cabecalho.csv $DIR/mediast.csv > $DIR/MLT_Ree.csv

# Limpar toda essa bagunça
rm -f $DIR/$file $DIR/$filename.txt $DIR/temp $DIR/cabecalho.csv $DIR/medias.csv $DIR/mediast.csv

O comando dos2unix serve converter a quebra de linha do formato DOS para o UNIX. Para conversão dos formatos da Microsoft, foram utilizados os programas xls2csv e xlsx2csv, instalados através de "sudo apt install catdoc xlsx2csv" (pra trabalhar com xls e xlsx, respectivamente). Outras opções são usar o libreoffice (1ª linha) ou o gnumeric (2ª linha):

libreoffice --headless --convert-to csv $DIR/$file --outdir $DIR
ssconvert $DIR/$file -O quoting-mode=never $DIR/$filename.txt

O comando do libreoffice só funciona corretamente se não tiver nenhuma janela do Office aberta.

- Script transpose.R

A transposição de uma matriz faz com que as linhas sejam transformadas em colunas (e vice-versa). A linguagem R é muito mais elegante para transpor uma matriz do que o shell script (procure transposição de matrizes em linguagem C para ter uma ideia). O script a seguir recebe como argumento os nomes dos arquivos de entrada de dados e onde serão gravados os valores. Além de ler o arquivo csv, guardar os valores em uma matriz e transformá-la na matriz transposta, ele seleciona somente algumas linhas que interessam - no caso, as que tem o valor médio.

# Script para ler arquivo CSV, trocar linhas por colunas e apagar o que não interessa
# Recebe variáveis
args <- commandArgs(trailingOnly = TRUE)
file_in = args[1]
file_out = args[2]

# Ler arquivo
matriz = read.csv(file_in, header=FALSE, sep=",")
# Transposição de matriz
transposta=t(matriz)
# Copiar somente as linhas com valores médios
temp=transposta[c(1,2,4,6,8,10,12,14,16,18,20,22,24),]
dir = paste(file_out, sep = '')
write.table(temp, dir, quote = FALSE, row.names = FALSE, col.names = FALSE, sep = ',')

Uma outra opção, para o caso de ter opções que exijam a interação do usuário através de menu HTML, pode ser vista no post sobre Python. Veja mais opções no outro post com Exemplos de scripts para reorganizar dados. Caso queira extrair dados de um arquivo PDF, clique no link para ver como fazer isso via shell script.

(Extra) Script para baixar arquivo mais recente

Uma dica: caso esse arquivo a ser baixado seja o mais recente dentro de uma sequência de diretórios, segue um script em PHP para encontrar e baixas esse arquivo - como exemplo, segue novamente o site do ONS:

#!/usr/bin/php
<?php

$base_url = "http://www.ons.com.br";
//$html = file_get_contents($base_url."/publicacao/PrevisaoVazoes/EntradaSaida/".Date('Y'));
$html = file_get_contents($base_url."/publicacao/PrevisaoVazoes/EntradaSaida/");

//Pegar último diretório do ano
if(preg_match_all('/<a\s+href=["\']([^"\']+)["\']/i', $html, $links_ano, PREG_PATTERN_ORDER))
    $all_hrefs = array_unique($links_ano[1]);

$html = file_get_contents($base_url.end($all_hrefs));

//Pegar último diretório do mês
if(preg_match_all('/<a\s+href=["\']([^"\']+)["\']/i', $html, $links, PREG_PATTERN_ORDER))
    $all_hrefs = array_unique($links[1]);

$html = file_get_contents($base_url.end($all_hrefs));

//Pegar último arquivo
if(preg_match_all('/<a\s+href=["\']([^"\']+)["\']/i', $html, $links_download, PREG_PATTERN_ORDER))
    $all_hrefs = array_unique($links_download[1]);

$data_link = $base_url.end($all_hrefs);
exec("wget $data_link");

?>

Caso você seja o programador da página, existe uma forma bem fácil de criar um botão para o usuário exportar uma tabela HTML para um arquivo CSV a ser baixado diretamente para o computador do usuário, sem tem que gerar arquivo no servidor, somente usando javascript/jQuery - clique no link para saber como usar.

Compartilhe o link desse texto, mas se for copiar algum trecho, cite a fonte. Valorize nosso trabalho.
Mais informações na licença de uso do site.

2 Pingbacks/Trackbacks

  • Othon

    Cara muito bom esse seu tutorial.
    Parabéns pela iniciativa e por ser um cara generoso.
    Estou fazendo uma disciplina de mineração de dados com tabelas gigantescas.
    Tinha desistido, momentaneamente de trabalhar com o R por achar que a curva de aprendizado fosse longa para um mestrado.

    Com esses seus scripts, tenho sido encorajado a tentar mais uma vez.

    Obrigado por compartilhar!!

    • Vinicius Roggério da Rocha

      Legal, o R é bem apropriado para trabalhar com tabelas de dados, e ainda por cima é uma linguagem livre, diferente de alguns “concorrentes” dela. Em pouco tempo já dá para se acostumar com ela, que tem muitas funções e pacotes que permitem expandir a programação sem tanto trabalho. Digo isso por experiência própria.

      Obrigado pela visita!

  • Pingback: Como extrair dados de arquivo PDF | Monolito Nimbus()

  • Pingback: Exemplos de scripts para reorganizar dados | Monolito Nimbus()