Capture only the number that is followed by a specific letter in Pyspark with regex

bzimons 0
Viewed 37

I have a dataframe that look like this:

from pyspark.sql.functions import *
from pyspark.sql.types import *
    data = [('1',"12345 soda bottle 1500ml"),\
      ('2',"6789 beer can 450ml"),\
       ("3","beer with no number before 375ml")\
      ]
columnname = ['id','product']
df = spark.createDataFrame(data=data, schema = columnname)

+---+--------------------------------+
|id |product                         |
+---+--------------------------------+
|1  |12345 soda bottle 1500ml        |
|2  |6789 beer can 450ml             |
|3  |beer with no number before 375ml|
+---+--------------------------------+

I want the volume in another column, but some of the values has numbers that I don't need.

So far I tried this:

df = df.withColumn('volume',regexp_extract(col('product'), '([0-9]{3,5}.*ml)', 1)
              ).withColumn('volume_number',regexp_extract(col('volume'), '^[^m]+', 0))

But the result is not what I spected:

+---+--------------------------------+------------------------+----------------------+
|id |product                         |volume                  |volume_number         |
+---+--------------------------------+------------------------+----------------------+
|1  |12345 soda bottle 1500ml        |12345 soda bottle 1500ml|12345 soda bottle 1500|
|2  |6789 beer can 450ml             |6789 beer can 450ml     |6789 beer can 450     |
|3  |beer with no number before 375ml|375ml                   |375                   |
+---+--------------------------------+------------------------+----------------------+

Desired output:

+---+--------------------------------+------------------------+----------------------+
|id |product                         |volume                  |volume_number         |
+---+--------------------------------+------------------------+----------------------+
|1  |12345 soda bottle 1500ml        |1500ml                  |1500                  |
|2  |6789 beer can 450ml             |450ml                   |450                   |
|3  |beer with no number before 375ml|375ml                   |375                   |
+---+--------------------------------+------------------------+----------------------+
pyspark regex python
2 Answers

You're almost there. Adding a .*\s before your pattern should do what you're looking for.

df = df.withColumn('volume',regexp_extract(col('product'), '.*\s([0-9]{3,5}.*ml)', 1))
df = df.withColumn('volume_number',regexp_extract(col('volume'), '^[^m]+', 0))
df.show()

+---+--------------------+------+-------------+
| id|             product|volume|volume_number|
+---+--------------------+------+-------------+
|  1|12345 soda bottle...|1500ml|         1500|
|  2| 6789 beer can 450ml| 450ml|          450|
|  3|beer with no numb...| 375ml|          375|
+---+--------------------+------+-------------+

Explanation:

  • .*\s captures everything until the first capture group
  • ([0-9]{3,5}.*ml) defines the first capture, that's formed of numbers between 3 and 5 digits, any amount of characters followed by the ml string.

Note: I would've used \b as a word boundary instead of the \s, but it doesn't work as expected in JupyterLab.

user1BZ0 
df = df.withColumn('volume',regexp_extract(col('product'), '[0-9]+ml', 0))
df = df.withColumn('volume_number',regexp_extract(col('volume'), '^[0-9]+', 0))
df.show()


+---+--------------------+------+-------------+
| id|             product|volume|volume_number|
+---+--------------------+------+-------------+
|  1|12345 soda bottle...|1500ml|         1500|
|  2| 6789 beer can 450ml| 450ml|          450|
|  3|beer with no numb...| 375ml|          375|
+---+--------------------+------+-------------+

[0-9]+ means that at least one number will be captured. (the + symbol means one or more). Followed by the literal 'ml'. If you know the column will always end with the volume you can use [0-9]+ml$ where the $ matches the end of the line

mahdilamb0
Related