CODEWITHSUNDEEP
pythonpandas
John Cornwell
John Cornwell

Reputation: 369

How to get the closest single row after a specific datetime index using Python Pandas

DataFrame I have:

            A   B   C 
2012-01-01  1   2   3 
2012-01-05  4   5   6 
2012-01-10  7   8   9 
2012-01-15  10  11  12

What I am using now:

date_after = dt.datetime( 2012, 1, 7 )
frame.ix[date_after:].ix[0:1]
Out[1]: 
            A  B  C
2012-01-10  7  8  9

Is there any better way of doing this? I do not like that I have to specify .ix[0:1] instead of .ix[0], but if I don't the output changes to a TimeSeries instead of a single row in a DataFrame. I find it harder to work with a rotated TimeSeries back on top of the original DataFrame.

Without .ix[0:1]:

frame.ix[date_after:].ix[0]
Out[1]: 
A    7
B    8
C    9
Name: 2012-01-10 00:00:00

Thanks,

John

Upvotes: 19

Views: 28093

Answers (3)

Kevin Zhu
Kevin Zhu

Reputation: 2836

Couldn't resist answering this, even though the question was asked, and answered, in 2012, by Wes himself, and again in 2015, by ajsp. Yes, besides 'truncate', you can also use get_loc with the option 'backfill' to get the nearest date after the specific date. By the way, if you want to nearest date before the specific date, use 'ffill'. If you just want nearby, use 'nearest'.

df.iloc[df.index.get_loc(datetime.datetime(2016,2,2),method='backfill')]

Upvotes: 41

ajsp
ajsp

Reputation: 2670

Couldn't resist answering this, even though the question was asked, and answered, in 2012, by Wes himself. Yes, just use truncate.

df.truncate(before='2012-01-07')

Upvotes: 9

Wes McKinney
Wes McKinney

Reputation: 105471

You might want to go directly do the index:

i = frame.index.searchsorted(date)
frame.ix[frame.index[i]]

A touch verbose but you could put it in a function. About as good as you'll get (O(log n))

Upvotes: 33

Related Questions