On the default/fill value for *multi-key* outer joins
<p>NB: The post below is the "multi-key" counterpart of an <a href="http://stackoverflow.com/q/39748976/559827">earlier question</a> of mine.  The solutions to that earlier question work only for the case where the join is on a single key, and it is not clear to me how to generalize those solutions to the multi-key case presented below.  Since, IME, modifying an already-answered question in a way that disqualifies the answers it has received is frowned upon in SO, I'm posting this variant separately.  I have also posted a <a href="http://meta.stackoverflow.com/q/335424/559827">question</a> to Meta SO on whether I should delete this post and instead modify the original question, at the expense of invalidating its current answers.</p>

<hr>

<p>Below are teeny/toy versions of much larger/complex dataframes I'm working with:</p>

<pre><code>&gt;&gt;&gt; A
  key1 key2         u         v         w         x
0    a    G  0.757954  0.258917  0.404934  0.303313
1    b    H  0.583382  0.504687       NaN  0.618369
2    c    I       NaN  0.982785  0.902166       NaN
3    d    J  0.898838  0.472143       NaN  0.610887
4    e    K  0.966606  0.865310       NaN  0.548699
5    f    L       NaN  0.398824  0.668153       NaN

  key1 key2         y         z
0    a    G  0.867603       NaN
1    b    H       NaN  0.191067
2    c    I  0.238616  0.803179
3    d    G  0.080446       NaN
4    e    H  0.932834       NaN
5    f    I  0.706561  0.814467
</code></pre>

<p>(FWIW, at the end of this post, I provide code to generate these dataframes.)</p>

<p>I want to produce an outer join of these dataframes on the <code>key1</code> and <code>key2</code> columns, in such a way that the new positions induced by the outer join get default value 0.0.  IOW, the desired result looks like this</p>

<pre><code>  key1 key2         u         v         w         x          y         z
0    a    G  0.757954  0.258917  0.404934  0.303313   0.867603       NaN
1    b    H  0.583382  0.504687       NaN  0.618369        NaN  0.191067
2    c    I       NaN  0.982785  0.902166       NaN   0.238616  0.803179
3    d    J  0.898838  0.472143       NaN  0.610887   0.000000  0.000000
4    e    K  0.966606   0.86531       NaN  0.548699   0.000000  0.000000
5    f    L       NaN  0.398824  0.668153       NaN   0.000000  0.000000
6    d    G  0.000000  0.000000  0.000000  0.000000   0.080446       NaN
7    e    H  0.000000  0.000000  0.000000  0.000000   0.932834       NaN
8    f    I  0.000000  0.000000  0.000000  0.000000   0.706561  0.814467
</code></pre>

<p>(Note that this desired output contains some NaNs, namely those that were already present in <code>A</code> or <code>B</code>.)</p>

<p>The <code>merge</code> method gets me part-way there, but the filled-in default values are NaN's, not 0.0's:</p>

<pre><code>&gt;&gt;&gt; C = pandas.DataFrame.merge(A, B, how='outer', on=('key1', 'key2'))
&gt;&gt;&gt; C
  key1 key2         u         v         w         x         y         z
0    a    G  0.757954  0.258917  0.404934  0.303313  0.867603       NaN
1    b    H  0.583382  0.504687       NaN  0.618369       NaN  0.191067
2    c    I       NaN  0.982785  0.902166       NaN  0.238616  0.803179
3    d    J  0.898838  0.472143       NaN  0.610887       NaN       NaN
4    e    K  0.966606  0.865310       NaN  0.548699       NaN       NaN
5    f    L       NaN  0.398824  0.668153       NaN       NaN       NaN
6    d    G       NaN       NaN       NaN       NaN  0.080446       NaN
7    e    H       NaN       NaN       NaN       NaN  0.932834       NaN
8    f    I       NaN       NaN       NaN       NaN  0.706561  0.814467
</code></pre>

<p>The <code>fillna</code> method fails to produce the desired output, because it modifies some positions that should be left unchanged:</p>

<pre><code>&gt;&gt;&gt; C.fillna(0.0)
  key1 key2         u         v         w         x         y         z
