Calculate Avg Time in R

Question

My Dataframe called copy1:

    copy1
Source: local data frame [4 x 4]
Groups: GM [2]

      GM Avg.Start.Time Avg.Close.Time Avg.Last.Task.Duration
  (fctr)         (fctr)         (fctr)                  (int)
1   ED          13:15          16:16                    181
2   ED          16:12          17:44                     92
3   LD          15:32          17:27                    115
4   LD          14:38          17:11                    153

I want to calculate Avg.Close.Time Per GM

I have tried:

copy1$Avg.Start.Time <-strptime(copy1$Avg.Start.Time, "%H:%M")
copy1%>%group_by(GM)%>%
        summarise(mean(copy1$Avg.Start.Time,na.rm=T))

But get this:

Error: column 'Avg.Start.Time' has unsupported type : POSIXlt, POSIXt

I have also tried using lubridate:

copy1$Avg.Start.Time <- hm(copy1$Avg.Start.Time)

mean(copy1$Avg.Start.Time,na.rm = T)

But get "0"

Any ideas how can I calculate Avg.Start.Time Per GM?

aichao · Accepted Answer · 2016-08-24T14:44:03.823

2

You can use as.POSIXct to do the conversion, the result of which can be used for mean:

result <- copy1%>%group_by(GM)%>%
  summarise(mean(as.POSIXct(Avg.Start.Time, format="%M:%S"),na.rm=T))

However, this will add the current date to the time:

print(result)
## A tibble: 2 x 2
##      GM mean(as.POSIXct(copy1$Avg.Start.Time,...
##  <fctr>                                   <time>
##1     ED                      2016-08-24 00:14:54
##2     LD                      2016-08-24 00:15:05

As pointed out by the OP, we can format the result to remove the date:

result <- copy1%>%group_by(GM)%>%
  summarise(Avg.Start.Time=format(mean(as.POSIXct(Avg.Start.Time, format="%M:%S"),na.rm=T), format="%M:%S"))
## A tibble: 2 x 2
##      GM Avg.Start.Time
##  <fctr>          <chr>
##1     ED          14:43
##2     LD          15:05

edited Aug 24 '16 at 14:44

answered Aug 24 '16 at 12:30

aichao

7,375
3
16
18

thanks for it is not a valid answer. both times are returned the same – Shery Aug 24 '16 at 12:33
@Shery: sorry, just a typo using `copy1$Avg.Start.Time` in `summarise` instead of just `Avg.Start.Time`. In any case, the other answer is much better since it strips the date. – aichao Aug 24 '16 at 12:38
you can format the time using `format(format = "%H:%M")` at the end as well – Shery Aug 24 '16 at 14:35

Ronak Shah · Answer 2 · 2016-08-24T12:36:39.563

You need to first convert the column to time format by,

copy1$Avg.Start.Time <- as.POSIXct(copy1$Avg.Start.Time, format = "%H:%M")

You can then use aggregate from base R to get mean for every GM

aggregate(Avg.Start.Time~GM, copy1, mean)

#  GM      Avg.Start.Time
#1 ED 2016-08-24 14:43:30
#2 LD 2016-08-24 15:05:00

If you want it in HH:MM format you can wrap it up inside format

aggregate(Avg.Start.Time~GM, copy1, function(x) format(mean(x),format = "%H:%M"))

#  GM Avg.Start.Time
#1 ED          14:43
#2 LD          15:05

score 2 · Answer 3 · answered Aug 24 '16 at 13:20

2

We can use data.table

library(data.table)
setDT(copy1)[,.(Avg.Start.Time = mean(as.POSIXct(Avg.Start.Time, format = "%M:%S")))  , GM]

answered Aug 24 '16 at 13:20

akrun

874,273
37
540
662

Calculate Avg Time in R

3 Answers3