Sorting data by time interval using start and end times in R

Question

I have a couple of data collected by date and time. The collected data is by time interval. I'm doing gas analysis by sensor. So the sensor gave me data in the following form

RFID `Farm Name` `Start Time`      `End Time`       
     <dbl>       <dbl> <chr>             <chr>            
 1 9.82e14     9.82e14 1/09/2022 0:34:15 1/09/2022 0:43:19
 2 9.82e14     9.82e14 1/09/2022 0:47:31 1/09/2022 0:53:12
 3 9.82e14     9.82e14 1/09/2022 0:59:33 1/09/2022 1:03:01
 4 9.82e14     9.82e14 1/09/2022 1:46:14 1/09/2022 1:49:27
 5 9.82e14     9.82e14 1/09/2022 1:58:17 1/09/2022 2:08:25
 6 9.82e14     9.82e14 1/09/2022 5:40:06 1/09/2022 5:48:05
 7 9.82e14     9.82e14 1/09/2022 6:03:59 1/09/2022 6:12:57
 8 9.82e14     9.82e14 1/09/2022 6:18:38 1/09/2022 6:21:06
 9 9.82e14     9.82e14 1/09/2022 6:23:10 1/09/2022 6:25:15
10 9.82e14     9.82e14 1/09/2022 6:27:42 1/09/2022 6:33:09

I need to put my data by sequence in seconds using the start time and end time for each row. So how I can do this? That means 1/09/2022 0:34:15, 1/09/2022 0:34:16, 1/09/2022 0:34:17.......

Also I wanted to have this date and time by one column in sequence order using the start and end time. like this:

1/09/2022 0:34:15,
1/09/2022 0:34:16,
1/09/2022 0:34:17,
1/09/2022 0:34:18,
..........

Answer 1

You can use the below code to give the desired result assuming the data is already ordered by start and end time.

library(tidyverse)

df <- tibble(RFID = c(9.82e14, 9.82e14, 9.82e14, 9.82e14, 9.82e14, 9.82e14, 9.82e14, 9.82e14, 9.82e14, 9.82e14), 
             `Farm Name` = c(9.82e14, 9.82e14, 9.82e14, 9.82e14, 9.82e14, 9.82e14, 9.82e14, 9.82e14, 9.82e14, 9.82e14),
             `Start Time` = c("1/09/2022 0:34:15","1/09/2022 0:47:31","1/09/2022 0:59:33","1/09/2022 1:46:14","1/09/2022 1:58:17","1/09/2022 5:40:06","1/09/2022 6:03:59","1/09/2022 6:18:38","1/09/2022 6:23:10","1/09/2022 6:27:42"),
             `End Time` = c("1/09/2022 0:43:19","1/09/2022 0:53:12","1/09/2022 1:03:01","1/09/2022 1:49:27","1/09/2022 2:08:25","1/09/2022 5:48:05","1/09/2022 6:12:57","1/09/2022 6:21:06","1/09/2022 6:25:15","1/09/2022 6:33:09"))

df <- df |>
  # convert it into dttm
  mutate(`Start Time` = dmy_hms(`Start Time`), `End Time` = dmy_hms(`End Time`)) |> 
  # create a sequence of time in 1 second increment based on the start and end time
  mutate(time_sequence = map2(`Start Time`, `End Time`, ~seq(from = .x, to = .y, by = "1 sec"))) |>  
  # unnest it 
  unnest(time_sequence)