I have two multi-level column dataframes:
data = {
'A': {
'X': {'Value1': 2, 'Value2': 1},
'Y': {'Value1': 2, 'Value2': 3}
},
'B': {
'X': {'Value1': 10, 'Value2': 11},
'Y': {'Value1': 10, 'Value2': 11}
}
}
df = pd.DataFrame(data)
Which looks like this...
Group A B
Subgroup X Y X Y
Metric Value1 Value2 Value1 Value2 Value1 Value2 Value1 Value2
2023-01-01 2 1 2 3 10 11 10 11
2023-01-02 2 1 2 3 10 11 10 11
2023-01-03 2 1 2 3 10 11 10 11
2023-01-04 2 1 2 3 10 11 10 11
2023-01-05 2 1 2 3 10 11 10 11
df2:
data = {
'A': {'Value1': [3, 3, 1, 3, 3], 'Value2': [5, 2, 2, 2, 2]},
'B': {'Value1': [3, 4, 7, 3, 3], 'Value2': [2, 2, 7, 2, 2]}
}
df_2 = pd.DataFrame(data, index=pd.to_datetime(['2023-01-01',
'2023-01-02', '2023-01-03', '2023-01-04', '2023-01-05']))
# Convert to constructor
df2 = df_2.unstack().unstack()
Which looks like this...
Group A B
Metric Value1 Value2 Value1 Value2
2023-01-01 3 5 3 2
2023-01-02 3 2 4 2
2023-01-03 1 2 7 7
2023-01-04 3 2 3 2
2023-01-05 3 2 3 2
and would like to add df2 to df1, but for each combination of Group and Metric, add across each Subgroup that match to look like this...
Group A B
Subgroup X Y X Y
Metric Value1 Value2 Value1 Value2 Value1 Value2 Value1 Value2
2023-01-01 5 6 5 5 13 13 13 13
2023-01-02 5 3 6 5 14 15 14 13
2023-01-03 3 3 9 10 17 18 17 18
2023-01-04 5 3 5 5 13 14 13 13
2023-01-05 5 3 5 5 13 14 13 13
Any help would be appreciated.
Some ideas like merging but I think i lose the middle subgroup level above but could have been doing it incorrectly.
I think the easiest might be to droplevel on df1, then add, finally recreate the DataFrame:
pd.DataFrame(df1.droplevel('Subgroup', axis=1).add(df2).to_numpy(),
index=df1.index, columns=df1.columns)
Alternatively, reindex df2 and convert to numpy:
df1 += df2.reindex_like(df1.droplevel('Subgroup', axis=1)).to_numpy()
Output:
Group A B
Subgroup X Y X Y
Metric Value1 Value2 Value1 Value2 Value1 Value2 Value1 Value2
2023-01-01 5 5 6 8 13 13 13 13
2023-01-02 5 5 3 5 14 14 13 13
2023-01-03 3 3 3 5 17 17 18 18
2023-01-04 5 5 3 5 13 13 13 13
2023-01-05 5 5 3 5 13 13 13 13