Pandas sum time interval in a group excluding overlaps

user_4759898 0
Viewed 1350

I have a dataframe where each row has a start time and an end time. Each row has also a group they belong to. I want to have a new column which gives the sum of all the time in that group in seconds.

For example if we have a group which looks like this:

         id1:    |----|
         id2:       |-----|
         id3:                      |--------|
                 .  .  .  .  .  .  .  .  .  .  .
time ->        12:00    12:04    12:07    12:10

then for all rows belonging to that group would get the summed time of 4+3 min = 420 seconds

If they overlap completely then we would get a scenario like this:

         id1:    |--------|
         id2:    |--------|
                 .  .  .  .  .  .  .  .  .  .  .
time ->        12:00    12:04    12:07    12:10

which would give us the result 4 min = 240 seconds.

Below is some dummy data:

import pandas as pd

ids = [x for x in range(10)]
group = [0, 1, 1, 2, 2, 3, 4, 4, 4, 4]

start = pd.to_datetime(["2019-10-21-16:20:00", "2019-10-21-16:22:00", "2019-10-21-16:22:00", "2019-10-21-16:15:00",
         "2019-10-21-16:22:00", "2019-10-21-16:58:00", "2019-10-21-17:02:00", "2019-10-21-17:03:00",
         "2019-10-21-17:04:00", "2019-10-21-17:20:00"])

end = pd.to_datetime(["2019-10-21-16:25:00", "2019-10-21-16:24:00", "2019-10-21-16:24:00", "2019-10-21-16:18:00",
       "2019-10-21-16:26:00", "2019-10-21-17:02:00", "2019-10-21-17:06:00", "2019-10-21-17:07:00",
       "2019-10-21-17:08:00", "2019-10-21-17:22:00"])

cols = ["id", "group", "start", "end"]


df = pd.DataFrame(dict(zip(cols, [ids, group, start, end])))

So far the approach I've tried is obviously not correct. I've tried grouping and then finding the min and max of each start and end of that group, and then just set that interval as the sum. This approach will not be correct as it will also include the gap in the interval.

gr = df.groupby("group").apply(lambda x : x.end.max() - x.start.min())
df['total_time'] = df.group.map(gr)
pandas-groupby pandas python-3.x python
4 Answers

First, add a column which tracks the latest end time seen so far (but only considering the same group):

df['notbefore'] = df.groupby('group').end.shift().cummax()

It's shifted by 1 so that it reflects the latest end time seen on previous rows, excluding the same row. It's important to have shift() before cummax(), otherwise the shift will "leak" values between groups.

Then add a column containing the "effective" start time:

df['effstart'] = df[['start', 'notbefore']].max(1)

This is the start time modified so that it is not before any previous end time (to avoid overlap).

Then compute the total seconds covered:

df['effsec'] = (df.end - df.effstart).clip(np.timedelta64(0))

df is now:

   id  group               start                 end           notbefore            effstart   effsec
0   0      0 2019-10-21 16:20:00 2019-10-21 16:25:00                 NaT 2019-10-21 16:20:00 00:05:00
1   1      1 2019-10-21 16:22:00 2019-10-21 16:24:00                 NaT 2019-10-21 16:22:00 00:02:00
2   2      1 2019-10-21 16:22:00 2019-10-21 16:24:00 2019-10-21 16:24:00 2019-10-21 16:24:00 00:00:00
3   3      2 2019-10-21 16:15:00 2019-10-21 16:18:00                 NaT 2019-10-21 16:15:00 00:03:00
4   4      2 2019-10-21 16:22:00 2019-10-21 16:26:00 2019-10-21 16:24:00 2019-10-21 16:24:00 00:02:00
5   5      3 2019-10-21 16:58:00 2019-10-21 17:02:00                 NaT 2019-10-21 16:58:00 00:04:00
6   6      4 2019-10-21 17:02:00 2019-10-21 17:06:00                 NaT 2019-10-21 17:02:00 00:04:00
7   7      4 2019-10-21 17:03:00 2019-10-21 17:07:00 2019-10-21 17:06:00 2019-10-21 17:06:00 00:01:00
8   8      4 2019-10-21 17:04:00 2019-10-21 17:08:00 2019-10-21 17:07:00 2019-10-21 17:07:00 00:01:00
9   9      4 2019-10-21 17:20:00 2019-10-21 17:22:00 2019-10-21 17:08:00 2019-10-21 17:20:00 00:02:00

