How do I replace a string value with a NULL in PySpark?

Question

I want to do something like this:

df.replace('empty-value', None, 'NAME')

Basically, I want to replace some value with NULL, but it does not accept None as an argument. How can I do this?

Hale

Answer 1

This will replace empty-value with None in your name column:

from pyspark.sql.functions import udf
from pyspark.sql.types import StringType


df = sc.parallelize([(1, "empty-value"), (2, "something else")]).toDF(["key", "name"])
new_column_udf = udf(lambda name: None if name == "empty-value" else name, StringType())
new_df = df.withColumn("name", new_column_udf(df.name))
new_df.collect()

Output:

[Row(key=1, name=None), Row(key=2, name=u'something else')]

By using the old name as the first parameter in withColumn, it actually replaces the old name column with the new one generated by the UDF output.

Tetrapod

I didn't think of trying UDFs, that seems to be the way to go – Hale

Your code will run faster if you use native Spark functions rather than UDFs - see the other answers. (That's why they have more upvotes than the accepted answer) – Bouncy

Answer 2

You can combine when clause with NULL literal and types casting as follows:

from pyspark.sql.functions import when, lit, col

df = sc.parallelize([(1, "foo"), (2, "bar")]).toDF(["x", "y"])

def replace(column, value):
    return when(column != value, column).otherwise(lit(None))

df.withColumn("y", replace(col("y"), "bar")).show()
## +---+----+
## |  x|   y|
## +---+----+
## |  1| foo|
## |  2|null|
## +---+----+

It doesn't introduce BatchPythonEvaluation and because of that should be significantly more efficient than using an UDF.

Goodygoody

Excellent, much more efficient for large datasets. – Aliment

@Aliment It is, Python UDFs have relatively high serde overhead. – Goodygoody

@Goodygoody you don't actually need the otherwise() part - when() will return null by default. – Optimize

Could you please show how to do that with nested fields? – Egarton

@Goodygoody in otherwise, I tried returning None instead of lit(None) and the result is same. Can you please explain why you used lit? – Cressler

@Goodygoody when can directly mention .otherwise(lit(null )) right ? – Middendorf

Answer 3

One-line solution in native spark code

You can simply use a dict for the first argument of replace: it accepts None as replacement value which will result in NULL.

Here is an example:

df = df.replace({'empty-value': None}, subset=['NAME'])

Just replace 'empty-value' with whatever value you want to overwrite with NULL. Note that your 'empty-value' needs to be hashable.

An additional advantage is that you can use this on multiple columns at the same time. Just add the column names to the list under subset.

Pacien

You can also use plain lists: df = df.replace(['null','', 'NULL'], None, ['Col1', 'Col2','Col3']) I find it easier/simpler to use than a dictionary. – Goren

Answer 4

The best alternative is the use of a when combined with a NULL. Example:

from pyspark.sql.functions import when, lit, col

df= df.withColumn('foo', when(col('foo') != 'empty-value',col('foo)))

If you want to replace several values to null you can either use | inside the when condition or the powerfull create_map function.

Important to note is that the worst way to solve it with the use of a UDF. This is so because udfs provide great versatility to your code but come with a huge penalty on performance.

Leftist

Answer 5

This will also work:

df5 = df.replace(str('empty-value'), None)

Corsetti

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Donovan