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

Question

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

Wink

Answer 1

You have to parenthesize individual expressions:

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

Stich

can we have a regex in the list so if you want "name" to contains the values ["ABC", "DVA"] so it should get all rows that have those letters in the name in any position of the full name? – Mechellemechlin

Answer 2

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

Maxentia

what does the :_* do? – Polonium

It's Scala annotation feature. – Maxentia

what does that mean? – Polonium

@WalrustheCat it converts the list into variable number of arguments. – Tellurian

I believe that _: * is referred to as "splat" (alvinalexander.com/bookmarks/scala/…) – Resort

Is there a python version of this annotation? – Endplay

how about not in list? – Hi

@Endplay yes, you can unpack in python using the character *. More here: stackabuse.com/unpacking-in-python-beyond-parallel-assignment – Oversell

Answer 3

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()

Hollenbeck