Get data which falls between at least one of multiple date ranges

user_3244464 0
Viewed 64

Multiple answers exist on how to extract data between two dates, but how can rows be selected if they fall within at least one of multiple start and end dates?

For the below data I would like to get all entries which are within x days of an event. For the below code lets assume x = 1

df = pd.DataFrame({"event": [True, False, False, False, True, False, False, False, False, False, True, True, False, False]},
              index=np.arange(np.datetime64('2021-01-01'),
                              np.datetime64('2021-01-15'))
              )

# DF
#             event
# 2021-01-01   True
# 2021-01-02  False
# 2021-01-03  False
# 2021-01-04  False
# 2021-01-05   True
# 2021-01-06  False
# 2021-01-07  False
# 2021-01-08  False
# 2021-01-09  False
# 2021-01-10  False
# 2021-01-11   True
# 2021-01-12   True
# 2021-01-13  False
# 2021-01-14  False


# Desired:
#             event
# 2021-01-01   True
# 2021-01-02  False
# 2021-01-04  False
# 2021-01-05   True
# 2021-01-06  False
# 2021-01-10  False
# 2021-01-11   True
# 2021-01-12   True
# 2021-01-13  False

My attemps

I assumed that intervals could be created the following way, but start and end days are identical.

intervals = [pd.Interval((x - pd.Timedelta(1, unit="days")), (x + - pd.Timedelta(1, unit="days"))) for x in df.query("event").index]

In a longer approach I used a loop to create the date ranges:

events = df.query("event").index
min_intervals = events - pd.Timedelta(1, unit="days")
max_intervals = events + pd.Timedelta(1, unit="days")

intervals = []

for i in range(len(max_intervals)):
    ##I could collect al entries here and merge again.
    
    intervals = pd.Interval(min_intervals[i]:max_intervals[i])

# [Interval('2020-12-31', '2021-01-02', closed='right'), 
#  Interval('2021-01-04', '2021-01-06', closed='right'), 
#  Interval('2021-01-10', '2021-01-12', closed='right'),
#  Interval('2021-01-11', '2021-01-13', closed='right')]

A single datetime can be compared against an interval:

df.index[0] in intervals[0] # == True

#So we should be able to create a mask right?
df.index in intervals[0] 
# ValueError: The truth value of an array with more than one element is ambiguous. Use a.any() or a.all()

Anything attempted with between, isin, query failed for me.

# With the following I could create the rows for each time range. But do I really have to loop 
# over each interval and afterward merge the data back together and take care of duplicates?
df = df.loc[min_intervals[i]:max_intervals[i]]

Edit: The above mentioned data is just an example to keep the explanation easy. My real dataset actually contains multiple entries per day, may have gaps and the time windows +-1 day might change.

The question might not have been worded properly and the example chosen is to simplistic. You might also be interested in the following question for a more general approach: Fitler dataframe by multiple date ranges

pandas python
3 Answers

Here's a pretty nifty way:

df[df['event'].rolling(3, center=True, min_periods=1).max().astype(bool)]

Output:

            event
2021-01-01   True
2021-01-02  False
2021-01-04  False
2021-01-05   True
2021-01-06  False
2021-01-10  False
2021-01-11   True
2021-01-12   True
2021-01-13  False

Timings on this data:

%timeit df[df['event'].rolling(3, center=True, min_periods=1).max().astype(bool)]

420 µs ± 6.61 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

%timeit df[df['event']|df['event'].shift()|df['event'].shift(-1)]

579 µs ± 20.3 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

user_63615310

You can use shift:

df[df['event']|df['event'].shift()|df['event'].shift(-1)]
user_163434640

While the proposed answers worked for the specific use case in the question I had a more general approach in mind.

enter image description here

import numpy as np
from numpy.random import RandomState

def filterDfByDates(df, dates_of_observations, date_range):

    """
        Extract all rows in the dataframe which fall between any date in the dates_of_observation +- date_range range
    """

    ##Build mask
    mask = np.full(df.shape[0],False)

    for query_date in dates_of_observations:
        min_day = query_date - date_range
        max_day = query_date + date_range
        mask |= ( (df.index >= min_day) & (df.index <= max_day) )

    return df[mask]


# Create sample dataset
rand = RandomState(17)
dates : np.ndarray = rand.choice(a=np.arange(np.datetime64('2021-01-01'),
                  np.datetime64('2021-01-15'), np.timedelta64(1, 'h')),size= 30, replace=True)
dates.sort()
randData = rand.choice([True, False], len(dates), p=[0.1, 0.9])
df = pd.DataFrame({"event": randData},
                  index=dates)

dates_of_obs = df.query("event").index

filterDfByDates(df,dates_of_obs, np.timedelta64(1,'D'))

I leave this question and answer up as it might help some other users in the future. For more information please see the follow up question: Fitler dataframe by multiple date ranges

user_32444640
Related