Using Tidyverse to slice data based on column level statistics - #X-Days-of-Code

Santosh Srinivas 23.Jan.2020

Putting X days of code into practice, I'll demonstrate few tiny but useful bits of code.

In this post, we will see how to slice data based on column level statistics using Tidyverse.

Load Libraries

# cleanup
rm(list=ls())

require(data.table)
require(tidyverse)
require(PerformanceAnalytics)

Get the data

# first, we need data and the stock market always rocks! ;-)

tickers <- c("FB", "AMZN", "AAPL", "NFLX", "GOOG", "SPY")

getYahooData <- function(ticker ,n=-1){
  require('quantmod')
  print(paste('Getting yahoo data for ...',ticker))

  ## Obtain symbol data
  getSymbols(ticker, from="2010-01-01")

  stock <- get(ticker)
  ## Utilise the backwards-adjusted closing prices
  stock.uA <- adjustOHLC(stock, use.Adjusted=TRUE)
  stock <- Cl(stock.uA)

  assign(ticker, stock, envir=.GlobalEnv)
}

none <- sapply(tickers, getYahooData)

Transform and visualize

# https://stackoverflow.com/questions/44911304/merging-multiple-xts-objects-by-index
# credit: [How to pass extra argument to the function argument of do.call in R - Stack Overflow](https://stackoverflow.com/questions/10119893/how-to-pass-extra-argument-to-the-function-argument-of-do-call-in-r)

all.xts <- do.call('merge', sapply(tickers, get))

require("gdata")
clean_names <- sapply(names(all.xts), function(name) {gsub(".Close", "", name)})

names(all.xts) <- clean_names

# lets get the time from which we have all data
# lets also do a bit of visualization ... always helps!

# [r - Remove rows with all or some NAs (missing values) in data.frame - Stack Overflow](https://stackoverflow.com/questions/4862178/remove-rows-with-all-or-some-nas-missing-values-in-data-frame)
all.xts <- all.xts[complete.cases(all.xts), ]
plot.xts(all.xts, main = "Trend Chart of FAANG - onwards and upwards!", legend.loc = "bottomright")

chart.TimeSeries(all.xts, main = "Trend Chart of FAANG - onwards and upwards!", legend.loc = "bottomright")

all.xts.ret <- Return.calculate(all.xts)
chart.TimeSeries(all.xts.ret, main = "Return Chart of FAANG - onwards and upwards!", legend.loc = "bottomright")

p <- chart.CumReturns(all.xts.ret, legend.loc="topright", main="Cumulative Performance of FAANG")
p

Use Tidyverse to slice and dice data

# this fails because the first row is NA!
cumsum(all.xts.ret) %>% tail

# this works!
cumsum(all.xts.ret[-1,]) %>% tail
# now we have an idea of the TOTAL returns for this period!


all.xts.ret[-1,] %>% as.tibble %>% select_if(~ !is.numeric(.) || sum(.) < 1.25)
# LOL! only SPY here! Index investing! Good but still our universe contains heroes!

# select columns that are numeric and have total return of < 1.25
all.xts.ret[-1,] %>% as.tibble %>% select_if(~ is.numeric(.) && sum(.) < 1.25) %>% tail

# select columns that are numeric and have total return of > 1.25 and < 2.00
all.xts.ret[-1,] %>% as.tibble %>% select_if(~ is.numeric(.) && sum(.) > 1.25 && sum(.) < 2.00) %>% tail


# select columns that are numeric and have total return of > 2.00
all.xts.ret[-1,] %>% as.tibble %>% select_if(~ is.numeric(.) && sum(.) > 2.00) %>% tail