R：基于时间有效地子集数据帧 [英] R: Efficiently subsetting dataframe based on time of day
问题描述
pTime时间价格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 $关闭，order.by = as.POSIXct（tser $ pTime，origin =1970-01-01））
window（zooser，start = A，end = B）
第二个条件是另一个滑动窗口，但是通过 / code>，并且只包含在任何一天的输入时间的 N 分钟内的条目。
示例：如果窗口大小为 2小时，输入时间为 12:00 PM 然后窗口必须包含时间之间的所有行 10:00 AM 和 2:00 PM
这是我遇到麻烦的一部分。
编辑：我的数据有孔，连续两行可能超过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
;
说你的目标时间t0在同一个缩放为pTime：从时代开始的秒数。那么t0 - pTime =（两个历元之间的天数差）+（剩余秒差）。以t0 - pTime %%（每天数秒）将使我们在时钟算术中以秒为单位差异（如果差值为负数则包裹）。这表示以下功能：
SecondsPerDay < - 24 * 60 * 60
in& ，t0Sec，wMin）{
diff < - （d $ pTime - t0Sec）%% SecondsPerDay
wSec < - 60 * wMin
return（d [diff< wSec | diff> ;（SecondsPerDay - wSec），]）
}
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)
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
;
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), ])
}
这篇关于R：基于时间有效地子集数据帧的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
