我有 2 个包含以下条目的数据框: 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...|
+--------------------+--------------------+
dataframe 1 和 dataframe 2 在 joinkey1 和 joinkey2 中都有列表。 列表的元素有些相同。 我想加入这两个数据帧,条件是 joinkey1 和 joinkey2 至少有 1 个共同元素。
例如,如果 joinkey1 有 ['clickart', '950', '000', 'premier', 'image', 'pack', 'dvd', 'rom', 'broderbund'] joinkey2 有 ['clickart', '950000', 'premier', 'image', 'pack', 'dvd', 'rom']
这 2 个列表有 6 个公共(public)元素,因此连接的数据框应该添加一个包含这 6 个公共(public)元素的列。
我想知道将 2 个数据帧与列表中的条件连接起来的条件,以及如何插入仅提供公共(public)元素的列。 这必须使用 pyspark(spark 版本 2.4.0+)完成
请您参考如下方法:
下面的方法是什么?
交叉连接你的两个数据框,添加一列 array_intersect函数,然后过滤相交结果列大小 > 0 的连接数据集。
例如:
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)
