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

[Matthew Dowle]

Hi Santosh,

Thanks. Looks like the task is fairly clear and your latest post
should produce output. I'm unlikely to have free (free) time
until next week. Maybe you'll get lucky and somebody else
can help before then.

Matthew


"sant" <santosh.srinivas at gmail.com> wrote in message 
news:3ecbc035-ed84-4e39-b93f-9c1386d28e9d at f2g2000yqf.googlegroups.com...
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...