Pivot_Wider and Pivot_Longer

Introduction:

library(tidyverse)
library(lubridate)
library(quantmod)
library(BatchGetSymbols)

Nice Example:

Here is a nice example from the tidyr website.

set.seed(12345)

stocks <- data_frame(
  time = ymd('2009-01-01') + 0:9,  # ymd() is from the lubridate package
  X = rnorm(10, 0, 1),
  Y = rnorm(10, 0, 2),
  Z = rnorm(10, 0, 4)
)

## Warning: `data_frame()` is deprecated, use `tibble()`.
## This warning is displayed once per session.

stocks

## # A tibble: 10 x 4
##    time            X      Y      Z
##    <date>      <dbl>  <dbl>  <dbl>
##  1 2009-01-01  0.586 -0.232  3.12 
##  2 2009-01-02  0.709  3.63   5.82 
##  3 2009-01-03 -0.109  0.741 -2.58 
##  4 2009-01-04 -0.453  1.04  -6.21 
##  5 2009-01-05  0.606 -1.50  -6.39 
##  6 2009-01-06 -1.82   1.63   7.22 
##  7 2009-01-07  0.630 -1.77  -1.93 
##  8 2009-01-08 -0.276 -0.663  2.48 
##  9 2009-01-09 -0.284  2.24   2.45 
## 10 2009-01-10 -0.919  0.597 -0.649

stocksm <- stocks %>% pivot_longer(-time,
                                   names_to = "stock", 
                                   values_to = "price"
                                   ) %>%
                      arrange(stock)
stocksm

## # A tibble: 30 x 3
##    time       stock  price
##    <date>     <chr>  <dbl>
##  1 2009-01-01 X      0.586
##  2 2009-01-02 X      0.709
##  3 2009-01-03 X     -0.109
##  4 2009-01-04 X     -0.453
##  5 2009-01-05 X      0.606
##  6 2009-01-06 X     -1.82 
##  7 2009-01-07 X      0.630
##  8 2009-01-08 X     -0.276
##  9 2009-01-09 X     -0.284
## 10 2009-01-10 X     -0.919
## # … with 20 more rows

stocksm %>% pivot_wider(
                       names_from = stock,
                       values_from = price
                       )

## # A tibble: 10 x 4
##    time            X      Y      Z
##    <date>      <dbl>  <dbl>  <dbl>
##  1 2009-01-01  0.586 -0.232  3.12 
##  2 2009-01-02  0.709  3.63   5.82 
##  3 2009-01-03 -0.109  0.741 -2.58 
##  4 2009-01-04 -0.453  1.04  -6.21 
##  5 2009-01-05  0.606 -1.50  -6.39 
##  6 2009-01-06 -1.82   1.63   7.22 
##  7 2009-01-07  0.630 -1.77  -1.93 
##  8 2009-01-08 -0.276 -0.663  2.48 
##  9 2009-01-09 -0.284  2.24   2.45 
## 10 2009-01-10 -0.919  0.597 -0.649

stocksm %>% pivot_wider(
                       names_from = time,
                       values_from = price
                       )

## # A tibble: 3 x 11
##   stock `2009-01-01` `2009-01-02` `2009-01-03` `2009-01-04` `2009-01-05`
##   <chr>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>
## 1 X            0.586        0.709       -0.109       -0.453        0.606
## 2 Y           -0.232        3.63         0.741        1.04        -1.50 
## 3 Z            3.12         5.82        -2.58        -6.21        -6.39 
## # … with 5 more variables: `2009-01-06` <dbl>, `2009-01-07` <dbl>,
## #   `2009-01-08` <dbl>, `2009-01-09` <dbl>, `2009-01-10` <dbl>

Download some stock market data. Nice and tidy? Yes or No?

Using quantmod

library(quantmod)

This report examines the price of three stocks.

• print(params$symbol1)
• params$symbol2
• params$symbol3

getSymbols(params$symbol1, src="yahoo") # from yahoo finance 

