13
$\begingroup$

I am very new to Scala and Spark, and am working on some self-made exercises using baseball statistics. I am using a case class create a RDD and assign a schema to the data, and am then turning it into a DataFrame so I can use SparkSQL to select groups of players via their stats that meet certain criteria.

Once I have the subset of players I am interested in looking at further, I would like to find the mean of a column; eg Batting Average or RBIs. From there I would like to break all the players into percentile groups based on their average performance compared to all players; the top 10%, bottom 10%, 40-50%

I've been able to use the DataFrame.describe() function to return a summary of a desired column (mean, stddev, count, min, and max) all as strings though. Is there a better way to get just the mean and stddev as Doubles, and what is the best way of breaking the players into groups of 10-percentiles?

So far my thoughts are to find the values that bookend the percentile ranges and writing a function that groups players via comparators, but that feels like it is bordering on reinventing the wheel.

I have the following imports currently:

 import org.apache.spark.rdd.RDD 
 import org.apache.spark.sql.SQLContext 
 import org.apache.spark.{SparkConf, SparkContext} 
 import org.joda.time.format.DateTimeFormat
| improve this question | |
$\endgroup$
  • $\begingroup$ Have you checked the scaladoc? It has an example for average and max: .agg(avg(people("salary")), max(people("age"))). With sorting you can probably find (using skip and take) the percentiles, but there might be faster options. $\endgroup$ – Gábor Bakos Jul 22 '15 at 16:47
  • $\begingroup$ I had seen this previously in the scaladocs. When I try to use them like the example I receive and error not found: value avg and not found: value max $\endgroup$ – the3rdNotch Jul 22 '15 at 18:46
  • $\begingroup$ What are your imports? It might be easier to help if there is an example and you describe what were the problem. $\endgroup$ – Gábor Bakos Jul 22 '15 at 18:48
  • $\begingroup$ import org.apache.spark.rdd.RDD import org.apache.spark.sql.SQLContext import org.apache.spark.{SparkConf, SparkContext} import org.joda.time.format.DateTimeFormat $\endgroup$ – the3rdNotch Jul 22 '15 at 19:02
  • $\begingroup$ The following test might help start using DataFrame functions. It seems you have to import the org.apache.spark.sql.functions._ too. (BTW.: I think the additional information is better added to the question itself and it is enough to add a comment after edit.) $\endgroup$ – Gábor Bakos Jul 22 '15 at 19:11
22
$\begingroup$

This is the import you need, and how to get the mean for a column named "RBIs":

import org.apache.spark.sql.functions._
df.select(avg($"RBIs")).show()

For the standard deviation, see scala - Calculate the standard deviation of grouped data in a Spark DataFrame - Stack Overflow

For grouping by percentiles, I suggest defining a new column via a user-defined function (UDF), and using groupBy on that column. See

| improve this answer | |
$\endgroup$
5
$\begingroup$

This is also returns average of column

df.select(mean(df("ColumnName"))).show()
+----------------+
| avg(ColumnName)|
+----------------+
|230.522453845909|
+----------------+
| improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.