title: Finding Percentile in Spark-Scala per a group
body: <p>I am trying to do a percentile over a column using a Window function as below. I have referred <a href="https://stackoverflow.com/questions/53548964/how-to-use-approxquantile-by-group">here</a> to use the <code>ApproxQuantile</code> definition over a group.</p>
  
  <pre class="lang-scala prettyprint-override"><code>val df1 = Seq(
      (1, 10.0), (1, 20.0), (1, 40.6), (1, 15.6), (1, 17.6), (1, 25.6),
      (1, 39.6), (2, 20.5), (2 ,70.3), (2, 69.4), (2, 74.4), (2, 45.4),
      (3, 60.6), (3, 80.6), (4, 30.6), (4, 90.6)
  ).toDF("ID","Count")
  
  val idBucketMapping = Seq((1, 4), (2, 3), (3, 2), (4, 2))
      .toDF("ID", "Bucket")
  
  //jpp
  import org.apache.spark.sql.Column
  import org.apache.spark.sql.catalyst.expressions.aggregate.ApproximatePercentile
  import org.apache.spark.sql.expressions.Window
  
  object PercentileApprox {
       def percentile_approx(col: Column, percentage: Column,
                               accuracy: Column): Column = {
           val expr = new ApproximatePercentile(
               col.expr, percentage.expr, accuracy.expr
           ).toAggregateExpression
           new Column(expr)
      }
  
      def percentile_approx(col: Column, percentage: Column): Column =
          percentile_approx(col, percentage,
                    lit(ApproximatePercentile.DEFAULT_PERCENTILE_ACCURACY))
  }
  </code></pre>
  
  <pre class="lang-scala prettyprint-override"><code>import PercentileApprox._
  
  var res = df1
      .withColumn("percentile",
          percentile_approx(col("count"), typedLit(doBucketing(2)))
                   .over(Window.partitionBy("ID"))
      )
  
  def doBucketing(bucket_size : Int) = (1 until bucket_size)
      .scanLeft(0d)((a, _) =&gt; a + (1 / bucket_size.toDouble))
  </code></pre>
  
  <pre class="lang-none prettyprint-override"><code>scala&gt; df1.show
  +---+-----+
  | ID|Count|
  +---+-----+
  |  1| 10.0|
  |  1| 20.0|
  |  1| 40.6|
  |  1| 15.6|
  |  1| 17.6|
  |  1| 25.6|
  |  1| 39.6|
  |  2| 20.5|
  |  2| 70.3|
  |  2| 69.4|
  |  2| 74.4|
  |  2| 45.4|
  |  3| 60.6|
  |  3| 80.6|
  |  4| 30.6|
  |  4| 90.6|
  +---+-----+
  
  
  scala&gt; idBucketMapping.show
  +---+------+
  | ID|Bucket|
  +---+------+
  |  1|     4|
  |  2|     3|
  |  3|     2|
  |  4|     2|
  +---+------+
  
  
  scala&gt; res.show
  +---+-----+------------------+
  | ID|Count|        percentile|
  +---+-----+------------------+
  |  1| 10.0|[10.0, 20.0, 40.6]|
  |  1| 20.0|[10.0, 20.0, 40.6]|
  |  1| 40.6|[10.0, 20.0, 40.6]|
  |  1| 15.6|[10.0, 20.0, 40.6]|
  |  1| 17.6|[10.0, 20.0, 40.6]|
  |  1| 25.6|[10.0, 20.0, 40.6]|
  |  1| 39.6|[10.0, 20.0, 40.6]|
  |  3| 60.6|[60.6, 60.6, 80.6]|
  |  3| 80.6|[60.6, 60.6, 80.6]|
  |  4| 30.6|[30.6, 30.6, 90.6]|
  |  4| 90.6|[30.6, 30.6, 90.6]|
  |  2| 20.5|[20.5, 69.4, 74.4]|
  |  2| 70.3|[20.5, 69.4, 74.4]|
  |  2| 69.4|[20.5, 69.4, 74.4]|
  |  2| 74.4|[20.5, 69.4, 74.4]|
  |  2| 45.4|[20.5, 69.4, 74.4]|
  +---+-----+------------------+
  </code></pre>
  
  <p>Upto here it is well and good and the logic is simple. But I need results in a dynamic fashion. This means the argument <code>doBucketing(2)</code> to this function should be taken from <code>idBucketMapping</code> based on the ID - Value. </p>
  
  <p>This seems to be little bit tricky for me. Is this possible by any means?</p>
  
  <p>Expected Output --
  This means the percentile bucket is based on - <code>idBucketMapping</code> Dataframe .</p>
  
  <pre><code>+---+-----+------------------------+
  |ID |Count|percentile              |
  +---+-----+------------------------+
  |1  |10.0 |[10.0, 15.6, 20.0, 39.6]|
  |1  |20.0 |[10.0, 15.6, 20.0, 39.6]|
  |1  |40.6 |[10.0, 15.6, 20.0, 39.6]|
  |1  |15.6 |[10.0, 15.6, 20.0, 39.6]|
  |1  |17.6 |[10.0, 15.6, 20.0, 39.6]|
  |1  |25.6 |[10.0, 15.6, 20.0, 39.6]|
  |1  |39.6 |[10.0, 15.6, 20.0, 39.6]|
  |3  |60.6 |[60.6, 60.6]            |
  |3  |80.6 |[60.6, 60.6]            |
  |4  |30.6 |[30.6, 30.6]            |
  |4  |90.6 |[30.6, 30.6]            |
  |2  |20.5 |[20.5, 45.4, 70.3]      |
  |2  |70.3 |[20.5, 45.4, 70.3]      |
  |2  |69.4 |[20.5, 45.4, 70.3]      |
  |2  |74.4 |[20.5, 45.4, 70.3]      |
  |2  |45.4 |[20.5, 45.4, 70.3]      |
  +---+-----+------------------------+
  </code></pre>