## 'getSymbols' currently uses auto.assign=TRUE by default, but will
## use auto.assign=FALSE in 0.5-0. You will still be able to use
## 'loadSymbols' to automatically load data. getOption("getSymbols.env")
## and getOption("getSymbols.auto.assign") will still be checked for
## alternate defaults.
## 
## This message is shown once per session and may be disabled by setting 
## options("getSymbols.warning4.0"=FALSE). See ?getSymbols for details.

## [1] "GM"

stock01 <- getSymbols(params$symbol1, auto.assign = FALSE, src="yahoo") 
barChart(stock01)

stock01 %>% head()

##            GM.Open GM.High GM.Low GM.Close GM.Volume GM.Adjusted
## 2010-11-18   35.00   35.99  33.89    34.19 457044300    26.75862
## 2010-11-19   34.15   34.50  33.11    34.26 107842000    26.81340
## 2010-11-22   34.20   34.48  33.81    34.08  36650600    26.67253
## 2010-11-23   33.95   33.99  33.19    33.25  31170200    26.02294
## 2010-11-24   33.73   33.80  33.22    33.48  26138000    26.20294
## 2010-11-26   33.41   33.81  33.21    33.80  12301200    26.45339

We will use the BatchGetSymbols R package to download stock market data.

library(BatchGetSymbols)

# set dates
first.date <- Sys.Date() - 30
last.date <- Sys.Date()

# set tickers
tickers <- params

l.out <- BatchGetSymbols(tickers = tickers, 
                         first.date = first.date,
                         last.date = last.date, 
                         cache.folder = file.path(tempdir(), 
                                                  'BGS_Cache') ) # cache in tempdir()

## 
## Running BatchGetSymbols for:
##    tickers =GM, F, TWTR
##    Downloading data for benchmark ticker
## ^GSPC | yahoo (1|1) | Not Cached | Saving cache
## GM | yahoo (1|3) | Not Cached | Saving cache - Got 100% of valid prices | Mais contente que cusco de cozinheira!
## F | yahoo (2|3) | Not Cached | Saving cache - Got 100% of valid prices | Nice!
## TWTR | yahoo (3|3) | Not Cached | Saving cache - Got 100% of valid prices | Youre doing good!

print(l.out$df.control)

## # A tibble: 3 x 6
##   ticker src   download.status total.obs perc.benchmark.d… threshold.decis…
##   <chr>  <chr> <chr>               <int>             <dbl> <chr>           
## 1 GM     yahoo OK                     21                 1 KEEP            
## 2 F      yahoo OK                     21                 1 KEEP            
## 3 TWTR   yahoo OK                     21                 1 KEEP

Is the data in a tidy format?

print(l.out$df.tickers)

