cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

shan_chandra
Esteemed Contributor
Esteemed Contributor

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)  

Screen Shot 2022-03-09 at 12.13.11 PM

1 ACCEPTED SOLUTION

Accepted Solutions

shan_chandra
Esteemed Contributor
Esteemed Contributor

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)  

View solution in original post

1 REPLY 1

shan_chandra
Esteemed Contributor
Esteemed Contributor

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)  

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!