How to replace null NAN or Infinite values to default value in Spark Scala

user_4556463 0
Viewed 7298

I'm reading in csvs into Spark and I'm setting the schema to all DecimalType(10,0) columns. When I query the data, I get the following error:

NumberFormatException: Infinite or NaN

If I have NaN/null/infinite values in my dataframe, I would like to set them to 0. How do I do this? This is how I'm attempting to load the data:

var cases = spark.read.option("header",false).
option("nanValue","0").
option("nullValue","0").
option("positiveInf","0").
option("negativeInf","0").
schema(schema).
csv(...

Any help would be greatly appreciated.

bigdata apache-spark-sql apache-spark scala
3 Answers

My environment (using Spark 2.3.1 with Scala 2.11) doesn't replicate @ShankarKoirala answer - the .na.fill()… doesn't capture the infinity and NaN, because those are not empty values. However, walues could be tested using .isin() function:

val x1 = Seq((1.0, 1, "a"),(1.0, 1, "a"), (2.0, 2, "b")
           , (Float.NaN, 1, "a"), (Float.PositiveInfinity, 2, "a")
           , (Float.NegativeInfinity, 2, "a"))
        .toDF("Value", "Id", "Name")
x1
  .withColumn("IsItNull", $"Value".isNull)
  .withColumn("IsItBad", $"Value".isin(Double.NaN, Double.PositiveInfinity, Double.NegativeInfinity))
.show()

this will produce following results:

+---------+---+----+--------+-------+
|    Value| Id|Name|IsItNull|IsItBad|
+---------+---+----+--------+-------+
|      1.0|  1|   a|   false|  false|
|      1.0|  1|   a|   false|  false|
|      2.0|  2|   b|   false|  false|
|      NaN|  1|   a|   false|   true|
| Infinity|  2|   a|   false|   true|
|-Infinity|  2|   a|   false|   true|
+---------+---+----+--------+-------+

If a replacement is needed, just use original column name in the withColumn() function and apply the .isin() as argument of when function.

user_68066530
Related