pyspark dataframe replace null in one column with another column by converting it from string to array

Question

I would like to replace a null value of a pyspark dataframe column with another string column converted to array.

import pyspark.sql.functions as F
import pyspark.sql.types as T

new_customers = spark.createDataFrame(data=[["Karen", ["a"]], ["Penny", ["b"]], ["John", [None]], ["Cosimo", ["d"]]], schema=["name", "val"])
new_customers.printSchema()
new_customers.show(5, False)

new_customers = new_customers.withColumn("new_val", F.coalesce(F.col("val"), F.array(F.col("name"))))

new_customers.show(10, truncate=False)

But, it is

 root
 |-- name: string (nullable = true)
 |-- val: array (nullable = true)
 |    |-- element: string (containsNull = true)

+------+---+
|name  |val|
+------+---+
|Karen |[a]|
|Penny |[b]|
|John  |[] |
|Cosimo|[d]|
+------+---+

+------+---+-------+
|name  |val|new_val|
+------+---+-------+
|Karen |[a]|[a]    |
|Penny |[b]|[b]    |
|John  |[] |[]     |
|Cosimo|[d]|[d]    |
+------+---+-------+

what I expect:

+------+---+-------+
|name  |val|new_val|
+------+---+-------+
|Karen |[a]|[a]    |
|Penny |[b]|[b]    |
|John  |[] |[John] |
|Cosimo|[d]|[d]    |
+------+---+-------+

Did I miss something ? thanks

Azhar Khan · Accepted Answer · 2022-09-12 06:27:17Z

1

Problem is that you've an array with null element in it. It will not test positive for isNull check.

First clean up single-null-element arrays:

import pyspark.sql.functions as F
import pyspark.sql.types as T

new_customers = spark.createDataFrame(data=[["Karen", ["a"]], ["Penny", ["b"]], ["John", [None]], ["Cosimo", ["d"]]], schema=["name", "val"])
+------+------+
|name  |val   |
+------+------+
|Karen |[a]   |
|Penny |[b]   |
|John  |[null]|
|Cosimo|[d]   |
+------+------+


new_customers = new_customers.withColumn("val", F.filter(F.col("val"), lambda x: x.isNotNull()))
+------+---+
|name  |val|
+------+---+
|Karen |[a]|
|Penny |[b]|
|John  |[] |
|Cosimo|[d]|
+------+---+

Then, change your expression for array empty check instead of null check:

new_customers = new_customers.withColumn("new_val", F.when(F.size("val")>0, F.col("val")).otherwise(F.array(F.col("name"))))
+------+---+-------+
|name  |val|new_val|
+------+---+-------+
|Karen |[a]|[a]    |
|Penny |[b]|[b]    |
|John  |[] |[John] |
|Cosimo|[d]|[d]    |
+------+---+-------+

answered Sep 12, 2022 at 6:27

Azhar Khan

4,15811 gold badges31 silver badges34 bronze badges

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

2 Comments

user3448011 Over a year ago

thanks for your help, but I still got "John -> []", my pyspark version is 2.3.2

user3448011 Over a year ago

my spark has no "filter()".

Collectives™ on Stack Overflow

pyspark dataframe replace null in one column with another column by converting it from string to array

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related