CODEWITHSUNDEEP
rxts
Eka
Eka

Reputation: 15000

How to do single column operations in xts

I want to do single column operations in xts datasets. I have a very large dataset so i removed some data and dates from it. Below dataset is calculated using excel and only shows some data from the beginning and the end.

  Date      a           b        c
    -       0.023       
    -       0.021   0.0214830   
    -       0.014   0.0142940   0.021483
    -       0.008   0.0081120   0.014294
    -       0.003   0.0030240   0.008112
    -       0.002   0.0020060   0.003024
    -       0       0           0.002006
    -       0       0           0
    -       0       0           0
    -       0       0           0
    -       0.001   0.0010000   0
    -       0.003   0.0030030   0.001
    -       0.005   0.0050150   0.003003
    -       0.001   0.0010050   0.005015
    -       0       0           0.001005
    -       -0.001  -0.0010000  0
    -       0.002   0.0019980   -0.001
    -       0.003   0.0030060   0.001998
    -       .       .            .
    -       .       .            .
    -       .       .            .
    -       .       .            .
    -       .       .            .
    -       .       .            .
    -       .       .
    -       0.019   0.01938     0.02042
    -       0.015   0.0152850   0.01938
    -       0.013   0.0131950   0.015285

In the above dataset a is my initial data which is used to calculate b and c. b is calculted using this forumula b2=(a2*a1)+a2 (eg:(0.021*0.023)+0.021=0.021483) and c is formed by shifting b down one row c3=b2.

My question is how can I do these operations in R xts dataset

EDITED

How can i shift a column up in xts. below transformation is d1=a3

 Date         a       d
    -       0.023   0.014
    -       0.021   0.008
    -       0.014   0.003
    -       0.008   0.002
    -       0.003   0
    -       0.002   0
    -       0       0
    -       0       0
    -       0       0.001
    -       0       0.003
    -       0.001   0.005
    -       0.003   0.001
    -       0.005   0
    -       0.001   -0.001
    -       0       0.002
    -       -0.001  0.003
    -       0.002   0.024
    -       0.003   0.015
    -       .       .
    -       .       .
    -       .       .
    -       .       .
    -       .       .
    -       .       0.019
    -       .       0.015
    -       0.019   0.013
    -       0.015   0
    -       0.013   0

I have tried this method

shift <- function(x, n){
  c(x[-(seq(n))], rep(NA, n))
}
d<-shift(df$a,2)

But its giving this error

Error in try.xts(NA) : Error in as.xts.logical(x, ..., .RECLASS = TRUE) : order.by must be either 'names()' or otherwise specified

Upvotes: 1

Views: 2302

Answers (1)

mrp
mrp

Reputation: 711

Pascal is correct, you would do it as if you were operating on a data.frame. Here is an example:

library(xts)

Ex <- xts(1:10, Sys.Date()+1:10)
names(Ex) <- "a"
Ex$b <- (Ex$a*lag(Ex$a))+Ex$a
Ex$c <- lag(Ex$b)

The above produces the following: 

##             a   b  c
## 2015-12-25  1  NA NA
## 2015-12-26  2   4 NA
## 2015-12-27  3   9  4
## 2015-12-28  4  16  9
## 2015-12-29  5  25 16
## 2015-12-30  6  36 25
## 2015-12-31  7  49 36
## 2016-01-01  8  64 49
## 2016-01-02  9  81 64
## 2016-01-03 10 100 81

Edit: To answer Edited question

To create a column where d1=a3 simply change the object in the function to a matrix and it works fine. Maybe someone could fill in the blank as to why, but at least it works now.

shift <- function(x, n){
  xMat <- as.matrix(x)
  c(xMat[-(seq(n))], rep(NA, n))
}

Ex$d <- shift(Ex$a, 2)

This produces:

##             a   b  c  d
## 2015-12-26  1  NA NA  3
## 2015-12-27  2   4 NA  4
## 2015-12-28  3   9  4  5
## 2015-12-29  4  16  9  6
## 2015-12-30  5  25 16  7
## 2015-12-31  6  36 25  8
## 2016-01-01  7  49 36  9
## 2016-01-02  8  64 49 10
## 2016-01-03  9  81 64 NA
## 2016-01-04 10 100 81 NA

Upvotes: 6

Related Questions