CODEWITHSUNDEEP
rdatetime
Achal Neupane
Achal Neupane

Reputation: 5719

Time data manipulation

I have my time and date as 

tt <- structure(list(Date = structure(c(1L, 1L, 1L, 1L, 3L, 3L), .Label = c("2018-10-27", 
"2018-10-28", "2018-11-15"), class = "factor"), Time = c("21:07:30.004", 
"21:07:31.000", "21:07:32.998", "21:07:32.000", "21:07:33.989", 
"21:08:33.989")), row.names = c(NA, 6L), class = "data.frame")

I would like to get the smallest to largest time range for each date as:

        Date                 Time
2018-10-27 21:07:30 to 21:07:32
2018-11-15 21:07:33 to 21:08:33

Code I tried:

time.range <- function(field_or_seed){
  paste0(gsub(".* ", "", range(strptime(field_or_seed$Time, format = "%H:%M:%S", tz = 'UTC'))[[1]]), " to ",
         gsub(".* ", "", range(strptime(field_or_seed$Time, format = "%H:%M:%S", tz = 'UTC'))[[2]]))
}


aggregate(Time~Date, data = tt, FUN = time.range)

I would really appreciate any suggestion I could use to fix this. Thanks!

Upvotes: 1

Views: 104

Answers (3)

alistaire
alistaire

Reputation: 43354

A more robust approach is to use appropriate objects, e.g. Date and hms:

library(tidyverse)

tt <- tibble(
    Date = c("2018-10-27", "2018-10-27", "2018-10-27", "2018-10-27", "2018-11-15", "2018-11-15"),
    Time = c("21:07:30.004", "21:07:31.000", "21:07:32.998", "21:07:32.000", "21:07:33.989", "21:08:33.989")
)

tt %>% 
    mutate(Date = as.Date(Date),
           Time = hms::as_hms(Time)) %>%
    group_by(Date) %>% 
    summarise(start = Time[which.min(Time)],    # which.min instead of min to avoid dropping class
              end = Time[which.max(Time)])
#> # A tibble: 2 x 3
#>   Date       start        end         
#>   <date>     <time>       <time>      
#> 1 2018-10-27 21:07:30.004 21:07:32.998
#> 2 2018-11-15 21:07:33.989 21:08:33.989

If you like you could use lubridate::interval to represent the interval, but you'd have to use POSIXct.

Upvotes: 2

utubun
utubun

Reputation: 4505

Also:

aggregate(
  data.frame(Time = strptime(do.call(paste, tt), '%F %R:%OS', tz = 'GMT')), 
  by = list(Date = tt$Date), 
  function(Time){
    paste(
      format(min(Time), '%T'), 
      format(max(Time), '%T'),
      sep = ' to '
      )
    }
  )

#        Date                 Time
#1 2018-10-27 21:07:30 to 21:07:32
#2 2018-11-15 21:07:33 to 21:08:33

Upvotes: 0

Pablo Rod
Pablo Rod

Reputation: 669

Try:

   tt$date_time <- as.POSIXct(paste(tt$Date, tt$Time))

do.call(rbind, by(
  tt,
  tt$Date,
  FUN = function(x) {
    within(x, Time <-
             paste(strftime(min(date_time), "%H:%M:%S"), 
                   "to", 
                   strftime(max(date_time), "%H:%M:%S")))[1, -3]
  }
))

Upvotes: 1

Related Questions