Add a data to a data frame from another smaller data frame based a condition

Question

I'm currently trying to come up with a way of pasting bits of data from one data frame to another based start times and end times

Suppose I have the data set call it testsubset1

        Event            FlowRate
1   2013-05-25 17:29:31 | 0.3739769
2   2013-05-25 17:37:31 | 0.5208873
3   2013-05-25 17:39:01 | 0.4235871
20  2013-05-26 01:16:31 | 0.3010403
21  2013-05-26 01:38:41 | 0.3054283
22  2013-05-26 02:01:01 | 0.3919175
116 2013-05-28 10:58:11 | 0.3851580
117 2013-05-28 11:11:12 | 0.3981671
118 2013-05-28 11:16:21 | 0.4075771
253 2013-05-31 08:31:11 | 0.3543576
254 2013-05-31 08:53:21 | 0.3553817

I also have another data set call it Flow Obs

    Start                  Finish              FlowObs
1 2013-05-25 17:29:00 | 2013-05-26 18:38:00 | 0.3307309
2 2013-05-27 16:22:00 | 2013-05-28 20:15:00 | 0.3286909
3 2013-05-29 13:05:00 | 2013-05-30 14:42:00 | 0.3211857
4 2013-05-30 15:08:00 | 2013-06-03 11:54:00 | 0.3277443

Now I want to to bind the elements of column 3 in Flow Obs to the big data based on the start and finish time so the final data set looks like

      Event               FlowRate     FlowObs
1   2013-05-25 17:29:31 | 0.3739769 | 0.3307309
2   2013-05-25 17:37:31 | 0.5208873 | 0.3307309
3   2013-05-25 17:39:01 | 0.4235871 | 0.3307309
20  2013-05-26 01:16:31 | 0.3010403 | 0.3307309
21  2013-05-26 01:38:41 | 0.3054283 | 0.3307309
22  2013-05-26 02:01:01 | 0.3919175 | 0.3307309
116 2013-05-28 10:58:11 | 0.3851580 | 0.3286909
117 2013-05-28 11:11:12 | 0.3981671 | 0.3286909
118 2013-05-28 11:16:21 | 0.4075771 | 0.3286909
253 2013-05-31 08:31:11 | 0.3543576 | 0.3277443
254 2013-05-31 08:53:21 | 0.3553817 | 0.3277443

The logic is that it rbinds FlowObs to testsubset if event is in between start and finish of Flow obs.

I'm sure there is a clever way to do this with and apply function, but I can't quite wrap my head around it.

I've tried coming up with a for loop that accomplishes this, but couldn't come up with a proper way to step in the smaller data frame

Hopefully this question makes sense. I am still new to asking questions on Stack Over flow.

As a side note the time is POSIX and the flows are all numeric.

Edit: I even tried just doing this:

testsubset1[(testsubset1$Event) %in% (c(flowobs[[1]][1], flowobs[[1]][2])),]

and it returns

[1] Event    FlowRate
<0 rows> (or 0-length row.names)

I feel certain I've seen something more specific to time series, but the best I could dig up was [this](http://stackoverflow.com/q/11892241/324364) question with a method that is intended to be used on genetic data, but should work here if you operate on the numeric values underlying the POSIX times. — joran, Jun 05 '13 at 22:28
Ok, [here](http://stackoverflow.com/q/8185201/324364) is one more specific to time series. — joran, Jun 05 '13 at 22:30

score 2 · Answer 1 · answered Jun 05 '13 at 22:59

Using sqldf:

library(sqldf)
sqldf("select * from testsubset1 t, FlowObs f 
   where t.Event between f.Start and f.Finish")

which gives:

                 Event  FlowRate               Start              Finish   FlowObs
1  2013-05-25 17:29:31 0.3739769 2013-05-25 17:29:00 2013-05-26 18:38:00 0.3307309
2  2013-05-25 17:37:31 0.5208873 2013-05-25 17:29:00 2013-05-26 18:38:00 0.3307309
3  2013-05-25 17:39:01 0.4235871 2013-05-25 17:29:00 2013-05-26 18:38:00 0.3307309
4  2013-05-26 01:16:31 0.3010403 2013-05-25 17:29:00 2013-05-26 18:38:00 0.3307309
5  2013-05-26 01:38:41 0.3054283 2013-05-25 17:29:00 2013-05-26 18:38:00 0.3307309
6  2013-05-26 02:01:01 0.3919175 2013-05-25 17:29:00 2013-05-26 18:38:00 0.3307309
7  2013-05-28 10:58:11 0.3851580 2013-05-27 16:22:00 2013-05-28 20:15:00 0.3286909
8  2013-05-28 11:11:12 0.3981671 2013-05-27 16:22:00 2013-05-28 20:15:00 0.3286909
9  2013-05-28 11:16:21 0.4075771 2013-05-27 16:22:00 2013-05-28 20:15:00 0.3286909
10 2013-05-31 08:31:11 0.3543576 2013-05-30 15:08:00 2013-06-03 11:54:00 0.3277443
11 2013-05-31 08:53:21 0.3553817 2013-05-30 15:08:00 2013-06-03 11:54:00 0.3277443

For some reason I was unable to get this package to work correctly. I think it may have to do with my build of R. — ZDwhite, Jun 06 '13 at 00:14

Add a data to a data frame from another smaller data frame based a condition

1 Answers1