I'm trying to find a simple way to merge two MultiIndex dataframes together in a way that adds new columns and merges existing. For example if I had two data frames
d1_columns = pd.MultiIndex.from_product([["A", "B",], ["1", "2"]])
d1_index = pd.date_range("2020-01-01", "2020-01-5", freq="D")
d1 = pd.DataFrame(random.rand(5, 4), columns=d1_columns, index=d1_index)
print(d1)
A B
1 2 1 2
2020-01-01 0.381909 0.487480 0.389250 0.853449
2020-01-02 0.752374 0.508806 0.491892 0.786918
2020-01-03 0.019655 0.537763 0.263242 0.378302
2020-01-04 0.460276 0.227113 0.423472 0.514639
2020-01-05 0.046673 0.864282 0.223340 0.929049
d2_columns = pd.MultiIndex.from_product([["B", "C"], ["1", "2"]])
d2_index = pd.date_range("2020-01-03", "2020-01-7", freq="D")
d2 = pd.DataFrame(random.rand(5, 4), columns=d2_columns, index=d2_index)
print(d2)
B C
1 2 1 2
2020-01-03 0.495979 0.888207 0.776861 0.531693
2020-01-04 0.408030 0.545351 0.452913 0.768284
2020-01-05 0.374996 0.593571 0.925979 0.398629
2020-01-06 0.085565 0.845354 0.792325 0.501057
2020-01-07 0.780985 0.390948 0.731769 0.488155
If I want to merge them I get the overlapping columns seperated, while the new columns work fine:
df = d1.merge(d2, left_index=True, right_index=True, how="outer")
print(df)
A B_x B_y \
1 2 1 2 1 2
2020-01-01 0.381909 0.487480 0.389250 0.853449 NaN NaN
2020-01-02 0.752374 0.508806 0.491892 0.786918 NaN NaN
2020-01-03 0.019655 0.537763 0.263242 0.378302 0.495979 0.888207
2020-01-04 0.460276 0.227113 0.423472 0.514639 0.408030 0.545351
2020-01-05 0.046673 0.864282 0.223340 0.929049 0.374996 0.593571
2020-01-06 NaN NaN NaN NaN 0.085565 0.845354
2020-01-07 NaN NaN NaN NaN 0.780985 0.390948
C
1 2
2020-01-01 NaN NaN
2020-01-02 NaN NaN
2020-01-03 0.776861 0.531693
2020-01-04 0.452913 0.768284
2020-01-05 0.925979 0.398629
2020-01-06 0.792325 0.501057
2020-01-07 0.731769 0.488155
Is there a simple way to have the overlapping columns merge so that new data is added to the existing columns (it doesn't matter if it overwrites previous data), so the output looks like this?
A B C
1 2 1 2 1 2
2020-01-01 0.633182 0.335651 0.072520 0.578472 NaN NaN
2020-01-02 0.785482 0.562421 0.658556 0.557171 NaN NaN
2020-01-03 0.755049 0.575611 0.592934 0.735094 0.647117 0.306296
2020-01-04 0.035943 0.792211 0.002617 0.159366 0.320691 0.825184
2020-01-05 0.932623 0.643129 0.778002 0.581527 0.718405 0.289289
2020-01-06 NaN NaN 0.085565 0.845354 0.012412 0.960234
2020-01-07 NaN NaN 0.780985 0.390948 0.444406 0.210821
Thanks