Q:

How to distribute multiple time interval values over 1 minute base in R?

I have a data of hundred of thousands cars which looks like: 
df <- data.frame(
'ID' = c('1','1','1','2','2','2'), 'start' = c('06:00', '08:00', '12:15', '06:00','12:30', '18:00'), 
'end' = c('07:00', '08:30', '12:45', '06:45', '13:00', '18:45'), 'value' = c('35', '34', '30', '45', '50', '32')
Now, I want to convert these values into 1 minute basis variable. So, converted data frame would consist of only minute base time column (1 to 1440, or from 00:00 to 23:59) and total minute base traffic of IDs  which is value divided by time difference (value/(end-start)) and plot it as a step chart. 
Below I have attached excel file as an example: excel example . Any help would be appreciated! 

A:

The hard part was converting you character strings into real times.  Does this work for you?
library(tidyverse)
df <- tibble(
  'ID' = c('1','1','1','2','2','2'), 
  'start' = c('06:00', '08:00', '12:15', '06:00','12:30', '18:00'), 
  'end' = c('07:00', '08:30', '12:45', '06:45', '13:00', '18:45'), 
  'value' = c('35', '34', '30', '45', '50', '32'))

durFn <- function(stDur){
  st <- str_split(stDur, ":") %>% 
    unlist() %>% 
    as.double()
  dur <- lubridate::duration(hours = st[1], minutes = st[2])
}

df %>% 
  as_tibble() %>% 
  mutate(startD = map(start, durFn),
         endD = map(end, durFn),
         value = as.double(value),
         minutes = map2_dbl(startD, endD, 
                        ~ suppressMessages(as.double(.y - .x) / 60 )),
         minutes = minutes %% 1440,       # Just in case we wrapped around midnight
         basis = value/ minutes) %>% 
  select(- startD, - endD)

I created a lubridate::duration for your start and end.  They're in seconds.  I took their difference and divided by 60 to get minutes.  Then your basis is just value/minutes.

