How to identify overlapping date ranges when grouping data

user_16292507 0
Viewed 142

Trying to figure out a formula to see whether two dates are overlapping for the same organization. I figure there is a way to do with

groupby('org_id').apply(lambda x: max(x.end_date1) > min(x.start_date2) & max(x.end_date2) > min(x.start_date1))

but preferably this would iterate through all dates (in the event that three dates overlap for one org).

Is there an easy way to do that?

This would be the intended outcome (in table form):

desired output

pandas overlap datetime python
1 Answers

In this answer I assume that you want to mark the entirety of an Org_ID as True if there is any overlap in dates. This approach handles situations where there are more than two date ranges to be compares.

The idea is to just start throwing the individual days for a given date range into a bucket. If one of the days that you are about to throw in the bucket is already in the bucket then you have an overlap. In which case the whole Org_ID is marked as True (has overlap).

d = [
    {'Org_ID': 'A', 'Start_Date': '6/1/2020', 'End_Date': '5/31/2022'},
    {'Org_ID': 'A', 'Start_Date': '12/1/2020', 'End_Date': '11/30/2021'},
    {'Org_ID': 'B', 'Start_Date': '6/1/2020', 'End_Date': '5/31/2021'},
    {'Org_ID': 'B', 'Start_Date': '6/1/2021', 'End_Date': '6/1/2022'},
    {'Org_ID': 'C', 'Start_Date': '1/1/2020', 'End_Date': '3/31/2020'},
    {'Org_ID': 'C', 'Start_Date': '4/1/2020', 'End_Date': '5/31/2020'},
    {'Org_ID': 'C', 'Start_Date': '6/1/2020', 'End_Date': '7/31/2020'},
    {'Org_ID': 'C', 'Start_Date': '8/1/2020', 'End_Date': '10/31/2020'},
    {'Org_ID': 'C', 'Start_Date': '11/1/2020', 'End_Date': '12/31/2021'},
    {'Org_ID': 'D', 'Start_Date': '1/1/2020', 'End_Date': '2/28/2020'},
    {'Org_ID': 'D', 'Start_Date': '3/1/2020', 'End_Date': '3/31/2020'},
    {'Org_ID': 'D', 'Start_Date': '4/1/2020', 'End_Date': '8/31/2020'},
    {'Org_ID': 'D', 'Start_Date': '8/1/2020', 'End_Date': '10/31/2020'},
    {'Org_ID': 'D', 'Start_Date': '11/1/2020', 'End_Date': '12/31/2021'},
]

df = pd.DataFrame(d)

df['Range'] = df.apply(lambda x: pd.date_range(start=x['Start_Date'], end=x['End_Date']), axis=1)

def determine_overlap(org_rng_group):
    bucket = pd.DatetimeIndex(['1/1/1900'])
    
    for x in org_rng_group:
        if x.isin(bucket).any():
            return [True]*len(org_rng_group)
        
        bucket = bucket.append(x)
        
    return [False]*len(org_rng_group)

df['Overlap'] = df.groupby('Org_ID')['Range'].transform(determine_overlap)
df.drop('Range', axis=1)

   Org_ID Start_Date    End_Date  Overlap
0       A   6/1/2020   5/31/2022     True
1       A  12/1/2020  11/30/2021     True
2       B   6/1/2020   5/31/2021    False
3       B   6/1/2021    6/1/2022    False
4       C   1/1/2020   3/31/2020    False
5       C   4/1/2020   5/31/2020    False
6       C   6/1/2020   7/31/2020    False
7       C   8/1/2020  10/31/2020    False
8       C  11/1/2020  12/31/2021    False
9       D   1/1/2020   2/28/2020     True
10      D   3/1/2020   3/31/2020     True
11      D   4/1/2020   8/31/2020     True
12      D   8/1/2020  10/31/2020     True
13      D  11/1/2020  12/31/2021     True
user_39687610
Related