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

[sant]

Thanks Mathew.

I used IDate and ITime and they seamlessly integrate with the key
requirements for the roll to work (as far as I can see currently).
Now the keys seem logical to me.

Thanks for your help.

On Apr 13, 3:56 am, Matthew Dowle <mdo... at mdowle.plus.com> wrote:
> There wasn't actually a link to IDateTime from ?data.table in 1.5.3, but
> there has been in 1.5.4 on R-Forge for a while (in 'see also'), so it
> should be easier to find going forward, hopefully.
> Matthew
>
>
>
>
>
>
>
>
>
> On Tue, 2011-04-12 at 04:39 +0530, Santosh Srinivas wrote:
> > 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 <mdo... 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.srini... 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-h... at lists.r-forge.r-project.org
> > >>https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatabl...
>
> _______________________________________________
> datatable-help mailing list
> datatable-h... at lists.r-forge.r-project.orghttps://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatabl...