PySpark中如何用另一列值填充目标列的空值？

给定数据集：

col_id     col_2     col_3    col_id_b
ABC111     shfhs     34775    null
ABC112     shfhe     34775    DEF345
ABC112     shfhs     34775    GFR563
ABC112     shfgh     34756    TRS572
ABC113     shfdh     34795    null
ABC114     shfhs     34770    null

需要生成新列col_new，规则为：

• 当col_id_b不为空时，直接使用col_id_b的值
• 当col_id_b为空时，用对应行的col_id填充

你之前使用的coalesce函数完全可以满足需求，只是参数顺序搞反了。coalesce函数的逻辑是返回传入参数列表中第一个非空的值，所以只要把col_id_b放在第一个参数位置，col_id放在第二个位置即可。

完整代码示例 #

from pyspark.sql import SparkSession
from pyspark.sql.functions import coalesce

# 初始化SparkSession
spark = SparkSession.builder.appName("fill_col_new").getOrCreate()

# 创建示例数据集
data = [
    ("ABC111", "shfhs", 34775, None),
    ("ABC112", "shfhe", 34775, "DEF345"),
    ("ABC112", "shfhs", 34775, "GFR563"),
    ("ABC112", "shfgh", 34756, "TRS572"),
    ("ABC113", "shfdh", 34795, None),
    ("ABC114", "shfhs", 34770, None)
]

columns = ["col_id", "col_2", "col_3", "col_id_b"]
df = spark.createDataFrame(data, columns)

# 添加col_new列
df = df.withColumn("col_new", coalesce(df["col_id_b"], df["col_id"]))

# 展示结果
df.show()

执行结果 #

+-------+------+------+---------+-------+
| col_id| col_2| col_3|col_id_b |col_new|
+-------+------+------+---------+-------+
|ABC111 |shfhs |34775 |null     |ABC111 |
|ABC112 |shfhe |34775 |DEF345   |DEF345 |
|ABC112 |shfhs |34775 |GFR563   |GFR563 |
|ABC112 |shfgh |34756 |TRS572   |TRS572 |
|ABC113 |shfdh |34795 |null     |ABC113 |
|ABC114 |shfhs |34770 |null     |ABC114 |
+-------+------+------+---------+-------+

内容的提问来源于stack exchange，提问作者johnnydoe