python - 在 python 中合并两个表(使用 pandas)，其中链接取决于唯一性

Question

假设我有两张 table

import pandas as pd
import numpy as np

first_table = pd.DataFrame({'key1': [1, 2, 2, 2, 3, 3],
                            'key2': ['a', 'a', 'a', 'b', 'a', 'b'],
                            'key3': ['A', 'A', 'B', 'A', 'A', 'A'],
                            'value_first': range(6)})
second_table = pd.DataFrame({'key1': [1, 1, 2, 2, 3],
                            'key2': [np.nan, np.nan, 'a', 'a', 'b'],
                            'key3': [np.nan, np.nan, 'A', 'B', np.nan],
                            'value_second': [6, 4, 2, 0, -2]})

所以第一个表看起来像这样

   key1 key2 key3  value_first
0     1    a    A            0
1     2    a    A            1
2     2    a    B            2
3     2    b    A            3
4     3    a    A            4
5     3    b    A            5

而第二个表看起来像这样

   key1 key2 key3  value_second
0     1  NaN  NaN             6
1     1  NaN  NaN             4
2     2    a    A             2
3     2    a    B             0
4     3    b  NaN            -2

现在我想要基于三个键对 first_table 和 second_table 进行外部合并。请注意，第二个表根据三个键不是唯一的，但第一个表是唯一的。因此，当 key1 在第一个表中唯一时，key2 和 key3 不是必需的。同样，当前两个键的组合是唯一的时，key3 也不是必需的。

如果第二个表被正确填写，那么合并将很简单

pd.merge(first_table, second_table, 
         how='outer', 
         left_on=['key1', 'key2', 'key3'], 
         right_on=['key1', 'key2', 'key3'])

但是在这种情况下如何获得想要的结果呢？期望的结果如下所示

   key1 key2 key3  value_first  value_second
0   1    a    A          0.0           6.0
1   1    a    A          0.0           4.0
2   2    a    A          1.0           2.0
3   2    a    B          2.0           0.0
4   2    b    A          3.0           NaN
5   3    a    A          4.0           NaN
6   3    b    A          5.0          -2.0

Answer 1

这个想法是首先合并 key1 上的数据帧，然后用第一个表中的相应值填充 NaN，然后在值不同时删除行，最后再次与第一个表合并以获取剩余行(其中 value_second=NaN)。

df = pd.merge(first_table, second_table, left_on=['key1'], right_on=['key1'], how='outer')
df['key2_y'] = df['key2_y'].fillna(df['key2_x'])
df['key3_y'] = df['key3_y'].fillna(df['key3_x'])
df = df[(df['key2_x'] == df['key2_y']) & (df['key3_x'] == df['key3_y'])]
df.drop(['key2_y', 'key3_y'], axis=1, inplace=True)
df = pd.merge(first_table, df, left_on=['key1', 'key2', 'key3', 'value_first'], 
              right_on=['key1', 'key2_x', 'key3_x', 'value_first'], how='outer')
df.drop(['key2_x', 'key3_x'], axis=1, inplace=True)

       key1 key2 key3  value_first  value_second
0     1    a    A            0           6.0
1     1    a    A            0           4.0
2     2    a    A            1           2.0
3     2    a    B            2           0.0
4     2    b    A            3           NaN
5     3    a    A            4           NaN
6     3    b    A            5          -2.0