How to covert a column with String to Array[String] in Scala/Spark?

Question

I have a data frame :

+--------------------------------------+------------------------------------------------------------+
|item                                  |item_codes                                               |
+--------------------------------------+------------------------------------------------------------+
|loose fit long sleeve swim shirt women|["2237741011","1046622","1040660","7147440011","7141123011"]|
+--------------------------------------+------------------------------------------------------------+

And schema looks like this =

root
 |-- item: string (nullable = true)
 |-- item_codes: string (nullable = true)

How can I convert the column item_codes string to Array[String] in Scala ?

Leo C · Accepted Answer · 2019-10-30 17:47:34Z

1

You can remove quotes/square brackets using regexp_replace, followed by a split to generate the ArrayType column:

val df = Seq(
  ("abc", "[\"2237741011\",\"1046622\",\"1040660\",\"7147440011\",\"7141123011\"]")
).toDF("item", "item_codes")

df.
  withColumn("item_codes", split(regexp_replace($"item_codes", """\[?\"\]?""", ""), "\\,")).
  show(false)
// +----+------------------------------------------------------+
// |item|item_codes                                            |
// +----+------------------------------------------------------+
// |abc |[2237741011, 1046622, 1040660, 7147440011, 7141123011]|
// +----+------------------------------------------------------+

answered Oct 30, 2019 at 17:47

Leo C

22.5k3 gold badges28 silver badges42 bronze badges

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

Comments

Wai Ha Lee · Accepted Answer · 2019-10-30 18:33:07Z

0

You can use the split method after doing some "preprocessing"

val col_names = Seq("item", "item_codes")

val data = Seq(("loose fit long sleeve swim shirt women", """["2237741011","1046622","1040660","7147440011","7141123011"]"""))

val df = spark.createDataFrame(data).toDF(col_names: _*)

// chop off first 2 and last 2 character and split at ","
df.withColumn("item_codes", split(expr("substring(item_codes, 3, length(item_codes)-4)"), """","""")).printSchema

If your format can change you might be more flexible using a regexp as leo suggestes chopping off everything that is not a digit or a , and split at ,

edited Oct 30, 2019 at 18:33

Wai Ha Lee

8,856102 gold badges61 silver badges100 bronze badges

answered Oct 30, 2019 at 17:50

Paul

1,1928 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to covert a column with String to Array[String] in Scala/Spark?

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