Hello
I am trying to figure out an efficient way to set up an “events in progress” problem in dataframes.jl. I have two tables. the first is a performance log that tracks what units are doing by hour. The second is an event log that spans a given time period for a list of particular units of interest. for a given unit at a specific time period, there may be 0, 1, or multiple events active at that time (i.e., the events are not mutually exclusive with one another).
not quite sure what the best approach here is. below is code outlining my MWE
using DataFrames, DataFramesMeta, Dates
# define first df (i.e., timeseries log)
start_dt = DateTime(2023, 1, 1)
end_dt = DateTime(2023, 2, 1)
dt_range = start_dt:Hour(1):end_dt
u_1 = rand(length(dt_range))
u_2 = rand(length(dt_range))
df_1 = DataFrame(dt_EST=dt_range, unit_1=u_1, unit_2=u_2)
df_1 = stack(df_1, [:unit_1, :unit_2])
# define second df (i.e., sample log of defined events)
event_id = [1, 2, 3, 4, 5, 6]
event_start_times = [DateTime(2022,12,22,3),DateTime(2022,12,24,14),DateTime(2022,12,31,14), DateTime(2023,1,5,14), DateTime(2023,1,18,14), DateTime(2023,2,16,21)]
event_end_times = [DateTime(2022,12,24,6),DateTime(2023,2,2,14),DateTime(2023,1,15,4), DateTime(2023,1,21,23), DateTime(2023,2,15,21), DateTime(2023,2,21,21)]
event_unit = ["unit_1", "unit_1", "unit_2", "unit_1", "unit_2", "unit_1"]
df_2 = DataFrame(id = event_id, event_start = event_start_times, event_end=event_end_times, unit_id=event_unit)
The desired output is a dataframe that gives me the performance of each unit by hour along with a indication of which event id were active in that hour (if any). while the number of events that could be simultaneously active for a given unit in any hour is theoretically unknown upfront, in reality it is not going to be more than 3.