I want to chart over the same x-axis which is fixed day 1 to day 31 the prices of multiple variable years of data so that it can be used for comparison
More on the data: I have 10 years of daily futures price spreads between variable ‘KE’ and ‘MW’ (for ref: the futures month on both is July or N which is fixed) .
Ideally I’d like to plot a line unique to each year of the spread (MWN-KEN) over the daily x-axis which is fixed
the issue is the ‘Date’ is staggered as some not all (N) begin at the same date and end at the same date because of calendrer changes
below is my attempt at code ( I am also a newbie to python so patience is appreciated)
df['Year'] = df['Year'].astype(int)
df['WeekNumber'] = df['Date'].dt.isocalendar().week
df['Day'] = df['Date'].dt.isocalendar().day
commodity1 = 'MW'
commodity2 = 'KE'
futures_month1 = 'N'
futures_month2 = 'N'
results = []
for year in range(2004, 2023):
commodity1_df = df[(df['Commodity'] == commodity1) & (df['FuturesMonth'] == futures_month1) & (df['Year'] == year)][['Date', 'Close']]
commodity2_df = df[(df['Commodity'] == commodity2) & (df['FuturesMonth'] == futures_month2) & (df['Year'] == year)][['Date', 'Close']]
merged_df = pd.merge(commodity1_df, commodity2_df, on=['Date'], suffixes=('_' + commodity1, '_' + commodity2)).dropna()
merged_df['Day'] = merged_df['Date'].dt.isocalendar().day
merged_df['Spread'] = merged_df['Close_' + commodity1] - merged_df['Close_' + commodity2]
merged_df.set_index('Day', inplace=True)
merged_df['Year'] = year
results.append(merged_df)
spread_df = pd.concat(results)
print(spread_df)