Range of Dates in Pandas df.

Question

I have some dates in index, and other in col2, I am trying to find a way to find the 2 closest 'bracket' dates from col2 in my col1. Here is an example.

[In]Dates
[Out]
  Index           col2
2065-12-20     2062-12-20
2061-10-31     2049-11-19
2045-11-28     2020-09-08 
2043-10-31     2053-11-19 
2040-07-30     2038-06-06
2049-06-30     2019-05-12 
2036-01-31     2040-11-21

now I want for each date on my col 2, the closest superior date present in my index and the closest inferior date present in my index too for each date of my col2.

[In] Find Bracket
[Out] 
Index           col2           High bracket       low bracket  
2065-12-20     2062-12-20       2065-12-20         2061-10-31
2061-10-31     2049-11-19       2061-10-31         2045-11-28
2045-11-28     2020-09-08       2020-09-08         2020-09-08
2043-10-31     2053-11-19       2061-10-31         2049-06-30
2040-07-30     2038-06-06       2040-07-30         2036-01-31
2049-06-30     2019-05-12       2036-01-31         2019-05-12
2036-01-31     2040-11-21       2043-10-31         2040-07-30

for the first line for example. 2065-12-20 is the closest higher date in index from 2062-12-20 (col2) and the closest lower one present in index is 2061-10-31 etc etc...

I am strugling with that... I understand I need to use argmin() and substract index and col2, but in that way I just can find one, not higher and lower together, that's where is my stuggle...

Thank you!

it is just what I understand I need to do, its the last part of my code. I obtained Index and col2. I dont use argmin now because I know its a dead end... I cant figure out what to use exactly... — user6457870, Apr 28 '17 at 09:13
Maybe this can help you: http://stackoverflow.com/questions/30112202/how-do-i-find-the-closest-values-in-a-pandas-series-to-an-input-number — Roelant, Apr 28 '17 at 09:36

score 1 · Accepted Answer · answered Apr 28 '17 at 10:45

def find_closest(x, index_col):
min_diff = x - index_col.max()
last_val = index_col.max()
for val in list(index_col):
    current_diff = x - val
    if current_diff.days > min_diff.days and current_diff.days <= 0:
        min_diff = current_diff
        last_val = val
return last_val

apply this function on the col2 to find the high bracket, similarly you can do this for low_bracket.

From my understanding point of view, your expected output for 2020-09-08 in high_bracket should be 2036-01-31.

Note: To achieve this, I have converted index column to normal column.

Range of Dates in Pandas df.

1 Answers1