pyspark使用regex搜索关键字，然后与其他Dataframe连接

e1xvtsh3 于 2021-05-27 发布在 Spark

关注(0)|答案(3)|浏览(305)

我有两个Dataframe
Dataframea

name       groceries 
Mike       apple, orange, banana, noodle, red wine
Kate       white wine, green beans, extra pineapple hawaiian pizza
Leah       red wine, juice, rice, grapes, green beans
Ben        water, spaghetti

Dataframeb

id       item
0001     red wine
0002     green beans

我将逐行遍历b，并使用regex在dataframe a中搜索商品是否存在于杂货店中

df = None
for keyword in B.select('item').rdd.flatMap(lambda x : x).collect():
    if keyword == None:
        continue
    pattern = '(?i)^'
    start = '(?=.*\\b'
    end = '\\b)'
    for word in re.split('\\s+', keyword):
        pattern = pattern + start + word + end
    pattern = pattern + '.*$'

    if df == None:
        df = A.filter(A['groceries'].rlike(pattern)).withColumn('item', F.lit(keyword))
    else:
        df = df.unionAll(A.filter(A['groceries'].rlike(pattern)).withColumn('item', F.lit(keyword)))

我想要的输出是a中的行，其中包含b中的项，但也包含作为新列插入的item关键字

name       groceries                                                     item
Mike       apple, orange, banana, noodle, red wine                       red wine
Leah       red wine, juice, rice, grapes, green beans                    red wine
Kate       white wine, green beans, extra pineapple hawaiian pizza       green beans
Leah       red wine, juice, rice, grapes, green beans                    green beans

实际输出不是我想要的，我不明白这种方法有什么不正确。
我还想知道是否有一种方法可以使用rlike直接连接a和b，这样只有当a中的项目存在于b中的杂货店时，行才会连接。谢谢！
更复杂的数据集

test1 = spark.createDataFrame([("Mike","apple, oranges, red wine"),("Kate","Whitewine, green beans waterrr, pineapple, red wine"), ("Leah", "red wine, juice, rice, grapes, green beans"),("Ben","Water,Spaghetti, the little prince 70th anniversary gift set (book/cd/downloadable audio)")],schema=["name","groceries"])
test2 = spark.createDataFrame([("001","red wine"),("002","green beans waterrr"), ("003", "the little prince 70th anniversary gift set (book/cd/downloadable audio)")],schema=["id","item"])

# %%

test_join =test1.join(test2,F.expr("""groceries rlike item"""),how='inner').show(truncate = False)

+----+---------------------------------------------------+---+-------------------+
|name|groceries                                          |id |item               |
+----+---------------------------------------------------+---+-------------------+
|Mike|apple, oranges, red wine                           |001|red wine           |
|Kate|Whitewine, green beans waterrr, pineapple, red wine|001|red wine           |
|Kate|Whitewine, green beans waterrr, pineapple, red wine|002|green beans waterrr|
|Leah|red wine, juice, rice, grapes, green beans         |001|red wine           |
+----+---------------------------------------------------+---+-------------------+

即使有一个确切的关键字匹配“小王子70周年礼物集（书/光盘/可下载音频）”，它仍然不会匹配的结果

test1 = spark.createDataFrame([("Mike","apple, oranges, red wine"),("Kate","Whitewine, green beans waterrr, pineapple, red wine"), ("Leah", "red wine, juice, rice, grapes, green beans"),("Ben","Water,Spaghetti, the little prince 70th anniversary gift set (book/cd/downloadable audio)")],schema=["name","groceries"])
test2 = spark.createDataFrame([("001","red apple"),("002","green beans waterrr"), ("003", "the little prince 70th anniversary gift set (book/cd/downloadable audio)")],schema=["id","item"])

python apache-spark pyspark

来源：https://stackoverflow.com/questions/62767293/pyspark-seaching-keywords-with-regex-and-then-join-with-other-dataframe

3条答案

按热度按时间

q35jwt9p1#

-----如果我用regex做一个rlike来寻找“红苹果”，就像下面这样

