Subtract time of the day (H:M form) in columns

Question

Here is subset of my original data I am working with:

dput(datumi)
structure(c("21:26", "21:33", "21:38", "23:02", "23:03", "21:27", 
"21:34", "21:39", "23:03", "23:04", "21:26", "21:33", "21:38", 
"23:02", "23:04", "21:26", "21:34", "21:38", "23:02", "23:04", 
"21:27", "21:34", "21:39", "23:02", "23:04"), .Dim = c(5L, 5L
), .Dimnames = list(c("2", "3", "4", "5", "6"), c("Datum_1", 
"Datum_2", "Datum_3", "Datum_4", "Datum_5")))

So I am working with time, where e.g., 21:26 means time of the day.

Now I would like to subtract second column from first one and third from second and so on, this means that I would subtract column Datum_2 from Datum_1 and column Datum_3 from Datum_2 and Datum_4 from Datum_3. And my output will be new columns with differences in seconds

I've already created function/loop that does this if my data would be numeric, so for example in case of numeric data I would do this and get the desired output:

dat <- data.frame(
  column1 =  round(runif(n = 10, min=0, max=5),0),
  column2 = round(runif(n = 10, min=0, max=5),0),
  column3 = round(runif(n = 10, min=0, max=5),0),
  column4 = round(runif(n = 10, min=0, max=5),0)
  )

results <- list()
for(i in 1:length(dat)) {
  if (i==length(dat)){
    results[[i]] <-dat[,i]
  } else {results[[i]] <-dat[,i+1] - dat[,i]}
}

results <- t(do.call(rbind,results))
results <- data.frame(results)

But I cannot figure it out for time format and I have tried strptime and as.POSIXct

x1 <- strptime(datumi, "%H:%M")
as.numeric(x1,units="secs")

and

 as.POSIXct(datumi,format="%H:%M")

And also looked at this

Subtract time in r

Subtracting Two Columns Consisting of Both Date and Time in R

convert character to time in R

score 2 · Accepted Answer · edited Sep 14 '17 at 21:55

Here is one solution based on the answer given in R: Convert hours:minutes:seconds.

datumi
#   Datum_1 Datum_2 Datum_3 Datum_4 Datum_5
# 2 "21:26" "21:27" "21:26" "21:26" "21:27"
# 3 "21:33" "21:34" "21:33" "21:34" "21:34"
# 4 "21:38" "21:39" "21:38" "21:38" "21:39"
# 5 "23:02" "23:03" "23:02" "23:02" "23:02"
# 6 "23:03" "23:04" "23:04" "23:04" "23:04"

makeTime <- function(x) as.POSIXct(paste(Sys.Date(), x))
dat <- apply(datumi, 2, makeTime)
mapply(x = 2:ncol(dat), 
       y = 1:(ncol(dat) -1), 
       function(x, y) dat[ , x] - dat[ , y])
#      [,1] [,2] [,3] [,4]
# [1,]   60  -60    0   60
# [2,]   60  -60   60    0
# [3,]   60  -60    0   60
# [4,]   60  -60    0    0
# [5,]   60    0    0    0

You can also use as.POSIXct without pasting the current data with the 'format' argument:

makeTime <- function(x) as.POSIXct(x, format = "%H:%M")

Note, the result is the same because as.POSIXct assumes the current date when none is given.

score 1 · Answer 2 · answered Jul 22 '16 at 16:05

One way you could also do it if you wanted to have column names in addition to your original data would be to do:

df<-as.data.frame(lapply(dat,strptime,format="%H:%M"))
lapply(1:4, function(i) df[,paste0("diff",i,"_",i+1)] <<- difftime(df[,i],df[,i+1],units=c("secs")))

df
              Datum_1             Datum_2             Datum_3             Datum_4             Datum_5  diff1_2 diff2_3  diff3_4
2 2016-07-22 21:26:00 2016-07-22 21:27:00 2016-07-22 21:26:00 2016-07-22 21:26:00 2016-07-22 21:27:00 -60 secs 60 secs   0 secs
3 2016-07-22 21:33:00 2016-07-22 21:34:00 2016-07-22 21:33:00 2016-07-22 21:34:00 2016-07-22 21:34:00 -60 secs 60 secs -60 secs
4 2016-07-22 21:38:00 2016-07-22 21:39:00 2016-07-22 21:38:00 2016-07-22 21:38:00 2016-07-22 21:39:00 -60 secs 60 secs   0 secs
5 2016-07-22 23:02:00 2016-07-22 23:03:00 2016-07-22 23:02:00 2016-07-22 23:02:00 2016-07-22 23:02:00 -60 secs 60 secs   0 secs
6 2016-07-22 23:03:00 2016-07-22 23:04:00 2016-07-22 23:04:00 2016-07-22 23:04:00 2016-07-22 23:04:00 -60 secs  0 secs   0 secs
   diff4_5
2 -60 secs
3   0 secs
4 -60 secs
5   0 secs
6   0 secs

score 0 · Answer 3 · answered Jul 22 '16 at 16:18

I've found solution to my problem including function/loop that I've created for numeric data, I just needed to include

difftime(strptime(datumi[,i+1], format = "%H:%M"), strptime(datumi[,i], format = "%H:%M"), units = "secs") in my for loop function so code looks like this

datumi <- as.data.frame(datumi)
results <- list()
for(i in 1:length(dat)) {
  if (i==length(dat)){
    results[[i]] <-NULL
  } else {results[[i]] <-difftime(strptime(datumi[,i+1], format = "%H:%M"), strptime(datumi[,1], format = "%H:%M"), units = "secs")              }
}

results <- t(do.call(rbind,results))
results <- data.frame(results)

#And output
  X1 X2 X3 X4
2 60  0  0 60
3 60  0 60 60
4 60  0  0 60
5 60  0  0  0
6 60 60 60 60

But because mapply used by @dayne is more convenient for me (because it applys function to multiple list arguments and is more readable for me) I used his solution.

Subtract time of the day (H:M form) in columns

3 Answers3