Checking if any date in a python list of dates is between two date columns

Question

I have a dataframe with two columns. One is a START_DATE and one is an END_DATE. I also have a python list of dates. I want a third column in the dataframe that indicates if any of the dates in my python list falls between the START_DATE and END_DATE in the dataframe for each particular row. If there is a date in the dates list between the START_DATE and END_DATE, the third column should show true.

dates_list = ['2019-01-06', '2019-04-08']

START_DATE|END_DATE|
____________________
2019-01-01|2019-01-12
2019-01-03|2019-01-05
2019-04-03|2019-04-09

I want a third column showing

TRUE
FALSE
TRUE

It would be great to have in pyspark, but pandas works too.

Answer 1

This could be done using pd.IntervalIndex. Let's start by converting all dates to datetime:

from datetime import datetime
df = df.apply(pd.to_datetime)
dates = [datetime.strptime(x, '%Y-%m-%d') for x in dates_list]

Now let's build a pd.IntervalIndex using its from_arrays method, and check which intervals contain any date from the list using a list comprehension:

ix = pd.IntervalIndex.from_arrays(df['START_DATE'],df['END_DATE'],closed='both')
[any(date in i for date in dates) for i in ix]
# [True, False, True]