I have a dataset similar to the one below
sdt1 = DataFrame(ID = 1:2, StartTime = DateTime(["4/13/2016 07:00", "4/13/2016 07:15"], "m/d/y H:M"),
EndTime = DateTime(["4/13/2016 12:00", "4/13/2016 12:15"], "m/d/y H:M"),
BreakTime1 = DateTime(["4/13/2016 10:00", "4/13/2016 10:15"], "m/d/y H:M"),
BreakTime2 = DateTime(["4/13/2016 12:00", "4/13/2016 12:15"], "m/d/y H:M")
)
I can get the sequence of date time using
for i in 1:nrow(sdt1)
si = Dates.format([sdt1[i,:StartTime]:Dates.Minute(30):sdt1[i,:EndTime]], "HH:MM")
println(si)
end
#Any["07:00","07:30","08:00","08:30","09:00","09:30","10:00","10:30","11:00","11:30","12:00"]
#Any["07:15","07:45","08:15","08:45","09:15","09:45","10:15","10:45","11:15","11:45","12:15"]
Suppose I want to subset the sequence to not include the times in BreakTime columns, how do I do it? I have tried
for i in 1:nrow(sdt1)
si = Dates.format([sdt1[i,:StartTime]:Dates.Minute(30):sdt1[i,:EndTime]], "HH:MM")
println(si in sdt1[i, [:BreakTime1, :BreakTime2]])
end
I get an error message
MethodError: `start` has no method matching start(::DataFrames.DataFrame)
in mapreduce_sc_impl at reduce.jl:194
in in at reduce.jl:377
I can do this using data.table in R
library(data.table)
dt1 <- data.table(ID = 1:2, StartTime =as.POSIXct(c("4/13/2016 07:00", "4/13/2016 07:15"), "%m/%d/%Y %H:%M", tz = "GMT"),
EndTime =as.POSIXct(c("4/13/2016 14:00", "4/13/2016 14:15"), "%m/%d/%Y %H:%M", tz = "GMT"),
BreakTime1 = as.POSIXct(c("4/13/2016 10:00", "4/13/2016 10:15"), "%m/%d/%Y %H:%M", tz = "GMT"),
BreakTime2 = as.POSIXct(c("4/13/2016 12:00", "4/13/2016 12:15"), "%m/%d/%Y %H:%M", tz = "GMT"))
dt1[, {
s1 <- head(seq(StartTime, EndTime, by = "30 min"), -1)
list(time= format(s1[!s1 %in% c(BreakTime1, BreakTime2)], "%H:%M"))},
by = ID]
ID time
1: 1 07:00
2: 1 07:30
3: 1 08:00
4: 1 08:30
5: 1 09:00
6: 1 09:30
7: 1 10:30
8: 1 11:00
9: 1 11:30
10: 1 12:30
11: 1 13:00
12: 1 13:30
13: 2 07:15
14: 2 07:45
15: 2 08:15
16: 2 08:45
17: 2 09:15
18: 2 09:45
19: 2 10:45
20: 2 11:15
21: 2 11:45
22: 2 12:45
23: 2 13:15
24: 2 13:45
Thanks.