How to limit decimal values to 2 digits before applying agg function?

Question

I am following this solution from one of the stack overflow post, my only requirement here is how can I limit the values that I want to sum to 2 digit after the decimal before applying the df.agg(sum()) function?

For examples: I have values like below and the sum function sums it,

2.346
1.549

However I want the values to be rounded to 2 digit after the decimal like

2.35
1.55

before summing it. How can I do it? I was not able to find any sub function like sum().round of function sum.

Note: I am using Spark 1.5.1 version.

akuiper · Accepted Answer · 2017-01-17 19:47:21Z

16

You can use bround:

val df = Seq(2.346, 1.549).toDF("A")
df.select(bround(df("A"), 2)).show
+------------+
|bround(A, 2)|
+------------+
|        2.35|
|        1.55|
+------------+


df.agg(sum(bround(df("A"), 2)).as("appSum")).show
+------------------+
|            appSum|
+------------------+
|3.9000000000000004|
+------------------+
                                          ^
df.agg(sum(df("A")).as("exactSum")).show
+--------+
|exactSum|
+--------+
|   3.895|
+--------+

answered Jan 17, 2017 at 19:47

akuiper

216k33 gold badges363 silver badges380 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Explorer Over a year ago

Hi @Psidom it looks like bround is available from spark 2.0 version is there anything similar available in 1.5.1 version?

akuiper Over a year ago

It seems that round is the a more universal version and available since 1.5.0. You can give it a try. Not sure why there are two functions doing the same thing though.

Markus Over a year ago

@Psidom: Actually they're not the same. bround uses HALF_EVEN rounding mode while round uses HALF_UP rounding mode

Explorer · Accepted Answer · 2017-01-17 22:55:35Z

5

The above solution does work for spark 2.0 version however for folks like me who are still using 1.5.*+ versions below is something that will work.(I used round function as suggested by @Psidom):

val df = Seq(2.346, 1.549).toDF("A")
df.select(bround(df("A"), 2)).show
+------------+
|bround(A, 2)|
+------------+
|        2.35|
|        1.55|
+------------+

val total=df.agg(sum(round(df.col(colName),2)).cast("double")).first.getDouble(0)
total: Double = 3.90

answered Jan 17, 2017 at 22:55

Explorer

1,6775 gold badges30 silver badges74 bronze badges

Collectives™ on Stack Overflow

How to limit decimal values to 2 digits before applying agg function?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related