CODEWITHSUNDEEP
pythonpandasdate
user14742805
user14742805

Reputation: 51

How to subset rows based on date overlap range efficiently using python pandas?

My data frame has two date type columns: start and end (yyyy-mm-dd).

Here's my data frame:

import pandas as pd
import datetime 

data=[["2016-10-17","2017-03-08"],["2014-08-17","2016-09-08"],["2014-01-01","2015-01-01"],["2017-12-20","2019-01-01"]]

df=pd.DataFrame(data,columns=['start','end'])
df['start'] =  pd.to_datetime(df['start'], format='%Y-%m-%d')
df['end'] =  pd.to_datetime(df['end'], format='%Y-%m-%d')

       start        end
0 2016-10-17 2017-03-08
1 2014-08-17 2016-09-08
2 2014-01-01 2015-01-01
3 2017-12-20 2019-01-01

And I have reference start and end date as following.

ref_start=datetime.date(2015, 9, 20)
ref_end=datetime.date(2017,1,31)
print(ref_start,ref_end)
2015-09-20 2017-01-31

I would like to subset rows if the start and end date range of a row overlaps with reference start and end date. The third and the fourth rows are not selected since the start and end date range does not overlap with reference date range (2015-09-20 ~ 2017-01-31)

So my desired outcome looks like this:

        start         end
0  2016-10-17  2017-03-08
1  2014-08-17  2016-09-08

To do that, I was thinking about using the following codes based on this: Efficient date range overlap calculation in python?

df[(max(df['start'],ref_start)>min(df['end'],ref_end))]

However, it doesn't work. Is there any way to get the desired outcome efficiently?

Upvotes: 3

Views: 315

Answers (1)

Code Different
Code Different

Reputation: 93161

A trick I learned early on in my career is what I call "crossing the dates": you compare the start of one range against the end of the other.

# pd.Timestamp can do everything that datetime/date does and some more
ref_start = pd.Timestamp(2015, 9, 20)
ref_end = pd.Timestamp(2017,1,31)

# Compare the start of one range to the end of another and vice-versa
# Made into a separate variable for reability
cond = (ref_start <= df['end']) & (ref_end >= df['start'])
df[cond]

Upvotes: 2

Related Questions