Session 1 - Data Ingesting and Stageing

Objectives and Learning

Packages in this Session

Installing R Packages

Easy one Step!

install.packages("xts")

Different Data Types in R

x<-5
x=5
str(x)
##  num 5
x<-"g"
str(x)
##  chr "g"
x<-c("a","b","c")
str(x)
##  chr [1:3] "a" "b" "c"
x.df<-data.frame(a=c("v1","v2"),b=c(1,2),stringsAsFactors = F)
str(x.df)
## 'data.frame':    2 obs. of  2 variables:
##  $ a: chr  "v1" "v2"
##  $ b: num  1 2
x<-list(v3=x.df,v4=c(1,2,3))
str(x)
## List of 2
##  $ v3:'data.frame':  2 obs. of  2 variables:
##   ..$ a: chr [1:2] "v1" "v2"
##   ..$ b: num [1:2] 1 2
##  $ v4: num [1:3] 1 2 3

Using Web APIs

#install.packages("jsonlite")
require(jsonlite)
samplefile<-fromJSON("https://www.quandl.com/api/v3/datasets/GDAX/CAD.json")
str(samplefile)
## List of 1
##  $ dataset:List of 21
##   ..$ id                   : int 27439542
##   ..$ dataset_code         : chr "CAD"
##   ..$ database_code        : chr "GDAX"
##   ..$ name                 : chr "BTC/CAD Exchange Rate"
##   ..$ description          : chr "Bitcoin price data from Coinbase for CAD. Retrieved at 6pm EST."
##   ..$ refreshed_at         : chr "2016-07-29T23:01:04.836Z"
##   ..$ newest_available_date: chr "2016-07-29"
##   ..$ oldest_available_date: chr "2015-08-31"
##   ..$ column_names         : chr [1:5] "Date" "Open" "High" "Low" ...
##   ..$ frequency            : chr "daily"
##   ..$ type                 : chr "Time Series"
##   ..$ premium              : logi FALSE
##   ..$ limit                : NULL
##   ..$ transform            : NULL
##   ..$ column_index         : NULL
##   ..$ start_date           : chr "2015-08-31"
##   ..$ end_date             : chr "2016-07-29"
##   ..$ data                 : chr [1:333, 1:5] "2016-07-29" "2016-07-28" "2016-07-27" "2016-07-26" ...
##   ..$ collapse             : NULL
##   ..$ order                : chr "desc"
##   ..$ database_id          : int 15077
value<-samplefile$data$data
#install.packages("Quandl")
require(Quandl)
cad<-Quandl("LOCALBTC/CAD")
usd<-Quandl("LOCALBTC/USD")
str(cad)
## 'data.frame':    746 obs. of  5 variables:
##  $ Date        : Date, format: "2016-08-04" "2016-08-03" ...
##  $ 24h Average : num  834 834 822 845 897 ...
##  $ 12h Average : num  815 860 817 856 892 ...
##  $ Last        : num  845 780 830 817 749 ...
##  $ Volume (BTC): num  27.09 46.01 59.79 20.5 7.81 ...
##  - attr(*, "freq")= chr "daily"
head(cad)
##         Date 24h Average 12h Average    Last Volume (BTC)
## 1 2016-08-04      834.42      814.84  844.85      27.0946
## 2 2016-08-03      834.29      860.37  779.63      46.0109
## 3 2016-08-02      821.83      817.41  829.65      59.7919
## 4 2016-08-01      845.30      855.91  817.19      20.4979
## 5 2016-07-31      896.92      891.99  749.06       7.8147
## 6 2016-07-30      920.42      933.48 1282.05      19.5303

Very Brief Introduction to dplyr Package

# install.packages("dplyr")
#install.packages("lubridate")
require(dplyr)
require(lubridate)
cad.clean<-cad %>% dplyr::select(date=Date,price.cad=Last,vol.cad=matches("Volume")) 

usd.clean<-usd %>% dplyr::select(date=Date,price.usd=Last,vol.usd=matches("Volume"))
prices<-cad.clean %>% inner_join(usd.clean, by="date")
prices<-prices %>% mutate(price.usd.ret=price.usd-lag(price.usd)) %>%
           mutate(price.cad.ret=price.cad-lag(price.cad)) %>% 
           mutate(year=year(date),month=month(date))
head(prices)
##         date price.cad vol.cad price.usd   vol.usd price.usd.ret
## 1 2016-08-04    844.85 27.0946    602.05 1482.7953            NA
## 2 2016-08-03    779.63 46.0109    861.33 1554.8099        259.28
## 3 2016-08-02    829.65 59.7919    664.45 1510.9650       -196.88
## 4 2016-08-01    817.19 20.4979   1461.99 1362.5854        797.54
## 5 2016-07-31    749.06  7.8147    693.72  515.9306       -768.27
## 6 2016-07-30   1282.05 19.5303    783.70  848.4142         89.98
##   price.cad.ret year month
## 1            NA 2016     8
## 2        -65.22 2016     8
## 3         50.02 2016     8
## 4        -12.46 2016     8
## 5        -68.13 2016     7
## 6        532.99 2016     7

Databases and Persistance

# install.packages("mongolite")
require(mongolite)
## Loading required package: mongolite
m<-mongo(collection="price",db="garp",url="mongodb://localhost")
m$drop()
## [1] TRUE
m<-mongo(collection="price",db="garp",url="mongodb://localhost")
m$insert(prices)
## 
Complete! Processed total of 742 rows.
## [1] TRUE
prices<-m$find()
## 
 Found 742 records...
 Imported 742 records. Simplifying into dataframe...

More resources: Link

Saving Your Work

# install.packages("readr")
require(readr)
prices %>% readr::write_csv("/home/data/prices.csv") 
head(readr::read_csv("/home/data/prices.csv"))
## Source: local data frame [6 x 9]
## 
##         date price.cad vol.cad price.usd   vol.usd  year month
##       (date)     (dbl)   (dbl)     (dbl)     (dbl) (int) (int)
## 1 2016-08-04    844.85 27.0946    602.05 1482.7953  2016     8
## 2 2016-08-03    779.63 46.0109    861.33 1554.8099  2016     8
## 3 2016-08-02    829.65 59.7919    664.45 1510.9650  2016     8
## 4 2016-08-01    817.19 20.4979   1461.99 1362.5854  2016     8
## 5 2016-07-31    749.06  7.8147    693.72  515.9306  2016     7
## 6 2016-07-30   1282.05 19.5303    783.70  848.4142  2016     7
## Variables not shown: price.usd.ret (dbl), price.cad.ret (dbl)
save(prices,file="/home/data/session1.RData")
rm(list=ls(all=TRUE)) 
load("/home/data/session1.RData")
head(prices)
##         date price.cad vol.cad price.usd   vol.usd year month
## 1 2016-08-04    844.85 27.0946    602.05 1482.7953 2016     8
## 2 2016-08-03    779.63 46.0109    861.33 1554.8099 2016     8
## 3 2016-08-02    829.65 59.7919    664.45 1510.9650 2016     8
## 4 2016-08-01    817.19 20.4979   1461.99 1362.5854 2016     8
## 5 2016-07-31    749.06  7.8147    693.72  515.9306 2016     7
## 6 2016-07-30   1282.05 19.5303    783.70  848.4142 2016     7
##   price.usd.ret price.cad.ret
## 1            NA            NA
## 2        259.28        -65.22
## 3       -196.88         50.02
## 4        797.54        -12.46
## 5       -768.27        -68.13
## 6         89.98        532.99