Get value form Pandas DF column that is closest to datetime64

Question

I am trying to isolate a specific heading value where time closest matches a datetime64/ timedelta value.

The DF looks like this.

   heading     times
0   270.00     2016-01-20 21:39:31
1     0.00     2016-01-20 21:39:30
2   270.00     2016-01-20 21:34:15
3   293.36     2016-01-20 21:29:00
4    90.00     2016-01-20 21:28:59

I've created a timedelta, which is times - 10 mins. What i'm trying to do is to get the heading value for the row that is closest to 10 mins ago and compare it to the current heading value. Any thoughts would be appreciated.

Edited to address comment below.

The ultimate result is to compare the heading value to the closest heading value of an arbitrary timedelta (e.g. 10 mins in the past).

So for example comparing the heading value in row 1 with the heading value in row 3 because it is the closest to 10 mins in the past.

Thanks, i've been playing with `np.argwhere` using this syntax `np.argwhere(start`(start = delta object)`<'2016-01-20 23:24:31')` but it returns a list of values like `[[ 0][ 1][ 2]` any thoughts on what might be going wrong or how I use this output? — hselbie, Jan 21 '16 at 18:33
`np.where` returns all values where the delta object is less than the time you are comparing against, hence why you get a list. If you only wanted the closest time *before* the time you are comparing against you could do something like: `df.iloc[np.amax(np.where(df.index < '1/2/2015 12:00'))]` Would it be possible to include an example of what you would like to get out of the code? For instance what if a time - 10m is passed that doesn't have a value corresponding to the dataframe? — johnchase, Jan 21 '16 at 18:47
Edited the question to hopefully make it more clear what i'm after — hselbie, Jan 21 '16 at 19:03

score 0 · Answer 1 · edited May 23 '17 at 12:31

Hopefully I am able to address this question exactly as it is stated. For instance the questions says that the goal is to obtain the value that is closest to ten minutes prior (even if there is not a value close to that). I will assume that closest means closest and not within a minute ore some other arbitrary cutoff.

First I would set a DatetimeIndex from the times column

df.index = pd.DatetimeIndex(df.times)

Then create your time deltas:

time_delta = df.index -  pd.Timedelta(minutes=10)

Define a function to get the closest time from the index. This is taken directly from a previous SO answer

def nearestDate(dates, pivot):
    return min(dates, key=lambda x: abs(x - pivot))

Create a new column where the values are the values from the closest time to the ten minutes prior

df['heading2'] = [df.loc[nearestDate(df.index, e)]['heading'] for e in time_delta]

df

                        heading   times                 heading2
 2016-01-20 21:39:31    270.0     2016-01-20 21:39:31   293.6
 2016-01-20 21:39:30    0.0       2016-01-20 21:39:30   293.6
 2016-01-20 21:34:15    270.0     2016-01-20 21:34:15   90.0
 2016-01-20 21:29:00    293.6     2016-01-20 21:29:00   90.0
 2016-01-20 21:28:59    90.0      2016-01-20 21:28:59   90.0

Get value form Pandas DF column that is closest to datetime64

1 Answers1