0

How can I sum the total time per driver in R? Can someone help me?

enter image description here

Total time Prefered end result

BIDATA
  • 11
  • 1

1 Answers1

0

One recommendation to make: please do not use images to share data. Instead, use dput() of your data frame. See this post on making a reproducible example on SO.

One approach to this involves the tidyverse and lubridate packages (I am sure there are other solutions).

First, would put your data into long form instead of wide. The times are then converted from %H:%M:%OS (with milliseconds) to durations since midnight.

Then, for each driver, these times are summed up, and results are provided in different formats:

  1. total_time1 - total number of seconds (with decimal places)
  2. total_time2 - number minutes (M) and the number decimal seconds (S)
  3. total_time3 - total time in %M:%OS format (minutes and decimal seconds)

Edit: In addition, I have added two columns based on OP request:

  1. total_time_minutes - total number of minutes (with decimal places)
  2. avg_speed - average speed in km/hr, assuming 27.004,65 meters

I hope this is helpful. Please let me know.

library(tidyverse)
library(lubridate)

df %>%
  pivot_longer(cols = -lap) %>%
  mutate(lap_time = as.numeric(as.POSIXct(value, format = "%H:%M:%OS", tz = "UTC")) -
           as.numeric(as.POSIXct(Sys.Date(), tz = "UTC"))) %>%
  group_by(name) %>%
  summarise(total_time1 = sum(lap_time)) %>%
  mutate(total_time2 = seconds_to_period(total_time1),
         total_time3 = sprintf("%d:%.4f", minute(total_time2), second(total_time2)),
         total_time_minutes = total_time1/60,
         avg_speed = 3.6 * 27004.65/total_time1) %>%
  as.data.frame()

Output

        name total_time1          total_time2 total_time3 total_time_minutes avg_speed
1     Bottas     319.782 5M 19.7815999984741S   5:19.7816            5.32969   304.010
2   Hamilton     320.320 5M 20.3204002380371S   5:20.3204            5.33867   303.498
3    Leclerc     319.981   5M 19.98140001297S   5:19.9814            5.33302   303.820
4 Verstappen     318.220  5M 18.219899892807S   5:18.2199            5.30366   305.502
5     Vettel     318.625 5M 18.6247997283936S   5:18.6248            5.31041   305.114

Data

df <- structure(list(lap = 1:5, Bottas = c("00:01:04.9388", "00:01:03.7164", 
"00:01:04.0028", "00:01:03.3424", "00:01:03.7812"), Hamilton = c("00:01:04.5280", 
"00:01:03.7524", "00:01:03.9632", "00:01:04.3712", "00:01:03.7056"
), Leclerc = c("00:01:04.9812", "00:01:03.7740", "00:01:04.6026", 
"00:01:03.3920", "00:01:03.2316"), Verstappen = c("00:01:04.1704", 
"00:01:03.7383", "00:01:03.7128", "00:01:02.8460", "00:01:03.7524"
), Vettel = c("00:01:04.3632", "00:01:02.8244", "00:01:03.7164", 
"00:01:03.8532", "00:01:03.8676")), class = "data.frame", row.names = c(NA, 
-5L))
Ben
  • 28,684
  • 5
  • 23
  • 45
  • Do you know how I can calculate the average speed per second or hour? For example, it took Verstappen 318.2199 seconds to drive 27.004,65 meters (5 laps). I also added a new picture called "Total time" in my first post. – BIDATA Nov 22 '20 at 16:16
  • My final result should look like "prefered end result" in my first post. Thank you very much for your help! – BIDATA Nov 22 '20 at 16:22
  • @BIDATA Please see edited answer with time in minutes and average speed in km/hr – Ben Nov 22 '20 at 18:29