I have a pandas dataframe df with two columns: date and price. For each row, I'd like to offset the date by 3 days, and find the price of that date and populate column price_new. Note that the dates are not necessarily in order not complete. See desired output:
df_new =
date price price_new
2021-01-01 37 N/A
2021-01-05 38 9
2021-01-06 35 42
2021-01-07 9 11
2021-01-08 11 ...
2021-01-11 42
2021-01-12 11
...
dataframe df:
import pandas as pd
import numpy as np
np.random.seed(50)
start_date = "2021-01-01"
end_date= "2021-01-31"
date_range = pd.bdate_range(start=start_date,end=end_date)
df = pd.DataFrame({'date':date_range, 'price':np.random.randint(5, 50, len(date_range))})
IIUC, you can first resample to make sure you have daily and then use shift():
new_df = df.set_index("date").resample("D").last().reset_index()
new_df["price_new"] = new_df["price"].shift(-3)
new_df = new_df[new_df["date"].isin(df["date"])].reset_index(drop=True)
>>> new_df
date price price_new
0 2021-01-01 37.0 NaN
1 2021-01-05 38.0 11.0
2 2021-01-06 35.0 NaN
3 2021-01-07 9.0 NaN
4 2021-01-08 11.0 42.0
5 2021-01-11 42.0 NaN
6 2021-01-12 11.0 NaN
df used:
>>> df
date price
0 2021-01-01 37
1 2021-01-05 38
2 2021-01-06 35
3 2021-01-07 9
4 2021-01-08 11
5 2021-01-11 42
6 2021-01-12 11