关于*多键*外连接的默认/填充值

注意：下面的帖子是“多键”对应的先前的问题 https://stackoverflow.com/q/39748976/559827我的。先前问题的解决方案仅适用于连接位于单个键上的情况，并且我不清楚如何将这些解决方案推广到下面介绍的多键情况。因为，IME，以取消其收到的答案资格的方式修改一个已经回答的问题，在 SO 中是不受欢迎的，所以我单独发布这个变体。我还发布了一个question https://meta.stackoverflow.com/q/335424/559827向 Meta SO 询问我是否应该删除这篇文章并修改原始问题，但代价是使其当前答案无效。

以下是我正在使用的更大/复杂数据帧的微小/玩具版本：

>>> 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

（FWIW，在这篇文章的末尾，我提供了生成这些数据帧的代码。）

我想在这些数据帧上生成一个外部连接key1 and key2列，这样由外连接引起的新位置将获得默认值 0.0。 IOW，期望的结果看起来像这样

  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

（请注意，这个所需的输出包含一些 NaN，即那些已经存在于A or B.)

The merge方法让我到达那里，但填充的默认值是 NaN，而不是 0.0：

>>> C = pandas.DataFrame.merge(A, B, how='outer', on=('key1', 'key2'))
>>> 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

The fillna方法无法产生所需的输出，因为它修改了一些应保持不变的位置：

>>> 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

如何高效地达到预期的输出？ （性能在这里很重要，因为我打算在比此处显示的数据帧大得多的数据帧上执行此操作。）

重要的：为了使示例保持最小，我使多键仅包含两列；实际上，多钥匙中的钥匙数量可能要多得多。建议的答案应该适合由至少六列组成的多键。

FWIW，下面是生成示例数据帧的代码A and B.

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() < 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'))

Set the keys作为两者的索引DF's:

def index_set(frame, keys=['key1', 'key2']):
    frame.set_index(keys, inplace=True)
    return frame

子集化DF's含有NaN values:

def nulls(frame):
    nulls_in_frame = frame[frame.isnull().any(axis=1)].reset_index()
    return nulls_in_frame

加入两个Df's。连接已连接的DF与每个子集NaN含有DF's并删除填充剩余的重复值NaN留下0。

然后，使用combine_first使用链接操作和连接来修补值DF.

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

Finally,

perform_join(A, B)