First highest value in every frame in the list and adding to a new column
I am trying to find the first time the "price" is higher than another "dayhigh" value on the day.
I am having trouble getting this result into a time series object, so I just use the POSIXlt class for datetime and the day for the reference is in the Date class. Sample data is in a frame called "example":
day,datetime,price,dayhigh
2016-09-01,2016-09-01 15:00:00,1.11912,1.11990
2016-09-01,2016-09-01 15:00:00,1.13000,1.11990
2016-09-01,2016-09-01 15:00:01,1.11911,1.11990
2016-09-05,2016-09-05 15:00:00,1.11436,1.11823
2016-09-05,2016-09-05 15:00:01,1.11436,1.11823
2016-09-05,2016-09-05 15:00:01,1.11900,1.11823
2016-09-05,2016-09-05 15:00:01,1.11436,1.11823
2016-09-06,2016-09-06 15:00:00,1.12383,1.12557
2016-09-06,2016-09-06 15:00:00,1.12382,1.12557
2016-09-06,2016-09-06 15:00:00,1.12382,1.12557
2016-09-06,2016-09-06 15:00:00,1.12384,1.12557
2016-09-06,2016-09-06 15:00:00,1.12384,1.12557
2016-09-06,2016-09-06 15:00:00,1.12558,1.12557
2016-09-06,2016-09-06 15:00:01,1.12559,1.12557
df = data.frame(
day = c("2016-09-01", "2016-09-01", "2016-09-01", "2016-09-05", "2016-09-05",
"2016-09-05", "2016-09-05", "2016-09-06", "2016-09-06", "2016-09-06",
"2016-09-06", "2016-09-06", "2016-09-06", "2016-09-06"),
datetime = c("2016-09-01 15:00:00", "2016-09-01 15:00:00", "2016-09-01 15:00:01",
"2016-09-05 15:00:00", "2016-09-05 15:00:01", "2016-09-05 15:00:01",
"2016-09-05 15:00:01", "2016-09-06 15:00:00", "2016-09-06 15:00:00",
"2016-09-06 15:00:00", "2016-09-06 15:00:00", "2016-09-06 15:00:00",
"2016-09-06 15:00:00", "2016-09-06 15:00:01"),
price = c(1.11912, 1.13, 1.11911, 1.11436, 1.11436, 1.119, 1.11436,
1.12383, 1.12382, 1.12382, 1.12384, 1.12384, 1.12558, 1.12559),
dayhigh = c(1.1199, 1.1199, 1.1199, 1.11823, 1.11823, 1.11823, 1.11823,
1.12557, 1.12557, 1.12557, 1.12557, 1.12557, 1.12557, 1.12557)
)
One of my ideas was to split the frame per day into a list of frames:
exlist <- split(example, as.Date(example$day))
Returns a list of objects.
What I would like to do is use which.max
for each frame object in the list and add "TRUE" to a new column in each frame for the row where the first day occurs. The first high day is defined as the first price > dayhigh
for each day.
From there, I can merge into one frame and perform further analysis.
source to share
You don't have to go through all this work, you can use data.table
it in one step:
library(data.table)
setDT(df)
df[ , first_high := (seq_len(.N) == which(price > dayhigh)[1]), by = day ]
df
# day datetime price dayhigh first_high
# 1: 2016-09-01 2016-09-01 15:00:00 1.11912 1.11990 FALSE
# 2: 2016-09-01 2016-09-01 15:00:00 1.13000 1.11990 TRUE
# 3: 2016-09-01 2016-09-01 15:00:01 1.11911 1.11990 FALSE
# 4: 2016-09-05 2016-09-05 15:00:00 1.11436 1.11823 FALSE
# 5: 2016-09-05 2016-09-05 15:00:01 1.11436 1.11823 FALSE
# 6: 2016-09-05 2016-09-05 15:00:01 1.11900 1.11823 TRUE
# 7: 2016-09-05 2016-09-05 15:00:01 1.11436 1.11823 FALSE
# 8: 2016-09-06 2016-09-06 15:00:00 1.12383 1.12557 FALSE
# 9: 2016-09-06 2016-09-06 15:00:00 1.12382 1.12557 FALSE
#10: 2016-09-06 2016-09-06 15:00:00 1.12382 1.12557 FALSE
#11: 2016-09-06 2016-09-06 15:00:00 1.12384 1.12557 FALSE
#12: 2016-09-06 2016-09-06 15:00:00 1.12384 1.12557 FALSE
#13: 2016-09-06 2016-09-06 15:00:00 1.12558 1.12557 TRUE
#14: 2016-09-06 2016-09-06 15:00:01 1.12559 1.12557 FALSE
source to share
You can use it ave
twice.
# 1) To make sure that price
more thandayhigh
# 2) To make sure this is the first time for a given subgroup.
ave(1:NROW(df), df$day, FUN = function(i) df$price[i] > df$dayhigh[i]) & #1
ave(1:NROW(df), df$day, FUN = function(i) cumsum(df$price[i] > df$dayhigh[i]) == 1) #2
#[1] FALSE TRUE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE
source to share