topic Re: How to calculate Percentile of column in a DataFrame in spark? in Data Engineering

How to calculate Percentile of column in a DataFrame in spark?

dheeraj — Tue, 07 Jun 2016 23:27:41 GMT

I am trying to calculate percentile of a column in a DataFrame? I cant find any percentile_approx function in Spark aggregation functions. For e.g. in Hive we have percentile_approx and we can use it in the following way

hiveContext.sql("select percentile_approx("Open_Rate",0.10) from myTable);

But I want to do it using Spark DataFrame for performance reasons.

Sample data set

    |User ID|Open_Rate|
    ------------------- 
    |A1     |10.3     |     
    |B1     |4.04     |     
    |C1     |21.7     |     
    |D1     |18.6     |

I want to find out how many users fall into 10 percentile or 20 percentile and so on. I want to do something like this

df.select($"id",Percentile($"Open_Rate")).show

Re: How to calculate Percentile of column in a DataFrame in spark?

dheeraj — Tue, 07 Jun 2016 23:29:33 GMT

correction in the question above, i want to do something like this

 df.select($"id",Percentile($"Open_Rate",0.1)).show

Re: How to calculate Percentile of column in a DataFrame in spark?

SiddSingal — Thu, 09 Jun 2016 04:16:31 GMT

You could try to code your own version of this. It does not seem like this functionality is built into the Spark DataFrames. You may need to use the Window class in order to accomplish this. Here is a blog post with some details: https://databricks.com/blog/2015/07/15/introducing-window-functions-in-spark-sql.html

Re: How to calculate Percentile of column in a DataFrame in spark?

amandaphy — Sat, 24 Sep 2016 08:56:39 GMT

You can try using df.registerTempTable("tmp_tbl") val newDF = sql(/ do something with tmp_tbl /)// and continue using newDF Learn More