pandas, selective join based on nearest date

user_4255748 0
Viewed 47

I have a data-frame, X, that contains the following

Index       A         B
2020-09-08  0.252167  0.263719  
2020-09-05  0.266898  0.270347
2019-09-04  0.254873  0.273878

I have another data-frame, Y, that contains the following

Index       C
2021-09-08  0.252167   
2015-09-05  0.266898

For every row in Y I want to efficiently select the nearest row in X and join them together. Here 'nearest' as function of the index, i.e: which date is closer.

In this case this should return.

Index       Index2     C          A         B
2021-09-08  2020-09-08 0.252167   0.252167  0.263719  
2015-09-05  2019-09-04 0.266898   0.254873  0.273878

(note: both indexes are datetime objects)

Since 2020-09-08 is the closest to 2021-09-08 and 2019-09-04 is the closest to 2015-09-05.

I can do this, by iterating through each index of Y and calling

X.index.get_loc(currentYIndex, "nearest")

Is there a more efficient way of doing this ?

pandas python
1 Answers

This is like what Quang's comment but need more detail

df1['Index2']=df1['Index']
Out = pd.merge_asof(df2.sort_values('Index'), 
                    df1.sort_values('Index'), 
                    on = 'Index', 
                    direction = 'nearest', 
                    allow_exact_matches = False)
Out[33]: 
       Index         C         A         B     Index2
0 2015-09-05  0.266898  0.254873  0.273878 2019-09-04
1 2021-09-08  0.252167  0.252167  0.263719 2020-09-08
user_79645270
Related