CODEWITHSUNDEEP
rdatetimeformattingtime-series
user12282058
user12282058

Reputation: 11

How to read 'hms' time format beyond 24-hours?

I am doing a time series where activity measurements were taken in 1-minute intervals over 48 hours. I am using the variable "Time Elapsed" as my x-value. In Excel I had it formatted as time up to the 48 hours, but when importing it into R anything over 24 hours was registered as N/A As a result, my graphs stop at 24 hours.

Entering str(data) has given me this:

$ TimeElapsed: 'hms' num  00:01:30 00:02:30 00:03:30 00:04:30 ...
  ..- attr(*, "units")= chr "secs"`

The formatting is correct < 24 hours, and then after that becomes NA.

On someone's advice, I tried linking it to the RunDate:

z1 <- format(data$RunDate,"%Y-%m-%d")
z2 <- format(data$TimeElapsed, "%H:%M:%S")
## RunDate and TimeElapsed combined:
z3 <- as.POSIXct(paste(z1,z2,sep = " "), format = "%Y-%m-%d %H:%M:%S")
start_date <- "2019-06-11"
start_time <- "14:00:00"
start_dt <- as.POSIXct(paste(start_date,start_time,sep = " "), 
                       format = "%Y-%m-%d %H:%M:%S")

But only got this error message:

Error in format.default(data$RunDate, "%Y-%m-%d"): invalid 'trim' argument

Some example data of mine would be:

ID       TimeElapsed   RunDate       Activity
Zx8      47:52:30      15-Jun-19      30
Zx8      47:53:30      15-Jun-19      0  
Zx8      47:54:30      15-Jun-19      6
Zx12     47:55:30      15-Jun-19      23
Zx12     47:56:30      15-Jun-19      14
Zx12     47:57:30      15-Jun-19      0

How do I get R to read the time format beyond 24-hours?

Upvotes: 1

Views: 961

Answers (1)

Ben Bolker
Ben Bolker

Reputation: 226087

If you just need a correct number (i.e. you're not trying to do fancy downstream processing/plotting with a H:M:S format), you can use a simple recipe based on strsplit() to convert the H:M:S format to hours:

dd <- read.table(header=TRUE,stringsAsFactors=FALSE, text="
ID       TimeElapsed   RunDate       Activity
Zx8      47:52:30      15-Jun-19      30
Zx8      47:53:30      15-Jun-19      0  
Zx8      47:54:30      15-Jun-19      6
Zx12     47:55:30      15-Jun-19      23
Zx12     47:56:30      15-Jun-19      14
Zx12     47:57:30      15-Jun-19      0
")

dd$NumTime <- sapply(strsplit(dd$TimeElapsed,":"),
        function(x) sum(as.numeric(x)*c(1,1/60,1/3600)))

(Converting to seconds instead of hours should be straightforward if you prefer that.)

When plotting, the simplest thing would be to specify tick labels manually (e.g. ticklabels "0:00", "24:00", "48:00"). If using ggplot2 you could manually define your own transformed axis (with ?scales::trans_new, using an identity transformation/inverse transformation and specifying an appropriate function for format).

Upvotes: 2

Related Questions