dataframe - cast string to decimal when encountering zeros returns OE-16

[shan_chandra]

The user is trying to cast string to decimal when encountering zeros. The cast function displays the  '0' as '0E-16'. could you please let us know your thoughts on whether 0s can be displayed as 0s?

from pyspark.sql import functions as F
df = spark.sql("select cast('0' AS decimal(38,16)) as decimal_number union all select cast('1.0000123400000' AS decimal(38,16))")
df2 = df.withColumn("string_column", F.expr("format_number(decimal_number, '0.######################')"))
display(df2)  

[shan_chandra · Accepted solution]

If the scale of decimal type is greater than 6, scientific notation kicks in hence seeing 0E-16.

This behavior is described in the existing OSS spark issue - https://issues.apache.org/jira/browse/SPARK-25177

Kindly cast the column to a decimal type less than or equal to 6 to have zeros displayed as zeros.

from pyspark.sql import functions as F
df = spark.sql("select cast('0' AS decimal(10,6)) as decimal_number union all select cast('1.0000123400000' AS decimal(4,2))")
df2 = df.withColumn("string_column", F.expr("format_number(decimal_number, '0.######################')"))
display(df2)