如何在 Scala 中给定特定条件的情况下组合两个数据框中的行?

Question

我有两个数据框，称为：

表1

+---------+------------+------+
|  Animal |    Owner   |count1|
+---------+------------+------+
|    Cat  |     Bob    |   3  |
|    Fish |    Jerry   |   2  |
|    Dog  |     Bob    |   2  |
|  Turtle |     Joe    |   5  |
+---------+------------+------+

表2

+---------+------------+------+
|  Animal |    Owner   |count2|
+---------+------------+------+
|    Cat  |     Bob    |   2  |
|    Fish |     Jerry  |   1  |
|    Dog  |     Bob    |   3  |
|   Snake |     Kim    |   6  |
+---------+------------+------+

我正在尝试以某种方式组合这两个数据框，以便下面的新数据框将包含行

• 出现在"表1"或表2中
在
• 两个表中找到的不同行包含的计数值在"table2"中大于在"table1"中

这是我想要生成的预期输出数据框。

+---------+------------+------+------+
|  Animal |   Owner    |count1|count2|
+---------+------------+------+------+
|   Dog   |     Bob    |   2  |   3  |
|  Turtle |     Joe    |   5  | null |
|   Snake |     Kim    | null |   6  |
+---------+------------+------+------+

出现在"table1"而不是"table2">

(或"table2"而不是"table1"(中的行的计数值可以为"null"。

Answer 1

在 Spark 中尝试完全连接filter条件

scala> var t1 = Seq(("Cat","Bob",3),  ("Fish" ,"Jerry" ,2),  ("Dog"  ,    "Bob",2),  ("Turtle" ,"Joe",5)).toDF("Animal","Owner","count1")
scala> var t2 = Seq(("Cat", "Bob",2),("Fish","Jerry",1),("Dog" ,"Bob",3),("Snake","Kim",6)).toDF("Animal","Owner","count2")

在数据帧 t1(表1(和 t2(表2(中，应用full join并保留表中两个计数列的空行。

scala> t2.join(t1,Seq("Animal","Owner"),"full").filter(col("count2")>col("count1") || col("count2").isNull || col("count1").isNull).show
+------+-----+------+------+
|Animal|Owner|count2|count1|
+------+-----+------+------+
|   Dog|  Bob|     3|     2|
| Snake|  Kim|     6|  null|
|Turtle|  Joe|  null|     5|
+------+-----+------+------+