Filter pyspark dataframe based on list of strings

user_2320099 0
Viewed 1719

I am very new to the Pyspark. I hope I get an answer here. I need an answer using DataFrame API

My question is to find the number of lines in the text file test.txt that contain words “testA” or “testB” or “testC”

lines=spark.read.text("C:\test.txt")
listStr=["testA","testB","testC"]

lines.filter(lines.isin(listStr)).count()  --> this is showing all the lines in the textfile

P.S: better if it can be solved using "lambda"

pyspark apache-spark-sql apache-spark dataframe python
3 Answers

To find all rows that contain one of the strings from the list you can use the method rlike. For Example:

+----------+
|     value|
+----------+
|      text|
|text testA|
|text testB|
|text testC|
|      text|
+----------+

listStr=["testA","testB","testC"]
lines.filter(F.col('value').rlike('|'.join(listStr))).show()

Output:

+----------+
|     value|
+----------+
|text testA|
|text testB|
|text testC|
+----------+

Your solution doesn't work because the method isin tests if the cell value is equal to one of the values in the list. You can use this method only with column objects (in PySpark 3) otherwise you get the AttributeError. It will work for the following data frame:

+-----+
|value|
+-----+
| text|
|testA|
|testB|
|testC|
| text|
+-----+

listStr=["testA","testB","testC"]
lines.filter(F.col('value').isin(*listStr)).show()

Output:

+-----+
|value|
+-----+
|testA|
|testB|
|testC|
+-----+
user_89736200

You can also use like :

from functools import reduce

df.filter(
    reduce(lambda a, b: a | b, [F.col("value").like(f"%{word}%") for word in listStr])
).count()
user_13865510

If you want to use lambda function, you can use RDD:

lines.rdd.filter(lambda r: any(s in r[0] for s in listStr)).count()
user_141657300
Related