search
Search
Login
Map of Data Science
menu
menu
search toc
Thanks for the thanks!
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
search
keyboard_voice
close
Searching Tips
Search for a recipe:
"Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to

Extracting substrings from PySpark DataFrame column

schedule Mar 5, 2023
Last updated
local_offer
PySpark
Tags
map
Check out the interactive map of data science

There are mainly two methods you can use to extract substrings from column values in a PySpark DataFrame:

Extracting substring using position and length (substr)

Consider the following PySpark DataFrame:

df = spark.createDataFrame([['Alex', 20], ['Bob', 30]], ['name', 'age'])
df.show()
+----+---+
|name|age|
+----+---+
|Alex| 20|
| Bob| 30|
+----+---+

To extract the substring from the 1st position (inclusive) with a length of 3:

from pyspark.sql import functions as F
df.select(F.col('name').substr(1,3).alias('shorter_name')).show()
+------------+
|shorter_name|
+------------+
| Ale|
| Bob|
+------------+

Here, note the following:

  • the first argument of substr(1,3) is the non-indexed-based starting position (inclusive). The second argument (3 in this case) is the maximum number of characters to extract. Emphasis on the maximum here because If we set substr(1,4), 'Bob' would still be returned.

  • we are using the alias(~) method to assign a label to the PySpark Column returned by substr(~).

You could also specify a negative starting position for substr(~):

from pyspark.sql import functions as F
df.select(F.col('name').substr(-3,2).alias('shorter_name')).show()
+------------+
|shorter_name|
+------------+
| le|
| Bo|
+------------+

Here, we are starting from the 3rd character (inclusive) from the end.

Extracting substring using regular expression (regexp_extract)

Consider the following PySpark DataFrame:

df = spark.createDataFrame([['id-6', 20], ['id-8', 30]], ['id', 'age'])
df.show()
+----+---+
| id|age|
+----+---+
|id-6| 20|
|id-8| 30|
+----+---+

To extract the id number from the id column, use the regexp_extract(~) method:

from pyspark.sql import functions as F
df.select(F.regexp_extract('id', '(\d+)', 1).alias('id_number')).show()
+---------+
|id_number|
+---------+
| 6|
| 8|
+---------+

Here, note the following:

  • the first argument of regexp_extract(~) is the label of the target column.

  • the regular expression (\d+) captures one or more digits. The parentheses are important here because they are used to capture and extract substrings.

  • the third argument value 1 means that we capture the substring obtained from the first group. This argument is useful when we have multiple capturing groups (e.g. (\d+)-(\d+)). For a more detailed discussion on regexp_extract(~), please consult our documentation here.

robocat
Published by Isshin Inada
Edited by 0 others
Did you find this page useful?
thumb_up
thumb_down
Comment
Citation
Ask a question or leave a feedback...
thumb_up
0
thumb_down
0
chat_bubble_outline
0
settings
Enjoy our search
Hit / to insta-search docs and recipes!