How to determine if values in a datetime column are between any pair of datetimes from another dataframe

user_15205635 0
Viewed 112

I've spent a few hours on google searching to solve this problem, but I haven't been able to find anything regarding these parameters. I want to find if a value associated with a specific datetime occurs between at least one datetime range in another data frame of a different size. Below are the example data frames:

import pandas as pd

df1 = pd.DataFrame({'Datetime': ['2020-01-01 01:01:01', '2020-01-01 10:10:10', '2020-01-01 12:10:01', '2020-01-02 03:16:24', '2020-12-01 04:34:21'], 'Value': [0.006, 0.002, 0.005, 0.034, 0.001]})
df2 = pd.DataFrame({'Start': ['2020-01-01 01:01:00', '2020-01-01 07:10:10', '2020-01-01 21:10:01', '2020-01-03 06:16:24', '2020-12-25 14:12:34'], 'End': ['2020-01-01 02:00:00', '2020-01-01 08:01:01', '2020-01-01 21:34:09', '2020-01-01 09:23:42', '2020-12-25 15:13:21']})

# convert columns to datetime format
df1.Datetime = pd.to_datetime(df1.Datetime)
df2[['Start', 'End']] = df2[['Start', 'End']].apply(pd.to_datetime)

df1

Datetime                Value     Check
2020-01-01 01:01:01     0.006     
2020-01-01 10:10:10     0.002
2020-01-01 12:10:01     0.005
2020-01-02 03:16:24     0.034
2020-12-01 04:34:21     0.001

df2

Start                   End    
2020-01-01 01:01:00     2020-01-01 02:00:00     
2020-01-01 07:10:10     2020-01-01 08:01:01
2020-01-01 21:10:01     2020-01-01 21:34:09
2020-01-03 06:16:24     2020-01-01 09:23:42
2020-12-25 14:12:34     2020-12-25 15:13:21

If df1['Value'] associated with df1['Datetime'] at index 1 is between any of the ranges in df2, the function should return True in df1['Check'] and False if the associated datetime is not between any of the ranges. This should continue to check every index in df1 against all ranges in df2.

I've tried using pd.DataFrame.any, but this throws "ValueError: Can only compare identically-labeled Series objects". I was thinking a nested for loop would be the way to go, but I am not sure how to set one up for something like this.

pandas numpy python
3 Answers

Using numpy's array broadcasting:

dt = df1['Datetime'].to_numpy()
start = df2['Start'].to_numpy()[:, None]
end = df2['End'].to_numpy()[:, None]

mask = (start <= dt) & (dt <= end)
df1['Check'] = mask.any(axis=0)
user_25389390

You can use merge_asof to associate Datetime value with the latest Start value it comes after, then filter to see if it is in the Start-End range. Below I define a dataframe with random timestamps within a 24 hr period, and the second dataframe has one hour intervals every two hours. If our dataframe is large, we expect half the rows to be within an interval, half outside them. Note this is for 1m rows, so you have an idea that this is pretty performant:

# create random time data
data = 24*np.random.random(1000000)
time_values = pd.to_datetime("2020-01-01") + pd.to_timedelta(data, unit="hour")
df = pd.DataFrame(time_values, columns=["Datetime"])
df["Value"] = data
df = df.sort_values("Datetime")

# create one hour intervals every two hours:
start_times = pd.to_datetime("2020-01-01") + pd.to_timedelta(np.arange(0,24,2), unit="hour")
df2 = pd.DataFrame(start_times, columns=["Start"])
df2["End"] = df2["Start"] + pd.to_timedelta(1, unit="hour")
df2 = df2.sort_values("Start")

# associate "Datetime" with the latest "Start" that occurs before it
df_merged = pd.merge_asof(left=df, right=df2, left_on="Datetime", right_on="Start")

# If it's within the window, "End" will come after "Datetime":
print("number of points in intervals:")
print(np.sum(df_merged.Datetime < df_merged.End))

# 499763

As we expect, about half of the random points fall within the intervals.

user_50329410 
df1.Check = df1.Datetime.apply(lambda x: ((df2.Start<=x) & (df2.End>=x)).any())

I have a feeling that the numpy broadcasting answer might in fact be faster , but apply is more pythonic. :-)

user_118465180
Related