Pandas calculate total hours from overlapping time ranges

Question

I have the following dataframe

import pandas as pd
from datetime import datetime

df_dict = {
    'id':[1,1,1,1,2,2,2,2],
    'start_time':[
    datetime.strptime('Jun 1 2020  1:30PM', '%b %d %Y %I:%M%p'),
    datetime.strptime('Jun 1 2020  2:30PM', '%b %d %Y %I:%M%p'),
    datetime.strptime('Jun 1 2020  3:30PM', '%b %d %Y %I:%M%p'),
    datetime.strptime('Jun 1 2020  4:30PM', '%b %d %Y %I:%M%p'),
    datetime.strptime('Jun 1 2020  1:30PM', '%b %d %Y %I:%M%p'),
    datetime.strptime('Jun 1 2020  2:30PM', '%b %d %Y %I:%M%p'),
    datetime.strptime('Jun 1 2020  3:30PM', '%b %d %Y %I:%M%p'),
    datetime.strptime('Jun 1 2020  4:30PM', '%b %d %Y %I:%M%p'),
    ],
    'end_time':[
    datetime.strptime('Jun 1 2020  2:45PM', '%b %d %Y %I:%M%p'),
    datetime.strptime('Jun 1 2020  3:00PM', '%b %d %Y %I:%M%p'),
    datetime.strptime('Jun 1 2020  4:50PM', '%b %d %Y %I:%M%p'),
    datetime.strptime('Jun 1 2020  4:30PM', '%b %d %Y %I:%M%p'),
    datetime.strptime('Jun 1 2020  3:45PM', '%b %d %Y %I:%M%p'),
    datetime.strptime('Jun 1 2020  5:00PM', '%b %d %Y %I:%M%p'),
    datetime.strptime('Jun 1 2020  5:50PM', '%b %d %Y %I:%M%p'),
    datetime.strptime('Jun 1 2020  6:30PM', '%b %d %Y %I:%M%p'),
    ]
}
df = pd.DataFrame.from_dict(df_dict)

#    id          start_time            end_time
# 0   1 2020-06-01 13:30:00 2020-06-01 14:45:00
# 1   1 2020-06-01 14:30:00 2020-06-01 15:00:00
# 2   1 2020-06-01 15:30:00 2020-06-01 16:50:00
# 3   1 2020-06-01 16:30:00 2020-06-01 16:30:00
# 4   2 2020-06-01 13:30:00 2020-06-01 15:45:00
# 5   2 2020-06-01 14:30:00 2020-06-01 17:00:00
# 6   2 2020-06-01 15:30:00 2020-06-01 17:50:00
# 7   2 2020-06-01 16:30:00 2020-06-01 18:30:00

I want to calculate the total hours for each id without double counting the overlapping intervals.

I have the below code, which gives the correct result

import sqlite3

conn = sqlite3.connect(':memory:')
df.to_sql('df', conn, index=False)

query = '''
SELECT id, SUM(CAST((JulianDay(end_time)-JulianDay(start_time))*24 AS real)) AS total_hours
FROM (
    SELECT s1.id,
           s1.start_time,
           MIN(t1.end_time) AS end_time
    FROM df s1 
    INNER JOIN df t1 ON s1.start_time <= t1.end_time
      AND s1.id = t1.id
      AND NOT EXISTS(SELECT * FROM df t2 
                     WHERE t1.end_time >= t2.start_time AND t1.end_time < t2.end_time AND t2.id = t1.id) 
    WHERE NOT EXISTS(SELECT * FROM df s2 
                     WHERE s1.start_time > s2.start_time AND s1.start_time <= s2.end_time AND s2.id = t1.id)
    GROUP BY s1.start_time, s1.id
    ORDER BY s1.id, s1.start_time
    ) x
GROUP BY id
'''

df = pd.read_sql_query(query, conn)
print(df)

#    id  total_hours
# 0   1     2.833333
# 1   2     5.000000

but I'm wondering if there is a better/more elegant way to approach this, without using SQL.

user_12936881 · Answer

you can use the groupby function of pandas. The following code will do the work:

import numpy as np
df['start_time_tmp']=np.where((df['start_time'] <= df['end_time'].shift(1))&
                              (df['end_time'] >= df['end_time'].shift(1)), df['end_time'].shift(1), df['start_time'])
df['diff'] = df['end_time']-df['start_time_tmp']
df.groupby(by='id')['diff'].sum().dt.total_seconds()/60/60

Output is:

id
1    2.833333
2    5.000000
Name: diff, dtype: float64

I hope, the code is self explanatory. If you need some help about the groupby function you can look the docs

user_5363686 · Answer

Basically, what your SQL code does is to remove overlapping intervals. You should do the same here. My suggestion is the following:

def remove_overlap_intervals(intervals):
    sorted = sorted(intervals, key=lambda tup: tup[0])
    merged = []

    for a in sorted:
        if not merged:
            merged.append(a)
        else:
            b = merged[-1]
            if a[0] <= b[1]:
                upper_bound = max(b[1], a[1])
                merged[-1] = (b[0], upper_bound) 
            else:
                merged.append(a)
    return merged

Then do exactly what you would do if the intevals weren't overlapping:


df['time'] = df[['start_time', 'end_time']].apply(tuple, axis=1)
Grouped = df.groupby(['id'])['time'].apply(list)
Grouped_no_overlap = Grouped.apply(remove_overlap_intervals)

Grouped = Grouped_no_overlap.apply(lambda x: sum([(y[1]-y[0]).seconds for y in x]))/3600

which gives:

id
1    2.833333
2    5.000000
Name: time, dtype: float64

user_7388477 · Answer

As I see, the time resolution of your datetimes is limited to minutes.

So one of possible solutions is:

for each row in a group (by id), generate date_range object with minute frequency, excluding the right edge of the respective range,
concatenate these ranges,
compute the sum of unique values (for now we have the number of minutes),
divide it by 60, getting the number of hours.

The code to do it is:

Define getRng function to generate the date_range object from the current row:

def getRng(row):
    return pd.date_range(row.start_time, row.end_time, freq='min', closed='left')

Define getHrs function to compute the number of hours for the current group:
```
def getHrs(grp):
    return np.unique(np.hstack(grp.apply(getRng, axis=1))).size / 60
```
I deliberately chose Numpy functions, as they are known to operate faster than those of Pandas.
Apply the above function to each group (by id) and convert the result to a DataFrame:
```
result = df.groupby('id').apply(getHrs).rename('total_hours').reset_index()
```

For your data sample, the result is:

   id  total_hours
0   1     2.833333
1   2     5.000000

I think, this solution is shorter (just 5 lines of code), more readable than your SQL and more pandasonic.

Pandas calculate total hours from overlapping time ranges

3 Answers