R：根据一天中的时间有效地对数据框进行子集化

发布于 2024-12-22 07:30:58 字数 1994 浏览 4 评论 0原文

我有一个大型 (150,000x7) 数据框，打算用于金融市场的回溯测试和实时分析。该数据以 5 分钟为间隔表示投资工具的状况（尽管确实存在漏洞）。它看起来像这样（但更长）：

        pTime     Time  Price       M1       M2        M3        M4
1  1212108300 20:45:00 1.5518 12.21849 -0.37125   4.50549 -31.00559
2  1212108900 20:55:00 1.5516 11.75350 -0.81792  -1.53846 -32.12291
3  1212109200 21:00:00 1.5512 10.75070 -1.47438  -8.24176 -34.35754
4  1212109500 21:05:00 1.5514 10.23529 -1.06044  -8.46154 -33.24022
5  1212109800 21:10:00 1.5514  9.74790 -1.02759 -10.21978 -33.24022
6  1212110100 21:15:00 1.5513  9.31092 -1.17076 -11.97802 -33.79888
7  1212110400 21:20:00 1.5512  8.84034 -1.28428 -13.62637 -34.35754
8  1212110700 21:25:00 1.5509  8.07843 -1.63715 -18.24176 -36.03352
9  1212111000 21:30:00 1.5509  7.39496 -1.49198 -20.65934 -36.03352
10 1212111300 21:35:00 1.5512  7.65266 -1.03717 -18.57143 -34.35754

数据已预先加载到 R 中，但在回溯测试期间，我需要按两个标准对其进行子集化：

第一个标准是滑动窗口，以避免窥视未来。该窗口必须是这样的，回测中每个新的 5 分钟间隔都会将整个窗口移至未来 5 分钟。这部分我可以这样做：

require(zoo)
zooser <- zoo(x=tser$Close, order.by=as.POSIXct(tser$pTime, origin="1970-01-01"))
window(zooser, start=A, end=B)

第二个条件是另一个滑动窗口，但它会滑动一天中的时间并且仅包含N内的那些条目 输入时间在任何给定日期的分钟数。

示例：如果窗口大小为2 小时，并且输入时间为12:00PM< /code> 那么窗口必须包含所有行10:00AM 和 2:00PM 之间的时间

这是我无法弄清楚的部分。

编辑：我的数据有漏洞，连续两行的间隔可能超过 5 分钟。数据看起来像这样（非常放大）在此处输入图像描述

当窗口移动穿过这些间隙时，窗口内的点数应该发生变化。

以下是我的 MySQL 代码，它执行我想要在 R 中执行的操作（相同的表结构）：

SET @qTime = Time(FROM_UNIXTIME(SAMP_endTime));

SET @inc = -1;
INSERT INTO MetIndListBuys (pTime,ArrayPos,M1,M2,M3,M4)
SELECT pTime,@inc:=@inc+1,M1,M2,M3,M4
 FROM mergebuys USE INDEX (`y`) WHERE  pTime BETWEEN SAMP_startTime AND SAMP_endTime 
AND TIME_TO_SEC(TIMEDIFF(Time,@qTime))/3600 BETWEEN 0-HourSpan AND HourSpan
;

原文

I have a large (150,000x7) dataframe that I intend to use for back-testing and real-time analysis of a financial market. The data represents the condition of an investment vehicle at 5 minute intervals (although holes do exist). It looks like this (but much longer):

        pTime     Time  Price       M1       M2        M3        M4
1  1212108300 20:45:00 1.5518 12.21849 -0.37125   4.50549 -31.00559
2  1212108900 20:55:00 1.5516 11.75350 -0.81792  -1.53846 -32.12291
3  1212109200 21:00:00 1.5512 10.75070 -1.47438  -8.24176 -34.35754
4  1212109500 21:05:00 1.5514 10.23529 -1.06044  -8.46154 -33.24022
5  1212109800 21:10:00 1.5514  9.74790 -1.02759 -10.21978 -33.24022
6  1212110100 21:15:00 1.5513  9.31092 -1.17076 -11.97802 -33.79888
7  1212110400 21:20:00 1.5512  8.84034 -1.28428 -13.62637 -34.35754
8  1212110700 21:25:00 1.5509  8.07843 -1.63715 -18.24176 -36.03352
9  1212111000 21:30:00 1.5509  7.39496 -1.49198 -20.65934 -36.03352
10 1212111300 21:35:00 1.5512  7.65266 -1.03717 -18.57143 -34.35754

The data is pre-loaded into R, but during my back-test I need to subset it by two criteria:

