Add column to data frames with specific condition in Spark/scala : <p>I have a data frame of the form (shown below) where for every id has a corresponding count of elements in the bucket. The bucket takes 3 values low, med and high.</p>

<pre><code>+---+------+-----+
| id|bucket|count|
+---+------+-----+
|id1|   low|  2  |
|id1|   med|  3  |
|id1|  high|  4  |
|id2|   low|  1  |
|id2|   med|  4  |
|id3|   low|  7  |
|id3|  high|  1  |
|id4|   med|  2  |
|id4|  high|  1  |
+---+------+-----+ 
</code></pre>

<p>The output I desire is as follows</p>

<pre><code>+---+-----+-----+-----+
|id | low | med | high|
+---+-----+-----+-----+
|id1|    2|  3  |  4  |
|id2|    1|  4  |  0  |
|id3|    7|  0  |  1  |
|id4|    0|  2  |  1  |
+---+-----+-----+-----+
</code></pre>

<p>As seen if there is no entry for the bucket for that particular id the count in the output defaults to zero.</p>

<p>I am new to spark and unable to figure out the query for getting this result. The final schema is fixed. </p>
