So I swear that I have run into a bug but I am hoping somebody can prove me wrong.
I can produce a Pandas DataFrame in two different formats, the one that I can't get to work is the preferred one, mentioned second. The first format looks like below:
1. open ... 8. split coefficient
date ...
1998-01-02 129.63 ... 1.0
1998-01-05 131.25 ... 1.0
1998-01-06 129.75 ... 1.0
1998-01-07 129.88 ... 1.0
1998-01-08 128.63 ... 1.0
1998-01-09 130.06 ... 1.0
1998-01-12 124.62 ... 1.0
1998-01-13 129.50 ... 1.0
1998-01-14 132.13 ... 1.0
[5292 rows x 8 columns]
I am trying to pick an row/entry whose date is closest to a specified date. I do so with the following function:
def nearest(items, pivot):
nearest_date = min(items, key=lambda x: abs(dt.strptime(x, '%Y-%m-%d') - dt.strptime(pivot, '%Y-%m-%d')))
return nearest_date
And then correctly obtain a value from the fourth cloumn corresponding to that entry:
market = (data.loc[nearest(data.index.get_values(), date)]['4. close'])
However, in the second format, my DataFrame looks something like this (with integer-based indexing):
date ... 8. split coefficient
0 1998-01-02 ... 1.0
1 1998-01-05 ... 1.0
2 1998-01-06 ... 1.0
3 1998-01-07 ... 1.0
4 1998-01-08 ... 1.0
5 1998-01-09 ... 1.0
6 1998-01-12 ... 1.0
7 1998-01-13 ... 1.0
8 1998-01-14 ... 1.0
[5292 rows x 9 columns]
So I adjust my "market" equation accordingly:
market = (data.loc[nearest(data['date'].values, date)]['4. close'])
And get this error:
KeyError: 'the label [2019-01-14] is not in the [index]'
I have tried all kinds of crazy stuff including converting the date column to pd.datetime, but have never gotten the errors. What you see is what makes sense to me and that's why it is the attempted solution going into this post. Any ideas on what the problem may be?