exclude (not like) filter using pyspark

abueno
Contributor

‎09-09-2024 07:34 PM - edited ‎09-09-2024 07:36 PM

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)

0 Kudos

brockb
Databricks Employee
Databricks Employee

‎09-09-2024 08:52 PM

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.

View solution in original post

0 Kudos

abueno
Contributor

‎09-10-2024 06:07 AM

Worked perfectly Thank you. 

0 Kudos