Turbinando o Excel com o R

Comemoração do Dia do Estatístico Turbinando o Excel com o R José Francisco Moreira Pessanha [email protected] Universidade do Estado do Rio de Janeiro Rio de Janeiro, 1 Junho de 2012 Introdução O R e o Excel são duas poderosas ferramentas computacionais para análise de dados. Conhecer as possibilidades de integração entre estes dois programas contribui para agilizar a análise de dados, melhorar a aprsentação de resultados e ainda possibilita desenvolver aplicativos. Objetivo: descrever diferentes maneiras de estebelecer uma integração entre o R e o Excel Para ilustrar a integração entre os programas será considerado um caso estudo sobre energia eólica. Base de dados: registros anemométricos disponibllizados na página do Projeto Sonda (INPE/CPTEC). Programa R • Linguagem e ambiente computacional para fazer análises estatísticas e mineração de dados. • É gratuito e open source. • Disponibiliza uma variedade de funções para análise estatística (regressão linear e não linear, testes estatísticos, análise de séries temporais, estatística multivariada, planejamento de experimentos, etc.). • Disponibiliza funções para a elaboração de diversos tipos de gráficos, úteis na análise exploratória de dados e visualização de resultados. • É altamente extensível. • Tem sido utilizado com sucesso em diversas aplicações da estatística nas mais diversas áreas do conhecimento. • Rápida difusão e segundo algumas estimativas conta com cerca de 2 milhões de usuários. MS Excel • Planilha eletrônica muito útil e fácil de aprender. • Organiza e armazena as informações em forma tabular. • Excelente interface com o usuário e um bom instrumento para comunicação e apresentação de informações. • Fácil integração com outras bases de dados. Suporta programação em VBA. • Contém funções estatísticas: distribuições de probabilidade, geração de números aleatórios, regressão linear, ANOVA, programação linear e não linear (Solver), etc.. Sem contar Add-Ins que podem incorporados. • Forma conveniente de fazer What-If analyses. • É um dos softwares mais utilizados no mundo. Como estabelecer uma integração entre o R e o Excel ? Duas formas diferentes de integrar o R ao Excel 1) R como uma extensão do Excel (Excel as Host) O usuário trabalha com uma versão da planilha Excel enriquecida pelas diversas funções estatísticas do R. (O R é embutido no Excel)  Adição de novos menus e novos itens no menu Produz resultados que não são atualizados automativamente pelo Excel  Novas funções que podem ser usadas como fórmulas do Excel Tem a vantagem de permitir a atualização automática dos resultados.  Funções embutidas em Macros VBA e que podem ser executadas quando solicitadas pelo usuário. 2) Excel acessado a partir do R (R as Host) O usuário trabalha com o R e tem a possibilidade de acessar (controlar) o Excel e utilizá-lo como um local onde o R pode ler os dados de entrada e gravar os resultados das análises estatísticas. (O R controla o Excel) Como fazer a integração entre o R e o Excel ? Diferentes maneiras e níveis de integração 1) Pacotes xlsReadWrite e xlsx Basicamente leitura/gravação de arquivos Excel .xls e .xlsx. 2) Pacote Excel.Link Manuseia arquivos Excel a partir do R (R as host). 3) Pacote XLConnect Manuseia arquivos Excel a partir do R (R as host). 3) Programas ExcelDNA e R.NET Cria funções no Excel capazes de executar scripts no R (Excel as host). 5) Pacote Rexcel Interface plena entre o R e o Excel Os 50 pacotes mais usados do R http://r-de-jeu.blogspot.com.br/2012/04/50most-used-r-packages.html Estudo de caso sobre energia eólica • A energia eólica é um recurso natural, limpo, abundante e inesgotável. • O seu aproveitamento para produção de energia elétrica é uma das alternativas mais promissoras para reduzir as emissões dos gases do efeito estufa e mitigar os efeitos de crises no mercado de combustíveis fósseis. • Rápida expansão da capacidade instalada da geração eólica no mundo, passando de 24.322 MW em 2001 para 159.213 MW em 2009, (World Wind Energy Report 2009 - WWEA, 2010) • Maior parte (48%) na Europa (76,218 MW), Ásia 39.961 MW (26 GW estão na China) América do Norte 38.748 MW (35.159 MW estão nos EUA). Brasil, capacidade instalada de 600 MW em 2009. • Potencial eólico brasileiro estimado em 143,5 GW a 50 metros de altura (Maior parte na região Nordeste – 52%). • Importante complemento ao regime de geração hidrelétrica nos períodos de estiagem, contribuindo para assegurar a segurança energética do País e diversificar a matriz energética brasileira. Projeto Sonda – Sistema de Organização Nacional de Dados Ambientais (INPE/CPTEC) http://sonda.ccst.inpe.br/ Exemplo de arquivo disponibilizado na página do projeto SONDA http://sonda.ccst.inpe.br/ Pacote xlsReadWrite Read and write Excel files (.xls) Arquivo Excel xlsread xlswrite Arquivo Excel Pacote xlsReadWrite: Lê e escreve arquivos Excel (.xls) Autor: Hans-Peter Suter Principais funções disponibilizadas: Objeto_R <- read.xls(‘nome do arquivo’,nº da planilha,primeira linha da planilha) dados <- read.xls('F:/SCR0601WD.xls', sheet=1, from=1) dateTimeToStr(data, “formato da data") converte datas do Excel para string dateTimeToStr(dados[,4],’dd/mm/yyyy hh:nn’) write.xls(variável, ‘nome do arquivo’,nº da planilha,primeira linha da planilha) write.xls(saida,'F:/saida.xls', sheet=1, from=1) Pacote xlsReadWrite Código em R para extração dos registros de velocidade do vento a 25 e 50 metros de altura #carrega pacote xlsReadWrite library(xlsReadWrite) # leitura do arquivo de dados do SONDA dados<-data.frame(read.xls('F:/SCR0601WD.xls',sheet=1,from=1)) dados[,4]<-dateTimeToStr(dados[1,4],'dd/mm/yyyy hh:nn') # Seleciona velocidade do vento a 25m e 50 m # de altura ( colunas 6 e 9 de dados ) velocidade <- dados[,c(6,9)] # grava em uma pasta separada as velocidades # a 25m e 50m saida<-data.frame(cbind(dados[,4],velocidade)) write.xls(saida,'F:/saida.xls', sheet=1, from=1) Gráfico gerado pelo R para a velocidade do vento a 50 metros de altura plot(velocidade[,2]) Pacote excel.link (R as Host) Convenient way to work with data in Microsoft Excel R é o programa mestre Pacote excel.link: Manuseia arquivos Excel a partir do R Autor: Gregory Demin Disponibilizas funções no R para manusear planilhas Excel, por exemplo: xl.workbook.add() # abre um novo arquivo Excel a partir do R xl.workbook.open('F:/SCR0601WD.xls') # abre um arquivo Excel xl.sheet.add(‘resultados') # adiciona pasta no arquivo Excel xl.workbook.save(“nome do arquivo”) grava um arquivo Excel xl.sheet.delete(“nome da pasta") # remove uma pasta do arquivo Excel xl[a1] <- objeto_R # insere valor do objeto_R na célula a1 da planilha Filtragem dos valores de velocidade do vento a 50 metros de altura library(excel.link) # carrega pacote excel.link # carrega script R com funções para filtragem de dados (LOESS) source('c:/turbinando/exemplo_excellink/rotinas_para_filtragem.txt') # abre arquivo saida.xls a partir do R xl.workbook.open(‘h:/saida.xls') velocidade<-xl[c2:c4465] # velocidade a 50 metros de altura # executa funções para filtrar as medições # alfa constante de alisamento para o método LOESS resultado_filtragem<-filtragem(velocidade,alfa= xl[e1],3,0,'resultados') xl.sheet.add('graficos') # adiciona uma worksheet chamada “graficos” xl[a1]=current.graphics() # insere o gráfico corrente na worksheet “graficos” # inspeciona outliers xl.sheet.activate('resultados') # ativa worksheet “resultados” graficozoom(instanteselecionado=xl[h1],janela=20,velocidade,resultado_filtragem ) xl.workbook.save('dados_filtrados') # grava o arquivo Excel “dados_filtrados” xl.workbook.close('dados_filtrados') # fecha arquivo Excel Medições de velocidade de vento e valores suavizados pelo procedimento LOESS Loess significa Local Polynomial Regression Fitting É uma função do pacote Stats Medições de velocidade de vento, valores suavizados pelo procedimento LOESS e intervalos de confiança Medições de velocidade de vento, valores filtrados e outliers Planilha com os resultados da filtragem reallizada no R Estudo de caso sobre Energia eólica A distribuição de Weibull é o modelo probabilístico que melhor descreve a distribuição de freqüência da velocidade do vento (CUSTÓDIO, 2009). v ~ Weibull(k,c) v0 v é a velocidade do vento em m/s (v0) k é o fator de forma (adimensional e k > 0) c é o fator de escala (em m/s e c > 1)  1 E v  c1    k k  v f v    c c k 1  v     c k e  v  c 1    1   k k   2      1  2  O fator de escala c tem relação direta com a velocidade média. k é um indicador da constância do vento, quanto maior o valor de k menor a dispersão em torno da velocidade média Tipicamente assume valores entre 2 e 3. Em regiões de ventos alísios, como no Nordeste brasileiro, o parâmetro k pode atingir valores superiores a 6 (AMARANTE et al, 2001). Estudo de caso sobre Energia eólica Correção dos parâmetros c e k em função da altura do rotor da turbina A velocidade do vento cresce com a altura Velocidade do vento (m/s) Lei da potência altura m  h vh  vref   h  ref      href =altura de referência (50m) h = altura do rotor vref = velocidade na altura de referência vh = velocidade na altura h  = entre 0,06 e 0,6 0,37  0,088  ln cref  Correções dos fatores c e k em função da altura h do rotor ch  cref kh  kref   h  h  ref     n 1  0,088  ln href 10 1  0,088  ln h 10 n 1  0,088  ln href 10 Aproximação que não considera a rugosidade do solo (CUSTÓDIO, 2009) Estudo de caso sobre Energia eólica A relação entre a velocidade do vento e a potência gerada é não linear P = Potência (kW) Velocidade nominal Vn (tipicamente entre 12 e 15 m/s) Velocidade de corte Vc (em geral, a 25 m/s) Potência nominal Turbina eólica Área de giro das lâminas Lâmina do rotor Diâmetro do Rotor Altura do rotor Velocidade de partida Vp (entre 2,5 e 3 m/s) Nascele Torre V = Velocidade do vento (m/s) Conteúdo energético a partir 3,5 m/s Potência gerada entre Vp e Vn 1 P  C p m  g    A v3 2 A turbina é desligada para protegê-la de esforços mecânicos Turbina Transmissão Gerador A = área de giro das lâminas m2 V Cp g m 3  = densidade do ar (kg/m ) Cp, m, g constantes que expressam os rendimentos nos estágios da conversão de energia P Estudo de caso sobre Energia eólica Curva de potência P = Potência (kW) Velocidade nominal Vn (tipicamente entre 12 e 15 m/s) Velocidade de corte Vc (em geral, a 25 m/s) Potência nominal Velocidade de partida Vp (entre 2,5 e 3 m/s) V = Velocidade do vento (m/s) P=0 Curva de potência P(V) 1 P  C p m  g    A v3 2 1 P  C p m  g    A vn3 2 P=0 velocidade < vp (velocidade de partida) vp < velocidade < vn (velocidade nominal) vn <velocidade < vc (velocidade de corte) velocidade > vc Estudo de caso sobre Energia eólica Na implantação de um aproveitamento eólico é fundamental identificar a turbina cujas velocidades características vp, vn e vc proporcionem a extração mais econômica da potência disponível no vento. O fator de capacidade (FC) é um dos parâmetros considerados na escolha da turbina mais adequada. O FC é definido pela razão entre a produção média da turbina e a sua produção potencial (máxima), se operada constantemente a plena capacidade. potência média Pmédia FC   potência máxima Pmá xima Estudo de caso sobre Energia eólica 1 3 Potência máxima Pmá xima  C p  m   g    A vn 2 Pmédia   P v  f v  dv Velocidade nominal  Potência média 0 Função densidade de probabilidade da velocidade do vento (Em geral, Weibull) v  vp 0  1 3  C p m g    A v vp  v  vn Curva de potência P v   2  1 C p m g    A vn3 vn  v  vc 2  0 v  vc 1 1 3   C pm g Av  f v  dv   C pm g Avn3  f v  dv 2 2 vp vn vn Pmédia vc Estudo de caso sobre Energia eólica JANGAMSHETTI & RAU (1999) Pmédia FC   Pmá xima c 1 1 3 3      C    Av f v dv v 2 p m g v 2 C pmg Avn  f v  dv p n vn v 1 C p m g    A vn3 2 1 FC  3  v3 f vdv   f vdv vn v p vn vn vc O fator de capacidade depende das velocidades características da curva de potência da turbina eólica (vp, vn e vc) e da função densidade de probabilidade da velocidade do vento na altura do rotor da turbina. Estudo de caso sobre Energia eólica A energia elétrica (Wh) gerada pela turbina eólica pode ser calculada pelo produto entre o fator de capacidade da turbina, a potência máxima da turbina e o período de tempo avaliado. energia  FC  potência máxima  período em horas Excel DNA + R.NET (Excel as Host) Permite criar funções no Excel que são executadas no R Excel como programa mestre Excel DNA ( http://exceldna.codeplex.com ) Integra a linguagem .NET no Excel RdotNet (http://rdotnet.codeplex.com) Integra as linguages R e .NET Excel DNA e RdoNet são open source. Um bom lugar para visitar é o blog http://mockquant.blogspot.com No blog podem ser obtidos os arquivos necessários e que devem ser armazenados em um mesmo diretório:  ExcelDNA.Integration.dll  R.NET.dll  ExcelDNA.xll  ExcelDNA.txt (arquivo com o programa) Excel DNA + R.NET <DnaLibrary RuntimeVersion="v4.0" Name="My First XLL" Language="CS"> <ExternalLibrary Path="R.NET.dll" /> <ExternalLibrary Path="ExcelDna.Integration.dll" /> <Reference Name="R.NET" /> <![CDATA[ using System; using System.Collections.Generic; using System.Linq; using System.Text; Exemplo de um código para integrar o Excel ao R com o auxílio do Excel.DNA e R.NET using ExcelDna.Integration; using RDotNet; namespace CSLib { public class CSLib { static REngine rengine = null; static CSLib() { // Local onde está instalado o arquivo R.dll O código cria uma função Excel para gerar um número aleatório N(0,1) usando a função rnorm do R REngine.SetDllDirectory(@"C:\Programas\R\R-2.13.2\bin\i386"); rengine = REngine.CreateInstance("RDotNet", new[] { "-q" }); } [ExcelFunction(Description = “gera número aleatório normal padrão")] public static double [] MyRnorm(int number) { rengine.EagerEvaluate("x<-rnorm("+ number +")").AsNumeric(); NumericVector x = rengine.GetSymbol("x").AsNumeric(); return (x.ToArray()); } } } ]]> </DnaLibrary> Cria função Excel MyRnorm Chama a função rnorm do R Excel DNA + R.NET Função MyWeibullFit [ExcelFunction(Description = "estima parametros Weibull")] public static double [] MyWeibullFit(int number) { rengine.EagerEvaluate("mes<-("+ number +")").AsNumeric(); NumericVector mes = rengine.GetSymbol("mes").AsNumeric(); rengine.EagerEvaluate(@"source('c:/ExcelDNA/ventos/fitweibull.txt')"); rengine.EagerEvaluate("estimativas").AsNumeric(); NumericVector estimativas = rengine.GetSymbol("estimativas").AsNumeric(); return (estimativas.ToArray()); } Executa código R contido no arquivo fitweibull.txt responsável peça estimação dos parâmetros da distribuição de Weibull da velocidade do vento e apresentação de histograma. Estimação pelo método dos momentos Foram utlizados os pacotes Stats, MASS e xlsReadWrite Código R para estimação dos parâmetros da Weibull pelo método dos momentos (Blischke/Scheuer)  2  1   2 S k  1  2 1   V  2 1    k  1 V  c1    k 2   2    1   2 2 S  c 1    1      k    k    c c xbar <- mean(velocidade) varx <- var(velocidade) f <- function(k){ return(gamma(1+2/k)/gamma(1+1/k)^2 - 1 - varx/xbar^2) } forma <- uniroot(f,c(0.02,50))$root escala <- xbar/gamma(1+1/shape) k̂  c k̂ V  1 1    k Excel DNA + R.NET [ExcelFunction(Description = "calcula fator de capacidade")] public static double [] MyWeibullFcap(double value1,double value2,double value3, double value4,double value5) { Função rengine.EagerEvaluate("vp<-("+ value1 +")").AsNumeric(); MyWeibullFcap NumericVector vp = rengine.GetSymbol("vp").AsNumeric(); rengine.EagerEvaluate("vn<-("+ value2 +")").AsNumeric(); NumericVector vn = rengine.GetSymbol("vn").AsNumeric(); rengine.EagerEvaluate("vc<-("+ value3 +")").AsNumeric(); Insere os valores das NumericVector vc = rengine.GetSymbol("vc").AsNumeric(); variáveis de entrada rengine.EagerEvaluate("k<-("+ value4 +")").AsNumeric(); NumericVector k = rengine.GetSymbol(“k").AsNumeric(); rengine.EagerEvaluate(“s<-("+ value5 +")").AsNumeric(); NumericVector s = rengine.GetSymbol(“s").AsNumeric(); rengine.EagerEvaluate(@"source('c:/ExcelDNA/ventos/calcfc.txt')"); rengine.EagerEvaluate("fc").AsNumeric(); NumericVector fc = rengine.GetSymbol("fc").AsNumeric(); return (fc.ToArray<double>()); } Executa código R contido no arquivo calcfc.txt responsável pela estimação do fator de capacidade da turbina eólica. Foi utilizado o pacote MASS. Código R para o cálculo do fator de capacidade (FC) 1 3 FC  3  v f vdv   f vdv vn v p vn vn vc cte<-vn^3 aux1<-function(w) (w*w*w*dweibull(w,k,s)) aux2<-function(w) (dweibull(w,k,s)) fc1<-integrate(aux1,vp,vn) fc2<-integrate(aux2,vn,vc) fc<-(1/cte)*fc1$value+fc2$value Excel DNA + R.NET Para carregar as funções MyWeibullFit e MyWeibullFca basta abrir o arquivo ExcelDNA.xll no Excel. Os registros anemométricos estão armazenados em outra planilha Excel denominada arquivo.xls, once cada coluna guarda os registros de um mês Excel DNA + R.NET Planilha Excel de onde são chamadas as funções MyWeibullFit e MyWeibullFcap Gráfico gerado pelo R MyWeibullFcap(vp,vn,vc,k,c) MyWeibullFit(mês) Pacote RExcel Thomas Baier Erich Neuwirth Baier Thomas, & Neuwirth Erich (2007). Excel :: COM :: R. Computational Statistics, Volume 22, Number 1/April 2007. Physica Verlag. http://rcom.univie.ac.at/ Oferece diferentes formas de interface entre o R e o Excel. Transferência de dados entre o R e o Excel Excel como editor de dados para serem analisados no R Excel como interface gráfica para o R Excel é um local para armazenamento de dados e códigos Executar comandos R a partir de macros em Excel VBA Permite usar comandos R em funções do Excel. Pacote RExcel Três diferentes formas de usar o Rexcel Rexcel menu R como Add-in do Excel 1) Scratchpad mode: Executa código R escrito na planilha e transfere variáveis entre o R e o Excel (Usa o Rexcel menu). 2) Macro mode: Chama funções do R a partir de macros no Excel (usa macros disponibilizados pelo Rexcel.xla). 3) Worksheet functions: Chama funções do R diretamente das células de uma planilha. Planilha do Estudo de caso Matriz de dados Cada coluna guarda a série temporal da velocidade do vento em um mês com resolução temporal de 10 minutos Parâmetros de entrada e resultados Planilha organizada em três partes 1) Matriz de dados 2) Parâmetros de controle e resultados 3) Código em R a ser executado por uma das macros Código em R escrito na planilha e chamado por uma das macros Planilha do Estudo de caso A matriz de dados tem 12 colunas, uma para cada mês O primeiro passo consiste em escolher o mês que será analisado Selecione o mês desejado com a Scrollbar A função Rput(“Mes”;P2) cria a variável Mes no R e que assume valor igual ao inserido na célula P2 O mês escolhido aparece na célula P3 Planilha do Estudo de caso Em seguida, as estatísticas descritivas devem ser calculadas Executa Macro VBA responsável pelas seguintes tarefas: 1) Carregamento dos dados de velocidade de vento no R 2) Cálculo das estatísticas descritivas no R 3) Transferência dos resultados do R para as células P5:P12 na planilha Excel Função Rput Transfere valor da variável Mes do Excel para o R =RPut("Mes";P2) Macro VBA Macro VBA para calcular estatísticas descritivas com funções do R v StartRServer Inicia chamada do R PutArray Transfere o v conteúdo em B2:X4465 para a matriz velocidade R RRun Executa um comando no R GetArray Transfere variáveis do R para determinadas células na planilha Excel v StopRServer Encerra chamada do R Planilha do Estudo de caso Estima fatores de forma e escala (parâmetros da Weibull). Modelos de turbinas Seleciona do modelo da turbina Faz histograma, QQ plot e Teste de Kolmogorov Smirnov Macro VBA RunFile Executa código em R armazenado no arquivo txt RunCode executa código em R armazenado nas células W3:W20 da planilha Macro VBA chama funções do R para estimar os parâmetros da distribuição de probabilidade da velocidade do vento PutArray Transfere o conteúdo em B2:X4465 para a matriz velocidade R RunCode executa código em R armazenado nas células W3:W20 RunCode executa código em R armazenado nas células W3:W20 da planilha GetArray Transfere resultados do R para o Excel de acordo com o modelo selecionado Código em R escrito na planilha Excel Estima os parâmetros da função densidade de probabilidade selecionada pelo usuário, calcula a estatística de Kolmogorov-Smirnov e desenha histograma e QQ-Plot Resultados gerados pelo R e exibidos na planilha Excel Planilha do Estudo de caso Cálculo do fator de capacidade =RPut("vp";P20) =RPut(“vn";P21) =RPut("vc";P22) Funções que transferem valores do Excel para o R Macro VBA para calcular fator de capacidade cte<-vn^3 aux1<-function(w) (w*w*w*dweibull(w,k,s)) aux2<-function(w) (dweibull(w,k,s)) fc2<-integrate(aux2,vn,vc) fc1<-integrate(aux1,vp,vn) fc<<-(1/cte)*fc1$value+fc2$value RunFile Executa código em R responsável pelo cálculo do fator de capacidade. Código em arquivo txt Código em R em arquivo txt Código em R para o cálculo do fator de capacidade (FC) c 1 n 3 FC  3  v f vdv   f vdv vn v p vn v v cte<-vn^3 aux1<-function(w) (w*w*w*dweibull(w,s,k)) aux2<-function(w) (dweibull(w,s,k)) fc2<-integrate(aux2,vn,vc) Variáveis definidas no Excel fc1<-integrate(aux1,vp,vn) fc<<- (1/cte)*fc1$value+fc2$value Conclusões A integração entre o R e o Excel é fácil. Oferece variadas formas novas e interessantes de usar ambos os programas. O R ganha uma poderosa interface com o usuário. O Excel é enriquecido com o poder do R. Possibilita o desenvolvimento de aplicativos (com baixo custo) que podem ser disponibilizados aos clientes. Referências bibliográficas Amarante, O.A.C., Brower, M., Zack, J. e Sá, A.L., Atlas do Potencial Eólico Brasileiro, Centro de Pesquisas de Energia Elétrica, Brasília, 2001. Baier Thomas.; Neuwirth Erich (2007). Excel :: COM :: R. Computational Statistics, Volume 22, Number 1/April 2007. Physica Verlag. http://rcom.univie.ac.at/ Custódio, R.S. Energia eólica para produção de energia elétrica, Eletrobras, Rio de Janeiro, 2009. Jangamshetti, S.H.J.; Rau, V.G. (1999), Site matching of wind turbine generators: a case study, IEEE Transactions on Energy Conversion, v. 14, n. 4, December, 1537-1543. Peternelli, L.A.; Mello, M.P. Conhecendo o R: uma visão estatística, Editora UFV, Viçosa, 2011. Ragsdale, C.T. Modelagem e análise de dados, Cengage Learning,São Paulo, 2009. Parabéns aos Estatísticos José Francisco Moreira Pessanha (UERJ) [email protected]