Pyspark: filter dataframe by regex with string formatting?

Question

I've read several posts on using the "like" operator to filter a spark dataframe by the condition of containing a string/expression, but was wondering if the following is a "best-practice" on using %s in the desired condition as follows:

input_path = <s3_location_str>
my_expr = "Arizona.*hot"  # a regex expression
dx = sqlContext.read.parquet(input_path)  # "keyword" is a field in dx

# is the following correct?
substr = "'%%%s%%'" %my_keyword  # escape % via %% to get "%"
dk = dx.filter("keyword like %s" %substr)

# dk should contain rows with keyword values such as "Arizona is hot."

Note

I'm trying to get all rows in dx that contain the expression my_keyword. Otherwise, for exact matches we wouldn't need surrounding percent signs '%'.

Tshilidzi Mudau · Accepted Answer · 2019-07-05 12:40:16Z

48

From neeraj's hint, it seems like the correct way to do this in pyspark is:

expr = "Arizona.*hot"
dk = dx.filter(dx["keyword"].rlike(expr))

Note that dx.filter($"keyword" ...) did not work since (my version) of pyspark didn't seem to support the $ nomenclature out of the box.

edited Jul 5, 2019 at 12:40

Tshilidzi Mudau

7,9496 gold badges39 silver badges51 bronze badges

answered Aug 9, 2017 at 21:10

Quetzalcoatl

2,1464 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ethan Wicker Over a year ago

I'd recommend using implicit column selection, as opposed to referencing dx twice. e.g., dk = dk.filter(F.col("keyword").rlike(expr)). This is recommended per the Palantir PySpark Style Guide, as it makes the code more portable (you don't have to update dk in both locations). For clarity, you'll need from pyspark.sql import functions as F.

David Arenburg · Accepted Answer · 2017-08-09 21:12:50Z

13

Try rlike function as mentioned below.

df.filter(<column_name> rlike "<regex_pattern>")

for example.

dk = dx.filter($"keyword" rlike "<pattern>")

edited Aug 9, 2017 at 21:12

David Arenburg

92.4k18 gold badges145 silver badges202 bronze badges

answered Aug 9, 2017 at 14:26

Neeraj Bhadani

3,14021 silver badges28 bronze badges

1 Comment

Dileep Kumar Patchigolla Over a year ago

Is this Scala? Pyspark doesn't seem to support col rlike expr syntax.

Dheeraj Inampudi · Accepted Answer · 2019-05-08 10:09:27Z

8

I used the following for the timestamp regex

expression = r'[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1]) (2[0-3]|[01][0-9]):[0-5][0-9]:[0-5][0-9]'
df1 = df.filter(df['eta'].rlike(expression))

answered May 8, 2019 at 10:09

Dheeraj Inampudi

1,46717 silver badges13 bronze badges

Collectives™ on Stack Overflow

Pyspark: filter dataframe by regex with string formatting?

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related