Spark Dataframes :Convert unix exponential numbers to string whole number to obtain timestamp

Question

The spark dataframe below has start_t and end_t in unix format but has an exponential e in it.

+------+----------------+------------------+--------+----------+----------+-------+-----------+-----------+-----------+-------------+-------+---------------+----------------+
| alt_t|           end_t|engine_fuel_rate_t|   lat_t|left_max_t|left_min_t|  lon_t|plm3_incl_t|right_max_t|right_min_t|road_class_u8|speed_t|sprung_weight_t|         start_t|
+------+----------------+------------------+--------+----------+----------+-------+-----------+-----------+-----------+-------------+-------+---------------+----------------+
|1237.5|1.521956985733E9|                 0|-27.7314|       0.0|       0.0|22.9552|        1.5|        0.0|        0.0|            0|   17.4|          198.0| 1.52195698056E9|
|1236.5|1.521956989922E9|                 0|-27.7316|       0.0|       0.0|22.9552|       -3.3|        0.0|        0.0|            0|   17.6|          156.1|1.521956985733E9|
|1234.5|1.521956995378E9|                 0|-27.7318|       0.0|       0.0|22.9552|       -2.7|        0.0|        0.0|            0|   11.9|          148.6|1.521956989922E9|
|1230.5|1.521957001498E9|                 0| -27.732|       0.0|       0.0|22.9551|        2.3|        0.0|        0.0|            0|   13.2|          169.1|1.521956995378E9|

Since it is double it can not be convert directly to timestamp. It will through an error stating it needs to be string.

+------+----------------+------------------+--------+----------+----------+-------+-----------+-----------+-----------+-------------+-------+---------------+-------+
| alt_t|           end_t|engine_fuel_rate_t|   lat_t|left_max_t|left_min_t|  lon_t|plm3_incl_t|right_max_t|right_min_t|road_class_u8|speed_t|sprung_weight_t|start_t|
+------+----------------+------------------+--------+----------+----------+-------+-----------+-----------+-----------+-------------+-------+---------------+-------+
|1237.5|1.521956985733E9|                 0|-27.7314|       0.0|       0.0|22.9552|        1.5|        0.0|        0.0|            0|   17.4|          198.0|   null|
|1236.5|1.521956989922E9|                 0|-27.7316|       0.0|       0.0|22.9552|       -3.3|        0.0|        0.0|            0|   17.6|          156.1|   null|
|1234.5|1.521956995378E9|                 0|-27.7318|       0.0|       0.0|22.9552|       -2.7|        0.0|        0.0|            0|   11.9|          148.6|   null|

Therefore I used the following code:

%scala

val df2 = df.withColumn("start_t", df("start_t").cast("string"))
val df3 = df2.withColumn("end_t", df("end_t").cast("string"))
val filteredDF = df3.withColumn("start_t", unix_timestamp($"start_t", "yyyyMMddHHmmss").cast("timestamp"))
filteredDF.show()

I get null in start_t and think its due to the E (exponential sign). I have tested it in pandas python, the dates are valid and outputs results. I know there is a way using precision to change this. I am trying to convert it to timestamp in the format yyyy-MM-dd HH:mm:ss and have a separate column for just the time and date.

Note: similar question was posed but not answered. Scala Spark : Convert Double Column to Date Time Column in dataframe

This may be help to answering the q https://stackoverflow.com/questions/47993311/print-float-without-scientific-notation-in-scala — Sade, Oct 17 '18 at 06:20
what is the original datatype of start_t ? - double or string? — stack0114106, Oct 17 '18 at 06:21

score 0 · Answer 1 · answered Oct 17 '18 at 06:47

You should be able to cast a double to timestamp as shown below

scala> import org.apache.spark.sql.functions._
import org.apache.spark.sql.functions._

scala>
     | val df = Seq((1237.5,1.521956985733E9),
     | (1236.5,1.521956989922E9),
     | (1234.5,1.521956995378E9),
     | (1230.5,1.521957001498E9)).toDF("alt_t","end_t")
df: org.apache.spark.sql.DataFrame = [alt_t: double, end_t: double]

scala>

scala> df.printSchema
root
 |-- alt_t: double (nullable = false)
 |-- end_t: double (nullable = false)


scala>

scala> df.withColumn("end_t",$"end_t".cast("timestamp")).show
+------+--------------------+
| alt_t|               end_t|
+------+--------------------+
|1237.5|2018-03-25 05:49:...|
|1236.5|2018-03-25 05:49:...|
|1234.5|2018-03-25 05:49:...|
|1230.5|2018-03-25 05:50:...|
+------+--------------------+

stack0114106 · Accepted Answer · 2018-10-17T09:44:49.977

Chain the casting from String -> Double -> Timestamp. The below works

scala> val df = Seq(("1237.5","1.521956985733E9"),("1236.5","1.521956989922E9"),("1234.5","1.521956995378E9"),("1230.5","1.521957001498E9")).toDF("alt_t","end_t")
df: org.apache.spark.sql.DataFrame = [alt_t: string, end_t: string]

scala> df.withColumn("end_t",'end_t.cast("double").cast("timestamp")).show(false)
+------+-----------------------+
|alt_t |end_t                  |
+------+-----------------------+
|1237.5|2018-03-25 01:49:45.733|
|1236.5|2018-03-25 01:49:49.922|
|1234.5|2018-03-25 01:49:55.378|
|1230.5|2018-03-25 01:50:01.498|
+------+-----------------------+


scala>

UPDATE1

scala> val df = Seq(("1237.5","1.521956985733E9"),("1236.5","1.521956989922E9"),("1234.5","1.521956995378E9"),("1230.5","1.521957001498E9")).toDF("alt_t","end_t").withColumn("end_t",'end_t.cast("double").cast("timestamp"))
df: org.apache.spark.sql.DataFrame = [alt_t: string, end_t: timestamp]

scala> df.printSchema
root
 |-- alt_t: string (nullable = true)
 |-- end_t: timestamp (nullable = true)


scala>

Was refering to a case when there two timestamp start_t and end_t — Sade, Oct 17 '18 at 10:21

Spark Dataframes :Convert unix exponential numbers to string whole number to obtain timestamp

2 Answers2