Extract previous day value from a time-series object in R

Question

I have time-series (xts formatted) power consumption data at 10 minutes rate as

                      power 
2015-08-01 00:00:00 101.22              
2015-08-01 00:10:00 122.941                
2015-08-01 00:20:00  67.596              
2015-08-01 00:30:00 184.180

Now I want to add 3 more columns to it as:

Column # 2: "Prevday1" - where "prevday1" will contain the power consumption readings of previous day at the same time. That is, if the current index is 5 August 2015, 1100 hours then "prevday1" should contain consumption of previous day at same time instant (4 August 2015, 1100 hours)
Column # 3: "Prevday2" - where "prevday2" will contain the power consumption readings of day before previous day at the same time instant
column # 4: "previnstant1" - where "previnstant1" will contain reading of previous time instant. In my case it will be power consumption before 10 minutes

Somehow new xts object will be like

                  power       prevday1     prevday2   previnstant1
2015-08-01 00:00:00 101.22       NA          NA          NA
2015-08-01 00:10:00 122.941      :            :           :
2015-08-01 00:20:00  67.596              
2015-08-01 00:30:00 184.180   
       :

Now the question is how should I extract the values for columns 2, 3 and 4 from the historical xts object. I started with .indexday type of functions but could not get the values. Is there any specific function in R to extract these type of values using xts indexes?

lag(xts_object, 24*6) gives you the previous day, lag (xts_object, 48*6) gives the prevday2 etc. This assumes you ignore daylight's savings time. — Buggy
No, lag() returns the entire object back with some observations having na as specified. I need values corresponding to specific previous timestamps. — Haroon Rashid
Sorry, I don't understand what you are trying to do here. Because lagging the object does give you the exact observation of the previous day in the same time frame if you choose the lag correctly, if you neglect DLS effects. — Buggy

Haroon Rashid Haroon Rashid · Accepted Answer · 2016-03-19T06:31:33

After struggling for a complete day, I came out with a way to fill the remaining three columns. The approach is:

Extract/Read index of current observation
Calculate the indices of the previous two days using an index of step 1
Read values corresponding to indices of step 2. This will fill columns 2 and 3 respectively
Find the periodicity of time-series data and read the previous value using this periodicity. This will fill column

Code is:

#x is a xts time series object containing columns as shown in question
 dates <- as.Date(index(x),tz="Asia/Kolkata") # timestamp in date format
 for(i in 0:200) # no. of observations
      {
      a <- x[i,1] # Current observation 
      prev_d1 <- as.Date(index(a), tz ="Asia/Kolkata")-1 # previous day
      prev_d2 <- as.Date(index(a), tz ="Asia/Kolkata")-2 # previous to previous day
      prev_value1 <- x[dates %in% prev_d1 & .indexhour(x) %in% .indexhour(a) & .indexmin(x) %in% .indexmin(a)]$power
      prev_value2 <- x[dates %in% prev_d2 & .indexhour(x) %in% .indexhour(a) & .indexmin(x) %in% .indexmin(a)]$power
      x[i,"prevday1"] <- if(length(prev_value1)!=0) prev_value1 else NA
      x[i,"prevday2"] <- if(length(prev_value2)!=0) prev_value2 else NA
      x[i,"previnstant1"] <- ifelse(length(x[index(a)-frequency]$power)!=0, x[index(a)-frequency]$power, NA)# frequency represents periodicity values in terms of seconds
       }

Extract previous day value from a time-series object in R

1 Answers