The first criteria is a sliding window to avoid peeking into the future. The window must be such that, each new 5 minute interval on the back-test shifts the whole window into the future by 5 minutes. This part I can do like this:

require(zoo)
zooser <- zoo(x=tser$Close, order.by=as.POSIXct(tser$pTime, origin="1970-01-01"))
window(zooser, start=A, end=B)

The second criteria is another sliding window, but one that slides through time of day and contains only those entries that are within N minutes of the input time on any given day.

Example: If the window's size is 2 hours, and the input time is 12:00PM then the window must contain all rows with Time between 10:00AM and 2:00PM

This is the part that I am having trouble figuring out.

Edit: My data has holes in it, two consecutive rows could be MORE than 5 minutes apart. The data looks like this (very zoomed in)
enter image description here

As the window moves through these gaps the number of points inside the windows should vary.

The following is my MySQL code that does what I want to do in R (same table structure):

SET @qTime = Time(FROM_UNIXTIME(SAMP_endTime));

SET @inc = -1;
INSERT INTO MetIndListBuys (pTime,ArrayPos,M1,M2,M3,M4)
SELECT pTime,@inc:=@inc+1,M1,M2,M3,M4
 FROM mergebuys USE INDEX (`y`) WHERE  pTime BETWEEN SAMP_startTime AND SAMP_endTime 
AND TIME_TO_SEC(TIMEDIFF(Time,@qTime))/3600 BETWEEN 0-HourSpan AND HourSpan
;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

晨曦慕雪 2024-12-29 07:30:58

假设您的目标时间 t0 与 pTime: 自纪元以来的秒数具有相同的尺度。那么 t0 - pTime =（自纪元以来两者之间的天数差）+（剩余秒数差）。采用 t0 - pTime %%（每天的秒数）将使我们得到时钟算术中以秒为单位的差异（如果差异为负则环绕）。这表明了以下功能：

SecondsPerDay <- 24 * 60 * 60
within <- function(d, t0Sec, wMin) {
  diff <- (d$pTime - t0Sec) %% SecondsPerDay
  wSec <- 60 * wMin
  return(d[diff < wSec | diff > (SecondsPerDay - wSec), ])
}

Say that you have your target time t0 on the same scale as pTime: seconds since epoch. Then t0 - pTime = (difference in the number of days since epoch between the two) + (difference in remaining seconds). Taking t0 - pTime %% (num. seconds per day) will leave us with the difference in seconds in clock arithmetic (wrapped around if the difference is negative). This suggests the following function:

SecondsPerDay <- 24 * 60 * 60
within <- function(d, t0Sec, wMin) {
  diff <- (d$pTime - t0Sec) %% SecondsPerDay
  wSec <- 60 * wMin
  return(d[diff < wSec | diff > (SecondsPerDay - wSec), ])
}

回复收藏 0 原文

咽泪装欢 2024-12-29 07:30:58

1) 如果 DF 是问题中显示的数据框，则像您所做的那样从中创建一个动物园对象，并将其拆分为给出 zs。然后lapply您的函数f到每个组件（即每天）中的每个连续的w点集。例如，如果您希望一次将函数应用于 2 小时的数据，并且您的数据定期间隔 5 分钟数据，则 w = 24（因为两小时内有 24 个五分钟周期）。在这种情况下，f 每次调用时都会以矩阵形式传递 24 行数据。另外，下面的 align 已设置为 "right"，但也可以设置为 align="center" 以及给出 的条件ix 可以更改为双面等。有关 rollapply 的更多信息，请参阅：?rollapply

library(zoo)
z <- zoo(DF[-2], as.POSIXct(DF[,1], origin = "1970-01-01"))
w <- 3 # replace this with 24 to handle two hours at a time with five min data
f <- function(x) {
            tt <- x[, 1]
            ix <- tt[w] - tt <= w * 5 * 60 # RHS converts w to seconds
            x <- x[ix, -1]
            sum(x) # replace sum with your function
    }
out <- rollapply(z, w, f, by.column = FALSE, align = "right")

使用问题中的数据框，我们得到：

> out
1) 如果 DF 是问题中显示的数据框，则像您所做的那样从中创建一个动物园对象，并将其拆分为给出 zs。然后lapply您的函数f到每个组件（即每天）中的每个连续的w点集。例如，如果您希望一次将函数应用于 2 小时的数据，并且您的数据定期间隔 5 分钟数据，则 w = 24（因为两小时内有 24 个五分钟周期）。在这种情况下，f 每次调用时都会以矩阵形式传递 24 行数据。另外，下面的 align 已设置为 "right"，但也可以设置为 align="center" 以及给出  的条件ix 可以更改为双面等。有关 rollapply 的更多信息，请参阅：?rollapply
library(zoo)
z <- zoo(DF[-2], as.POSIXct(DF[,1], origin = "1970-01-01"))
w <- 3 # replace this with 24 to handle two hours at a time with five min data
f <- function(x) {
            tt <- x[, 1]
            ix <- tt[w] - tt <= w * 5 * 60 # RHS converts w to seconds
            x <- x[ix, -1]
            sum(x) # replace sum with your function
    }
