How can I exclude file rows that are non numerical for use by XTS in R?

Question

I have a reactive dataframe that reads a file that updates with new data. Occasionally the data in the file will have error messages or other non numeric lines and will break the order.by XTS function.

timestamp	siteid	value1	value2	value3	value4
1666800000	144496	58.933	53.7	5.094	5.141
1666782000	144496	62.575	49.1	0.635	0.754
1666749600	144496	58.567	58.733	3.843	2.438
<"html>
<"sometext>
1666760400	144496	71.959	51.872	0.037	0.041

server <- function(input, output, session) {
  ez.read = function(file, ..., skip.rows=NULL, tolower=FALSE){
    if (!is.null(skip.rows)) {
      tmp = readLines(file)
      tmp = tmp[-(skip.rows)]
      tmpFile = tempfile()
      on.exit(unlink(tmpFile))
      writeLines(tmp,tmpFile)
      file = tmpFile
    }
    result = read.csv(file, ...)
    if (tolower) names(result) = tolower(names(result))
    return(result)
  }
  
  data <- reactivePoll(1000 * 60 * 15, session,
                       checkFunc = function() { file.info("merged.csv")$mtime}, 
                       valueFunc = function() {
                         data <- ez.read("merged.csv", tolower = T)
                         data$time_stamp <- as_datetime(data$time_stamp)
                         
})  
  
  renderTable(data())

n51921_cfa <- xts(x = data()$value1, order.by = data()$date)

Warning: Error in xts: 'order.by' cannot contain 'NA', 'NaN', or 'Inf'

How do I ignore these rows?

score 0 · Answer 1 · answered Oct 26 '22 at 21:08

Attempting to convert the garbage text data to a numeric type will yield NA, then simply ignore any entries that are NA upon conversion...

idx <- (as.integer(data$timestamp) |> is.na() == FALSE) |> which()
n51921_cfa <- xts(x = data[ idx, 'value1' ], order.by = data[ idx, 'timestamp' ])

How can I exclude file rows that are non numerical for use by XTS in R?

1 Answers1