1

I already asked a similar question but was able to piece some more of it together but need more help. Determining how one date/time range overlaps with the second date/time range? I want to be able to check when two date range with start date/time and end date/time overlap. My type2 has about 50 rows while type 1 has over 500. I want to be able to take the start and end of type2 and see if it falls within type1 range. Here is a snip of the data, however the dates do change down the list from 2019-04-01 the the following days. 

    type1 type1_start                type1_end
    a    2019-04-01T00:43:18.046Z    2019-04-01T00:51:35.013Z
    b    2019-04-01T02:16:46.490Z    2019-04-01T02:23:23.887Z
    c    2019-04-01T03:49:31.981Z    2019-04-01T03:55:16.153Z
    d    2019-04-01T05:21:22.131Z    2019-04-01T05:28:05.469Z

    type2 type2_start                type2_end
    1    2019-04-01T00:35:12.061Z    2019-04-01T00:37:00.783Z
    2    2019-04-02T00:37:15.077Z    2019-04-02T00:39:01.393Z
    3    2019-04-03T00:39:18.268Z    2019-04-03T00:41:01.844Z
    4    2019-04-04T00:41:21.576Z    2019-04-04T00:43:02.071Z`

I have been googling the best way to this and have read through Determine Whether Two Date Ranges Overlap and understand how it should be done, but I don't know enough about how to call for the variables and make them work. 

#Here is what I have, but I am stuck and have no clue where to go form here:

import pandas as pd
from pandas import Timestamp
import numpy as np
from collections import namedtuple

colnames = ['type1', 'type1_start', 'type1_end', 'type2', 'type2_start', 'type2_end']
data = pd.read_csv('test.csv', names=colnames, parse_dates=['type1_start', 'type1_end','type2_start', 'type2_end'])

A_start = data['type1_start']
A_end = data['type1_end']
B_start= data['typer2_start']
B_end = data['type2_end']
t1 = data['type1']
t2 = data['type2']

r1 = (B_start, B_end)
r2 = (A_start, A_end)

def doesOverlap(r1, r2):
    if B_start > A_start:
        swap(r1, r2)
    if A_start > B_end:
        return false
    return true

It would be nice to have a csv with a result of true or false overlap. I was able to make my data run using this also Efficiently find overlap of date-time ranges from 2 dataframes but it isn't correct in the results. I added couple of rows that I know should overlap to the data, and it didn't work. I'd need for each type2 start/end to go through each type1.

Any help would be greatly appreciated.

Maria Wolfie
  • 11
  • 3

2 Answers2

0

Here is one way to do it:

import pandas as pd


def overlaps(row):
    if ((row['type1_start'] < row['type2_start'] and row['type2_start'] < row['type1_end'])
            or (row['type1_start'] < row['type2_end'] and row['type2_end'] < row['type1_end'])):
        return True

    else:
        return False


colnames = ['type1', 'type1_start', 'type1_end', 'type2', 'type2_start', 'type2_end']
df = pd.read_csv('test.csv', names=colnames, parse_dates=[
    'type1_start', 'type1_end', 'type2_start', 'type2_end'])

df['overlap'] = df.apply(overlaps, axis=1)

print('\n', df)

gives:

    type1               type1_start                 type1_end  type2               type2_start                 type2_end  overlap
0  type1               type1_start                 type1_end  type2               type2_start                 type2_end    False
1      a  2019-03-01T00:43:18.046Z  2019-04-02T00:51:35.013Z      1  2019-04-01T00:35:12.061Z  2019-04-01T00:37:00.783Z     True
2      b  2019-04-01T02:16:46.490Z  2019-04-01T02:23:23.887Z      2  2019-04-02T00:37:15.077Z  2019-04-02T00:39:01.393Z    False
3      c  2019-04-01T03:49:31.981Z  2019-04-01T03:55:16.153Z      3  2019-04-03T00:39:18.268Z  2019-04-03T00:41:01.844Z    False
4      d  2019-04-01T05:21:22.131Z  2019-04-01T05:28:05.469Z      4  2019-04-04T00:41:21.576Z  2019-04-04T00:43:02.071Z    False
Russ Brown
  • 171
  • 6
  • This is what I was going for, but I'd need each type2 to be checked against each type1. Currently it is checking against one row in type1 and type2. Any ideas? – Maria Wolfie Mar 26 '19 at 20:32
0

Below df1 contains type1 records and df2 contains type2 records:

df_new = df1.assign(key=1)\
            .merge(df2.assign(key=1), on='key')\
            .assign(has_overlap=lambda x: ~((x.type2_start > x.type1_end) | (x.type2_end < x.type1_start)))

REF: Performant cartesian product (CROSS JOIN) with pandas

jxc
  • 13,553
  • 4
  • 16
  • 34
  • what does the `x` mean on this? This didn't work either :( – Maria Wolfie Mar 26 '19 at 22:16
  • `x` is the function argument, for `assign()` function it is the merged dataframe after the cross-join. what was the error message you received? – jxc Mar 27 '19 at 01:11