Remove sub-string from array elements and duplicate pyspark

Question

I have a pyspark dataframe:

number  |  matricule      
--------------------------------------------
1       |  ["AZ 1234", "1234", "00100"]                   
--------------------------------------------
23      |  ["1010", "12987"]                   
--------------------------------------------
56      |  ["AZ 98989", "22222", "98989"]                   
--------------------------------------------

In matricule array, I have duplicates values if I remove AZ String. I would like to remove "AZ" string then remove duplicates values in matricule array. Knowing that sometimes I have a space just after AZ, I should remove it also.

I did an udf:

def remove_AZ(A)
    for item in A:
        if item.startswith('AZ'):
            item.replace('AZ','')
udf_remove_AZ = F.udf(remove_AZ)
df = df.withColumn("AZ_2", udf_remove_AZ(df.matricule))

I got null in all AZ_2 column.

How can I remove the AZ from the each value in matricule array then removing the duplicates inside ? Thank you

blackbishop · Accepted Answer · 2020-02-26 12:53:03Z

7

For Spark 2.4+, you can use transform + array_distinct function like this:

t = "transform(matricule, x -> trim(regexp_replace(x, '^AZ', '')))"
df.withColumn("matricule", array_distinct(expr(t))).show(truncate=False) 

#+------+--------------+
#|number|matricule     |
#+------+--------------+
#|1     |[1234, 00100] |
#|23    |[1010, 12987] |
#|56    |[98989, 22222]|
#+------+--------------+

For each element of the array, using transform, we remove AZ characters from the beginning of the string using regexp_replace and trim the leading and trailing spaces if there are.

answered Feb 26, 2020 at 12:53

blackbishop

32.8k11 gold badges61 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pissall · Accepted Answer · 2020-02-26 12:52:46Z

4

Can you write your udf as:

def remove_az(array):
    array = [w.replace('AZ', '').strip() for w in array]
    return array

remove_az_udf = F.udf(remove_az)

df = df.withColumn("AZ_2", remove_az_udf(df.matricule))

answered Feb 26, 2020 at 12:52

pissall

7,4442 gold badges29 silver badges47 bronze badges

Collectives™ on Stack Overflow

Remove sub-string from array elements and duplicate pyspark

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related