Merge/Join/Append two Pandas DataFrames with MultiIndex columns by both index and cols

dataframe, multi-index, pandas, python

Solution

try pandas.DataFrame.update

DataFrame.update(other, join='left', overwrite=True,
                 filter_func=None, raise_conflict=False)

Modify DataFrame in place using non-NA values from passed DataFrame. Aligns on indices

Problem

I have been banging my head against my desk over this one, can't figure out if there is a way, maybe I am trying something impossible. I have two DataFrames with MultiIndex columns (three levels) and time Index (single level). The first is like this: ``` border a-b c-d from a b c to b a d 2009-03-01 -0.778346 -0.928997 NaN 2009-03-02 -1.352559 1.247335 NaN 2009-03-03 -0.967939 0.432638 NaN 2009-03-04 0.786094 -2.209559 NaN 2009-03-05 -0.001338 1.084152 NaN 2009-03-06 1.163334 NaN NaN 2009-03-07 -0.587593 NaN NaN 2009-03-08 0.118469 NaN NaN 2009-03-09 NaN NaN -1.272959 2009-03-10 NaN NaN -1.207129 2009-03-11 NaN NaN 0.244019 ``` To this DF I want to add the following: ``` border a-b from a to b 2009-03-09 1.243296 2009-03-10 -0.049870 2009-03-11 1.599999 ``` Taking into account both rows and columns indices. The result should be: ``` border a-b c-d from a b c to b a d 2009-03-01 -0.778346 -0.928997 NaN 2009-03-02 -1.352559 1.247335 NaN 2009-03-03 -0.967939 0.432638 NaN 2009-03-04 0.786094 -2.209559 NaN 2009-03-05 -0.001338 1.084152 NaN 2009-03-06 1.163334 NaN NaN 2009-03-07 -0.587593 NaN NaN 2009-03-08 0.118469 NaN NaN 2009-03-09 1.243296 NaN -1.272959 2009-03-10 -0.049870 NaN -1.207129 2009-03-11 1.599999 NaN 0.244019 ``` I have tried several ways, including with Merge and Join, but can't get it to work. Any Ideas? Thanks in advance. P.S. I could post the code I used to generate the two DFs above, if that is on any help, but it's a bit long. But in any case I am looking for a generic answer, the exact name of the columns or the index for rows is irrelevant (could even be a index of integers).

Original source