Pandas DateTimeIndex - Shifting over index

Question

So I'm working on some technical analysis using Pandas, however I'm struggling with the DateTimeIndex, since a lot of financial data doesn't have a consistent frequency.

I use pandas_datareader to get yahoo finance data containing DateTimeIndex, Open, Close, High, Low and Volume prices. Next I'm calculating some Dates that I want to start analysing. My problem is that once I have those dates, it's really hard for me to 'access' the values corresponding to the previous and next trading day. Shift on the dataframe only works on the dataframe itself, and won't shift the indices. Shift on a DateTimeIndex would only work with a consistent frequency.

            Open    High    Low Close   Adj Close   Volume
Date                        
2017-05-11  160.330002  160.520004  157.550003  158.539993  158.539993  5677400
2017-05-12  159.110001  160.839996  158.509995  160.809998  160.809998  5092900
2017-05-15  160.250000  161.779999  159.759995  160.020004  160.020004  4972000
2017-05-16  160.500000  161.179993  159.330002  159.410004  159.410004  3464900
2017-05-17  158.089996  158.779999  153.000000  153.199997  153.199997  8184500
2017-05-18  153.610001  156.889999  153.240005  155.699997  155.699997  6802700
2017-05-19  156.149994  158.050003  155.910004  157.020004  157.020004  4091500
2017-05-22  157.860001  158.600006  156.429993  157.160004  157.160004  3744100
2017-05-23  157.750000  158.309998  156.800003  157.949997  157.949997  3370900
2017-05-24  158.350006  158.479996  157.169998  157.750000  157.750000  2970800

So for example, given the Date 2017-05-19, I would like to be able to access the row for date 2017-05-18 as well as 2017-05-22. Not only the values, since those are still easily found using shift on the original df, but I also want to get the datetimeindex that is 'next in line'.

Any help on this problem would be greatly appreciated.

--- EDIT I had an index 'series' that contained multiple dates, and I wanted to find the 'next rows' for each date in that series.

tmp = data.iloc[8:15, :1]
print(tmp)

h, l = momentum_gaps(data)
print(h)

print( tmp.iloc[ tmp.index.get_loc[h] ] )

This code produces the output

                  Open
Date                  
2017-05-23  157.750000
2017-05-24  158.350006
2017-05-25  161.000000
2017-05-26  162.839996
2017-05-30  163.600006
2017-05-31  163.610001
2017-06-01  163.520004
DatetimeIndex(['2017-05-25', '2017-07-12', '2017-07-18'], dtype='datetime64[ns]', name=u'Date', freq=None)
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-159-a3f58efdc9d2> in <module>()
      5 print(h)
      6 
----> 7 print( tmp.iloc[ tmp.index.get_loc[h] ] )

TypeError: 'instancemethod' object has no attribute '__getitem__'

Vaishali · Accepted Answer · 2017-10-02T21:20:53.350

2

You can use get_loc and iloc

t = '2017-05-19'
req_row = df.index.get_loc(t)

Now get the slice of the dataframe

df.iloc[[req_row-1, req_row,req_row+1]]

You get

            Open        High        Low         Close       Adj_Close   Volume
Date                        
2017-05-18  153.610001  156.889999  153.240005  155.699997  155.699997  6802700
2017-05-19  156.149994  158.050003  155.910004  157.020004  157.020004  4091500
2017-05-22  157.860001  158.600006  156.429993  157.160004  157.160004  3744100

Edit: Say you have a series, get the indices in a list tmp.

tmp = df.iloc[4:8].index.tolist()

Now to get the next row for each date,

req_rows = [df.index.get_loc(t)+1 for t in tmp]
df.iloc[req_rows]

You get

            Open        High        Low         Close       Adj_Close   Volume
Date                        
2017-05-18  153.610001  156.889999  153.240005  155.699997  155.699997  6802700
2017-05-19  156.149994  158.050003  155.910004  157.020004  157.020004  4091500
2017-05-22  157.860001  158.600006  156.429993  157.160004  157.160004  3744100
2017-05-23  157.750000  158.309998  156.800003  157.949997  157.949997  3370900

edited Oct 02 '17 at 21:20

answered Oct 02 '17 at 20:48

Vaishali

37,545
5
58
86

1

Ahhhh, you beat me by 5 seconds again, with another identical answer. – cs95 Oct 02 '17 at 20:49
@cᴏʟᴅsᴘᴇᴇᴅ, would you feel better if I tell you that it happens with me all the time?:) – Vaishali Oct 02 '17 at 20:51
Aah that actually helps a bunch! So apparently I already had this, but it didn't work for me since I had a `` object with multiple datetimes and it can't convert that into one timestamp. Do you have any suggestions as to how to 'get the next date' for every element if `t` would contain multiple dates? – MrBobJamesBob Oct 02 '17 at 20:56
1

I wish I could say yes, but... ;-) – cs95 Oct 02 '17 at 20:58
1

@MrBobJamesBob An MCVE that reproduces your problem would be helpful here. – cs95 Oct 02 '17 at 20:59
I added my attempt at an MCVE to the main post, thanks for the help! – MrBobJamesBob Oct 02 '17 at 21:08
Sadly, that still tells me the TypeError `TypeError: 'instancemethod' object has no attribute '__getitem__'` for the line `req_rows = [ data.index.get_loc[t] for t in tmpdates ]` – MrBobJamesBob Oct 02 '17 at 21:34
It req_rows = [df.index.get_loc(t)+1 for t in tmp] (note the round bracket after get_loc) – Vaishali Oct 02 '17 at 21:40

Pandas DateTimeIndex - Shifting over index

1 Answers1