[datatable-help] Help with using roll = TRUE ... need some variation of VLOOKUP in excel with LOCF

[sant]

Hi All,

I am not sure if this is a hack ... but I did the following
manipulations after loading the data from the link.
I think I've managed to get the data ... only not sure if it is the
correct way of doing this.

dt_Opt_Final$time <- with(dt_Opt_Final, substr(datetime, 9,
length(datetime))) #Extracting the time part only
dt_Opt_Final$time <- as.numeric(dt_Opt_Final$time) * 1e6 #So that the
hierarchy of the timestamp is preserved
dt_Opt_Final$time <- as.integer(dt_Opt_Final$time) #since roll works
only when the key is an integer
setkey(dt_Opt_Final, "time")

dt_Fut_Final$time <- with(dt_Fut_Final, substr(datetime, 9,
length(datetime))) #Extracting the time part only
dt_Fut_Final$time <- as.numeric(dt_Fut_Final$time) * 1e6 #So that the
hierarchy of the timestamp is preserved
dt_Fut_Final$time <- as.integer(dt_Fut_Final$time) #since roll works
only when the key is an integer
setkey(dt_Fut_Final, "time")

tmp <- dt_Fut_Final[dt_Opt_Final, list(Id, price),roll=TRUE]
dt_Final <- data.table(dt_Opt_Final, tmp)
print(dt_Final, nrows=100)


On Apr 7, 3:54 pm, Santosh Srinivas <santosh.srini... at gmail.com>
wrote:
> Hi All,
>
> I am completely stuck on this and I want to do this only with
> data.table! Here are the data files (click link pls):http://bit.ly/e5P5d5
>
> I am just not able to make the roll functions work.
> I have the two data sets 1) Options and 2) Futures
>
> Please take a look at the data so that the question becomes quite easy
> to understand.
>
> The options have repeated datetimes ... and the options and futures
> timestamps do not match 1 to 1
>
> > NROW(dt_Opt_Final)
> [1] 11793
> > NROW(unique(dt_Opt_Final$datetime))
>
> [1] 6292
>
> I want to get the nearest futures price available for every option.
>
> I believe it should be able to get my data using
> dt_Fut_Final[dt_Opt_Final, roll=TRUE]
> (But I tried all combinations of setkey but unable to get it to work).
>
> I tried setting keys but I think I am losing information because the datetime
> between the two datasets are absolute values but the conversion to
> factors when I do setkey loses the relative information the two
> datasets.
>
> Please help!
>
> Thanks,
> Santosh
> _______________________________________________
> datatable-help mailing list
> datatable-h... at lists.r-forge.r-project.orghttps://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatabl...