How to union DataFrames and add only missing rows?

Question

I have a dataframe df1, which contains below data:

**customer_id**   **product**   **Val_id**    **rule_name**
     1               A            1               rule1
     2               B            X               rule1

I have another dataframe df2, which contains below data:

**customer_id**   **product**   **Val_id**    **rule_name**
     1               A            1               rule2
     2               B            X               rule2
     3               C            y               rule2

rule_name values in both dataframes is always fixed

I want a new unionized dataframe df3. It should have all customers from dataframe df1 and all other customers from dataframe df2, which are not present in df1. So final df3 should look like:

**customer_id**   **product**   **Val_id**        **rule_name**
         1               A            1               rule1
         2               B            X               rule1
         3               C            y               rule2

Can anyone please help me out to achieve this outcome. Any help will be appreciated.

Jacek Laskowski Jacek Laskowski · Accepted Answer · 2017-06-03T20:26:27

Given the following datasets:

val df1 = Seq(
  (1, "A", "1", "rule1"),
  (2, "B", "X", "rule1")
).toDF("customer_id", "product", "Val_id", "rule_name")

val df2 = Seq(
  (1, "A", "1", "rule2"),
  (2, "B", "X", "rule2"),
  (3, "C", "y", "rule2")
).toDF("customer_id", "product", "Val_id", "rule_name")

And the requirement:

It should have all customers from dataframe df1 and all other customers from dataframe df2, which are not present in df1.

My first solution could be as follows:

val missingCustomers = df2.
  join(df1, Seq("customer_id"), "leftanti").
  select($"customer_id", df2("product"), df2("Val_id"), df2("rule_name"))
val all = df1.union(missingCustomers)
scala> all.show
+-----------+-------+------+---------+
|customer_id|product|Val_id|rule_name|
+-----------+-------+------+---------+
|          1|      A|     1|    rule1|
|          2|      B|     X|    rule1|
|          3|      C|     y|    rule2|
+-----------+-------+------+---------+

Another (and perhaps slower) solution could be as follows:

// find missing ids, i.e. ids in df2 that are not in df1
// BE EXTRA CAREFUL: "Downloading" all missing ids to the driver
val missingIds = df2.
  select("customer_id").
  except(df1.select("customer_id")).
  as[Int].
  collect

// filter ids in df2 that match missing ids
val missingRows = df2.filter($"customer_id" isin (missingIds: _*))

scala> df1.union(missingRows).show
+-----------+-------+------+---------+
|customer_id|product|Val_id|rule_name|
+-----------+-------+------+---------+
|          1|      A|     1|    rule1|
|          2|      B|     X|    rule1|
|          3|      C|     y|    rule2|
+-----------+-------+------+---------+

How to union DataFrames and add only missing rows?

1 Answers