Get the overlap duration between date intervals based on condition

Question

I have two dataframes, they have a start/end datetime and a value. Not the same number of rows. The intervals which overlap may not be in the same row/index.

df1

start_datetime   end_datetime   value
08:50            09:50          5
09:52            10:10          6
10:50            11:30          2

df2

start_datetime   end_datetime   value
08:51            08:59          3
09:52            10:02          9
10:03            10:30          1
11:03            11:39          1
13:10            13:15          0

I would like to calculate the sum of duration time when df1 and df2 overlap only if df1.value > df2.value. During one df2 time interval, df1 can overlaps multiple times and sometimes the condition is True.

I tried something like that:

            time = timedelta()
            for i, row1 in df1.iterrows():
                t1 = pd.Interval(row1.start, row1.end)
                for j, row2 in df2.iterrows():
                    t2 = pd.Interval(row2.start, row2.end)
                    if t1.overlaps(t2) and row1.value > row2.value:
                        latest_start = np.maximum(row1.start, row1.start)
                        earliest_end = np.minimum(row2.end, row2.end)
                        delta = earliest_end - latest_start
                        time += delta

I can loop on every df1 rows and test with the whole df2 data but it's not optimized.

expected output (example):

Timedelta('0 days 00:99:99')

Answer 1

Here is my solution:

1. Create DataFrames:

df1 = pd.DataFrame(
    {"start_datetime1": ['08:50' ,'09:52' ,'10:50 ' ],  
     'end_datetime1' : ['09:50','10:10','11:30'] , 
     'value1': [5,6,2]})

df2 = pd.DataFrame(
      {"start_datetime2": ['08:51' ,'09:52' ,'10:03 ','11:03 ','13:10 ' ], 
       'end_datetime2' : ['08:59','10:02','10:30','11:39', '13:15'] ,
       'value2': [3,9,1,1,0]})

df2["start_datetime2"]= pd.to_datetime(df2["start_datetime2"])
df2["end_datetime2"]= pd.to_datetime(df2["end_datetime2"])

df1["start_datetime1"]= pd.to_datetime(df1["start_datetime1"])
df1["end_datetime1"]= pd.to_datetime(df1["end_datetime1"])

2. Combine dataframes to make comparison easier. Combined dataframe has all possible matches :

df1['temp'] = 1 #temporary keys to create all combinations
df2['temp'] = 1
df_combined = pd.merge(df1,df2,on='temp').drop('temp',axis=1)

3. Compare values with lambda function:

df_combined['Result'] = df_combined.apply(lambda row: max(row["start_datetime1"],row["start_datetime2"]) -
                                         min(row["start_datetime1"],row["start_datetime2"]) 
                                         if pd.Interval(row['start_datetime1'], row['end_datetime1']).overlaps(
                                             pd.Interval(row['start_datetime2'], row['end_datetime2'])) and
                                             row["value1"] > row["value2"] 
                                             else 0, axis = 1 )
df_combined

Result :

total_timedelta = df_combined['Result'].loc[df_combined['Result'] != 0].sum()
0 days 00:25:00

Dataframe: