How to make time increments 'finer' from minutes to seconds in R

  • A+
Category:Languages

I have a time series data with 1 minute increments. I have written a code but with the large amount of data I have (over 1M rows), looping through each line is taking way too long. The data looks something like the below:

t0 = as.POSIXlt("2018-12-23 00:01:00") t0 = t0+seq(60,60*10,60) p1 = seq(5,5*10,5) p2 = seq(7,7*10,7) m0 = cbind(p1,p2) rownames(m0) = as.character(t0) 

Where it looks something like:

> head(m0)                     p1 p2 2018-12-23 00:02:00  5  7 2018-12-23 00:03:00 10 14 2018-12-23 00:04:00 15 21 2018-12-23 00:05:00 20 28 2018-12-23 00:06:00 25 35 2018-12-23 00:07:00 30 42 

I want to turn this data into 5 seconds increments by adding 11 lines (55 seconds) before each minute with the value carrying over from the latest value. So it would something like:

> new0                     p1 p2 2018-12-23 00:01:05  5  7 2018-12-23 00:01:10  5  7 2018-12-23 00:01:15  5  7 2018-12-23 00:01:20  5  7 2018-12-23 00:01:25  5  7 2018-12-23 00:01:30  5  7 2018-12-23 00:01:35  5  7 2018-12-23 00:01:40  5  7 2018-12-23 00:01:45  5  7 2018-12-23 00:01:50  5  7 2018-12-23 00:01:55  5  7 2018-12-23 00:02:00  5  7 2018-12-23 00:02:05 10 14 2018-12-23 00:02:10 10 14 2018-12-23 00:02:15 10 14 2018-12-23 00:02:20 10 14 2018-12-23 00:02:25 10 14 2018-12-23 00:02:30 10 14 2018-12-23 00:02:35 10 14 2018-12-23 00:02:40 10 14 2018-12-23 00:02:45 10 14 2018-12-23 00:02:50 10 14 2018-12-23 00:02:55 10 14 2018-12-23 00:03:00 10 14 

I am hoping to find some way to do it without using a loop and utilizing the efficient codes in xts and/or data.table which I am not too familiar with. I tried using 'ave' base function and is also not fast enough..

I would greatly appreciate it if someone can help me on this problem!

 


Here's one way to do it in base R. First, convert your data to a data frame with an explicit column for the time stamps:

m0 <- as.data.frame(m0) m0$t <- t0     p1 p2                   t 1   5  7 2018-12-23 00:02:00 2  10 14 2018-12-23 00:03:00 3  15 21 2018-12-23 00:04:00 4  20 28 2018-12-23 00:05:00 5  25 35 2018-12-23 00:06:00 6  30 42 2018-12-23 00:07:00 7  35 49 2018-12-23 00:08:00 8  40 56 2018-12-23 00:09:00 9  45 63 2018-12-23 00:10:00 10 50 70 2018-12-23 00:11:00 

Then merge this data frame with a 1-column data frame of time differences (0 to 55):

m1 <- merge(m0, data.frame(diff = seq(0, 55, 5))) m1$t2 <- with(m1, t - diff) 

And finally, subtract the difference column from the timestamp column to create new values:

m1$t2 <- with(m1, t - diff)  > m1[c(1, 20, 40), ]     p1 p2                   t diff                  t2 1   5  7 2018-12-23 00:02:00    0 2018-12-23 00:02:00 20 50 70 2018-12-23 00:11:00    5 2018-12-23 00:10:55 40 50 70 2018-12-23 00:11:00   15 2018-12-23 00:10:45 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: