How can I replace numbers by nulls in a DataFrame?

Question

It might be strange, but I was wondering how to replace any number of a whole DataFrame's Column for null using Scala.

Imagine I have a nullable DoubleType column named col. There, I want to replace all numbers different to (1.0 ~ 10.0) by a null.

I tried unsatisfactorily the next code.

val xf = df.na.replace("col", Map(0.0 -> null.asInstanceOf[Double]).toMap)

But, as you realize in Scala when you convert a null into a Double it becomes represented as a 0.0, and this is not what I want. Besides, I can't realize any way to do it with a range of values. Therefore, I am thinking if there is any way to achieve this?

@zero323 No there is no need, I am just pulling out my hairs. — Alberto Bonsanto
– Alberto Bonsanto, Commented Feb 23, 2016 at 20:28

Community · Accepted Answer · 2017-05-23 12:08:06Z

2

How about when clause instead?

import org.apache.spark.sql.functions.when

val df = sc.parallelize(
  (1L, 0.0) :: (2L, 3.6) :: (3L, 12.0) :: (4L, 5.0) ::  Nil
).toDF("id", "val")

df.withColumn("val", when($"val".between(1.0, 10.0), $"val")).show

// +---+----+
// | id| val|
// +---+----+
// |  1|null|
// |  2| 3.6|
// |  3|null|
// |  4| 5.0|
// +---+----+

Any value which doesn't satisfy the predicate (here val BETWEEN 1.0 AND 10.0) will be replaced with NULL.

See also Create new Dataframe with empty/null field values

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered Feb 23, 2016 at 20:35

zero323

331k108 gold badges982 silver badges958 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Alberto Bonsanto Over a year ago

Just to leave it as an informative comment for the future. How would you do if you want to replace others except....? :)

zero323 Over a year ago

You can replace $"val".between(1.0, 10.0) with some other logical expression (isIn, not(isIn) and so on).

zero323 Over a year ago

@AlbertoBonsanto Can I take a moment of you time?

zero323 Over a year ago

If so lets switch to chat: chat.stackoverflow.com/rooms/103319/…

Collectives™ on Stack Overflow

How can I replace numbers by nulls in a DataFrame?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related