minBy equivalent in Spark Dataframe

Question

I'm looking for equivalent function of minBy aggregate in Spark Dataframe or may need to manually aggregate. Any thoughts? Thanks.

https://prestodb.io/docs/current/functions/aggregate.html#min_by

Answer 1

There is no such direct function to get the 'min_by' values from the Dataframe.

It is a two stage operation in Spark. First groupby the column then apply min function to get min value for each numeric column for each group.

scala> val inputDF = Seq(("a", 1),("b", 2), ("b", 3), ("a", 4), ("a", 5)).toDF("id", "count")
inputDF: org.apache.spark.sql.DataFrame = [id: string, count: int]

scala> inputDF.show()
+---+-----+
| id|count|
+---+-----+
|  a|    1|
|  b|    2|
|  b|    3|
|  a|    4|
|  a|    5|
+---+-----+

scala> inputDF.groupBy($"id").min("count").show()
+---+----------+
| id|min(count)|
+---+----------+
|  b|         2|
|  a|         1|
+---+----------+