Efficient pandas between() for any of multiple values

user_6206337 0
Viewed 127

I have a pandas DataFrame looking like this

data = [["2020-01-01", "2020-01-01"], ["2020-01-02", "2020-01-04"], ["2020-01-05", "2020-01-06"]]
df = pd.DataFrame(data, columns=["START", "END"]).astype({"END": "datetime64[ns]" , "START": "datetime64[ns]"})
START END
0 2020-01-01 2020-01-01
1 2020-01-02 2020-01-04
2 2020-01-05 2020-01-06

and

a Series/numpy array of datetime64[ns] like this

timestamps = pd.Series(["2020-01-02", "2020-01-03"], dtype="datetime64[ns]")

For every row of df I want to know if there is at least one value in timestamps which lies inbetween START and END.

I can do the following

df["START"].apply(lambda x: (timestamps >= x).any()) & df["END"].apply(lambda x: (timestamps <= x).any())

resulting in [False, True, False], but is there a more performant or built-in way without using df.apply?

EDIT: Actually, my solution using apply was incorrect because if we had

timestamps = pd.Series(["2019-01-01", "2021-01-01"], dtype="datetime64[ns]")

the output would be [True, True, True], which is obviously false. However, the accepted answer does produce a correct result.

pandas python
2 Answers

We can broadcast the values in START and END columns to create a boolean mask, then reduce the resulting boolean mask along axis=1

t = timestamps.values
((df['START'].values[:, None] <= t) & (df['END'].values[:, None] >= t)).any(1)

array([False,  True, False])
user_128331660

Use IntervalIndex.overlaps if possible create Interval from timestamps by minimal and maximal values:

s = pd.IntervalIndex.from_arrays(df['START'], 
                                 df['END'], 
                                 closed='both')

i = pd.Interval(timestamps.min(), timestamps.max(), closed='both')
out = s.overlaps(i)
print (out )
[False  True False]
user_29010020
Related