Filter a DataFrame by Array Column

Question

I want to filter a dataframe which has a column with categories (List[String]). I want to ignore all the rows that have a non valid category. They are not valid when they are not in model.getCategories

def checkIncomingData(model: Model, incomingData: DataFrame) : DataFrame = {
  val list = model.getCategories.toList
  sc.broadcast(list)
  incomingData.filter(incomingData("categories").isin(list))
}

Unfortunately my approach does not work because categories is a list, not a single element. Any idea who to make it work?

Alberto Bonsanto · Accepted Answer · 2016-10-25 17:37:49Z

3

The first problem I see is that you didn't assign the broadcast to a variable.

val broadcastList = sc.broadcast(list)

Besides you have to reference it using broadcastList.value. For instance:

incomingData.filter($"categories".isin(broadcastList.value: _*))

NOTE @LostInOverflow made an important contribution, he clarified my answer and said that the method isin is actually evaluated in the driver, so broadcasting the list doesn't help at all, and more important the list shall be expanded in order to be evaluated.

edited Oct 25, 2016 at 17:37

answered Oct 25, 2016 at 15:33

Alberto Bonsanto

18.1k10 gold badges67 silver badges93 bronze badges

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

6 Comments

user6022341 Over a year ago

broadcast has no effect here.

Alberto Bonsanto Over a year ago

Excuse me what? I tried to keep my answer in the dame context of the question

user6022341 Over a year ago

Think about the order of evaluation. Argument of isin is evaluated eagerly on the driver. It is not different than lncomingData.filter($"categories".isin(list))

Alberto Bonsanto Over a year ago

Are you totally sure? Because I think this might be parallelized, if each node has the list (broadcasted), it could help to boost the performance. Maybe I'm wrong

user6022341 Over a year ago

I am sure. I upvoted because it is useful note but it doesn't resolve the problem. Please check my answer.

|

user6022341 · Accepted Answer · 2016-10-25 17:29:39Z

1

Just expand list:

incomingData.filter(incomingData("categories").isin(list: _*))

Note: broadcasting won't help you here. This is evaluated on driver.

answered Oct 25, 2016 at 17:29

community wiki

user6022341

Collectives™ on Stack Overflow

Filter a DataFrame by Array Column

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related