Python Forum
2-dataframe, datetime lookup problem
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
2-dataframe, datetime lookup problem
#1
Please see the attached screenshot:

   

What I'm trying to do is go down df['Period_End_Date'], take those dates and find the respective values in spx_prices['Adj Close'], and put that value in the a new column df['Price'] for the same date. This seems like a straightforward .loc[] or .iloc[] problem but I get a lot of KeyError or I don't even remember what else.

Here's the code so far (with a lot of extra stuff for unsuccessful debugging):

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

friday_dates = []
resultant_date = date(2001,5,25)
previous_month = 0

while resultant_date < date(2022,1,8):
    if previous_month != resultant_date.month:
        if len(friday_dates) == 0:
            friday_dates.append(resultant_date)
            previous_month = resultant_date.month
        else:
            friday_dates.append(resultant_date-timedelta(7))
            previous_month = resultant_date.month
    resultant_date = resultant_date + timedelta(7)

del friday_dates[0] #got duplicate of first date

#print(friday_dates)

df = pd.DataFrame(friday_dates,columns=['Period_End_Date'])
#df.columns[0] = 'Period End Date'
#print(df)

spx_prices_raw = yf.download('^gspc',start='2001-5-25')
spx_prices = spx_prices_raw.drop(['Open','High','Low','Close','Volume'],axis=1)

#print(spx_prices.head())

#df = pd.to_datetime(df['Period_End_Date'])  <----wrong
df['Period_End_Date'] = pd.to_datetime(df['Period_End_Date'])
print()
print('df.info() :')
print(df.info())
print('df.head() :')
print(df.head())
print()
print('spx_prices.info() :')
print(spx_prices.info())
print('spx_prices.head() :')
print(spx_prices.head())
print('Shapes of df and spx_prices are {} and {}, respectively'.format(df.shape,spx_prices.shape))
#df.head()
#print(type(spx_prices))
#for i in df['Period_End_Date']:
    #print(spx_prices.iloc[i])
    #df['Price'] = spx_prices.loc[i]
Something like the last three (commented out) lines, in particular, is my attempt to get dates from df (which has dates of last Friday for each month), look up in spx_prices (which has dates for every trading day), Thanks for any help you can give!
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Problem with datetime [SOLVED] AlphaInc 3 2,543 Apr-22-2022, 01:33 PM
Last Post: snippsat
  Filter dataframe by datetime.date column glidecode 2 4,912 Dec-05-2021, 12:51 AM
Last Post: glidecode
  Python VLookup? Lookup Table? Nu2Python 3 2,371 Oct-25-2021, 08:47 PM
Last Post: Nu2Python
  problem writing dataframe to oracle aliyesami 4 2,585 Sep-25-2021, 11:20 PM
Last Post: SamHobbs
  Problem in saving .xlsm (excel) file using pandas dataframe in python shantanu97 2 4,158 Aug-29-2021, 12:39 PM
Last Post: snippsat
  Dataframe problem stylingpat 3 2,761 Mar-22-2021, 10:36 AM
Last Post: stylingpat
  Can I replace IF statements with a key lookup ? jehoshua 3 2,460 Mar-05-2021, 10:24 PM
Last Post: jehoshua
  Error when Excelwriter saving a dataframe with datetime datatype with timezone klllmmm 3 13,075 Dec-08-2020, 11:37 AM
Last Post: Larz60+
  python 3 dns lookup private domain didact 1 2,501 Sep-19-2020, 06:01 PM
Last Post: bowlofred
  Problem with If statement and dataframe Milfredo 1 1,738 Sep-16-2020, 05:50 AM
Last Post: Milfredo

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020