##    price.open price.high price.low price.close   volume price.adjusted
## 1       37.51      37.60     37.19       37.36  3899900       36.98875
## 2       37.20      37.29     36.92       36.96  4110400       36.59272
## 3       37.36      37.36     37.06       37.12  3791800       36.75113
## 4       37.25      37.51     37.05       37.26  4044300       36.88974
## 5       36.61      37.18     35.90       36.06  7834100       35.70166
## 6       36.49      36.61     36.01       36.25  4838500       35.88977
## 7       36.47      36.58     35.85       35.89  6221600       35.53335
## 8       35.70      36.57     35.58       36.44  7284600       36.07789
## 9       36.80      37.02     36.73       36.91  5123700       36.54322
## 10      37.25      37.34     36.91       37.09  4599000       36.72143
## 11      36.89      37.01     36.54       36.91  6093900       36.54322
## 12      37.31      38.32     37.24       38.24  8105700       37.86000
## 13      38.62      39.54     38.62       38.73 10747000       38.73000
## 14      38.92      38.93     38.52       38.78  5012700       38.78000
## 15      39.06      39.78     38.95       39.58  7543800       39.58000
## 16      39.20      39.68     38.84       39.58  7233600       39.58000
## 17      39.40      39.54     38.86       39.46  6991800       39.46000
## 18      39.41      39.49     38.90       39.07  6982100       39.07000
## 19      39.47      39.58     38.78       38.86 10133600       38.86000
## 20      37.97      38.16     36.97       37.21 15650300       37.21000
## 21      37.20      38.42     36.85       38.29 11349200       38.29000
## 22       9.05       9.10      9.02        9.03 21844000        9.03000
## 23       9.00       9.07      8.93        8.96 25108600        8.96000
## 24       9.02       9.05      8.97        9.04 20434800        9.04000
## 25       9.05       9.14      9.02        9.04 20974700        9.04000
## 26       8.90       8.99      8.73        8.77 44982300        8.77000
## 27       8.88       8.91      8.79        8.82 31888200        8.82000
## 28       8.89       8.91      8.75        8.76 23024400        8.76000
## 29       8.72       9.04      8.70        9.00 35147300        9.00000
## 30       9.10       9.14      9.03        9.12 22011000        9.12000
## 31       9.16       9.23      9.10        9.17 32057900        9.17000
## 32       9.18       9.20      9.04        9.10 26331000        9.10000
## 33       9.17       9.23      9.07        9.20 27406400        9.20000
## 34       9.25       9.40      9.25        9.34 36626800        9.34000
## 35       9.37       9.41      9.20        9.34 29210100        9.34000
## 36       9.39       9.65      9.39        9.54 48059400        9.54000
## 37       9.08       9.42      9.04        9.42 70567300        9.42000
## 38       9.29       9.43      9.29        9.42 34190300        9.42000
## 39       9.36       9.48      9.32        9.41 35687700        9.41000
## 40       9.47       9.59      9.44        9.45 27161300        9.45000
## 41       9.36       9.45      9.24        9.30 50052600        9.30000
## 42       9.27       9.31      9.18        9.28 27391200        9.28000
## 43      41.10      41.88     41.05       41.70 10276500       41.70000
## 44      41.64      42.85     41.38       42.29 11356700       42.29000
## 45      42.83      43.28     42.36       42.80 12018600       42.80000
## 46      42.85      43.08     41.59       42.18  8277000       42.18000
## 47      41.82      42.69     40.81       41.00 14036200       41.00000
## 48      41.40      41.48     41.00       41.44  6403600       41.44000
## 49      42.01      42.69     41.53       42.14 16087500       42.14000
## 50      41.75      42.20     41.30       41.68 10418400       41.68000
## 51      42.25      42.65     41.82       42.49 10594400       42.49000
## 52      42.70      42.70     41.90       42.65  8863600       42.65000
## 53      42.38      43.77     41.64       41.96 16159600       41.96000
## 54      42.49      43.40     42.11       43.36 11567900       43.36000
## 55      43.78      45.33     43.62       45.30 19319800       45.30000
## 56      45.22      45.68     44.56       45.42 13108500       45.42000
## 57      45.60      45.86     43.84       44.26 14547400       44.26000
## 58      43.90      43.95     42.51       43.25 14040100       43.25000
## 59      43.55      43.78     43.05       43.25  7861200       43.25000
## 60      43.76      44.11     43.11       43.20  8251700       43.20000
## 61      43.15      43.20     42.33       42.63  8059600       42.63000
## 62      42.40      43.07     42.19       42.76  6187900       42.76000
## 63      42.90      43.59     42.88       43.24  8654400       43.24000
##      ref.date ticker ret.adjusted.prices ret.closing.prices
## 1  2019-08-19     GM                  NA                 NA
## 2  2019-08-20     GM        -0.010706689       -0.010706691
## 3  2019-08-21     GM         0.004328976        0.004329004
## 4  2019-08-22     GM         0.003771503        0.003771525
## 5  2019-08-23     GM        -0.032206032       -0.032206040
## 6  2019-08-26     GM         0.005268942        0.005268968
## 7  2019-08-27     GM        -0.009931018       -0.009931062
## 8  2019-08-28     GM         0.015324560        0.015324603
## 9  2019-08-29     GM         0.012897984        0.012897942
## 10 2019-08-30     GM         0.004876719        0.004876727
## 11 2019-09-03     GM        -0.004853052       -0.004853060
## 12 2019-09-04     GM         0.036033609        0.036033649
## 13 2019-09-05     GM         0.022979371        0.012813755
## 14 2019-09-06     GM         0.001290963        0.001290963
## 15 2019-09-09     GM         0.020629268        0.020629268
## 16 2019-09-10     GM         0.000000000        0.000000000
## 17 2019-09-11     GM        -0.003031910       -0.003031910
## 18 2019-09-12     GM        -0.009883401       -0.009883401
## 19 2019-09-13     GM        -0.005374942       -0.005374942
## 20 2019-09-16     GM        -0.042460164       -0.042460164
## 21 2019-09-17     GM         0.029024510        0.029024510
## 22 2019-08-19      F                  NA                 NA
## 23 2019-08-20      F        -0.007751938       -0.007751938
## 24 2019-08-21      F         0.008928571        0.008928571
## 25 2019-08-22      F         0.000000000        0.000000000
## 26 2019-08-23      F        -0.029867257       -0.029867257
## 27 2019-08-26      F         0.005701254        0.005701254
## 28 2019-08-27      F        -0.006802721       -0.006802721
## 29 2019-08-28      F         0.027397260        0.027397260
## 30 2019-08-29      F         0.013333333        0.013333333
## 31 2019-08-30      F         0.005482456        0.005482456
## 32 2019-09-03      F        -0.007633588       -0.007633588
## 33 2019-09-04      F         0.010989011        0.010989011
## 34 2019-09-05      F         0.015217391        0.015217391
## 35 2019-09-06      F         0.000000000        0.000000000
## 36 2019-09-09      F         0.021413276        0.021413276
## 37 2019-09-10      F        -0.012578616       -0.012578616
## 38 2019-09-11      F         0.000000000        0.000000000
## 39 2019-09-12      F        -0.001061571       -0.001061571
## 40 2019-09-13      F         0.004250797        0.004250797
## 41 2019-09-16      F        -0.015873016       -0.015873016
## 42 2019-09-17      F        -0.002150538       -0.002150538
## 43 2019-08-19   TWTR                  NA                 NA
## 44 2019-08-20   TWTR         0.014148681        0.014148681
## 45 2019-08-21   TWTR         0.012059541        0.012059541
## 46 2019-08-22   TWTR        -0.014485958       -0.014485958
## 47 2019-08-23   TWTR        -0.027975344       -0.027975344
## 48 2019-08-26   TWTR         0.010731683        0.010731683
## 49 2019-08-27   TWTR         0.016891892        0.016891892
## 50 2019-08-28   TWTR        -0.010915971       -0.010915971
## 51 2019-08-29   TWTR         0.019433829        0.019433829
## 52 2019-08-30   TWTR         0.003765592        0.003765592
## 53 2019-09-03   TWTR        -0.016178264       -0.016178264
## 54 2019-09-04   TWTR         0.033365158        0.033365158
## 55 2019-09-05   TWTR         0.044741650        0.044741650
## 56 2019-09-06   TWTR         0.002648985        0.002648985
## 57 2019-09-09   TWTR        -0.025539411       -0.025539411
## 58 2019-09-10   TWTR        -0.022819658       -0.022819658
## 59 2019-09-11   TWTR         0.000000000        0.000000000
## 60 2019-09-12   TWTR        -0.001156046       -0.001156046
## 61 2019-09-13   TWTR        -0.013194444       -0.013194444
## 62 2019-09-16   TWTR         0.003049425        0.003049425
## 63 2019-09-17   TWTR         0.011225538        0.011225538

l.out$df.tickers %>% ggplot(aes(x = ref.date, y = price.close)) +
   geom_line() +
   facet_wrap(~ticker, scales = 'free_y')