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

[Matthew Dowle]

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 <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
> >
> >
> >