赞(0）回复(0）举报 2021-05-27

rslzwgfq2#

test1 = spark.createDataFrame([("Mike","apple, oranges, red wine"),("Kate","Whitewine, green beans waterrr, pineapple, red wine"), ("Leah", "red wine, juice, rice, grapes, green beans"),("Ben","Water,Spaghetti, the little prince 70th anniversary gift set (book/cd/downloadable audio)")],schema=["name","groceries"])
test2 = spark.createDataFrame([("001","red apple"),("002","green beans waterrr"), ("003", "the little prince 70th anniversary gift set (book/cd/downloadable audio)")],schema=["id","item"])

test_join = test1.filter(test1['groceries'].rlike('(?i)^(?=.*\\bred\\b)(?=.*\\bapple\\b).*$'))

+----+------------------------+
|name|groceries               |
+----+------------------------+
|Mike|apple, oranges, red wine|
+----+------------------------+

它会给我我想要的，因为我只想确认所有的字，在项目中存在的杂货店，即使他们是不正常的。然而，做下面不会给我以上匹配

test_join =test1.join(test2,F.expr("""groceries rlike item"""),how='inner').show(truncate = False)
test_join =test1.join(test2,F.col('groceries').contains(F.col('item')),how='inner')

解决方案：

def my_udf(keyword):
    if keyword == None:
        return ''
    pattern = '(?i)^'
    start = '(?=.*\\b'
    end = '\\b)'
    for word in re.split('\\s+', keyword):
        pattern = pattern + start + word + end
    pattern = pattern + '.*$'
    return pattern

regex_udf = udf(my_udf, T.StringType())
B = B.withColumn('regex', regex_udf(B['item']))

regex_join = A.join(B, F.expr("""groceries rlike regex"""), how = 'inner')

它设法做到了我想要的，但仍然跑得很慢。这可能是因为join和udf的使用

赞(0）回复(0）举报 2021-05-27

xdnvmnnf3#

使用f.expr（）可以实现rlike连接。在您的情况下，您需要将其与内部连接一起使用。试试这个，


# %%

import pyspark.sql.functions as F
test1 =sqlContext.createDataFrame([("Mike","apple,greenbeans,redwine,the little prince 70th anniversary gift set (book/cd/downloadable audio)" ),("kate","Whitewine,greenbeans,pineapple"),("Ben","Water,Spaghetti")],schema=["name","groceries"])
test2 = sqlContext.createDataFrame([("001","redwine"),("002","greenbeans"),("003","cd")],schema=["id","item"])

# %%

test_join =test1.join(test2,F.expr("""groceries rlike item"""),how='inner')

结果：

test_join.show(truncate=False)
   +----+-------------------------------------------------------------------------------------------------+---+----------+
|name|groceries                                                                                        |id |item      |
+----+-------------------------------------------------------------------------------------------------+---+----------+
|Mike|apple,greenbeans,redwine,the little prince 70th anniversary gift set (book/cd/downloadable audio)|001|redwine   |
|Mike|apple,greenbeans,redwine,the little prince 70th anniversary gift set (book/cd/downloadable audio)|002|greenbeans|
|Mike|apple,greenbeans,redwine,the little prince 70th anniversary gift set (book/cd/downloadable audio)|003|cd        |
|kate|Whitewine,greenbeans,pineapple                                                                   |002|greenbeans|
+----+-------------------------------------------------------------------------------------------------+---+----------+

对于复杂的数据集，contains（）函数必须起作用

import pyspark.sql.functions as F
test1 = spark.createDataFrame([("Mike","apple, oranges, red wine,green beans"),("Kate","Whitewine, green beans waterrr, pineapple, red wine"), ("Leah", "red wine, juice, rice, grapes, green beans"),("Ben","Water,Spaghetti, the little prince 70th anniversary gift set (book/cd/downloadable audio)")],schema=["name","groceries"])
test2 = spark.createDataFrame([("001","red wine"),("002","green beans waterrr"), ("003", "the little prince 70th anniversary gift set (book/cd/downloadable audio)")],schema=["id","item"])

# %%

test_join =test1.join(test2,F.col('groceries').contains(F.col('item')),how='inner')

结果：

+----+-----------------------------------------------------------------------------------------+---+------------------------------------------------------------------------+
|name|groceries                                                                                |id |item                                                                    |
+----+-----------------------------------------------------------------------------------------+---+------------------------------------------------------------------------+
|Mike|apple, oranges, red wine,green beans                                                     |001|red wine                                                                |
|Kate|Whitewine, green beans waterrr, pineapple, red wine                                      |001|red wine                                                                |
|Kate|Whitewine, green beans waterrr, pineapple, red wine                                      |002|green beans waterrr                                                     |
|Leah|red wine, juice, rice, grapes, green beans                                               |001|red wine                                                                |
|Ben |Water,Spaghetti, the little prince 70th anniversary gift set (book/cd/downloadable audio)|003|the little prince 70th anniversary gift set (book/cd/downloadable audio)|
+----+-----------------------------------------------------------------------------------------+---+------------------------------------------------------------------------+

赞(0）回复(0）举报 2021-05-27

我来回答

pyspark使用regex搜索关键字，然后与其他Dataframe连接

3条答案

相关问题

热门标签

最新问答