load("/home/data/session1.RData")
require(dplyr)
tmp<-prices %>% group_by(year) %>% summarise(count=n())
head(tmp)
## Source: local data frame [3 x 2]
##
## year count
## (int) (int)
## 1 2014 223
## 2 2015 313
## 3 2016 190
tmp<-prices %>% group_by(year) %>% summarise_each(funs(n(),mean),matches("price"))
head(tmp)
## Source: local data frame [3 x 9]
##
## year price.cad_n price.usd_n price.usd.ret_n price.cad.ret_n
## (int) (int) (int) (int) (int)
## 1 2014 223 223 223 223
## 2 2015 313 313 313 313
## 3 2016 190 190 190 190
## Variables not shown: price.cad_mean (dbl), price.usd_mean (dbl),
## price.usd.ret_mean (dbl), price.cad.ret_mean (dbl)
tmp<-prices %>% group_by(year) %>% summarise(max=max(price.usd.ret))
head(tmp)
## Source: local data frame [3 x 2]
##
## year max
## (int) (dbl)
## 1 2014 368.38
## 2 2015 402.38
## 3 2016 NA
tmp<-prices %>% na.omit() %>% group_by(year) %>% summarise(max=max(price.usd.ret))
head(tmp)
## Source: local data frame [3 x 2]
##
## year max
## (int) (dbl)
## 1 2014 368.38
## 2 2015 402.38
## 3 2016 902.99
tmp<-prices %>% na.omit() %>% group_by(year) %>% summarise(sd=sd(price.usd.ret))
head(tmp)
## Source: local data frame [3 x 2]
##
## year sd
## (int) (dbl)
## 1 2014 103.28473
## 2 2015 86.28132
## 3 2016 193.62067
filter data
Filter with numeric data
tmp<-prices %>% filter(abs(price.usd.ret) > 100)
head(tmp)
## date price.cad vol.cad price.usd vol.usd year month
## 1 2016-07-18 1286.17 19.6529 1359.43 1437.1228 2016 7
## 2 2016-07-17 841.52 10.0444 858.26 516.7683 2016 7
## 3 2016-07-15 881.21 30.4980 745.43 1428.4377 2016 7
## 4 2016-07-13 999.17 27.1975 1100.35 1828.1450 2016 7
## 5 2016-07-12 917.43 44.0611 789.89 1437.8521 2016 7
## 6 2016-07-10 1233.62 16.7221 664.98 524.7990 2016 7
## price.usd.ret price.cad.ret
## 1 393.25 195.26
## 2 -501.17 -444.65
## 3 -154.66 -36.22
## 4 383.89 -261.33
## 5 -310.46 -81.74
## 6 -146.27 320.38
tmp<-prices %>% filter(date > as.Date("2015-01-01",format="%Y-%m-%d")) %>% dplyr::arrange(-desc(date))
head(tmp)
## date price.cad vol.cad price.usd vol.usd year month price.usd.ret
## 1 2015-01-02 379.01 51.37 314.65 1452.08 2015 1 1.59
## 2 2015-01-03 362.19 34.41 313.06 1174.32 2015 1 -63.73
## 3 2015-01-04 260.51 21.45 376.79 790.86 2015 1 84.62
## 4 2015-01-05 367.78 58.75 292.17 2348.36 2015 1 -40.19
## 5 2015-01-06 361.27 42.64 332.36 2247.81 2015 1 -17.21
## 6 2015-01-08 364.22 57.37 349.57 1991.61 2015 1 33.51
## price.cad.ret
## 1 16.82
## 2 101.68
## 3 -107.27
## 4 6.51
## 5 -2.95
## 6 9.30
tail(tmp)
## date price.cad vol.cad price.usd vol.usd year month
## 497 2016-07-14 1260.50 28.3950 716.46 1420.9063 2016 7
## 498 2016-07-15 881.21 30.4980 745.43 1428.4377 2016 7
## 499 2016-07-16 917.43 19.0927 900.09 899.7820 2016 7
## 500 2016-07-17 841.52 10.0444 858.26 516.7683 2016 7
## 501 2016-07-18 1286.17 19.6529 1359.43 1437.1228 2016 7
## 502 2016-07-19 1090.91 40.6011 966.18 1507.2402 2016 7
## price.usd.ret price.cad.ret
## 497 -28.97 379.29
## 498 -154.66 -36.22
## 499 41.83 75.91
## 500 -501.17 -444.65
## 501 393.25 195.26
## 502 NA NA
#install.packages(tidyr)
require(tidyr)
tmp<-prices %>% dplyr::select(date,year,price.usd.ret)%>%
tidyr::spread(year,price.usd.ret)
head(tmp)
## date 2014 2015 2016
## 1 2014-04-15 -11.43 NA NA
## 2 2014-04-16 40.50 NA NA
## 3 2014-04-18 -45.07 NA NA
## 4 2014-04-19 40.22 NA NA
## 5 2014-04-22 -54.92 NA NA
## 6 2014-04-24 68.63 NA NA
tail(tmp)
## date 2014 2015 2016
## 721 2016-07-14 NA NA -28.97
## 722 2016-07-15 NA NA -154.66
## 723 2016-07-16 NA NA 41.83
## 724 2016-07-17 NA NA -501.17
## 725 2016-07-18 NA NA 393.25
## 726 2016-07-19 NA NA NA
tmp<-tmp %>% gather(year,price,-date)
head(tmp)
## date year price
## 1 2014-04-15 2014 -11.43
## 2 2014-04-16 2014 40.50
## 3 2014-04-18 2014 -45.07
## 4 2014-04-19 2014 40.22
## 5 2014-04-22 2014 -54.92
## 6 2014-04-24 2014 68.63
str(tmp)
## 'data.frame': 2178 obs. of 3 variables:
## $ date : chr "2014-04-15" "2014-04-16" "2014-04-18" "2014-04-19" ...
## $ year : Factor w/ 3 levels "2014","2015",..: 1 1 1 1 1 1 1 1 1 1 ...
## $ price: num -11.4 40.5 -45.1 40.2 -54.9 ...
# install.packages("purrr")
require(purrr)
tmp<-tmp %>% map_if(is.factor,as.character)
str(tmp)
## 'data.frame': 2178 obs. of 3 variables:
## $ date : chr "2014-04-15" "2014-04-16" "2014-04-18" "2014-04-19" ...
## $ year : chr "2014" "2014" "2014" "2014" ...
## $ price: num -11.4 40.5 -45.1 40.2 -54.9 ...
prices %>%
split(.$year) %>%
map(~ lm(price.cad.ret ~ price.usd.ret, data = .)) %>%
map(summary) %>%
map_dbl("r.squared")
## 2014 2015 2016
## 0.007623260 0.002188747 0.003720268
prices %>%
split(.$year) %>%
map(~ lm(price.cad ~ price.usd, data = .)) %>%
map(summary) %>%
map_dbl("r.squared")
## 2014 2015 2016
## 0.6207953 0.4533583 0.1954208
Further reading: purrr package github
require(xts)
head(prices)
## date price.cad vol.cad price.usd vol.usd year month
## 1 2016-07-19 1090.91 40.6011 966.18 1507.2402 2016 7
## 2 2016-07-18 1286.17 19.6529 1359.43 1437.1228 2016 7
## 3 2016-07-17 841.52 10.0444 858.26 516.7683 2016 7
## 4 2016-07-16 917.43 19.0927 900.09 899.7820 2016 7
## 5 2016-07-15 881.21 30.4980 745.43 1428.4377 2016 7
## 6 2016-07-14 1260.50 28.3950 716.46 1420.9063 2016 7
## price.usd.ret price.cad.ret
## 1 NA NA
## 2 393.25 195.26
## 3 -501.17 -444.65
## 4 41.83 75.91
## 5 -154.66 -36.22
## 6 -28.97 379.29
str(prices)
## 'data.frame': 726 obs. of 9 variables:
## $ date : chr "2016-07-19" "2016-07-18" "2016-07-17" "2016-07-16" ...
## $ price.cad : num 1091 1286 842 917 881 ...
## $ vol.cad : num 40.6 19.7 10 19.1 30.5 ...
## $ price.usd : num 966 1359 858 900 745 ...
## $ vol.usd : num 1507 1437 517 900 1428 ...
## $ year : int 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 ...
## $ month : int 7 7 7 7 7 7 7 7 7 7 ...
## $ price.usd.ret: num NA 393.2 -501.2 41.8 -154.7 ...
## $ price.cad.ret: num NA 195.3 -444.6 75.9 -36.2 ...
prices.xts<-xts(prices[,-1],order.by=as.Date(prices[,1],format="%Y-%m-%d"))
str(prices.xts)
## An 'xts' object on 2014-04-15/2016-07-19 containing:
## Data: num [1:726, 1:8] 595 618 428 597 585 ...
## - attr(*, "dimnames")=List of 2
## ..$ : NULL
## ..$ : chr [1:8] "price.cad" "vol.cad" "price.usd" "vol.usd" ...
## Indexed by objects of class: [Date] TZ: UTC
## xts Attributes:
## NULL
head(prices.xts)
## price.cad vol.cad price.usd vol.usd year month price.usd.ret
## 2014-04-15 595.00 18.63 543.00 822.14 2014 4 -11.43
## 2014-04-16 618.00 27.35 554.43 760.69 2014 4 40.50
## 2014-04-18 427.87 6.57 513.93 580.56 2014 4 -45.07
## 2014-04-19 597.00 11.69 559.00 370.58 2014 4 40.22
## 2014-04-22 585.17 20.10 518.78 759.92 2014 4 -54.92
## 2014-04-24 575.63 24.69 573.70 664.01 2014 4 68.63
## price.cad.ret
## 2014-04-15 -23.00
## 2014-04-16 190.13
## 2014-04-18 -169.13
## 2014-04-19 11.83
## 2014-04-22 9.54
## 2014-04-24 -43.37
ret<-diff(prices.xts[,c("price.cad","price.usd")])
ret<-na.omit(ret)
summary(lm(price.usd~price.cad,data=ret))
##
## Call:
## lm(formula = price.usd ~ price.cad, data = ret)
##
## Residuals:
## Min 1Q Median 3Q Max
## -904.09 -43.61 -1.33 46.45 620.34
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 0.61104 4.72705 0.129 0.897
## price.cad -0.03997 0.03072 -1.301 0.194
##
## Residual standard error: 127.3 on 723 degrees of freedom
## Multiple R-squared: 0.002335, Adjusted R-squared: 0.0009555
## F-statistic: 1.692 on 1 and 723 DF, p-value: 0.1937
save(prices.xts,file="/home/data/session2.RData")