My question is regarding two Pandas Dataframes with timestamp information - my left dataframe has a static timestamp, and right dataframe has a time 'window' with a start and end time. I'd like to 'left' merge these dataframes on the basis of the left timestamp falling between the start and end values in the right time window. pd.merge_asof() gets me most of the way there if I was directly comparing two timestamps - however, my time window values and durations vary.
This would be a simplified representation of the columns in question after the goal merge:
timestamp_left start_right end_right
2014-08-30 18:00:00 2014-08-30 15:00:00 2014-08-31 03:00:00
2017-03-31 15:30:00 nan nan
Writing this expression is pretty simple, but I can't seem to find any information in the pandas docs on how to call a function in a merge.
Thanks in advance.
UPDATE:
Found a solution to my problem here. This cartesian product method and subquery works because of my fairly small dataframe dimensions of [8000x30] and [50x5]. However, for larger datasets, and more complex problems, it would be really useful to merge on the basis of a function call returning boolean instead of testing for equality.
