I have two dataframes that have a common index: "Date".
data1 = [['0', 100], ['1', 235], ['2', 164]]
df1 = pd.DataFrame(data1, columns=['Date', 'Sales Product 1'])
data2 = [['0', 300], ['1', 105], ['2', 200]]
df2 = pd.DataFrame(data2, columns=['Date', 'Sales Product 2'])
each dataframe looks something like this:
| Date | Sales Product 1 |
|---|---|
| 0 | 100 |
| 1 | 235 |
What I want is to make a correlation matrix between the sales of product 1 and the sales of product 2 for each of the date values. Something that looks like this
| Sales product 2 | ||||
| 0 | 1 | 2 | ||
| 0 | corr_value | corr_value | corr_value | |
| Sales product 1 | 1 | corr_value | corr_value | corr_value |
| 2 | corr_value | corr_value | corr_value |
Try the following:
df_corr = pd.merge(df1, df2, on='Date')
result = df_corr.groupby('Date').corr()
Can you help me get the result I want?
