xts conversion fails on 7 line data frame-CodePudding

I am trying to read a csv file and create an xts object. The xts conversion function fails on an 7 line input file. The error is:

Error in xts(Lagoon$Inst, Lagoon$time) : 'order.by' cannot contain 'NA', 'NaN', or 'Inf'

The code is:

require(xts)
> Lagoon <- read.csv("Mara/csv/test/X8.csv")
> Lagoon
                 time Inst
1 00:45:00 03/10/2019 5.76
2 01:00:00 03/10/2019 5.77
3 01:15:00 03/10/2019 5.81
4 01:30:00 03/10/2019 5.61
5 01:45:00 03/10/2019 5.46
6 02:00:00 03/10/2019 5.40
7 02:15:00 03/10/2019 5.38
> Lagoon$time <- as.POSIXlt(Lagoon$time, format = "%H:%M:%S %m/%d/%Y")
> Lagoon
                 time Inst
1 2019-03-10 00:45:00 5.76
2 2019-03-10 01:00:00 5.77
3 2019-03-10 01:15:00 5.81
4 2019-03-10 01:30:00 5.61
5 2019-03-10 01:45:00 5.46
6 2019-03-10 02:00:00 5.40
7 2019-03-10 02:15:00 5.38
> xtsLagoon <- xts(Lagoon$Inst,Lagoon$time)
Error in xts(Lagoon$Inst, Lagoon$time) : 
  'order.by' cannot contain 'NA', 'NaN', or 'Inf'
>

I have tried to slice and dice the real input file into smaller and smaller chunks; the above csv file is a minimum reproducible example. Removing one line of data creates a file that does convert properly into an xts object.

Note that the Lagoon data frame looks OK when I list it before the attempted its conversion (no NA, Nan, or Inf).

My version of xts is the latest (0.12.1)

What am I doing wrong? Is this a bug in the xts conversion function? Any help appreciated!

CodePudding user response：

This could be an issue from the conversion of last two elements (as it returns NA with as.POSIXct - perhaps due to daylight savings). An option is to use parse_date (parsedate) to convert to Datetime object and then use xts

library(parsedate)
Lagoon$time <- parse_date(Lagoon$time)
xts::xts(Lagoon$Inst,Lagoon$time)
                [,1]
2019-03-10 00:45:00 5.76
2019-03-10 01:00:00 5.77
2019-03-10 01:15:00 5.81
2019-03-10 01:30:00 5.61
2019-03-10 01:45:00 5.46
2019-03-10 02:00:00 5.40
2019-03-10 02:15:00 5.38

data

Lagoon <- structure(list(time = c("00:45:00 03/10/2019", "01:00:00 03/10/2019", 
"01:15:00 03/10/2019", "01:30:00 03/10/2019", "01:45:00 03/10/2019", 
"02:00:00 03/10/2019", "02:15:00 03/10/2019"), Inst = c(5.76, 
5.77, 5.81, 5.61, 5.46, 5.4, 5.38)), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7"))

CodePudding user response：

The input file is not shown in the question so we will assume we have the file generated reproducibly in the Note at the end. With that use read.csv.zoo and be sure to specify the time zone as "UTC" because in your local time zone 02:15 does not exist on 03/10/2019 due to the change from standard to daylight savings time.

(Alternately use the code in the question except (1) first set the session's time zone to UTC using Sys.setenv(TZ = "UTC") and (2) use as.POSIXct rather than as.POSIXlt.)

library(xts)

z <- read.csv.zoo("X8.csv", format = "%H:%M:%S %m/%d/%Y", tz = "UTC")
x <- as.xts(z)

Note

Lines <- "time,Inst
00:45:00 03/10/2019,5.76
01:00:00 03/10/2019,5.77
01:15:00 03/10/2019,5.81
01:30:00 03/10/2019,5.61
01:45:00 03/10/2019,5.46
02:00:00 03/10/2019,5.40
02:15:00 03/10/2019,5.38"
cat(Lines, file = "X8.csv")