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

[Santosh Srinivas]

Thanks Mathew. Never heard of IDateTime. I'll try that today.
Sorry about the data structure, it has both the "base" data and the
results of my transformations (may have messed the conversions up
trying to get this thing to work .... tried all options I knew of
then)


On Tue, Apr 12, 2011 at 3:42 AM, Matthew Dowle <mdowle at mdowle.plus.com> wrote:
> I took a quick look. I don't completely follow
> what you're doing (e.g. why the dates are factors)
> but have you seen IDateTime?
> Or the other way you can do this is have one column
> as date (IDate) and another as time (ITime); a
> 2 column key. That way you can ensure you join within
> the same day, but the prevailing record by time that day.
> HTH
> Matthew
>
> On Thu, 2011-04-07 at 14:05 +0100, Matthew Dowle wrote:
>> 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...
>>
>>
>>
>> _______________________________________________
>> datatable-help mailing list
>> datatable-help at lists.r-forge.r-project.org
>> https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help
>
>
>