2

I am looking to generate or complete a column of dates and times. I have a dataframe of four numeric columns and one POSIXct time column that looks like this:

    CH_1   CH_2  CH_3  CH_4           date_time
1 -10096 -11940 -9340 -9972 2018-07-24 10:45:01
2 -10088 -11964 -9348 -9960                <NA>
3 -10084 -11940 -9332 -9956                <NA>
4 -10088 -11956 -9340 -9960                <NA>
5 -10084 -11944 -9332 -9976                <NA>
6 -10076 -11940 -9340 -9948                <NA>
7 -10088 -11956 -9352 -9960                <NA>
8 -10084 -11944 -9348 -9980                <NA>
9 -10076 -11964 -9348 -9976                <NA>
0 -10076 -11956 -9348 -9964                <NA>

I would like to sequentially generate dates and times for the date_time column, increasing by 1 second until the dataframe is filled. (i.e. the next date/time should be 2018-07-24 10:45:02). This is meant to be reproducible for multiple datasets and the number of rows that need filled is not always known, but the start date/time will always be present in that first cell.

I know that the solution is likely within seq.Date (or similar), but the problem I have is that I won't always know the end date/time, which is what most examples I have found require. Any help would be appreciated!

LukeP
  • 93
  • 8
  • 1
    Please share your data using `dput()` and the code you're working on so others can help. See more here [How to make a great R reproducible example?](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) – Tung Oct 02 '18 at 03:28
  • If you are always going to have data at first row , you coud try `seq(as.POSIXct(df$date_time[1]), length.out = nrow(df), by = "1 sec")` . Check this for example, `seq(as.POSIXct("2018-07-24 10:45:01"), length.out = 10, by = "1 sec")` – Ronak Shah Oct 02 '18 at 03:38

2 Answers2

3

Here's a tidyverse solution, using Zygmunt Zawadzki's example data:

library(lubridate)
library(tidyverse)

df %>% mutate(date_time = date_time[1] + seconds(row_number()-1))

Output:

             date_time
1  2018-01-01 00:00:00
2  2018-01-01 00:00:01
3  2018-01-01 00:00:02
4  2018-01-01 00:00:03
5  2018-01-01 00:00:04
6  2018-01-01 00:00:05
7  2018-01-01 00:00:06
8  2018-01-01 00:00:07
9  2018-01-01 00:00:08
10 2018-01-01 00:00:09
11 2018-01-01 00:00:10

Data:

df <- data.frame(date_time = c(as.POSIXct("2018-01-01 00:00:00"), rep(NA,10)))
andrew_reece
  • 20,390
  • 3
  • 33
  • 58
2

No need for lubridate, just,R code:

x <- data.frame(date = c(as.POSIXct("2018-01-01 00:00:00"), rep(NA,10)))
startDate <- x[["date"]][1]
x[["date2"]] <- startDate + (seq_len(nrow(x)) - 1)
x
#         date               date2
# 1  2018-01-01 2018-01-01 00:00:00
# 2        <NA> 2018-01-01 00:00:01
# 3        <NA> 2018-01-01 00:00:02
# 4        <NA> 2018-01-01 00:00:03
# 5        <NA> 2018-01-01 00:00:04
# 6        <NA> 2018-01-01 00:00:05
# 7        <NA> 2018-01-01 00:00:06
# 8        <NA> 2018-01-01 00:00:07
# 9        <NA> 2018-01-01 00:00:08
# 10       <NA> 2018-01-01 00:00:09
# 11       <NA> 2018-01-01 00:00:10
Zygmunt Zawadzki
  • 251
  • 1
  • 6
  • Thank you for your solution. It works perfectly, but I have a follow up question. How would I increase the time by tenth's of second? For instance, if the starting point was 2018-07-24 10:45:01.1 and I would like the next row would be 2018-07-24 10:45:01.2? – LukeP Oct 18 '18 at 11:52