In PySpark, pyspark.sql.functions.split() divides a string column wherever a regular-expression pattern matches and returns an array of strings. For example, F.split(F.col('line'), ',') turns a comma-separated value such as red,blue,green into an array with three elements; use the optional limit argument to control how many splits occur.
What the PySpark split function does
split() is a Spark SQL column function, not Python’s built-in string method. It creates a column expression that Spark evaluates as part of a DataFrame query. Its output is an array, so splitting a value does not automatically create several DataFrame columns or rows. You decide what to do with that array afterward: select individual positions, keep it as an array, or expand it into rows.
The function is useful when a source field contains values separated by a consistent delimiter, such as a pipe-delimited code, a list of tags, or words separated by whitespace. Its pattern argument is a regular expression interpreted by Spark’s Java-regex engine. That means simple delimiters are easy to supply, but characters with special regex meanings need attention.
Basic syntax and a working example
Import the function module, then pass a string column and a pattern. The following example adds an array column named items:
from pyspark.sql import functions as F
df = spark.createDataFrame(
[('red,blue,green',), ('black,white',)],
['colors']
)
result = df.withColumn('items', F.split(F.col('colors'), ','))
result.show(truncate=False)
The new column contains ['red', 'blue', 'green'] in the first row and ['black', 'white'] in the second. The original colors column remains because withColumn() adds or replaces a column; it does not remove the source. To return only the split result, select it directly:
result = df.select(F.split(F.col('colors'), ',').alias('items'))
You can also use F.col('colors') without explicitly constructing a column expression in some APIs, but using F.col() makes it clear that the input is a DataFrame column rather than a Python string value. The DataFrame operation is lazy: Spark builds a plan and evaluates it when an action such as show(), count(), or a write requires results.
Patterns are regular expressions
The second argument is not always treated as a literal delimiter. It is a regular expression, so a comma needs no special handling, while characters such as a dot, pipe, plus sign, asterisk, or question mark have regex meanings. To split on a literal pipe, escape it:
df = spark.createDataFrame([('north|south|west',)], ['route'])
result = df.withColumn('segments', F.split(F.col('route'), r'\|'))
The raw Python string r'\|' passes a backslash and pipe to the regex engine. The backslash tells that engine to treat the pipe literally instead of interpreting it as “or.” For a literal dot, use r'\.'. A character class is useful when several single-character delimiters should be equivalent: r'[,;]' splits on either a comma or semicolon.
For one or more whitespace characters, use r'\s+'. This is often better than splitting on a single space when input may contain repeated spaces or tabs. However, leading and trailing whitespace can still produce empty array elements. If those spaces are unwanted, trim the input first:
result = df.withColumn(
'words',
F.split(F.trim(F.col('sentence')), r'\s+')
)
Python’s raw-string prefix only affects how Python writes the pattern into the expression; the pattern is still interpreted as a Java regular expression by Spark. This distinction matters when copying regexes from another language, because regex features and escaping conventions are not identical across all engines.
How the limit argument changes the result
The optional third argument, limit, controls the maximum number of array elements. Its default is -1. With a positive limit, Spark makes no more than that many pieces, and the last piece contains the unsplit remainder. For example:
df = spark.createDataFrame([('A|B|C',)], ['code'])
all_parts = F.split(F.col('code'), r'\|')
first_two = F.split(F.col('code'), r'\|', 2)
The default expression produces ['A', 'B', 'C']. The expression with a limit of 2 produces ['A', 'B|C']: Spark splits at the first matching pipe, then keeps the remaining text together as the second element. A limit of 1 therefore leaves the input as a single piece. A limit of zero or a negative value allows the pattern to be applied as many times as possible; the default negative limit also preserves trailing empty pieces when a delimiter appears at the end.
Choose a positive limit when the data has a known leading structure but a variable remainder. For instance, a record may contain a category followed by a free-form description that itself includes the delimiter. A limit of two separates the category while preserving the rest of the description in one field. It does not validate that the input has the expected number of fields, so check the resulting array length if malformed records need to be detected.
Get array elements or turn them into rows
Because the result is an array, you can retrieve a position with indexing or the array methods available on a Spark column. PySpark’s getItem() uses a zero-based index: position 0 is the first element.
parts = F.split(F.col('record'), r'\|')
result = df.select(
parts.getItem(0).alias('first_field'),
parts.getItem(1).alias('second_field')
)
Be deliberate about indexing when comparing APIs: Spark’s element_at() uses one-based positive indexes, unlike getItem(). A missing position may produce a null result, depending on the operation and Spark settings, so records with short arrays should be considered when writing downstream logic.
To produce one row per array element, use explode():
result = df.select(
'record',
F.explode(F.split(F.col('record'), r'\|')).alias('field')
)
This changes the row count: a record with three split elements contributes three output rows. Ordinary explode() does not produce a row for a null or empty array. If retaining a row for null or empty input is important, look at explode_outer() and confirm that its null-row behavior fits the downstream schema and logic.
Practical example: normalize a tag column
Imagine a DataFrame with a tags field containing entries such as spark, data engineering, analytics. The goal is to split on commas, trim spaces around each tag, and make one row per tag. Splitting and exploding handle the main shape change, but trimming each array element is a separate transformation. One approach is to split, explode, and then trim the resulting string:
tags = df.select(
F.explode(F.split(F.col('tags'), ',')).alias('tag')
)
clean_tags = tags.select(F.trim(F.col('tag')).alias('tag'))
For the example value, the output rows are spark, data engineering, and analytics, without the spaces surrounding the second and third entries. This does not split the phrase data engineering into two words because the delimiter is a comma, not whitespace. If empty tags should be excluded, add a filter after trimming, for example clean_tags.filter(F.col('tag') != ''). Consider nulls separately: comparisons against null do not behave like ordinary string comparisons, so a null policy may require an explicit null check or explode_outer().
Common edge cases and mistakes
- Forgetting regex escaping: A pattern of
'|'is not a literal pipe pattern. Escape regex metacharacters or use an appropriate character class. - Expecting multiple columns:
split()returns one array-valued column. UsegetItem()to select positions orexplode()to make rows. - Assuming a fixed number of fields: Input records may have missing or extra delimiters. Check array size or apply validation before relying on particular positions.
- Overlooking empty elements: Consecutive delimiters can create an empty element between them, and a trailing delimiter can produce an empty final element with the default negative limit.
- Using a Python string method on a Spark column: Python’s
str.split()operates on a local Python string. For distributed DataFrame columns, usepyspark.sql.functions.split(). - Assuming split parses CSV: Splitting on commas does not understand quoted fields, escaped quotes, or embedded commas inside quotes.
Null input generally produces a null array, not an array containing the text 'null'. That is different from the literal string 'null', which is ordinary input text and can be split. Decide whether null input should be kept, filtered, or replaced before using the array in later expressions.
When split is not the right parser
split() is appropriate when the delimiter rules are simple and the source format is genuinely delimiter-separated. It is not a complete parser for structured formats. For CSV data with quoted fields, use Spark’s CSV reader or CSV parsing functions with suitable options rather than splitting on commas. For JSON, use Spark’s JSON functions or reader. Those tools understand format-specific quoting, escaping, types, and nested structure that a regex split does not.
For extracting one known piece without needing an array, consider a more targeted function. regexp_extract() can capture a portion that matches a regex group, while substring_index() can return text before or after a specified occurrence of a delimiter. These alternatives can make intent clearer when the task is extraction rather than tokenization. If records have a stable schema, parsing into named fields is usually easier to maintain than scattering array indexes throughout later transformations.
Performance and data-quality considerations
Since split() is a Spark SQL expression, it can be part of a distributed DataFrame plan; it does not require collecting every string into Python on the driver. Still, splitting very large text fields can create large arrays and increase memory and shuffle costs if those arrays are later exploded or joined. Split only when the downstream task needs the pieces, and avoid expanding arrays into rows if an array-valued result is sufficient.
Regex complexity also matters. A straightforward literal delimiter or simple character class is easier to reason about than a highly elaborate pattern. Validate representative inputs, including nulls, empty strings, repeated delimiters, trailing delimiters, and unexpected whitespace. For production pipelines, record or filter malformed values explicitly rather than silently assuming every row has the same number of fields.
Frequently Asked Questions
Does PySpark split use a regular expression or a literal delimiter?
It uses a regular-expression pattern. A comma is safe as a simple pattern, but regex metacharacters such as a pipe or dot must be escaped when they are intended literally.
How do I split a PySpark column on whitespace?
Use F.split(F.col('text'), r'\s+') to split on one or more whitespace characters. Apply F.trim() first if leading or trailing whitespace should not create empty elements.
How do I split a string into columns in PySpark?
First call split() to create an array, then select positions with getItem(0), getItem(1), and so on, assigning each selected expression an alias. The split function itself returns an array, not separate columns.
What does limit mean in PySpark split?
A positive limit caps the number of resulting pieces; the last piece contains the remainder after the final split. The default is -1, which allows all matches and preserves trailing empty elements.
How can I split an array into separate rows?
Pass the split expression to F.explode(), usually in select(). Each array element becomes a row, so the result can contain more rows than the original DataFrame.
Can split correctly parse a CSV field with quoted commas?
No. A regular-expression split on commas does not apply CSV quoting rules. Use Spark’s CSV parsing support when commas inside quoted fields or escaped quotes must be interpreted correctly.
Related
- Split by Position in Power Query: A Practical Guide
- TinyTask for Sol’s RNG: What It Can and Can’t Do Safely
- Java split() Method: Split Strings with Regex and Limits
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Age From DOB in Excel: DATEDIF Formula and ExamplesTo calculate age in completed years from a date of birth in Excel, enter the birth date in…
- How to Calculate a Unique Count in Excel with FormulasTo count distinct, nonblank values in Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0), replacing A2:A100 with your data range.…
- Count Unique Text Values in Excel: Formulas & TipsTo count unique, nonblank text values in Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,ISTEXT(A2:A100)*(A2:A100<>"")))),0) when the values are in…
- Excel Concatenate Dates While Keeping Formatting in TextTo concatenate a date in Excel and keep its display format, convert the date to formatted text with…
- Excel What-If Analysis with Goal Seek: A Practical GuideIn Excel for Microsoft 365 on Windows, use Goal Seek to find the input value that makes a…
- Bing Chat Excel Formula Not Responding?If Bing Chat is not responding to an Excel formula, first check whether the problem is the chat…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.