How to Coalesce Values from Multiple Columns into One in PySpark?

How to Coalesce Values from Multiple Columns into One in PySpark? You can use the PySpark coalesce() function to combine multiple columns into a single column. It returns the first non-null value from the specified columns for each row.

This function helps to handle missing data. If your data is spread across several columns and some values are missing, coalesce() picks the first non-null value, making it easy to create a clean and consolidated column.

In this article, you will learn how to use pyspark.sql.functions.coalesce() effectively to merge multiple columns into one.

Key Points-

coalesce() returns the first non-null value from the specified columns in each row.
You can apply it to two or more columns as needed.
It is ideal for merging sparse or incomplete data spread across multiple columns.
You can use it along with lit("default") to provide a fallback value when all columns are null.
String, numeric, and boolean columns can all be used, as long as the data types are compatible.
Empty strings ("") are not nulls—use when(col == "", None) to handle them.
Unlike fillna(), which applies a fixed value per column, coalesce() dynamically checks multiple columns per row.
You can chain coalesce() inside withColumn() to create new columns.
It supports use in both DataFrame transformations and SQL expressions.
Commonly used in ETL, reporting, and data cleaning to simplify and unify scattered data.

PySpark coalesce() Function

In PySpark, the coalesce() function is used to combine two or more columns into a single column by selecting the first non-null value from the specified columns for each row. If all the values in a row are null, coalesce() returns null, unless you provide a default value using lit("default").

Syntax of coalesce()

The following is the syntax of the coalesce() function.


from pyspark.sql.functions import coalesce
coalesce(col1, col2, col3, ...)

Parameters

col1, col2, ... are PySpark column expressions.

Return Value

coalesce() returns a single column where each row contains the first non-null value from the specified columns.

Sample DataFrame

First, let’s create a sample DataFrame


# Create DataFrame
# Imports
from pyspark.sql import SparkSession
from pyspark.sql.functions import coalesce

# Create Spark session
spark = SparkSession.builder.getOrCreate()

# Create data
data = [[None, "blue", None], 
        ["red", None, None], 
        [None, None, "green"], 
        [None, "yellow", "black"], 
        [None, None, None]]

# Create column names
columns = ['primary_color', 'secondary_color', 'backup_color']

# Create DataFrame using data and column names
df = spark.createDataFrame(data, columns)

# View original DataFrame
df.show()

Yields below the output.

Pyspark Coalesce Multiple Columns

You can use PySpark coalesce() function to add a new column to a DataFrame having the first non-null value from a specified list of columns. Let's pass specified columns of a DataFrame into this function to get a new column that will contain the first available non-null values from specified columns of each row.


# Add a new column using coalesce from multiple columns
df = df.withColumn("final_color", coalesce("primary_color", "secondary_color", "backup_color"))

# View updated DataFrame
df.show()

Yields below the output.

Coalesce two Columns in a PySpark DataFrame

You can also use coalesce() to combine two columns into one by returning the first non-null value from the specified columns for each row.


# Add a new column using coalesce from two columns
df = df.withColumn("final_color", coalesce("primary_color", "secondary_color"))

# View updated DataFrame
df.show()

Yields below the output.


# Output:
+-------------+---------------+------------+-----------+
|primary_color|secondary_color|backup_color|final_color|
+-------------+---------------+------------+-----------+
|         NULL|           blue|        NULL|       blue|
|          red|           NULL|        NULL|        red|
|         NULL|           NULL|       green|       NULL|
|         NULL|         yellow|       black|     yellow|
|         NULL|           NULL|        NULL|       NULL|
+-------------+---------------+------------+-----------+

Coalesce with Literal Values

If you want to avoid null values in the new column, you can use coalesce() along with a default fallback value using lit(). Simply pass the columns into this function along with lit("default"); if all column values are null, it returns the specified default value, otherwise, it returns the first non-null value from the given columns.


# Add a new column using coalesce with a default fallback value "Missing"
df = df.withColumn(
    "final_value_with_default",
    coalesce("primary_color", "secondary_color", "backup_color", lit("Missing"))
)

# View updated DataFrame
df.show()

Yields below the output.


# Output:
+-------------+---------------+------------+-----------+------------------------+
|primary_color|secondary_color|backup_color|final_color|final_value_with_default|
+-------------+---------------+------------+-----------+------------------------+
|         NULL|           blue|        NULL|       blue|                    blue|
|          red|           NULL|        NULL|        red|                     red|
|         NULL|           NULL|       green|      green|                   green|
|         NULL|         yellow|       black|     yellow|                  yellow|
|         NULL|           NULL|        NULL|       NULL|                 Missing|
+-------------+---------------+------------+-----------+------------------------+

Frequently Asked Questions of Pyspark Coalesce Multiple Columns

coalesce(col1, col2, ...) returns the first non-null value from the list of specified columns for each row.

You can use the coalesce() to replace nulls by returning the first available value among the given columns, optionally falling back to a default value using lit().

If all values are null in a row, coalesce() will return null unless you specify a default value using F.lit("default").

fillna() replaces nulls column-wise with a fixed value.
coalesce() lets you pick the first non-null value across multiple columns per row.

You can use coalesce() on numeric and string columns together by first casting them to the same data type, like StringType.

Conclusion

In this article, you learned how to use coalesce() in PySpark to merge multiple columns into one by selecting the first non-null value in each row. You also saw how to provide a default value using the lit() function to avoid nulls in the resulting column.

Happy Learning!!