Populating missing Date and Time in time-series data in R, with zoo package

Question

I have a quarter-hour (15 min interval) frequency data.

sasan<-read.csv("sasanhz.csv", header = TRUE)

head(sasan)
               Timestamp Avg.Hz
1 12/27/2017 12:15:00 AM  50.05
2 12/27/2017 12:30:00 AM  49.99
3 12/27/2017 12:45:00 AM  49.98
4 12/27/2017 01:00:00 AM  50.01
5 12/27/2017 01:15:00 AM  49.97
6 12/27/2017 01:30:00 AM  49.98

str(sasan)
'data.frame':   5501 obs. of  2 variables:
 $ Timestamp: Factor w/ 5501 levels "01/01/2018 00:00:00 AM",..: 5112 5114 5116 5023 5025 
                                 5027 5029 5031 5033 5035 ...
 $ Avg.Hz   : num  50 50 50 50 50 ...

 #change to posixct

sasan$Timestamp<-as.POSIXct(sasan$Timestamp, format="%m/%d/%Y %I:%M:%S %p")

Here in this time-series I have some missing data-time in the column "Timestamp" I want to impute the missing date-time.

I have tried with zoo:

    z<-zoo(sasan)
    > head(z[1489:1497])
     Timestamp           Avg.Hz
1489 2018-01-11 12:15:00 50.02 
1490 2018-01-11 12:30:00 49.99 
1491 2018-01-11 12:45:00 49.94 
1492 <NA>                49.98 
1493 <NA>                50.02 
1494 <NA>                49.95

While imputing NA value of dates and time with "na.locf" function in zoo package I am getting following error.

 sasan_mis<-seq(start(z), end(z), by = times("00:15:00"))
> na.locf(z, xout = sasan_mis)
Error in approx(x[!na], y[!na], xout, ...) : zero non-NA points
In addition: Warning message:
In xy.coords(x, y, setLab = FALSE) : NAs introduced by coercion

How to overcome this error? How can I impute this missing date-time? Appreciate your suggestion.

dput(head(z))
structure(c("2017-12-27 00:15:00", "2017-12-27 00:30:00", "2017-12-27 00:45:00", 
"2017-12-27 01:00:00", "2017-12-27 01:15:00", "2017-12-27 01:30:00", 
"50.05", "49.99", "49.98", "50.01", "49.97", "49.98"), .Dim = c(6L, 
2L), .Dimnames = list(NULL, c("Timestamp", "Avg.Hz")), index = 1:6, class = "zoo")

The library package I have used are

library(ggplot2)
library(forecast)
library(tseries)
library(xts)
library(zoo)
library(dplyr)

zoo doesn't have a capital Z. And I don't think the error is coming from the na.locf call, but rather is a consequence of the seq call on an object retruned by start. Where are you getting start, stop and times functions? Need complete code (which includes code to load all needed packages). Start with a clean session and then show what has been done and the sessionInfo() output for debugging errors. — IRTFM
– IRTFM, Commented Mar 24, 2018 at 3:38
Post output of dput(head(z)). I'm guessing there's a problem with start and stop not working with zoo objects that have non-datatime index values. Your zoo-object has integer indexes. — IRTFM
– IRTFM, Commented Mar 24, 2018 at 3:49
You need to fix this up before reading it into zoo. Once you do that you can use read.zoo(my_data_frame) or zoo(data, index) to read it into zoo. — G. Grothendieck
– G. Grothendieck, Commented Mar 24, 2018 at 11:31

MKR · Accepted Answer · 2018-03-24 07:59:51Z

2

Assuming that OP have got missing values of Timestamp variables in data and looking for a way to populate it.

na.approx from zoo package comes very handy in such cases.

# na.approx from zoo to populate missing values of Timestamp
sasan$Timestamp <- as.POSIXct(na.approx(sasan$Timestamp), origin = "1970-1-1")
sasan
# 1  2017-12-27 00:15:00  50.05
# 2  2017-12-27 00:30:00  49.99
# 3  2017-12-27 00:45:00  49.98
# 4  2017-12-27 01:00:00  50.01
# 5  2017-12-27 01:15:00  49.97
# 6  2017-12-27 01:30:00  49.98
# 7  2017-12-27 01:45:00  49.98
# 8  2017-12-27 02:00:00  50.02
# 9  2017-12-27 02:15:00  49.95
# 10 2017-12-27 02:30:00  49.98

Data

# OP's data has been slightly modified to include NAs
sasan <- read.table(text = 
"Timestamp           Avg.Hz
1 '12/27/2017 12:15:00 AM'  50.05
2 '12/27/2017 12:30:00 AM'  49.99
3 '12/27/2017 12:45:00 AM'  49.98
4 '12/27/2017 01:00:00 AM'  50.01
5 '12/27/2017 01:15:00 AM'  49.97
6 '12/27/2017 01:30:00 AM'  49.98
7 <NA>                      49.98 
8 <NA>                      50.02 
9 <NA>                      49.95
10 '12/27/2017 02:30:00 AM'  49.98", 
header = TRUE, stringsAsFactors = FALSE)

# convert to POSIXct 
sasan$Timestamp<-as.POSIXct(sasan$Timestamp, format="%m/%d/%Y %I:%M:%S %p")

answered Mar 24, 2018 at 7:59

MKR

20.2k4 gold badges26 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MKR Over a year ago

@AvijitMallick Have a look at answer. Let me know if doesnt meet your requirement. Also, you can accept answer by clicking on tick symbol in left of answer box so that it will be helpful to future users. You can have a look at stackoverflow.com/help/someone-answers .

Avijit Mallick Over a year ago

Thank you for your support.

Collectives™ on Stack Overflow

Populating missing Date and Time in time-series data in R, with zoo package

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related