AWS Glue - pySpark: spliting a string column into a new integer array column

Question

I am trying to perform an ETL job on AWS using Glue and pySpark, but unfortunately, I'm really new to this.

For the most part I dont have any problem working with glue dynamic dataframe to perform applymapping and some of the other transformations that I must perform. But I am facing a problem with a particular column that I must convert from string to integer array. In this column, value, we have the datatype set as string that is infact an array of integers converted to string and separated by space, for example a data entry in the value column looks like '111 222 333 444 555 666'. I must convert this column to be an integer array so that my data is transformed into '[111, 222, 333, 444, 555, 666]'.

How can I achieve this in AWS Glue and using pySpark? Any help is really appreciated.

notNull · Accepted Answer · 2020-04-20 16:22:49Z

1

Split the value column by space using split function and cast to array<int>.

(or) by using transform (From Spark-2.4) function and casting array elements as int.

Example:

df=spark.createDataFrame([('111 222 333 444 555 666',)],["value"])
df.printSchema()
#root
# |-- value: string (nullable = true)

#using split and cast as array<int>  
df.withColumn("array_int",split(col("value"),"\\s+").cast("array<int>")).\
    show(10,False)

#using transform function
df.withColumn("array_int",expr("""transform(split(value,"\\\s+"), x -> int(x))""")).\
show(10,False)
#+-----------------------+------------------------------+
#|value                  |array_int                     |
#+-----------------------+------------------------------+
#|111 222 333 444 555 666|[111, 222, 333, 444, 555, 666]|
#+-----------------------+------------------------------+

df.withColumn("array_int",split(col("value"),"\\s+").cast("array<int>")).printSchema()
df.withColumn("array_int",expr("""transform(split(value,"\\\s+"), x -> int(x))""")).printSchema()    
#root
# |-- value: string (nullable = true)
# |-- array_int: array (nullable = true)
# |    |-- element: integer (containsNull = true)

edited Apr 20, 2020 at 16:22

answered Apr 20, 2020 at 16:11

notNull

31.8k4 gold badges41 silver badges58 bronze badges

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

1 Comment

Hirad Gorgoroth Over a year ago

Thank you so much sir. I have used the split method that you mentioned and it works perfectly.

Collectives™ on Stack Overflow

AWS Glue - pySpark: spliting a string column into a new integer array column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related