How to Extract Substrings in PySpark Using the `substr` Function

To handle diverse data cleaning and transformation needs, PySpark offers multiple approaches for isolating text segments within a column. We will explore five primary methods here, utilizing both fixed-length functions and delimiter-based functions.

Understanding the PySpark substr Function

The core of fixed-length string extraction in DataFrames is the F.substr() function. It is important to remember that PySpark indices are 1-based, meaning the first character is at position 1, not 0 (unlike native Python indexing). This function is critical for standardized data parsing where the required substring length is known or predictable.

Method 1: Extract Substring from Beginning of String

This method involves starting the extraction at index 1 and specifying the desired length. It is the most common use case for extracting prefixes or short codes, ensuring consistency when data starts uniformly.

from pyspark.sql import functions as F

#extract first three characters from team column
df_new = df.withColumn('first3', F.substring('team', 1, 3))

Method 2: Extract Substring from Middle of String

To extract content from the middle, you must accurately determine the starting position within the string. For example, if we want four characters starting from the second position, the start index is 2 and the length is 4. This technique is often necessary when dealing with fixed-width data fields that have been concatenated.

from pyspark.sql import functions as F

#extract four characters starting from position two in team column
df_new = df.withColumn('mid4', F.substring('team', 2, 4))

Method 3: Extract Substring from End of String

PySpark supports negative indexing within the substr function to facilitate backward traversal. Using a negative starting index allows us to easily isolate suffixes or trailing segments, regardless of the overall string length. For example, using -3 as the start index will begin counting three characters from the end of the string. The length parameter must still be specified as a positive integer.

from pyspark.sql import functions as F

#extract last three characters from team column
df_new = df.withColumn('last3', F.substring('team', -3, 3))

Delimiter-Based Extraction Using substring_index

While substr works well for fixed-length or position-based extraction, many real-world strings (like URLs, file paths, or complex identifiers) are structured using delimiters (e.g., periods, slashes, spaces). For these scenarios, PySpark’s substring_index function provides a more practical solution. This function takes three arguments: the column, the delimiter, and a count (which determines whether to keep content before or after the delimiter).

Method 4: Extract Substring Before Specific Character

To capture all content preceding a specific separator, we use a positive count value (typically 1). This tells PySpark to return the substring that appears before the Nth occurrence of the specified delimiter. In cases where we want the first word or segment, using a count of 1 is sufficient.

from pyspark.sql import functions as F

#extract all characters before space in team column
df_new = df.withColumn('beforespace', F.substring_index('team', ' ', 1))

Method 5: Extract Substring After Specific Character

Conversely, to isolate content that appears after a delimiter, we utilize a negative count value (typically -1). A count of -1 instructs PySpark to return the text following the delimiter, counting backward from the end of the string. This is ideal for extracting suffixes, last names, or specific elements at the end of a path or identifier.

from pyspark.sql import functions as F

#extract all characters after space in team column
df_new = df.withColumn('afterspace', F.substring_index('team', ' ', -1))

Setting Up the Demonstration Environment

To demonstrate these five powerful methods, we will first define a sample DataFrame containing NBA team names and points. This initial setup requires importing the SparkSession and defining our data schema to create the environment.

from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()

#define data
data = [['Dallas Mavs', 18], 
        ['Brooklyn Nets', 33], 
        ['Atlanta Hawks', 12], 
        ['Boston Celtics', 15], 
        ['Miami Heat', 19],
        ['Cleveland Cavs', 24],
        ['Orlando Magic', 28]] 
  
#define column names
columns = ['team', 'points'] 
  
#create dataframe using data and column names
df = spark.createDataFrame(data, columns) 
  
#view dataframe
df.show()

+--------------+------+
|          team|points|
+--------------+------+
|   Dallas Mavs|    18|
| Brooklyn Nets|    33|
| Atlanta Hawks|    12|
|Boston Celtics|    15|
|    Miami Heat|    19|
|Cleveland Cavs|    24|
| Orlando Magic|    28|
+--------------+------+

Example 1: Extract Substring from Beginning of String

Applying Fixed-Length Extraction (substr)

This first example demonstrates the most basic application of the substr function. We instruct PySpark to start at the first position (index 1) and capture a fixed length of 3 characters. This method is highly effective for generating standardized abbreviations or initials from text fields like the team name.

from pyspark.sql import functions as F

#extract first three characters from team column
df_new = df.withColumn('first3', F.substring('team', 1, 3))

#view updated DataFrame
df_new.show()

+--------------+------+------+
|          team|points|first3|
+--------------+------+------+
|   Dallas Mavs|    18|   Dal|
| Brooklyn Nets|    33|   Bro|
| Atlanta Hawks|    12|   Atl|
|Boston Celtics|    15|   Bos|
|    Miami Heat|    19|   Mia|
|Cleveland Cavs|    24|   Cle|
| Orlando Magic|    28|   Orl|
+--------------+------+------+

Example 2: Extract Substring from Middle of String

Targeting Internal Characters

When the desired data segment is located internally, precise index specification is mandatory. In this instance, we are extracting four characters, beginning at the second position of the string (start index 2). This operation allows data engineers to isolate specific embedded codes or sequences that are not located at either end of the string.