Spark: Cannot create new column from the output of filling one column null values from another

Question

I am trying to fill the null values from ColY with values from ColX whilst storing the output as a new column in my DataFrame Col_new. I am using pyspark in databricks, however I am fairly new to this.

Sample Data is as follows:

ColX              ColY  
apple             orange
pear              null
grapefruit        pear
apple             null

The desired output would look like the following:

ColX              ColY              Col_new
apple             orange            orange  
pear              null              pear
grapefruit        pear              pear
apple             null              apple

I have tried several lines of code to no avail. My latest attempt was as follows:

.withColumn("Col_new", col('ColX').select(coalesce('ColY')))

Any help would be greatly appreciated. Many Thanks.

Leo C · Accepted Answer · 2019-03-28 16:24:57Z

1

Both columns ColY and ColX should be provided as coalesce's arguments:

df = spark.createDataFrame([
  ("apple", "orange"),
  ("pear", None),
  ("grapefruit", "pear"),
  ("apple", None)
]).toDF("ColX", "ColY")

from pyspark.sql.functions import coalesce

df.withColumn("ColNew", coalesce("ColY", "ColX")).show()
+----------+------+------+
|      ColX|  ColY|ColNew|
+----------+------+------+
|     apple|orange|orange|
|      pear|  null|  pear|
|grapefruit|  pear|  pear|
|     apple|  null| apple|
+----------+------+------+

edited Mar 28, 2019 at 16:24

answered Mar 28, 2019 at 16:13

Leo C

22.5k3 gold badges28 silver badges42 bronze badges

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

Comments

pault · Accepted Answer · 2019-03-28 16:16:28Z

1

coalesce will return the first non-null value from a list of columns. You're only passing in one column, so coalesce has no effect.

The correct syntax in this case would be:

from pyspark.sql.functions import coalesce
df = df.withColumn("Col_new", coalesce('ColY', 'ColX'))

This means take the value of ColY unless it is null, in which case take the value from ColX.

In this case, you can also use when for the equivalent logic:

from pyspark.sql.functions import when

df = df.withColumn(
    "Col_new", 
    when(col("ColY").isNull(), col("ColX")).otherwise(col("ColY"))
)

answered Mar 28, 2019 at 16:16

pault

43.7k17 gold badges121 silver badges161 bronze badges

Collectives™ on Stack Overflow

Spark: Cannot create new column from the output of filling one column null values from another

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related