Spark dataframe filter issue

Question

Coming from a SQL background here.. I'm using df1 = spark.read.jdbc to load data from Azure sql into a dataframe. I am trying to filter the data to exclude rows meeting the following criteria:

df2 = df1.filter("ItemID <> '75' AND Code1 <> 'SL'")

The dataframe ends up being empty but when i run equivalent SQL it is correct. When i change it to

df2 = df1.filter("ItemID **=** '75' AND Code1 **=** 'SL'")

it produces the rows i want to filter out.

What is the best way to remove the rows meeting the criteria, so they can be pushed to a SQL server? Thank you

could you try something like filter($"ItemIID" =!= "75" && $"Code1" =!= "SL" ) — Saša Zejnilović
– Saša Zejnilović, Commented Jun 25, 2020 at 21:30
i forgot to mention, i am doing this in python. i got invalid syntax on the $ — JD84212
– JD84212, Commented Jun 25, 2020 at 21:41

Som · Accepted Answer · 2020-06-26 03:40:03Z

2

In SQL world, <> means Checks if the value of two operands are equal or not, if values are not equal then condition becomes true.

The equivalent of it in spark sql is !=. Thus your sql condition inside filter becomes-

# A != B -> TRUE if expression A is not equivalent to expression B; otherwise FALSE
df2 = df1.filter("ItemID != '75' AND Code1 != 'SL'")

= has same meaning in spark sql as ansi sql

df2 = df1.filter("ItemID = '75' AND Code1 = 'SL'")

answered Jun 26, 2020 at 3:40

Som

6,3681 gold badge13 silver badges22 bronze badges

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

1 Comment

JD84212 Over a year ago

so i just tried converting to pandas dataframe and it is now working. pdf = df2.select("*").toPandas(), then pdf1 = pdf.loc[(pdf['ItemID']!='75') & (pdf['Code1'] != 'SL')], then convert back to spark for pushdown query df3 = spark.createDataFrame(pdf1). im not sure why the suggested syntax with "!=" above is not working.

notNull · Accepted Answer · 2020-06-25 22:01:20Z

0

Use & operator with != in pyspark.

<> deprecated from python3.

Example:

df=spark.createDataFrame([(75,'SL'),(90,'SL1')],['ItemID','Code1'])

df.filter((col("ItemID") != '75') & (col("code1") != 'SL') ).show()

#or using negation
df.filter(~(col("ItemID") == '75') & ~(col("Code1") == 'SL') ).show()

#+------+-----+
#|ItemID|Code1|
#+------+-----+
#|    90|  SL1|
#+------+-----+

edited Jun 25, 2020 at 22:01

answered Jun 25, 2020 at 21:54

notNull

31.8k4 gold badges41 silver badges58 bronze badges

Collectives™ on Stack Overflow

Spark dataframe filter issue

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related