How to remove scientific notation while generating CSV files as output?

userdWEl1 0
Viewed 58

I had a delta table which is my input after reading that and generating a output as CSV file i see the scientific notation being displayed if the no of digits exceeds more than 7. Eg:delta table has column value a = 22409595 which is a double data type O/P : CSV is being generated as a= 2.2409595E7. I have tried all the possible methods such as format_number,casting etc but unfortunately I haven't succeeded.Using format_number is only working if i had a single record in my output it is not working for multiple records. Any help on this will appreciated ☺️ thanks in advance.

databricks pyspark apache-spark azure
1 Answers

I reproduced this and able to remove the scientific notation in the dataframe by converting it to decimal in dataframe.

Please follow the below demonstration below:

This is my Delta table:

enter image description here

You can see, I have the numbers more than 7 in all columns.

Generating scientific notation in the dataframe:

enter image description here

Cast this to Decimal type in the dataframe by which you can specify the count of precision. Give the count of maximum digits. Here, I have given 10, as my maximum number of digits of number is 10.

enter image description here

Save this dataframe as csv by which you can get the desired values.

My Source code:

%sql

CREATE TABLE table1 (col1 double,col2 double,col3 double);

insert into table1 values (22409595,12241226,17161224),(191919213,191919213,191919213);

%python
sqldf=spark.sql("select * from table1")

from pyspark.sql.types import *
for col in sqldf.columns:
    sqldf=sqldf.withColumn(col, sqldf[col].cast(DecimalType(10, 0)))
sqldf.show()
Rakesh Govindula0
Related