Remove whitespaces in pyspark dataframes

2
votes

I have a dataframe whose the schema is as follows:

root
 |-- key: string (nullable = true)
 |-- value: array (nullable = true)
 |    |-- element: string (containsNull = true)

I want to remove the first whitespace (if exists) in each element of the array in the value column from pyspark.sql.functions import regexp_replace I think it will be like the code below:

df.select(regexp_replace(col("values"), \s*, *)).show()

The df:

+---+------------------------+
|key|                   value|
+---+------------------------+
| k1|       [  x1 x2, x3, x4]|
| k2|      [x5,   x6 x7,  x8]|
| k3|[ x9 x10, x11,  x12 x13]|
+---+------------------------+

Expected result:

+---+------------------------+
|key|                   value|
+---+------------------------+
| k1|         [x1 x2, x3, x4]|
| k2|         [x5, x6 x7, x8]|
| k3|  [x9 x10, x11, x12 x13]|
+---+------------------------+

(All the whitespaces before the arrays' elements must be eliminated) Thank you

2
dataframepyspark
could you add sample input data and expected output just to make question more clear – kites

2 Answers

3
votes

Use posexplode to update first space if exists.

df.show()
#+---+----------------+
#|key|           value|
#+---+----------------+
#|  1| [a b, b c, c d]|
#|  2|[z x, xu, l z u]|
#+---+----------------+
from pyspark.sql.functions import *

df.selectExpr("key","posexplode(value)").withColumn("col",when(col("pos") ==0,regexp_replace(col('col'),' ','').otherwise(col("col")))).show()
#+---+---------------+
#|key|          value|
#+---+---------------+
#|  1| [ab, b c, c d]|
#|  2|[zx, xu, l z u]|
#+---+---------------+

For replacing whitespace at start case you can use

Spark-2.4+:

#+---+-----------------------+
#|key|value                  |
#+---+-----------------------+
#|1  |[   a b i,    b c, c d]|
#|2  |[   z x u, xu, l z u]  |
#+---+-----------------------+
df.selectExpr("transform(value, x -> regexp_replace(x,'^\\\s+','')) as dd").show(10,False)
#+------------------+
#|dd                |
#+------------------+
#|[a b i, b c, c d] |
#|[z x u, xu, l z u]|
#+------------------+

For Spark <2.4:

df.selectExpr("key","explode(value)").withColumn("value",regexp_replace(col("col"),"^\\s+",'')).groupBy(col("key")).agg(collect_list(col("value"))).show()
#+---+-------------------+
#|key|collect_list(value)|
#+---+-------------------+
#|  1|  [a b i, b c, c d]|
#|  2| [z x u, xu, l z u]|
#+---+-------------------+
2
votes

For removing spaces only at the start of the string. Use ltrim

    # Input DF
    df.show(truncate=False)

    +---+------------------------+
    |key|value                   |
    +---+------------------------+
    |1  |[  x1 x2, x3, x4 ]      |
    |2  |[x5,    x6 x7, x8]      |
    |2  |[ x9 x10, x11,  x12 x13]|
    +---+------------------------+

 df.withColumn("value", F.expr('''transform(value, x-> ltrim(x))''')).show(truncate=False)

    +---+----------------------+
    |key|value                 |
    +---+----------------------+
    |1  |[x1 x2, x3, x4 ]      |
    |2  |[x5, x6 x7, x8]       |
    |2  |[x9 x10, x11, x12 x13]|
    +---+----------------------+