Replace Empty values with nulls in Spark Dataframe

Question

I have a data frame with n number of columns and I want to replace empty strings in all these columns with nulls.

I tried using

val ReadDf = rawDF.na.replace("columnA", Map( "" -> null));

and

val ReadDf = rawDF.withColumn("columnA", if($"columnA"=="") lit(null) else $"columnA" );

Both of them didn't work.

Any leads would be highly appreciated. Thanks.

There is many errors here and it's not just related to replacing an empty string with null but also the jdbc error and as a matter of fact, that second error can't be resolved with the current description and information because it cannot be reproduced. Please review your question as it is subject of being closed ! — eliasah
– eliasah, Commented May 22, 2017 at 7:40
One thing I don't understand is, How is [this][1] relevant to the question I asked. It talks about an array of strings in MYSQL and my question was about replacing empty strings with nulls in the data frame. Can someone enlighten me? — Vinay Kumar
– Vinay Kumar, Commented May 23, 2017 at 11:57
Here it is. I haven't voted for this question to be closed as a duplicated but as I said in the comment earlier, you have 2 differents errors and the latest error message is related to the question mentioned by the voters. — eliasah
– eliasah, Commented May 23, 2017 at 12:01
@eliasah I was not referring to you. I am very sorry that I didn't address the persons. Let me see if I can edit the comment and add them. — Vinay Kumar
– Vinay Kumar, Commented May 23, 2017 at 12:28
I'm not sure why this was closed as a duplicate of an SQL question, but this has been reopened. — cs95
– cs95, Commented Sep 12, 2018 at 23:50

Tzach Zohar · Accepted Answer · 2017-05-26 13:27:45Z

6

Your first approach seams to fail due to a bug that prevents replace from being able to replace values with nulls, see here.

Your second approach fails because you're confusing driver-side Scala code for executor-side Dataframe instructions: your if-else expression would be evaluated once on the driver (and not per record); You'd want to replace it with a call to when function; Moreover, to compare a column's value you need to use the === operator, and not Scala's == which just compares the driver-side Column object:

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

rawDF.withColumn("columnA", when($"columnA" === "", lit(null)).otherwise($"columnA"))

edited May 26, 2017 at 13:27

answered May 21, 2017 at 23:38

Tzach Zohar

37.9k3 gold badges83 silver badges86 bronze badges

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

Collectives™ on Stack Overflow

Replace Empty values with nulls in Spark Dataframe

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related