Adding missing time values

Question

I have a table that is gives me the date-time that I have received data and the count of how much data was received in a thirty minute interval. My problem is some half hour blocks are missing, and I want to insert them into the column and then insert a 0 in the count column.

Here is an example of what the table looks like:

Date-Time           Count
2017-07-13 17:30:00 111

2017-07-13 18:00:00 85

2017-07-13 20:00:00 127

2017-07-13 20:30:00 515

I want it to have 18:30:00 0 and so on

Not sure how to do this if anyone has an idea that would be great.

Here is what I have tried to do:

starttime <- df[1,`Date-Time`]

for (i in df){
  time <- starttime + 30
  new_dt$datetime <- ifelse(df[i] = time, df$datetime, time)
  new_dt$count <- ifelse(df[i] = time, df$count, 0)
}

Answer 1

First let's create some dummy data.

library(tidyverse)
library(lubridate)

time_series <- tibble(
  DateTime = c(
    "2017-07-13 17:30:00",
    "2017-07-13 18:00:00",
    "2017-07-13 20:00:00",
    "2017-07-13 20:30:00"
  ),
  Count = c(111, 85, 127, 515)
) %>%
  mutate(DateTime = ymd_hms(DateTime))

Now let's figure out the smallest and largest datetimes that we have in the data.

from <- min(time_series$DateTime)
to <- max(time_series$DateTime)

Finally, let's create a sequence of dates from from to to at 30 minute intervals. We then join the existing data to that sequence and replace any missing values of Count with zero.

tibble(DateTime = seq(from = from, to = to, by = 1800)) %>%
  left_join(time_series) %>%
  mutate(Count = ifelse(is.na(Count), 0, Count))