CODEWITHSUNDEEP
scalaapache-sparkdataframe
Markus
Markus

Reputation: 3782

How to properly join two DataFrames for my case?

I use Spark 2.2.0 and Scala 2.11.8. I have some problems with joining two DataFrames.

df1 = 

product1_PK   product2_PK
111           222
333           111
...

and:

df2 = 

product_PK    product_name
111           AAA
222           BBB
333           CCC

I want to get this result:

product1_PK   product2_PK    product1_name    product2_name
111           222            AAA              BBB
333           111            CCC              AAA
...

How can I do it?

This is how I tried as a part solution, but I don't know how to efficiently make joining for both product1_PK and product2_PK and rename columns:

val result = df1.as("left")
  .join(df2.as("right"), $"left.product1_PK" === $"right.product_PK")
  .drop($"left.product_PK")
  .withColumnRenamed("right.product_name","product1_name")

Upvotes: 0

Views: 56

Answers (1)

Ramesh Maharjan
Ramesh Maharjan

Reputation: 41987

You need to use two joins : first for product1_name and second for product2_name

df1.join(df2.withColumnRenamed("product_PK", "product1_PK").withColumnRenamed("product_name", "product1_name"), Seq("product1_PK"), "left")
  .join(df2.withColumnRenamed("product_PK", "product2_PK").withColumnRenamed("product_name", "product2_name"), Seq("product2_PK"), "left")
  .show(false)

You should have your desired output as 

+-----------+-----------+-------------+-------------+
|product2_PK|product1_PK|product1_name|product2_name|
+-----------+-----------+-------------+-------------+
|222        |111        |AAA          |BBB          |
|111        |333        |CCC          |AAA          |
+-----------+-----------+-------------+-------------+

Upvotes: 1

Related Questions