Using .loc on DatetimeIndex to retrieve a value on a specific date (KeyError

user_7554320 0
Viewed 1161

I am trying to retrieve a specific value using .loc on a dataframe. This used to work, but I've upgraded to most recent version of Pandas and it is no longer working. See the sample data and code below, anyone know what's going on here?

Sample Data

            Open        High        Low         Close       Volume
Date                    
2020-09-24  2906.500000 2962.000000 2871.000000 2960.469971 6117900
2020-09-25  3033.840088 3133.989990 3000.199951 3128.989990 6948800

Code

import pandas as pd
from datetime import date, timedelta
import datetime

yesterday = date.today() - timedelta(3)
symbol_data.loc[yesterday]['Close']

In the past it would retrieve the value "3128.989990", which is the Close value on 2020-09-25.
Now I get "KeyError: datetime.date(2020, 9, 25)".

When I look at the index, it shows DatetimeIndex(['2020-09-24', '2020-09-25'], dtype='datetime64[ns]', name='Date', freq=None)

If I pass the string value, it works. But I need to use my variable to calculate a date.

symbol_data.loc['2020-09-25']['Close']  ##this works, but I don't want to use a hard coded date
python-datetime pandas python
2 Answers

Recent pandas version doesn't allow .loc and .at slicing by python dattetime object. I got hit by it, so I knew. You need to convert it to pandas Timestamp or use string as you already discovered. To wrap it to pandas Timestamp, just pass the variable to pd.Timestamp

In [44]: print(df.loc[pd.Timestamp(date.today() - timedelta(3)), 'Close'])

Output:

3128.98999
user_101892140

Similar to what Trenton suggested, but chained with normalize to get the exact date. Also, try to avoid index chaining when possible

yesterday = pd.Timestamp.now().normalize() - pd.Timedelta(days=3)
df.loc[yesterday, 'Close']
# out
# 3128.98999
user_42384080
Related