python - 从源表中获取未合并的数据

Question

我有两个数据框:

df1 = (data = {'Invoice' : [1, 2, 3, 4, 5],
                            'Value' : [10, 25, 40, 10, 15]})

     Invoice    Value  Param
0        1     10      0
1        2     25      0
2        3     40      0
3        4     10      0
4        5     15      0

df2 = (data = {'Invoice' : [2, 3, 5, 2],
                'Value' : [25, 15, 15,25],
                'TestData': ["A",'B','C','D']})

   Invoice    TestData  Value  Param
0        2        A     25      0
1        3        B     15      0
2        5        C     15      0
3        2        D     25      1

我不想将 df2 中的两次发票 2 与 df1 中的单次发票合并，因此:

df1["Param"]=df1.groupby(["Invoice","Value"]).cumcount()
df2["Param"]=df2.groupby(["Invoice","Value"]).cumcount()

合并后:

df3 = (df1,df2, left_on=["Invoice","Value","Param"], right_on=["Invoice","Value","Param"])`

有最终的、合并的数据框:

     Invoice    Value   Param TestData 
0     2      25      0      A 
1     5      15      0      C

现在我想从 df1 获取未合并的数据:

df1[(~df1.Invoice.isin(df3.Invoice))&(~df1.Value.isin(df3.Value))]`

对于 df1 来说效果很好:

    Invoice Value   Param
0   1     10      0
2   3     40      0
3   4     10      0

但 df2 失败，结果为空数据帧:

df2[(~df2.Value.isin(df3.Value))&(~df2.Invoice.isin(df3.Invoice))]`

据我检查，我认为比较运行“两次”而不是一次(&运算符) - 首先检查每个发票的代码编号，然后再次独立检查值而不是检查同时满足两个条件。

你知道如何以这种形状取消合并 df2 吗:

         Invoice   TestData   Value   Param
1        3        B     15      0
3        2        D     25      1

Answer 1

更新为合并添加了“Param”

要从两个数据帧中查找“未合并”的数据，一种方法是使用 how='outer' 和 indicator=True。这将向您显示一个数据框，其中的列 _merge 具有三个值，“both”表示合并成功，“left_only”表示来自 df1 的“未合并”数据，“right_only”表示来自 df2 的“未合并”数据.

示例:

df1 = pd.DataFrame(data = {'Invoice' : [1, 2, 3, 4, 5],
                            'Value' : [10, 25, 40, 10, 15]})

df2 = pd.DataFrame(data = {'Invoice' : [2, 3, 5, 2],
                'Value' : [25, 15, 15,25],
                'TestData': ["A",'B','C','D']})
df1["Param"]=df1.groupby(["Invoice","Value"]).cumcount()
df2["Param"]=df2.groupby(["Invoice","Value"]).cumcount()

df3 = pd.merge(df1,df2, left_on=["Invoice","Value","Param"], 
               right_on=["Invoice","Value","Param"],
               how='outer', indicator=True)

df3

输出:

   Invoice  Value  Param TestData      _merge
0        1     10      0      NaN   left_only
1        2     25      0        A        both
2        3     40      0      NaN   left_only
3        4     10      0      NaN   left_only
4        5     15      0        C        both
5        3     15      0        B  right_only
6        2     25      1        D  right_only

要获取内连接完整合并数据:

df3.query('_merge == "both"')

输出:

   Invoice  Value  Param TestData _merge
1        2     25      0        A   both
4        5     15      0        C   both

从 df1 获取“未合并”数据

df3.query('_merge == "left_only"')

   Invoice  Value  Param TestData     _merge
0        1     10      0      NaN  left_only
2        3     40      0      NaN  left_only
3        4     10      0      NaN  left_only

并且，获取“来自 df2 的未合并数据

df3.query('_merge == "right_only"')

   Invoice  Value  Param TestData      _merge
5        3     15      0        B  right_only
6        2     25      1        D  right_only