CODEWITHSUNDEEP
rdatetimedatetime-formatdifference
ACLAN
ACLAN

Reputation: 401

Calculate time difference in %H:%M:%S format in R

I'm trying to calculate the time difference between 2 columns for rows in which a certain criteria is fulfilled. 

dates1 <- c("1899-12-31 12:20:00 PMT", "1899-12-31 15:30:00 PMT", "1899-12-31 13:20:00 PMT", "1899-12-31 11:50:00 PMT",
             NA)   

dates2 <- c("1899-12-31 11:13:00 PMT", "1899-12-31 11:41:00 PMT", "1899-12-31 14:04:00 PMT", "1899-12-31 13:03:00 PMT", 
            "1899-12-31 13:18:00 PMT")

site <- c(15, 16, 18, 18,
          15)

DS <- as.data.frame(cbind(site, dates1 , dates2))

## convert to POSIXct format
DS[, 2:3] <- lapply(DS[, 2:3], function(x) as.POSIXct(strptime(x,"%Y-%m-%d %H:%M:%S ",tz="")))

## create a new columns with the time difference between dates1 and dates2 in %H:%M:%S format if site=18 , else take value from column dates1
DS$output <- ifelse(DS$site ==18, 
                      difftime(DS$dates1, DS$dates2, units = "mins"),
                      DS$dates1) 

## using the code above doesnt work because:
#1- using the difftime function I can't choose the "%H:%M:%S" for the output
#2- for sites != 18, the dates1 lose its "%H:%M:%S format


# I have also tried to use:
DS$output <- ifelse(DS$site ==18, 
                      difftime(DS$dates1, DS$dates2, units = "mins"),
                      0) 
## and then convert the difference in minutes to %H:%M:%S format using
DS$output<- as.difftime(DS$output , format = "%H:%M:%S", units = "mins")

## but it doesnt work.

## the output should be something like:

output<- c("12:20:00", "15:30:00","00:44:00", "01:13:00", NA)

DS.out <- cbind(DS, output)

> DS.out 
  site                  dates1                  dates2   output
1   15 1899-12-31 12:20:00 PMT 1899-12-31 11:13:00 PMT 12:20:00
2   16 1899-12-31 15:30:00 PMT 1899-12-31 11:41:00 PMT 15:30:00
3   18 1899-12-31 13:20:00 PMT 1899-12-31 14:04:00 PMT 00:44:00
4   18 1899-12-31 11:50:00 PMT 1899-12-31 13:03:00 PMT 01:13:00
5   15                    <NA> 1899-12-31 13:18:00 PMT     <NA>

    #Where output is the time difference calculated for rows 3 and 4 (site=18) 
#or a copy of the time from dates 1 for the other rows (with sites different from 18).

Is there anyway to calculate time difference in H%:%M:%S format using a different function?

Upvotes: 4

Views: 4691

Answers (4)

Karol Daniluk
Karol Daniluk

Reputation: 544

You could use hms library.

require(hms)
x <- data.frame(TIME_SPENT = as.difftime(runif(10) * 10, format = 'H', units = 'hours'))
x$TIME_SPENT_HMS <- hms(hours = as.numeric(x$TIME_SPENT))

> x
      TIME_SPENT  TIME_SPENT_HMS
1  0.670335 hours 00:40:13.206023
2  6.740590 hours 06:44:26.122495
3  9.433242 hours 09:25:59.670743
4  9.350243 hours 09:21:00.873019
5  1.459504 hours 01:27:34.214544
6  4.820711 hours 04:49:14.560171
7  5.052186 hours 05:03:07.870653
8  9.415136 hours 09:24:54.489527
9  4.717802 hours 04:43:04.086949
10 4.131969 hours 04:07:55.087836

It supports objects of class difftime (but units are always seconds) and stores the values in HH:MM:SS format.

> class(x$TIME_SPENT_HMS)
[1] "hms"      "difftime"

You can perform all sort of calculations on it.

> x$TIME_SPENT_HMS[1] + 10
Time difference of 2423.206 secs

Values displayed in table view are also represented with semicolons:

enter image description here

Upvotes: 2

jay.sf
jay.sf

Reputation: 72593

You could use difftime on hours; floor and %% 1 of this give hours and minutes to be pasted together. Some rounding needed, though.

DS$output[DS$site == 18] <- {
  tmp <- as.numeric(round(with(DS[DS$site == 18, ], difftime(dates2, dates1, units="hours")), 2))
  paste(sprintf("%02d", floor(tmp)), round(tmp %% 1 * 60), "00", sep=":")
}
DS$output[site != 18] <- strftime(DS$dates1[DS$site != 18], "%T")
DS
#   site              dates1              dates2   output
# 1   15 1899-12-31 12:20:00 1899-12-31 11:13:00 12:20:00
# 2   16 1899-12-31 15:30:00 1899-12-31 11:41:00 15:30:00
# 3   18 1899-12-31 13:20:00 1899-12-31 14:04:00 00:44:00
# 4   18 1899-12-31 11:50:00 1899-12-31 13:03:00 01:13:00
# 5   15                <NA> 1899-12-31 13:18:00     <NA>

Upvotes: 0

HMSP
HMSP

Reputation: 162

You can use "times" format from chron library.

(this is not very straightforward but it seems to work)

# get the difference in minutes
DS$Time1NEW <- ifelse(DS$site ==18, 
                      difftime(DS$dates1, DS$dates2, units = "mins"),
                      NA) 

# get the absolute value
DS$Time1NEW<-abs(DS$Time1NEW)

# conver to hours:min:sec using "times" from library chron
DS$hours_minutes<-times((DS$Time1NEW%/%60 +  DS$Time1NEW%%60 /60)/24)

# convert the others (not in site 18)
DS$no18<-times(paste0(hours(DS$dates1),':', minutes(DS$dates1),':', seconds(DS$dates1)))

# create final data column
DS$final<-DS$hours_minutes

# now substitute when necessary
wh<-which(is.na(DS$final))
DS$final[wh]<-DS$no18[wh]

And this what you get:

> head(DS)
  site                  dates1                  dates2 Time1NEW hours_minutes     no18    final
1   15 1899-12-31 12:20:00 PMT 1899-12-31 11:13:00 PMT       NA          <NA> 12:20:00 12:20:00
2   16 1899-12-31 15:30:00 PMT 1899-12-31 11:41:00 PMT       NA          <NA> 15:30:00 15:30:00
3   18 1899-12-31 13:20:00 PMT 1899-12-31 14:04:00 PMT       44      00:44:00 13:20:00 00:44:00
4   18 1899-12-31 11:50:00 PMT 1899-12-31 13:03:00 PMT       73      01:13:00 11:50:00 01:13:00
5   15                    <NA> 1899-12-31 13:18:00 PMT       NA          <NA>     <NA>     <NA>

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 388807

Would something like this work ?

with(DS, ifelse(site == 18, as.character(lubridate::seconds_to_period(
                difftime(dates2, dates1, units = "secs"))), 
                format(dates1, "%T")))

#[1] "12:20:00"  "15:30:00"  "44M 0S"    "1H 13M 0S" NA

Upvotes: 0

Related Questions