Add "forward" and "nearest" direction to merge_asof() · Issue #14887 · pandas-dev/pandas (original) (raw)

Currently pd.merge_asof() looks backwards, meaning that it gets the last row from the right table whose timestamp is less the left table's timestamp. It might be nice to look forwards, meaning get the first row in the right table whose timestamps is greater than the left table's timestamp. And we could have a nearest, meaning get the row from the right table whose timestamp is closest to the left table's timestamp regardless of direction.

I propose a new direction parameter whose default value is "backward", which looks for prior timestamps as pd.merge_asof() currently does. A value of "forward" will cause cause the function to look for subsequent timestamps. A value of "nearest" will look on both directions.

Here's a modified example from the docstring:

In [16]: left Out[16]: a left_val 0 1 a 1 5 b 2 10 c

In [17]: right Out[17]: a right_val 0 1 1 1 2 2 2 3 3 3 6 6 4 7 7

In [18]: pd.merge_asof(left, right, on='a') Out[18]: a left_val right_val 0 1 a 1 1 5 b 3 2 10 c 7

In [19]: pd.merge_asof(left, right, on='a', direction='forward') Out[19]: a left_val right_val 0 1 a 1.0 1 5 b 6.0 2 10 c NaN

In [20]: pd.merge_asof(left, right, on='a', direction='nearest') Out[20]: a left_val right_val 0 1 a 1 1 5 b 6 2 10 c 7