pandas - Find date range overlap in python

Question

I am trying to find an more efficient way of finding overlapping data ranges (start/end dates provided per row) in a dataframe based on a specific column (id).

Dataframe is sorted on 'from' column

I think there is a way to avoid "double" apply function like I did...

import pandas as pd
from datetime import datetime

df = pd.DataFrame(columns=['id','from','to'], index=range(5), 
                  data=[[878,'2006-01-01','2007-10-01'],
                        [878,'2007-10-02','2008-12-01'],
                        [878,'2008-12-02','2010-04-03'],
                        [879,'2010-04-04','2199-05-11'],
                        [879,'2016-05-12','2199-12-31']])

df['from'] = pd.to_datetime(df['from'])
df['to'] = pd.to_datetime(df['to'])


    id  from        to
0   878 2006-01-01  2007-10-01
1   878 2007-10-02  2008-12-01
2   878 2008-12-02  2010-04-03
3   879 2010-04-04  2199-05-11
4   879 2016-05-12  2199-12-31

I used the "apply" function to loop on all groups and within each group, I use "apply" per row:

def check_date_by_id(df):

    df['prevFrom'] = df['from'].shift()
    df['prevTo'] = df['to'].shift()

    def check_date_by_row(x):

        if pd.isnull(x.prevFrom) or pd.isnull(x.prevTo):
            x['overlap'] = False
            return x

        latest_start = max(x['from'], x.prevFrom)
        earliest_end = min(x['to'], x.prevTo)
        x['overlap'] = int((earliest_end - latest_start).days) + 1 > 0
        return x

    return df.apply(check_date_by_row, axis=1).drop(['prevFrom','prevTo'], axis=1)

df.groupby('id').apply(check_date_by_id)

    id  from        to          overlap
0   878 2006-01-01  2007-10-01  False
1   878 2007-10-02  2008-12-01  False
2   878 2008-12-02  2010-04-03  False
3   879 2010-04-04  2199-05-11  False
4   879 2016-05-12  2199-12-31  True

My code was inspired from the following links :

• Efficient date range overlap calculation in python?

• How to find range overlap in python?

Answer 1

You could just shift the to column and perform a direct subtraction of the datetimes.

df['overlap'] = (df['to'].shift()-df['from']) > timedelta(0)

Applying this while grouping by id may look like

df['overlap'] = (df.groupby('id')
                   .apply(lambda x: (x['to'].shift() - x['from']) > timedelta(0))
                   .reset_index(level=0, drop=True))

Demo

>>> df
    id       from         to
0  878 2006-01-01 2007-10-01
1  878 2007-10-02 2008-12-01
2  878 2008-12-02 2010-04-03
3  879 2010-04-04 2199-05-11
4  879 2016-05-12 2199-12-31

>>> df['overlap'] = (df.groupby('id')
                       .apply(lambda x: (x['to'].shift() - x['from']) > timedelta(0))
                       .reset_index(level=0, drop=True))

>>> df
    id       from         to overlap
0  878 2006-01-01 2007-10-01   False
1  878 2007-10-02 2008-12-01   False
2  878 2008-12-02 2010-04-03   False
3  879 2010-04-04 2199-05-11   False
4  879 2016-05-12 2199-12-31    True