Converting df into ts object and decompose in 15 minute intervals in R

Question

I know there has been a lot on this topic already but I can't seem to get what I want working. I've read:

how to convert data frame into time series in R

Convert data frame with date column to timeseries

As well as several others but can't get it to work.

I have the following df

df <- data.frame(CloseTime = c("2017-09-13 19:15:00","2017-09-13 19:30:00","2017-09-13 19:45:00","2017-09-13 20:00:00","2017-09-13 20:15:00"),
             OpenPice = c(271.23,269.50,269.82,269.10,269.50),
             HightPrice = c(271.23,269.50,269.82,269.10,269.50),
             LowPrice = c(271.23,269.50,269.82,269.10,269.50),
             ClosePrice = c(271.23,269.50,269.82,269.10,269.50))

I'd like to convert it into a tsobject, with 15-minute intervals and decompose the time series.

I also read that the zoo package allows you to decompose specific multiple intervals i.e. 15 mins, 1h, 1 day?

Can someone please help. How can I convert this into a ts object and decompose my ts object?

GoGonzo · Accepted Answer · 2017-09-19T16:26:25.390

Just for the reproducibility purpose, another toy-example with longer period of time.

df <- 
  data.frame(
    CloseTime = seq(as.POSIXct("2017-09-13 19:15:00"),as.POSIXct("2018-10-20 21:45:00"),by="15 mins"),
    ClosePrice1 = cumsum(rnorm(38603)),
    ClosePrice2 = cumsum(rnorm(38603)),
    ClosePrice3 = cumsum(rnorm(38603))
  )

I found it much better to aggregate time series into different intervals using dplyr and lubridate::floor_date. Instead of mean, one can summarise using min, max, first, last. I would recommend stay around the tidyr to keep code readable. Below example converting into 30minutes interval.

library(lubridate); library(dplyr); library(magrittr)
df30m <-
  df %>%
  group_by( CloseTime = floor_date( CloseTime, "30 mins")) %>%
  summarize_all(mean)

Data.frame can be converted to timeseries object such as zoo and than to ts for decomposing purposes.

library(zoo)
df30m_zoo <- zoo( df30m[-1], order.by = df30m$CloseTime )
df30m_ts  <- ts(df30m_zoo, start=1, frequency = 2 * pi)

df30m_decomposed <- decompose(df30m_ts)

thanks, worked like a charm. A quick question, my `seasonal` component contains `NA` values at the beginning and the end of my series. Why is that? — Davis, Sep 19 '17 at 20:40
Probably because of filtering function (MA). https://stackoverflow.com/questions/33194922/na-results-in-decomposition-of-additive-time-series-in-r — GoGonzo, Sep 20 '17 at 04:35

G. Grothendieck · Answer 2 · 2017-09-20T13:01:42.633

The points are already 15 minutes apart so assuming that you want a period of 1 day this will convert it. There are 24 * 60 * 60 seconds in a day (which s the period) but you can change the denominator to the number of seconds in a period get a different period. You will need at least two periods of data to decompose it.

library(zoo)

z <- read.zoo(df)
time(z) <- (as.numeric(time(z)) - as.numeric(start(z))) / (24 * 60 * 60)
as.ts(z)

giving:

Time Series:
Start = c(0, 1) 
End = c(0, 5) 
Frequency = 96 
           OpenPice HightPrice LowPrice ClosePrice
0.00000000   271.23     271.23   271.23     271.23
0.01041667   269.50     269.50   269.50     269.50
0.02083333   269.82     269.82   269.82     269.82
0.03125000   269.10     269.10   269.10     269.10
0.04166667   269.50     269.50   269.50     269.50

Alhtough not asked for in the question, in another answer the data was converted to 30 minutes. That could readily be done like this:

library(xts) # also loads zoo

z <- read.zoo(df)
to.minutes30(z)

Thanks, if I use `decompose` I'm getting error `Error in decompose(z) : time series has no or less than 2 periods`. What am I doing wrong? — Davis, Sep 19 '17 at 14:33
I have changed it to use a period of one day but if you have less than 2 days of data change it to a smaller period as described. You must have at least 2 periods in your data to decompose it. — G. Grothendieck, Sep 19 '17 at 14:52

Converting df into ts object and decompose in 15 minute intervals in R

2 Answers2