To get the final results:

df.groupby('group').effsec.sum()

Which gives you:

group
0   00:05:00
1   00:02:00
2   00:05:00
3   00:04:00
4   00:08:00
John_Zwinck_43230

Use- 

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

    for higher in sorted_by_lower_bound:
        if not merged:
            merged.append(higher)
        else:
            lower = merged[-1]
            # test for intersection between lower and higher:
            # we know via sorting that lower[0] <= higher[0]
            if higher[0] <= lower[1]:
                upper_bound = max(lower[1], higher[1])
                merged[-1] = (lower[0], upper_bound)  # replace by merged interval
            else:
                merged.append(higher)
    return merged

df['dt'] = df[['start', 'end']].apply(tuple, axis=1)
op = df.groupby(['group'])['dt'].apply(list)
f_op = op.apply(merge_intervals)

op_d = f_op.apply(lambda x: sum([(y[1]-y[0]).seconds for y in x]))

Output

group
0    300
1    120
2    420
3    240
4    480
user_40980130

Assuming your dataframe is sorted, what about something like this?

In [1]: import datetime 
        def calc_periods(x):
            time_delt = datetime.timedelta()
            for i in x.index:
                if (i > x.index[0]):
                    if x.loc[i].start < x.loc[i-1].end:
                        time_delt += x.loc[i].end - x.loc[i-1].end
                    else:
                        time_delt += x.loc[i].end - x.loc[i].start
                else:
                    time_delt += x.loc[i].end - x.loc[i].start
            return time_delt.seconds


In [2]: df.groupby('group')[['start', 'end']].apply(calc_periods)
Out[2]: group
        0    300
        1    120
        2    420
        3    240
        4    480
        dtype: int64
user_63510500

As the source data I took the following DataFrame:

  group             start               end
0    G1  2019-09-01 12:00  2019-09-01 12:02
1    G1  2019-09-01 12:01  2019-09-01 12:04
2    G1  2019-09-01 12:07  2019-09-01 12:10
3    G2  2019-09-01 12:05  2019-09-01 12:12
4    G2  2019-09-01 12:10  2019-09-01 12:15

The first step is to define a function counting seconds within a group of rows:

def getSecs(grp):
    return pd.DatetimeIndex([]).union_many([ pd.date_range(
        row.start, row.end, freq='s', closed='left')
            for _, row in grp.iterrows() ]).size

Then apply this function to each group, grouping by group:

secs = df.groupby('group').apply(getSecs).rename('secs')

For my test data, the result is:

group
G1    420
G2    600
Name: secs, dtype: int64

And the last step is to create a new column in df by merging with secs:

df = df.merge(secs, left_on='Grp', right_index=True)

The result is:

  group             start               end  secs
0    G1  2019-09-01 12:00  2019-09-01 12:02   420
1    G1  2019-09-01 12:01  2019-09-01 12:04   420
2    G1  2019-09-01 12:07  2019-09-01 12:10   420
3    G2  2019-09-01 12:05  2019-09-01 12:12   600
4    G2  2019-09-01 12:10  2019-09-01 12:15   600

A quite concise solution, just 6 lines of code, substantially less than some other solutions.

Note also that only my solution creates a new column, with equal values for all rows within each group (one of other solution failed on this detail). All other solutions stopped at computing numbers of seconds for each group.

user_73884770
Related