out <- rollapply(z, w, f, by.column = FALSE, align = "right")

使用问题中的数据框，我们得到：
2008-05-30`
2008-05-30 02:00:00 2008-05-30 02:05:00 2008-05-30 02:10:00 2008-05-30 02:15:00 
          -66.04703           -83.92148           -95.93558          -100.24924 
2008-05-30 02:20:00 2008-05-30 02:25:00 2008-05-30 02:30:00 2008-05-30 02:35:00 
         -108.15038          -121.24519          -134.39873          -140.28436

顺便说一句，请务必阅读这篇文章。

2) 这也可以按以下方式完成，其中 w 和 f 如上：

n <- nrow(DF)
m <- as.matrix(DF[-2])
sapply(w:n, function(i) { m <- m[seq(length = w, to = i), ]; f(m) })

将 sapply 替换为如果需要，lapply。此外，这可能看起来比第一个解决方案短，但一旦添加代码来定义 f 和 w （出现在第一个解决方案中，而不是第二个解决方案中），它就没有太大不同。

如果白天没有空洞，只有几天之间有空洞，那么这些解决方案就可以简化。

1) If DF is the data frame shown in the question then create a zoo object from it as you have done and split it into days giving zs. Then lapply your function f to each successive set of w points in each component (i.e. in each day). For example, if you want to apply your function to 2 hours of data at a time and your data is regularly spaced 5 minute data then w = 24 (since there are 24 five minute periods in two hours). In such a case f would be passed 24 rows of data as a matrix each time its called. Also align has been set to "right" below but it can alternately be set to align="center" and the condition giving ix can be changed to double sided, etc. For more on rollapply see: ?rollapply

library(zoo)
z <- zoo(DF[-2], as.POSIXct(DF[,1], origin = "1970-01-01"))
w <- 3 # replace this with 24 to handle two hours at a time with five min data
f <- function(x) {
            tt <- x[, 1]
            ix <- tt[w] - tt <= w * 5 * 60 # RHS converts w to seconds
            x <- x[ix, -1]
            sum(x) # replace sum with your function
    }
out <- rollapply(z, w, f, by.column = FALSE, align = "right")

Using the data frame in the question we get this:

> out
1) If DF is the data frame shown in the question then create a zoo object from it as you have done and split it into days giving zs.  Then lapply your function f to each successive set of w points in each component (i.e. in each day).  For example, if you want to apply your function to 2 hours of data at a time and your data is regularly spaced 5 minute data then w = 24 (since there are 24 five minute periods in two hours). In such a case f would be passed 24 rows of data as a matrix each time its called.  Also align has been set to "right" below but it can alternately be set to align="center" and the condition giving ix can be changed to double sided, etc. For more on rollapply see: ?rollapply
library(zoo)
z <- zoo(DF[-2], as.POSIXct(DF[,1], origin = "1970-01-01"))
w <- 3 # replace this with 24 to handle two hours at a time with five min data
f <- function(x) {
            tt <- x[, 1]
            ix <- tt[w] - tt <= w * 5 * 60 # RHS converts w to seconds
            x <- x[ix, -1]
            sum(x) # replace sum with your function
    }
out <- rollapply(z, w, f, by.column = FALSE, align = "right")

Using the data frame in the question we get this:
2008-05-30`
2008-05-30 02:00:00 2008-05-30 02:05:00 2008-05-30 02:10:00 2008-05-30 02:15:00 
          -66.04703           -83.92148           -95.93558          -100.24924 
2008-05-30 02:20:00 2008-05-30 02:25:00 2008-05-30 02:30:00 2008-05-30 02:35:00 
         -108.15038          -121.24519          -134.39873          -140.28436

By the way, be sure to read this post .

2) This could alternately be done as the following where w and f are as above:

n <- nrow(DF)
m <- as.matrix(DF[-2])
sapply(w:n, function(i) { m <- m[seq(length = w, to = i), ]; f(m) })

Replace the sapply with lapply if needed. Also this may seem shorter than the first solution but its not much different once you add the code to define f and w (which appear in the first but not the second).

If there are no holes during the day and only holes between days then these solutions could be simplified.

回复收藏 0 原文

~没有更多了~