使用python在两个数据框之间检查逗号分隔的列值中的部分字符串

Question

我有两个数据。

df1= sqlContext.createDataFrame([("A001","RED, WHITE, BLUE"),("A002","RED, YELLOW"),("A003","GREEN,RED"),("A004","WHITE,YELLOW")], ["id","csv_column"])
df1.show()


df2= sqlContext.createDataFrame([("C1","RED"),("C2","WHITE"),("C3","BLUE"),("C4","YELLOW"),("C5","RED"),("C6","GREEN"),("C7","BLUE")], ["CLRCODE","COLOR"])
df2.show()

+----+----------------+ 
| id | csv_column     | 
+----+----------------+ 
|A001|RED, WHITE, BLUE| 
|A002|RED, YELLOW     | 
|A003|GREEN, RED      | 
|A004|WHITE, YELLOW   |
 +----+----------------+

+-------+-------+ 
|CLRCODE| COLOR | 
+-------+-------+ 
| C1    | RED   | 
| C2    | WHITE | 
| C3    | BLUE  | 
| C4    | YELLOW| 
| C5    | RED   | 
| C6    | GREEN | 
| C7    | BLUE  | 
+-------+-------+

+-------+
|df1.id |
+-------+
|A001   |
|A002   |
|A003   |
|A001   |
|A004   |
|A001   |
|A002   |
|A004   |
|A001   |
|A002   |
|A003   |
|A003   |
|A001   |
+-------+

我在这里签入了这个SO解决方案，但在那里dataframe逗号分隔列是根据静态字符串值进行验证的，但我需要迭代一个包含许多不同值的行的dataframe。

谢谢你的帮助。

Answer 1

您可以首先拆分和分解列DF1.csv_column，在DF2上联接之后，您可以groupby和concat_ws。例如，您可以执行以下操作：

import pyspark.sql.functions as F
df_res = (df2.join( df1.withColumn('color', F.explode(F.split('csv_column',',\s*'))),
                    on='color', how='left')
              .groupby("CLRCODE").agg(F.concat_ws(", ", F.collect_list(F.col('id'))).alias('id'))
              .orderBy('CLRCODE').drop('CLRCODE'))
df_res.show()
+----------------+
|              id|
+----------------+
|A001, A002, A003|
|      A001, A004|
|            A001|
|      A002, A004|
|A001, A002, A003|
|            A003|
|            A001|
+----------------+

编辑：对于编辑后的预期输出，需要split、explode和join部分。然后orderby和选择所需的列：

import pyspark.sql.functions as F
df_res = (df2.join( df1.withColumn('color', F.explode(F.split('csv_column',',\s*'))),
                    on='color', how='left').orderBy(*['CLRCODE', 'id']).select('id'))
df_res.show()
+----+
|  id|
+----+
|A001|
|A002|
|A003|
|A001|
|A004|
|A001|
|A002|
|A004|
|A001|
|A002|
|A003|
|A003|
|A001|
+----+