Dealing with negatives in calculations (Databricks-Spark SQL)

Question

When multiplying two columns together in a spark SQL table with random negative values, returns "NaN" for those which have a negative in one of the columns. Any techniques to help get the calculations work?

SELECT temperature * days FROM weather_data

question from:https://stackoverflow.com/questions/66059902/dealing-with-negatives-in-calculations-databricks-spark-sql

Answer 1

If you get NaN from a multiplication, mybe one or more columns contains NaN values. You can use nanvl to set a default value (ex. 0) when the column is NaN. Use it with coalesce to handle nulls too:

SELECT coalesce(nanvl(temperature, 0), 0) * days FROM weather_data

Example:

weather_data table:
+-----------+----+
|temperature|days|
+-----------+----+
|        NaN|   1|
|     -12.34|   2|
|       null|   3|
|       15.5|   4|
+-----------+----+

spark.sql("SELECT coalesce(nanvl(temperature, 0), 0) * days AS mul FROM weather_data").show()

+------+
|   mul|
+------+
|   0.0|
|-24.68|
|   0.0|
|  62.0|
+------+