Find date in column, append adjacent column value to new column - all in one df?

Question

I have a timeseries df with 5 years of stock index prices (so df has 2 columns Date and Price. I then have a new column '3M forward date' which is just the original Date column + 3 months.

I'm then trying to create a '3M forward Price' column. This would be the corresponding price from the original Price column but at the 3M forward date.

I need to find the syntax for saying : Find the date from the 3M Forward Date column within the Date column and append the corresponding value from the original Price column toa new 3M forward price column.

I've tried variation of .loc ideas and looked through several historic questions but given my lookup value is from a df column and requiring the appending to a new column I cant figure it out.

Small section of current DF :

  DATE       Price     Target Date 1  Price on Target Date 1
            
2019-01-22  3112.80     2019-04-22     <--need to lookup this date in DATE and return Price here
2019-01-23  3112.13     2019-04-23      
2019-01-24  3126.31     2019-04-24      
2019-01-25  3163.24     2019-04-25

score 1 · Accepted Answer · answered Jan 22 '21 at 12:42

so if the columns are date, price and target_date and we want to create a new column which is target_price. what about this:

date_price_dict = df.set_index("date").to_dict()["price"]
df["target_date_price"] = df.apply(lambda x: date_price_dict.get(x["target_date"]), axis=1)

Find date in column, append adjacent column value to new column - all in one df?

1 Answers1