Insert rows for missing dates/times

by

This is an old question, but I just wanted to post a dplyr way of handling this, as I came across this post while searching for an answer to a similar problem. I find it more intuitive and easier on the eyes than the zoo approach.

library(dplyr)

ts <- seq.POSIXt(as.POSIXct("2001-09-01 0:00",'%m/%d/%y %H:%M'), as.POSIXct("2001-09-01 0:07",'%m/%d/%y %H:%M'), by="min")

ts <- seq.POSIXt(as.POSIXlt("2001-09-01 0:00"), as.POSIXlt("2001-09-01 0:07"), by="min")
ts <- format.POSIXct(ts,'%m/%d/%y %H:%M')

df <- data.frame(timestamp=ts)

data_with_missing_times <- full_join(df,original_data)

   timestamp     tr tt sr st
1 09/01/01 00:00 15 15 78 42
2 09/01/01 00:01 20 64 98 87
3 09/01/01 00:02 31 84 23 35
4 09/01/01 00:03 21 63 54 20
5 09/01/01 00:04 15 23 36 15
6 09/01/01 00:05 NA NA NA NA
7 09/01/01 00:06 NA NA NA NA
8 09/01/01 00:07 NA NA NA NA

Also using dplyr, this makes it easier to do something like change all those missing values to something else, which came in handy for me when plotting in ggplot.

data_with_missing_times %>% group_by(timestamp) %>% mutate_each(funs(ifelse(is.na(.),0,.)))

   timestamp     tr tt sr st
1 09/01/01 00:00 15 15 78 42
2 09/01/01 00:01 20 64 98 87
3 09/01/01 00:02 31 84 23 35
4 09/01/01 00:03 21 63 54 20
5 09/01/01 00:04 15 23 36 15
6 09/01/01 00:05  0  0  0  0
7 09/01/01 00:06  0  0  0  0
8 09/01/01 00:07  0  0  0  0

More Related Contents:

  1. Include levels of zero count in result of table()
  2. Replace NA with previous or next value, by group, using dplyr
  3. Basic lag in R vector/dataframe
  4. Replace NA in column with value in adjacent column
  5. Replace missing values with column mean
  6. Coalesce two string columns with alternating missing values to one
  7. How to parse milliseconds?
  8. R tick data : merging date and time into a single object
  9. How to perform join over date ranges using data.table?
  10. Reading multiple files and calculating mean based on user input
  11. Converting a data frame to xts
  12. Forecasting time series data
  13. How to make gradient color filled timeseries plot in R
  14. Transforming a time-series into a data frame and back
  15. Randomly insert NAs into dataframe proportionaly
  16. Return data subset time frames within another timeframes?
  17. Convert NA into a factor level
  18. Analyzing Daily/Weekly data using ts in R
  19. Creating regular 15-minute time-series from irregular time-series
  20. Subset xts object by time of day
  21. Filling missing dates in a grouped time series – a tidyverse-way?
  22. starting a daily time series in R
  23. Fill NA in a time series only to a limited number
  24. rolling joins data.table in R
  25. Select NA in a data.table in R
  26. Replace -inf, NaN and NA values with zero in a dataset in R
  27. R: merge two irregular time series
  28. simple examples of filter function, recursive option specifically
  29. optimized rolling functions on irregular time series with time-based window
  30. Interpolating timeseries

Leave a Comment