Join condition on data frames with list as entries

Question

I have 2 dataframes with the following entries: df1-

+----------+--------------------+
|       id1|            joinKey1|
+----------+--------------------+
|b000jz4hqo|[clickart, 950, 0...|
|b0006zf55o|[ca, internationa...|
|b00004tkvy|[noah, ark, activ...|
|b000g80lqo|[peachtree, sage,...|
|b0006se5bq|[singing, coach, ...|
|b000ehpzv8|[emc, retrospect,...|
|b00021xhzw|[adobe, effects, ...|
|b000gzwjgc|[acad, upgrade, d...|
|b0000dbykm|[mia, math, adven...|
|b00029bqa2|[disney, 1st, 2nd...|
|b0007prnjo|[paper, art, gift...|
|b000aazr5i|[nfs, maestro, so...|
|b000bhl1r8|[microsoft, sql, ...|
|b000i82j80|[spy, sweeper, sp...|
|b00006hmwc|[domino, designer...|
|b000in6u62|[omnioutliner, pr...|
|b000083k56|[compaq, comp, se...|
|b00006hvvo|[upg, sgms, 1000,...|
|b0000ycfcw|[human, body, top...|
|b00066dd5m|[school, zone, pe...|
+----------+--------------------+

df2:

+--------------------+--------------------+
|                 id2|            joinKey2|
+--------------------+--------------------+
|http://www.google...|[spanish, vocabul...|
|http://www.google...|[topics, presents...|
|http://www.google...|[sierrahome, hse,...|
|http://www.google...|[adobe, cs3, prod...|
|http://www.google...|[equisys, premium...|
|http://www.google...|[quicken, r, quic...|
|http://www.google...|[sea, scene, livi...|
|http://www.google...|[autodesk, 34006,...|
|http://www.google...|[apple, garageban...|
|http://www.google...|[first, bible, st...|
|http://www.google...|[apple, apple, ma...|
|http://www.google...|[adobe, systems, ...|
|http://www.google...|[microsoft, 392, ...|
|http://www.google...|[panda, software,...|
|http://www.google...|[learn2, training...|
|http://www.google...|[family, tree, ma...|
|http://www.google...|[data, protection...|
|http://www.google...|[pencil, pal, big...|
|http://www.google...|[sos, aggregation...|
|http://www.google...|[fogware, publish...|
+--------------------+--------------------+

The dataframe 1 and dataframe 2 has list in joinkey1 and joinkey2. The elements of the list are somewhat same. I want to join these 2 dataframes with such a condition that joinkey1 and joinkey2 has at least 1 element in common.

That is for e.g if joinkey1 has ['clickart', '950', '000', 'premier', 'image', 'pack', 'dvd', 'rom', 'broderbund'] and joinkey2 has ['clickart', '950000', 'premier', 'image', 'pack', 'dvd', 'rom']

The 2 lists have 6 common elements therefore the joined data frame should add a column with these 6 common elements.

I would like to know the conditions for joining 2 dataframes with conditions on list and how to insert a column which gives only common elements. This has to be done using pyspark (spark version 2.4.0+)

And apply intersect. Or, a variation on this. How about trying first? — thebluephantom
It works with explode but I want to compare the list as it is during join.... — sk1426
Would work but the provided answer looks as it dhould be and simpler. — thebluephantom

skadya skadya · Accepted Answer · 2019-01-20T10:10:33

What's about below approach?

Cross join your both data frames, add a column with array_intersect function and then filter your joined dataset having size of intersected resultant column > 0.

For example:

df1 = spark.read  # ... Read your first source
df2 = spark.read  # ... Read your other source

from pyspark.sql import functions as fn

joined = df1.crossJoin(df2). \
    withColumn("common_join_keys", fn.array_intersect(fn.col("joinkey1"), fn.col("joinkey2")))

result = joined.filter(fn.size(fn.col("common_join_keys")) > 0)  # your condition

result.show(truncate=False)

Join condition on data frames with list as entries

1 Answers