In PySpark, the concat_ws() function is used to concatenate multiple string columns into a single column using a specified separator. Both concat_ws() and concat() are part of the pyspark.sql.functions module and allow you to combine multiple columns, with concat_ws() providing the option to include separators.
In this article, we will explore how concat_ws() works, highlight its differences from concat(), and cover various use cases such as handling null values, combining strings with other columns, and using it within SQL queries.
Key Points-
- You can use
concat_ws()to join multiple columns into a single column with a custom separator. - Unlike
concat(), it automatically adds the specified separator between values. - If a column contains
null,concat_ws()ignores it instead of returningnull. - To join more than two or three columns, you can pass multiple column names.
- You can use fixed strings along with column values.
- To conditionally add prefixes or suffixes, combine
concat_ws()withwhen(). - You can specify different separators like space
" ", comma",", dash"-", or even custom text. - You can use
concat_ws()in both DataFrame API and Spark SQL queries. - It is commonly used in preparing CSV-style output, full names, or formatted labels.
- Compared to
concat(),concat_ws()is more flexible and reliable when handling missing values.
<a id="h-pyspark-concat-ws-function"></a>
PySpark concat_ws() Function
The concat_ws() function combines multiple input string columns into a single string column using the specified separator. The “ws” in concat_ws stands for “with separator.” It returns one string column where all the input values are joined with the given separator. If any column contains a null value, concat_ws() automatically skips it instead of returning null.
<a id="h-syntax"></a>
Syntax
Following is the syntax of the concat_ws() function.
# Syntax of the cocat_ws()
concat_ws(sep, *cols)
<a id="h-parameters"></a>
Parameters
sep: (string) The separator to use between values (e.g.," ",",","-", etc.).*cols: (string or Column) One or more column names or column expressions to concatenate.
<a id="h-return-value"></a>
Return Value
Returns a single string column where all input columns are concatenated using the specified separator.
If any column is null, concat_ws() skips it instead of returning null.
We’ll use the following sample DataFrame throughout the examples:
from pyspark.sql import SparkSession
from pyspark.sql.functions import concat, concat_ws, col
# Create SparkSession
spark = SparkSession.builder.appName("aistudylabs").getOrCreate()
# Sample Data
data = [
('James','','Smith','1991-04-01','M',3000),
('Michael','Rose','','2000-05-19','M',4000),
('Robert','','Williams','1978-09-05','M',4000),
('Maria','Anne','Jones','1967-12-01','F',4000),
('Jen','Mary','Brown','1980-02-17','F',-1)
]
columns = ["firstname","middlename","lastname","dob","gender","salary"]
df = spark.createDataFrame(data=data, schema=columns)
df.show(truncate=False)
Yields below the output.
<a id="h-pyspark-concat-multiple-columns"></a>
PySpark Concat Multiple Columns
You can concat more than two or three columns using concat_ws() based on a specified separator. This function returns a single column where all values are combined using the given separator.
# Concatenate multiple columns using concat_ws()
df_multi = df.select(
concat_ws("-", "firstname", "middlename", "lastname", "gender").alias("FullInfo")
)
df_multi.show(truncate=False)
Yields below the output.
<a id="h-pyspark-concat-ws-vs-concat"></a>
PySpark concat_ws() vs concat()
To concatenate multiple columns, you can use either concat() or concat_ws().
You can use concat() to combine multiple columns into one string. However, it does not add any separator between the values. It returns values joined directly without any separator.
# Using concat() → merges values without separator
df_concat = df.select(
concat(df.firstname, df.middlename, df.lastname).alias("FullName"),
"dob", "gender", "salary"
)
df_concat.show(truncate=False)
Yields below the output.
# Output:
+--------------+----------+------+------+
|FullName |dob |gender|salary|
+--------------+----------+------+------+
|JamesSmith |1991-04-01|M |3000 |
|MichaelRose |2000-05-19|M |4000 |
|RobertWilliams|1978-09-05|M |4000 |
|MariaAnneJones|1967-12-01|F |4000 |
|JenMaryBrown |1980-02-17|F |-1 |
+--------------+----------+------+------+
Whereas, the concat_ws() function merges multiple columns into a single string using a specified separator (such as a space, comma, or hyphen), returning the combined values with that delimiter.
# Using concat_ws() → merges values with a separator
df_concat_ws = df.select(
concat_ws(" ", df.firstname, df.middlename, df.lastname).alias("FullName"),
"dob", "gender", "salary"
)
df_concat_ws.show(truncate=False)
Yields below the output.
# Output:
+----------------+----------+------+------+
|FullName |dob |gender|salary|
+----------------+----------+------+------+
|James Smith |1991-04-01|M |3000 |
|Michael Rose |2000-05-19|M |4000 |
|Robert Williams|1978-09-05|M |4000 |
|Maria Anne Jones|1967-12-01|F |4000 |
|Jen Mary Brown |1980-02-17|F |-1 |
+----------------+----------+------+------+
<a id="h-handle-null-values-using-pyspark-concat-ws"></a>
Handle Null Values using PySpark concat_ws()
You can handle null values using concat_ws(). Unlike concat(), which may produce unwanted null, concat_ws() will ignore null values in the concatenation.
# concat_ws() ignores null values
from pyspark.sql import SparkSession
from pyspark.sql.functions import concat_ws
# Create Spark session
spark = SparkSession.builder.appName("ConcatWSExample").getOrCreate()
# Sample Data with nulls
data = [
("James", None, "Smith"),
("Michael", "Rose", None),
("Robert", None, None)
]
columns = ["firstname", "middlename", "lastname"]
df = spark.createDataFrame(data, columns)
# concat_ws() ignores null values
df_nulls = df.select(
concat_ws(" ", df.firstname, df.middlename, df.lastname).alias("FullName")
)
df_nulls.show(truncate=False)
Yields below the output.
# Output:
+------------+
|FullName |
+------------+
|James Smith |
|Michael Rose|
|Robert |
+------------+
<a id="h-pyspark-concat-string-to-column-value"></a>
PySpark Concat String to Column Value
You can use concat_ws() with conditional expressions to add a specified prefix to column values. This approach generates column names with prefixes in a PySpark DataFrame.
# PySpark concat string to column with prefix
from pyspark.sql.functions import when, concat_ws, col
df_prefix = df.select(
concat_ws(
" ",
when(col("gender") == "M", "Mr.").otherwise("Ms."),
col("firstname"),
col("lastname")
).alias("FullNameWithPrefix"),
"gender"
)
df_prefix.show(truncate=False)
Yields below the output.
# Output:
+-------------------+------+
|FullNameWithPrefix |gender|
+-------------------+------+
|Mr. James Smith |M |
|Mr. Michael |M |
|Mr. Robert Williams|M |
|Ms. Maria Jones |F |
|Ms. Jen Brown |F |
+-------------------+------+
<a id="h-pyspark-concat-with-separator"></a>
PySpark Concat with Separator
You can set a custom separator inside concat_ws() to return a single column with the chosen delimiter. It is commonly used in preparing CSV-style output, full names, or formatted labels.
# Use comma as separator
df_sep = df.select(
concat_ws(", ", "firstname", "lastname", "dob").alias("CSV_Format")
)
df_sep.show(truncate=False)
Yields below the output.
# Output:
+----------------------------+
|CSV_Format |
+----------------------------+
|James, Smith, 1991-04-01 |
|Michael, , 2000-05-19 |
|Robert, Williams, 1978-09-05|
|Maria, Jones, 1967-12-01 |
|Jen, Brown, 1980-02-17 |
+----------------------------+
<a id="h-pyspark-concat-ws-in-sql"></a>
PySpark concat_ws() in SQL
You can also use concat_ws() directly in SQL queries if your DataFrame is registered as a SQL view. This will return results directly from a SQL query using concat_ws().
# Register DataFrame as SQL view
df.createOrReplaceTempView("people")
# Use concat_ws in SQL
df_sql = spark.sql("""
SELECT concat_ws(' ', firstname, middlename, lastname) AS FullName, dob, gender, salary
FROM people
""")
df_sql.show(truncate=False)
Yields below the output.
# Output:
+----------------+----------+------+------+
|FullName |dob |gender|salary|
+----------------+----------+------+------+
|James Smith |1991-04-01|M |3000 |
|Michael Rose |2000-05-19|M |4000 |
|Robert Williams|1978-09-05|M |4000 |
|Maria Anne Jones|1967-12-01|F |4000 |
|Jen Mary Brown |1980-02-17|F |-1 |
+----------------+----------+------+------+
<a id="h-frequently-asked-questions-of-pyspark-concat-ws"></a>
Frequently Asked Questions of PySpark concat_ws()
concat() merges columns directly without adding any separators.
It skips null values during concatenation instead of returning null, ensuring clean output.
You can register a DataFrame as a SQL view and use concat_ws() in the SQL SELECT statement to merge multiple columns with a separator.
You can add prefixes, suffixes, or any constant string along with column values to format output, such as adding "Mr." or "Ms." to names.
It returns an empty string rather than a null value, avoiding unexpected null outputs.
<a id="h-conclusion"></a>
Conclusion
In this article, you have learned how to join multiple columns into a single string using PySpark’s concat_ws() function. While concat() merges values directly, it does not add separators and may not handle null values properly. In contrast, concat_ws() allows you to specify a separator, automatically ignores nulls, and offers greater flexibility. You can also use it with fixed strings, conditional expressions, or even within SQL queries. By applying these techniques, you can perform string concatenation in PySpark efficiently, creating clean and easy-to-read results.
Happy Learning!!
<a id="h-reference"></a>