CODEWITHSUNDEEP
scalaapache-sparkapache-spark-sql
Bamqf
Bamqf

Reputation: 3542

Filter Spark DataFrame by checking if value is in a list, with other criteria

As a simplified example, I tried to filter a Spark DataFrame with following code:

val xdf = sqlContext.createDataFrame(Seq(
  ("A", 1), ("B", 2), ("C", 3)
)).toDF("name", "cnt")
xdf.filter($"cnt" >1 || $"name" isin ("A","B")).show()

Then it errors:

org.apache.spark.sql.AnalysisException: cannot resolve '((cnt > 1) || name)' due to data type mismatch: differing types in '((cnt > 1) || name)' (boolean and string).;

What's the right way to do it? It seems to me that it stops reading after name column. Is it a bug in the parser? I'm using Spark 1.5.1

Upvotes: 50

Views: 133728

Answers (3)

Kuxha
Kuxha

Reputation: 15

We can use isInCollection for this as well now (available since version 2.4.0) Link to Documentation

The code would look like this

val filteredList = List("A","B")
xdf.filter(col("name").isInCollection(filteredList)).show()

Upvotes: 0

pschilakanti
pschilakanti

Reputation: 911

val list = List("x","y","t") 
xdf.filter($"column".isin(list: _*))

Upvotes: 80

zero323
zero323

Reputation: 330063

You have to parenthesize individual expressions:

xdf.filter(($"cnt" > 1) || ($"name" isin ("A","B"))).show()

Upvotes: 42

Related Questions