Spark MLlib - SQLContext - Group items based on top 3 value
<p>I'm trying to make some Basket Market Analysis using Spark MLlib with this dataset:</p>

<pre><code>Purchase_ID Category    Furnisher   Value
1   ,   A   ,   1   ,   7
1   ,   B   ,   2   ,   7
2   ,   A   ,   1   ,   1
3   ,   C   ,   2   ,   4
3   ,   A   ,   1   ,   4
3   ,   D   ,   3   ,   4
4   ,   D   ,   3   ,   10
4   ,   A   ,   1   ,   10
5   ,   E   ,   1   ,   8
5   ,   B   ,   3   ,   8
5   ,   A   ,   1   ,   8
6   ,   A   ,   1   ,   3
6   ,   B   ,   1   ,   3
6   ,   C   ,   5   ,   3
7   ,   D   ,   3   ,   4
7   ,   A   ,   1   ,   4
</code></pre>

<p>The transaction value (value) is grouped by each Purchase_ID. And what I want is just return the top 3 categories ​​with higher Value. Basically, I want to return this dataset:</p>

<pre><code>D,A
E,B,A
A,B
</code></pre>

<p>For that I'm trying with the following code:</p>

<pre><code>val data = sc.textFile("PATH");

case class Transactions(Purchase_ID:String,Category:String,Furnisher:String,Value:String);

def csvToMyClass(line: String) = {
val split = line.split(',');
Transactions(split(0),split(1),split(2),split(3))}

val df = data.map(csvToMyClass)
        .toDF("Purchase_ID","Category","Furnisher","Value")
        .(select("Purchase_ID","Category") FROM (SELECT "Purchase_ID","Category",dense_rank() over (PARTITION BY "Category" ORDER BY "Value" DESC) as rank) tmp WHERE rank &lt;= 3)
        .distinct();
</code></pre>

<p>The rank function isn't correct...</p>

<p>Anyone knows how to solve this problem?</p>

<p>Many thanks!</p>