CODEWITHSUNDEEP
r
PVic
PVic

Reputation: 459

R Time Series from Time Intervals

I have two columns of datetimes. One being START & other being END. Is there a way to create a time series increment of 1 minute from the START to END with multiple rows of time intervals...

What I have...

x <- data.frame(START = c("3/28/2018  9:30", "3/28/2018  9:40"),
       END = c("3/28/2018  9:35", "3/28/2018  9:42"))

What I want...

LOG_START_DT    LOG_END_DT
3/28/2018 9:30  3/28/2018 9:35
3/29/2018 9:31  3/29/2018 9:35
3/30/2018 9:32  3/30/2018 9:35
3/31/2018 9:33  3/31/2018 9:35
4/1/2018 9:34   4/1/2018 9:35
4/2/2018 9:35   4/2/2018 9:35
3/28/2018 9:40  3/28/2018 9:42
3/28/2018 9:41  3/28/2018 9:42
3/28/2018 9:42  3/28/2018 9:42

The only thing I've been able to think of doing is a for loop, but I'm sure there has to be a more efficient way.

This is what I've done that works well, but with how large my data is will take longer than I need...

out = NULL
for(i in seq_along(x$LOG_START_DT)){
  sq = as.POSIXct(seq(from=x$LOG_START_DT[i], to=x$LOG_END_DT[i], by = 60), origin='1970-1-1', tz="UTC")
  out = rbindlist(list(out, data.frame(sq)))
}

Upvotes: 1

Views: 63

Answers (2)

Ralf Stubner
Ralf Stubner

Reputation: 26843

You can use apply to extend every row and then Reduce and rbind to combine the results:

x <- data.frame(START = c("2018-03-28  9:30", "2018-03-28  9:40", "2018-03-28 9:45"),
                END = c("2018-03-28  9:35", "2018-03-28  9:42", "2018-03-28 9:49"))

Reduce(rbind, 
       apply(x, 1, function(row) {
                      data.frame(START = seq.POSIXt(from = as.POSIXct(row[1]), 
                                                    to = as.POSIXct(row[2]), 
                                                    by = "min"), 
                                 END = as.POSIXct(row[2]),
                                 row.names = NULL)
                   }
             )
)
#                  START                 END
# 1  2018-03-28 09:30:00 2018-03-28 09:35:00
# 2  2018-03-28 09:31:00 2018-03-28 09:35:00
# 3  2018-03-28 09:32:00 2018-03-28 09:35:00
# 4  2018-03-28 09:33:00 2018-03-28 09:35:00
# 5  2018-03-28 09:34:00 2018-03-28 09:35:00
# 6  2018-03-28 09:35:00 2018-03-28 09:35:00
# 7  2018-03-28 09:40:00 2018-03-28 09:42:00
# 8  2018-03-28 09:41:00 2018-03-28 09:42:00
# 9  2018-03-28 09:42:00 2018-03-28 09:42:00
# 10 2018-03-28 09:45:00 2018-03-28 09:49:00
# 11 2018-03-28 09:46:00 2018-03-28 09:49:00
# 12 2018-03-28 09:47:00 2018-03-28 09:49:00
# 13 2018-03-28 09:48:00 2018-03-28 09:49:00
# 14 2018-03-28 09:49:00 2018-03-28 09:49:00

Upvotes: 1

Marmite Bomber
Marmite Bomber

Reputation: 21115

Use seq to fill the minutes and rbind the data.frames for each row or your original data.frame

> x <- data.frame(START = c("2018-03-28  9:30", "2018-03-28  9:40"),
+        END = c("2018-03-28  9:35", "2018-03-28  9:42"))
> rbind(       
+ data.frame(LOG_START_DT = seq(as.POSIXct(x[1,1]), as.POSIXct(x[1,2]), by="min"), LOG_END_DT = as.POSIXct(x[1,2])),
+ data.frame(LOG_START_DT = seq(as.POSIXct(x[2,1]), as.POSIXct(x[2,2]), by="min"), LOG_END_DT = as.POSIXct(x[2,2])) 
+ )
         LOG_START_DT          LOG_END_DT
1 2018-03-28 09:30:00 2018-03-28 09:35:00
2 2018-03-28 09:31:00 2018-03-28 09:35:00
3 2018-03-28 09:32:00 2018-03-28 09:35:00
4 2018-03-28 09:33:00 2018-03-28 09:35:00
5 2018-03-28 09:34:00 2018-03-28 09:35:00
6 2018-03-28 09:35:00 2018-03-28 09:35:00
7 2018-03-28 09:40:00 2018-03-28 09:42:00
8 2018-03-28 09:41:00 2018-03-28 09:42:00
9 2018-03-28 09:42:00 2018-03-28 09:42:00

Upvotes: 0

Related Questions