Merge overlapping / adjacent time intervals and drop inclusive intervals with pandas

user_4303718 0
Viewed 59

I'm trying to find a way in pandas to

  1. Merge adjacent (+1 day) or partly overlapping time intervals
  2. Remove rows with an interval that is inclusive

Here is an example of the Dataset that I'm using:

import pandas as pd

df = pd.DataFrame([
        [42,1,'1990-01-01','2099-12-31'],
        [42,2,'1990-01-01','2099-12-31'],
        [42,2,'2010-10-01','2016-02-29'],
        [42,2,'2011-09-01','2014-09-30'],
        [42,2,'2012-04-01','2016-07-31'],
        [42,2,'2013-04-01','2014-06-30'],
        [42,2,'2013-09-01','2014-02-28'],
        [42,2,'2014-03-01','2017-04-30'],
        [42,2,'2014-10-01','2017-10-31'],
        [42,2,'2015-04-01','2015-12-31'],
        [42,2,'2016-03-01','2020-03-31'],
        [42,2,'2016-08-01','2016-12-31'],
        [42,2,'2017-01-01','2018-08-31'],
        [42,2,'2017-05-01','2018-12-31'],
        [42,2,'2017-11-01','2021-06-14'],
        [42,2,'2018-09-01','2019-01-31'],
        [42,2,'2019-02-01','2019-05-31'],
        [42,2,'2019-04-01','2021-03-31'],
        [42,2,'2019-06-01','2020-07-31'],
        [42,2,'2020-08-01','2021-03-14'],
        [42,2,'2021-03-15','2021-06-30'],
        [43,1,'1998-01-01','2001-12-31'],
        [43,1,'2010-03-15','2021-06-30'],
        [43,1,'2021-01-01','2021-12-31'],
        [43,2,'1998-01-01','1998-12-31'],
        [43,2,'1999-01-01','2000-12-31'],
        [43,2,'2001-01-01','2021-06-30']
    ], columns=['Id', 'SubId', 'Start', 'End'])
df['Start'] = pd.to_datetime(df['Start'])
df['End'] = pd.to_datetime(df['End'])

Expected Output

The data I expect:

42, 1, 1990-01-01, 2099-12-31
42, 2, 1990-01-01, 2099-12-31
43, 1, 1998-01-01, 2001-12-31
43, 1, 2010-03-15, 2021-12-31
43, 2, 1998-01-01, 2021-06-30

Tested (not correctly working) Solution

Based on the solution to a similar problem Pandas: Combine rows with consecutive dates (with NaT) within groups of same id I have implemented their solution, but it does not yield the expected result

df = df.sort_values(['Id', 'SubId', 'Start'])
day_diff = (df['Start'] - df['End'].groupby([df['Id'], df['SubId']]).shift()).dt.days
group_no = (day_diff.isna() | day_diff.gt(1)).cumsum()
df_out = (df.groupby(['Id', 'SubId', group_no], dropna=False, as_index=False)
            .agg({'Id': 'first',
                  'SubId': 'first',
                  'Start': 'first',
                  'End': lambda x: x.iloc[-1]
                }))

As far as I understand the solution will work fine if all intervals are adjacent to each other, but if there is an overarching interval it will not remember the overarching interval.

42 ,1 , 1990-01-01, 2099-12-31
42 ,2 , 1990-01-01, 2015-12-31  <-- Wrong End date; expected 2099-12-31
42 ,2 , 2016-03-01, 2021-06-30  <-- Wrong; Should no be there, but included in above
43 ,1 , 1998-01-01, 2001-12-31
43 ,1 , 2010-03-15, 2021-12-31
43 ,2 , 1998-01-01, 2021-06-30

What would be interesting to know is: If it would be possible to keep track of the min Start and max End while iterating over the same Id, SubId pair.

pandas python
1 Answers

I managed to find a solution which is working (tm). If anyone has a better solution, please share it!

import pandas as pd

df = pd.DataFrame([
        [42,1,'1990-01-01','2099-12-31'],
        [42,2,'1990-01-01','2099-12-31'],
        [42,2,'2010-10-01','2016-02-29'],
        [42,2,'2011-09-01','2014-09-30'],
        [42,2,'2012-04-01','2016-07-31'],
        [42,2,'2013-04-01','2014-06-30'],
        [42,2,'2013-09-01','2014-02-28'],
        [42,2,'2014-03-01','2017-04-30'],
        [42,2,'2014-10-01','2017-10-31'],
        [42,2,'2015-04-01','2015-12-31'],
        [42,2,'2016-03-01','2020-03-31'],
        [42,2,'2016-08-01','2016-12-31'],
        [42,2,'2017-01-01','2018-08-31'],
        [42,2,'2017-05-01','2018-12-31'],
        [42,2,'2017-11-01','2021-06-14'],
        [42,2,'2018-09-01','2019-01-31'],
        [42,2,'2019-02-01','2019-05-31'],
        [42,2,'2019-04-01','2021-03-31'],
        [42,2,'2019-06-01','2020-07-31'],
        [42,2,'2020-08-01','2021-03-14'],
        [42,2,'2021-03-15','2021-06-30'],
        [43,1,'1998-01-01','2001-12-31'],
        [43,1,'2010-03-15','2021-06-30'],
        [43,1,'2021-01-01','2021-12-31'],
        [43,2,'1998-01-01','1998-12-31'],
        [43,2,'1999-01-01','2000-12-31'],
        [43,2,'2001-01-01','2021-06-30']
    ], columns=['Id', 'SubId', 'Start', 'End'])
df['Start'] = pd.to_datetime(df['Start'])
df['End'] = pd.to_datetime(df['End'])
df.set_index(['Id', 'SubId'], inplace=True)
df = df.sort_values(['Id', 'SubId', 'Start'])

Extract maximum time intervals, since they will nullify any other intervals

all_df = df[(df['Start']==pd.to_datetime('1990-01-01')) & (df.End==pd.to_datetime('2099-12-31'))]

Merge all adjacent time intervals, by first removing all maximum intervals

inter_df = df.drop(df[(df['Start']==pd.to_datetime('1990-01-01')) & (df.End==pd.to_datetime('2099-12-31'))].index)
day_diff = (inter_df['Start'] - inter_df['End'].groupby(['Id', 'SubId']).shift()).dt.days
inter_df['_group'] = (day_diff.isna() | day_diff.gt(1)).cumsum()
merged_inter_df = inter_df.groupby(['Id', 'SubId', '_group'], dropna=False, as_index=True)\
    .agg({'Start': 'min', 'End': 'max'})\
    .droplevel('_group')

Concat data frame with maximum interval and merged adjacent time intervals

full_df = pd.concat([all_df, merged_inter_df])
full_df.sort_index(inplace=True)
full_df

Which yields me the expected result

        Start   End
Id  SubId       
42  1   1990-01-01  2099-12-31
2       1990-01-01  2099-12-31
43  1   1998-01-01  2001-12-31
1       2010-03-15  2021-12-31
2       1998-01-01  2021-06-30
user_43037180
Related