How keep number in text

Question

I have a pyspark Dataframe, I would like to improve the regex bellow. I want to add a condition or modify the regex to:

• recover all number that is attached to a / or letter in the end.

Example of case1:

column_example                                        |   new_column
------------------------------------------------------|-----------------                                       |
mundo por el número de NJU/LOK 12345T98789-hablantes  |   12345
hispanohablantes ZES/UJ86758/L87586:residentes en     |   86758

Example case 2:

• I shouldn't take the number that arrived after ABC word.

Column Example:

    My_column                                             |         new_column
------------------------------------------------------|---------------------
mundo por el número de ABC 8567 hablantes             |           []
------------------------------------------------------|---------------------
con dominio nativo ABC 987480 millones de personas    |           []
------------------------------------------------------|---------------------
hispanohablantes residentes en ABC98754 otros países  |           []

The following code is:

ptn = re.complie(r'^(?:MOD)?[0-9]{4,6}$')
array_filter = udf(lambda arr: [ x.lstrip('MOD') for x in arr if re.match(ptn, x) ] if type(arr) is list else arr, ArrayType(StringType()))

How can I do it ? Thank you

Answer 1

One way without using udf for Spark before version 2.4.0:

from pyspark.sql.functions import split, regexp_replace

df.withColumn('new_column'
   , split(
       regexp_replace(
           regexp_replace('My_column', r'.*?(?<!ABC\s{0,5})(?<!\d)(\d{4,6})(?=[A-Z/])', '$1\0')
         , '\0?[^\0]*$'
         , ''
       )
     ,'\0')
   ) \
  .show(truncate=False)
+-----------------------------------------------------------------------+--------------+
|My_column                                                              |new_column    |
+-----------------------------------------------------------------------+--------------+
|23458/ mundo por el nmero de NJU/LOK 12345T98789 hablantes             |[23458, 12345]|
|con dominio nativo ABC 987480 millones ZES/UJ86758/L87586:residentes en|[86758]       |
|hispanohablantes  residentes en ABC98754/ otros pases                  |[]            |
+-----------------------------------------------------------------------+--------------+

Where:

• use regexp_replace: to replace the text matching the following pattern

  .*?(?<!ABC\s{0,5})(?<!\d)(\d{4,6})(?=[A-Z/])
  

with $1\0 which removes all unrelated text before NUMBER_NEEDED(saved in $1) which is not preceded by ABC\s{0,5} and \d but followed by [A-Z/]. put a NULL char \0 at the end of each matched $1.

• use split(text, '\0') to convert the above text into an array, notice that the last item of the array is irrelevant which should be excluded

• use another regexp_replace(text, '\0?[^\0]*$', '') to remove the trailing unrelated text before running the above split() function

Notes:

• (?<!ABC\s{0,5}) will allow to test 0-5 whitespaces between ABC and the NUMBER_NEEDED. since regex negative lookbehind does not support (?<!ABC\s*), if your text might contain more spaces in between, you can adjust 5 to a larger number. BTW. (?<!ABC\s{0,5}) is fine with PySpark but invalid in Python re module which allows only fixed-width pattern

• prepend (?s) to allow dotall mode if any texts contain line breaks

• I assumed that the NULL char \0 is not shown in your original texts, since it wont be part of matches, you can remove them all (regexp_replace(text, '\0', '')) before running the above 3 functions.

Another way using udf:

import re
from pyspark.sql.types import ArrayType, StringType
from pyspark.sql.functions import udf

ptn = re.compile(r'(?<!ABC)(?<!\d)(\d{4,6})(?=[A-Z/])')

find_number = udf(lambda x: re.findall(ptn, re.sub(r'(?<=ABC)\s+', '', x)) if x else [], ArrayType(StringType()))

df.withColumn('new_column', find_number('My_column')).show()