pyspark 将列与两个不同df中的字符串值列表进行比较,并返回列表之间匹配度最高的ID

jecbmhm3  于 2023-03-17  发布在  Spark
关注(0)|答案(1)|浏览(156)

我有一个pyspark Dataframe “jobs”,如下所示:

jobs=
id    position  keywords
5663123 A   ["Engineer","Quality"]
5662986 B   ['Java']
5663237 C   ['Art', 'Paint', 'Director']
5663066 D   ["Junior","Motion","Designer"]
5663039 E   ['Junior', 'Designer']
5663153 F   ["Client","Specialist"]
5663266 G   ['Pyhton']

我有另一个数据框命名为'人民'作为:

people=

pid           skills
5662321 ["Engineer","L2"]
5663383 ["Quality","Engineer","L2"]
5662556 ["Art","Director"]
5662850 ["Junior","Motion","Designer"]
5662824 ['Designer', 'Craft', 'Junior']
5652496 ["Client","Support","Specialist"]
5662949 ["Community","Manager"]

我想做的是将人员['skills']的列表值与工作['keywords']匹配
如果匹配超过2个标记,即len(list(set(A)-set(B)))〉=2,则返回列表中people ['match']的新列中的jobs ['id']表中特定作业的ID,因为可能有多个匹配,否则为None。
最终的people df应该如下所示:

people=
pid           skills                         match
5662321 ["Engineer","L2"]                    None
5663383 ["Quality","Engineer","L2"]         [5663123]
5662556 ["Art","Director"]                  [5663237]
5662850 ["Junior","Motion","Designer"]  [5663066,5663039]
5662824 ['Designer', 'Craft', 'Junior'] [5663066,5663039]
5652496 ["Client","Support","Specialist"]   [5663153]
5662949 ["Community","Manager"]              None

我目前有一个解决方案,但一点效率都没有,现在我逐行迭代spark Dataframe ,这对于一个大的df来说要花很多时间。
我对Pandas的解决方案也持开放态度。

92dk7w1h

92dk7w1h1#

这是可行的:

people.join(jobs, F.expr("size(array_intersect(skills, keywords))>=2"), "left")\
.groupBy("pid")\
.agg(F.first("skills").alias("skills"), F.collect_list("id").alias("match"))\
.withColumn("match", F.when(F.size("match")==0, None).otherwise(F.col("match")))\
.show(truncate=False)

第4行的withColumn只是将空列表更改为None,如需求中所述。
如果你遇到什么问题就告诉我。
输入:
作业DF:

+-------+--------+--------------------------+
|id     |position|keywords                  |
+-------+--------+--------------------------+
|5663123|A       |[Engineer, Quality]       |
|5662986|B       |[Java]                    |
|5663237|C       |[Art, Paint, Director]    |
|5663066|D       |[Junior, Motion, Designer]|
|5663039|E       |[Junior, Designer]        |
+-------+--------+--------------------------+

人员DF:

+-------+--------------------------+
|pid    |skills                    |
+-------+--------------------------+
|5662321|[Engineer, L2]            |
|5663383|[Quality, Engineer, L2]   |
|5662556|[Art, Director]           |
|5662850|[Junior, Motion, Designer]|
+-------+--------------------------+

输出:

+-------+--------------------------+------------------+
|pid    |skills                    |match             |
+-------+--------------------------+------------------+
|5662321|[Engineer, L2]            |null              |
|5663383|[Quality, Engineer, L2]   |[5663123]         |
|5662556|[Art, Director]           |[5663237]         |
|5662850|[Junior, Motion, Designer]|[5663066, 5663039]|
+-------+--------------------------+------------------+

相关问题