Skip to main content
 首页 » 编程设计

python之以列表作为条目的数据框的连接条件

2024年12月31日7wayfarer

我有 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)