topic exclude (not like) filter using pyspark in Get Started Discussions

exclude (not like) filter using pyspark

abueno — Tue, 10 Sep 2024 02:36:22 GMT

I am trying to exclude rows with a specific variable when querying using pyspark but the filter is not working. Similar to the "Not like" function in SQL. e.g. not like '%var4%'. The part of the code that is not working is: (col('col4').rlike('var4') == False)
Code:

%python
from pyspark.sql.functions import col

fc_run = spark.table("tbl1")

flowchart_run = fc_run.select(
'col1',
'col2',
'col3',
'col4',

).filter(
(col('col1') == 'var1') &
(col('col2').rlike('var2')) &
(col('col3').rlike('var3')) &
(col('col4').rlike('var4') == False)

)

display(flowchart_run)

Re: exclude (not like) filter using pyspark

brockb — Tue, 10 Sep 2024 03:52:56 GMT

Hi @abueno ,

To replicate a SQL `not like '%var4%'` clause in the Dataframe API, you could use `rlike` with negation using `~` such as:

df.filter(~col('col4').rlike('var4')).display()

Here's a basic reproducible example:

df = (spark.range(10).withColumn("col4", f.lit("var3"))).union( spark.range(10).withColumn("col4", f.lit("var4"))) df.filter(~col('col4').rlike('var4')).groupBy('col4').count().display() col4 count var3 10

Hope this helps.

Re: exclude (not like) filter using pyspark

abueno — Tue, 10 Sep 2024 13:07:05 GMT

Worked perfectly Thank you.