Generating a Column of Timestamps from a Column of Numbers Returns NAs in the Middle and End of the Column

Question

I'm having some trouble getting a column of numbers to convert to a time format. Which I would like to use as a reference for a timeseries. The column increments at intervals of 3, which represents minutes. I want to convert it to a timestamp, with or without a date. I've tried lubridate library using hms(df$V1, as.POSIXct(df$V1, format=%H:%M) and other versions, chron library using chron(dates = NULL, times = df$V1, format = c(NULL, "m"), "h:m") although I'm pretty sure I don't understand the proper format, strptime using strptime(df$V1, format="%M") as well as format(strptime(test$rn,format = "%M"), format = "%H:%M"), and as.Date. Some of these I got to work but when they did I consistently ran into the same problem as demonstrated by one such example:

library(data.table)
df<-as.data.table(seq(1,801,by=3))
strptime(df$V1, format="%M")

Which returns:

[1] "2019-03-22 00:00:00 EDT" "2019-03-22 00:03:00 EDT"
[3] "2019-03-22 00:06:00 EDT" "2019-03-22 00:09:00 EDT"
[5] "2019-03-22 00:12:00 EDT" "2019-03-22 00:15:00 EDT"
[7] "2019-03-22 00:18:00 EDT" "2019-03-22 00:21:00 EDT"
[9] "2019-03-22 00:24:00 EDT" "2019-03-22 00:27:00 EDT"
[11] "2019-03-22 00:30:00 EDT" "2019-03-22 00:33:00 EDT"
[13] "2019-03-22 00:36:00 EDT" "2019-03-22 00:39:00 EDT"
[15] "2019-03-22 00:42:00 EDT" "2019-03-22 00:45:00 EDT"
[17] "2019-03-22 00:48:00 EDT" "2019-03-22 00:51:00 EDT"
[19] "2019-03-22 00:54:00 EDT" "2019-03-22 00:57:00 EDT"
[21] NA                        NA                       
[23] NA                        NA                       
[25] NA                        NA                       
[27] NA                        NA                       
[29] NA                        NA                       
[31] NA                        NA                       
[33] NA                        NA                       
[35] "2019-03-22 00:10:00 EDT" "2019-03-22 00:10:00 EDT"
[37] "2019-03-22 00:10:00 EDT" "2019-03-22 00:11:00 EDT"
[39] "2019-03-22 00:11:00 EDT" "2019-03-22 00:11:00 EDT"
[41] "2019-03-22 00:12:00 EDT" "2019-03-22 00:12:00 EDT"
[43] "2019-03-22 00:12:00 EDT" "2019-03-22 00:12:00 EDT"
[45] "2019-03-22 00:13:00 EDT" "2019-03-22 00:13:00 EDT"
[47] "2019-03-22 00:13:00 EDT" "2019-03-22 00:14:00 EDT"
[49] "2019-03-22 00:14:00 EDT" "2019-03-22 00:14:00 EDT"
[51] "2019-03-22 00:15:00 EDT" "2019-03-22 00:15:00 EDT"
[53] "2019-03-22 00:15:00 EDT" "2019-03-22 00:15:00 EDT"
[55] "2019-03-22 00:16:00 EDT" "2019-03-22 00:16:00 EDT"
[57] "2019-03-22 00:16:00 EDT" "2019-03-22 00:17:00 EDT"
[59] "2019-03-22 00:17:00 EDT" "2019-03-22 00:17:00 EDT"
[61] "2019-03-22 00:18:00 EDT" "2019-03-22 00:18:00 EDT"
[63] "2019-03-22 00:18:00 EDT" "2019-03-22 00:18:00 EDT"
[65] "2019-03-22 00:19:00 EDT" "2019-03-22 00:19:00 EDT"
[67] "2019-03-22 00:19:00 EDT" "2019-03-22 00:20:00 EDT"
[69] "2019-03-22 00:20:00 EDT" "2019-03-22 00:20:00 EDT"
[71] "2019-03-22 00:21:00 EDT" "2019-03-22 00:21:00 EDT"
[73] "2019-03-22 00:21:00 EDT" "2019-03-22 00:21:00 EDT"
[75] "2019-03-22 00:22:00 EDT" "2019-03-22 00:22:00 EDT"
[77] "2019-03-22 00:22:00 EDT" "2019-03-22 00:23:00 EDT"
[79] "2019-03-22 00:23:00 EDT" "2019-03-22 00:23:00 EDT"
[81] "2019-03-22 00:24:00 EDT" "2019-03-22 00:24:00 EDT"
[83] "2019-03-22 00:24:00 EDT" "2019-03-22 00:24:00 EDT"
[85] "2019-03-22 00:25:00 EDT" "2019-03-22 00:25:00 EDT"
[87] "2019-03-22 00:25:00 EDT" "2019-03-22 00:26:00 EDT"
[89] "2019-03-22 00:26:00 EDT" "2019-03-22 00:26:00 EDT"
[91] "2019-03-22 00:27:00 EDT" "2019-03-22 00:27:00 EDT"
[93] "2019-03-22 00:27:00 EDT" "2019-03-22 00:27:00 EDT"
[95] "2019-03-22 00:28:00 EDT" "2019-03-22 00:28:00 EDT"
[97] "2019-03-22 00:28:00 EDT" "2019-03-22 00:29:00 EDT"
[99] "2019-03-22 00:29:00 EDT" "2019-03-22 00:29:00 EDT"
[101] "2019-03-22 00:30:00 EDT" "2019-03-22 00:30:00 EDT"
[103] "2019-03-22 00:30:00 EDT" "2019-03-22 00:30:00 EDT"
[105] "2019-03-22 00:31:00 EDT" "2019-03-22 00:31:00 EDT"
[107] "2019-03-22 00:31:00 EDT" "2019-03-22 00:32:00 EDT"
[109] "2019-03-22 00:32:00 EDT" "2019-03-22 00:32:00 EDT"
[111] "2019-03-22 00:33:00 EDT" "2019-03-22 00:33:00 EDT"
[113] "2019-03-22 00:33:00 EDT" "2019-03-22 00:33:00 EDT"
[115] "2019-03-22 00:34:00 EDT" "2019-03-22 00:34:00 EDT"
[117] "2019-03-22 00:34:00 EDT" "2019-03-22 00:35:00 EDT"
[119] "2019-03-22 00:35:00 EDT" "2019-03-22 00:35:00 EDT"
[121] "2019-03-22 00:36:00 EDT" "2019-03-22 00:36:00 EDT"
[123] "2019-03-22 00:36:00 EDT" "2019-03-22 00:36:00 EDT"
[125] "2019-03-22 00:37:00 EDT" "2019-03-22 00:37:00 EDT"
[127] "2019-03-22 00:37:00 EDT" "2019-03-22 00:38:00 EDT"
[129] "2019-03-22 00:38:00 EDT" "2019-03-22 00:38:00 EDT"
[131] "2019-03-22 00:39:00 EDT" "2019-03-22 00:39:00 EDT"
[133] "2019-03-22 00:39:00 EDT" "2019-03-22 00:39:00 EDT"
[135] "2019-03-22 00:40:00 EDT" "2019-03-22 00:40:00 EDT"
[137] "2019-03-22 00:40:00 EDT" "2019-03-22 00:41:00 EDT"
[139] "2019-03-22 00:41:00 EDT" "2019-03-22 00:41:00 EDT"
[141] "2019-03-22 00:42:00 EDT" "2019-03-22 00:42:00 EDT"
[143] "2019-03-22 00:42:00 EDT" "2019-03-22 00:42:00 EDT"
[145] "2019-03-22 00:43:00 EDT" "2019-03-22 00:43:00 EDT"
[147] "2019-03-22 00:43:00 EDT" "2019-03-22 00:44:00 EDT"
[149] "2019-03-22 00:44:00 EDT" "2019-03-22 00:44:00 EDT"
[151] "2019-03-22 00:45:00 EDT" "2019-03-22 00:45:00 EDT"
[153] "2019-03-22 00:45:00 EDT" "2019-03-22 00:45:00 EDT"
[155] "2019-03-22 00:46:00 EDT" "2019-03-22 00:46:00 EDT"
[157] "2019-03-22 00:46:00 EDT" "2019-03-22 00:47:00 EDT"
[159] "2019-03-22 00:47:00 EDT" "2019-03-22 00:47:00 EDT"
[161] "2019-03-22 00:48:00 EDT" "2019-03-22 00:48:00 EDT"
[163] "2019-03-22 00:48:00 EDT" "2019-03-22 00:48:00 EDT"
[165] "2019-03-22 00:49:00 EDT" "2019-03-22 00:49:00 EDT"
[167] "2019-03-22 00:49:00 EDT" "2019-03-22 00:50:00 EDT"
[169] "2019-03-22 00:50:00 EDT" "2019-03-22 00:50:00 EDT"
[171] "2019-03-22 00:51:00 EDT" "2019-03-22 00:51:00 EDT"
[173] "2019-03-22 00:51:00 EDT" "2019-03-22 00:51:00 EDT"
[175] "2019-03-22 00:52:00 EDT" "2019-03-22 00:52:00 EDT"
[177] "2019-03-22 00:52:00 EDT" "2019-03-22 00:53:00 EDT"
[179] "2019-03-22 00:53:00 EDT" "2019-03-22 00:53:00 EDT"
[181] "2019-03-22 00:54:00 EDT" "2019-03-22 00:54:00 EDT"
[183] "2019-03-22 00:54:00 EDT" "2019-03-22 00:54:00 EDT"
[185] "2019-03-22 00:55:00 EDT" "2019-03-22 00:55:00 EDT"
[187] "2019-03-22 00:55:00 EDT" "2019-03-22 00:56:00 EDT"
[189] "2019-03-22 00:56:00 EDT" "2019-03-22 00:56:00 EDT"
[191] "2019-03-22 00:57:00 EDT" "2019-03-22 00:57:00 EDT"
[193] "2019-03-22 00:57:00 EDT" "2019-03-22 00:57:00 EDT"
[195] "2019-03-22 00:58:00 EDT" "2019-03-22 00:58:00 EDT"
[197] "2019-03-22 00:58:00 EDT" "2019-03-22 00:59:00 EDT"
[199] "2019-03-22 00:59:00 EDT" "2019-03-22 00:59:00 EDT"
[201] NA                        NA                       
[203] NA                        NA                       
[205] NA                        NA                       
[207] NA                        NA                       
[209] NA                        NA                       
[211] NA                        NA                       
[213] NA                        NA                       
[215] NA                        NA                       
[217] NA                        NA                       
[219] NA                        NA                       
[221] NA                        NA                       
[223] NA                        NA                       
[225] NA                        NA                       
[227] NA                        NA                       
[229] NA                        NA                       
[231] NA                        NA                       
[233] NA                        NA                       
[235] NA                        NA                       
[237] NA                        NA                       
[239] NA                        NA                       
[241] NA                        NA                       
[243] NA                        NA                       
[245] NA                        NA                       
[247] NA                        NA                       
[249] NA                        NA                       
[251] NA                        NA                       
[253] NA                        NA                       
[255] NA                        NA                       
[257] NA                        NA                       
[259] NA                        NA                       
[261] NA                        NA                       
[263] NA                        NA                       
[265] NA                        NA                       
[267] NA                        NA

As can be seen, the performance works well until it reaches the 1 hour mark then returns NA for several values. It then resumes generating values but the values return every minute-value 3 times instead of one value every 3 minutes. It stops generating values altogether after the 200th value. In addition, It is not generating a value for the hour after it increments past the one hour mark. All approaches which I got to work had the same problem. Any help would be greatly appreciated.

Robele Baker · Accepted Answer · 2019-03-22T23:27:33.623

1

Have you thought about using the seq.POSIXt function in R's base library?

The function takes the following: seq.POSIXt(from, to, by, length.out = NULL, along.with = NULL, ...)

Maybe I am not comprehending what your from and to are, but I believe the following should work.

library(data.table)
fromDate <- as.POSIXct('2018-03-22')
toDate <- as.POSIXct('2018-03-23')

df<-as.data.table(seq.POSIXt(fromDate, toDate, by = '3 min'))

Adjust your fromDate and toDate to whatever makes sense to you. If you only want to increment by a fixed number of positions, I can make an edit for that. Otherwise, this will work incredibly quickly!

edited Mar 22 '19 at 23:27

answered Mar 22 '19 at 23:22

Robele Baker

91
5

1

This is an interesting approach. I could use these values by substituting them for the incrementing values that I already have. I appreciate your response. Thanks for taking the time. I also appreciate your willingness to make the edit but I would like to try that part on my own, now that you have given me a way to get there. Thanks again. – Bar-Tzur Mar 24 '19 at 16:48

Generating a Column of Timestamps from a Column of Numbers Returns NAs in the Middle and End of the Column

1 Answers1