1

There's two time datasets: data from raincollector -- time interval ti with start, end and rain p (total amount of rain per period in mm)

ti <- data.frame(
             start = c("2017-06-05 19:30:00", "2017-06-06 12:00:00"),
               end = c("2017-06-05 23:30:00", "2017-06-06 14:00:00"),
                 p = c(16.4, 4.4)
      )

ti[,1] <- as.POSIXct(ti[, 1])
ti[,2] <- as.POSIXct(ti[, 2])

and timeseries ts from gauging station with time and parameter q, which is the water discharge (cu. m per sec)

ts <- data.frame(stringsAsFactors=FALSE,
              time = c("2017-06-05 16:00:00", "2017-06-05 19:00:00",
                       "2017-06-05 21:00:00", "2017-06-05 23:00:00",
                       "2017-06-06 9:00:00", "2017-06-06 11:00:00", "2017-06-06 13:00:00",
                       "2017-06-06 16:00:00", "2017-06-06 17:00:00"),
                 q = c(0.78, 0.84, 0.9, 0.78, 0.78, 0.78, 0.78, 1.22, 1.25)
      )
ts[,1] <- as.POSIXct(ts[,1])

I need to intersect timeseries with time interval and create a new column in ts with TRUE/FALSE if this row in the rain interval (TRUE) and if it not (FALSE) like this one:

                 time    q  rain
1 2017-06-05 16:00:00 0.78 FALSE
2 2017-06-05 19:00:00 0.84 FALSE
3 2017-06-05 21:00:00 0.90  TRUE # there were rain
4 2017-06-05 23:00:00 0.78  TRUE # there were rain
5  2017-06-06 9:00:00 0.78 FALSE
6 2017-06-06 11:00:00 0.78 FALSE
7 2017-06-06 13:00:00 0.78  TRUE # there were rain
8 2017-06-06 16:00:00 1.22 FALSE
9 2017-06-06 17:00:00 1.25 FALSE

Have you got any ideas how to apply such simple operation?

Konrad Rudolph
  • 530,221
  • 131
  • 937
  • 1,214
atsyplenkov
  • 1,158
  • 13
  • 25
  • What's the purpose of `p` and `q`? – acylam Aug 16 '18 at 15:47
  • 1
    https://stackoverflow.com/questions/11892241/merge-by-range-in-r-applying-loops – BENY Aug 16 '18 at 15:53
  • @useR both of them are variables I'm investigating. `p` is the total amount of liquid precipitation (in mm) for the period from `start` to `end`. While `q` is the water discharge of the river – atsyplenkov Aug 16 '18 at 16:25

1 Answers1

2

With sqldf:

library(sqldf)
sqldf('select ts.*, case when ti.p is not null then 1 else 0 end as rain 
      from ts
      left join ti
      on start <= time and
         time <= end')

Result:

                 time    q rain
1 2017-06-05 16:00:00 0.78    0
2 2017-06-05 19:00:00 0.84    0
3 2017-06-05 21:00:00 0.90    1
4 2017-06-05 23:00:00 0.78    1
5  2017-06-06 9:00:00 0.78    0
6 2017-06-06 11:00:00 0.78    0
7 2017-06-06 13:00:00 0.78    1
8 2017-06-06 16:00:00 1.22    0
9 2017-06-06 17:00:00 1.25    0
acylam
  • 18,231
  • 5
  • 36
  • 45