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

shan_chandra — Wed, 09 Mar 2022 22:12:04 GMT

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)

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

shan_chandra — Wed, 09 Mar 2022 22:19:39 GMT

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)

topic Re: dataframe - cast string to decimal when encountering zeros returns OE-16 in Data Engineering

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

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