0    a    G  0.757954  0.258917  0.404934  0.303313  0.867603  0.000000
1    b    H  0.583382  0.504687  0.000000  0.618369  0.000000  0.191067
2    c    I  0.000000  0.982785  0.902166  0.000000  0.238616  0.803179
3    d    J  0.898838  0.472143  0.000000  0.610887  0.000000  0.000000
4    e    K  0.966606  0.865310  0.000000  0.548699  0.000000  0.000000
5    f    L  0.000000  0.398824  0.668153  0.000000  0.000000  0.000000
6    d    G  0.000000  0.000000  0.000000  0.000000  0.080446  0.000000
7    e    H  0.000000  0.000000  0.000000  0.000000  0.932834  0.000000
8    f    I  0.000000  0.000000  0.000000  0.000000  0.706561  0.814467
</code></pre>

<p>How can I achieve the desired output efficiently?  (Performance matters here, because I intend to perform this operation on much larger dataframes than those shown here.)</p>

<hr>

<p><strong>IMPORTANT:</strong> In order to keep the example minimal, I made the multikey consist of only two columns; in practice the number of keys in a multi-key may be significantly greater.  Proposed answers should be suitable for multi-keys consisting of at least half-dozen columns.</p>

<hr>

<p>FWIW, below is the code to generate the example dataframes <code>A</code> and <code>B</code>.</p>

<pre><code>from pandas import DataFrame
from collections import OrderedDict
from random import random, seed

def make_dataframe(rows, colnames):
    return DataFrame(OrderedDict([(n, [row[i] for row in rows])
                                 for i, n in enumerate(colnames)]))

maybe_nan = lambda: float('nan') if random() &lt; 0.4 else random()

seed(0)

A = make_dataframe([['A', 'g', maybe_nan(), maybe_nan(), maybe_nan(), maybe_nan()],
                    ['B', 'h', maybe_nan(), maybe_nan(), maybe_nan(), maybe_nan()],
                    ['C', 'i', maybe_nan(), maybe_nan(), maybe_nan(), maybe_nan()],
                    ['D', 'j', maybe_nan(), maybe_nan(), maybe_nan(), maybe_nan()],
                    ['E', 'k', maybe_nan(), maybe_nan(), maybe_nan(), maybe_nan()],
                    ['F', 'l', maybe_nan(), maybe_nan(), maybe_nan(), maybe_nan()]],
                   ('key1', 'key2', 'u', 'v', 'w', 'x'))

B = make_dataframe([['A', 'g', maybe_nan(), maybe_nan()],
                    ['B', 'h', maybe_nan(), maybe_nan()],
                    ['C', 'i', maybe_nan(), maybe_nan()],
                    ['D', 'g', maybe_nan(), maybe_nan()],
                    ['E', 'h', maybe_nan(), maybe_nan()],
                    ['F', 'i', maybe_nan(), maybe_nan()]],
                   ('key1', 'key2', 'y', 'z'))
</code></pre>

<p>Set the <code>keys</code> to be the index of the two <code>DF's</code>:</p>

<pre><code>def index_set(frame, keys=['key1', 'key2']):
    frame.set_index(keys, inplace=True)
    return frame
</code></pre>

<p>Subset the <code>DF's</code> containing <code>NaN</code> values:</p>

<pre><code>def nulls(frame):
    nulls_in_frame = frame[frame.isnull().any(axis=1)].reset_index()
    return nulls_in_frame
</code></pre>

<p>Join the two <code>Df's</code>. Concatenate the joined <code>DF</code> with each of the subset of <code>NaN</code> containing <code>DF's</code> and drop the duplicated values filling the remaining <code>NaN</code> left with 0's.</p>

<p>Then, using <code>combine_first</code> to patch the values using chaining operation with the joined <code>DF</code>.</p>

<pre><code>def perform_join(fr_1, fr_2, keys=['key1', 'key2']):
    fr_1 = index_set(fr_1); frame_2 = index_set(fr_2)
    frame = fr_1.join(fr_2, how='outer').reset_index()
    cat_fr_1 = pd.concat([frame, nulls(fr_1)]).drop_duplicates(keys, keep=False).fillna(0)
    cat_fr_2 = pd.concat([frame, nulls(fr_2)]).drop_duplicates(keys, keep=False).fillna(0)
    fr_1_join = frame.combine_first(frame.fillna(cat_fr_1[fr_1.columns]))
    joined_frame = fr_1_join.combine_first(frame.fillna(cat_fr_2[fr_2.columns]))
    return joined_frame
</code></pre>

<p>Finally,</p>

<pre><code>perform_join(A, B)
</code></pre>

<p><a href="http://i.stack.imgur.com/6WWjf.png" rel="nofollow"><img src="http://i.stack.imgur.com/6WWjf.png" alt="Image"></a></p>

python pandas