Convert HH:MM:SS column to ZT time

Question

I am hoping somebody can help me with a logic question in R-studio. I have a rather large data set, with "Time" as one of my columns. This column has values from 00:00:00 to 23:59:00, and is in HH:MM:SS format.

Because of some trouble I have had with analysis of time in this format, I am trying to create a new column, called "ZT" where I convert this time column to ZT time. Lights turn on at 7am, so need the time 07:00:00 to correspond to ZT=0, with 07:01:00 to correspond to ZT=0.016... and so on and so forth.

Can anybody help me with this? It would be much appreciated!

It's easier to help you if you include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. — MrFlick, Oct 27 '20 at 22:20

score 0 · Answer 1 · answered Oct 28 '20 at 04:17

Not sure if this is what you are going for or not but this seems to work at converting a character vector of times in the format HH:MM:SS to your ZT time in the format HH:MM:SS starting at 7am as 00:00:00.

I am unclear exactly what you mean when you state that 07:01:00 should correspond to ZT=0.016, but maybe this can be a start.

Fair warning this is a little slow (took about 1 minute on my machine) but maybe someone else can help vectorize it and speed it up:

#Make Some Fake Data
df<-data.frame(Time=format(seq(ISOdate(2020,1,1), ISOdate(2020,2,1), by = "min"), '%H:%M:00'), Variable1=runif(n=44641))

#We need the help of a an external package to handle time in HH:MM:SS format
library(lubridate)

time_store<- hms(df$Time) #Convert your times to HMS format
ZT_vec<-vector() #Create an empty vector that we will fill in
for (i in 1:length(time_store)){ #iterate over each observation
  if (hour(time_store[i])<7){ #Make sure the conversions are going the right direction
    ZT<-time_store[i]+hours(17)
    ZT_vec<-c(ZT_vec,sprintf("%02d:%02d:%02d", hour(ZT), minute(ZT), second(ZT))) #format the times in HH:MM:SS
  } else {
    ZT<-time_store[i]-hours(7)
    ZT_vec<-c(ZT_vec,sprintf("%02d:%02d:%02d", hour(ZT), minute(ZT), second(ZT)))
  }
}

df<-cbind(df,ZT_vec) #Bind on our new column

head(df)
    Time Variable1   ZT_vec
12:00:00 0.6560604 05:00:00
12:01:00 0.3485023 05:01:00
12:02:00 0.8396784 05:02:00
12:03:00 0.4773929 05:03:00
12:04:00 0.6969242 05:04:00
12:05:00 0.5371502 05:05:00

head(df[4020:4025,])

    Time Variable1   ZT_vec
06:59:00 0.6758364 23:59:00
07:00:00 0.1255861 00:00:00
07:01:00 0.2789485 00:01:00
07:02:00 0.2175933 00:02:00
07:03:00 0.1855100 00:03:00
07:04:00 0.1632865 00:04:00

Convert HH:MM:SS column to ZT time

1 Answers1