CODEWITHSUNDEEP
rtime
P. Vauclin
P. Vauclin

Reputation: 387

Calculate a time according to criteria in R

I'm working on a project to calculate player game times. My problem is that I want to calculate the "TimeOnIce" of the "Player" when their "Start" and "Stop" fit into those of the "Play".

I hope I've been clear enough

Data <- data.table(Name = c("Play", "Play", "Play", "Play", "Player", "Player", "Player"), Start = c("00:15:01", "00:15:56", "00:16:29", "00:22:18", "00:14:58", "00:18:04", "00:20:38"), Stop = c("00:15:24", "00:16:04", "00:21:50", "00:23:33", "00:16:25", "00:19:13", "00:21:22"), TimeOnIce = " ")

What i expect :

NAME          START         STOP        TIMEONICE
PLAY          00:15:01      00:15:24    
PLAY          00:15:56      00:16:04
PLAY          00:16:29      00:21:50
PLAY          00:22:18      00:23:33
PLAYER        00:14:58      00:16:25    00:00:31
PLAYER        00:18:04      00:19:13    00:01:09
PLAYER        00:20:38      00:21:22    00:01:16

Image Representation to better understand Thanks

Upvotes: 0

Views: 57

Answers (1)

Wimpel
Wimpel

Reputation: 27732

library( data.table )
library( dplyr ) # <---- for using dplyr::if_else, which leaves POSIXct intact (base::ifelse does not!)

sample data

Data <- data.table(Name = c("Play", "Play", "Play", "Play", "Player", "Player", "Player"), 
               Start = as.POSIXct( c("00:15:01", "00:15:56", "00:16:29", "00:22:18", "00:14:58", "00:18:04", "00:20:38"), format = "%H:%M:%S"),
               Stop = as.POSIXct( c("00:15:24", "00:16:04", "00:21:50", "00:23:33", "00:16:25", "00:19:13", "00:21:22"), format = "%H:%M:%S"))

code

#create a data.table with play periods
dt_play <- Data[ Name == "Play", ]
#key it
setkey( dt_play, Start, Stop )

#create a data.frame with player times
dt_players <- Data[ !Name == "Play", ]

#overlap join, then het the revelant periods playd, and sum per player
result <- foverlaps( dt_players, dt_play, type = "any")

#set time from and to, based on the relevant start- end of periods
result[, `:=`(from = if_else( i.Start < Start, Start, i.Start),
              to = if_else( i.Stop > Stop, Stop, i.Stop) )][]
result[, TimeOnIce := as.numeric( to - from )]

output

#summarise per player
result[, list(TimeOnIce = sum(to - from)), by = i.Name]

#    i.Name TimeOnIce
# 1: Player  144 secs

#or get the results per played interval
result[, list(TimeOnIce = sum(TimeOnIce)), by = list(i.Name, i.Start, i.Stop)]

#    i.Name             i.Start              i.Stop TimeOnIce
# 1: Player 2018-10-11 00:14:58 2018-10-11 00:16:25        31
# 2: Player 2018-10-11 00:18:04 2018-10-11 00:19:13        69
# 3: Player 2018-10-11 00:20:38 2018-10-11 00:21:22        44

Upvotes: 1

Related Questions