Session 2 Data Munging and Cleaning

Objectives and Learning

Tools and Packages

Using dplyr to clean data

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
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

Pivot Table Functionality in R and tidyr

#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 ...

Apply Functions using Purrr Package

# 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

Using xts vs. dataframe Pros/Cons

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")

Other Useful Packages: