Error in xts, as.POSIXct "'order.by' cannot contain 'NA', 'NaN', or 'Inf'"

Question

I have a CSV file with a date column and a price column.

I am trying to convert it to xts format so that I can eventually use the aggregatets function. However when I try to run the following code:

mydata_xts=xts(mydata, order.by=as.POSIXct(mydata$Date, format="%d%/m/%y %H:%M"))

I receive this error:

Error in xts(mydata, order.by = as.POSIXct(mydata$Date, format = "%d%/m/%y %H:%M"))

'order.by' cannot contain 'NA', 'NaN', or 'Inf'

I am sure my data does not have any missing or dud values. What I suspect to be the problem is the way Excel formats time.

Attached is some information about my data.

  head(mydata)
            Date Price
1 1/03/2018 0:30 62.07
2 1/03/2018 1:00 60.35
3 1/03/2018 1:30 57.16
4 1/03/2018 2:00 55.88
5 1/03/2018 2:30 58.19
6 1/03/2018 3:00 60.63

summary(mydata)
                  Date          Price       
     1/03/2018 0:30 :   1   Min.   : 14.46  
     1/03/2018 1:00 :   1   1st Qu.: 61.98  
     1/03/2018 1:30 :   1   Median : 63.77  
     1/03/2018 10:00:   1   Mean   : 66.70  
     1/03/2018 10:30:   1   3rd Qu.: 69.86  
     1/03/2018 11:00:   1   Max.   :136.21  
     (Other)        :1481

Here is my Excel/csv file: https://imgur.com/a/QofdEZl

Prasanna S Prasanna S · Accepted Answer · 2018-12-11T05:30:50

Here are some lines of code with randomly generated data to help you. Just replace "db0" with your data imported from Excel (start from scratch and try to remove the Date column this time)

library(xts) 
set.seed(97531) 
db0 <- as.data.frame(matrix(rnorm(1*1000, mean=5, sd=2), ncol=1)) 
date <- seq(from=as.POSIXct("2011-03-12 0:30", format = "%Y-%m-%d %H:%M"), length.out = nrow(db0), by = "30 min") 
dbt <- xts(db0, date) 
# index(dbt) 
apply.weekly(dbt, FUN = "sum")

Error in xts, as.POSIXct "'order.by' cannot contain 'NA', 'NaN', or 'Inf'"

1 Answers