R Xts: Generating 1 Minute Time Series from Second Events

R xts: generating 1 minute time series from second events

There are to.period() functions, eg to.minute() in xts which do that.

Dirk

R xts package - to.period by minute over a day

You can calculate cumulative max/min using cummax() and cummin(), respectively. You just need to apply those functions by day. You can do that by split()ing your data into daily groups, applying the function below to each group, then rbind()ing the data back together.

Here's a reproducible example using daily data in the xts package.

library(xts)
set.seed(21)
tm <- timeBasedSeq('2017-07-14/2017-07-15/M')
x <- xts(20*cumprod(1+rnorm(length(tm), 0, 0.0025)), tm)
colnames(x) <- "price"

aggfun <- function(x) {
  stopifnot(ncol(x) > 0)
  # remove potential column name
  colnames(x) <- NULL
  r <- xts(cbind(Open = rep(x[1], nrow(x)),
                 High = cummax(x),
                 Low = cummin(x),
                 Close = x), index(x))
  r
}
y <- do.call(rbind, lapply(split(x, "day"), aggfun))

The output from one day to the next looks like:

y[1439:1442]
#                         Open     High      Low    Close
# 2017-07-14 23:58:00 20.03965 25.02193 19.60128 23.73810
# 2017-07-14 23:59:00 20.03965 25.02193 19.60128 23.71598
# 2017-07-15 00:00:00 23.73816 23.73816 23.73816 23.73816
# 2017-07-15 00:01:00 23.73816 23.73816 23.71164 23.71164

R XTS package to.minutes – Unable to create 15m and 30m time series from 5m correctly

This is pretty clearly described on the ?to.minutes help page. The default is for the groups to start at the end of your data and work backwards so it doesn't necessarily pay attention to what the first value is. However, you can explicity set the indexAt= parameter to "startof". For example

x <- zoo(runif(25), order.by=seq(as.POSIXct("2010-05-03 09:00:00"), 
    as.POSIXct("2010-05-03 11:00:00"), by="5 min"))

to.minutes15(x)

#                         x.Open    x.High      x.Low    x.Close
# 2010-05-03 09:10:00 0.35570172 0.3557017 0.04524480 0.04524480
# 2010-05-03 09:25:00 0.78939084 0.7893908 0.44032175 0.44032175
# 2010-05-03 09:40:00 0.05272398 0.5381755 0.05272398 0.53817548
# 2010-05-03 09:55:00 0.02198503 0.1113298 0.02198503 0.11132980
# 2010-05-03 10:10:00 0.78785210 0.8804505 0.04152860 0.04152860
# 2010-05-03 10:25:00 0.79317091 0.9497044 0.54751546 0.94970444
# 2010-05-03 10:40:00 0.03886176 0.7425681 0.03886176 0.06614893
# 2010-05-03 10:55:00 0.58684500 0.5868450 0.02794687 0.14291696
# 2010-05-03 11:00:00 0.11713868 0.1171387 0.11713868 0.11713868

versus

to.minutes15(x, indexAt="startof")

#                         x.Open    x.High      x.Low    x.Close
# 2010-05-03 09:00:00 0.35570172 0.3557017 0.04524480 0.04524480
# 2010-05-03 09:15:00 0.78939084 0.7893908 0.44032175 0.44032175
# 2010-05-03 09:30:00 0.05272398 0.5381755 0.05272398 0.53817548
# 2010-05-03 09:45:00 0.02198503 0.1113298 0.02198503 0.11132980
# 2010-05-03 10:00:00 0.78785210 0.8804505 0.04152860 0.04152860
# 2010-05-03 10:15:00 0.79317091 0.9497044 0.54751546 0.94970444
# 2010-05-03 10:30:00 0.03886176 0.7425681 0.03886176 0.06614893
# 2010-05-03 10:45:00 0.58684500 0.5868450 0.02794687 0.14291696
# 2010-05-03 11:00:00 0.11713868 0.1171387 0.11713868 0.11713868

Converting a list of events into a series of the number of events every two minutes

How about

as.data.frame(table(cut(x, breaks=c(y, Inf))))

                 Var1 Freq
1 2013-06-20 01:00:00    3
2 2013-06-20 01:02:00    0
3 2013-06-20 01:04:00    2
4 2013-06-20 01:06:00    0

Combining time series data with different resolution in R

I found a solution to my problem by converting the 15 minute data into hourly data using the very useful .index* function from the xts package like shown under.

prod.new <- data.15min$prod.15min[.indexmin(data.15min$prod.15min) %in% c(45:59)]

This creates a new time series with only the values occuring in the 45-59 minute interval each hour.

For those curious my data looked like this:

Original hourly series:

> data.h$prod.h[1:4]
2013-01-01 00:00:00 19.744
2013-01-01 01:00:00 27.866
2013-01-01 02:00:00 26.227
2013-01-01 03:00:00 16.013

Original 15 minute series:

> data.15min$prod.15min[1:4]
2013-09-30 00:00:00    16.4251
2013-09-30 00:15:00    18.4495
2013-09-30 00:30:00     7.2125
2013-09-30 00:45:00    12.1913
2013-09-30 01:00:00    12.4606
2013-09-30 01:15:00    12.7299
2013-09-30 01:30:00    12.9992
2013-09-30 01:45:00    26.7522

New series with only the last 15 minutes in each hour:

> prod.new[1:4]
2013-09-30 00:45:00    12.1913
2013-09-30 01:45:00    26.7522
2013-09-30 02:45:00     5.0332
2013-09-30 03:45:00     2.6974

Aggregate15 minute data to hourly

When working with time series, I suggest you work with xts package for this, and for example hourly.apply:

 library(xts)
 dat.xts <- xts(Total_Solar_Gesamt$TotalSolar_MW,
                as.POSIXct(otal_Solar_Gesamt$Timedate))
 hourly.apply(dat.xts,sum)

More general you can use period.apply which is (lapply equivalent) , for example to aggregate your data each 2 hours you can do the following:

 ends <- endpoints(zoo.data,'hours',2) 
 period.apply(dat.xts,ends ,sum)

---

Related Topics