R para usuários do Excel: tabelas dinâmicas, VLOOKUPs em R

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br


[Esteartigofoipublicadopelaprimeiravezem[Thisarticlewasfirstpublishedon business-science.io, e gentilmente contribuiu para os R-blogueiros]. (Você pode relatar um problema sobre o conteúdo desta página aqui)


Deseja compartilhar seu conteúdo com R-blogueiros? clique aqui se você tiver um blog ou aqui se não tiver.

Novos analistas de negócios e financeiros estão descobrindo R todo dia. A maioria destes novos userRs (Usuários R) são provenientes de um background que não é de programação. Eles têm ampla experiência no domínio em funções como finanças, marketing e negócios, mas sua ferramenta de escolha é Excel (ou mais recentemente, Tableau e PowerBI).

Aprendendo R pode ser um grande obstáculo. Você precisa aprender estruturas de dados, algoritmos, ciência de dados, aprendizado de máquina, aplicativos Web com Shiny e mais para conseguir realizar um painel básico. Isto é um BIG ASK para não codificadores. Este é o problema que pretendo começar a resolver com o próximos liberação de tidyquant v1.0.0. Leia o atualizado “R para usuários do Excel” em ciência de negócios.

Vamos demonstrar vários dos novos recursos do Excel!

Excel em R: Introdução ao tidyquant
Novos recursos na v1.0.0.9000 (Beta no GitHub)

Dentro tidyquant versão 1.0.0.9000 (ainda está no modo beta no GitHub aqui, mas o lançamento esperado do tidyquant v1.0.0 para CRAN é março de 2020), Adicionei os seguintes recursos ao suporte novo Comercial transição de um Excel fundo.

Novas características
Para facilitar a transição para o R para usuários do Excel

✅ Tabelas dinâmicas em R

✅ VLOOKUP em R

✅ Resumindo as funções “IFS”

✅ 100 + Novas Funções do Excel

✅ NOVAS Funções Tidyverse – Resumir por Tempo

📅 NOVAS integrações de API (implementação prevista para março)

Vou mostrar um pequena porção dos novos recursos deste post. Attend Learning Lab 30 (Registre-se aqui gratuitamente) para um exemplo real de negócios onde eu mostro Shiny e tidyquant juntos. Você verá como o novo tidyquant recursos simplificam o desenvolvimento de Financeiro e de Negócios Shiny Apps.

Requisitos de replicação

Por favor, use tidyquant (>= 1.0.0). A instalação é recomendada via GitHub até o lançamento oficial do CRAN (previsto para março de 2020).

devtools::install_github("business-science/tidyquant")

Carregue as seguintes bibliotecas.

library(tidyverse)
library(tidyquant)
library(knitr)

✅ Tabelas dinâmicas

A tabela dinâmica é um dos recursos mais poderosos do Excel. Honestamente, quando cheguei ao R, uma das maiores coisas que perdi foi a Tabela Dinâmica – Uma ferramenta usada para resumir rapidamente os dados em uma tabela digerível. Está agora em R com pivot_table().

Tabela Dinâmica do Excel

Para aqueles que podem nunca ter usado o Tabela Dinâmica do Excel antes, o processo é mais ou menos assim.

  1. Comece com uma tabela bruta no formato “longo”, em que cada linha é uma observação única
  2. Use a caixa de diálogo Tabela dinâmica do Excel para posicionar os campos em colunas, linhas e valores resumidos
  3. A tabela dinâmica é retornada com os dados resumidos em colunas e linhas
Leia Também  Comentários sobre o código-fonte do modelo COVID-19 da Imperial

Tabelas dinâmicas

A Tabela Dinâmica do Excel está agora em R

R Tabela dinâmica

Tabela dinâmica do Excel agora em R com pivot_table(). Vamos tentar.

Primeiro, vamos obter alguns dados brutos. o FANG conjunto de dados é enviado com tidyquant e representa a saída do tq_get(c("FB", "AMZN", "NFLX", "GOOG")) de 2013 a 2016. São dados brutos do OHLCV com preços ajustados das ações baixados do Yahoo Finance.

FANG
## # A tibble: 4,032 x 8
##    symbol date        open  high   low close    volume adjusted
##                       
##  1 FB     2013-01-02  27.4  28.2  27.4  28    69846400     28  
##  2 FB     2013-01-03  27.9  28.5  27.6  27.8  63140600     27.8
##  3 FB     2013-01-04  28.0  28.9  27.8  28.8  72715400     28.8
##  4 FB     2013-01-07  28.7  29.8  28.6  29.4  83781800     29.4
##  5 FB     2013-01-08  29.5  29.6  28.9  29.1  45871300     29.1
##  6 FB     2013-01-09  29.7  30.6  29.5  30.6 104787700     30.6
##  7 FB     2013-01-10  30.6  31.5  30.3  31.3  95316400     31.3
##  8 FB     2013-01-11  31.3  32.0  31.1  31.7  89598000     31.7
##  9 FB     2013-01-14  32.1  32.2  30.6  31.0  98892800     31.0
## 10 FB     2013-01-15  30.6  31.7  29.9  30.1 173242600     30.1
## # … with 4,022 more rows

Podemos resumir essas informações com uma tabela dinâmica usando pivot_table(.rows, .columns, .values). Primeiro, vou dar uma olhada para ver se há algum ausente (NA) valores. O único truque é usar um ~ na frente de qualquer cálculo. Todos os zeros são bons.

FANG %>%
    pivot_table(
        .columns = symbol,
        .values  = ~ SUM(is.na(adjusted))
    ) %>%
    kable()
AMZNFBGOOGNFLX
0 00 00 00 0

Em seguida, farei algumas resumos financeiros. Gostaria de dar uma olhada nas porcentagens de retorno por ano e trimestre. Isso é fácil usando funções empilhadas com o c() operador ligado .columns e .rows.

FANG %>%
    pivot_table(
        .rows    = c(symbol, ~ QUARTER(date)),
        .columns = ~ YEAR(date),
        .values  = ~ (LAST(adjusted) - FIRST(adjusted)) / FIRST(adjusted)
    ) %>%
    kable()
símboloTRIMESTRE (data)2013201420152016
AMZN10,0356768-0.15478560.2060807-0,0680544
AMZN20,0614656-0,05309190.17239230.1956892
AMZN30.1082595-0,02993480.17032850.1538281
AMZN40,2425300-0,02239650,2979913-0.1038196
FB1-0,08642860.10107850,04805610.1162199
FB2-0,02546030,07457680,0502020-0,0153369
FB31.02458690.16132830,03440340.1233033
FB40,08389540,01920310,1507422-0.1065466
GOOG10,09808510,00174010,04418730,0041923
GOOG20,09882790,0143170-0,0406450-0,0770892
GOOG3-0,0134815-0.00911320.16591280.1116688
GOOG40.2634837-0,07367980,2414403-0.0009578
NFLX11.0571677-0,02973930.1941594-0,0702983
NFLX20.15710140,20814940.5901917-0,1345316
NFLX30,3786783-0,04633270.10278440,0194477
NFLX40,1341568-0,22149040,07926020,2062750

Alguns pontos:

  1. Colapso – Eu apenas usei funções para recolher a data diária em YEAR() e QUARTER(). Isso essencialmente cria uma nova variável de agrupamento que faz parte da data.
  2. Empilhamento – EU empilhado várias operações de agrupamento com o c() operador: .rows = c(symbol, ~ QUARTER(date))
  3. Detalhes da sumarização – Adicionei várias chamadas de função para obter a variação percentual no .values operação de compactação. Isso é permitido desde que o resultado retorne um único valor.
  4. Tilde Obrigatório – Para cálculos (por exemplo, ~ YEAR(date)), Usei o til (~) cada vez que É necessário.
  5. Til não obrigatório – Para nomes de colunas simples sem cálculo, um til (~) é não requerido.
Leia Também  Brilhante: ajuste de desempenho com futuro / promessas - a prática

Minha parte favorita das tabelas dinâmicas em R

Podemos alternar facilmente as Tabelas Dinâmicas para fornecer diferentes níveis de resumo. Agora, mudarei rapidamente para retornos por ano. Observe que estou usando uma nova função de resumo, PCT_CHANGE_FIRSTLAST() para me poupar digitando.

FANG %>%
    pivot_table(
        .rows    = symbol,
        .columns = ~ YEAR(date),
        .values  = ~ PCT_CHANGE_FIRSTLAST(adjusted)
    ) %>% 
    kable()
símbolo2013201420152016
AMZN0.5498426-0.22016731.19074950.1772084
FB0.95178580,42606470,33409830.1255136
GOOG0.5495473-0,05324160.44600240,0404130
NFLX3.0014129-0,05845871.29454910.1258640

✅ VLOOKUP

Quando comecei a aprender R, não conseguia entender como mesclar / associar dados. Foi muito frustrante porque eu estava acostumado com a função VLOOKUP do Excel que extraía um valor ou uma coluna de valores conforme necessário.

Excel VLOOKUP

VLOOKUP é uma função do Excel para pesquisar e recuperar dados de uma coluna específica da tabela. Veja como o processo funciona no Excel.

  1. Comece com uma tabela de pesquisa. Contém pares de valor-chave.
  2. Caso simples – use um VLOOKUP para inserir um único valor e gerar um único valor.
  3. Caso mais poderoso – use um VLOOKUP para adicionar uma coluna a uma tabela do Excel.

VLOOKUP

O Excel VLOOKUP () está agora em R

R VLOOKUP

A mais popular função de referência do Excel, VLOOKUP, agora está em R como VLOOKUP(). É vetorizado, o que significa que podemos usar VLOOKUP() dentro de dplyr::mutate().

Vamos replicar um VLOOKUP em R usando o novo VLOOKUP() função. Primeiro, vamos criar uma tabela de pesquisa simples.

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br
lookup_table <- lookup_table <- tibble(
    stock   = c("FB", "AMZN", "NFLX", "GOOG"),
    company = c("Facebook", "Amazon", "Netflix", "Google")
)

lookup_table %>% kable()
estoquecompanhia
FBFacebook
AMZNAmazonas
NFLXNetflix
GOOGGoogle

Caso VLOOKUP simples

Primeiro, imitemos o caso “simples” em que apenas queremos pesquisar um Valor unico.

VLOOKUP("AMZN", lookup_table, stock, company)
## [1] "Amazon"

Então o que aconteceu? Fornecemos a string “AMZN” e o VLOOKUP() nova função para pesquisar o lookup_table combinando o stock coluna e retornando o company.

Caso VLOOKUP mais poderoso

Vamos tentar mais Caso poderoso – puxando uma coluna de valores de pesquisa correspondentes. Podemos fazer isso usando o mutate() função de dplyr. Isso funciona porque VLOOKUP() é vetorizado.

FANG %>%
    mutate(company = VLOOKUP(symbol, lookup_table, stock, company))
## # A tibble: 4,032 x 9
##    symbol date        open  high   low close    volume adjusted company 
##                           
##  1 FB     2013-01-02  27.4  28.2  27.4  28    69846400     28   Facebook
##  2 FB     2013-01-03  27.9  28.5  27.6  27.8  63140600     27.8 Facebook
##  3 FB     2013-01-04  28.0  28.9  27.8  28.8  72715400     28.8 Facebook
##  4 FB     2013-01-07  28.7  29.8  28.6  29.4  83781800     29.4 Facebook
##  5 FB     2013-01-08  29.5  29.6  28.9  29.1  45871300     29.1 Facebook
##  6 FB     2013-01-09  29.7  30.6  29.5  30.6 104787700     30.6 Facebook
##  7 FB     2013-01-10  30.6  31.5  30.3  31.3  95316400     31.3 Facebook
##  8 FB     2013-01-11  31.3  32.0  31.1  31.7  89598000     31.7 Facebook
##  9 FB     2013-01-14  32.1  32.2  30.6  31.0  98892800     31.0 Facebook
## 10 FB     2013-01-15  30.6  31.7  29.9  30.1 173242600     30.1 Facebook
## # … with 4,022 more rows

O que mais me anima a usar o VLOOKUP para

Mal posso esperar para usar VLOOKUP() dentro Shiny aplicativos. Muitas vezes, quero que o usuário insira uma variável (uma chave) e internamente no Shiny Server a converta em algo mais útil em uma tabela (um valor). Mostrarei esta técnica AO VIVO no Learning Lab 30 – Aplicativos brilhantes e organizados (cadastre-se aqui gratuitamente).

Leia Também  MLOPS para R com o Azure Machine Learning

✅ Resumindo as funções “IFS”

Uma das funções que muitos usuários do Excel (inclusive eu) se acostumam é: somatórios de filtragem, aos quais me refiro como funções “IFS”. Eles são muito úteis para filtrar rapidamente as condições e agregar seus dados.

Soma-se do Excel (e amigos)

Excel tem SUMIFS(), COUNTIFS(), AVERAGEIFS()e várias outras funções resumidas do “IFS”. Veja como eles funcionam:

  1. Desenvolva uma Condição para resumir: Soma Vendas quando Região = “Leste”
  2. Use uma ou mais coluna (s) de condicionamento para desenvolver casos lógicos (por exemplo, região = “Leste”)
  3. Use uma Coluna resumida para agregar (por exemplo, SUMIFS (vendas))
  4. Junte tudo, retornando um único valor: SUMIFS (vendas, região, “= Leste”)

SUMIFS do Excel

O Excel SUMIFS agora está em R como SUM_IFS ()

R Soma-Se (e amigos)

R agora possui um conjunto completo de funções “IFS”. Podemos testá-los para obter o básico.

SUM_IFS ()

Resumindo as coisas.

SUM_IFS(x = 1:10, x > 5)
## [1] 40

COUNT_IFS ()

Contando coisas.

COUNT_IFS(x = letters, str_detect(x, "[a-c]"))
## [1] 3

Usado em tidyverse

Vamos usar COUNT_IFS() para contar quantas vezes ocorre um volume comercial alto em 2015. Podemos fazer isso de maneira chocante e fácil combinando summarise() e a COUNT_IFS() função.

FANG %>%
    group_by(symbol) %>%
    summarise(
        high_volume_in_2015 = COUNT_IFS(volume,
                                        year(date) == 2015,
                                        volume > quantile(volume, 0.75))
    )
## # A tibble: 4 x 2
##   symbol high_volume_in_2015
##                   
## 1 AMZN                    62
## 2 FB                      15
## 3 GOOG                    19
## 4 NFLX                    54

✅ Mais de 100 novas funções do Excel

Mais de 100 funções estatísticas, de data / hora e de matemática financeira baseadas em Excel foram portados para R. O que mais me empolga são os cálculos do Calendário Comercial:

  • Calendários de férias de negócios – Resolve um grande problema nos cálculos de datas comerciais. Integrações com lubridate e timeDate habilitaram as Seqüências de datas de feriados para automatizar o cálculo dos dias úteis líquidos e períodos úteis.

  • Cálculos Financeiros – eu portado NPV, IRR, FV, PV, PMTe RATE. Então percebi que há um pacote incrível chamado FinCal. O plano é alavancar FinCal daqui para frente.

100 Funções do Excel

Mais de 100 funções do Excel agora em R

Calendários de Negócios: Factoring em Férias de Negócios Facilitado

As empresas confiam em sua capacidade de prever com precisão as receitas. Um dos principais fatores é se a empresa está ou não aberta (chocante!). Para uma empresa fechada nos fins de semana e feriados comerciais padrão, agora é super fácil calcular algo simples como NET_WORKDAYS().

Exemplo de dias úteis líquidos

Quando executado apenas com início e fim, retorna o número de dias, exceto os finais de semana.

NET_WORKDAYS("2020-01-01", "2020-07-01") # 131 Skipping Weekends
## [1] 131

Mas e as férias? Temos uma nova função chamada HOLIDAY_SEQUENCE() para calcular os feriados comerciais entre duas datas (graças a timeDate!).

HOLIDAY_SEQUENCE("2020-01-01", "2020-07-01", calendar = "NYSE")
## [1] "2020-01-01" "2020-01-20" "2020-02-17" "2020-04-10" "2020-05-25"

Agora podemos simplesmente remover essas datas do cálculo de dias úteis de rede. Temos 126 dias removendo feriados comerciais padrão.

NET_WORKDAYS("2020-01-01", "2020-07-01",
             holidays = HOLIDAY_SEQUENCE("2020-01-01", "2020-07-01",
                                         calendar = "NYSE")) # 126 Skipping 5 NYSE Holidays
## [1] 126

✅ NOVA funcionalidade Tidyverse

summarise_by_time() é uma nova variante baseada em tempo de dplyr::summarise() que permite recolher dados de séries temporais por “segundo”, “minuto”, “hora”, “dia”, “semana”, “mês”, “trimestre” e “ano”.

Por mês

Aqui está um exemplo rápido resumindo por "month".

FANG %>%
    group_by(symbol) %>%

    # Collapse from daily to FIRST value by month
    summarise_by_time(
        .date_var  = date,
        .by        = "month",
        adjusted   = FIRST(adjusted)
    )
## # A tibble: 192 x 3
## # Groups:   symbol [4]
##    symbol date       adjusted
##              
##  1 AMZN   2013-01-01     257.
##  2 AMZN   2013-02-01     265 
##  3 AMZN   2013-03-01     266.
##  4 AMZN   2013-04-01     262.
##  5 AMZN   2013-05-01     248.
##  6 AMZN   2013-06-01     267.
##  7 AMZN   2013-07-01     282.
##  8 AMZN   2013-08-01     306.
##  9 AMZN   2013-09-01     289.
## 10 AMZN   2013-10-01     321.
## # … with 182 more rows

Por ano

O benefício é que posso mudar rapidamente de período.

FANG %>%
    group_by(symbol) %>%
    summarise_by_time(
        .date_var  = date,
        .by        = "year",
        adjusted   = FIRST(adjusted)
    )
## # A tibble: 16 x 3
## # Groups:   symbol [4]
##    symbol date       adjusted
##              
##  1 AMZN   2013-01-01    257. 
##  2 AMZN   2014-01-01    398. 
##  3 AMZN   2015-01-01    309. 
##  4 AMZN   2016-01-01    637. 
##  5 FB     2013-01-01     28  
##  6 FB     2014-01-01     54.7
##  7 FB     2015-01-01     78.4
##  8 FB     2016-01-01    102. 
##  9 GOOG   2013-01-01    361. 
## 10 GOOG   2014-01-01    556. 
## 11 GOOG   2015-01-01    525. 
## 12 GOOG   2016-01-01    742. 
## 13 NFLX   2013-01-01     13.1
## 14 NFLX   2014-01-01     51.8
## 15 NFLX   2015-01-01     49.8
## 16 NFLX   2016-01-01    110.

Sobre o que estou mais animado

Eu posso usar summarise_by_time() no Shiny Apps para criar gráficos de resumo comuns. Na verdade, estou ensinando: Laboratório de Aprendizagem 30 – Brilhante e organizado para aplicativos financeiros (registre-se aqui, é grátis)

Resumir por Tempo - Aplicativo Brilhante

Resumindo por tempo em um aplicativo brilhante

📅 NOVA integração de API (implementação prevista para março)

Em breve – Tingo API é um popular software livre e aberto para preços de ações, criptomoedas e feeds intradiários da IEX (Investors Exchange). Estou planejando a integração via riingo pacote.

Sumário

Há muito para aprender. Tanto que eu não pude revisar todos os novos recursos do tidyquant v1.0.0 neste artigo. E, o mais importante, você não viu tidyquant resolver alguns problemas de negócios reais e confusos.

Tenho boas notícias. No Learning Lab 30 – Shiny + Tidyquant for Finance Apps (cadastre-se aqui, é gratuito), abordarei alguns dados financeiros reais e mostrarei como podemos fazer coisas realmente importantes, como:

  • Executar análise de portfólio
  • Use NOVOS Recursos do Excel
  • Crie aplicativos brilhantes com tabelas dinâmicas, VLOOKUPs e SUMIFS.

Experimente Brilhante + arrumado
App de modelagem financeira com brilhante e arrumado

Aprenda a fazer uma Shiny Finance App usando Shiny + tidyquant para automação de modelagem financeira – DE GRAÇA. Além disso, mostrarei minha tidyquant 1.0.0 NOVO Excel em recursos R e como eles facilitam o aproveitamento Shiny. O registro é um acéfalo. Assine aqui. 👇

Registre-se no Learning Lab 30 aqui

As inscrições encerram no dia 11 de março (dia do evento).

Laboratório 30 – Shiny + Tidyquant começa em…

countdownmail.com


var vglnk = {key: ‘949efb41171ac6ec1bf7f206d57e90b8’};

(função (d, t) {
var s = d.createElement
s.src = ‘//cdn.viglink.com/api/vglnk.js’;
var r = d.getElementsByTagName
} (documento, ‘script’));

Para Deixe um comentário para o autor, siga o link e comente no blog: business-science.io.

R-bloggers.com oferece atualizações diárias por email sobre notícias e tutoriais do R sobre o aprendizado do R e muitos outros tópicos. Clique aqui se você deseja publicar ou encontrar um emprego em ciência da dados / R.


Deseja compartilhar seu conteúdo com R-blogueiros? clique aqui se você tiver um blog ou aqui se não tiver.



cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br